Pivot tabulas ir viena no visspēcīgākajām Excel funkcijām. Pat ja esat iesācējs, jūs varat sasmalcināt lielu datu daudzumu noderīgā informācijā. Pivot tabula var palīdzēt jums izveidot pārskatus dažu minūšu laikā. Viegli analizējiet savus tīros datus, un, ja dati nav tīri, tas var palīdzēt jums notīrīt savus datus. Es nevēlos tevi garlaikot, tāpēc iesim tajā un izpētīsim.
Kā izveidot rakurstabulu
Tas ir vienkārši. Vienkārši atlasiet savus datus. Dodieties uz Ievietot. Noklikšķiniet uz rakurstabulas un tas ir izdarīts.
Bet pagaidi. Pirms rakurstabulas izveidošanas pārliecinieties, vai visām kolonnām ir virsraksts.
Ja kāds kolonnas virsraksts tiek atstāts tukšs, rakurstabula netiks izveidota un tiks parādīts kļūdas ziņojums.
1. prasība: lai sāktu darbu ar rakurstabulām programmā Excel, visām slejām jābūt virsrakstam
Jums vajadzētu sakārtot savus datus ar pareizu virsrakstu. Kad esat to ieguvis, varat ievietot rakurstabulu.
Ievietojiet rakurstabulu no lentes
Lai no izvēlnes ievietotu rakurstabulu, rīkojieties šādi:
1. Izvēlieties savu datu diapazonu
2. Dodieties uz ievietošanas cilni
3. Noklikšķiniet uz rakurstabulas ikonas.
4. Parādīsies opcijas lodziņš Izveidot rakurstabulu.
5. Šeit jūs varat redzēt atlasīto datu diapazonu. Ja domājat, ka šis nav diapazons, kuru vēlaties atlasīt, tieši mainiet diapazona izmērus no šejienes, nevis atgriezieties un vēlreiz atlasiet datus.
6. Pēc tam varat izvēlēties, kur vēlaties pagriezt tabulu. Es iesaku izmantot jauno darblapu, bet varat izmantot arī pašreizējo darblapu. Vienkārši definējiet atrašanās vietu lodziņā Atrašanās vieta.
7. Tagad, ja esat pabeidzis iestatījumus, nospiediet pogu Labi. Pivot tabula būs jauna lapa. Vienkārši atlasiet savus laukus kopsavilkumiem. Mēs redzēsim, kā mēs veidojam datu kopsavilkumu, izmantojot rakurstabulu, bet vispirms noskaidrosim pamatus. Šajā Excel rakurstabulu apmācībā jūs uzzināsit vairāk, nekā gaidījāt.
Ievietot rakurstabulas saīsni (Alt> N> V)
Šis ir secīgs īsinājumtaustiņš, lai atvērtu Izveidot Pivot tabula opciju kaste.
Nospiediet pogu Alt un atlaidiet to. Sist N un atlaidiet to. Sist V un atlaidiet to. Tiks atvērts opcija Izveidot rakurstabulu.
Tagad vienkārši izpildiet iepriekš minēto procedūru, lai izveidotu Excel rakurstabulu.
Ievietojiet rakurstabulas saīsni, izmantojot veco Excel saīsni (Alt> D> P)
Viena lieta, kas man visvairāk patīk programmā Microsoft Excel, ir tā, ka katrā jaunā Excel versijā tie ievieš jaunas funkcijas, taču tās neatmetiet vecās funkcijas (kā to darīja MS ar uzvaru 8. Tas bija nožēlojami). Šī ļauj vecajiem lietotājiem normāli strādāt ar jaunām versijām, kā viņi strādāja ar vecākām versijām.
Ja secīgi nospiežat ALT, D un P. uz tastatūras tiks atvērta programma Excel, lai izveidotu rakurstabulas vedni.
Izvēlieties atbilstošo opciju. Iepriekš redzamajā ekrānuzņēmumā atlasītā opcija liks mums izveidot rakurstabulu, kā mēs izveidojām iepriekš.
Nospiediet Enter vai noklikšķiniet uz Tālāk, ja vēlaties pārbaudīt izvēlēto diapazonu.
Vēlreiz nospiediet taustiņu Enter.
Atlasiet jaunu darblapu vai visur, kur vēlaties, lai jūsu rakurstabula tiktu nospiesta Enter. Un tas ir izdarīts.
Izveidojiet pārskatus, izmantojot rakurstabulas
Tagad jūs zināt, kā ievietot šarnīra tabulu. Sāksim sagatavot pārskatus, izmantojot rakurstabulas dažu minūšu laikā.
Mums ir dati par stacionāro pasūtījumu.
Kolonnu lauki ir šādi:
Pasūtījuma datums: Pasūtījuma datums (acīmredzot)
Novads: Kārtības reģions valstī
Klients: Klienta vārds (kas vēl var būt)
Lieta: Pasūtītā prece
Vienība: Pasūtītās preces vienību skaits
Vienības cena: Par vienības izmaksām
Kopā: Kopējās pasūtījuma izmaksas (vienība*vienības cena).
Lai izveidotu pārskatus, izmantojot rakurstabulas, mēs izmantosim
Pivot tabulas lauki: Satur datu sleju nosaukumu sarakstu.
Šarnīra apgabali: Šīs 4 jomas tiek izmantotas, lai jūsu dati tiktu parādīti pieklājīgā veidā.
FILTRI: Ievietojiet šeit laukus, no kuriem pārskatā vēlaties lietot filtrus.
SLEJAS. Ievietojiet pārskatā vajadzīgos laukus ziņojuma slejās: (labāk parādīt nekā izskaidrot)
RIJAS: Velciet laukus, kurus vēlaties parādīt neapstrādāti, kā parādīts iepriekš redzamajā attēlā, es esmu parādījis reģionu rindās.
VĒRTĪBAS: Atlasiet lauku, lai iegūtu skaitu, summu, vidējo, procentus (un daudz ko citu) utt., Ko vēlaties redzēt.
Tagad, izmantojot iepriekš minēto informāciju, mēs esam sagatavojuši šo ātro pagrieziena ziņojumu, kurā parādīts, no kura reģiona ir pasūtījumu skaits katram vienumam.
Tagad, kad saprotat savus datus un Pivot laukus (galu galā esat gudrs), ātri atbildēsim uz dažiem jautājumiem, kas saistīti ar šiem datiem, izmantojot rakurstabulu.
1. jautājums. Cik pasūtījumu ir?
Šarnīra tabula sākotnēji ir tukša, kā parādīts attēlā zemāk.
Jums ir jāizvēlas lauki (kolonnu nosaukumi) attiecīgajās zonās, lai redzētu šī lauka kopsavilkumu vai informāciju.
Tagad, lai atbildētu uz iepriekš minēto jautājumu, vērtību laukos es atlasīšu vienumu (atlasiet jebkuru kolonnu, tikai pārliecinieties, vai tajā nav tukšas šūnas).
Lauku sarakstā atlasiet vienumu un velciet to laukā Vērtība.
Mums ir sava atbilde. Pivot tabulas man saka, ka kopumā ir 43 pasūtījumi. Tas ir pareizi.
Tagad, kad esmu izvēlējies Pasūtījuma datums apgabalā Vērtības tas rāda 42. Šī nozīmē Pasūtījuma datums ir tukša šūna kopš mēs zinām, ka kopējais pasūtījumu skaits ir 43.
Identificējiet neregulārus datus, izmantojot rakurstabulas, un notīriet tos.
Pivot tabula var palīdzēt atrast nepareizu informāciju.
Lielāko daļu laika mūsu datus sagatavo datu ievadīšanas operatori vai lietotāji, kuri parasti ir neregulāri un kuriem ir nepieciešama tīrīšana, lai sagatavotu precīzu pārskatu un analīzi.
Pirms pārskatu sagatavošanas jums vienmēr ir jātīra un jāsagatavo savādāk. Bet dažreiz tikai pēc ziņojuma sagatavošanas mēs uzzinām, ka mūsu datos ir daži pārkāpumi. Nāc, es tev parādīšu, kā…
2. jautājums: norādiet pasūtījumu skaitu no katra reģiona
Tagad, lai atbildētu uz šo jautājumu:
Izvēlieties Novads un velciet to uz Rindas Platība un Lieta uz Vērtības Platība.
Mēs iegūsim sadalītos reģionu datus. Mēs varam atbildēt, no kura reģiona ir saņemts pasūtījumu.
Saskaņā ar rakurstabulu mūsu datos kopumā ir 4 reģioni. Bet pagaidi, ievēro to Centrālā un Centrle novads. Mēs zinām, ka centram vajadzētu būt Centrālā. Ir pārkāpums. Mums jādodas uz mūsu datiem un jāveic datu tīrīšana.
Lai notīrītu datus reģiona laukā, mēs filtrējam nepareizo reģiona nosaukumu (Centrle) un izlabojam (Centrālais).
Tagad atgriezieties pie saviem galvenajiem datiem.
Ar peles labo pogu noklikšķiniet jebkurā vietā rakurstabulā un noklikšķiniet uz Atsvaidzināt.
Jūsu pārskats tagad ir atjaunināts.
Tagad jūs varat redzēt, ka patiesībā ir tikai 3 reģioni.
Pivot pārskata formatēšana ar kategorizētām rindām.
Dažreiz jums būs nepieciešami ziņojumi, piemēram, iepriekš redzamais attēls. Tādējādi ir viegli redzēt savus datus strukturētā veidā. Jūs varat viegli noteikt, no kura reģiona tiek pasūtīts preču skaits. Apskatīsim, kā jūs to varat izdarīt.
Pārvietot Novads un Lieta uz RIJAS apgabalā. Pārliecinieties, vai reģions atrodas augšpusē un vienumi apakšā, kā parādīts attēlā.
Velciet Lieta priekš Vērtība Platība.
Rezultātā jūs saņemsiet šo pārskatu.
Tas derēs. Bet dažreiz jūsu priekšnieks vēlas ziņot tabulas veidā bez starpsummām. Lai to izdarītu, mums ir jāformatē rakurstabula.
Noņemiet starpsummas no rakurstabulas
Izpildiet šīs darbības:
1. Noklikšķiniet jebkurā vietā uz rakurstabulas.
2. Dodieties uz Dizains Tab.
3. Noklikšķiniet uz starpsummas izvēlne.
4. Noklikšķiniet uz Nerādīt starpsummas.
Var redzēt, ka tagad nav starpsummu.
Labi. Bet tas joprojām nav tabulas veidā. Reģioni un vienumi tiek parādīti vienā slejā. Parādiet tos atsevišķi.
Izveidojiet šarnīra tabulu
Tagad, lai dažādās kolonnās parādītu reģionus un vienumus, rīkojieties šādi:
1. Noklikšķiniet jebkurā vietā uz rakurstabulas
2. Atveriet cilni Dizains
3. Noklikšķiniet uz Pārskata izkārtojums.
4. Noklikšķiniet uz Show opcijai Tabulas forma. Visbeidzot, jums būs šis izsmalcinātais pārskata pārskats.
Tagad mēs zinām kopējo pasūtījumu skaitu, kas veikts katram vienumam no katra reģiona. Tas ir redzams grāfā vienumu slejās.
Lūdzu, nomainiet kolonnas nosaukumu uz Pasūtījumi.
Tagad izskatās labāk.
Q3: Cik vienības tiek pasūtītas katrai precei?
Lai atbildētu uz šo jautājumu, mums ir nepieciešama vienību summa. Lai to izdarītu, vienkārši pārvietojiet lauku Vienības uz Vērtības. Tas automātiski apkopos katra vienuma vienību skaitu. Ja kolonnā rakurstabulā ir tikai vērtības, rakurstabula pēc noklusējuma parāda šo vērtību summu. Bet to var mainīt no vērtības lauka iestatījuma. Kā? Es jums parādīšu pēdējo.
Pivot tabulas vērtības lauka iestatījumi
4. jautājums: katras preces vidējā cena?
Mūsu izlases datos vienas preces cena dažādiem pasūtījumiem ir atšķirīga. Piemēram, skatiet Saistvielas.
Es vēlos uzzināt katra elementa vidējās izmaksas rakurstabulā. Lai to uzzinātu, velciet vienības izmaksas uz vērtības lauku. Tas parādīs vienības izmaksu summu.
Mēs negribam Vienības izmaksu summa, mēs gribam Vidējās vienības izmaksas. Tā darīt…
1. Ar peles labo pogu noklikšķiniet uz jebkuras rakurstabulas slejas Vienības izmaksas summas
2. Noklikšķiniet uz Vērtību lauku iestatījumi
3. Pamatojoties uz pieejamajām opcijām, izvēlieties Vidēji un nospiediet OK.
Visbeidzot, jums būs šāds Pivot pārskats:
Pivot tabulas aprēķinātie lauki
Viena no noderīgākajām rakurstabulas funkcijām ir tās aprēķinātie lauki. Aprēķinātie lauki ir lauki, kas iegūti, veicot dažas darbības ar pieejamajām kolonnām.
Let's saprast, kā ievietot aprēķinātos laukus rakurstabulā, izmantojot vienu piemēru:
Pamatojoties uz mūsu datiem, mēs sagatavojām šo ziņojumu.
Lūk, mums ir Vienību summa un Kopējās izmaksas. Es tikko pārvietoju kopējo kolonnu uz lauku Vērtības un pēc tam pārdēvēju to par Kopējās izmaksas. Tagad es vēlos uzzināt katra posteņa vienības vidējo cenu katram reģionam. Un tas būtu:
Vidējā cena = kopējās izmaksas / kopējās vienības
Pivot tabulā ievietosim lauku, kurā parādīta katra vienuma reģiona vidējā cena:
Izpildiet šīs darbības, lai rakurstabulā ievietotu aprēķināto lauku
1. Noklikšķiniet jebkurā vietā uz rakurstabulas un dodieties uz cilni Analīze
2. Aprēķinu grupā noklikšķiniet uz Lauki, vienumi un kopas.
3. Noklikšķiniet uz Aprēķinātie lauki.
Jūs redzēsit šo aprēķina lauka ievades lodziņu:
4. Nosaukuma ievades lodziņā ierakstiet vidējās izmaksas vai jebko, kas jums patīk, Excel neiebildīs. Formulas ievades lodziņā ierakstiet un nospiediet Labi.
= Kopā / vienības
Lai veiktu darbības, varat to rakstīt manuāli, izmantojot tastatūru, vai arī veiciet dubultklikšķi uz lauku nosaukumiem, kas norādīti laukā Lauki.
5. Aprēķinātais lauks tagad ir pievienots rakurstabulai. To sauc par vidējo izmaksu summu, bet tā nav summa. Excel vienkārši palaiž noklusējuma funkciju, lai nosauktu kolonnu (piemēram, rituāls). Pārdēvējiet šo kolonnu un ierobežojiet parādītos ciparus aiz komata.
Un tur jums ir aprēķināts lauks. Jūs varat padarīt to tik sarežģītu, cik vēlaties. Piemēra labad es izmantoju šo vienkāršo vidējo darbību.
Grupēšana rakurstabulā
Jūs esat sagatavojis šo galveno pārskatu.
Tagad es vēlos, lai šis ziņojums tiktu sadalīts katru gadu. Skatiet momentuzņēmumu zemāk.
Mums ir sleja par pasūtījuma datumu. Pārvietot lauku OrderDate uz rindām augšpusē.
Tas neizskatās pēc pieprasītā ziņojuma. Mums ir jābūt grupas datumiem katru gadu.
Tagad, lai grupētu lauku Excel rakurstabulā, rīkojieties šādi:
1. Ar peles labo pogu noklikšķiniet uz lauka, kuru vēlaties grupēt.
2. Noklikšķiniet uz Grupēt. Jums būs šī izvēles rūtiņa pielāgošanai. Tā kā šī ir datu sleja, Excel parāda mums attiecīgi grupu. Jūs varat izvēlēties sākuma un beigu datumu.
3. Izvēlieties gadiem un nospiediet OK. Jūs esat veicis ikgadēju grupēšanu Excel rakurstabulā.
Pivot tabulas šķēlēji
Slicers tika ieviests programmā Excel 2010 kā papildinājums. Programmās Excel 2013 un 2016 tas pēc noklusējuma ir pieejams tāpat kā filtri.
Griezēji ir nekas cits kā filtri. Atšķirībā no filtriem, Slicers parāda visas pieejamās opcijas tieši jūsu priekšā. Tas padara jūsu informācijas paneli interaktīvāku.
Kā pievienot šķēlējus rakurstabulā, Excel 2016 un 2013
Pivot Table Slicers ir viegli pievienot. Izpildiet šīs darbības:
1. Noklikšķiniet jebkurā vietā rakurstabulā un atveriet cilni Analīze.
2. Noklikšķiniet uz Ievietot šķēlēju. Jums būs datu lauku saraksts. Izvēlieties tik daudz, cik vēlaties.
3. Šajā piemērā atlasiet Reģions un nospiediet Labi.
Jūs savam pārskatam esat pievienojis Slicer. Tagad izmantojiet filtru tikai ar vienu klikšķi.
Ievietojiet laika skalu programmā Excel 2016 un 2013
Šī ir viena no manām iecienītākajām Excel rakurstabulu funkcijām. Šī jaunā funkcionalitāte darbojas tikai ar datumiem. Izmantojot to, jūs varat vizuāli izvēlēties laika periodu, lai filtrētu savus datus.
Lai ievietotu laika skalas kursoru, rīkojieties šādi:
Kā pievienot laika skalu rakurstabulā, Excel 2016 un 2013
1. Noklikšķiniet jebkurā vietā rakurstabulā un atveriet cilni Analīze.
2. Noklikšķiniet uz Ievietot laika skalu no filtru grupas. Visas kolonnas, kas satur laika vērtības iegūtajos datos, tiks uzskaitītas opciju lodziņā, no kura izvēlēties. Šeit mums ir tikai viens. Tātad jā…
2. Izvēlieties opcijas un nospiediet taustiņu Enter vai noklikšķiniet uz Labi. Tas ir paveikts, un jums ir pagrieziena tabulas laika skala.
Varat izvēlēties to rādīt katru dienu, mēnesi, ceturksni vai gadu. Šeit es izvēlējos ikmēneša.
Šajā rakstā es apskatīju svarīgākās un noderīgākās rakurstabulas funkcijas. Mēs izpētījām jaunas rakurstabulas funkcijas programmā Excel 2016 un 2013. Mēs uzzinājām par klasisko šarnīra tabulas izmantošanu, kas tika palaista programmā Excel 2007, 2010 un vecākās versijās. Tie joprojām ir noderīgi. Ja šeit neatradāt atbildi, kas saistīta ar jūsu rakurstabulu, jautājiet komentāru sadaļā.
Ir daudzas citas funkcijas, kuras vēl nav izskaidrotas. Nākamajā rakstā mēs apgūstam uzlabotas rakurstabulas funkcijas. Līdz tam Excel ar visu.