Nilalaman
Pivot tables ay isa sa pinakamakapangyarihang tool sa Excel. Nagbibigay-daan sa iyo ang mga ito na pag-aralan at pagbubuod ng iba't ibang mga buod ng malalaking halaga ng data sa pamamagitan lamang ng ilang pag-click ng mouse. Sa artikulong ito, makikilala natin ang mga pivot table, mauunawaan kung ano ang mga ito, matutunan kung paano lumikha at i-customize ang mga ito.
Ang artikulong ito ay isinulat gamit ang Excel 2010. Ang konsepto ng PivotTables ay hindi masyadong nagbago sa paglipas ng mga taon, ngunit ang paraan ng paggawa mo sa mga ito ay bahagyang naiiba sa bawat bagong bersyon ng Excel. Kung mayroon kang isang bersyon ng Excel hindi 2010, pagkatapos ay maging handa na ang mga screenshot sa artikulong ito ay mag-iiba mula sa kung ano ang nakikita mo sa iyong screen.
Isang kaunting kasaysayan
Sa mga unang araw ng spreadsheet software, ang Lotus 1-2-3 rule ball. Ang pangingibabaw nito ay kumpleto na kaya ang pagsisikap ng Microsoft na bumuo ng sarili nitong software (Excel) bilang alternatibo sa Lotus ay tila isang pag-aaksaya ng oras. Ngayon fast forward sa 2010! Ang Excel ay nangingibabaw sa mga spreadsheet nang higit pa sa Lotus code na nagawa sa kasaysayan nito, at ang bilang ng mga taong gumagamit pa rin ng Lotus ay malapit sa zero. Paano ito nangyari? Ano ang dahilan ng gayong dramatikong pagbabago ng mga pangyayari?
Tinutukoy ng mga analyst ang dalawang pangunahing salik:
- Una, nagpasya si Lotus na itong bagong-fangled na platform ng GUI na tinatawag na Windows ay isang lumilipas na uso na hindi magtatagal. Tumanggi silang bumuo ng bersyon ng Windows ng Lotus 1-2-3 (ngunit sa loob lamang ng ilang taon), na hinuhulaan na ang bersyon ng DOS ng kanilang software ay ang lahat ng kakailanganin ng mga mamimili. Natural na binuo ng Microsoft ang Excel para sa Windows.
- Pangalawa, ipinakilala ng Microsoft ang isang tool sa Excel na tinatawag na PivotTables na hindi available sa Lotus 1-2-3. Ang mga PivotTables, na eksklusibo sa Excel, ay napatunayang lubhang kapaki-pakinabang na ang mga tao ay may posibilidad na manatili sa bagong Excel software suite sa halip na magpatuloy sa Lotus 1-2-3, na wala sa kanila.
Ang PivotTables, kasama ang pagmamaliit sa tagumpay ng Windows sa pangkalahatan, ay naglaro ng death march para sa Lotus 1-2-3 at nag-udyok sa tagumpay ng Microsoft Excel.
Ano ang mga pivot table?
Kaya, ano ang pinakamahusay na paraan upang makilala kung ano ang mga PivotTables?
Sa simpleng mga termino, ang mga pivot table ay mga buod ng ilang data, na ginawa upang mapadali ang pagsusuri ng data na ito. Hindi tulad ng mga manu-manong ginawang kabuuan, ang Excel PivotTables ay interactive. Kapag nagawa na, madali mong mababago ang mga ito kung hindi nila ibibigay ang larawang iyong inaasahan. Sa ilang pag-click lang ng mouse, maaaring i-flip ang mga kabuuan upang ang mga heading ng column ay maging row heading at vice versa. Magagawa mo ang maraming bagay gamit ang mga pivot table. Sa halip na subukang ilarawan sa mga salita ang lahat ng mga tampok ng mga pivot table, mas madaling ipakita ito sa pagsasanay ...
Ang data na sinusuri mo gamit ang PivotTables ay hindi maaaring random. Ito ay dapat na raw raw data, tulad ng isang listahan ng ilang uri. Halimbawa, maaaring ito ay isang listahan ng mga benta na ginawa ng kumpanya sa nakalipas na anim na buwan.
Tingnan ang data na ipinapakita sa figure sa ibaba:
Tandaan na ito ay hindi raw raw data, dahil ito ay nai-summarized na. Sa cell B3 nakikita natin ang $30000, na marahil ang kabuuang resulta na ginawa ni James Cook noong Enero. Nasaan ang orihinal na data kung gayon? Saan nagmula ang halagang $30000? Nasaan ang orihinal na listahan ng mga benta kung saan nakuha ang buwanang kabuuang ito? Malinaw na ang isang tao ay gumawa ng isang mahusay na trabaho sa pag-aayos at pag-uuri ng lahat ng data ng mga benta para sa huling anim na buwan at ginagawa ito sa talahanayan ng mga kabuuan na nakikita natin. Gaano katagal ito sa tingin mo? Oras? Alas diyes?
Ang katotohanan ay ang talahanayan sa itaas ay hindi isang pivot table. Ginawa ito ng kamay mula sa hilaw na data na nakaimbak sa ibang lugar at tumagal ng hindi bababa sa ilang oras upang maproseso. Ang gayong talahanayan ng buod ay maaaring gawin gamit ang mga pivot table sa loob lamang ng ilang segundo. Alamin natin kung paano…
Kung babalik tayo sa orihinal na listahan ng mga benta, magiging ganito ang hitsura:
Maaaring magulat ka na mula sa listahan ng mga trade na ito sa tulong ng mga pivot table at sa ilang segundo lang, makakagawa kami ng buwanang ulat ng benta sa Excel, na sinuri namin sa itaas. Oo, magagawa natin iyon at higit pa!
Paano gumawa ng pivot table?
Una, tiyaking mayroon kang ilang source data sa isang Excel sheet. Ang listahan ng mga transaksyon sa pananalapi ay ang pinakakaraniwang nangyayari. Sa katunayan, maaaring ito ay isang listahan ng anumang bagay: mga detalye sa pakikipag-ugnayan ng empleyado, isang koleksyon ng CD, o data ng pagkonsumo ng gasolina ng iyong kumpanya.
Kaya, sinisimulan namin ang Excel ... at nag-load ng ganoong listahan ...
Pagkatapos naming buksan ang listahang ito sa Excel, maaari na naming simulan ang paggawa ng pivot table.
Pumili ng anumang cell mula sa listahang ito:
Pagkatapos ay sa tab pagpapasok (Ipasok) piliin ang utos PivotTable (Pivot table):
May lalabas na dialog box Lumikha ng PivotTable (Paggawa ng pivot table) na may dalawang tanong para sa iyo:
- Anong data ang gagamitin para gumawa ng bagong pivot table?
- Saan ilalagay ang pivot table?
Dahil sa nakaraang hakbang ay napili na namin ang isa sa mga cell ng listahan, ang buong listahan ay awtomatikong pipiliin upang lumikha ng pivot table. Tandaan na maaari tayong pumili ng ibang range, ibang table, at kahit ilang external na data source gaya ng Access o MS-SQL database table. Bilang karagdagan, kailangan nating pumili kung saan ilalagay ang bagong pivot table: sa isang bagong sheet o sa isa sa mga umiiral na. Sa halimbawang ito, pipiliin namin ang opsyon - Bagong Worksheet (sa isang bagong sheet):
Gagawa ang Excel ng bagong sheet at maglalagay ng walang laman na pivot table dito:
Sa sandaling mag-click kami sa anumang cell sa pivot table, lalabas ang isa pang dialog box: Listahan ng Field ng PivotTable (Mga field ng pivot table).
Ang listahan ng mga field sa tuktok ng dialog box ay isang listahan ng lahat ng mga pamagat mula sa orihinal na listahan. Ang apat na walang laman na lugar sa ibaba ng screen ay nagbibigay-daan sa iyong sabihin sa PivotTable kung paano mo gustong i-summarize ang data. Hangga't walang laman ang mga lugar na ito, wala rin sa mesa. Ang kailangan lang nating gawin ay i-drag ang mga heading mula sa tuktok na lugar patungo sa mga walang laman na lugar sa ibaba. Kasabay nito, awtomatikong nabubuo ang pivot table, alinsunod sa aming mga tagubilin. Kung magkamali tayo, maaari nating alisin ang mga heading sa ibabang bahagi o i-drag ang iba upang palitan ang mga ito.
Pook Halaga ng (Kahulugan) ay marahil ang pinakamahalaga sa apat. Aling heading ang nakalagay sa lugar na ito ang tumutukoy kung anong data ang ibubuod (sum, average, maximum, minimum, atbp.) Ito ay halos palaging mga numerical na halaga. Ang isang mahusay na kandidato para sa isang lugar sa lugar na ito ay ang data sa ilalim ng heading dami (Gastos) ng aming orihinal na talahanayan. I-drag ang pamagat na ito sa lugar Halaga ng (Mga Halaga):
Mangyaring tandaan na ang pamagat dami ay minarkahan na ngayon ng checkmark, at sa lugar Halaga ng (Values) may lumabas na entry Kabuuan ng Halaga (Halaga ng field na Halaga), na nagpapahiwatig na ang column dami summed up.
Kung titingnan natin ang pivot table mismo, makikita natin ang kabuuan ng lahat ng value mula sa column dami orihinal na talahanayan.
Kaya, ang aming unang pivot table ay nilikha! Maginhawa, ngunit hindi partikular na kahanga-hanga. Marahil ay gusto naming makakuha ng higit pang impormasyon tungkol sa aming data kaysa sa kasalukuyang mayroon kami.
Bumaling tayo sa orihinal na data at subukang tukuyin ang isa o higit pang mga column na maaaring gamitin upang hatiin ang kabuuan na ito. Halimbawa, maaari naming bumuo ng aming pivot table sa paraang ang kabuuang halaga ng mga benta ay kalkulahin para sa bawat nagbebenta nang paisa-isa. Yung. idadagdag ang mga row sa aming pivot table na may pangalan ng bawat salesperson sa kumpanya at ang kabuuang halaga ng kanilang benta. Upang makamit ang resultang ito, i-drag lamang ang pamagat taong nagbebenta (sales representative) sa rehiyon Mga Label ng Hilera (Mga String):
Ito ay nagiging mas kawili-wili! Nagsisimula nang magkaroon ng hugis ang aming PivotTable...
Tingnan ang mga benepisyo? Sa ilang mga pag-click, gumawa kami ng isang talahanayan na magtatagal nang manu-manong gumawa.
Ano pa ang magagawa natin? Well, sa isang kahulugan, handa na ang aming pivot table. Gumawa kami ng kapaki-pakinabang na buod ng orihinal na data. Mahalagang impormasyon na natanggap na! Sa natitirang bahagi ng artikulong ito, titingnan natin ang ilang mga paraan upang lumikha ng mas kumplikadong Mga PivotTables, pati na rin matutunan kung paano i-customize ang mga ito.
Setup ng PivotTable
Una, maaari tayong lumikha ng two-dimensional na pivot table. Gawin natin ito gamit ang heading ng column Paraan ng Pagbayad (Paraan ng Pagbayad). I-drag lang ang pamagat Paraan ng Pagbayad sa lugar Mga Label ng Haligi (Mga Hanay):
Nakukuha namin ang resulta:
Mukhang napaka-cool!
Ngayon gumawa tayo ng tatlong-dimensional na talahanayan. Ano ang magiging hitsura ng gayong mesa? Tingnan natin…
I-drag ang Header pakete (Complex) sa lugar mga filter ng ulat (Mga Filter):
Pansinin kung nasaan siya...
Nagbibigay ito sa amin ng pagkakataong i-filter ang ulat batay sa "Aling holiday complex ang binayaran." Halimbawa, makakakita tayo ng breakdown ng mga nagbebenta at ng mga paraan ng pagbabayad para sa lahat ng complex, o sa ilang pag-click ng mouse, baguhin ang view ng pivot table at ipakita ang parehong breakdown para lang sa mga nag-order ng complex Sunseekers.
Kaya, kung naiintindihan mo ito nang tama, kung gayon ang aming pivot table ay maaaring tawaging three-dimensional. Ipagpatuloy natin ang pag-set up…
Kung biglang lumabas na ang pagbabayad lamang sa pamamagitan ng tseke at credit card (iyon ay, cashless na pagbabayad) ang dapat ipakita sa pivot table, maaari nating i-off ang pagpapakita ng pamagat Cash (Cash). Para dito, sa tabi Mga Label ng Haligi i-click ang pababang arrow at alisan ng check ang kahon sa drop-down na menu Cash:
Tingnan natin kung ano ang hitsura ng aming pivot table ngayon. Tulad ng nakikita mo, ang hanay Cash nawala sa kanya.
Pag-format ng PivotTables sa Excel
Ang mga PivotTables ay malinaw na isang napakalakas na tool, ngunit sa ngayon ang mga resulta ay mukhang medyo simple at nakakainip. Halimbawa, ang mga numero na aming pinagsama-sama ay hindi mukhang mga halaga ng dolyar - ang mga ito ay mga numero lamang. Ayusin natin ito.
Nakatutukso na gawin ang nakasanayan mo sa ganoong sitwasyon at piliin lamang ang buong talahanayan (o ang buong sheet) at gamitin ang karaniwang mga pindutan sa pag-format ng numero sa toolbar upang itakda ang nais na format. Ang problema sa diskarteng ito ay kung babaguhin mo ang istraktura ng pivot table sa hinaharap (na mangyayari na may 99% na pagkakataon), mawawala ang pag-format. Ang kailangan natin ay isang paraan upang ito (halos) maging permanente.
Una, hanapin natin ang entry Kabuuan ng Halaga in Halaga ng (Mga Halaga) at i-click ito. Sa lalabas na menu, piliin ang item Mga Setting ng Value Field (Mga pagpipilian sa field ng halaga):
May lalabas na dialog box Mga Setting ng Value Field (Mga pagpipilian sa field ng halaga).
I-click ang pindutan Format ng Numero (Number Format), magbubukas ang isang dialog box. Mga Format ng Cell (format ng cell):
Mula sa listahan kategorya (Mga format ng numero) piliin Accounting (Financial) at itakda ang bilang ng mga decimal na lugar sa zero. Ngayon pindutin ng ilang beses OKupang bumalik sa aming pivot table.
Tulad ng nakikita mo, ang mga numero ay naka-format bilang mga halaga ng dolyar.
Habang tayo ay nasa pag-format, i-set up natin ang format para sa buong PivotTable. Mayroong ilang mga paraan upang gawin ito. Ginagamit namin ang isa na mas simple ...
I-click ang Mga Tool sa PivotTable: Disenyo (Paggawa gamit ang PivotTables: Constructor):
Susunod, palawakin ang menu sa pamamagitan ng pag-click sa arrow sa kanang sulok sa ibaba ng seksyon Mga Estilo ng PivotTable (PivotTable Styles) upang makita ang malawak na koleksyon ng mga inline na istilo:
Pumili ng anumang angkop na istilo at tingnan ang resulta sa iyong pivot table:
Iba pang Mga Setting ng PivotTable sa Excel
Minsan kailangan mong i-filter ang data ayon sa mga petsa. Halimbawa, sa aming listahan ng mga kalakalan mayroong marami, maraming mga petsa. Ang Excel ay nagbibigay ng tool sa pagpapangkat ng data ayon sa araw, buwan, taon, at iba pa. Tingnan natin kung paano ito ginawa.
Alisin muna ang entry. Paraan ng Pagbayad mula sa rehiyon Mga Label ng Haligi (Mga Hanay). Upang gawin ito, i-drag ito pabalik sa listahan ng mga pamagat, at sa lugar nito, ilipat ang pamagat Petsa ng Pag-book (petsa ng booking):
Gaya ng nakikita mo, pansamantalang ginawa nitong inutil ang aming pivot table. Gumawa ang Excel ng hiwalay na column para sa bawat petsa kung saan ginawa ang isang trade. Bilang resulta, nakakuha kami ng napakalawak na mesa!
Upang ayusin ito, mag-right-click sa anumang petsa at pumili mula sa menu ng konteksto GROUP (Pangkat):
Lalabas ang dialog box ng pagpapangkat. Pumili tayo Buwan (Mga buwan) at i-click OK:
Voila! Ang talahanayang ito ay mas kapaki-pakinabang:
Sa pamamagitan ng paraan, ang talahanayang ito ay halos magkapareho sa ipinakita sa simula ng artikulo, kung saan ang mga kabuuan ng mga benta ay manu-manong pinagsama-sama.
May isa pang napakahalagang punto na kailangan mong malaman! Maaari kang lumikha ng hindi isa, ngunit ilang antas ng mga heading ng row (o column):
… at magiging ganito…
Ang parehong ay maaaring gawin sa mga heading ng column (o kahit na mga filter).
Bumalik tayo sa orihinal na anyo ng talahanayan at tingnan kung paano ipakita ang mga average sa halip na mga kabuuan.
Upang makapagsimula, mag-click sa Kabuuan ng Halaga at mula sa menu na lilitaw piliin Mga Setting ng Value Field (Mga pagpipilian sa field ng halaga):
Ang listahan Ibuod ang field ng halaga ayon sa (Operasyon) sa dialog box Mga Setting ng Value Field (Mga pagpipilian sa field ng halaga) piliin karaniwan (Karaniwan):
At the same time, habang nandito tayo, magpalit tayo CustomName (Pasadyang pangalan) na may Average ng Halaga (Halaga ng field na Halaga) sa isang bagay na mas maikli. Ipasok sa field na ito ang isang bagay tulad ng Avg:
pindutin OK at tingnan kung ano ang mangyayari. Tandaan na ang lahat ng mga halaga ay nagbago mula sa mga kabuuan hanggang sa mga average, at ang header ng talahanayan (sa itaas na kaliwang cell) ay nagbago sa Avg:
Kung gusto mo, maaari mong makuha agad ang halaga, average at numero (benta) na nakalagay sa isang pivot table.
Narito ang isang hakbang-hakbang na gabay sa kung paano ito gawin, simula sa isang walang laman na pivot table:
- I-drag ang Header taong nagbebenta (sales representative) sa rehiyon Mga Label ng Haligi (Mga Hanay).
- I-drag ang pamagat ng tatlong beses dami (Gastos) sa lugar Halaga ng (Mga halaga).
- Para sa unang larangan dami baguhin ang pamagat sa total (Halaga), at ang format ng numero sa field na ito ay Accounting (Pananalapi). Ang bilang ng mga decimal na lugar ay zero.
- Pangalawang larangan dami pangalan Averagee, itakda ang operasyon para dito karaniwan (Karaniwan) at ang format ng numero sa field na ito ay nagbabago rin sa Accounting (Pananalapi) na may zero decimal na lugar.
- Para sa ikatlong larangan dami magtakda ng pamagat Bilangin at isang operasyon para sa kanya - Bilangin (Dami)
- Sa Mga Label ng Haligi Awtomatikong nilikha ang field (Mga Column). Σ Mga halaga (Σ Values) – i-drag ito sa lugar Mga Label ng Hilera (Mga Linya)
Narito ang ating hahantong:
Kabuuang halaga, average na halaga at bilang ng mga benta – lahat sa isang pivot table!
Konklusyon
Ang mga pivot table sa Microsoft Excel ay naglalaman ng maraming feature at setting. Sa ganoong kaliit na artikulo, hindi man lang sila malapit na masakop ang lahat. Mangangailangan ng isang maliit na libro o isang malaking website upang ganap na mailarawan ang lahat ng mga posibilidad ng mga pivot table. Maaaring ipagpatuloy ng mga matatapang at matanong na mambabasa ang kanilang paggalugad ng mga pivot table. Upang gawin ito, i-right-click lamang sa halos anumang elemento ng pivot table at tingnan kung anong mga function at setting ang bukas. Sa Ribbon makikita mo ang dalawang tab: Mga Tool sa PivotTable: Mga Opsyon (pagsusuri) at Disenyo (Konstruktor). Huwag matakot na magkamali, maaari mong tanggalin ang PivotTable anumang oras at magsimulang muli. Mayroon kang pagkakataong hindi kailanman naranasan ng mga matagal nang gumagamit ng DOS at Lotus 1-2-3.