✅ Priprema izvornih podataka za zaokretnu tablicu

Sadržaj

Što je dobar dizajn izvornih podataka za zaokretnu tablicu?
Uobičajene zamke koje treba izbjegavati prilikom pripreme izvornih podataka
Primjeri loših dizajna izvornih podataka
[STUDIJA SLUČAJA] Pretvaranje loše formatiranih podataka u izvorne podatke spremne za zaokretnu tablicu

Pravilno oblikovanje podataka ključan je korak u stvaranju robusne zaokretne tablice bez grešaka. Ako to ne učinite na pravi način, na kraju ćete imati puno problema s zaokretnom tablicom.

Što je dobar dizajn izvornih podataka za zaokretnu tablicu?

Pogledajmo primjer dobrih izvornih podataka za zaokretnu tablicu.

Evo što ga čini dobrim dizajnom izvornih podataka:

Prvi redak sadrži zaglavlja koja opisuju podatke u stupcima.
Svaki stupac predstavlja jedinstvenu kategoriju podataka. Na primjer, stupac C sadrži samo podatke o proizvodu i samo stupac D i podatke o mjesecima.
Svaki red je zapis koji bi predstavljao jednu instancu transakcije ili prodaje.
Zaglavlja podataka jedinstvena su i ne ponavljaju se nigdje u skupu podataka. Na primjer, ako imate prodajne brojeve za četiri tromjesečja u godini, NE biste ih trebali nazvati prodajom. Umjesto toga, dajte ovim zaglavljima stupaca jedinstvena imena, poput prodaje Q1, prodaje Q2 itd.
- Ako nemate jedinstvene naslove, možete nastaviti stvarati zaokretnu tablicu, a Excel bi ih automatski učinio jedinstvenim dodavanjem sufiksa (kao što su Prodaja, Prodaja2, Prodaja3). Međutim, to bi bio grozan način za pripremu i korištenje zaokretne tablice.

Uobičajene zamke koje treba izbjegavati prilikom pripreme izvornih podataka

U izvornim podacima ne bi trebalo biti praznih stupaca. Ovaj je lako uočiti. Ako u izvornim podacima imate prazan stupac, ne biste mogli izraditi zaokretnu tablicu. Pokazat će pogrešku kao što je prikazano u nastavku.
U izvornim podacima ne bi trebalo biti praznih ćelija/redaka. Iako možete uspješno stvoriti zaokretnu tablicu unatoč tome što imate prazne ćelije ili retke, postoje mnoge nuspojave koje vas mogu ugristi kasnije tijekom dana.
- Na primjer, recimo da imate praznu ćeliju u stupcu prodaje. Ako kreirate zaokretnu tablicu pomoću ovih podataka i stavite polje prodaje u područje stupaca, prikazat će vam se COUNT, a ne SUM. To je zato što Excel tumači cijeli stupac kao tekstualne podatke (samo zbog jedne prazne ćelije).
Primijenite odgovarajući format na ćelije u izvornim podacima. Na primjer, ako imate datume (koji su pohranjeni kao serijski brojevi u pozadini programa Excel), primijenite jedan od prihvatljivih formata datuma. To bi vam pomoglo da stvorite zaokretnu tablicu i koristite Datum kao jedan od kriterija za sažimanje, grupiranje i sortiranje podataka.
- Ako imate nekoliko sekundi, pokušajte ovo. Oblikujte datume u zaokretnoj tablici kao brojeve, a zatim pomoću tih podataka izradite zaokretnu tablicu. Sada u zaokretnoj tablici odaberite polje s datumom i pogledajte što se događa. Automatski će ga staviti u područje vrijednosti. To je zato što vaša zaokretna tablica ne zna da su to datumi. Tumači ih kao brojeve.
Ne uvrštavajte ukupne stupce, zbrojeve redova, prosjeke itd. Kao dio izvornih podataka. Kad dobijete zaokretnu tablicu, kasnije ih možete lako nabaviti.
Uvijek stvorite Excel tablicu, a zatim je upotrijebite kao izvor za zaokretnu tablicu. Ovo je više dobra praksa, a ne zamka. Vaša zaokretna tablica radila bi sasvim dobro s izvornim podacima koji također nisu Excel tablica. Prednost Excel tablice je ta što može prilagoditi proširene podatke. Ako u skup podataka dodate još redaka, ne morate uvijek i opet prilagođavati izvorne podatke. Zaokretnu tablicu možete jednostavno osvježiti i automatski će uzeti u obzir nove retke dodane izvornim podacima.

Primjeri loših dizajna izvornih podataka

Pogledajmo neke loše primjere dizajna izvornih podataka.

Loš dizajn izvornih podataka - Primjer 1

Ovo je uobičajen način za održavanje podataka koje je lako pratiti i razumjeti. Postoje dva problema s ovim rasporedom podataka:

Ne možete dobiti potpunu sliku. Na primjer, možete vidjeti da je prodaja za Srednji zapad u prvom tromjesečju 2924300. No radi li se o pojedinačnoj prodaji ili o broju prodaja. Ako imate svaki zapis dostupan u zasebnom retku, možete napraviti bolju analizu.
Ako krenete naprijed i stvorite zaokretnu tablicu koristeći ovo (što možete), dobit ćete različita polja za različite tromjesečja. Nešto kao što je prikazano ispod:

Loš dizajn izvornih podataka - Primjer 2

Ovaj prikaz podataka može biti dobro prihvaćen od strane uprave i publike PowerPoint prezentacija, ali nije prikladan za stvaranje zaokretne tablice.

Opet, ovo je vrsta sažetka koji možete jednostavno stvoriti pomoću zaokretne tablice. Dakle, čak i ako na kraju želite takav izgled svojih podataka, održavajte izvorne podatke u formatu spremnom za zaokret i stvorite ovaj prikaz pomoću zaokretne tablice.

Loš dizajn izvornih podataka - Primjer 3

Ovo je opet izlaz koji se lako može dobiti pomoću zaokretne tablice. Ali ne može se koristiti za stvaranje zaokretne tablice.

U skupu podataka postoje prazne ćelije, a četvrtine su raspoređene kao zaglavlja stupaca.

Također, regija je navedena pri vrhu, dok bi trebala biti dio svakog zapisa.

[STUDIJA SLUČAJA] Pretvaranje loše formatiranih podataka u izvorne podatke spremne za zaokretnu tablicu

Ponekad možete dobiti skup podataka koji je neprikladan za upotrebu kao izvorni podaci za zaokretnu tablicu. U tom slučaju možda nećete imati drugog izbora nego pretvoriti podatke u format podataka prilagođen zaokretu.

Evo primjera lošeg dizajna podataka:

Sada možete koristiti Excel funkcije ili zaokretni upit za pretvaranje ovih podataka u format koji se može koristiti kao izvorni podaci za zaokretnu tablicu.

Pogledajmo kako funkcioniraju obje ove metode.

Metoda 1: Korištenje Excel formula

Pogledajmo kako koristiti Excel funkcije za pretvaranje ovih podataka u format spreman za zaokretnu tablicu.

Izradite jedinstveno zaglavlje stupca za sve kategorije u izvornom skupu podataka. U ovom primjeru to bi bila regija, tromjesečje i prodaja.
U ćeliji ispod zaglavlja regije upotrijebite sljedeću formulu: = INDEKS ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Povucite formulu prema dolje i ona će ponoviti sve regije.
U ćeliji ispod zaglavlja četvrtine upotrijebite sljedeću formulu: = INDEKS ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1)) , 0))
- Povucite formulu prema dolje i ona će ponoviti sve četvrtine.
U zaglavlju ispod prodaje koristite sljedeću formulu: = INDEKS ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Povucite ga prema dolje da biste dobili sve vrijednosti. Ova formula koristi podatke o regiji i tromjesečju kao vrijednosti pretraživanja i vraća vrijednost prodaje iz izvornog skupa podataka.

Sada ove rezultirajuće podatke možete koristiti kao izvorne podatke za zaokretnu tablicu.

Kliknite ovdje za preuzimanje datoteke primjera.

2. način: Korištenje programa Power Query

Power Query ima značajku koja lako može pretvoriti ovu vrstu podataka u format podataka spreman za zaokret.

Ako koristite Excel 2016, značajke Power Query bile bi dostupne na kartici Podaci u grupi Dohvati i transformiraj. Ako koristite Excel 2013 ili starije verzije, možete ga koristiti kao dodatak.

Evo izvrsnog vodiča o instalaciji Power Query by Jon iz programa Excel Campus.

Opet, s obzirom da ste podatke oblikovali kako je prikazano u nastavku:

Evo koraka za pretvaranje izvornih podataka u format spreman za zaokretnu tablicu:

Pretvorite podatke u Excel tablicu. Odaberite skup podataka i idite na Umetni -> Tablice -> Tablica.
U dijaloškom okviru Umetni tablicu provjerite je li odabran ispravan raspon i kliknite U redu. To će pretvoriti tablične podatke u Excel tablicu.
U Excelu 2016 idite na Podaci -> Preuzmi i transformiraj -> Iz tablice.
- Ako koristite dodatak Power Query u prethodnoj verziji, idite na Power Query -> Vanjski podaci -> Iz tablice.
U uređivaču upita odaberite stupce koje želite otkazati. U ovom slučaju, to su oni za četiri četvrtine. Da biste odabrali sve stupce, držite tipku Shift, a zatim odaberite prvi stupac, a zatim zadnji stupac.
Unutar uređivača upita idite na Transform -> Any Column -> Unpivot Columns. To će pretvoriti podatke stupca u format prilagođen zaokretnoj tablici.
Power Query daje stupcima generičke nazive. Promijenite ta imena u ona koja želite. U tom slučaju promijenite Atribut na tromjesečje, a Vrijednost na Prodaja.
U uređivaču upita idite na Datoteka -> Zatvori i učitaj. Ovo će zatvoriti dijaloški okvir Power Query Editor i stvoriti zasebni radni list koji će imati podatke s neokrenutim stupcima.