Kako povzeti velike nabore podatkov z vrtilnimi tabelami v Officeu

Ko delate v Excelu s stotinami ali tisoči vrstic, razporejenih po več listih, pride do točke, ko ročno filtriranje in seštevanje postane prava nočna mora . Prav tu pridejo na vrsto vrtilne tabele : orodje, zasnovano za pretvorbo kaotičnih podatkov v jasne, primerljive povzetke, ki jih je enostavno razložiti vsakomur, tudi tistim brez izkušenj s preglednicami.

V tem članku bomo podrobno raziskali, kako povzeti velike nabore podatkov z uporabo vrtilnih tabel v sistemu Office (predvsem Excel) , kako konfigurirati polja z vrednostmi, kaj pomenijo različne funkcije povzemanja, kako prikazati rezultate v odstotkih ali razvrstitvah in kako ravnati v bolj zapletenih scenarijih, kot je 30 ali 40 zavihkov z razpršenimi informacijami. Ohranili bomo praktičen in realističen pristop, namenjen tistim, ki svoje dneve preživijo s pogajanji, analiziranjem poročil ali posodabljanjem proračunov.

Kaj je vrtilna tabela in zakaj je tako uporabna pri velikih količinah podatkov?

Vrtilna tabela je v bistvu interaktivni povzetek, ki se začne z »plosko« podatkovno tabelo v Excelu . Od tam lahko polja povlečete v različna območja (vrstice, stolpce, filtre in vrednosti), da si ogledate vsote, povprečja, štetja in številne druge izračune, ne da bi morali pisati zapletene formule.

Lepota vrtilnih tabel je v tem, da lahko informacije reorganizirate v nekaj sekundah : polje lahko spremenite iz vrstic v stolpce, dodate drugo polje z vrednostmi ali pretvorite vsoto v odstotek skupne vsote z le nekaj kliki. Vse to, ne da bi se dotaknili izvornih podatkov, ki ostanejo nedotaknjeni.

Poleg tega so vrtilne tabele idealne za pripovedovanje zgodbe s podatki : prikazujejo, kaj se je spremenilo med enim proračunom in drugim, katere postavke so najbolj zrasle, v katerem scenariju pridobivate ali izgubljate maržo ... In vse to v obliki, ki jo stranke veliko lažje prebavijo kot neskončna tabela s tisoči vrstic.

Končno so odlična rešitev, ko morate odpraviti ročne formule in avtomatizirati vsote, vmesne vsote in tekoče seštevke . Preprosto posodobite vrtilno tabelo in vsi povzetki bodo preračunani glede na izvorni obseg podatkov.

Kako hitro ustvariti vrtilno tabelo v Excelu

Ustvarjanje vrtilne tabele v Excelu je lažje, kot se zdi. V sodobnih različicah sistema Office je osnovni potek dela vedno enak: izberite podatke, izberite možnost vrtilne tabele in nato povlecite polja glede na to, kaj želite analizirati.

Konkretni koraki bi bili naslednji:

1. Pripravite izvorno tabeloPoskrbite, da bodo vaši podatki organizirani v tabelarni obliki, z jasno vrstico glave in brez praznih vrstic vmes. Vsak stolpec mora imeti edinstveno ime (npr. dobavitelj, scenarij, leto, artikel, znesek itd.).
2. Izberite obseg: Postavite kazalec v katero koli celico podatkovne tabele. Excel običajno samodejno zazna celoten obseg, če ni vrzeli.
3. Pojdi na Vstavi → Vrtilna tabelaNa traku kliknite »Vstavi« in nato »Vrtilna tabela«. Odpre se pogovorno okno, kjer lahko potrdite obseg podatkov in izberete, ali želite vrtilno tabelo na novem ali obstoječem listu.
4. Izberite lokacijoObičajno je najbolje, da ga ustvarite na novem listu, da imate čist delovni prostor, če pa ga raje integrirate v obstoječo nadzorno ploščo, ga lahko postavite na obstoječi list.
5. Oblikujte poročilo tako, da povlečete in spustite poljaV podoknu s polji vrtilne tabele povlecite polja, ki jih želite analizirati, v območja »Vrstice«, »Stolpci«, »Filtri« in »Vrednosti«. Excel bo nato samodejno ustvaril povzetek.

