Paano bumuo ng isang pivot table sa Excel. Paano magtrabaho sa data

Ang MS Excel Pivot Table ay isang mahusay na tool sa pagsusuri ng data. Binibigyang-daan ka ng tool na ito na kumuha ng impormasyon mula sa isang malaking hanay ng data, sa anumang konteksto, sa ilang mga pag-click lamang ng mouse. Sa artikulong ito susubukan kong ipaliwanag sa simple at nauunawaan na wika, hakbang-hakbang, kung ano at kung paano gawin upang makuha ang lubos na maginhawang tool ng MS Excel na iyong itapon.

Mga kinakailangan para sa source data.

Kaya, ang batayan ng anumang pivot table ay isang wastong itinayong hanay ng data - ang "tamang talahanayan". Sa figure sa ibaba makikita mo ang isang halimbawa ng isang wastong nabuong array ng data:

Tingnan natin kung ano, sa katunayan, ang "katumpakan" ng talahanayang ito? Ang tama ay:

• Ang bawat column ay naglalaman lamang ng parehong uri ng data, ang column A Mga Petsa lamang, sa column SA Mga Dokumento lamang, sa column SA Mga Customer lamang, Pera sa Pera, Mga Supplier sa Mga Supplier, Mga Kategorya ng Produkto sa Mga Kategorya at iba pa... Sa ganoong talahanayan ay napakadali nating magagamit Salain;
• Ang mga column na may parehong uri ng data ay hindi inuulit;
• Walang kabuuang mga hilera sa talahanayan, tanging "malinis na data";
• Walang mga walang laman na cell sa data ng text sa talahanayan; ang bawat linya ay naglalaman ng pangalan ng Kliyente, Produkto, Supplier, Tagapamahala, at iba pa...

Sa pamamagitan ng paraan, sa isang array, ito ay hindi mahalaga sa lahat sa kung ano ang pagkakasunod-sunod ay matatagpuan ang mga haligi at sa pamamagitan ng kung anong field ang data ay pinagsunod-sunod. Hindi ito makakaapekto sa pagbuo ng Pivot Table sa anumang paraan.

At narito ang isang halimbawa ng isang "maling talahanayan", kung saan, kahit na pumutok ka, ang Pivot Table ay hindi itatayo, at kahit na ito ay binuo, ito ay ganap na imposible na gumana sa data... Ang aming " paboritong" accounting system ay karaniwang "pakiusap" sa amin ng mga ganoong talahanayan, na nagbibigay sa amin ng mga ito sa anyo ng mga ulat na ganap na hindi angkop para sa karagdagang pagsusuri:

Narito ang isa pang halimbawa ng "Invalid Array":

Mayroong isang buong grupo ng "mga iregularidad" dito:

• Una, ang mga column na "Supplier" at "Kategorya" ay may mga walang laman na cell, kaya hindi namin magagamit ang filter;
• Pangalawa, sa halos bawat hanay ay may mga linyang "Kabuuan...", sila ay ganap na hindi kailangan para sa pagbuo ng Pivot Table, bukod dito, sila ay makakasagabal lamang;
• Pangatlo, hindi rin kailangan ang column na "Grand Total";
• Pang-apat, ang parehong uri ng data, katulad ng "Pera", ay nasa kasing dami ng tatlong column: "Ene", "Peb" at "Mar", na makabuluhang magpapalubha sa pagbuo ng Pivot Table, na nangangahulugang kailangan nating gumawa ng isang bagay dito...

Ngunit ito ay para sa isang hiwalay na pag-uusap, kung gusto mong matutunan kung paano mabilis na i-convert ang mga "baluktot na talahanayan" sa "tamang mga array", basahin ang mga artikulo: "Paano mabilis na bumuo ng isang pivot table mula sa isang 1C o SAP na ulat?" at "Paano mabilis na i-convert ang isang talahanayan sa isang array para sa isang pivot table?"

Sa totoo lang, ang pagbuo ng Pivot Table:

Kunin ang "tamang array", ilagay ang cursor sa anumang cell ng array, piliin ang tab na "Insert" sa pangunahing menu, sa kaliwang sulok, sa seksyong "Mga Talahanayan", i-click ang pindutang "Pivot Table":

Sa dialog box na "Gumawa ng Pivot Table" na bubukas, i-click ang "OK":

Ang MS Excel ay lilikha ng isang bagong sheet kung saan ipahiwatig nito ang lugar kung saan ilalagay ang Pivot Table, at sa kanan ay magpapakita ito ng isang window para sa pag-set up ng mga field ng Pivot Table, kung saan makikita mo ang lahat ng mga pangalan ng mga column ng iyong array:

Simulan natin ang pag-set up ng Pivot Table. Anong uri ng hiwa ang gusto natin? Mga kategorya ng produkto ayon sa Manager - mangyaring. Ilipat ang mouse sa field na "Kategorya", i-click ito gamit ang kaliwang pindutan ng mouse at i-drag ito sa field na "ROWS". Ang field na "Manager" ay dina-drag sa "COLUMNS", at ang "Amount" ay i-drag sa "VALUES":

Ang mga pivot table ay isa sa mga pinakaepektibong tool sa MS Excel. Sa tulong nila, maaari mong baguhin ang isang milyong row ng data sa isang maigsi na ulat sa ilang segundo. Bilang karagdagan sa mabilis na pagbubuod ng mga resulta, ang mga pivot table ay nagbibigay-daan sa iyo na literal na baguhin ang paraan ng iyong pagsusuri sa mabilisang pag-drag sa mga field mula sa isang lugar ng ulat patungo sa isa pa.

Ang mga pivot table ay isa rin sa mga pinaka-underrated na tool sa Excel. Karamihan sa mga gumagamit ay hindi alam ang mga posibilidad na nasa kanilang mga kamay. Isipin natin na ang mga pivot table ay hindi pa naiimbento. Nagtatrabaho ka sa isang kumpanyang nagbebenta ng mga produkto nito sa iba't ibang customer. Para sa pagiging simple, mayroon lamang 4 na item sa assortment. Ang mga produkto ay regular na binili ng ilang dosenang mga kliyente na matatagpuan sa iba't ibang mga rehiyon. Ang bawat transaksyon ay ipinasok sa database at kumakatawan sa isang hiwalay na linya.

Inutusan ka ng iyong direktor na gumawa ng isang maikling ulat sa mga benta ng lahat ng mga kalakal ayon sa rehiyon (rehiyon). Ang problema ay maaaring malutas tulad ng sumusunod.

