Darbs ar Pivot tabulām programmā Microsoft Excel

PivotTables ir viena no spēcīgākajām Microsoft Excel funkcijām. Tie ļauj analizēt un apkopot tikai dažus peles klikšķus. Šajā rakstā mēs pētām PivotTables, saprotam, kas tie ir, un uzziniet, kā tos izveidot un pielāgot.

Piezīme: Šis raksts ir rakstīts, izmantojot Excel 2010 (Beta). PivotTable koncepcija gadu gaitā ir maz mainījusies, taču tās izveides metode ir mainījusies gandrīz katrā Excel atkārtojumā. Ja izmantojat Excel versiju, kas nav 2010, sagaidiet dažādus ekrānus no tiem, kurus redzat šajā rakstā.

Mazā vēsture

Sākotnējās izklājlapu programmu dienās Lotus 1-2-3 pārvaldīja to. Tās dominējošais stāvoklis bija tik pilnīgs, ka cilvēki domāja, ka ir jātērē laiks, lai Microsoft apgrūtinātu savu izklājlapu programmatūras (Excel) izstrādi, lai konkurētu ar Lotus. Flash-forward līdz 2010. gadam un Excel dominējošais izklājlapu tirgus ir lielāks nekā jebkad bijis Lotus, savukārt lietotāju skaits, kas joprojām izmanto Lotus 1-2-3, tuvojas nullei. Kā tas notika? Kas izraisīja tik dramatisku likteni?

Nozares analītiķi to nosauca uz diviem faktoriem: Pirmkārt, Lotus nolēma, ka šī jaunā GUI platforma, ko sauc par „Windows”, bija iet bojā, kas nekad nebeidzas. Viņi atteicās izveidot Windows versiju Lotus 1-2-3 (dažus gadus, jebkurā gadījumā), paredzot, ka viņu DOS versija programmatūras bija visi, kas kādreiz būtu nepieciešams. Microsoft, protams, izstrādāja Excel tikai Windows. Otrkārt, Microsoft izstrādāja Excel funkciju, ko Lotus nesniedza 1-2-3 PivotTables.  PivotTables iezīme, kas bija ekskluzīva Excel, tika uzskatīta par tik satriecošu, ka cilvēki bija gatavi mācīties visu jaunu programmatūras pakotni (Excel), nevis pieturēties pie programmas (1-2-3), kurai tas nebija. Šī viena iezīme, kā arī nepareiza Windows panākumu vērtēšana bija Lotus 1-2-3 nāves zīme un Microsoft Excel panākumu sākums.

PivotTables izpratne

Tātad, tieši tā ir PivotTable?

Vienkārši sakot, PivotTable ir dažu datu kopsavilkums, kas izveidots, lai varētu viegli analizēt šos datus. Bet atšķirībā no manuāli izveidotā kopsavilkuma, Excel PivotTables ir interaktīvas. Kad esat to izveidojis, to var viegli nomainīt, ja tas nesniedz precīzus ieskatus jūsu datiem, kurus jūs cerējāt. Pāris klikšķos kopsavilkumu var “pagriezt” - pagriezt tā, ka kolonnu virsraksti kļūst par rindu virsrakstiem, un otrādi. Ir daudz vairāk, ko var izdarīt. Tā vietā, lai mēģinātu aprakstīt visas PivotTables funkcijas, mēs vienkārši parādīsim tos ...

Dati, kurus analizējat, izmantojot PivotTable, nevar būt vienkārši jebkurš datiem - tam jābūt neapstrādātas dati, iepriekš neapstrādāti (nesagatavoti) - parasti ir kāda veida saraksts. Tā piemērs varētu būt pārdošanas darījumu saraksts uzņēmumā pēdējos sešos mēnešos.

Pārbaudiet tālāk redzamos datus:

Ņemiet vērā, ka tas ir ne neapstrādāti dati. Faktiski tas jau ir sava veida kopsavilkums. Šūnā B3 mēs varam redzēt $ 30,000, kas acīmredzot ir James Cook pārdošanas apjoms janvārī. Tātad, kur ir neapstrādāti dati? Kā mēs nonācām pie $ 30 000? Kur ir sākotnējais pārdošanas darījumu saraksts, no kura šis skaitlis tika iegūts? Ir skaidrs, ka kaut kur kādam ir jāvēršas pie visu pēdējo sešu mēnešu pārdošanas darījumu apkopošanas iepriekš redzamajā kopsavilkumā. Cik ilgi jūs domājat, ka tas notika? Stunda? Desmit?

Visticamāk, jā. Redzat, ka iepriekš minētā izklājlapa ir faktiski ne PivotTable. Tas tika izveidots manuāli no citur saglabātiem neapstrādātiem datiem, un tas patiešām bija nepieciešams pāris stundu laikā. Tomēr tas ir tieši tāds kopsavilkums varētu jāizveido, izmantojot PivotTables, tādā gadījumā tas būtu bijis tikai dažas sekundes. Noskaidrosim, kā ...

