Excel

Nasveti za vrtilno tabelo

Pivot Table Tips

Pregled | Zakaj Pivot? | Nasveti | Primeri | Usposabljanje

Vrtilne tabele so mehanizem za poročanje, vgrajen v Excel. So najboljše orodje v Excelu za analizo podatkov brez formul . Osnovno vrtilno tabelo lahko ustvarite v približno eni minuti in začnete interaktivno raziskovati svoje podatke. Spodaj je več kot 20 nasvetov, kako najbolje izkoristiti to prilagodljivo in zmogljivo orodje.



1. Vrtilno tabelo lahko sestavite v približno eni minuti

Mnogi ljudje mislijo, da je izgradnja vrtilne tabele zapletena in dolgotrajna, vendar preprosto ne drži. V primerjavi s časom, ki ga potrebujete za ročno izdelavo enakovrednega poročila, so vrtilne tabele neverjetno hitre. Če imate dobro strukturirane izvorne podatke, lahko vrtilno tabelo ustvarite v manj kot minuti. Začnite z izbiro katere koli celice v izvornih podatkih:

Surovi podatki (prodaja čokolade), pripravljeni za vrtilno tabelo
Primer izvornih podatkov





Nato sledite tem štirim korakom:

  1. Na zavihku Vstavljanje na traku kliknite gumb Vrtilna tabela
  2. V pogovornem oknu Ustvari vrtilno tabelo preverite podatke in kliknite V redu
  3. Povlecite polje z oznako v območje Oznake vrstic (npr. Stranka)
  4. Povlecite številsko polje v območje vrednosti (npr. Prodaja)

Hitra vrtilna tabela Excel, ki prikazuje prodajo čokolade
Osnovna vrtilna tabela v približno 30 sekundah



Zgornja vrtilna tabela prikazuje skupno prodajo po izdelkih, polja pa lahko preprosto preuredite, da prikažete skupno prodajo po regijah, kategorijah, mesecih itd. Za hiter prikaz si oglejte spodnji video:

Video: Kako hitro ustvariti vrtilno tabelo

2. Očistite izvorne podatke

Če želite čim manj težav, se prepričajte, da so vaši podatki v dobrem stanju. Izvorni podatki ne smejo imeti praznih vrstic ali stolpcev in vmesnih seštevkov. Vsak stolpec mora imeti edinstveno ime (samo v eni vrstici) in predstavljati polje za vsako vrstico/zapis v podatkih:

kako narediti pi v

Popolni podatki za vrtilno tabelo!
Popolni podatki za vrtilno tabelo!

Morda boste včasih morali dodati manjkajoče podatke. Za nasvete si oglejte ta video:

Video: Kako hitro zapolniti manjkajoče podatke

3. Najprej preštejte podatke

Ko prvič ustvarite vrtilno tabelo, z njo najprej ustvarite preprosto štetje, da se prepričate, da vrtilna tabela obdeluje podatke, kot ste pričakovali. Če želite to narediti, preprosto dodajte katero koli besedilno polje kot polje vrednosti. Videli boste zelo majhno vrtilno tabelo, ki prikazuje skupno število zapisov, to je skupno število vrstic v vaših podatkih. Če se vam zdi ta številka smiselna, ste pripravljeni. Če vam številka ni smiselna, je možno, da vrtilna tabela ne bere podatkov pravilno ali pa podatki niso bili pravilno definirani.

300 imen pomeni, da imamo 300 zaposlenih. Preverite.
300 imen pomeni, da imamo 300 zaposlenih. Preverite.

4. Načrtujte, preden zgradite

Čeprav je zelo zabavno vlečenje polj okoli vrtilne tabele in opazovanje, kako Excel izloči še eno nenavadno predstavitev podatkov, se lahko zelo enostavno spustite po številnih neproduktivnih zajčjih luknjah. Uro kasneje ni več tako zabavno. Preden začnete graditi, si zapišite, kaj poskušate izmeriti ali razumeti, in v beležnico narišite nekaj preprostih poročil. Te preproste opombe vam bodo pomagale pri velikem številu možnosti, ki jih imate na voljo. Naj bodo stvari preproste in se osredotočite na vprašanja, na katera morate odgovoriti.

