Saturs
Pivot galdi ir viens no jaudīgākajiem Excel rīkiem. Tie ļauj analizēt un apkopot dažādus lielu datu apjomu kopsavilkumus tikai ar dažiem peles klikšķiem. Šajā rakstā mēs iepazīsimies ar rakurstabulām, sapratīsim, kas tās ir, uzzināsim, kā tās izveidot un pielāgot.
Šis raksts tika uzrakstīts, izmantojot programmu Excel 2010. Rakurstabulu koncepcija gadu gaitā nav daudz mainījusies, taču to izveides veids katrā jaunajā Excel versijā ir nedaudz atšķirīgs. Ja jums ir Excel versija, nevis 2010, esiet gatavi tam, ka šajā rakstā redzamie ekrānuzņēmumi atšķirsies no ekrānā redzamajiem.
Mazliet vēstures
Izklājlapu programmatūras sākuma dienās Lotus 1-2-3 noteikumu bumba. Tā dominēšana bija tik pilnīga, ka Microsoft centieni izstrādāt savu programmatūru (Excel) kā alternatīvu Lotus šķita kā laika izšķiešana. Tagad ātri uz priekšu uz 2010. gadu! Excel dominē izklājlapās vairāk nekā Lotus kods jebkad savā vēsturē, un to cilvēku skaits, kuri joprojām izmanto Lotus, ir tuvu nullei. Kā tas varēja notikt? Kāds bija iemesls šādam dramatiskam notikumu pavērsienam?
Analītiķi identificē divus galvenos faktorus:
- Pirmkārt, Lotus nolēma, ka šī jaunizveidotā GUI platforma ar nosaukumu Windows ir tikai pārejoša iedoma, kas nebūs ilga. Viņi atteicās izveidot Lotus 1-2-3 Windows versiju (bet tikai dažus gadus), paredzot, ka viņu programmatūras DOS versija būs viss, kas patērētājiem būs vajadzīgs. Microsoft, protams, izstrādāja programmu Excel īpaši operētājsistēmai Windows.
- Otrkārt, Microsoft programmā Excel ieviesa rīku PivotTables, kas nebija pieejams Lotus 1-2-3. Rakurstabulas, kas ir ekskluzīvas programmā Excel, izrādījās tik ļoti noderīgas, ka cilvēki mēdza pieturēties pie jaunā Excel programmatūras komplekta, nevis turpināt darbu ar Lotus 1-2-3, kurā to nebija.
Rakurstabulas, kā arī Windows panākumu nenovērtēšana kopumā, spēlēja nāves gājienu Lotus 1-2-3 un ievadīja Microsoft Excel panākumus.
Kas ir rakurstabulas?
Tātad, kāds ir labākais veids, kā raksturot rakurstabulas?
Vienkārši izsakoties, rakurstabulas ir dažu datu kopsavilkumi, kas izveidoti, lai atvieglotu šo datu analīzi. Atšķirībā no manuāli izveidotajām summām, Excel rakurstabulas ir interaktīvas. Kad tie ir izveidoti, varat tos viegli modificēt, ja tie nesniedz vēlamo attēlu. Tikai ar pāris peles klikšķiem kopsummas var apgriezt tā, lai kolonnu virsraksti kļūtu par rindu virsrakstiem un otrādi. Ar rakurstabulām varat paveikt daudzas lietas. Tā vietā, lai mēģinātu vārdos aprakstīt visas rakurstabulu funkcijas, ir vieglāk to demonstrēt praksē...
Dati, kurus analizējat, izmantojot rakurstabulas, nevar būt nejauši. Tam vajadzētu būt neapstrādātiem datiem, piemēram, sava veida sarakstam. Piemēram, tas varētu būt uzņēmuma pēdējo sešu mēnešu pārdošanas darījumu saraksts.
Apskatiet tālāk attēlā redzamos datus:
Ņemiet vērā, ka tie nav neapstrādāti neapstrādāti dati, jo tie jau ir apkopoti. Šūnā B3 mēs redzam 30000 30000 $, kas, iespējams, ir Džeimsa Kuka kopējais rezultāts janvārī. Kur tad ir sākotnējie dati? No kurienes radās XNUMX XNUMX USD skaitlis? Kur ir sākotnējais pārdošanas darījumu saraksts, no kura tika iegūta šī mēneša kopsumma? Ir skaidrs, ka kāds ir paveicis lielisku darbu, organizējot un sakārtojot visus pārdošanas datus par pēdējo pusgadu un pārvēršot tos kopsummas tabulā, ko mēs redzam. Kā jūs domājat, cik ilgi tas prasīja? Stunda? pulksten desmitos?
Fakts ir tāds, ka iepriekš minētā tabula nav rakurstabula. Tas tika izveidots ar rokām no citur glabātiem neapstrādātiem datiem, un tā apstrāde prasīja vismaz pāris stundas. Tikai šādu kopsavilkuma tabulu var izveidot tikai dažu sekunžu laikā, izmantojot rakurstabulas. Izdomāsim, kā…
Ja atgriezīsimies pie sākotnējā pārdošanas saraksta, tas izskatītos apmēram šādi:
Jūs varētu būt pārsteigts, ka no šī darījumu saraksta ar rakurstabulu palīdzību un tikai dažu sekunžu laikā mēs varam izveidot ikmēneša pārdošanas pārskatu programmā Excel, kuru mēs analizējām iepriekš. Jā, mēs varam to un vēl vairāk!
Kā izveidot rakurstabulu?
Vispirms pārliecinieties, vai Excel lapā ir daži avota dati. Finanšu darījumu saraksts ir visizplatītākais, kas notiek. Faktiski tas varētu būt visu veidu saraksts: darbinieku kontaktinformācija, kompaktdisku kolekcija vai jūsu uzņēmuma degvielas patēriņa dati.
Tātad, mēs startējam programmu Excel ... un ielādējam šādu sarakstu ...
Pēc šī saraksta atvēršanas programmā Excel mēs varam sākt veidot rakurstabulu.
Izvēlieties jebkuru šūnu no šī saraksta:
Pēc tam cilnē Ievietošana (Ievietot) atlasiet komandu Rakurstabula (Rakurstabula):
Parādīsies dialoglodziņš Rakurstabulas izveide (Rakurstabulas izveide) ar diviem jautājumiem jums:
- Kādus datus izmantot, lai izveidotu jaunu rakurstabulu?
- Kur likt rakurstabulu?
Tā kā iepriekšējā darbībā mēs jau esam atlasījuši vienu no saraksta šūnām, viss saraksts tiks automātiski atlasīts, lai izveidotu rakurstabulu. Ņemiet vērā, ka mēs varam atlasīt citu diapazonu, citu tabulu un pat dažus ārējus datu avotus, piemēram, Access vai MS-SQL datu bāzes tabulu. Turklāt mums ir jāizvēlas, kur novietot jauno rakurstabulu: uz jaunas lapas vai uz kādas no esošajām. Šajā piemērā mēs izvēlēsimies opciju - Jauna darblapa (uz jaunu lapu):
Excel izveidos jaunu lapu un ievietos tajā tukšu rakurstabulu:
Tiklīdz mēs noklikšķināsim uz jebkuras rakurstabulas šūnas, parādīsies cits dialoglodziņš: Rakurstabulas lauku saraksts (Rakurstabulas lauki).
Lauku saraksts dialoglodziņa augšdaļā ir visu oriģinālā saraksta nosaukumu saraksts. Četri tukši apgabali ekrāna apakšdaļā ļauj norādīt rakurstabulai, kā vēlaties apkopot datus. Kamēr šīs zonas ir tukšas, tabulā arī nekā nav. Viss, kas mums jādara, ir velciet virsrakstus no augšējā apgabala uz tukšajiem apgabaliem zemāk. Tajā pašā laikā saskaņā ar mūsu norādījumiem tiek automātiski ģenerēta rakurstabula. Ja pieļaujam kļūdu, varam noņemt virsrakstus no apakšējā apgabala vai vilkt citus, lai tos aizstātu.
Platība Vērtības (Nozīmes), iespējams, ir vissvarīgākā no četrām. Kurš virsraksts ir ievietots šajā apgabalā, nosaka, kādi dati tiks apkopoti (summa, vidējais, maksimālais, minimums utt.) Tās gandrīz vienmēr ir skaitliskās vērtības. Lielisks kandidāts uz vietu šajā jomā ir dati zem virsraksta summa (Izmaksas) no mūsu sākotnējās tabulas. Velciet šo nosaukumu uz apgabalu Vērtības (Vērtības):
Lūdzu, ņemiet vērā, ka virsraksts summa tagad ir atzīmēts ar atzīmi un apgabalā Vērtības (Vērtības) ir parādījies ieraksts Summas summa (Summa lauks Summa), norādot, ka kolonna summa summē.
Ja skatāmies uz pašu rakurstabulu, mēs redzēsim visu kolonnas vērtību summu summa oriģinālais galds.
Tātad, mūsu pirmā rakurstabula ir izveidota! Ērts, bet ne īpaši iespaidīgs. Mēs, iespējams, vēlamies iegūt vairāk informācijas par saviem datiem, nekā mums pašlaik ir.
Pievērsīsimies sākotnējiem datiem un mēģināsim identificēt vienu vai vairākas kolonnas, kuras var izmantot šīs summas sadalīšanai. Piemēram, mēs varam izveidot savu rakurstabulu tā, lai kopējais pārdošanas apjoms tiktu aprēķināts katram pārdevējam atsevišķi. Tie. rindas tiks pievienotas mūsu rakurstabulai ar katra uzņēmuma pārdevēja vārdu un kopējo pārdošanas apjomu. Lai sasniegtu šo rezultātu, vienkārši velciet virsrakstu pārdevējs (tirdzniecības pārstāvis) uz reģionu Rindu etiķetes (stīgas):
Kļūst interesantāk! Mūsu rakurstabula sāk veidoties…
Vai redzat priekšrocības? Ar pāris klikšķiem mēs izveidojām tabulu, kuras manuāla izveidošana būtu prasījusi ļoti ilgu laiku.
Ko vēl mēs varam darīt? Savā ziņā mūsu rakurstabula ir gatava. Mēs esam izveidojuši noderīgu sākotnējo datu kopsavilkumu. Svarīga informācija jau saņemta! Pārējā šī raksta daļā apskatīsim dažus veidus, kā izveidot sarežģītākas rakurstabulas, kā arī uzzināsim, kā tās pielāgot.
Rakurstabulas iestatīšana
Pirmkārt, mēs varam izveidot divdimensiju rakurstabulu. Darīsim to, izmantojot kolonnas virsrakstu Apmaksas veids (Apmaksas veids). Vienkārši velciet virsrakstu Apmaksas veids uz apgabalu Kolonnu etiķetes (Slejas):
Mēs iegūstam rezultātu:
Izskatās ļoti forši!
Tagad izveidosim trīsdimensiju tabulu. Kā izskatītos šāds galds? Paskatīsimies…
Velciet galveni Pakete (Komplekss) uz apgabalu pārskatu filtri (Filtri):
Ņemiet vērā, kur viņš atrodas…
Tas dod mums iespēju filtrēt pārskatu, pamatojoties uz “Par kuru brīvdienu kompleksu tika samaksāts”. Piemēram, mēs varam redzēt sadalījumu pa pārdevējiem un maksājumu veidiem visiem kompleksiem vai ar pāris peles klikšķiem mainīt rakurstabulas skatu un parādīt to pašu sadalījumu tikai tiem, kas pasūtīja kompleksu. Saules meklētāji.
Tātad, ja jūs to pareizi saprotat, mūsu rakurstabulu var saukt par trīsdimensiju. Turpināsim iestatīšanu…
Ja pēkšņi izrādās, ka rakurstabulā ir jāparāda tikai maksājums ar čeku un kredītkarti (tas ir, bezskaidras naudas maksājums), tad virsraksta rādīšanu varam izslēgt. Nauda (Skaidra nauda). Šim, blakus Kolonnu etiķetes noklikšķiniet uz lejupvērstās bultiņas un nolaižamajā izvēlnē noņemiet atzīmi no izvēles rūtiņas Nauda:
Apskatīsim, kā tagad izskatās mūsu rakurstabula. Kā redzat, kolonna Nauda pazuda no viņas.
Rakurstabulu formatēšana programmā Excel
Rakurstabulas acīmredzami ir ļoti spēcīgs rīks, taču līdz šim rezultāti izskatās nedaudz vienkārši un garlaicīgi. Piemēram, skaitļi, kurus mēs saskaitām, neizskatās pēc dolāriem — tie ir tikai skaitļi. Labosim šo.
Ir vilinoši šādā situācijā darīt to, pie kā esat pieradis, un vienkārši atlasīt visu tabulu (vai visu lapu) un izmantot standarta skaitļu formatēšanas pogas rīkjoslā, lai iestatītu vēlamo formātu. Šīs pieejas problēma ir tāda, ka, ja kādreiz nākotnē mainīsit rakurstabulas struktūru (kas notiek ar 99% iespējamību), formatējums tiks zaudēts. Mums ir nepieciešams veids, kā padarīt to (gandrīz) pastāvīgu.
Vispirms atradīsim ierakstu Summas summa in Vērtības (Vērtības) un noklikšķiniet uz tā. Parādītajā izvēlnē atlasiet vienumu Vērtības lauka iestatījumi (Vērtības lauka opcijas):
Parādīsies dialoglodziņš Vērtības lauka iestatījumi (Vērtību lauka opcijas).
prese Numuru formāts (Numura formāts), tiks atvērts dialoglodziņš. Formatēt šūnas (šūnas formāts):
No saraksta Kategorija (Ciparu formāti) atlasiet grāmatvedība (Finanšu) un iestatiet zīmju aiz komata skaitu uz nulli. Tagad nospiediet dažas reizes OKlai atgrieztos pie mūsu rakurstabulas.
Kā redzat, skaitļi ir formatēti kā dolāru summas.
Kamēr strādājam pie formatēšanas, iestatīsim visas rakurstabulas formātu. Ir vairāki veidi, kā to izdarīt. Mēs izmantojam vienkāršāku…
Noklikšķiniet Rakurstabulas rīki: dizains (Darbs ar rakurstabulām: konstruktors):
Pēc tam izvērsiet izvēlni, noklikšķinot uz bultiņas sadaļas apakšējā labajā stūrī Rakurstabulas stili (PivotTable Styles), lai skatītu plašo iekļauto stilu kolekciju:
Izvēlieties jebkuru piemērotu stilu un skatiet rezultātu savā rakurstabulā:
Citi rakurstabulas iestatījumi programmā Excel
Dažreiz jums ir jāfiltrē dati pēc datumiem. Piemēram, mūsu darījumu sarakstā ir daudz, daudz datumu. Excel nodrošina rīku datu grupēšanai pēc dienas, mēneša, gada un tā tālāk. Paskatīsimies, kā tas ir paveikts.
Vispirms noņemiet ierakstu. Apmaksas veids no reģiona Kolonnu etiķetes (Slejas). Lai to izdarītu, velciet to atpakaļ uz nosaukumu sarakstu un tā vietā pārvietojiet nosaukumu Rezervācijas datums (rezervācijas datums):
Kā redzat, tas īslaicīgi padarīja mūsu rakurstabulu nederīgu. Excel izveidoja atsevišķu kolonnu katram datumam, kurā tika veikts darījums. Rezultātā mēs saņēmām ļoti plašu galdu!
Lai to labotu, ar peles labo pogu noklikšķiniet uz jebkura datuma un konteksta izvēlnē atlasiet GROUP (Grupa):
Parādīsies grupēšanas dialoglodziņš. Mēs izvēlamies Mēneši (Mēneši) un noklikšķiniet OK:
Voila! Šī tabula ir daudz noderīgāka:
Starp citu, šī tabula ir gandrīz identiska tai, kas parādīta raksta sākumā, kur pārdošanas kopsummas tika apkopotas manuāli.
Ir vēl viens ļoti svarīgs punkts, kas jums jāzina! Varat izveidot ne vienu, bet vairākus rindu (vai kolonnu) virsrakstu līmeņus:
... un tas izskatīsies šādi...
To pašu var izdarīt ar kolonnu virsrakstiem (vai pat filtriem).
Atgriezīsimies pie sākotnējās tabulas formas un redzēsim, kā summas vietā parādīt vidējos rādītājus.
Lai sāktu, noklikšķiniet uz Summas summa un parādītajā izvēlnē atlasiet Vērtības lauka iestatījumi (Vērtības lauka opcijas):
Saraksts Apkopojiet vērtību lauku pēc (Darbība) dialoglodziņā Vērtības lauka iestatījumi (Vērtības lauka opcijas) atlasiet vidējais (vidēji):
Tajā pašā laikā, kamēr esam šeit, mainīsimies CustomName (Pielāgots nosaukums) ar Vidējā summa (Summa lauks Summa) uz kaut ko īsāku. Ievadiet šajā laukā kaut ko līdzīgu Vid:
prese OK un paskaties, kas notiek. Ņemiet vērā, ka visas vērtības ir mainītas no kopējām vērtībām uz vidējām vērtībām, un tabulas galvene (augšējā kreisajā šūnā) ir mainīta uz Vid:
Ja vēlaties, varat uzreiz iegūt summu, vidējo un skaitu (pārdošanu) ievietot vienā pivot tabulā.
Šeit ir sniegts soli pa solim ceļvedis, kā to izdarīt, sākot ar tukšu rakurstabulu.
- Velciet galveni pārdevējs (tirdzniecības pārstāvis) uz reģionu Kolonnu etiķetes (Slejas).
- Velciet virsrakstu trīs reizes summa (Izmaksas) uz platību Vērtības (Vērtības).
- Pirmajam laukam summa mainiet virsrakstu uz Kopā (Summa), un skaitļa formāts šajā laukā ir grāmatvedība (Finanšu). Ciparu aiz komata skaits ir nulle.
- Otrais lauks summa nosaukums Vidējie, iestatiet tai darbību vidējais (Vidējs) un skaitļu formāts šajā laukā arī mainās uz grāmatvedība (Finanšu) ar nulli aiz komata.
- Trešajam laukam summa uzstādiet nosaukumu Skaitīt un operācija viņam - Skaitīt (Daudzums)
- Iekš Kolonnu etiķetes Lauks (Slejas) tiek izveidots automātiski Σ Vērtības (Σ Vērtības) – velciet to uz apgabalu Rindu etiķetes (Līnijas)
Lūk, ar ko mēs beigsies:
Kopējā summa, vidējā vērtība un pārdošanas apjoms – viss vienā rakurstabulā!
Secinājumi
Rakurstabulās programmā Microsoft Excel ir daudz līdzekļu un iestatījumu. Tik mazā rakstā viņi pat ne tuvu nav, lai aptvertu visus. Lai pilnībā aprakstītu visas rakurstabulu iespējas, būtu nepieciešama neliela grāmata vai liela vietne. Drosmīgi un zinātkāri lasītāji var turpināt rakurstabulu izpēti. Lai to izdarītu, ar peles labo pogu noklikšķiniet uz gandrīz jebkura rakurstabulas elementa un skatiet, kuras funkcijas un iestatījumi tiek atvērti. Lentē atradīsit divas cilnes: Rakurstabulas rīki: opcijas (analīze) un Dizains (Konstruktors). Nebaidieties kļūdīties, jūs vienmēr varat izdzēst rakurstabulu un sākt no jauna. Jums ir iespēja, kāda ilgstošiem DOS un Lotus 1-2-3 lietotājiem nekad nav bijusi.