Okuqukethwe
Amathebula e-Pivot mahle kuwo wonke umuntu - abala ngokushesha, futhi ahlelwa ngendlela eguquguqukayo, futhi umklamo ungafakwa kuwo ngobuhle, uma kudingeka. Kodwa kukhona futhi izindiza ezimbalwa ku-ointment, ikakhulukazi, ukungakwazi ukudala isifinyezo, lapho indawo yenani akufanele iqukathe izinombolo, kodwa umbhalo.
Ake sizame ukuzungeza lo mkhawulo futhi siqhamuke “nezinduku ezimbalwa” esimeni esifanayo.
Ake sithi inkampani yethu ithutha imikhiqizo yayo ngeziqukathi iye emadolobheni amaningana aseZweni Lethu naseKazakhstan. Iziqukathi azithunyelwa isikhathi esingaphezu kwesisodwa ngenyanga. Isiqukathi ngasinye sinenombolo ye-alphanumeric. Njengedatha yokuqala, kukhona uhlu olujwayelekile lokulethwa kwetafula, lapho udinga ukwenza uhlobo oluthile lwesifinyezo ukuze ubone ngokucacile izinombolo zeziqukathi ezithunyelwa edolobheni ngalinye kanye nenyanga ngayinye:
Ukuze kube lula, masenze ithebula ngedatha yokuqala “ihlakaniphile” kusenesikhathi sisebenzisa umyalo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula) futhi umqambe igama Ukukhishwa ithebhu umakhi (Umklamo). Esikhathini esizayo, lokhu kuzokwenza ukuphila kube lula, ngoba. kuzokwazi ukusebenzisa igama letafula namakholomu alo ngqo kumafomula.
Indlela 1. Okulula kakhulu - sebenzisa Umbuzo Wamandla
I-Power Query iyithuluzi elinamandla kakhulu lokulayisha nokuguqula idatha ku-Excel. Lesi sengezo sakhelwe ku-Excel ngokuzenzakalela kusukela ngo-2016. Uma une-Excel 2010 noma 2013, ungayilanda futhi uyifake ngokuhlukene (mahhala ngokuphelele).
Yonke inqubo, ukuze icace, ngiyihlaziye isinyathelo ngesinyathelo kuvidiyo elandelayo:
Uma kungenakwenzeka ukusebenzisa Umbuzo Wamandla, ungahamba ngezinye izindlela - ngethebula le-pivot noma amafomula.
Indlela 2. Isifinyezo esiyisizayo
Ake sengeze ikholomu eyodwa etafuleni lethu lokuqala, lapho sisebenzisa ifomula elula sibala inombolo yomugqa ngamunye kuthebula:
Ngokusobala, u- -1 uyadingeka, ngoba sinesihloko somugqa owodwa etafuleni lethu. Uma ithebula lakho lingekho ekuqaleni kweshidi, ungasebenzisa ifomula eyinkimbinkimbi kancane, kodwa yendawo yonke ebala umehluko ezinambeni zomugqa wamanje kanye nesihloko sethebula:
Manje, ngendlela evamile, sizokwakha ithebula le-pivot lohlobo olufunekayo ngokusekelwe kudatha yethu, kodwa kunkambu yenani sizolahla inkambu. Inombolo yomugqa esikhundleni salokho esikufunayo isitsha:
Njengoba singenazo iziqukathi ezimbalwa edolobheni elilodwa ngenyanga efanayo, isifinyezo sethu, empeleni, ngeke sikhiphe inani, kodwa izinombolo zomugqa zeziqukathi esizidingayo.
Ukwengeza, ungavala isamba esikhulu nesincane kuthebhu Umakhi – Isamba esiphelele и Isamba esincane (Idizayini - Amanani Amakhulu, Ingqikithi Encane) futhi endaweni efanayo shintsha isifinyezo sibe isakhiwo sethebula esivumelana nenkinobho Bika isiqophi (Isakhiwo sombiko).
Ngakho-ke, sesivele siphakathi komphumela: sinetafula lapho, ezimpambanweni zedolobha nenyanga, kukhona inombolo yomugqa etafuleni lomthombo, lapho ikhodi yesiqukathi esiyidingayo ilele.
Manje ake sikopishe isifinyezo (eshidini elifanayo noma kwelinye) futhi sinamathisele njengamavelu, bese sifaka ifomula yethu endaweni yenani, ezokhipha ikhodi yesiqukathi ngenombolo yomugqa etholakala esifinyezweni:
Umsebenzi IF (UMA), kulesi simo, ihlola ukuthi iseli elilandelayo kusifinyezo alinalutho. Uma ingenalutho, khipha iyunithi yezinhlamvu yombhalo engenalutho “”, okungukuthi shiya iseli lingenalutho. Uma ingenalutho, khipha kukholamu Isiqukathi ithebula lomthombo Ukukhishwa okuqukethwe kweseli ngenombolo yomugqa usebenzisa umsebenzi INDEX (INDEX).
Mhlawumbe okuwukuphela kwephuzu elingacacile lapha igama eliphindwe kabili Isiqukathi kufomula. Indlela yokubhala eyinqaba kanje:
Izimpahla[[Isitsha]:[Isitsha]]
… iyadingeka kuphela ukuze kubhekiselwe kukholamu Isiqukathi ibiphelele (njengereferensi enezimpawu ezingu-$ zamathebula “angenahlakaniphile”) futhi ayizange ishelele kumakholomu angomakhelwane lapho sikopisha ifomula yethu kwesokudla.
Ngokuzayo, lapho ushintsha idatha kuthebula lomthombo Ukukhishwa, kufanele sikhumbule ukubuyekeza isifinyezo sethu esiyisisetshenziswa ngezinombolo zomugqa ngokuchofoza kwesokudla kuso nokukhetha umyalo Buyekeza & Londoloza (Vuselela).
Indlela 3. Amafomula
Le ndlela ayidingi ukwakhiwa kwethebula le-pivot eliphakathi nendawo nokubuyekezwa okwenziwa ngesandla, kodwa isebenzisa “isikhali esinzima” se-Excel - umsebenzi. I-SUMMESLIMN (SUMIFS). Esikhundleni sokubheka izinombolo zemigqa ngokufingqa, ungazibala usebenzisa le fomula:
Ngobukhulu obuthile bangaphandle, empeleni, lokhu kuyindlela evamile yokusetshenziswa komsebenzi okhethiwe wokuhlanganisa I-SUMMESLIMNA ehlanganisa izinombolo zomugqa zedolobha nenyanga enikeziwe. Futhi, njengoba singenazo iziqukathi ezimbalwa edolobheni elifanayo ngenyanga efanayo, umsebenzi wethu ngeke, empeleni, ukhiphe inani, kodwa inombolo yomugqa ngokwayo. Bese umsebenzi kakade ajwayelekile kusukela indlela odlule INDEX Ungaphinda ukhiphe amakhodi esiqukathi:
Yiqiniso, kulokhu, akusadingeki ucabange ngokubuyekeza isifinyezo, kodwa ematafuleni amakhulu, umsebenzi. I-SUMMESLI ingahamba kancane ngokuphawulekayo. Ngemva kwalokho kuzodingeka ukuthi uvale ukubuyekezwa okuzenzakalelayo kwamafomula, noma usebenzise indlela yokuqala - ithebula le-pivot.
Uma ukubukeka kwesifinyezo kungafanele kakhulu embikweni wakho, ungakwazi ukukhipha izinombolo zomugqa kuwo uye kuthebula lokugcina hhayi ngokuqondile, njengoba senzile, kodwa usebenzisa umsebenzi. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Indlela yokwenza lokhu ingatholakala lapha.
- Ungawakha kanjani umbiko usebenzisa ithebula le-pivot
- Uzimisa kanjani izibalo kumathebula e-pivot
- Ukubala okukhethiwe nge-SUMIFS, COUNTIFS, njll.