Okuqukethwe
Amatafula e-Pivot ingelinye lamathuluzi anamandla ku-Excel. Zikuvumela ukuthi uhlaziye futhi ufingqe izifinyezo ezahlukahlukene zenani elikhulu ledatha ngokuchofoza amagundane okumbalwa nje. Kulesi sihloko, sizojwayelana namathebula e-pivot, siqonde ukuthi ayini, sifunde ukuwakha nokuwenza ngendlela oyifisayo.
Lesi sihloko sabhalwa kusetshenziswa i-Excel 2010. Umqondo we-PivotTables awukashintshi kakhulu phakathi neminyaka edlule, kodwa indlela owakha ngayo ihluke kancane kunguqulo entsha ye-Excel. Uma unenguqulo ye-Excel hhayi engu-2010, bese uzilungiselela ukuthi izithombe-skrini kulesi sihloko zizohluka kulokho okubona esikrinini sakho.
Umlando omncane
Ezinsukwini zokuqala zesoftware yesipredishithi, i-Lotus 1-2-3 rule ball. Ukubusa kwayo bekuphelele kangangokuba imizamo yeMicrosoft yokuzakhela eyayo isoftware (i-Excel) njengenye ye-Lotus yabonakala njengokumosha isikhathi. Manje phambili ngokushesha ku-2010! I-Excel ibusa amaspredishithi ngaphezu kwekhodi ye-Lotus eyake yenziwa emlandweni wayo, futhi inani labantu abasasebenzisa i-Lotus liseduze noziro. Kungenzeka kanjani lokhu? Sasiyini isizathu soshintsho olukhulu kangaka lwezenzakalo?
Abahlaziyi bahlonza izici ezimbili eziyinhloko:
- Okokuqala, uLotus wanquma ukuthi le nkundla entsha ye-GUI ebizwa ngokuthi iWindows bekuyimfashini edlulayo engeke ihlale isikhathi eside. Benqabile ukwakha inguqulo ye-Windows ye-Lotus 1-2-3 (kodwa iminyaka embalwa kuphela), bebikezela ukuthi inguqulo ye-DOS yesofthiwe yabo izoba yibo bonke abathengi abangake bayidinge. IMicrosoft ithuthukise i-Excel ngokwemvelo yeWindows.
- Okwesibili, iMicrosoft yethule ithuluzi ku-Excel elibizwa ngokuthi i-PivotTables elalingatholakali ku-Lotus 1-2-3. I-PivotTables, ngaphandle kwe-Excel, ibonakale iwusizo kakhulu kangangokuthi abantu bavame ukunamathela kuhlelo olusha lwesofthiwe ye-Excel kunokuba baqhubeke ne-Lotus 1-2-3, ababengenayo.
I-PivotTables, kanye nokubukela phansi impumelelo ye-Windows ngokuvamile, idlale imashi yokufa ye-Lotus 1-2-3 futhi yangenisa impumelelo ye-Microsoft Excel.
Ayini amathebula e-pivot?
Ngakho-ke, iyiphi indlela engcono kakhulu yokubonakalisa ukuthi i-PivotTables iyini?
Ngamagama alula, amathebula e-pivot ayizifinyezo zedatha ethile, edalwe ukusiza ukuhlaziya le datha. Ngokungafani namanani adalwe ngesandla, i-Excel PivotTables iyasebenzisana. Uma sezidaliwe, ungazishintsha kalula uma zingakuniki isithombe obusilindele. Ngokuchofoza okumbalwa nje kwegundane, isamba singaphenywa ukuze izihloko zekholomu zibe izihloko zemigqa futhi ngokuphambene nalokho. Ungenza izinto eziningi ngamathebula e-pivot. Esikhundleni sokuzama ukuchaza ngamagama zonke izici zamathebula e-pivot, kulula ukukubonisa ngokwenza ...
Idatha oyihlaziya nge-PivotTables ayinakwenzeka ngokungahleliwe. Kufanele kube idatha eluhlaza, njengohlu lolunye uhlobo. Isibonelo, kungaba uhlu lwezinto ezithengisiwe ezenziwe yinkampani ezinyangeni eziyisithupha ezedlule.
Bheka idatha eboniswe esithombeni esingezansi:
Qaphela ukuthi lena akuyona idatha eluhlaza, njengoba isivele ifinyeziwe. Esitokisini B3 sibona i-$ 30000, okungenzeka ukuthi umphumela ophelele owenziwa nguJames Cook ngoJanuwari. Ikuphi-ke idatha yoqobo? Isibalo sika-$30000 sivelaphi? Lukuphi uhlu lwangempela lwezindali lapho le ngqikithi yanyanga zonke ithathwe kulo? Kuyacaca ukuthi othile wenze umsebenzi omuhle kakhulu wokuhlela nokuhlunga yonke idatha yokuthengisa yezinyanga eziyisithupha ezedlule futhi ayiguqule ibe yithebula lamathothali esiwabonayo. Ucabanga ukuthi kwathatha isikhathi esingakanani? Ihora? Ihora leshumi?
Iqiniso liwukuthi ithebula elingenhla akulona ithebula le-pivot. Yenziwe ngesandla kusuka kudatha eluhlaza egcinwe kwenye indawo futhi yathatha okungenani amahora ambalwa ukuyicubungula. Ithebula elinjalo lokufingqa lingadalwa kusetshenziswa amathebula e-pivot emizuzwaneni embalwa nje. Ake sibheke ukuthi kanjani…
Uma sibuyela kuhlu lwangempela lokuthengisa, lungabukeka kanje:
Ungase umangale ukuthi kulolu hlu lwezohwebo ngosizo lwamathebula e-pivot futhi ngemizuzwana embalwa nje, singakha umbiko wokuthengisa wanyanga zonke ku-Excel, esiwuhlaziye ngenhla. Yebo, singakwenza lokho nokunye okwengeziwe!
Indlela yokudala ithebula le-pivot?
Okokuqala, qiniseka ukuthi unedatha ethile yomthombo eshidini le-Excel. Uhlu lwemisebenzi yezezimali luvame kakhulu okwenzekayo. Eqinisweni, kungaba uhlu lwanoma yini: imininingwane yokuxhumana yesisebenzi, iqoqo le-CD, noma idatha yenkampani yakho yokusebenzisa uphethiloli.
Ngakho-ke, siqala i-Excel ... futhi silayishe uhlu olunjalo ...
Ngemva kokuvula lolu hlu ku-Excel, singakwazi ukuqala ukwakha ithebula le-pivot.
Khetha noma yiliphi iseli kulolu hlu:
Bese kuthebhu Insertion (Faka) khetha umyalo I-PivotTable (Ithebula le-Pivot):
Ibhokisi lengxoxo lizovela Dala i-PivotTable (Ukudala ithebula le-pivot) elinemibuzo emibili yakho:
- Iyiphi idatha ongayisebenzisa ukuze udale ithebula le-pivot elisha?
- Ungalibeka kuphi itafula le-pivot?
Njengoba esinyathelweni sangaphambilini sesivele sikhethe iseli elilodwa lohlu, lonke uhlu luzokhethwa ngokuzenzakalelayo ukuze kwakhiwe ithebula le-pivot. Qaphela ukuthi singakhetha ububanzi obuhlukile, ithebula elihlukile, ngisho nomthombo wedatha wangaphandle njengoFinyelela noma ithebula lesizindalwazi se-MS-SQL. Ngaphezu kwalokho, sidinga ukukhetha ukuthi silibeka kuphi ithebula le-pivot elisha: eshidini elisha noma kwelinye lalawo akhona. Kulesi sibonelo, sizokhetha inketho - Ishidi Lokusebenzela Elisha (eshidini elisha):
I-Excel izodala ishidi elisha bese ibeka ithebula le-pivot elingenalutho kulo:
Uma nje sichofoza kunoma iyiphi iseli kuthebula le-pivot, kuzovela elinye ibhokisi lengxoxo: I-PivotTable Field List (Izinkambu zethebula le-Pivot).
Uhlu lwezinkambu phezulu kwebhokisi lengxoxo luwuhlu lwazo zonke izihloko ezisohlwini lwangempela. Izindawo ezine ezingenalutho ngaphansi kwesikrini zikuvumela ukuthi utshele i-PivotTable ukuthi ufuna ukufingqa kanjani idatha. Uma nje lezi zindawo zingenalutho, akukho lutho etafuleni futhi. Okufanele sikwenze ukuhudula izihloko ukusuka endaweni ephezulu ukuya ezindaweni ezingenalutho ngezansi. Ngesikhathi esifanayo, ithebula le-pivot likhiqizwa ngokuzenzakalelayo, ngokuvumelana nemiyalelo yethu. Uma senza iphutha, singasusa izihloko endaweni engezansi noma sihudule ezinye ukuze sizimiselele.
Indawo Izindinganiso (Izincazelo) cishe yiyona ebaluleke kakhulu kwezine. Isiphi isihloko esibekwe kule ndawo sinquma ukuthi iyiphi idatha ezofingqwa (isamba, isilinganiso, esiphezulu, ubuncane, njll.) Lawa cishe njalo amanani ezinombolo. Ikhandidethi elihle kakhulu lendawo kule ndawo idatha engaphansi kwesihloko Inani le (Izindleko) zetafula lethu langempela. Hudula lesi sihloko usiyise endaweni Izindinganiso (Amanani):
Sicela uqaphele ukuthi isihloko Inani le manje imakwe ngophawu, futhi isendaweni Izindinganiso (Amanani) sekuvele okufakiwe Isamba Semali (Inani lenkambu Inani), okubonisa ukuthi ikholomu Inani le kufingqwe.
Uma sibheka ithebula le-pivot ngokwalo, sizobona isamba sawo wonke amanani kusuka kukholomu Inani le ithebula langempela.
Ngakho, ithebula lethu lokuqala le-pivot liyadalwa! Elula, kodwa hhayi umxhwele ikakhulukazi. Cishe sifuna ukuthola ulwazi olwengeziwe mayelana nedatha yethu kunalokho esinalo njengamanje.
Ake siphendukele kudatha yokuqala futhi sizame ukuhlonza ikholomu eyodwa noma ngaphezulu engasetshenziswa ukuhlukanisa lesi samba. Isibonelo, singakha ithebula lethu le-pivot ngendlela yokuthi inani eliphelele lokuthengisa libalwe kumdayisi ngamunye ngamunye. Labo. imigqa izokwengezwa kuthebula lethu le-pivot negama lomthengisi ngamunye enkampanini kanye nenani labo eliphelele lokuthengisa. Ukuze uzuze lo mphumela, vele uhudule isihloko umuntu othengisayo (omele ukuthengisa) esifundeni Amalebula Omugqa (Izintambo):
Kuba mnandi kakhulu! I-PivotTable yethu isiqala ukwakheka…
Uyabona izinzuzo? Ngokuchofoza okumbalwa, sidale ithebula ebelizothatha isikhathi eside ukulidala mathupha.
Yini enye esingayenza? Nokho, ngomqondo othile, ithebula lethu le-pivot selilungile. Sidale isifinyezo esiwusizo sedatha yoqobo. Ulwazi olubalulekile seluvele lwamukelwe! Kuso sonke lesi sihloko, sizobheka izindlela ezithile zokwenza ama-PivotTables ayinkimbinkimbi, futhi sifunde ukuthi awenza kanjani ngokwezifiso.
Ukusethwa kwe-PivotTable
Okokuqala, singakha ithebula le-pivot elinezinhlangothi ezimbili. Masenze lokhu sisebenzisa isihloko sekholomu Indlela yokukhokha (Indlela yokukhokha). Vele uhudule isihloko Indlela yokukhokha endaweni Amalebula wekholomu (Amakholomu):
Sithola umphumela:
Kubukeka kupholile kakhulu!
Manje masenze itafula elinezinhlangothi ezintathu. Itafula elinjalo belingaba njani? Asibone…
Hudula Unhlokweni Iphakheji (Iyinkimbinkimbi) endaweni bika izihlungi (Izihlungi):
Qaphela lapho ekhona...
Lokhu kusinika ithuba lokuhlunga umbiko ngokwesisekelo sokuthi “Iyiphi indawo yeholide eyakhokhelwa.” Isibonelo, singabona ukuhlukaniswa ngabathengisi nangezindlela zokukhokha zazo zonke izakhiwo, noma ngokuchofoza okumbalwa kwegundane, sishintshe ukubuka kwethebula le-pivot futhi sibonise ukwahlukaniswa okufanayo kuphela kwalabo aba-ode inkimbinkimbi. Abafuna ilanga.
Ngakho-ke, uma ukuqonda lokhu ngendlela efanele, khona-ke ithebula lethu le-pivot lingabizwa nge-three-dimensional. Masiqhubeke sisethe...
Uma kungazelelwe kuvela ukuthi inkokhelo kuphela ngesheke nekhadi lesikweletu (okungukuthi, inkokhelo engenakheshi) okufanele iboniswe kuthebula le-pivot, lapho-ke singacisha isibonisi sesihloko. Cash (Imali). Ukuze lokhu, eduze Amalebula wekholomu chofoza umcibisholo oya phansi bese ususe ukumaka ibhokisi kumenyu yokudonsela phansi Cash:
Ake sibone ukuthi ithebula lethu le-pivot libukeka kanjani manje. Njengoba ubona, ikholomu Cash wanyamalala kuye.
Ukufometha ama-PivotTables ku-Excel
I-PivotTables ngokusobala iyithuluzi elinamandla kakhulu, kodwa kuze kube manje imiphumela ibukeka ilula futhi inesicefe. Isibonelo, izinombolo esizihlanganisayo azibukeki njengamanani amadola - ziyizinombolo nje. Asilungise lokhu.
Kuyakulinga ukwenza lokho okujwayele esimweni esinjalo bese uvele ukhethe lonke ithebula (noma ishidi lonke) bese usebenzisa izinkinobho zokufometha izinombolo ezisezingeni kubha yamathuluzi ukuze usethe ifomethi oyifunayo. Inkinga ngale ndlela ukuthi uma kwenzeka ushintsha ukwakheka kwethebula le-pivot esikhathini esizayo (okwenzeka ngethuba elingu-99%), ukufometha kuzolahleka. Esikudingayo yindlela yokwenza (cishe) ihlale njalo.
Okokuqala, ake sithole okungenayo Isamba Semali in Izindinganiso (Amanani) bese uchofoza kukho. Kumenyu evelayo, khetha into Izilungiselelo Zenkambu Yenani (Izinketho zenkambu yenani):
Ibhokisi lengxoxo lizovela Izilungiselelo Zenkambu Yenani (Izinketho zenkambu yenani).
Chofoza inkinobho Ifomethi yenombolo (Ifomethi Yenombolo), ibhokisi lengxoxo lizovuleka. Fometha Amaseli (ifomethi yeseli):
Kusukela ohlwini Isigaba (Amafomethi ezinombolo) khetha Accounting (Ezezimali) bese usetha inombolo yezindawo zamadesimali ku-zero. Manje cindezela izikhathi ezimbalwa OKukuze sibuyele etafuleni lethu le-pivot.
Njengoba ubona, izinombolo zifomethwe njengamanani edola.
Ngenkathi sisakuyo nokufometha, ake simise ifomethi ye-PivotTable yonke. Kunezindlela eziningana zokwenza lokhu. Sisebenzisa leyo elula...
Chofoza Amathuluzi e-PivotTable: Idizayini (Isebenza ngama-PivotTables: Umakhi):
Okulandelayo, nweba imenyu ngokuchofoza umcibisholo osekhoneni elingezansi kwesokudla sesigaba Izitayela ze-PivotTable (PivotTable Styles) ukuze ubone iqoqo elibanzi lezitayela ezisemgqeni:
Khetha noma yisiphi isitayela esifanele bese ubheka umphumela kuthebula lakho le-pivot:
Ezinye izilungiselelo ze-PivotTable ku-Excel
Kwesinye isikhathi udinga ukuhlunga idatha ngamadethi. Isibonelo, ohlwini lwethu lwezohwebo kunezinsuku eziningi, eziningi. I-Excel inikeza ithuluzi lokuqoqa idatha ngosuku, inyanga, unyaka, njalonjalo. Ake sibone ukuthi kwenziwa kanjani.
Okokuqala susa okufakiwe. Indlela yokukhokha kusukela esifundeni Amalebula wekholomu (Amakholomu). Ukuze wenze lokhu, idonsele emuva ohlwini lwezihloko, futhi endaweni yayo, hambisa isihloko Idethi Yokubhuka (usuku lokubhukha):
Njengoba ubona, lokhu kwenze isikhashana ithebula lethu le-pivot laba yize. I-Excel idale ikholomu ehlukile yosuku ngalunye lapho uhwebo lwenziwa khona. Ngenxa yalokho, sithole itafula elibanzi kakhulu!
Ukuze ulungise lokhu, chofoza kwesokudla kunoma iyiphi idethi bese ukhetha kumenyu yokuqukethwe IQEMBU (Iqembu):
Ibhokisi lebhokisi lengxoxo lizovela. Siyakhetha Izinyanga (Izinyanga) bese uchofoza OK:
Voila! Leli thebula liwusizo kakhulu:
Ngendlela, leli thebula licishe lifane nalelo eliboniswe ekuqaleni kwe-athikili, lapho amanani okuthengisa ahlanganiswe ngesandla.
Kukhona elinye iphuzu elibaluleke kakhulu okudingeka ukwazi! Awukwazi ukudala eyodwa, kodwa amazinga amaningana ezihloko zomugqa (noma ikholomu):
... futhi kuzobukeka kanjena ...
Okufanayo kungenziwa ngezihloko zekholomu (noma ngisho nezihlungi).
Ake sibuyele esimweni sokuqala sethebula futhi sibone ukuthi singabonisa kanjani izilinganiso esikhundleni sezibalo.
Ukuze uqalise, chofoza okuthi Isamba Semali nakumenyu evelayo khetha Izilungiselelo Zenkambu Yenani (Izinketho zenkambu yenani):
Uhlu Fingqa inkambu yenani nge (Ukusebenza) ebhokisini lengxoxo Izilungiselelo Zenkambu Yenani (Izinketho zenkambu yenani) khetha Isilinganiso (Isilinganiso):
Ngesikhathi esifanayo, ngenkathi silapha, asishintshe CustomName (Igama langokwezifiso) nge Isilinganiso senani (Inkambu yenani Inani) kokuthile okufushane. Faka into efana nale kulo mkhakha Avg:
Cindezela OK futhi ubone ukuthi kwenzekani. Qaphela ukuthi wonke amanani ashintshile ukusuka engqikithini kuya kokumaphakathi, futhi unhlokweni wethebula (kuseli elingaphezulu kwesokunxele) ushintshele ekubeni. Avg:
Uma ufuna, ungathola ngokushesha inani, isilinganiso kanye nenombolo (ukuthengisa) ebekwe etafuleni elilodwa le-pivot.
Nansi inkomba yesinyathelo ngesinyathelo yokuthi ungakwenza kanjani lokhu, uqale ngethebula le-pivot elingenalutho:
- Hudula Unhlokweni umuntu othengisayo (omele ukuthengisa) esifundeni Amalebula wekholomu (Amakholomu).
- Hudula isihloko kathathu Inani le (Izindleko) endaweni Izindinganiso (Amanani).
- Ngenkundla yokuqala Inani le shintsha isihloko sibe Ubude (Inani), kanye nefomethi yenombolo kulo mkhakha Accounting (Ezezimali). Inombolo yezindawo zamadesimali inguziro.
- Inkambu yesibili Inani le Igama Isilinganisoe, setha ukusebenza kwayo Isilinganiso (Isilinganiso) kanye nefomethi yenombolo kulo mkhakha nawo ashintshela ku Accounting (Ezezimali) ezinezindawo zamadesimali eziziro.
- Ngenkundla yesithathu Inani le setha isihloko Bala kanye nokuhlinzwa kwakhe - Bala (Ubuningi)
- In the Amalebula wekholomu (Amakholomu) inkambu idalwe ngokuzenzakalelayo Σ Amanani (Σ Values) – idonsele endaweni Amalebula Omugqa (Olayini)
Nakhu esizophetha ngakho:
Inani eliphelele, inani eliyisilinganiso kanye nenani lokuthengisiwe - konke kuthebula le-pivot elilodwa!
Isiphetho
Amathebula e-Pivot ku-Microsoft Excel aqukethe izici eziningi nezilungiselelo. Esihlokweni esincane kangaka, awasondeli ngisho nokuwahlanganisa wonke. Kungathatha incwadi encane noma iwebhusayithi enkulu ukuchaza ngokugcwele konke okunokwenzeka kwamathebula e-pivot. Abafundi abanesibindi nabafuna ukwazi bangaqhubeka nokuhlola amathebula e-pivot. Ukuze wenze lokhu, vele uchofoze kwesokudla cishe kunoma iyiphi ingxenye yethebula le-pivot futhi ubone ukuthi yimiphi imisebenzi nezilungiselelo ezivuliwe. Ku-Ribbon uzothola amathebhu amabili: Amathuluzi e-PivotTable: Izinketho (ukuhlaziya) kanye Design (Umakhi). Ungesabi ukwenza iphutha, ungakwazi njalo ukususa i-PivotTable bese uqala kabusha. Unethuba abasebenzisi besikhathi eside be-DOS neLotus 1-2-3 abangakaze babe nalo.