Una, gumawa tayo ng layout ng talahanayan, iyon ay, isang header na binubuo ng mga natatanging halaga para sa mga produkto at rehiyon. Gumawa tayo ng kopya ng column ng produkto at alisin ang mga duplicate. Pagkatapos, gamit ang insert special, inililipat namin ang column sa isang row. Ganoon din ang ginagawa namin sa mga rehiyon, nang walang transposing. Matatanggap namin ang header ng ulat.

Ang plato na ito ay dapat punan, i.e. ibuod ang kita para sa mga nauugnay na produkto at rehiyon. Ito ay madaling gawin gamit ang SUMIFS function. Idagdag din natin ang mga resulta. Makakakuha ka ng buod ng ulat sa mga benta ayon sa rehiyon at produkto.

Nakumpleto mo na ang gawain at ipakita ang ulat sa direktor. Sa pagtingin sa talahanayan, bumubuo siya ng maraming magagandang ideya nang sabay-sabay.

— Posible bang gumawa ng isang ulat hindi sa kita, ngunit sa kita?

— Posible bang magpakita ng mga produkto ayon sa mga hilera at rehiyon ayon sa mga hanay?

— Posible bang gumawa ng ganoong mga talahanayan para sa bawat manager nang hiwalay?

Kahit na ikaw ay isang bihasang gumagamit ng Excel, ang paggawa ng mga bagong ulat ay aabutin ng maraming oras. Hindi ito banggitin ang mga posibleng pagkakamali. Gayunpaman, kung alam mo ang mga pivot table, ang sagot ay: oo, kailangan ko ng 5 minuto, marahil mas kaunti.

Narito kung paano ito ginawa. Buksan ang source data. Ang isang pivot table ay maaaring buuin gamit ang isang regular na hanay, ngunit ito ay magiging mas tama upang baguhin ito. Agad nitong malulutas ang isyu ng awtomatikong pagkuha ng bagong data. Pumili ng anumang cell at pumunta sa tab Ipasok. Sa kaliwang bahagi ng laso mayroong dalawang mga pindutan: at Inirerekomendang PivotTables.

Kung hindi mo alam kung paano ayusin ang umiiral na data, maaari mong gamitin ang command Inirerekomendang PivotTables. Magpapakita ang Excel ng mga thumbnail ng mga posibleng layout batay sa iyong data.

Mag-click sa naaangkop na opsyon at handa na ang pivot table. Ang natitira na lang ay isaisip ito, dahil... Ito ay malamang na ang isang karaniwang paghahanda ay ganap na magkakasabay sa iyong mga hinahangad. Kung kailangan mong bumuo ng pivot table mula sa simula, o mayroon kang lumang bersyon ng program, pagkatapos ay i-click ang button. Lilitaw ang isang window kung saan kailangan mong tukuyin ang source range (kung i-activate mo ang anumang Excel table cell, ito mismo ang tutukuyin) at ang lokasyon ng hinaharap na pivot table (isang bagong sheet ang pipiliin bilang default).

Kadalasan ay hindi na kailangang baguhin ang anuman dito. Pagkatapos i-click ang OK, gagawa ng bagong Excel sheet na may blangkong layout ng PivotTable.

Bago lumipat sa mga setting, kilalanin natin ang interface at mga pangunahing konsepto. Ang layout ng talahanayan ay nako-customize sa panel Mga field ng pivot table, na matatagpuan sa kanang bahagi ng sheet.

Sa tuktok ng panel mayroong isang listahan ng lahat ng magagamit na mga field, iyon ay, mga column sa source data. Kung kailangan mong magdagdag ng bagong patlang sa layout, maaari mong lagyan ng tsek ang kahon sa tabi nito - Ang Excel mismo ang tutukoy kung saan dapat ilagay ang patlang na ito. Gayunpaman, hindi ito palaging tama, kaya mas mahusay na i-drag ang mouse sa nais na lugar sa layout. Maaari ding tanggalin ang mga field sa pamamagitan ng pag-alis ng check o pag-drag sa mga ito pabalik.

Ang pivot table ay binubuo ng 4 na lugar, na matatagpuan sa ibaba ng panel: mga value, row, column, filter. Tingnan natin ang kanilang layunin.

Saklaw ng mga halaga– ito ang gitnang bahagi ng pivot table na may mga value na nakuha sa pamamagitan ng pagsasama-sama ng source data gamit ang napiling paraan.
Sa karamihan ng mga kaso, ang pagsasama-sama ay nangyayari sa pamamagitan ng Pagsusuma. Kung ang lahat ng data sa napiling field ay nasa numeric na format, magtatalaga ang Excel ng default na kabuuan. Kung ang source data ay naglalaman ng kahit isang text o walang laman na cell, sa halip na kabuuan ay kakalkulahin ito Dami mga selula. Sa aming halimbawa, ang bawat cell ay ang kabuuan ng lahat ng kaukulang produkto sa kaukulang rehiyon.

Maaari kang gumamit ng iba pang mga paraan ng pagkalkula sa mga cell ng PivotTable. Mayroong humigit-kumulang 20 uri (average, minimum na halaga, proporsyon, atbp.). Mayroong ilang mga paraan upang baguhin ang paraan ng pagkalkula. Ang pinakasimpleng ay ang pag-right-click sa anumang cell ng gustong field sa pivot table mismo at pumili ng ibang paraan ng pagsasama-sama.

Lugar ng linya– ang mga pangalan ng mga linya na matatagpuan sa pinakakaliwang column. Ito ang lahat ng mga natatanging halaga ng napiling field (column). Maaaring may ilang field sa row area, pagkatapos ay magiging multi-level ang table. Karaniwang inilalagay dito ang mga qualitative variable tulad ng mga pangalan ng produkto, buwan, rehiyon, atbp.

Lugar ng mga hanay– katulad ng mga hilera, ipinapakita ang mga natatanging halaga ng napiling field, sa mga hanay lamang. Ang mga pamagat ng column ay karaniwang isang tagapagpahiwatig ng husay. Halimbawa, mga taon at buwan, mga pangkat ng produkto.

I-filter ang lugar– ginamit, gaya ng ipinahihiwatig ng pangalan, para sa pag-filter. Halimbawa, ang ulat mismo ay nagpapakita ng mga produkto ayon sa rehiyon. Kailangan mong limitahan ang iyong pivot table sa isang partikular na industriya, panahon, o manager. Pagkatapos ay inilalagay ang isang field ng filter sa lugar ng filter at ang nais na halaga ay pipiliin mula sa drop-down na listahan.

Sa pamamagitan ng pagdaragdag at pag-alis ng mga field sa mga tinukoy na lugar, maaari mong i-customize ang anumang slice ng iyong data na gusto mo sa ilang segundo.