5. Uporabite tabelo za podatke, da ustvarite 'dinamični razpon'

Če za izvorne podatke vrtilne tabele uporabite Excelovo tabelo, boste imeli zelo lepo korist: vaš podatkovni obseg postane 'dinamičen'. Dinamični obseg se bo samodejno razširil in skrčil, ko boste dodali ali odstranili podatke, zato vam ne bo treba skrbeti, da v vrtilni tabeli manjkajo najnovejši podatki. Ko za vrtilno tabelo uporabljate tabelo, bo vrtilna tabela vedno sinhronizirana z vašimi podatki.

Če želite uporabiti tabelo za vrtilno tabelo:

  1. Izberite katero koli celico v podatkih, za ustvarjanje tabele uporabite bližnjico na tipkovnici Ctrl-T
  2. Kliknite gumb Povzemi z vrtilno tabelo (TableTools> Design)
  3. Običajno sestavite svojo vrtilno tabelo
  4. Dobiček: podatki, ki jih dodate v tabelo, se ob osvežitvi samodejno prikažejo v vrtilni tabeli

Video: Za naslednjo vrtilno tabelo uporabite tabelo

Ustvarjanje preproste tabele iz podatkov z uporabo (Ctrl-T)
Ustvarjanje preproste tabele iz podatkov z uporabo (Ctrl-T)

Zdaj, ko imamo tabelo, lahko uporabimo povzetek s vrtilno tabelo
Zdaj, ko imamo tabelo, lahko uporabimo povzetek s vrtilno tabelo

Še vedno potrebujete navdih, zakaj bi se morali naučiti vrtilnih tabel? Glej moja osebna zgodba .

6. Uporabite vrtilno tabelo za štetje stvari

Vrtilna tabela privzeto šteje vsa besedilna polja. To je lahko zelo priročna funkcija v številnih splošnih poslovnih situacijah. Recimo, da imate seznam zaposlenih in želite šteti po oddelkih? Če želite razčleniti po oddelkih, sledite tem korakom:

  1. Običajno ustvarite vrtilno tabelo
  2. Dodajte oddelek kot oznako vrstice
  3. Polje Ime zaposlenega dodajte kot vrednost
  4. Vrtilna tabela bo prikazala število zaposlenih po oddelkih

Razčlenitev zaposlenih po oddelkih
Razčlenitev zaposlenih po oddelkih

7. Prikažite vsote kot odstotek

V mnogih vrtilnih tabelah boste želeli prikazati odstotek in ne štetje. Morda želite na primer prikazati razčlenitev prodaje po izdelkih. Toda namesto da prikažete skupno prodajo za vsak izdelek, želite prikazati prodajo kot odstotek celotne prodaje. Če imate v svojih podatkih polje z imenom Prodaja, sledite tem korakom:

  1. Dodajte izdelek v vrtilno tabelo kot oznako vrstice
  2. Dodajte prodajo v vrtilno tabelo kot vrednost
  3. Z desno miškino tipko kliknite polje Prodaja in nastavite »Pokaži vrednosti kot« na »% celotne vsote«

Glejte spodnji nasvet 'Dodaj polje večkrat v vrtilno tabelo', če želite izvedeti, kako prikazati skupno prodajo in prodajo kot odstotek od skupne vrednosti hkrati.

Spreminjanje prikaza vrednosti na % celotne vrednosti
Spreminjanje prikaza vrednosti na % celotne vrednosti

Prikazana vsota zaposlenih kot % od skupnega zneska
Prikazana vsota zaposlenih kot % od skupnega zneska

8. S pomočjo vrtilne tabele sestavite seznam edinstvenih vrednosti

Ker vrtilne tabele povzemajo podatke, jih je mogoče uporabiti za iskanje edinstvenih vrednosti v polju. To je dober način, da hitro vidite vse vrednosti, ki se pojavijo na polju, in odkrijete tudi pravopisne napake in druge nedoslednosti. Recimo, da imate podatke o prodaji in želite videti seznam vseh prodanih izdelkov. Seznam izdelkov ustvarite tako:

  1. Običajno ustvarite vrtilno tabelo
  2. Dodajte izdelek kot oznako vrstice
  3. Kot vrednost dodajte katero koli drugo besedilno polje (kategorijo, stranko itd.)
  4. Vrtilna tabela bo prikazala seznam vseh izdelkov, ki so prikazani v podatkih o prodaji