Ja mēs izsekotu pārdošanas darījumu sākotnējo sarakstu, tas varētu izskatīties šādi:

Jums var būt pārsteigts, uzzinot, ka, izmantojot Excel funkciju PivotTable, dažu sekunžu laikā mēs varam izveidot ikmēneša pārdošanas kopsavilkumu, kas ir līdzīgs iepriekš minētajam, tikai ar dažiem peles klikšķiem. Mēs to varam darīt - un vēl daudz vairāk!

Kā izveidot PivotTable

Pirmkārt, pārliecinieties, ka programmā Excel darblapā ir daži neapstrādāti dati. Finanšu darījumu saraksts ir tipisks, bet tas var būt tikai par kaut ko: darbinieku kontaktinformācija, jūsu CD kolekcija vai degvielas patēriņa rādītāji jūsu uzņēmuma autoparkam..

Tāpēc mēs sākam Excel ... un mēs ielādējam šādu sarakstu ...

Kad būsim atvērts saraksts programmā Excel, mēs esam gatavi sākt izveidot PivotTable.

Noklikšķiniet uz jebkuras vienas šūnas sarakstā:

Tad, no Ievietot cilnē noklikšķiniet uz PivotTable ikona:

The Izveidot PivotTable parādās divi jautājumi: kādi dati būtu jābalsta uz jūsu jauno PivotTable, un kur tas būtu jāizveido? Tā kā mēs jau esam noklikšķinājuši uz šūnas sarakstā (iepriekš minētajā solī), viss saraksts ar šo šūnu jau ir izvēlēts mums ($ A $ 1: $ G $ 88 uz Maksājumi šajā piemērā). Ņemiet vērā, ka mēs varam izvēlēties sarakstu jebkurā citā jebkuras citas darblapas reģionā vai pat kādu ārēju datu avotu, piemēram, Access datu bāzes tabulu vai pat MS-SQL Server datu bāzes tabulu. Mums ir arī jāizvēlas, vai mēs vēlamies, lai jaunā PivotTable tiktu izveidota uz a jauns darblapā vai uz esošo vienu. Šajā piemērā mēs izvēlamies a jauns viens:

Jaunā darblapa ir izveidota mums, un šajā darblapā tiek izveidota tukša PivotTable:

Tiek parādīts arī cits lodziņš: PivotTable lauka saraksts.  Šis lauks tiks parādīts ikreiz, kad mēs noklikšķināsim uz jebkuras šūnas, kas atrodas PivotTable (iepriekš):

Kastes augšējā daļā esošo lauku saraksts faktiski ir kolonnu virsrakstu kolekcija no sākotnējās neapstrādātās datu lapas. Četri tukšie lodziņi ekrāna apakšējā daļā ļauj izvēlēties veidu, kādā mēs vēlamies, lai PivotTable apkopotu neapstrādātus datus. Līdz šim šajās kastēs nav nekas, tāpēc PivotTable ir tukšs. Viss, kas mums jādara, ir vilkt laukus no iepriekš minētā saraksta un nometiet tos zemākajos lodziņos. PivotTable tiek automātiski izveidots, lai tas atbilstu mūsu norādījumiem. Ja mēs to izdarīsim nepareizi, mums tikai jāvelk lauki atpakaļ uz vietu, kur viņi nāca un / vai velk jauns laukus, lai tos aizstātu.

The Vērtības kaste, iespējams, ir vissvarīgākā no četrām. Šajā lodziņā vilktais lauks atspoguļo datus, kas ir jāapkopo kādā veidā (summējot, aprēķinot vidējo vērtību, atrodot maksimālo, minimālo utt.). Tas ir gandrīz vienmēr skaitliski datus. Lielisks kandidāts šim lodziņam mūsu izlases datos ir lauks "Summa". Velciet šo lauku uz Vērtības lodziņš:

Ņemiet vērā, ka (a) lauciņu sarakstā laukā esošais lauks ir atzīmēts, un “Summa” ir pievienota Vērtības lodziņā, norādot, ka summas kolonna ir summēta.

Ja mēs paši pārbaudām PivotTable, mēs patiešām atrodam visu "Summu" vērtību summu no neapstrādāta datu darba lapas:

Mēs esam izveidojuši savu pirmo PivotTable! Ērts, bet ne īpaši iespaidīgs. Iespējams, ka mums ir nepieciešams nedaudz vairāk ieskatu par mūsu datiem.