Tingnan natin kung paano ito gumagana sa aksyon. Sa ngayon, gumawa tayo ng parehong talahanayan tulad ng ginawa na gamit ang SUMIFS function. Upang gawin ito, i-drag sa lugar Mga halaga field na "Kita", sa lugar Mga string i-drag ang field na "Rehiyon" (rehiyon ng pagbebenta) sa Mga hanay- "Produkto".

Bilang resulta, nakakakuha kami ng totoong pivot table.

Literal na tumagal ng 5-10 segundo upang mabuo ito. Madali din ang pagbabago ng isang umiiral nang pivot table. Tingnan natin kung paano madaling naisalin sa realidad ang kagustuhan ng direktor.

Palitan natin ng tubo ang kita.

Ang mga produkto at lugar ay maaari ding palitan sa pamamagitan ng pag-drag ng mouse.

Mayroong ilang mga tool na magagamit para sa pag-filter ng mga pivot table. Sa kasong ito, ilalagay lang namin ang field na "Manager" sa lugar ng filter.

Inabot ng ilang segundo ang lahat. Narito kung gaano talaga kadaling magtrabaho sa Pivot Tables. Siyempre, hindi lahat ng gawain ay napakaliit. Mayroon ding mga kaso kung saan kinakailangang gumamit ng mas sopistikadong paraan ng pagsasama-sama, pagdaragdag ng mga kalkuladong field, conditional formatting, atbp. Ngunit ito ay isang mas advanced na paggamit ng mga pivot table.

Paunang data

Upang matagumpay na gumana sa mga pivot table, dapat matugunan ng source data ang ilang kinakailangan. Ang isang paunang kinakailangan ay ang pagkakaroon ng mga pangalan sa itaas ng bawat field (column) kung saan matutukoy ang mga field na ito. Ngayon ang ilang mga kapaki-pakinabang na tip.

1. Ang pinakamahusay na format para sa data ay isang Excel Spreadsheet. Maganda ito dahil may pangalan ang bawat field at kapag nagdagdag ng mga bagong row, awtomatiko silang isasama sa pivot table.

2. Iwasan ang paulit-ulit na mga pangkat sa mga hanay. Halimbawa, ang lahat ng petsa ay dapat nasa isang field, at hindi hinati, halimbawa, ayon sa buwan sa magkahiwalay na column.

3. Alisin ang mga puwang at walang laman na mga cell, kung hindi, ang row na ito ay maaaring mawala sa pagsusuri.

4. Ilapat ang wastong pag-format sa mga field. Ang mga numero ay dapat nasa numeric na format, ang mga petsa ay dapat na mga petsa. Kung hindi, lilitaw ang mga problema sa panahon ng pagpapangkat at pagproseso ng matematika. Ngunit tutulungan ka ng Excel dito, dahil... Tinutukoy nito ang format ng data nang maayos.

Sa pangkalahatan, kakaunti ang mga kinakailangan, ngunit dapat mong malaman ang mga ito.

Kung gagawa ka ng mga pagbabago sa pinagmulan (halimbawa, pagdaragdag ng mga bagong row), hindi magbabago ang pivot table hanggang sa i-refresh mo ito gamit ang kanang pindutan ng mouse

o
sa pamamagitan ng utos sa tab Data – I-refresh Lahat.

Sinadya itong ginawa dahil ang pivot table ay kumukuha ng maraming espasyo sa RAM. Samakatuwid, upang magamit ang mga mapagkukunan ng computer nang mas matipid, ang trabaho ay ginagawa hindi direkta sa pinagmulan, ngunit sa cache, kung saan matatagpuan ang isang snapshot ng source data.

Gamit ang mga talahanayan ng pivot kahit na sa pangunahing antas na ito, maaari mong makabuluhang taasan ang bilis at kalidad ng pagproseso ng malalaking halaga ng data. Kung may nananatiling hindi malinaw, sumulat sa mga komento.

Mga pivot table ay isa sa pinakamakapangyarihang tool sa Excel. Nagbibigay-daan sa iyo ang mga ito na suriin at ibuod ang iba't ibang resulta para sa malalaking dami ng data sa ilang pag-click lang. Sa artikulong ito, makikilala natin ang mga pivot table, mauunawaan kung ano ang mga ito, matutunan kung paano lumikha at i-configure ang mga ito.

Noong isinusulat ko ang artikulong ito, ginamit ko ang Excel 2010. Ang konsepto ng mga pivot table ay nanatiling hindi nagbabago sa loob ng maraming taon, ngunit ang paraan ng paglikha ng mga ito ay bahagyang naiiba sa bawat bagong bersyon ng Excel. Kung mayroon kang bersyon ng Excel maliban sa 2010, pagkatapos ay maging handa na ang mga screenshot sa artikulong ito ay mag-iiba sa nakikita mo sa iyong screen.

Isang maliit na kasaysayan

Sa madaling araw ng pagbuo ng mga programa para sa paglikha ng mga spreadsheet, ang mga patakaran ng Lotus ay 1-2-3. 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 industriya ng spreadsheet nang higit pa kaysa sa nagawa ng Lotus sa kasaysayan nito, at ang bilang ng mga taong gumagamit pa rin ng Lotus ay papalapit na 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 isa lamang lumilipas na uso na hindi magtatagal. Tumanggi silang lumikha 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 ang tanging kakailanganin ng mga mamimili. Siyempre, binuo ng Microsoft ang Excel na partikular para sa Windows.
• Pangalawa, bumuo ang Microsoft ng tool sa Excel na tinatawag na mga pivot table, na hindi available sa Lotus 1-2-3. Ang mga pivot table, isang eksklusibong Excel, ay lubhang kapaki-pakinabang na ang mga tao ay hilig na matutunan ang bagong software ng Excel sa halip na manatili sa Lotus 1-2-3, na wala sa kanila.

Ang mga pivot table, kasama ang pagmamaliit ng tagumpay ng Windows sa pangkalahatan, ay naglaro ng death march para sa Lotus 1-2-3 at minarkahan ang simula ng tagumpay ng Microsoft Excel.

Ano ang Pivot Tables?

Kaya ano ang pinakamahusay na paraan upang ilarawan kung ano ang mga pivot table?

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 inaasahan mong makuha. Sa ilang pag-click lang, maaaring i-flip ang mga kabuuan upang ang mga heading ng column ay maging row heading at vice versa. Mayroong maraming iba't ibang mga bagay na maaari mong gawin sa mga pivot table. Sa halip na subukang ilarawan sa mga salita ang lahat ng mga kakayahan ng mga pivot table, mas madaling ipakita ito sa pagsasanay...

