✅ Priprava izvornih podatkov za vrtilno tabelo

Kazalo

Kaj je dobro oblikovanje izvornih podatkov za vrtilno tabelo?
Pogoste pasti, ki se jim je treba izogniti pri pripravi izvornih podatkov
Primeri slabih virov podatkovnih modelov
[ŠTUDIJA PRIMERA] Pretvorba slabo oblikovanih podatkov v izvorne podatke, pripravljene za vrtilno tabelo

Pravilna oblika podatkov je ključni korak pri ustvarjanju robustne vrtilne tabele brez napak. Če tega ne storite pravilno, lahko naletite na veliko težav s svojo vrtilno tabelo.

Kaj je dobro oblikovanje izvornih podatkov za vrtilno tabelo?

Oglejmo si primer dobrih izvornih podatkov za vrtilno tabelo.

To je tisto, zaradi česar je dobra zasnova izvornih podatkov:

Prva vrstica vsebuje glave, ki opisujejo podatke v stolpcih.
Vsak stolpec predstavlja edinstveno kategorijo podatkov. V stolpcu C so na primer samo podatki o izdelku in samo stolpec D in podatki o mesecih.
Vsaka vrstica je zapis, ki bi predstavljal en primerek transakcije ali prodaje.
Glave podatkov so edinstvene in se ne ponavljajo nikjer v naboru podatkov. Na primer, če imate prodajne številke za štiri četrtletja v enem letu, ne bi smeli imenovati vseh teh kot prodaje. Namesto tega dajte naslovom stolpcev edinstvena imena, na primer Sales Q1, Sales Q2 itd.
- Če nimate edinstvenih naslovov, lahko še vedno ustvarite vrtilno tabelo in Excel bi jih samodejno naredil edinstvene z dodajanjem pripon (na primer Prodaja, Prodaja2, Prodaja3). Vendar bi bil to grozen način za pripravo in uporabo vrtilne tabele.

Pogoste pasti, ki se jim je treba izogniti pri pripravi izvornih podatkov

V izvornih podatkih ne sme biti praznih stolpcev. To je enostavno opaziti. Če imate v izvornih podatkih prazen stolpec, ne boste mogli ustvariti vrtilne tabele. Prikazal bo napako, kot je prikazano spodaj.
V izvornih podatkih ne sme biti praznih celic/vrstic. Kljub temu, da imate prazne celice ali vrstice, lahko uspešno ustvarite vrtilno tabelo, vendar vas lahko čez dan ugriznejo številni stranski učinki.
- Recimo, da imate v stolpcu prodaje prazno celico. Če s temi podatki ustvarite vrtilno tabelo in v polje stolpcev postavite prodajno polje, se prikaže COUNT in ne SUM. To je zato, ker Excel razlaga celoten stolpec kot besedilne podatke (samo zaradi ene prazne celice).
Uporabite ustrezno obliko za celice v izvornih podatkih. Na primer, če imate datume (ki so shranjeni kot serijske številke v zaledju v Excelu), uporabite eno od sprejemljivih oblik datuma. To bi vam pomagalo ustvariti vrtilno tabelo in uporabiti datum kot eno od meril za povzemanje, združevanje in razvrščanje podatkov.
- Če imate nekaj sekund, poskusite to. Formatirajte datume v vrtilni tabeli kot številke in nato s temi podatki ustvarite vrtilno tabelo. Zdaj v vrtilni tabeli izberite polje z datumom in poglejte, kaj se zgodi. Samodejno ga bo postavil v območje vrednosti. To je zato, ker vaša vrtilna tabela ne ve, da so to datumi. To razlaga kot številke.
Kot del izvornih podatkov ne vključite nobenih seštevkov stolpcev, seštevkov vrstic, povprečja itd. Ko imate vrtilno tabelo, jih lahko pozneje preprosto dobite.
Vedno ustvarite Excelovo tabelo in jo nato uporabite kot vir za vrtilno tabelo. To je bolj dobra praksa in ne pasti. Vaša vrtilna tabela bi dobro delovala z izvornimi podatki, ki niso tudi Excelova tabela. Prednost Excelove tabele je, da lahko prilagodi razširjene podatke. Če v niz podatkov dodate več vrstic, vam ni treba vedno znova prilagajati izvornih podatkov. Vrtilno tabelo lahko preprosto osvežite in samodejno bo upoštevala nove vrstice, dodane izvornim podatkom.

Primeri slabih virov podatkovnih modelov

Oglejmo si nekaj slabih primerov zasnove izvornih podatkov.

Slaba zasnova izvornih podatkov - primer 1

To je običajen način za vzdrževanje podatkov, saj jim je enostavno slediti in jih razumeti. Pri tej razporeditvi podatkov obstajata dve težavi:

Ne dobite popolne slike. Na primer, prodaja prvega četrtletja na Srednjem zahodu je 2924300. Ali gre za eno samo prodajo ali več prodaj. Če imate vsak zapis na voljo v ločeni vrstici, lahko naredite boljšo analizo.
Če boste s tem ustvarili vrtilno tabelo (kar lahko), boste dobili različna polja za različna četrtletja. Nekaj, kot je prikazano spodaj:

