PivotTables ir viena no spēcīgākajām Microsoft Excel funkcijām. Tie ļauj lielu datu apjomu analizēt un apkopot ar dažiem peles klikšķiem. Šajā rakstā mēs izpētām PivotTables, saprotam, kas viņi ir, un uzzināsim, kā tos izveidot un pielāgot.
Piezīme. Šis raksts ir izveidots, izmantojot programmu Excel 2010 (beta versija). PivotTable koncepcija gadu gaitā ir mainījusies, bet tās izveides metode ir mainījusies gandrīz katrā Excel atkārtojumā. Ja jūs izmantojat Excel versiju, kas nav 2010. gads, gaidiet atšķirīgus ekrānus no tiem, kas redzami šajā rakstā.
Maza vēsture
Izklājlapu programmu sākuma dienās Lotus 1-2-3 izlēma. Tās dominance bija tik pilnīga, ka cilvēki domāja, ka Microsoft laika trūkums ir apgrūtināt izstrādāt savu izklājlapu programmatūru (Excel), lai konkurētu ar Lotus. Flash uz priekšu līdz 2010.gadam un Excel dominēšana izklājlapu tirgū ir lielāks nekā Lotus ir kādreiz bija, bet lietotāju skaits joprojām darbojas Lotus 1-2-3 tuvojas nullei. Kā tas notika? Kas izraisīja tik dramatisku pārmaiņu likteni?
Nozares analītiķi to noteica divos faktoros: pirmkārt, Lotus nolēma, ka šī iedomātā jauna GUI platforma, ko sauc par "Windows", bija īsta fad, kas nekad neaizņemtu. Viņi nolēma izveidot Windows versiju Lotus 1-2-3 (dažus gadus tik un tā), paredzot, ka viņu programmatūras DOS versija bija viss, kas kādreiz būtu vajadzīga. Microsoft, protams, izstrādāja Excel tikai Windows. Otrkārt, Microsoft izstrādāja funkciju Excel, ko Lotus nebija sniegusi 1-2-3, proti PivotTables. PivotTables funkcija, kas ir ekskluzīva programma Excel, tika uzskatīta par tik satriecoši noderīgu, ka cilvēki gribēja apgūt visu jauno programmatūras pakotni (Excel), nevis palaist ar programmu (1-2-3), kurai tas nebija. Šī viena iezīme, kā arī nepareizs Windows panākumu novērtējums, bija Lotus 1-2-3 nāves knell un Microsoft Excel panākumu sākums.
PivotTables izpratne
Tātad, kas ir PivotTable, tieši tā?
Vienkārši sakot, PivotTable ir kopsavilkums par dažiem datiem, kas izveidoti, lai ļautu viegli analizēt minētos datus. Bet atšķirībā no manuāli izveidotā kopsavilkuma, Excel PivotTables ir interaktīvas. Kad esat to izveidojis, to var viegli mainīt, ja tas nepiedāvā precīzu ieskatu jūsu gaidītajos datos. Pāris klikšķu laikā kopsavilkums var būt "pagriezts" - pagriezts tā, ka kolonnu virsraksti kļūst par rindu virsrakstiem un otrādi. Tas ir daudz vairāk, ko var izdarīt arī. Tā vietā, lai mēģinātu aprakstīt visas PivotTables funkcijas, mēs vienkārši parādīsim viņiem ...
Datu, ko analizējat, izmantojot PivotTable, nevar būt vienkārši jebkura dati - tam jābūt neapstrādāts dati, kas iepriekš nebija apstrādāti (nesammināros), parasti dažu kategoriju saraksts. Piemērs tam varētu būt pārdošanas darījumu saraksts uzņēmumā pēdējo sešu mēnešu laikā.
Pārbaudiet tālāk norādītos datus.
Ievērojiet, ka tas ir nē neapstrādāti dati. Patiesībā tas jau ir sava veida kopsavilkums. Šūnā B3 mēs varam redzēt $ 30000, kas, šķiet, ir James Cook kopējais pārdošanas apjoms janvāra mēnesī. Tātad, kur ir neapstrādāti dati? Kā mēs sasniedzām 30 000 dolāru lielu skaitli? Kur ir sākotnējais pārdošanas darījumu saraksts, no kura tika iegūts šis skaitlis? Ir skaidrs, ka kaut kur kaut kas, iespējams, ir nonācis pie grūtībām apkopot visus pārdošanas darījumus pēdējo sešu mēnešu laikā kopsavilkumā, ko mēs redzam iepriekš. Cik ilgi jūs domājat, ka tas notika? Stunda? Desmit
Iespējams, jā. Redzi, iepriekšējā izklājlapa patiešām ir nē PivotTable. Tas tika izveidots manuāli no neapstrādātiem datiem, kas glabājas citur, un apkopošanai patiešām vajadzēja pāris stundas. Tomēr tas ir tieši tāds kopsavilkums par to varētu var izveidot, izmantojot PivotTables, tādā gadījumā tas būtu vajadzīgs tikai dažas sekundes. Noskaidrosim, kā ...
Ja mēs būtu izsekojuši sākotnējo pārdošanas darījumu sarakstu, tas varētu izskatīties šādi:
Jūs, iespējams, būsiet pārsteigti, uzzināsim, ka, izmantojot Excel pivotTable funkciju, 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 izdarīt - un vēl daudz vairāk!
Kā izveidot pivotTable
Vispirms pārliecinieties, ka Excel darblapā ir neapstrādāti dati. Finanšu darījumu saraksts ir raksturīgs, taču tas var būt tikai jebkura informācija: darbinieku kontaktinformācija, kompaktdisku kolekcija vai degvielas patēriņa dati par jūsu uzņēmuma automobiļu parku.
Tātad mēs sākam Excel ... un mēs ielādējam šādu sarakstu ...
Kad mums saraksts ir atvērts programmā Excel, mēs esam gatavi sākt veidot PivotTable.
Noklikšķiniet uz jebkura viena saraksta šūna:
Tad, no Ievietot cilni noklikšķiniet uz PivotTable ikona:
The Izveidojiet pivotTable parādās lodziņš, kurā tika uzdoti divi jautājumi. Kādus datus vajadzētu balstīt uz jūsu jauno PivotTable un kur to izveidot? Tā kā mēs jau noklikšķinājām uz šūnas sarakstā (iepriekš minētajā solī), viss saraksts, kas ap šo šūnu, jau ir izvēlēts mums ($ A $ 1: $ G $ 88 uz Maksājumi lapa šajā piemērā). Ņemiet vērā, ka mēs varētu atlasīt sarakstu jebkurā citā citā darblapā vai pat kādā ārējā datu avotā, piemēram, Access datu bāzes tabulā vai pat MS-SQL Server datu bāzes tabulā. Mums arī jāizvēlas, vai mēs vēlamies izveidot mūsu jauno PivotTable vietnē jauns darblapā vai uz esošās vienu. Šajā piemērā mēs izvēlamies a jauns viens:
Mūsu jaunā darba lapa ir izveidota, un šajā darblapā ir izveidots tukšs PivotTable:
Parādās arī cits lodziņš: The PivotTable lauku saraksts. Šis lauku saraksts tiks parādīts ikreiz, kad mēs noklikšķināsim uz jebkura šūna PivotTable (iepriekš):
Lauku saraksts lodziņa augšdaļā faktiski ir kolonnu virsrakstu kolekcija no sākotnējā izejas datu lapas. Četras tukšās lodziņas ekrāna apakšējā daļā ļauj mums izvēlēties veidu, kā mēs vēlētos, lai mūsu PivotTable apkopotu sākotnējos datus. Līdz šim šajās kastēs nav nekas, tāpēc PivotTable ir tukša. Viss, kas mums jādara, ir vilkt laukus no saraksta augšpusē un nometiet tos zemāk esošajos lodziņos. Tad automātiski izveido PivotTable, lai atbilstu mūsu norādījumiem. Ja mēs to nepareizi, mums tikai jāpārvelk lauki atpakaļ uz to, no kurienes viņi nāca un / vai velciet jauns laukus uz leju, lai tos nomainītu.
The Vērtības kas ir vissvarīgākais no četriem. Laukā, kas tiek vilktas šajā lodziņā, ir dati, kas kaut kādā veidā jāapkopo (summējot, vidējo rādītāju, maksimuma, minimuma utt. Atrašanai). Tas ir gandrīz vienmēr skaitliski datus. Ideāls kandidāts šai lodziņā mūsu izlases datos ir lauks / sleja "Summa". Liekam vilkt šo lauku Vērtības kaste:
Ņemiet vērā, ka a) laukā "Daudzums" laukā ir atzīmēts, un "Summa Summa" ir pievienota Vērtības kas norāda, ka summas sleja ir summēta.
Ja mēs pārbaudīsim PivotTable pati, mēs patiešām atrodam visu "Summa" vērtību summu no neapstrādātu datu lapas:
Esam izveidojuši mūsu pirmo PivotTable! Ērts, bet ne īpaši iespaidīgs. Iespējams, ka mums ir vajadzīgs nedaudz vairāk informācijas par mūsu datiem.
Atsaucoties uz mūsu izlases datiem, mums ir jānosaka viena vai vairākas sleju virsraksti, ko mēs varētu iedomāties izmantot, lai sadalītu šo kopsummu. Piemēram, mēs varam izlemt, ka mēs gribētu redzēt mūsu datu kopsavilkumu, kurā mums ir rindu virsraksts par katru no mūsu uzņēmuma dažādajiem pārdevējiem, un par katru no tiem. Lai to paveiktu, viss, kas mums jādara, ir vilkt "Field Salesperson" lauku uz Rindu uzlīmes kaste:
Tagad, beidzot, lietas kļūst interesantas! Mūsu PivotTable sāk veidoties ...
Ar pāris klikšķiem esam izveidojuši tabulu, kas manuāli veiktu ilgu laiku.
Tātad, ko vēl mēs varam darīt? Nu, vienā ziņā mūsu PivotTable ir pabeigta. Esam izveidojuši noderīgu mūsu avota datu kopsavilkumu. Svarīgie elementi jau ir iemācījušies! Pārējā rakstā mēs apskatīsim dažus veidus, kā izveidot sarežģītākus PivotTables un veidus, kā šos PivotTables var pielāgot.
Pirmkārt, mēs varam izveidot a divutrīsdimensiju tabula. Darīsim to, izmantojot sleju "Maksājumu metode". Vienkārši velciet virsrakstu "Maksājumu metode" uz Slejas etiķetes kaste:
Kas izskatās šādi:
Sāk saņemt ļoti atdzesē
Padarēsim a trīs-dimensiju galds. Kā varētu izskatīties šāds galds? Nu, redzēsim ...
Velciet sleju / virsrakstu "Iepakojums" uz Ziņojuma filtrs kaste:
Paziņojums, kur tas beidzas ...
Tas ļauj mums filtrēt mūsu pārskatu, pamatojoties uz to, kurš tika iegādāts "brīvdienu paketes". Piemēram, mēs varam redzēt pārdevēju sadalījumu salīdzinājumā ar maksājuma veidu visi paketes vai ar pāris klikšķiem mainiet to, lai parādītu to pašu sadalījumu paketei "Sunseekers":
Un tā, ja jūs domājat par to pareizi, mūsu PivotTable tagad ir trīsdimensiju. Turpināsim pielāgot ...
Ja izrādās, teiksim, ka mēs tikai vēlamies redzēt čeku un kredītkarti darījumi (t.i., bez skaidras naudas darījumiem), tad mēs varam no slejas pozīcijās atcelt izvēles rūtiņu Nauda. Noklikšķiniet uz nolaižamās bultiņas blakus Slejas etiķetes, un noņemiet atzīmi "Nauda":
Apskatīsim, kas tas izskatās ... Kā redzat, "Cash" ir pazudis.
Formatēšana
Tas, protams, ir ļoti spēcīga sistēma, taču līdz šim rezultāti izskatās ļoti skaidri un garlaicīgi. Lai sāktu, skaitļi, kurus mēs apkopojam, neizskatās kā dolāru summas - vienkārši veci numuri. Labosim to.
Kārdinājums varētu būt darīt to, ko mēs šādos apstākļos esam izmantojuši, un vienkārši atlasiet visu tabulu (vai visu darblapu) un izmantojiet standarta numura formatēšanas pogas rīkjoslā, lai pabeigtu formatējumu. Problēma ar šo pieeju ir tāda, ka, ja jūs kādreiz maināt PivotTable struktūru nākotnē (kas ir 99% iespējams), tad šie skaitļu formāti tiks zaudēti. Mums ir vajadzīgs veids, kā padarīt tos (daļēji) pastāvīgus.
Pirmkārt, mēs atrodam ierakstu "Summa Summa" Vērtības lodziņā un noklikšķiniet uz tā. Parādās izvēlne. Mēs izvēlamies Vērtību lauka iestatījumi ... no izvēlnes:
The Vērtības lauka iestatījumi parādās lodziņš.
Noklikšķiniet uz Ciparu formāts pogu un standartu Formatēt šūnu kastīti parādās:
No Kategorija sarakstā izvēlieties (teiksim) Grāmatvedība, un nometiet decimāldaļu skaitu līdz 0. Noklikšķiniet uz labi dažas reizes, lai atgrieztos PivotTable ...
Kā redzat, šie skaitļi ir pareizi formatēti kā dolāru summas.
Lai gan mēs esam par formātu, formatēsim visu PivotTable. Ir daži veidi, kā to izdarīt. Izmantosim vienkāršu ...
Noklikšķiniet uz PivotTable Tools / Design cilnē:
Pēc tam nolaižamās bultiņas apakšējā labajā stūrī nolaižat PivotTable stili lai redzētu plašu iebūvēto stilu kolekciju:
Izvēlieties kādu, kas pievilina, un skatiet rezultātu savā PivotTable:
Citas iespējas
Mēs varam strādāt arī ar datumiem. Tagad parasti darījumu sarakstā ir daudz, daudzi datumi, piemēram, tie, ar kuriem sākām darbu. Bet Excel nodrošina iespēju grupēt datu vienumus pa dienu, nedēļu, mēnesi, gadu utt. Apskatīsim, kā tas tiek darīts.
Vispirms noņemiet sleju "Maksājumu metode" no Slejas etiķetes lodziņā (vienkārši velciet to atpakaļ uz lauka sarakstu) un aizvietojiet to ar sleju "Datums rezervēts":
Kā jūs varat redzēt, tas padara mūsu PivotTable uzreiz bezjēdzīgi, dodot mums vienu kolonnu katram darījuma datumam - ļoti plaša tabula!
Lai to novērstu, ar peles labo pogu noklikšķiniet uz jebkura datuma un atlasiet 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 vairāk noderīga tabula:
(Starp citu, šī tabula ir gandrīz identiska tai, kas parādīta šī raksta sākumā - sākotnējais pārdošanas kopsavilkums, kas tika izveidots manuāli.)
Vēl viena atrisināta lieta, ka jums var būt vairāk nekā viena rindu virsrakstu kopuma (vai sleju virsraksti):
... kas izskatās šādi ...
Varat veikt līdzīgu darbību ar kolonnu virsrakstiem (vai pat ziņojumu filtriem).
Turot lietas vienkārši atkal, redzēsim, kā apgūt vidējais rādītājs vērtības, nevis summētās vērtības.
Vispirms noklikšķiniet uz "Summa Summa" un atlasiet Vērtību lauka iestatījumi ... no redzamās konteksta izvēlnes:
Iekš Apkopo vērtības lauku ar sarakstā Vērtības lauka iestatījumi lodziņā atlasiet Vidēji:
Lai gan mēs esam šeit, mainīsim Pielāgots nosaukums, no "Vidējā summa" uz kaut ko nedaudz daudz īsāku. Ievadiet kaut ko līdzīgu "Avg":
Klikšķis labiun redzēt, kā tas izskatās. Ievērojiet, ka visas vērtības mainās no summām uz vidējiem rādītājiem, un tabulas nosaukums (augšējā kreisā šūna) ir mainīts uz "Avg":
Ja mums patīk, mēs varam pat iegūt summas, vidējos rādītājus un skaitļus (skaits = cik pārdošanas bija) viss vienā PivotTable!
Tālāk ir norādīti soļi, lai izveidotu kaut ko līdzīgu (sākot ar tukšu PivotTable):
no lauka no Slejas etiķetes uz Rindu uzlīmesLūk, ko mēs galu galā:
Kopā, vidēji un rēķināties ar to pašu PivotTable!
Secinājums
Microsoft Excel izveidotais PivotTables ir daudz, daudz vairāk iespēju un iespēju, kas ir pārāk daudz, lai to varētu iekļaut tādā rakstā kā šis. Lai pilnībā aptvertu PivotTables potenciālu, būtu nepieciešama neliela grāmata (vai liela vietne). Drosmīgi un / vai geeky lasītāji var izpētīt PivotTables tālāk diezgan viegli: Vienkārši ar peles labo pogu noklikšķiniet uz gandrīz visu, un redzēt, kādas iespējas jums kļūst pieejamas. Ir arī divas lentes cilnes: PivotTable rīki / opcijas un Dizains. Nav svarīgi, vai jūs pieļāvāt kļūdu - viegli izdzēst PivotTable un sākt no jauna - iespējams, ka vecie Lotus 1-2-3 DOS lietotāji nekad nav bijuši.
Ja jūs strādājat programmā Office 2007, iespējams, vēlēsieties iepazīties ar mūsu rakstu par to, kā izveidot PivotTable programmā Excel 2007.
Mēs esam iekļāvuši Excel darbgrāmatu, kuru varat lejupielādēt, lai praksē izmantotu savas PivotTable prasmes. Tam vajadzētu strādāt ar visām Excel versijām sākot no 97 gadiem.
Lejupielādējiet mūsu Practice Excel darbgrāmatu