Ang data na sinusuri mo gamit ang PivotTables ay hindi maaaring maging anumang uri ng data. Ito ay dapat na raw data, tulad ng isang listahan ng ilang uri. Halimbawa, ito ay maaaring isang listahan ng mga nakumpletong benta sa kumpanya sa nakalipas na anim na buwan.

Tingnan ang data na ipinapakita sa figure sa ibaba:

Pakitandaan na hindi raw data ang mga ito dahil nai-summarized na ang mga ito. Sa cell B3 nakikita natin ang $30,000, na malamang na ang kabuuang ginawa ni James Cook noong Enero. Nasaan ang pinagmulan ng data? Saan nagmula ang halagang $30,000? Nasaan ang orihinal na listahan ng mga benta kung saan nakuha ang buwanang kabuuang ito? Malinaw na may isang taong nagsumikap sa pag-aayos at pag-uri-uriin ang lahat ng data ng mga benta para sa huling anim na buwan at gawin itong talahanayan ng buod na nakikita natin. Gaano katagal ito sa tingin mo? Oras? Alas diyes?

Ang katotohanan ay ang talahanayan sa itaas ay hindi isang talahanayan ng buod. Ito ay ginawa nang manu-mano mula sa raw data na nakaimbak sa ibang lugar at tumagal ng hindi bababa sa ilang oras upang maproseso. Ito ang eksaktong uri ng talahanayan ng buod na maaaring gawin gamit ang mga pivot table, na gumugugol lamang ng ilang segundo dito. Alamin natin kung paano...

Kung babalik tayo sa orihinal na listahan ng mga benta, magiging ganito ang hitsura:

Maaaring magulat ka na mula sa listahang ito ng mga transaksyon sa pangangalakal, gamit ang mga pivot table at sa loob lamang ng ilang segundo, makakagawa kami ng buwanang ulat sa pagbebenta sa Excel, na tinalakay namin sa itaas. Oo, magagawa natin ito at marami 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 bagay na matatagpuan. Sa katunayan, maaaring ito ay isang listahan ng anumang bagay: impormasyon sa pakikipag-ugnayan ng empleyado, isang koleksyon ng mga CD, o data ng pagkonsumo ng gasolina ng iyong kumpanya.

Kaya, ilunsad natin ang Excel... at i-load ang listahang ito...

Kapag nabuksan na namin 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 Ipasok(Ipasok) piliin ang utos PivotTable(Talahanayan ng buod):

May lalabas na dialog box Lumikha ng PivotTable(Paggawa ng PivotTable) na may dalawang tanong para sa iyo:

• Anong data ang dapat kong gamitin para gumawa ng bagong pivot table?
• Saan ko dapat ilagay 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, o 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 worksheet at maglalagay ng walang laman na pivot table dito:

Sa sandaling mag-click kami sa anumang cell sa pivot table, lilitaw 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 sa orihinal na listahan. Hinahayaan ka ng apat na blangkong bahagi sa ibaba ng screen na sabihin sa PivotTable kung paano mo gustong i-summarize ang data. Habang 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. Sa kasong ito, awtomatikong nabuo ang isang pivot table alinsunod sa aming mga tagubilin. Kung nagkamali kami, maaari naming alisin ang mga heading sa ibabang bahagi o i-drag ang iba upang palitan ang mga ito.

Rehiyon Mga halaga(Kahulugan) ay marahil ang pinakamahalaga sa apat. Anong heading ang ilalagay sa lugar na ito ang tumutukoy kung anong data ang ibubuod (sum, average, maximum, minimum, atbp.) Ito ay halos palaging mga numerical na halaga. Isang mahusay na kandidato para sa isang posisyon sa lugar na ito - data sa ilalim ng heading Halaga(Gastos) ng aming orihinal na talahanayan. I-drag natin ang pamagat na ito sa lugar Mga halaga(Mga Halaga):

Mangyaring tandaan na ang pamagat Halaga ay minarkahan na ngayon ng markang tsek, at sa lugar Mga halaga(Values) entry ay lumitaw Kabuuan ng Halaga(Sum by Amount field), na nagsasaad na ang column Halaga summed up

Kung titingnan natin ang pivot table mismo, makikita natin ang kabuuan ng lahat ng mga halaga mula sa column Halaga orihinal na talahanayan.

Kaya, ang aming unang pivot table ay nilikha! Maginhawa, ngunit hindi partikular na kahanga-hanga. Marahil ay gusto namin ng higit pang impormasyon tungkol sa aming data kaysa sa kasalukuyan.

Tingnan natin ang pinagmumulan ng data at subukang tukuyin ang isa o higit pang mga column na maaaring gamitin upang hatiin ang kabuuan na ito. Halimbawa, maaari kaming lumikha ng aming pivot table sa paraang ang kabuuang halaga ng benta ay kalkulahin para sa bawat nagbebenta nang hiwalay. Yung. Ang mga row ay idaragdag sa aming pivot table na may pangalan ng bawat salesperson ng kumpanya at ang kanyang kabuuang halaga ng benta. Upang makamit ang resultang ito, i-drag lamang ang pamagat Salesperson Mga Label ng Hilera(Mga Linya):

Ito ay nagiging mas kawili-wili! Nagsisimula nang mahubog ang aming pivot table...

Nakikita mo ba 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 raw data. Ang mahahalagang impormasyon ay natanggap na! Sa natitirang bahagi ng artikulong ito, titingnan natin ang ilang paraan upang lumikha ng mas kumplikadong mga pivot table at matutunan kung paano i-customize ang mga ito.

Pagse-set up ng pivot table

Una, maaari tayong lumikha ng two-dimensional na pivot table. Gawin natin ito gamit ang header ng column Paraan ng Pagbayad(Paraan ng Pagbayad). I-drag lang ang pamagat Paraan ng Pagbayad sa rehiyon Mga Label ng Column(Mga Hanay):

Nakukuha namin ang resulta:

Mukhang napaka-cool!

Ngayon gumawa tayo ng tatlong-dimensional na talahanayan. Ano kaya ang hitsura ng gayong mesa? Tingnan natin…

I-drag ang pamagat Package(Complex) sa lugar Filter ng Ulat(Mga Filter):

Pansinin kung saan siya napunta...

Nagbibigay ito sa amin ng pagkakataong i-filter ang ulat ayon sa katangiang "Aling holiday complex ang binayaran." Halimbawa, makakakita tayo ng breakdown ayon sa mga nagbebenta at paraan ng pagbabayad para sa lahat ng complex, o sa ilang pag-click, maaari nating baguhin ang view ng summary 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-configure...