Ta osnovni postopek vam omogoča, da v nekaj minutah ustvarite poročila o prodaji, analize stroškov, primerjave scenarijev ali preproste nadzorne plošče, ne da bi morali napisati eno samo formulo. Še posebej je zmogljiv pri obdelavi velikih količin pogosto posodobljenih podatkov.

Razumevanje področja Vrednosti: »Povzemanje vrednosti po«

Eno ključnih področij v kateri koli vrtilni tabeli je razdelek Vrednosti , kjer se izračunajo vsote, povprečja in druge funkcije. Excel privzeto poskuša uganiti, kateri izračun potrebujete: če je polje številsko, uporabi SUM; če zazna besedilo, uporabi COUNT.

To ima pomembno posledico: mešanje besedila in številk v istem stolpcu pogosto povzroča težave . Če polje, ki bi moralo biti številsko, vsebuje celico z besedilom (na primer »N/A« ali vezaj), ga lahko Excel interpretira kot besedilo in namesto SUM uporabi COUNT. Zato je tako pomembno, da podatke pravilno očistite in se izognete mešanju podatkovnih tipov v poljih, ki bodo uporabljena za vrednosti.

Če želite spremeniti izračun polja z vrednostjo, lahko to storite v sami zasnovi vrtilne tabele:

• Kliknite puščico, ki se prikaže desno od imena polja v območju Vrednosti.
• Izberite možnost »Konfiguracija polja vrednosti«.
• Tab "Povzemi vrednosti po"Izberite funkcijo, ki jo želite uporabiti (SUM, AVERAGE, MAX itd.).

Ko spremenite način izračuna, bo Excel samodejno posodobil ime polja po meri in dodal nekaj takega, kot je »Vsota zneskov« ali »Povprečje zneskov«. To besedilo lahko spremenite v polju »Ime po meri«, da prikažete jasnejši naslov, na primer »Skupni znesek« ali »Letno povprečje«.

Poleg tega lahko v istem oknu z gumbom »Oblika števil« določite slog oblikovanja za celotno polje (valuta, decimalno število, odstotki itd.), tako da vam ni treba spreminjati oblike za vsako celico posebej znotraj vrtilne tabele.

Funkcije povzetka, ki so na voljo v območju Vrednosti

Ko dostopate do nastavitev za polje z vrednostjo, se na zavihku »Povzemi vrednosti po« prikaže seznam funkcij povzemanja. Vsaka funkcija strne podatke na drugačen način , zato je pomembno natančno razumeti, kaj vsaka od njih počne, da se izognete napačni razlagi rezultatov.

To so standardne funkcije povzemanja, ki jih Excel ponuja za polja v vrtilni tabeli:

• SUMA: izračuna vsoto vseh numeričnih vrednosti v polju. Je privzeta možnost za numerične podatke in najpogosteje uporabljena za zneske, količine in kumulativne metrike.
• Count: vrne skupno število (nepraznih) zapisov v polju, ne glede na to, ali so besedilo ali številke. Deluje podobno kot funkcija COUNTA v preglednici.
• POVPREČNO: izračuna aritmetično sredino vrednosti polj. Zelo uporabno, če želite videti na primer povprečni znesek na leto, na dobavitelja ali na kategorijo.
• MAX: prikaže največjo vrednost v naboru podatkov za to polje. To je uporabno za zaznavanje najvišjega zneska, najnovejšega datuma itd.
• MIN: prikazuje minimalno vrednost. Dopolnjuje MAX, kadar želite poznati ekstremne vrednosti porazdelitve podatkov.
• IZDELEKTa funkcija izračuna produkt vseh vrednosti (jih pomnoži). Je manj pogosta funkcija za standardno analizo, vendar je na voljo, če potrebujete to vrsto agregacije.
• Štetje števil: Prešteje, koliko številskih vrednosti je v polju, pri čemer prezre besedilo. Podobna je funkciji COUNT v navadni preglednici.
• STDEV: ocenjuje standardni odklon a kaže populacije. Uporablja se, kadar podatki predstavljajo le del možnega skupnega števila.
• DESVESTP: izračuna standardni odklon celotnega populacija podatkov, ki se povzemajo, ob predpostavki, da ne gre za delni vzorec, temveč za celoten nabor.
• VAR: izračuna varianco vzorca, torej kako razpršeni so podatki glede na povprečje, glede na to, da delate z vzorcem.
• VARP: izračuna varianco celotne populacije, podobno kot VAR, vendar uporabljeno za vse podatke.
• Različno štetjeTo šteje število edinstvenih vrednosti v polju. Idealno je za poznavanje števila različnih strank, števila edinstvenih elementov, ki se prikažejo itd. Na voljo je le, če uporabljate podatkovni model v Excelu.ne v vseh standardnih vrtilnih tabelah.

