✅ Avota datu sagatavošana rakurstabulai

Satura rādītājs

Kāds ir labs rakurstabulas avota datu dizains?
Bieži sastopamas kļūdas, no kurām jāizvairās, sagatavojot avota datus
Sliktu avota datu dizaina piemēri
[GADĪJUMA STUDIJS] Slikti formatētu datu pārvēršana rakurstabulas gatavos avota datos

Datu pareiza formāta iegūšana ir būtisks solis, lai izveidotu stabilu un bez kļūdām rakurstabulu. Ja tas nav izdarīts pareizi, galu galā var rasties daudz problēmu ar rakurstabulu.

Kāds ir labs rakurstabulas avota datu dizains?

Apskatīsim piemēru labiem avota datiem rakurstabulai.

Lūk, kas padara to par labu avota datu dizainu:

Pirmajā rindā ir galvenes, kas apraksta sleju datus.
Katra kolonna attēlo unikālu datu kategoriju. Piemēram, C slejā ir tikai produkta dati, bet D slejā un tikai mēnešu dati.
Katra rinda ir ieraksts, kas atspoguļo vienu darījuma vai pārdošanas gadījumu.
Datu galvenes ir unikālas un nekur neatkārtojas datu kopā. Piemēram, ja jums ir pārdošanas skaitļi četros ceturkšņos gadā, jums nevajadzētu tos visus nosaukt par pārdošanu. Tā vietā piešķiriet šo sleju galvenēm unikālus nosaukumus, piemēram, Pārdošana Q1, Pārdošana Q2 utt.
- Ja jums nav unikālu nosaukumu, joprojām varat izveidot rakurstabulu, un programma Excel tos automātiski padarīs unikālus, pievienojot sufiksu (piemēram, Pārdošana, Pārdošana2, Pārdošana3). Tomēr tas būtu šausmīgs veids, kā sagatavot un izmantot rakurstabulu.

Bieži sastopamas kļūdas, no kurām jāizvairās, sagatavojot avota datus

Avota datos nedrīkst būt tukšas kolonnas. Šo ir viegli pamanīt. Ja avota datos ir tukša kolonna, jūs nevarēsit izveidot rakurstabulu. Tas parādīs kļūdu, kā parādīts zemāk.
Avota datos nedrīkst būt tukšas šūnas/rindas. Lai gan jūs varat veiksmīgi izveidot rakurstabulu, neskatoties uz tukšām šūnām vai rindām, ir daudz blakusparādību, kas var jūs iekost vēlāk.
- Piemēram, pieņemsim, ka pārdošanas slejā ir tukša šūna. Ja, izmantojot šos datus, izveidojat rakurstabulu un sleju apgabalā ievietojat pārdošanas lauku, tajā tiks parādīts COUNT, nevis SUM. Tas ir tāpēc, ka Excel visu kolonnu interpretē kā teksta datus (tikai vienas tukšas šūnas dēļ).
Piemērot formātu avota datu šūnām. Piemēram, ja jums ir datumi (kas tiek saglabāti kā sērijas numuri programmā Excel), izmantojiet kādu no pieņemamajiem datuma formātiem. Tas palīdzētu jums izveidot rakurstabulu un izmantot datumu kā vienu no datu apkopošanas, grupēšanas un kārtošanas kritērijiem.
- Ja jums ir pāris sekundes, izmēģiniet šo. Formatējiet rakurstabulas datumus kā skaitļus un pēc tam izveidojiet rakurstabulu, izmantojot šos datus. Tagad rakurstabulā atlasiet datuma lauku un skatiet, kas notiek. Tas automātiski tiks ievietots vērtību apgabalā. Tas ir tāpēc, ka jūsu rakurstabula nezina, ka tie ir datumi. Tas tos interpretē kā skaitļus.
Avota datos neiekļaujiet sleju kopsummas, rindu kopsummas, vidējos rādītājus utt. Kad jums ir rakurstabula, varat tos viegli iegūt vēlāk.
Vienmēr izveidojiet Excel tabulu un pēc tam izmantojiet to kā rakurstabulas avotu. Tā ir vairāk laba prakse, nevis kļūda. Jūsu rakurstabula lieliski darbotos ar avota datiem, kas arī nav Excel tabula. Excel tabulas priekšrocība ir tā, ka tā var pielāgot paplašināmos datus. Ja datu kopai pievienojat vairāk rindu, avota dati nav jāpielāgo atkal un atkal. Jūs varat vienkārši atsvaidzināt rakurstabulu, un tā automātiski ņems vērā avota datiem pievienotās jaunās rindas.

Sliktu avota datu dizaina piemēri

Apskatīsim dažus sliktos avota datu dizaina piemērus.

Slikta avota datu dizains - 1. piemērs

Tas ir izplatīts veids, kā saglabāt datus, jo tiem ir viegli sekot un saprast. Šai datu sakārtošanai ir divas problēmas:

Jūs nesaņemat pilnīgu priekšstatu. Piemēram, 1. ceturksnī vidējo rietumu pārdošanas apjoms ir 2924300. Bet vai tā ir viena pārdošana vai vairāki pārdošanas apjomi. Ja katrs ieraksts ir pieejams atsevišķā rindā, varat veikt labāku analīzi.
Ja turpināsit un izveidosit rakurstabulu, izmantojot šo (ko varat), jūs iegūsit dažādus laukus dažādiem ceturkšņiem. Kaut kas, kā parādīts zemāk:

Slikta avota datu dizains - 2. piemērs

Šo datu attēlojumu vadība un PowerPoint prezentāciju auditorija var uztvert labi, taču tas nav piemērots, lai izveidotu rakurstabulu.

Arī šāds kopsavilkums ir viegli izveidojams, izmantojot rakurstabulu. Tātad, pat ja jūs galu galā vēlaties šādu datu izskatu, saglabājiet avota datus rakursam gatavā formātā un izveidojiet šo skatu, izmantojot rakurstabulu.

Slikta avota datu dizains - 3. piemērs

Tas atkal ir rezultāts, ko var viegli iegūt, izmantojot rakurstabulu. Bet to nevar izmantot, lai izveidotu rakurstabulu.

Datu kopā ir tukšas šūnas, un ceturtdaļas tiek sadalītas kā kolonnu galvenes.

Turklāt reģions ir norādīts augšpusē, lai gan tam vajadzētu būt katra ieraksta sastāvdaļai.

[GADĪJUMA STUDIJS] Slikti formatētu datu pārvēršana rakurstabulas gatavos avota datos

Dažreiz jūs varat iegūt datu kopu, kas nav piemērota izmantošanai kā rakurstabulas avota dati. Šādā gadījumā jums, iespējams, nav citas izvēles, kā pārvērst datus Pivot draudzīgā datu formātā.

Šeit ir slikta datu noformējuma piemērs:

Tagad varat izmantot Excel funkcijas vai Pivot vaicājumu, lai pārvērstu šos datus formātā, ko var izmantot kā rakurstabulas avota datus.

Apskatīsim, kā darbojas abas šīs metodes.

1. metode: izmantojot Excel formulas

Apskatīsim, kā izmantot Excel funkcijas, lai šos datus pārvērstu rakurstabulas formātā.

Izveidojiet unikālu kolonnu galveni visām sākotnējās datu kopas kategorijām. Šajā piemērā tas būtu reģions, ceturksnis un pārdošana.
Šūnā zem galvenes Reģions izmantojiet šādu formulu: = INDEKSS ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Velciet formulu uz leju, un tā atkārtos visus reģionus.
Šūnā zem ceturkšņa galvenes izmantojiet šādu formulu: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2)), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
- Velciet formulu uz leju, un tā atkārtos visus ceturkšņus.
Virsrakstā zem pārdošanas izmantojiet šādu formulu: = INDEKSS ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Velciet to uz leju, lai iegūtu visas vērtības. Šī formula izmanto reģiona un ceturkšņa datus kā uzmeklēšanas vērtības un atgriež pārdošanas vērtību no sākotnējās datu kopas.

Tagad šos iegūtos datus varat izmantot kā rakurstabulas avota datus.

Noklikšķiniet šeit, lai lejupielādētu parauga failu.

2. metode: Power Query izmantošana

Power Query ir funkcija, kas var viegli pārvērst šāda veida datus Pivot gatavā datu formātā.

Ja izmantojat programmu Excel 2016, Power Query funkcijas būtu pieejamas cilnes Dati grupā Iegūt un pārveidot. Ja izmantojat Excel 2013 vai iepriekšējās versijas, varat to izmantot kā pievienojumprogrammu.

Šeit ir lielisks ceļvedis par Jon Query Power Query instalēšanu no Excel Campus.

Atkal, ņemot vērā, ka dati ir formatēti, kā parādīts zemāk:

Tālāk ir norādītas darbības, lai avota datus pārvērstu Pivot Table ready formātā.

Pārveidojiet datus Excel tabulā. Atlasiet datu kopu un dodieties uz Ievietot -> Tabulas -> Tabula.
Dialoglodziņā Tabulas ievietošana pārliecinieties, vai ir atlasīts pareizais diapazons, un noklikšķiniet uz Labi. Tādējādi tabulas dati tiks pārvērsti Excel tabulā.
Programmā Excel 2016 dodieties uz Dati -> Iegūt un pārveidot -> No tabulas.
- Ja Power Query pievienojumprogrammu izmantojat iepriekšējā versijā, dodieties uz Power Query -> Ārējie dati -> No tabulas.
Vaicājumu redaktorā atlasiet slejas, kuras vēlaties atvienot. Šajā gadījumā šīs ir četras ceturtdaļas. Lai atlasītu visas kolonnas, turiet nospiestu taustiņu Shift un pēc tam atlasiet pirmo kolonnu un pēc tam pēdējo kolonnu.
Vaicājumu redaktorā dodieties uz Pārveidot -> Jebkura kolonna -> Atvērt kolonnas. Tādējādi kolonnas dati tiks pārveidoti rakurstabulai draudzīgā formātā.
Power Query slejām piešķir vispārīgus nosaukumus. Mainiet šos vārdus uz vajadzīgajiem. Šajā gadījumā mainiet atribūtu uz ceturksni un vērtību uz pārdošanu.
Vaicājumu redaktorā dodieties uz Fails -> Aizvērt un ielādēt. Tādējādi tiks aizvērts dialoglodziņš Power Query redaktors un tiks izveidota atsevišķa darblapa, kurā būs dati ar nepārvietotām kolonnām.