Kung biglang lumabas na ang mga pagbabayad lamang sa pamamagitan ng tseke at credit card (iyon ay, bank transfer) ang dapat ipakita sa pivot table, maaari naming i-disable ang display ng header Cash(Cash). Upang gawin ito sa tabi Mga Label ng Column i-click ang pababang arrow at sa drop-down na menu ay alisan ng check ang item Cash:

Tingnan natin kung ano ang hitsura ng aming pivot table ngayon. Tulad ng nakikita mo, ang column Cash nawala sa kanya.

Pag-format ng Mga Pivot Table sa Excel

Malinaw, ang PivotTables ay 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—mga numero lamang ang mga ito. Ayusin natin ito.

Nakatutukso na gawin ang mga karaniwang aksyon sa ganoong sitwasyon at piliin lamang ang buong talahanayan (o buong sheet) at gamitin ang karaniwang mga pindutan ng 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 (at may 99% na pagkakataong mangyari ito), mawawala ang pag-format. Kailangan natin ng paraan para (halos) maging permanente ito.

Una, hanapin natin ang entry Kabuuan ng Halaga sa lugar Mga halaga(Mga halaga) at i-click ito. Sa lalabas na menu, piliin ang item Mga Setting ng Value Field(Mga Opsyon sa Value Field):

May lalabas na dialog box Mga Setting ng Value Field(Mga parameter ng patlang ng halaga).

I-click ang button Format ng Numero(Number Format), lalabas ang isang dialog box I-format ang mga 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 nang maraming beses OK upang bumalik sa aming pivot table.

Tulad ng nakikita mo, ang mga numero ay natapos na na-format bilang mga halaga ng dolyar.

Ngayong tapos na tayo sa pag-format, i-set up natin ang format para sa buong pivot table. Mayroong ilang mga paraan upang gawin ito. Gamitin natin ang mas simple...

Buksan ang tab Mga Tool sa PivotTable: Disenyo(Paggawa gamit ang mga pivot table: Designer):

Pumili ng anumang naaangkop na istilo at tingnan ang resulta sa iyong pivot table:

Iba pang mga setting para sa mga pivot table sa Excel

Minsan kailangan mong i-filter ang data ayon sa mga petsa. Halimbawa, ang aming listahan ng mga transaksyon sa kalakalan ay naglalaman ng marami, maraming petsa. Ang Excel ay nagbibigay ng isang tool upang pangkatin ang data ayon sa araw, buwan, taon, atbp. Tingnan natin kung paano ito ginawa.

Una, alisin ang entry. Paraan ng Pagbayad mula sa rehiyon Mga Label ng Column(Mga Hanay). Upang gawin ito, i-drag ito pabalik sa listahan ng mga heading, at sa lugar nito, ilipat ang heading Petsa ng Pag-book(Petsa ng booking):

Gaya ng nakikita mo, pansamantalang ginawa nitong walang silbi 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 Grupo(Pangkat):

Lilitaw ang dialog box ng pagpapangkat. Pumili tayo mga buwan(Mga buwan) at pindutin ang OK:

Voila! Ang talahanayang ito ay mas kapaki-pakinabang:

Sa pamamagitan ng paraan, ang talahanayan na ito ay halos magkapareho sa ipinakita sa simula ng artikulo, kung saan ang mga resulta 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 bagay ay maaaring gawin sa mga header ng hanay (o kahit na mga filter).

Bumalik tayo sa orihinal na view ng talahanayan at tingnan kung paano ipakita ang mga average na halaga sa halip na mga kabuuan.

Upang makapagsimula, mag-click sa Kabuuan ng Halaga at mula sa lalabas na menu, piliin ang Mga Setting ng Value Field(Mga Opsyon sa Value Field):

Sa listahan Ibuod ang field ng halaga ayon sa(Operasyon) sa dialog box Mga Setting ng Value Field(Mga Opsyon sa Value Field) piliin Katamtaman(Karaniwan):

At the same time, habang nandito tayo, magpalit tayo Custom na Pangalan(Pasadyang pangalan) na may Average ng Halaga(Dami ayon sa Halaga na field) sa mas maikli. Maglagay ng ganito sa field na ito Avg:

I-click OK at tingnan kung ano ang nangyari. Pansinin na ang lahat ng mga halaga ay nagbago mula sa mga kabuuan hanggang sa mga average, at ang pamagat ng talahanayan (sa kaliwang itaas na cell) ay nagbago sa Avg:

Kung gusto mo, maaari mong makuha agad ang kabuuan, average at dami (benta) na nakalagay sa isang pivot table.

Narito ang isang sunud-sunod na gabay sa kung paano ito gawin, simula sa isang walang laman na pivot table:

1. I-drag ang pamagat Salesperson(Sales Representative) sa rehiyon Mga Label ng Column(Mga Hanay).
2. I-drag ang pamagat ng tatlong beses Halaga(Gastos) sa lugar Mga halaga(Mga halaga).
3. Para sa unang larangan Halaga palitan ang pangalan sa Kabuuan(Halaga), at ang format ng numero sa field na ito ay Accounting(Pananalapi). Ang bilang ng mga decimal na lugar ay zero.
4. Pangalawang larangan Halaga pangalan Average e, itakda ang operasyon para dito Katamtaman(Karaniwan) at baguhin ang format ng numero sa field na ito sa Accounting(Financial) na may bilang ng mga decimal na lugar na katumbas ng zero.
5. Para sa ikatlong larangan Halaga itakda ang pamagat Bilangin at isang operasyon para sa kanya - Bilangin(Dami)
6. Sa lugar Mga Label ng Column Awtomatikong nilikha ang field (Mga Column). ΣMga halaga(Σ Values) – i-drag ito sa lugar Mga Label ng Hilera(Mga String)

Ito ang makukuha natin sa dulo:

Kabuuan, average at bilang ng mga benta - lahat sa isang pivot table!

Konklusyon

Ang mga pivot table sa Microsoft Excel ay naglalaman ng marami, maraming function at setting. Ang gayong maikling artikulo ay hindi man lang makalapit sa pagsaklaw sa lahat ng ito. Upang ganap na mailarawan ang lahat ng mga kakayahan ng mga pivot table ay mangangailangan ng isang maliit na libro o isang malaking website. Ang matatapang at mausisa na mga mambabasa ay maaaring magpatuloy sa 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 palaging tanggalin ang pivot table at magsimulang muli. Mayroon kang pagkakataong hindi kailanman nagkaroon ng matagal nang gumagamit ng DOS at Lotus 1-2-3.