Izbira ene ali druge funkcije bo vplivala na odgovore na vprašanja, kot so »Koliko je bilo skupno porabljenih na scenarij?« (SUM), »Koliko artiklov je na dobavitelja?« (Count) ali »Kakšna je tipična razlika v zneskih med različnimi leti?« (STDEV/VAR).

Rezultate prikažite drugače: »Prikaži vrednosti kot«

Poleg spreminjanja funkcije povzemanja Excel v nastavitvah polja z vrednostmi ponuja še en zmogljiv zavihek: »Prikaži vrednosti kot« . Tukaj ne spreminjate osnovnega izračuna (ostane vsota, povprečje itd.), temveč način predstavitve rezultata , na primer kot odstotek, razlika od osnovne vrednosti ali kumulativna vsota.

Za dostop do teh možnosti se vrnite v pogovorno okno »Nastavitve polja vrednosti« in pojdite na zavihek »Prikaži vrednosti kot« . Našli boste seznam izračunov po meri, ki jih lahko uporabite za rezultat funkcije povzemanja.

Nekatere najbolj uporabne možnosti so naslednje:

• Brez izračuna: prikaže vrednost takšno, kot je, brez dodatnih transformacij. To je privzeto vedenje.
• % celotnega zneska: prikaže vsako vrednost kot odstotek celotnega seštevka vrtilne tabele. Zelo uporabno za prikaz deleža posamezne kategorije, scenarija ali leta v seštevku.
• % vseh stolpcevVsaka vrednost je izražena kot odstotek skupne vrednosti v svojem stolpcu. To je idealno, kadar želite primerjati, kako je relativna teža porazdeljena med vrsticami znotraj istega stolpca.
• % vseh vrsticEnako kot prejšnji, vendar se kot referenca uporablja vsota vsake vrstice. To boste uporabili, ko je glavna dimenzija analize v vrsticah.
• % de: omogoča prikaz vrednosti v odstotkih Osnovni element znotraj a Baza CampoNa primer, lahko vidite, koliko vsako leto predstavlja v primerjavi z osnovnim letom 1 ali vsak scenarij v primerjavi z referenčnim scenarijem.
• % vseh glavnih vrstic y % vseh glavnih stolpcevTi izračunajo odstotek vrednosti nadrejenega elementa v hierarhičnih vrstičnih ali stolpčnih strukturah. Zelo uporabni so pri delu z ravnmi podrobnosti (npr. kategorija → podkategorija).
• % glavnega zneska: prikaže vsako vrednost kot odstotek skupne vrednosti izbranega osnovnega elementa, pri čemer združuje obe osi analize.
• Razlika: pretvori vrednosti v razliko glede na osnovni element (npr. trenutni scenarij minus prejšnji scenarij) v izbranem osnovnem polju.
• % razlike od: enako kot prejšnji, vendar izraženo v odstotkih. Omogoča vam, da vidite, koliko se je vrednost povečala ali zmanjšala glede na osnovni element.
• Skupaj zbranoTa funkcija pretvori zaporedne vrednosti v kumulativni seštevek v osnovnem polju. Zelo uporabna je za analizo časovnih vrst (meseci, leta) ali za vizualizacijo napredovanja.
• % od skupnega zneska v: združuje koncept odstotka in kumulativne vsote glede na izbrano osnovno polje.
• Razvrsti od najmanjšega do največjega: dodeli številčno razvrstitev, začenši z najmanjšo vrednostjo (1 je najnižja). Vsaka večja vrednost ima višjo številko razvrstitve.
• Razvrsti od najvišjega do najnižjega: ustvari obratno razvrstitev: 1 je največja vrednost, naslednje pa predstavljajo vedno manjše vrednosti.
• KazaloIzračunajte relativni indeks z uporabo te formule:
  ((vrednost v celici) × (Skupna vsota skupnih vsot)) / ((Skupna vsota vrstic) × (Skupna vsota stolpcev)).
  Naprednejši izračun je primerjava relativne pomembnosti posameznega križišča glede na skupno pomembnost.