Napačno oblikovanje izvornih podatkov - primer 2

To predstavitev podatkov lahko vodstvo in občinstvo predstavitev PowerPoint dobro sprejmeta, vendar ni primerno za ustvarjanje vrtilne tabele.

Tudi to je povzetek, ki ga lahko preprosto ustvarite s pomočjo vrtilne tabele. Torej, tudi če sčasoma želite takšen pogled na svoje podatke, ohranite izvorne podatke v obliki, pripravljeni za vrtilno enoto, in ustvarite ta pogled s pomočjo vrtilne tabele.

Napačno oblikovanje izvornih podatkov - primer 3

To je spet rezultat, ki ga je mogoče zlahka pridobiti s pomočjo vrtilne tabele. Vendar ga ni mogoče uporabiti za ustvarjanje vrtilne tabele.

V naboru podatkov so prazne celice, četrtine pa so razporejene kot glave stolpcev.

Na vrhu je navedena tudi regija, ki pa mora biti del vsakega zapisa.

[ŠTUDIJA PRIMERA] Pretvorba slabo oblikovanih podatkov v izvorne podatke, pripravljene za vrtilno tabelo

Včasih lahko dobite nabor podatkov, ki ni primeren za uporabo kot izvorni podatki za vrtilno tabelo. V takem primeru morda ne boste imeli druge izbire, kot da podatke pretvorite v obliko podatkov, prijazno do vrtilne enote.

Tu je primer slabe zasnove podatkov:

Zdaj lahko z Excelovimi funkcijami ali vrtilno poizvedbo pretvorite te podatke v obliko, ki jo lahko uporabite kot izvorne podatke za vrtilno tabelo.

Poglejmo, kako delujeta obe metodi.

1. način: Uporaba Excelovih formul

Poglejmo, kako s funkcijami Excel pretvoriti te podatke v obliko, pripravljeno za vrtilno tabelo.

Ustvarite edinstveno glavo stolpca za vse kategorije v prvotnem naboru podatkov. V tem primeru bi to bila regija, četrtletje in prodaja.
V celici pod glavo regije uporabite naslednjo formulo: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Povlecite formulo navzdol in ponovila bo vsa območja.
V celici pod glavo četrtine uporabite naslednjo formulo: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1)) , 0))
- Povlecite formulo navzdol in ponovila bo vse četrtine.
V glavi pod »Prodaja« uporabite naslednjo formulo: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Povlecite navzdol, da dobite vse vrednosti. Ta formula uporablja podatke o regiji in četrtini kot vrednosti iskanja in vrne prodajno vrednost iz prvotnega nabora podatkov.

Zdaj lahko te dobljene podatke uporabite kot izvorne podatke za vrtilno tabelo.

Kliknite tukaj, če želite prenesti primer datoteke.

2. način: Uporaba Power Query

Power Query ima funkcijo, ki lahko preprosto pretvori tovrstne podatke v podatkovno obliko, pripravljeno za Pivot.

Če uporabljate Excel 2016, bi bile funkcije Power Query na voljo na zavihku Podatki v skupini Get & Transform. Če uporabljate Excel 2013 ali starejše različice, ga lahko uporabite kot dodatek.

Tukaj je odličen vodnik o namestitvi Power Query Jona iz Excelovega kampusa.

Še enkrat, glede na to, da imate oblikovane podatke, kot je prikazano spodaj:

Tu so koraki za pretvorbo izvornih podatkov v obliko, pripravljeno za vrtilno tabelo:

Pretvorite podatke v Excelovo tabelo. Izberite nabor podatkov in pojdite na Vstavi -> Tabele -> Tabela.
V pogovornem oknu Vstavi tabelo se prepričajte, da je izbran pravilen obseg, in kliknite V redu. S tem se tabelarni podatki pretvorijo v Excelovo tabelo.
V Excelu 2016 pojdite na Podatki -> Pridobi in preoblikuj -> Iz tabele.
- Če v prejšnji različici uporabljate dodatek Power Query, pojdite na Power Query -> Zunanji podatki -> Iz tabele.
V urejevalniku poizvedb izberite stolpce, ki jih želite razveljaviti. V tem primeru so to tisti za štiri četrtine. Če želite izbrati vse stolpce, pridržite tipko Shift in nato izberite prvi stolpec in nato zadnji stolpec.
V urejevalniku poizvedb pojdite na Transform -> Any Column -> Unpivot Columns. S tem se podatki stolpca pretvorijo v obliko, prijazno do vrtilne tabele.
Power Query daje splošna imena stolpcem. Spremenite ta imena v tista, ki jih želite. V tem primeru spremenite Attribute na Quarter in Value na Sales.
V urejevalniku poizvedb pojdite na Datoteka -> Zapri in naloži. S tem boste zaprli pogovorno okno Urejevalnik poizvedb Power in ustvarili ločen delovni list, ki bo vseboval podatke z nespremenjenimi stolpci.