Binibigyang-daan ka ng mga pivot table sa Excel na suriin ang data na nasa isang malaking hanay. Bukod dito, ang source table na gagamitin para sa pagsusuri ay maaaring gawin sa Excel o sa ibang database o iba pang dokumento.

Ang kailangan mo lang gawin ay piliin ang mga kinakailangang header para sa mga row at column mula sa mga umiiral na. Maaari ka ring maglapat ng filter sa buong pivot table sa kabuuan, o sa mga value lang na ipinahiwatig sa mga pangalan ng mga row at column.

Ngayon tungkol sa mga kinakailangan na dapat sundin kapag nilikha ito sa Excel. Ang pinagmulan ng data ay dapat ipakita sa anyo ng isang talahanayan, na dapat ay may pamagat para sa mga hanay, iyon ay, isang header. Para sa mga layuning ito, ang paglikha ng isang matalinong talahanayan sa Excel ay perpekto. Hindi rin ito dapat maglaman ng mga walang laman na row, column o cell. Dapat ay walang mga nakatagong row/column o pinagsamang mga cell.

Paano lumikha

Ngayon tingnan natin ang isang halimbawa ng paglikha ng pivot table. Ipagpalagay na mayroon kaming data sa mga benta ng isang tindahan ng damit: sinong nagbebenta, anong numero, anong mga bagay ang kanyang nabili at kung anong halaga.

Upang gumawa ng pivot table, pumili ng anumang cell mula sa orihinal, pagkatapos ay pumunta sa tab na "Ipasok" at mag-click sa pindutan "Pivot table".

Ang sumusunod na dialog box ay lilitaw. Sa loob nito kailangan mong ipahiwatig ang saklaw o pangalan ng talahanayan na may data ng pinagmulan, at dito maaari mong piliin ito mula sa isa pang mapagkukunan. Pagkatapos ay markahan ng marker kung saan ilalagay ang ulat na gagawin. Gawin natin ito sa isang bagong sheet. I-click ang OK.

Gagawa ng bagong sheet sa bukas na workbook ng Excel, kung saan ilalagay ang walang laman na pivot table.

Ang isang listahan ng mga patlang at mga lugar ay lilitaw sa kanang bahagi. Ang mga field ay ang lahat ng mga header ng column na nasa orihinal na hanay. Gamit ang mouse, i-drag namin ang mga ito sa isa sa apat na lugar sa ibaba. Kaya, bumubuo ng isang pivot table.

Ang mga idinagdag na field ay mamarkahan ng check mark. Sa mga lugar ng talahanayan, maaari mong baguhin ang kanilang mga lugar upang makuha ang hitsura na pinakaangkop para sa pagsusuri ng iyong pinagmulang data.

Paano ayusin ang mga patlang

Ngayon magpasya tayo kung anong prinsipyo ang susuriin natin ang data. Halimbawa, kailangan mong malaman kung sinong nagbebenta ang nagbebenta ng produkto sa bawat buwan at kung anong halaga.

I-filter namin ang data ayon sa napiling hanay ng mga nagbebenta. Iyon ay, pumili kami ng isang nagbebenta, at ang talahanayan ay magpapakita ng mga kalakal na ibinebenta niya. I-left-click ang field na “Vendor” at i-drag ito sa lugar na “Report Filter”. Ang talahanayan ay nagbago at ang idinagdag na patlang ay minarkahan na ngayon ng isang marka ng tsek.

Para sa mga linya, piliin ang "Mga Produkto". Katulad nito, i-drag ang nais na field sa lugar "Mga pamagat ng linya".

Pakitandaan na maaari kang magpasok ng maraming field sa isang lugar. Halimbawa, sa mga linya ay pipili kami ng mga produkto at ipahiwatig ang presyo. Ang mga produkto ay naging mga drop-down na listahan na nagpapakita ng presyo. Kung una mong tinukoy ang presyo at pagkatapos ay ang mga produkto, ang drop-down na listahan ay magiging presyo. Dito mahalaga ang pagkakasunud-sunod ng mga patlang.

Kung mayroon kaming column na "Mga Yunit" sa orihinal na talahanayan. Pagkatapos ay ipapakita ng talahanayan ng buod na ito kung aling nagbebenta, sa kung anong buwan, kung gaano karaming mga yunit ng mga kalakal ang kanyang naibenta sa isang tiyak na presyo.

Sa rehiyon "Mga pamagat ng column" I-drag ang field na “Petsa”. Upang ipakita ang mga benta hindi para sa bawat araw, ngunit, halimbawa, sa pamamagitan ng buwan, i-right-click sa anumang petsa at piliin ang "Group" mula sa menu.

Ang talahanayan ay kukuha ng sumusunod na anyo.

Ngayon i-drag ang field na "Halaga" sa lugar na "Mga Halaga".

Gaya ng nakikita mo, mga numero lang ang ipinakita, bagama't sa orihinal na hanay ay itinakda ang numeric na format ng mga cell para sa column na ito; maaari rin itong pera o pinansyal. Sa column na "Petsa", naaangkop din ang format ng cell - petsa.

Upang ayusin ito, piliin ang nais na hanay ng mga cell sa buod at pindutin ang kanang pindutan ng mouse. pagkatapos ay pumili mula sa menu "Format ng Numero".

Sa susunod na window, piliin ang "Numerical", maaari mong suriin ang kahon "Bit group separator" at i-click ang "OK".

Paano magtrabaho sa data

Pagkatapos naming isaalang-alang ang lahat ng kinakailangang field, maaari na kaming magsimulang magtrabaho kasama ang pivot table sa Excel. Pagpili ng nagbebenta: maaari kang pumili ng isa, marami o lahat nang sabay-sabay sa pamamagitan ng paglalagay ng check sa kahon "Pumili ng maraming item".

Maaari ka ring gumamit ng filter para sa mga row at column. Sa halimbawa, ito ay mga produkto at buwan. Halimbawa, sa pamamagitan ng paglalagay ng tsek sa mga kahon para sa Suit at Pants, malalaman mo kung magkano ang naibenta sa kanila ng lahat ng nagbebenta o ng isang partikular na nagbebenta.

Sa lugar ng Mga Halaga, maaari mong i-configure ang mga setting para sa field. Ipinapakita ng halimbawa ang kabuuan ng mga halaga: Nagbenta ang Roma ng mga kamiseta sa halagang 1,800.00 noong Pebrero. tingnan natin kung ilang piraso ito. Mag-left-click sa linya "Sum by field..." at pumili mula sa menu "Mga Opsyon sa Value Field".