Naveden je vsak izdelek, ki je prikazan v podatkih (vključno z napako)
Naveden je vsak izdelek, ki je prikazan v podatkih (vključno z napako)

Video usposabljanje za vrtilno tabelo - hitro, čisto in bistveno

9. Ustvarite samostojno vrtilno tabelo

Ko ustvarite vrtilno tabelo iz podatkov na istem delovnem listu, lahko po želji odstranite podatke in vrtilna tabela bo še naprej normalno delovala. To je zato, ker ima vrtilna tabela skriti pivot ki vsebuje natančen dvojnik podatkov, uporabljenih za izdelavo vrtilne tabele.

  1. Osvežite vrtilno tabelo, da zagotovite, da je predpomnilnik posodobljen (Orodja za vrtilne tabele> Osveži)
  2. Izbrišite delovni list, ki vsebuje podatke
  3. Običajno uporabljajte vrtilno tabelo

Video: Kako narediti samostojno vrtilno tabelo

10. Ročno združite vrtilno tabelo

Čeprav vrtilne tabele samodejno združujejo podatke na različne načine, lahko ročno razvrstite elemente tudi v svoje skupine po meri. Recimo, da imate vrtilno tabelo, ki prikazuje razčlenitev zaposlenih po oddelkih. Recimo, da želite oddelke za inženiring, izpolnjevanje in podporo nadalje razvrstiti v skupino 1, prodajo in trženje pa v skupino 2. Skupina 1 in skupina 2 se ne pojavita v podatkih, ampak sta vaši skupini po meri. Če želite vrtilno tabelo razvrstiti v začasne skupine, skupino 1 in skupino 2:

  1. Pritisnite Control, da izberete vsak element v prvi skupini
  2. Z desno miškino tipko kliknite eno od postavk in v meniju izberite Skupina
  3. Excel ustvari novo skupino 'Group1'
  4. V stolpcu B izberite trženje in prodajo ter združite kot zgoraj
  5. Excel ustvari drugo skupino, 'Group2'

Ročno začnite združevati
Ročno začnite združevati

Na polovici ročnega združevanja - 1. skupina je opravljena
Na polovici ročnega združevanja - 1. skupina je opravljena

Ročno zaključeno združevanje
Ročno zaključeno združevanje

11. Razvrstite številske podatke v obsege

Ena najbolj zanimivih in zmogljivih funkcij vsake vrtilne tabele je možnost združevanja številskih podatkov v obsege ali vedra. Predpostavimo, da imate na primer seznam rezultatov glasovanja, ki vključuje starost volivcev, in želite povzeti rezultate po starostni skupini:

  1. Običajno ustvarite vrtilno tabelo
  2. Dodajte starost kot oznako vrstice, glasujte kot oznako stolpca in ime kot vrednost
  3. Z desno miškino tipko kliknite poljubno vrednost v polju Starost in izberite Skupina
  4. Vnesite 10 kot interval v vnosno polje 'Z:'
  5. Ko kliknete V redu, boste videli podatke o glasovanju, razvrščene po starosti v 10-letne skupine

Video: Kako združiti vrtilno tabelo po starostnem razponu

Izvorni podatki za rezultate glasovanja
Izvorni podatki za rezultate glasovanja

Razvrstitev starostnega polja v 10 -letna vedra
Razvrstitev starostnega polja v 10 -letna vedra

Končano združevanje rezultatov glasovanja po starostnem razponu
Končano združevanje rezultatov glasovanja po starostnem razponu

12. Preimenujte polja za boljšo berljivost

Ko v vrtilno tabelo dodate polja, bo vrtilna tabela prikazala ime, ki se pojavi v izvornih podatkih. Imena polj vrednosti bodo prikazana z 'Sum of' ali 'Count of', ko bodo dodana v vrtilno tabelo. Videli boste na primer Vsota prodaje, Število regij itd. Lahko pa preprosto prepišete to ime s svojim. Samo izberite celico, ki vsebuje polje, ki ga želite preimenovati, in vnesite novo ime.