Atsaucoties uz mūsu izlases datiem, mums ir jāidentificē viena vai vairākas sleju virsraksti, ko mēs varētu izmantot, lai sadalītu šo kopējo summu. Piemēram, mēs varam nolemt, ka mēs vēlamies redzēt mūsu datu kopsavilkumu, kur mums ir rindas virsraksts katram no mūsu uzņēmuma dažādiem pārdevējiem un par katru no tiem. Lai to panāktu, viss, kas mums jādara, ir vilkt „Pārdevēja” lauku Rindu etiķetes lodziņš:

Tagad, visbeidzot, lietas sāk kļūt interesantas! Mūsu PivotTable sāk veidoties ... .

Ar pāris klikšķiem mēs esam izveidojuši tabulu, kas būtu ilgu laiku aizpildījusi manuāli.

Tātad, ko vēl mēs varam darīt? Nu, vienā ziņā mūsu PivotTable ir pabeigta. Mēs esam izveidojuši noderīgu mūsu avota datu kopsavilkumu. Svarīgi priekšmeti jau ir iemācījušies! Pārējā rakstā mēs izskatīsim dažus veidus, kā var izveidot sarežģītākas PivotTables, un veidus, kā šīs PivotTables var pielāgot.

Pirmkārt, mēs varam izveidot divi-izmēra tabula. Darīsim to, izmantojot “Maksājuma metodi” kā slejas virsrakstu. Vienkārši velciet pozīciju “Maksājuma metode” uz Kolonnu uzlīmes lodziņš:

Kas izskatās šādi:

Sākt nokļūt ļoti atdzesējiet!

Padarīsim to a trīs-izmēra tabula. Kāda varētu būt šāda tabula? Nu, redzēsim…

Velciet sleju “Iepakojums” / virsrakstu uz Ziņojuma filtrs lodziņš:

Ievērojiet, kur tas beidzas… .

Tas ļauj mums filtrēt mūsu ziņojumu, pamatojoties uz to, kura “brīvdienu pakete” tika iegādāta. Piemēram, mēs redzam pārdevēja un maksājumu metodes sadalījumu viss paketes, vai ar pāris klikšķiem nomainiet to, lai parādītu to pašu sadalījumu “Sunseekers” paketē:

Un, ja jūs domājat par to pareizi, mūsu PivotTable tagad ir trīsdimensiju. Turpināsim pielāgot ...

Ja izrādās, ka mēs tikai vēlamies redzēt čeku un kredītkarti darījumi (t.i., bez skaidras naudas darījumiem), tad no slejas virsrakstiem var atcelt “Naudas” vienumu. Noklikšķiniet uz nolaižamās bultiņas blakus Kolonnu uzlīmes, un atcelt „Nauda”:

Redzēsim, kā tas izskatās… Kā redzat, „Nauda” ir pagājis.

Formatēšana

Tas, protams, ir ļoti spēcīga sistēma, bet līdz šim rezultāti ir ļoti vienkāršs un garlaicīgi. Sākotnēji skaitļi, kurus mēs summējam, neizskatās kā dolāru summas - vienkārši veci skaitļi. Labosim to.

Kārdinājums varētu būt darīt to, ko esam darījuši šādos apstākļos, un vienkārši atlasiet visu tabulu (vai visu darblapu) un izmantojiet standarta numura formatēšanas pogas rīkjoslā, lai pabeigtu formatēšanu. Šīs pieejas problēma ir tāda, ka, ja jūs nākotnē mainīsiet PivotTable struktūru (kas ir 99% varbūtība), tad šie skaitļu formāti tiks zaudēti. Mums ir vajadzīgs veids, kā padarīt viņus (daļēji) pastāvīgus.

Pirmkārt, mēs atrodam ierakstu "Summas summa" Vērtības un noklikšķiniet uz tā. Parādās izvēlne. Mēs izvēlamies Vērtības lauku iestatījumi ... no izvēlnes:

The Vērtības lauka iestatījumi parādās lodziņš.

Noklikšķiniet uz Numura formāts pogas un standarta Formatēt šūnu lodziņu parādās:

No Kategorija sarakstā, izvēlieties (teiksim) Grāmatvedība, un nomest decimāldaļu skaitu līdz 0. Noklikšķiniet labi dažas reizes, lai atgrieztos PivotTable…

Kā redzat, skaitļi ir pareizi formatēti kā dolāra summas.

Kamēr mēs atrodamies formatēšanas jomā, formatēsim visu PivotTable. Ir daži veidi, kā to izdarīt. Izmantosim vienkāršu ...

Noklikšķiniet uz PivotTable rīki / dizains cilne:

Tad nolaižamās bultiņas apakšējā labajā stūrī PivotTable stili sarakstu, lai redzētu plašu iebūvēto stilu kolekciju:

Izvēlieties jebkuru, kas pārsūdz, un apskatiet rezultātu savā PivotTable:

Citas iespējas