Sa susunod na window, piliin ang "Dami" mula sa listahan, piliin kung ano ang partikular na angkop para sa iyong kaso, at i-click ang "OK".

Ngayon, sa pagtingin sa mga halaga, mauunawaan natin na ang Roma ay nagbebenta ng dalawang kamiseta noong Pebrero.

Ngayon gawin natin ang pangkalahatang filter para sa talahanayan ayon sa buwan. Binabago namin ang mga lugar: sa "Filter ng Ulat" i-drag namin ang field na "Petsa", papunta sa "Mga pamagat ng column"- "Tindero" .

Ipapakita ng naturang talahanayan ng buod kung magkano at kung anong uri ng mga kalakal ang ibinebenta ng bawat nagbebenta para sa buong panahon o para sa isang partikular na buwan.

Bigyang-pansin din ang tape. Kapag pumili ka ng anumang mga cell mula sa isang pivot table, may lalabas na tab dito "Paggawa gamit ang Pivot Tables" na may dalawang subtab na "Mga Parameter" at "Designer".

Ang problemang kinakaharap nating lahat ay hindi kakulangan ng data; sa kabaligtaran - ito ay malaking halaga ng data! Ito ang dahilan kung bakit itinataguyod ko ang paggamit ng isang mahusay na function ng Excel - Mga pivot table upang ibuod at pag-aralan ang iyong data.

Ang pagkakita sa kapangyarihan ng PivotTables ay kasing simple ng paghahambing ng orihinal na data sa isang bersyon ng PivotTable; Sa ilang pag-click lang, nakita ko ang average na presyo ng isang item ayon sa estado.

Bilang propesyunal sa pananalapi, genetically inclined akong mahilig sa mga spreadsheet. Ngunit nalaman ko rin na gumagamit ako ng mga spreadsheet upang ayusin ang aking malikhain at freelance na trabaho. Anuman ang ginagamit mo sa mga spreadsheet, makakatulong sa iyo ang isang pivot table na magkaroon ng higit na kahulugan mula sa iyong data.

Bubuo kami ng araling ito ayon sa sa amin, para sa mas mahusay na trabaho sa data. Ipapakita ko sa iyo ang lima sa aking paboritong pinakamahuhusay na kagawian sa pivot table.

Sa buong tutorial na ito, gagamitin ko ang sample na data na ibinigay ng Microsoft sa pahinang ito. Gamitin ang data na ito upang muling likhain ang mga halimbawa o subukan ang mga function na ipinapakita ko.

Paano Gamitin ang Mga Advanced na PivotTable Technique sa Excel (Maikling Video)

Gusto kong magturo gamit ang mga screencast, na nagbibigay sa iyo ng pagkakataong panoorin akong gumamit ng mga feature nang sunud-sunod. Tingnan ang maikling video sa ibaba na sumasaklaw sa lima sa aking mga paboritong advanced na feature ng Excel PivotTable:

5 Advanced na Pivot Table Trick sa Excel

Magpatuloy sa pagbabasa upang matutunan kung paano gamitin ang bawat isa sa limang feature na ito sa aralin sa ibaba, kabilang ang: Slicers, Timeline, Table View, Calculated Fields, at Featured Pivot Tables. Magsimula na tayo.

1. Mga Seksyon

Mga hiwa- Isang point-and-click na tool upang pinuhin ang data na kasama sa isang Excel pivot table. Maglagay ng slicer at madali mong mababago ang data na kasama sa pivot table.

Sa halimbawang ito ay nagpasok ako ng isang slice para sa uri item. Pagkatapos kong mag-click Backpack, ipinapakita lamang ng pivot table ang parameter na ito sa talahanayan.

Maraming beses akong bumuo ng mga ulat sa dashboard na gagamitin ng iba. Ang pagdaragdag ng mga slicer ay makakatulong sa end user na i-customize ang ulat ayon sa gusto nila.

Upang magdagdag ng slicer, mag-click sa pivot table at hanapin ang tab Pagsusuri sa Excel ribbon.

Lagyan ng check ang maramihang mga kahon upang isama ang mga column na ito, na ang bawat isa ay magiging isang slice.

Hawakan Ctrl sa keyboard upang pumili ng maraming item ng slicer na magsasama ng maraming mga pagpipilian mula sa isang column bilang data ng PivotTable.

2. Timeline

Mga timeline Ito ay isang espesyal na uri ng slicer na ginagamit upang ayusin ang mga petsang kasama bilang bahagi ng data ng PivotTable. Kung naglalaman ang iyong data ng mga petsa, kailangan mo talagang subukan ang Timeline bilang isang paraan upang pumili ng data mula sa mga partikular na yugto ng panahon.

Pumunta sa seksyon Suriin > Ipasok ang Timeline upang magdagdag ng timeline, isang espesyal na uri ng slicer na namamahala sa data na kasama sa isang advanced na Excel PivotTable batay sa petsa.

Tip: Kung hindi gumagana ang feature na ito para sa iyo, tiyaking may petsang naka-format ang iyong source data sa spreadsheet bilang petsa.

Upang magdagdag ng timeline, tiyaking pipiliin mo ang PivotTable (i-click sa loob nito) at pagkatapos ay i-click ang button Suriin > Ipasok ang Timeline sa Excel ribbon. Sa pop-up window, pumili ng column ng petsa (o maraming column) at i-click OK para gumawa ng timeline.

I-click at i-drag sa window ng Timeline upang pumili ng partikular na hanay ng oras para sa PivotTable.

Kapag naipasok na ang timeline, maaari mong i-click at ilipat ang mga handle sa timeline upang baguhin kung ano ang kasama sa pivot table.

Maaari mong baguhin ang paraan ng paggana ng timeline sa pamamagitan ng pag-click sa drop-down na listahan sa kanang sulok sa ibaba. Maaari mong baguhin ang timeline upang ipakita ang quarterly o taunang data sa halip na mga partikular na petsa, halimbawa.

3. Tabular view

Ang karaniwang view ng PivotTables sa Excel ay mukhang isang talon; Habang nagda-drag ka ng higit pang mga field papunta sa mga row, gumagawa ang Excel ng higit pang "mga layer" sa data.

Ang problema ay mahirap na magkasya ang mga formula sa PivotTables sa Standard View. Kung mayroon kang data sa isang PivotTable, ngunit gusto mong tingnan ito bilang isang regular na spreadsheet, dapat kang gumamit ng table view para sa PivotTable.

Pumili Designer > Report Layout > Ipakita sa Tabular Form upang gumana sa iyong pivot table bilang isang regular na talahanayan.

