Pivot tabulas ir ziņošanas programma, kas iebūvēta programmā Excel. Tie ir vienīgais labākais rīks programmā Excel datu analīzei bez formulām . Aptuveni vienas minūtes laikā varat izveidot pamata rakurstabulu un sākt interaktīvi izpētīt savus datus. Tālāk ir sniegti vairāk nekā 20 padomi, kā maksimāli izmantot šo elastīgo un jaudīgo rīku.
1. Pivot tabulu var izveidot aptuveni vienas minūtes laikā
Daudzi cilvēki domā, ka šarnīra tabulas izveide ir sarežģīta un laikietilpīga, taču tā vienkārši nav taisnība. Salīdzinot ar laiku, kas nepieciešams, lai līdzvērtīgu pārskatu izveidotu manuāli, rakurstabulas ir neticami ātras. Ja jums ir labi strukturēti avota dati, varat izveidot rakurstabulu mazāk nekā minūtes laikā. Sāciet, atlasot jebkuru avota datu šūnu:
Avota datu piemērs
Tālāk veiciet šīs četras darbības:
- Lentes cilnē Ievietot noklikšķiniet uz pogas PivotTable
- Dialoglodziņā Izveidot rakurstabulu pārbaudiet datus un noklikšķiniet uz Labi
- Velciet lauku “etiķete” rindu iezīmju apgabalā (piemēram, klients)
- Velciet ciparu lauku vērtību apgabalā (piemēram, pārdošana)
Piviv pamata tabula aptuveni 30 sekundēs
Augšējā rakurstabulā ir parādīts kopējais pārdošanas apjoms pa produktiem, taču laukus varat viegli pārkārtot, lai parādītu kopējos pārdošanas apjomus pa reģioniem, kategorijām, mēnešiem un tā tālāk. Lai ātri demonstrētu, skatieties tālāk redzamo videoklipu:
Video: Kā ātri izveidot pagrieziena tabulu
2. Notīriet avota datus
Lai samazinātu problēmas ceļā, pārliecinieties, vai jūsu dati ir labā stāvoklī. Avota datiem nedrīkst būt tukšas rindas vai kolonnas, kā arī starpsummas. Katrai slejai jābūt ar unikālu nosaukumu (tikai vienā rindā), un tai ir jābūt katras datu rindas/ieraksta laukam:
Excel, ja datums ir starp diviem datumiem atgriešanās vērtību
Perfekti dati šarnīra tabulai!
Dažreiz jums, iespējams, būs jāpievieno trūkstošie dati. Padomus skatiet šajā video:
Video: Kā ātri aizpildīt trūkstošos datus
3. Vispirms saskaitiet datus
Kad pirmo reizi izveidojat rakurstabulu, izmantojiet to, lai vispirms ģenerētu vienkāršu skaitli, lai pārliecinātos, ka rakurstabula apstrādā datus, kā paredzēts. Lai to izdarītu, vienkārši pievienojiet jebkuru teksta lauks kā vērtību lauku. Jūs redzēsit ļoti mazu rakurstabulu, kurā parādīts kopējais ierakstu skaits, tas ir, kopējais datu rindu skaits. Ja šis skaitlis jums šķiet saprātīgs, varat sākt. Ja skaitlis jums nav jēgas, iespējams, ka rakurstabula neizlasa datus pareizi vai dati nav pareizi definēti.
300 vārdi nozīmē, ka mums ir 300 darbinieku. Pārbaudiet.
4. Plānojiet pirms būvniecības
Lai gan ir ļoti jautri vilkt laukus ap šarnīra tabulu un skatīties, kā programma Excel izrāda vēl vienu neparastu datu attēlojumu, jūs varat ļoti viegli nokļūt daudz neproduktīvu trušu bedrīšu. Pēc stundas vairs nav tik jautri. Pirms sākat būvniecību, pierakstiet to, ko mēģināt izmērīt vai saprast, un piezīmju grāmatiņā ieskicējiet dažus vienkāršus ziņojumus. Šīs vienkāršās piezīmes palīdzēs jums izvēlēties milzīgo izvēli. Turiet lietas vienkāršas un koncentrējieties uz jautājumiem, uz kuriem jāatbild.
5. Izmantojiet tabulu saviem datiem, lai izveidotu “dinamisko diapazonu”
Ja rakurstabulas avota datiem izmantojat Excel tabulu, iegūstat ļoti jauku labumu: jūsu datu diapazons kļūst “dinamisks”. Dinamiskais diapazons automātiski paplašinās un samazinās tabulu, pievienojot vai noņemot datus, tāpēc nebūs jāuztraucas, ka rakurstabulai trūkst jaunāko datu. Ja rakurstabulai izmantojat tabulu, rakurstabula vienmēr būs sinhronizēta ar jūsu datiem.
Lai izmantotu tabulu savā rakurstabulā:
- Atlasiet jebkuru datu šūnu, lai izveidotu tabulu, izmantojot īsinājumtaustiņu Ctrl-T
- Noklikšķiniet uz pogas Apkopot, izmantojot rakurstabulu (TableTools> Design)
- Veidojiet savu rakurstabulu normāli
- Peļņa: dati, kurus pievienojat savai tabulai, tiks automātiski parādīti jūsu rakurstabulā, kad tie tiks atsvaidzināti
Video: Nākamajai rakurstabulai izmantojiet tabulu
Vienkāršas tabulas izveide no datiem, izmantojot (Ctrl-T)
Tagad, kad mums ir tabula, mēs varam izmantot Apkopot ar rakurstabulu
6. Izmantojiet rakurstabulu, lai saskaitītu lietas
Pēc noklusējuma rakurstabula saskaita jebkuru teksta lauku. Tas var būt patiešām ērts līdzeklis daudzās vispārējās biznesa situācijās. Piemēram, pieņemsim, ka jums ir darbinieku saraksts un vēlaties iegūt skaitu pēc nodaļas? Lai iegūtu sadalījumu pa nodaļām, rīkojieties šādi:
- Izveidojiet rakurstabulu normāli
- Pievienojiet departamentu kā rindas iezīmi
- Pievienojiet darbinieka vārda lauku kā vērtību
- Šarnīra tabulā tiks parādīts darbinieku skaits pa departamentiem
Darbinieku sadalījums pa nodaļām
7. Rādīt kopsummas procentos
Daudzās rakurstabulās vēlaties rādīt procentus, nevis skaitļus. Piemēram, varbūt vēlaties parādīt pārdošanas sadalījumu pēc produkta. Bet tā vietā, lai parādītu katra produkta kopējo pārdošanas apjomu, vēlaties parādīt pārdošanas apjomu procentos no kopējā pārdošanas apjoma. Pieņemot, ka jūsu datos ir lauks Pārdošana, vienkārši rīkojieties šādi:
- Pievienojiet produktu rakurstabulai kā rindas iezīmi
- Pievienojiet Sales rakurstabulai kā vērtību
- Ar peles labo pogu noklikšķiniet uz lauka Pārdošana un iestatiet “Rādīt vērtības kā” uz “% no kopējās summas”
Skatiet padomu zemāk “Pievienot lauku vairāk nekā vienu reizi rakurstabulai”, lai uzzinātu, kā vienlaikus parādīt kopējo pārdošanas apjomu un procentus no kopējā apjoma.
Vērtības displeja maiņa uz % no kopsummas
Darbinieku summa tiek parādīta % no kopskaita
8. Izmantojiet rakurstabulu, lai izveidotu unikālu vērtību sarakstu
Tā kā rakurstabulas apkopo datus, tās var izmantot, lai laukā atrastu unikālas vērtības. Tas ir labs veids, kā ātri redzēt visas vērtības, kas parādās laukā, kā arī atrast drukas kļūdas un citas neatbilstības. Piemēram, pieņemsim, ka jums ir pārdošanas dati un vēlaties redzēt visu pārdoto produktu sarakstu. Lai izveidotu produktu sarakstu:
- Izveidojiet rakurstabulu normāli
- Pievienojiet produktu kā rindas etiķeti
- Kā vērtību pievienojiet jebkuru citu teksta lauku (kategorija, klients utt.)
- Pivot tabulā tiks parādīts visu produktu saraksts, kas parādās pārdošanas datos
Visi produkti, kas parādās datos, ir uzskaitīti (ieskaitot drukas kļūdu)
Pivot Table video apmācība - ātri, tīri un precīzi
9. Izveidojiet autonomu rakurstabulu
Kad esat izveidojis rakurstabulu no datiem tajā pašā darblapā, varat noņemt datus, ja vēlaties, un rakurstabula turpinās darboties normāli. Tas ir tāpēc, ka rakurstabulai ir a slēpšanās šarnīrs kurā ir precīzs rakurstabulas izveidošanai izmantoto datu dublikāts.
- Atsvaidziniet rakurstabulu, lai nodrošinātu, ka kešatmiņa ir atjaunināta (rakurstabulas rīki> Atsvaidzināt)
- Izdzēsiet darblapu, kurā ir dati
- Izmantojiet savu rakurstabulu kā parasti
Video: Kā izveidot autonomu šarnīra tabulu
10. Pivot tabulu grupējiet manuāli
Lai gan rakurstabulas automātiski grupē datus daudzos veidos, vienumus varat grupēt arī manuāli savās pielāgotajās grupās. Piemēram, pieņemsim, ka jums ir rakurstabula, kurā parādīts darbinieku sadalījums pa nodaļām. Pieņemsim, ka vēlaties Inženierzinātņu, Izpildes un Atbalsta nodaļas vēl grupēt 1. grupā, bet Pārdošanu un Mārketingu - 2. grupā. 1. un 2. grupa nav redzama datos, tās ir jūsu pielāgotās grupas. Lai grupētu rakurstabulu ad hoc grupās, 1. un 2. grupā:
- Ctrl-klikšķis, lai atlasītu katru vienumu pirmajā grupā
- Ar peles labo pogu noklikšķiniet uz viena no vienumiem un izvēlnē izvēlieties Grupēt
- Excel izveido jaunu grupu 'Group1'
- B slejā atlasiet Mārketings un pārdošana un grupējiet, kā norādīts iepriekš
- Excel izveido citu grupu 'Group2'
Sākt grupēt manuāli
Pusceļā manuālā grupēšana - 1. grupa ir pabeigta
Grupēšana ir pabeigta manuāli
11. Grupējiet skaitliskos datus diapazonos
Viena no interesantākajām un spēcīgākajām funkcijām, kas piemīt katrai rakurstabulai, ir iespēja grupēt skaitliskos datus diapazonos vai grupās. Piemēram, pieņemsim, ka jums ir balsošanas rezultātu saraksts, kurā iekļauts vēlētāja vecums, un vēlaties apkopot rezultātus pēc vecuma grupas:
- Izveidojiet rakurstabulu normāli
- Pievienojiet vecumu kā rindas iezīmi, balsojiet kā kolonnas iezīmi un nosaukumu kā vērtību
- Ar peles labo pogu noklikšķiniet uz jebkuras vērtības laukā Vecums un izvēlieties Grupēt
- Ievades laukā “Pēc:” ievadiet 10 kā intervālu
- Noklikšķinot uz Labi, redzēsit balsošanas datus, kas grupēti pēc vecuma 10 gadu grupās
Video: Kā grupēt rakurstabulu pēc vecuma diapazona
Balsošanas rezultātu avota dati
Vecuma lauka grupēšana 10 gadu grupās
Balsošanas rezultāti grupēti pēc vecuma diapazona
12. Pārdēvējiet laukus labākai lasāmībai
Pievienojot laukus rakurstabulai, rakurstabula parādīs nosaukumu, kas tiek parādīts avota datos. Vērtību lauku nosaukumi tiks parādīti ar “Sum” vai “Count of”, kad tie tiks pievienoti rakurstabulai. Piemēram, jūs redzēsit pārdošanas apjomu, reģiona skaitu utt. Tomēr jūs varat vienkārši pārrakstīt šo vārdu ar savu. Vienkārši atlasiet šūnu, kurā ir lauks, kuru vēlaties pārdēvēt, un ierakstiet jaunu nosaukumu.
Pārdēvējiet lauku, ierakstot virs sākotnējā nosaukuma
13. Pievienojiet atstarpi lauku nosaukumiem, kad Excel sūdzas
Mēģinot pārdēvēt laukus, var rasties problēma, ja mēģināt izmantot tieši to pašu lauka nosaukumu, kas redzams datos. Piemēram, pieņemsim, ka avota datos ir lauks Pārdošana. Kā vērtības lauks tas parādās kā Pārdošanas summa , bet (saprātīgi) jūs vēlaties, lai tas teiktu Pārdošana . Tomēr, mēģinot izmantot pakalpojumu Sales, programma Excel sūdzas, ka lauks jau pastāv, un parāda kļūdas ziņojumu “PivotTable lauka nosaukums jau pastāv”.
Programmai Excel nepatīk jūsu jaunā lauka nosaukums
Kā vienkāršs risinājums vienkārši pievienojiet atstarpi jaunā lauka nosaukuma beigās. Jūs nevarat redzēt atšķirību, un Excel nesūdzēsies.
Pievienojot nosaukumam atstarpi, problēma tiek novērsta
14. Pievienojiet lauku vairāk nekā vienu reizi rakurstabulai
Ir daudzas situācijas, kad ir jēga vienu un to pašu lauku pievienot rakurstabulai vairāk nekā vienu reizi. Tas var šķist dīvaini, taču to pašu lauku var pievienot rakurstabulai vairāk nekā vienu reizi. Piemēram, pieņemsim, ka jums ir rakurstabula, kurā redzams darbinieku skaits pa nodaļām.
Skaits darbojas labi, taču jūs vēlaties arī parādīt skaitli procentos no kopējā darbinieku skaita. Šajā gadījumā vienkāršākais risinājums ir pievienot to pašu lauku divreiz kā vērtības lauku:
- Pievienojiet teksta lauku vērtību laukam (piemēram, vārds, vārds utt.)
- Pēc noklusējuma jūs saņemsiet teksta lauku skaitu
- Pievienojiet to pašu lauku vēlreiz laukam Vērtība
- Ar peles labo pogu noklikšķiniet uz otrās instances un mainiet Rādīt vērtības uz “% no kopējās summas”
- Pārdēvējiet abus laukus, kā vēlaties
Lauka iestatīšana, lai tiktu rādīti procenti no kopsummas
Lauks Nosaukums ir pievienots divas reizes
15. Automātiski formatēt visus vērtību laukus
Katru reizi, kad pievienojat ciparu lauku kā vērtību rakurstabulā, skaitļa formāts jāiestata tieši laukā. Jums var rasties kārdinājums tieši formatēt rakurstabulā redzamās vērtības, taču tā nav laba ideja, jo tā nav uzticama, mainoties rakurstabulai. Formāta iestatīšana tieši uz lauka nodrošinās, ka lauks tiek parādīts vēlamajā formātā neatkarīgi no tā, cik liela vai maza ir rakurstabula.
Piemēram, pieņemsim rakurstabulu, kurā parādīts pārdošanas sadalījums pa reģioniem. Kad pirmo reizi pievienojat lauku Pārdošana rakurstabulai, tas tiks parādīts vispārējā skaitļa formātā, jo tas ir ciparu lauks. Lai pašam laukam lietotu grāmatvedības numura formātu:
lielāks vai mazāks par Excel
- Ar peles labo pogu noklikšķiniet uz lauka Pārdošana un izvēlnē atlasiet Vērtības lauka iestatījumi
- Parādītajā vērtību lauka iestatījumu dialoglodziņā noklikšķiniet uz pogas Ciparu formāts
- Iestatiet formātu Grāmatvedība un noklikšķiniet uz Labi, lai izietu
Formāta iestatīšana tieši vērtību laukā
16. Izpētiet, lai redzētu (vai iegūtu) datus, kas atrodas aiz jebkura kopsummas
Ikreiz, kad rakurstabulā redzat kopsummu, jūs varat viegli redzēt un iegūt datus, kas veido kopsummu, veicot “urbšanu”. Piemēram, pieņemsim, ka skatāties rakurstabulu, kurā redzams darbinieku skaits pa nodaļām. Jūs varat redzēt, ka Inženierzinātņu nodaļā strādā 50 darbinieki, taču vēlaties redzēt faktiskos vārdus. Lai redzētu 50 cilvēkus, kas veido šo numuru, veiciet dubultklikšķi tieši uz skaitļa 50, un programma Excel pievienos jūsu darbgrāmatai jaunu lapu, kurā ir precīzi dati, kas izmantoti 50 inženieru aprēķināšanai. Varat izmantot šo pašu pieeju, lai skatītu un iegūtu datus aiz kopsummām visur, kur tos redzat rakurstabulā.
Veiciet dubultklikšķi, lai veiktu pilnīgu pārbaudi
50 inženieri, kas automātiski iegūti jaunā lapā
17. Klonējiet savas rakurstabulas, kad nepieciešams cits skats
Kad esat izveidojis vienu rakurstabulu, iespējams, vēlēsities redzēt atšķirīgu to pašu datu skatu. Jūs, protams, varat vienkārši pārkārtot esošo rakurstabulu, lai izveidotu jaunu skatu. Bet, ja veidojat pārskatu, kuru plānojat pastāvīgi izmantot un atjaunināt, vienkāršākais ir klonēt esošo rakurstabulu, lai vienmēr būtu pieejami abi datu skati.
Ir divi vienkārši veidi, kā klonēt šarnīra tabulu. Pirmais veids bija dublēt darblapu, kurā atrodas rakurstabula. Ja darblapā ir izveidota rakurstabula ar nosaukumu utt., Varat ar peles labo pogu noklikšķināt uz darblapas cilnes, lai kopētu darblapu tajā pašā darbgrāmatā. Vēl viens veids, kā klonēt rakurstabulu, ir kopēt rakurstabulu un ielīmēt to citur. Izmantojot šīs pieejas, jūs varat izgatavot tik daudz kopiju, cik vēlaties.
Klonējot šarnīra tabulu šādā veidā, abām rakurstabulām ir viena un tā pati slēpšanās šarnīrs . Tas nozīmē, ka, atsvaidzinot kādu no kloniem (vai oriģinālu), tiks atsvaidzinātas visas saistītās rakurstabulas.
Video: Kā klonēt šarnīra tabulu
18. Atklājiet šarnīra tabulu, lai to atsvaidzinātu neatkarīgi
Pēc rakurstabulas klonēšanas jūs varat nonākt situācijā, kad jūs patiešām nevēlaties, lai klons būtu saistīts ar to pašu šarnīra kešatmiņu kā oriģināls. Parasts piemērs ir pēc datuma lauka sagrupēšanas vienā rakurstabulā, atsvaidzināšanas un atklāšanas, ka arī nejauši esat grupējis to pašu datuma lauku cits rakurstabula, kuru neplānojāt mainīt. Ja rakurstabulām ir viena un tā pati šarnīra kešatmiņa, tām ir kopīga arī lauku grupēšana.
Šeit ir viens veids, kā atvienot rakurstabulu, tas ir, atsaistīt to no šarnīra kešatmiņas, ko tā koplieto ar citām tās pašas darblapas rakurstabulām.
- Izgrieziet visu šarnīra tabulu starpliktuvē
- Ielīmējiet rakurstabulu pavisam jaunā darbgrāmatā
- Atsvaidziniet rakurstabulu
- Kopējiet to vēlreiz starpliktuvē
- Ielīmējiet to atpakaļ sākotnējā darbgrāmatā
- Izmetiet pagaidu darbgrāmatu
Jūsu rakurstabula tagad izmantos savu pagrieziena kešatmiņu un netiks atsvaidzināta kopā ar citām darbgrāmatas šarnīra tabulām, vai arī netiks izmantota viena un tā pati lauku grupēšana.
19. Atbrīvojieties no bezjēdzīgiem virsrakstiem
Jaunu rakurstabulu noklusējuma izkārtojums ir kompakts izkārtojums. Šajā izkārtojumā rakurstabulā kā virsraksti būs redzamas rindu iezīmes un kolonnu iezīmes. Šie nav intuitīvākie virsraksti, īpaši cilvēkiem, kuri bieži neizmanto rakurstabulas. Vienkāršs veids, kā atbrīvoties no šiem nepāra virsrakstiem, ir mainīt rakurstabulas izkārtojumu no kompakta uz kontūru vai tabulas izkārtojumu. Tādējādi rakurstabula parādīs faktiskos lauku nosaukumus kā virsrakstus rakurstabulā, kas ir daudz saprātīgāk. Lai pilnībā atbrīvotos no šīm etiķetēm, rakurstabulas rīku lentes cilnē Analizēt meklējiet pogu Lauku galvenes. Noklikšķinot uz šīs pogas, virsraksti tiks pilnībā atspējoti.
Ņemiet vērā bezjēdzīgos un mulsinošos lauku virsrakstus
Izkārtojuma pārslēgšana no kompakta uz kontūru
Lauku virsraksti Outline izkārtojumā ir daudz saprātīgāki
20. Pievienojiet nelielu atstarpi ap šarnīra tabulām
Tas ir tikai vienkāršs dizaina padoms. Visi labi dizaineri zina, ka patīkamam dizainam ir nepieciešama neliela atstarpe. Baltā telpa nozīmē tikai tukšu vietu, kas atvēlēta, lai izkārtojumam dotu elpošanas telpu. Pēc rakurstabulas izveidošanas ievietojiet papildu kolonnu pa kreisi un vienu vai divas rindas augšpusē. Tas jūsu šarnīra galdam piešķirs elpošanas telpu un radīs labāku izkārtojumu. Vairumā gadījumu es arī iesaku darblapā izslēgt režģlīnijas. Pivot tabula pati parādīs spēcīgu vizuālo režģi, tāpēc režģlīnijas ārpus rakurstabulas nav vajadzīgas un vienkārši radīs vizuālu troksni.
Neliela atstarpe ļauj šarnīra tabulām izskatīties izsmalcinātākām
Iedvesma: 5 rakurstabulas, kuras iepriekš neesat redzējis .
21. Atbrīvojieties no rindu un kolonnu kopsummām
Pēc noklusējuma rakurstabulas parāda kopsummas gan rindām, gan kolonnām, taču, ja nevēlaties, varat viegli atspējot vienu vai abas šīs kopsummas. Lentes cilnē Pivot Table vienkārši noklikšķiniet uz pogas Kopā un izvēlieties vajadzīgās opcijas.
Varat noņemt kopsummas gan rindām, gan kolonnām
22. Formatējiet tukšas šūnas
Ja jums ir rakurstabula, kurā ir daudz tukšu šūnu, varat kontrolēt rakstzīmi, kas tiek parādīta katrā tukšajā šūnā. Pēc noklusējuma tukšās šūnas vispār neko nerādīs. Lai iestatītu savu rakstzīmi, ar peles labo pogu noklikšķiniet rakurstabulas iekšpusē un atlasiet rakurstabulas opcijas. Pēc tam pārliecinieties, vai ir atzīmēta opcija “Tukšas šūnas kā:”, un ievadiet rakstzīmi, kuru vēlaties redzēt. Ņemiet vērā, ka šis iestatījums respektē piemēroto skaitļu formātu. Piemēram. ja skaitliskās vērtības laukam izmantojat uzskaites numura formātu un ievadāt nulli, rakurstabulā redzēsit defisi “-”, jo šādi grāmatvedības formātā tiek parādītas nulles vērtības.
Tukšās šūnas ir iestatītas uz 0 (nulle), un uzskaites numura formāts sniedz defises
23. Ja nepieciešams, izslēdziet AutoFit
Pēc noklusējuma, atsvaidzinot rakurstabulu, slejas, kurās ir dati, tiek automātiski pielāgotas, lai tās vislabāk atbilstu datiem. Parasti tā ir laba lieta, taču tas var padarīt jūs traku, ja darblapā kopā ar rakurstabulu ir citas lietas vai ja esat rūpīgi manuāli pielāgojis kolonnu platumus un nevēlaties, lai tie tiktu mainīti. Lai atspējotu šo līdzekli, ar peles labo pogu noklikšķiniet rakurstabulas iekšpusē un izvēlieties PivotTable Options. Opciju pirmajā cilnē (vai Mac izkārtojuma cilnē) noņemiet atzīmi no opcijas AutoFit Column Widths on Update.
Pivot tabulas kolonnas automātiskās pielāgošanas opcija operētājsistēmai Windows
Pivot tabulas kolonnas automātiskās pielāgošanas opcija operētājsistēmai Mac
Vai jums jāapgūst rakurstabulas? Mums ir stabils video treniņš ar prakses darblapām.
Autors Deivs Bruns