S temi možnostmi lahko na primer preklopite z ogleda absolutnih zneskov na ogled odstotnih deležev ali variacij med scenariji, ne da bi pri tem podvajali podatke ali ustvarjali nove stolpce v viru.

Če izbrani izračun to zahteva, boste morali določiti tudi osnovno polje (na primer »Scenarij« ali »Leto«) in osnovni element (na primer »Začetni scenarij« ali »2022«), da bo Excel vedel, s katero referenčno vrednostjo naj se primerja.

Prikaz istih podatkov kot vsote in odstotka hkrati

Pogosto je koristno videti tako absolutno vrednost kot njen odstotek glede na skupno vrednost ali osnovni element. V vrtilni tabeli lahko to dosežemo s preprostim trikom: isto polje dvakrat povlečemo v območje Vrednosti.

Postopek bi bil:

1. V podoknu polj vrtilne tabele povlecite številsko polje (na primer Znesek) na območje Vrednosti.
2. Povlecite isto polje Znesek nazaj v Vrednosti. Zdaj boste imeli dve polji z vrednostmi, ki temeljita na istem stolpcu s podatki.
3. V prvem primeru pojdite na »Nastavitve polja z vrednostmi« in v razdelku »Povzemi vrednosti po« pustite želeno funkcijo (običajno SUM) in v razdelku »Brez izračuna« izberite »Prikaži vrednosti kot«.
4. V drugem primeru pojdite v nastavitve in obdržite SUM ali drugo osnovno funkcijo ter spremenite »Prikaži vrednosti kot« v % celotnega zneskaali možnost, ki najbolj ustreza (npr. % razlike, % vseh vrstic itd.).

Na ta način bo ista vrtilna tabela prikazala na primer vsoto zneskov in njihov odstotek od skupnega zneska . To stranki olajša razlago ne le razlik med scenariji, temveč tudi, koliko teže ima vsak element v skupnem znesku.

Spremenite ime in obliko polj z vrednostmi

Ko spremenite funkcije ali načine prikaza vrednosti, Excel ponavadi ustvari samodejna imena, kot so »Vsota zneska«, »% od skupnega zneska« itd. Čeprav so ta imena uporabna, niso vedno najjasnejši način za predstavitev poročila nekomu, ki ni seznanjen z vrtilnimi tabelami.

V polju »Nastavitve polja vrednosti« lahko:

• Glej Izvirno ime, kar je dejansko ime polja v izvirnem obsegu podatkov.
• Spremenite Ime po merikar je prikazano v vrtilni tabeli. Preprosto kliknite v besedilno polje in vnesite nekaj bolj razumljivega, na primer »Skupni trenutni scenarij«, »% od skupnega zneska«, »Letno povprečje po ponudniku« itd.

Poleg tega gumb »Oblika števil« odpre standardno pogovorno okno »Oblika celic«, kjer lahko:

• Izberite Moneda Za zneske uporabite simbol in število decimalnih mest, ki vas zanima.
• Uporabite Številka z ločilom tisočic za velike vrednosti.
• aplicar Odstotek z ustreznimi decimalnimi mesti.
• Izberite katero koli drugo obliko zapisa (datum, po meri itd.), odvisno od vrste podatkov.

To oblikovanje bo dosledno uporabljeno za vse celice v tem polju z vrednostjo znotraj vrtilne tabele, s čimer boste prihranili čas in preprečili nedosledno in težko berljivo oblikovanje poročila.