Preimenujte polje tako, da vnesete prvotno ime
Preimenujte polje tako, da vnesete prvotno ime

13. Ko se Excel pritoži, dodajte presledek v imena polj

Ko poskušate preimenovati polja, lahko naletite na težavo, če poskusite uporabiti popolnoma enako ime polja, ki je prikazano v podatkih. Recimo, da imate v izvornih podatkih polje z imenom Prodaja. Kot polje vrednosti je prikazano kot Vsota prodaje , vendar (smiselno) želite, da to pove Prodaja . Ko pa poskusite uporabiti prodajo, se Excel pritoži, da polje že obstaja, in vrne sporočilo o napaki »Ime polja vrtilne tabele že obstaja«.

Excel ne
Excel ne mara imena vašega novega polja

Kot preprosto rešitev preprosto dodajte presledek na konec imena novega polja. Ne vidite razlike in Excel se ne bo pritoževal.

Če dodate ime presledka, se izognete težavi
Če dodate ime presledka, se izognete težavi

14. V vrtilno tabelo dodajte polje večkrat

Obstaja veliko situacij, ko je smiselno dodati isto polje v vrtilno tabelo več kot enkrat. Morda se zdi nenavadno, vendar lahko isto polje v vrtilno tabelo dodate več kot enkrat. Recimo, da imate vrtilno tabelo, ki prikazuje število zaposlenih po oddelkih.

Štetje deluje v redu, vendar želite prikazati tudi število kot odstotek vseh zaposlenih. V tem primeru je najpreprostejša rešitev, da isto polje dvakrat dodate kot polje vrednosti:

  1. V polje za vrednost dodajte besedilno polje (npr. Ime, ime itd.)
  2. Privzeto boste prešteli besedilna polja
  3. Znova dodajte isto polje v območje vrednosti
  4. Z desno miškino tipko kliknite drugi primerek in spremenite Prikaži vrednosti kot na '% celotne vsote'
  5. Preimenujte obe polji, kot želite

Nastavitev polja za prikaz odstotka skupnega zneska
Nastavitev polja za prikaz odstotka skupnega zneska

Polje Ime je bilo dodano dvakrat
Polje Ime je bilo dodano dvakrat

15. Samodejno formatirajte vsa polja vrednosti

Kadar koli v vrtilni tabeli dodate številsko polje kot vrednost, morate obliko števila nastaviti neposredno v polju. Morda vas bo zamikalo, da bi vrednosti, ki jih vidite v vrtilni tabeli, oblikovali neposredno, vendar to ni dobra ideja, ker ni zanesljiva, saj se vrtilna tabela spreminja. Če nastavite obliko neposredno na polju, bo prikazano polje v želeni obliki, ne glede na to, kako velika ali majhna bo vrtilna tabela.

Predpostavimo na primer vrtilno tabelo, ki prikazuje razčlenitev prodaje po regijah. Ko prvič dodate polje Prodaja v vrtilno tabelo, bo prikazano v obliki splošne številke, saj je to številsko polje. Če želite uporabiti obliko računovodske številke v samem polju:

kako skriti izbrani stolpec v
  1. Z desno miškino tipko kliknite polje Prodaja in v meniju izberite Nastavitve polja vrednosti
  2. V pogovornem oknu z nastavitvami polja Vrednost kliknite gumb Oblika številke
  3. Nastavite obliko na Računovodstvo in za izhod kliknite V redu

Nastavitev oblike neposredno v polju vrednosti
Nastavitev oblike neposredno v polju vrednosti

16. Podrobno preglejte, če si želite ogledati (ali izvleči) podatke za vsemi vsotami