Mēs varam strādāt arī ar datumiem. Tagad parasti ir daudzi, daudzi datumi darījumu sarakstā, piemēram, tas, ko mēs sākām ar. Bet Excel nodrošina iespēju grupēt datu vienumus kopā ar dienu, nedēļu, mēnesi, gadu utt. Apskatīsim, kā tas tiek darīts.

Pirmkārt, noņemsim sleju “Maksājumu metodes” no Kolonnu uzlīmes lodziņā (vienkārši velciet to atpakaļ uz lauka sarakstu) un nomainiet to ar sleju “Rezervētais datums”:

Kā redzat, tas padara mūsu PivotTable uzreiz bezjēdzīgu, sniedzot mums vienu kolonnu par katru datumu, kad notika darījums - ļoti plaša tabula!

Lai to labotu, ar peles labo pogu noklikšķiniet uz jebkura datuma un izvēlieties Grupa… no konteksta izvēlnes:

Parādās grupēšanas lodziņš. Mēs izvēlamies Mēneši un noklikšķiniet uz Labi:

Voila! A daudz noderīgāka tabula:

(Starp citu, šī tabula ir praktiski identiska tam, kas parādīts šī raksta sākumā - sākotnējais pārdošanas kopsavilkums, kas tika izveidots manuāli.)

Vēl viena laba lieta, kas jāapzinās, ir tā, ka jums var būt vairāki rindu virsraksti (vai sleju virsraksti):

… Kas izskatās šādi: .

Līdzīgu lietu var izdarīt ar kolonnu virsrakstiem (vai pat pārskatu filtriem).

Turot vēlreiz vienkāršu, skatīsimies, kā veidot vidēji vērtības, nevis summētās vērtības.

Vispirms noklikšķiniet uz “Summas summa” un izvēlieties Vērtības lauku iestatījumi ... no kontekstizvēlnes, kas parādās:

Iekš Apkopojiet vērtību lauku ar sarakstā Vērtības lauka iestatījumi lodziņā izvēlieties Vidējais:

Kamēr mēs esam šeit, mainīsim Pielāgots nosaukums, no “Summas vidējās vērtības” līdz kaut ko nedaudz precīzākam. Ievadiet kaut ko līdzīgu: “Avg”:

Klikšķis labi, un redzēt, kā tas izskatās. Ievērojiet, ka visas vērtības mainās no summētiem kopsummas uz vidējiem rādītājiem, un tabulas nosaukums (augšējā kreisā šūna) ir mainījies uz “Avg”:

Ja mums patīk, mēs varam pat būt summas, vidējie rādītāji un skaitļi (skaits = cik pārdošanas apjoms bija) tajā pašā PivotTable!

Tālāk ir norādīti soļi, kā iegūt kaut ko līdzīgu (sākot ar tukšu PivotTable):

1. Velciet “pārdevēju” Kolonnu uzlīmes
2. Velciet lauku “Summa” uz leju Vērtības trīs reizes
3. Pirmajā laukā „Summa” nomainiet savu pielāgoto nosaukumu uz “Kopā” un tā numuru formātu uz Grāmatvedība (0 zīmes aiz komata)
4. Otrajā laukā “Summa” nomainiet savu pasūtījuma nosaukumu uz “Vidēji”, tā funkciju uz Vidējais un tā numuru formāts Grāmatvedība (0 zīmes aiz komata)
5. Trešajā laukā „Summa” nomainiet tā nosaukumu uz “Count” un tā funkciju uz Skaits
6. Velciet automātiski izveidoto lauks no Kolonnu uzlīmes uz Rindu etiķetes

Lūk, ko mēs beidzam ar:

Kopā, vidēji un uz to pašu PivotTable!

Secinājums

Ir daudz, daudzas citas funkcijas un iespējas, ko piedāvā Microsoft Excel izveidotās PivotTables - pārāk daudz, lai uzskaitītu šādu rakstu. Lai pilnībā aptvertu PivotTables potenciālu, būtu nepieciešama neliela grāmata (vai liela tīmekļa vietne). Drosmīgie un / vai geeky lasītāji var pavisam viegli izpētīt PivotTables: vienkārši ar peles labo pogu noklikšķiniet tikai uz visu, un skatiet, kādas iespējas ir pieejamas. Ir arī divas lentes: PivotTable rīki / opcijas un Dizains.  Nav nozīmes, ja jūs kļūdāties - ir viegli izdzēst PivotTable un sākt no jauna - iespēja, ka vecie DOS lietotāji Lotus 1-2-3 nekad nav bijuši.

Ja strādājat programmā Office 2007, iespējams, vēlēsities apskatīt mūsu rakstu par to, kā izveidot programmu PivotTable programmā Excel 2007.

Mēs esam iekļāvuši Excel darbgrāmatu, kuru varat lejupielādēt, lai izmantotu savas PivotTable prasmes. Tai jādarbojas ar visām Excel versijām no 97. gada.

Lejupielādēt mūsu prakses Excel darbgrāmatu