Povzemanje podatkov, razpršenih po več zavihkih: primer scenarijev

Zelo pogosta situacija v pogajanjih o pogodbah, proračunu za programsko opremo ali večletnem načrtovanju je, da imamo na ducate enakih zavihkov z različnimi scenariji . Na primer, en list na datum ali različico proračuna, vsi z enako strukturo polj, vendar z različnimi podatki.

Predstavljajte si predlogo, kjer delate z 10 do 40 zavihki , od katerih vsak predstavlja scenarij (običajno drugačen datum predloga). Vsak list vključuje več let, različne razdelke ter pojasnjevalno besedilo in glave, ki jih želijo natisniti določene stranke. Rezultat je, da ima vsak zavihek:

• Več ločenih podatkovnih blokov (oddelek 1, oddelek 2 itd.).
• Vrstice, ki so relevantni podatki za vrtilno tabelo.
• Vrstice, ki so samo naslovi ali opisi, ki jih ne želite mešati s podatki.

Konec koncev, za izdelavo konsolidirane vrtilne tabele mnogi ljudje ročno kopirajo ustrezne vrstice iz vsakega zavihka na list z imenom »konsolidirano «, kjer združijo vse scenarije. Nato iz tega lista ustvarijo vrtilno tabelo. To je pristop, ki deluje, vendar ima več težav:

• Es Počasen in nagnjen k napakamše posebej, če je treba to pogosto ponavljati.
• Če nato na zavihku scenarija nekaj spremenite, ne pozabite posodobiti tudi konsolidiranega lista, kar ustvari podvojene podatke.
• Ko stranke spreminjajo posamezne zavihke, pogosto domnevajo, da se bo vrtilna tabela posodobila sama, medtem ko v resnici konsolidiranega obsega niso dotaknili.

V zapletenih scenarijih, kjer ima lahko vsak list od 10 do nekaj tisoč vrstic, te vrstice pa niso vedno v istem obsegu (na primer, zavihek 1 uporablja vrstice 8–33, 56–70, 101–300; zavihek 2 uporablja vrstice 12–16, 44, 90–99, 456–848 itd.), postane ustvarjanje poimenovanih obsegov na blok nepraktično . Če vsak zavihek potrebuje 15–20 različnih obsegov, je upravljanje te količine referenc izjemno okorno.

Power Pivot ali podatkovni model sta morda elegantnejša rešitev, vendar v mnogih primerih ustvarjata več režijskih stroškov, kot je potrebno , zlasti v računalnikih Mac ali računalnikih z manjšo porabo virov, za številne napredne poslovne uporabnike pa sta lahko pretirana za vsakodnevno uporabo.

Praktične strategije za združevanje velikih, razpršenih naborov podatkov

Brez uporabe preveč naprednih orodij lahko sledite nekaterim pristopom, s katerimi boste lažje obvladovali združevanje podatkov, razpršenih po več zavihkih, za vaše vrtilne tabele:

• Definirajte ravno podatkovno strukturo za vsak scenarijČeprav zavihke ohranjate »lepe« za tiskanje, poskusite na vsakem listu rezervirati jasno definiran tabelarni blok (brez vmesnih glav), kamor vnesete vse podatke, ki jih želite analizirati z vrtilno tabelo. Vsako okrasno ali opisno besedilo se lahko nahaja nad ali na drugem območju, tako da imate en sam, sosednji obseg, ki ga lahko uporabite.
• Uporabite "glavni" list z vsemi vrsticamiNamesto ročnega kopiranja vsakič znova lahko model oblikujete tako, da je konsolidirani list edini "resen" vir podatkov, in celo avtomatizirate izpis podatkov iz zavihkov z uporabo formul, Power Queryja (v sistemu Windows) ali nekaj podobnega. VBA Če vam je uporaba makrov udobna. Ključno je, da stranka razume, da Vrtilne tabele temeljijo izključno na tem konsolidiranem listu..
• Zelo dobro označite kontekstna poljaPri združevanju podatkov dodajte stolpce, ki jasno označujejo, iz katerega zavihka prihajajo podatki: Scenarij, Datum, Različica itd. To vam bo omogočilo enostavno filtriranje in primerjavo scenarijev pozneje v vrtilni tabeli.
• Odločite se, katere vrstice ne smejo biti nikoli vključene v vrtilno tabeloNamesto da vsakič iščete »na oko«, definirajte pravila (na primer samo vrstice, kjer stolpec »Element« ni prazen in je stolpec »Vrsta« »Podrobnosti«) in ta merila dosledno uporabljajte, da zmanjšate ročno delo.