Kadar koli v vrtilni tabeli vidite skupni znesek, lahko preprosto vidite in izvlečete podatke, ki jih sestavljajo, tako da 'vrtate navzdol'. Recimo, da gledate vrtilno tabelo, ki prikazuje število zaposlenih po oddelkih. Vidite lahko, da je na inženirskem oddelku 50 zaposlenih, vendar želite videti dejanska imena. Če si želite ogledati 50 ljudi, ki sestavljajo to številko, dvokliknite neposredno na številko 50 in Excel bo v vaš delovni zvezek dodal nov list, ki vsebuje natančne podatke, uporabljene za izračun 50 inženirjev. Ta isti pristop lahko uporabite za ogled in izvlečenje podatkov za vsotami, kjer koli jih vidite v vrtilni tabeli.

Dvokliknite skupno na
Dvokliknite skupno število za 'vrtanje navzdol'

50 inženirjev, samodejno izvlečenih v nov list
50 motorjev, samodejno izvlečenih v nov list

17. Klonirajte svoje vrtilne tabele, ko potrebujete drug pogled

Ko nastavite eno vrtilno tabelo, boste morda želeli videti drugačen pogled na iste podatke. Seveda lahko preprosto preuredite obstoječo vrtilno tabelo, da ustvarite nov pogled. Če pa sestavljate poročilo, ki ga nameravate stalno uporabljati in posodabljati, je najlažje klonirati obstoječo vrtilno tabelo, tako da sta oba pogleda podatkov vedno na voljo.

Obstajata dva preprosta načina za kloniranje vrtilne tabele. Prvi način je vključeval podvajanje delovnega lista, ki vsebuje vrtilno tabelo. Če imate na delovnem listu nastavljeno vrtilno tabelo z naslovom itd., Lahko z desno tipko miške kliknete zavihek delovnega lista, da ga kopirate v isti delovni zvezek. Drug način kloniranja vrtilne tabele je kopiranje vrtilne tabele in jo prilepite drugam. S temi pristopi lahko naredite toliko kopij, kot želite.

Ko na ta način klonirate vrtilno tabelo, si obe vrtilni tabeli delita enako skriti pivot . To pomeni, da bodo pri osvežitvi katerega koli od klonov (ali izvirnika) osvežene vse povezane vrtilne tabele.

Video: Kako klonirati vrtilno tabelo

18. Odklonite vrtilno tabelo, če jo želite osvežiti neodvisno

Ko ste klonirali vrtilno tabelo, bi lahko naleteli na situacijo, ko res ne želite, da je klon povezan z istim vrtilnim predpomnilnikom kot izvirnik. Pogost primer je, ko ste datumsko polje združili v eno vrtilno tabelo, ga osvežite in odkrili, da ste isto polje z datumom tudi pomotoma združili v drugo vrtilno tabelo, ki je niste nameravali spremeniti. Ko vrtilne tabele delijo isti vrtilni predpomnilnik, si delijo tudi razvrščanje polj.

Tu je en način, da odklonite vrtilno tabelo, to je, da jo prekinite od vrtilnega predpomnilnika, ki ga deli z drugimi vrtilnimi tabelami na istem delovnem listu:

  1. Celotno vrtilno tabelo izrežite v odložišče
  2. Vrtilno tabelo prilepite v popolnoma nov delovni zvezek
  3. Osvežite vrtilno tabelo
  4. Ponovno kopirajte v odložišče
  5. Prilepite ga nazaj v izvirni delovni zvezek
  6. Zavrzite začasni delovni zvezek

Vaša vrtilna tabela bo zdaj uporabljala lasten vrtilni predpomnilnik in se ne bo osvežila z drugimi vrtilnimi tabelami v delovnem zvezku ali delila istega razvrščanja polj.

19. Znebite se neuporabnih naslovov

Privzeta postavitev za nove vrtilne tabele je kompaktna postavitev. Ta postavitev bo prikazala 'Oznake vrstic' in 'Oznake stolpcev' kot naslove v vrtilni tabeli. To niso najbolj intuitivni naslovi, zlasti za ljudi, ki ne uporabljajo pogosto vrtilnih tabel. Preprostih načinov, da se znebite teh čudnih naslovov, je, da postavitev vrtilne tabele preklopite iz kompaktne v orisno ali tabelarno postavitev. Zaradi tega bo vrtilna tabela prikazala dejanska imena polj kot naslove v vrtilni tabeli, kar je veliko bolj smiselno. Če se želite popolnoma znebiti teh oznak, na zavihku Analiza na traku Orodja vrtilne tabele poiščite gumb z imenom Glave polja. S klikom na ta gumb boste popolnoma onemogočili naslove.