Bakit kailangan mong gumamit ng table view? Ang pagdadala sa iyong data ng PivotTable sa isang klasikong istilo ng tabular ay magbibigay-daan sa iyong mas madaling magpasok ng mga formula ng data o ipasok ang mga ito sa isang hiwalay na ulat.

Gamitin ang Table View upang gawing mas katulad ng mga karaniwang row at column ang iyong PivotTable.

Kadalasan, mas gusto kong gamitin ang table view ng Excel. Mas mukhang isang karaniwang spreadsheet at mas madaling magsulat ng mga formula at magtrabaho kasama ang data dito. Maaari ko ring kunin ang view na ito at i-paste ito sa isang bagong tab.

4. Mga kalkuladong field

Ang mga kalkuladong field ay isang paraan upang magdagdag ng column sa isang PivotTable na wala sa source data. Maaari kang gumamit ng mga karaniwang pagpapatakbo sa matematika upang lumikha ng ganap na bagong mga field na gagamitin. Kumuha ng dalawang umiiral nang column at gumamit ng math para gumawa ng mga bago.

Sabihin nating mayroon kaming data ng benta sa isang spreadsheet. Mayroon kaming bilang ng mga bagay na naibenta at ang presyo ng pagbebenta para sa bawat item. Ito ang perpektong oras para gumamit ng kalkuladong field para kalkulahin ang kabuuang order.

Upang magsimulang magtrabaho sa mga kalkuladong field, magsimula sa pamamagitan ng pag-click sa loob ng PivotTable at pagkatapos ay hanapin sa Ribbon Pagsusuri. I-click ang menu Mga field, elemento at set at pagkatapos ay piliin Kinalkula na field.

Gamitin Pagsusuri > Mga Field, Mga Miyembro, at Mga Set > Kinakalkula na Field upang ipasok ang kalkuladong field sa iyong PivotTable.

Sa bagong pop-up window, magsimula sa pamamagitan ng pagbibigay ng pangalan sa kalkuladong field. sa kaso ko tatawagan ko ito Kabuuang Order. Ang kabuuang halaga ng order ay ang dami na na-multiply sa presyo ng bawat unit. Pagkatapos ay nag-double click ako sa unang field ng pamagat (dami) sa listahan ng mga field sa window na ito.

Upang kalkulahin ang kabuuang halaga ng order, pinarami ko ang umiiral na field ng presyo ng unit ( Presyo ng isang piraso) sa field ng dami ( Dami).

Pagkatapos idagdag ang pangalan ng field na ito, magdadagdag ako ng multiplication sign * , at pagkatapos ay i-double click ang kabuuang dami (dami). Sige at i-click natin OK.

Ngayon na-update ng Excel ang aking pinalawak na PivotTable at nagdagdag ng bagong kalkuladong field. Makakakita ka rin ng listahan ng mga PivotTables sa Listahan ng Field upang ma-drag mo sila kahit saan sa ulat tuwing kailangan mo ito.

Kung ayaw mong gumamit ng two-column arithmetic, maaari mo ring ipasok ang iyong sariling arithmetic values ​​sa kalkuladong field. Halimbawa, kung gusto kong magdagdag lang ng 5% na buwis sa pagbebenta para sa bawat order, maaari kong isulat ang sumusunod sa kalkuladong field:

Pinarami ko ang kabuuang halaga ng order sa 1.05 upang kalkulahin ang gastos kasama ang buwis sa pagbebenta; maaari kang gumamit ng mga numerong halaga kasama ng mga umiiral na patlang.

Sa prinsipyo, ang mga kalkuladong field ay maaaring maglaman ng anumang karaniwang mathematical operator tulad ng karagdagan, pagbabawas, pagpaparami, at paghahati. Gamitin ang mga kalkuladong field na ito kung ayaw mong i-update ang orihinal na data.

Pumunta sa Ipasok > upang subukan ang tampok na ito.

Ang tampok na ito ay napakadaling gamitin na ito ay walang sinasabi. Magagamit mo ito upang mabilis na gumawa ng mga advanced na pivot table sa Excel. I-highlight lang ang iyong data, pumunta sa tab Ipasok sa Excel ribbon at piliin Inirerekomendang PivotTables.

Ang pop-up window ay naglalaman ng maraming mga opsyon para sa paggawa ng pivot table mula sa source data. I-click ang mga thumbnail sa kaliwang bahagi ng window na ito upang tingnan ang mga inirerekomendang opsyon sa PivotTable na ibinigay ng Excel.

Ang tampok na Rekomendasyon ng PivotTable ay nag-aalok ng maraming mga opsyon upang suriin ang iyong data sa isang pag-click.

Kahit na ito ay isang advanced na tampok na kakaunti sa mga user ang nakakaalam, isa rin itong mahusay na tool para sa pagpapatakbo ng mga pivot table. Walang pumipigil sa iyo na baguhin ang PivotTable sa pamamagitan ng pagpapalit ng mga field sa iyong sarili, ngunit ito ay isang magandang panimulang punto.

Gayundin, gusto ko ang feature na ito bilang tool sa paggalugad ng data. Kung hindi ko alam kung ano ang hinahanap ko kapag sinimulan kong galugarin ang data, ang mga inirerekomendang Excel Pivot Table ay kadalasang mas insightful kaysa sa akin!

Ulitin at magpatuloy sa pag-aaral (na may higit pang mga Excel lesson)

Ang advanced na Excel lesson na ito ay nakatulong sa iyo na sumisid nang mas malalim sa PivotTables, isa sa aking mga paboritong feature para sa pagsusuri at pagtingin sa isang Excel spreadsheet. Gumagamit ako ng mga pivot table para makahanap ng kahulugan sa malalaking set ng data para makagawa ako ng mas mahuhusay na desisyon at makakilos.

Dadalhin ng mga araling ito ang iyong mga kasanayan sa Excel at Pivot Table sa susunod na antas. Tingnan ang mga ito:

• Ang ExcelZoo ay may mahusay na pangkalahatang-ideya ng mga diskarte sa Pivot Table sa kanilang artikulo, 10 Mga Aralin para sa Mastering Pivot Tables (sa English).
• Kami sa Envato Tuts+ ay tinakpan ang mga pivot table na may tutorial ng baguhan.
• Para sa mas madaling pagpapakilala sa Microsoft Excel, tingnan ang aming serye ng tutorial.

Ano ang gusto mo pa ring malaman tungkol sa mga pivot table? Ipaalam sa akin ang iyong mga ideya o tanong sa mga komento sa ibaba ng tutorial na ito.