Pomembno je sprejeti, da ko delate s 35 ali 40 zavihki z nekoliko drugačnimi strukturami, glavni problem ni sama vrtilna tabela, temveč način organizacije izvornih podatkov . Bolj ko jo lahko standardizirate v konsolidirano tabelarično obliko, manj težav boste imeli s posodabljanjem in razlago rezultatov.

Dodatni nasveti za boljši izkoristek vrtilnih tabel z velikimi količinami podatkov

Ko so vaše vrtilne tabele napajane z zelo velikimi obsegi (tako v vrsticah kot v stolpcih), je priporočljivo upoštevati nekaj dobrih praks, da se izognete izgubi nadzora in da bo poročilo razumljivo vsem vpletenim.

Nekaj ​​koristnih nasvetov je:

• Ohranite čiste podatkovne tipeIzogibajte se mešanju besedila, številk in praznih celic v istem stolpcu, če bo ta stolpec šel v območje Vrednosti. Če morate nekaj označiti kot »neveljavno«, uporabite številsko kodo (na primer -1) in jo razložite v ločeni legendi.
• Ne preobremenjujte preveč polj hkrati.Vrtilna tabela z 8 vrsticami, 5 stolpci in 4 vrednostmi je praktično nemogoče berljiva. Bolje je ustvariti več enostavnejših povzetkov kot enega nerazumljivega.
• Izboljšajte vidna imenaUporabite polje »Ime po meri«, da zagotovite, da so naslovi smiselni s poslovnega vidika in ne le s tehničnega. Tako bo poročilo »pripovedovalo zgodbo« veliko bolj naravno.
• Razložite transformacijeČe uporabljate izračune »Prikaži vrednosti kot« (odstotki, razlike, kumulativne vsote, uvrstitve), v samem poročilu jasno navedite, kaj pomenijo, da nihče ne bo odstotek razlagal kot absolutno vrednost ali obratno.
• Izkoristite filtre in segmentacijePri veliki količini podatkov je bolje uporabniku omogočiti filtriranje po scenariju, letu, ponudniku ali drugih ključnih dimenzijah, kot pa poskušati prikazati vse naenkrat.

Z uporabo teh idej postanejo vrtilne tabele nekakšna zelo prilagodljiva nadzorna plošča , kjer lahko preidete od pregleda do določenih podrobnosti, ne da bi morali ponavljati izračune ali spreminjati formule.

Konec koncev vam obvladovanje možnosti, kot sta »Povzemi vrednosti po« in »Prikaži vrednosti kot«, izbira pravih funkcij povzemanja (SUM, AVERAGE, MAX, Distinct Count itd.) in premišljeno načrtovanje konsolidacije podatkov iz več zavihkov omogoča, da kaotične sklade Excelovih preglednic pretvorite v jasna, primerljiva in zelo prepričljiva poročila . Ta razlika je opazna, ko morate utemeljiti odločitve, se pogajati z dobavitelji ali preprosto dokazati, da dobro razumete številke, tudi v najzahtevnejših scenarijih.

Povezani članek:

Avtomatizirajte grafikone z imenovanimi obsegi in dinamičnimi serijami v Excelu

Isaac

Strasten pisec o svetu bajtov in tehnologije nasploh. Rad delim svoje znanje s pisanjem in to je tisto, kar bom počel v tem blogu, saj vam bom pokazal vse najbolj zanimive stvari o pripomočkih, programski opremi, strojni opremi, tehnoloških trendih in še več. Moj cilj je, da vam pomagam krmariti po digitalnem svetu na preprost in zabaven način.