Upoštevajte neuporabne in zmedene naslove polj
Upoštevajte neuporabne in zmedene naslove polj

Preklop postavitve iz Compact v Outline
Preklop postavitve iz Compact v Outline

Naslovi polj v postavitvi Outline so veliko bolj smiselni
Naslovi polj v postavitvi Outline so veliko bolj smiselni

20. Okoli vrtilnih tabel dodajte malo praznega prostora

To je le preprost nasvet pri oblikovanju. Vsi dobri oblikovalci vedo, da prijetna oblika zahteva malo belega prostora. Beli prostor pomeni le prazen prostor, ki je namenjen za postavitev prostora za dihanje. Ko ustvarite vrtilno tabelo, vstavite dodaten stolpec na levo in dodatno vrstico ali dve na vrhu. Tako bo vaša vrtilna miza dobila nekaj prostora za dihanje in ustvarila lepšo postavitev. V večini primerov priporočam tudi, da izklopite mrežne črte na delovnem listu. Vrtilna tabela bo predstavljala močno vizualno mrežo, zato so mreže zunaj vrtilne tabele nepotrebne in bodo preprosto ustvarile vizualni šum.

Okoli vrtilnih tabel dodajte malo praznega prostora
Z malo praznega prostora bodo vaše vrtilne mize videti bolj polirane

Navdih: 5 vrtilnih tabel, ki jih še niste videli .

21. Znebite seštevkov vrstic in stolpcev

Vrtilne tabele privzeto prikazujejo vsote za vrstice in stolpce, vendar lahko eno ali obe od teh vsot preprosto onemogočite, če jih ne želite. Na zavihku Vrtilna tabela na traku kliknite gumb Vsote in izberite želene možnosti.

Omogočite in onemogočite skupne vsote
Skupne vsote za vrstice in stolpce lahko odstranite

22. Formatirajte prazne celice

Če imate vrtilno tabelo, ki ima veliko praznih celic, lahko nadzirate znak, ki je prikazan v vsaki prazni celici. Privzeto prazne celice ne bodo prikazale ničesar. Če želite nastaviti svoj znak, z desno miškino tipko kliknite v vrtilni tabeli in izberite možnosti vrtilne tabele. Nato se prepričajte, da je polje »Izprazni celice kot:« in vnesite znak, ki ga želite videti. Upoštevajte, da ta nastavitev upošteva uporabljeno obliko številke. Na primer. če uporabljate obliko številke obračuna za polje s številsko vrednostjo in vnesete ničlo, se v vrtilni tabeli prikaže vezaj '-', saj se tako pri formatu računovodstva prikažejo ničelne vrednosti.

Vrtilna tabela prikazuje prazne celice kot 0 (nič) z obliko računovodstva
Prazne celice, nastavljene na prikaz 0 (nič), oblika računovodske številke pa vam daje vezaje

23. Po potrebi izklopite AutoFit

Ko osvežite vrtilno tabelo, se privzeto stolpci, ki vsebujejo podatke, samodejno prilagodijo tako, da najbolje ustrezajo podatkom. Običajno je to dobra stvar, vendar vas lahko zmeša, če imate poleg vrtilne tabele na delovnem listu še druge stvari ali če ste ročno previdno prilagodili širino stolpcev in ne želite, da se spreminjajo. Če želite onemogočiti to funkcijo, z desno miškino tipko kliknite v vrtilni tabeli in izberite Možnosti vrtilne tabele. Na prvem zavihku možnosti (ali zavihku postavitve na Macu) počistite polje »Samodejno prilagodi širino stolpcev pri posodobitvi«.

Možnost samodejnega prilagajanja stolpca vrtilne tabele za Windows
Možnost samodejnega prilagajanja stolpca vrtilne tabele za Windows

Možnost samodejnega prilagajanja stolpcev vrtilne tabele
Možnost samodejnega prilagajanja stolpcev vrtilne tabele

Se morate naučiti vrtilnih tabel? Imamo trden video trening z delovnimi listi.

Avtor Dave Bruns


^