Šaltinio duomenų ruošimas „Pivot Table“

Tinkamo formato duomenų turėjimas yra esminis žingsnis kuriant patikimą ir be klaidų suvestinę lentelę. Jei tai daroma netinkamai, gali kilti daug problemų su suvestine lentele.

Koks yra tinkamas „Pivot Table“ šaltinio duomenų dizainas?

Pažvelkime į gerų „Pivot“ lentelės šaltinių duomenų pavyzdį.

Štai kodėl tai yra geras šaltinio duomenų dizainas:

• Pirmoje eilutėje yra antraštės, apibūdinančios stulpelių duomenis.
• Kiekvienas stulpelis yra unikali duomenų kategorija. Pavyzdžiui, C stulpelyje yra tik produkto duomenys, o D stulpelyje ir tik mėnesio duomenys.
• Kiekviena eilutė yra įrašas, vaizduojantis vieną sandorio ar pardavimo atvejį.
• Duomenų antraštės yra unikalios ir niekur nesikartoja duomenų rinkinyje. Pavyzdžiui, jei turite pardavimo numerius keturis ketvirčius per metus, neturėtumėte jų visų pavadinti pardavimais. Vietoj to, suteikite šioms stulpelių antraštėms unikalius pavadinimus, pvz., „Pardavimo Q1“, „Pardavimo Q2“ ir kt.
  • Jei neturite unikalių pavadinimų, vis tiek galite sukurti „Pivot“ lentelę, o „Excel“ automatiškai padarys jas unikalias pridėdama priesagą (pvz., „Sales“, „Sales2“, „Sales3“). Tačiau tai būtų baisus būdas paruošti ir naudoti „Pivot Table“.

Bendros klaidos, kurių reikia vengti rengiant šaltinio duomenis

• Šaltinio duomenyse neturėtų būti tuščių stulpelių. Tai lengva pastebėti. Jei šaltinio duomenyse yra tuščias stulpelis, negalėsite sukurti „Pivot“ lentelės. Tai parodys klaidą, kaip parodyta žemiau.
• Šaltinio duomenyse neturėtų būti tuščių langelių/eilučių. Nors galite sėkmingai sukurti „Pivot“ lentelę, nepaisant tuščių langelių ar eilučių, yra daug šalutinių poveikių, kurie gali jus įkandėti vėliau.
  • Pvz., Tarkime, kad pardavimo stulpelyje yra tuščias langelis. Jei sukursite „Pivot“ lentelę naudodami šiuos duomenis ir pardavimo lauką įdėsite į stulpelių sritį, joje bus rodomas COUNT, o ne SUM. Taip yra todėl, kad „Excel“ aiškina visą stulpelį kaip turintį teksto duomenų (tik dėl vieno tuščio langelio).
• Taikykite atitinkamą formatą šaltinio duomenų langeliams. Pvz., Jei turite datas (kurios „Excel“ užpakalinėje dalyje saugomos kaip serijos numeriai), taikykite vieną iš priimtinų datos formatų. Tai padėtų jums sukurti „Pivot“ lentelę ir naudoti datą kaip vieną iš kriterijų duomenims apibendrinti, grupuoti ir rūšiuoti.
  • Jei turite porą sekundžių, pabandykite tai padaryti. Suformatuokite „Pivot“ lentelės datas kaip skaičius ir sukurkite „Pivot“ lentelę naudodami šiuos duomenis. Dabar „Pivot“ lentelėje pasirinkite datos lauką ir pažiūrėkite, kas atsitiks. Jis automatiškai įdės jį į reikšmių sritį. Taip yra todėl, kad jūsų suvestinė lentelė nežino, kad tai datos. Tai interpretuoja kaip skaičius.
• Neįtraukite jokių stulpelių sumų, eilučių sumų, vidurkių ir tt kaip šaltinio duomenų dalies. Kai turėsite „Pivot Table“, vėliau galėsite lengvai jas gauti.
• Visada sukurkite „Excel“ lentelę ir naudokite ją kaip „Pivot“ lentelės šaltinį. Tai daugiau gera praktika, o ne duobė. Jūsų „Pivot“ lentelė puikiai veiktų su šaltinio duomenimis, kurie taip pat nėra „Excel“ lentelė. „Excel“ lentelės pranašumas yra tas, kad ji gali koreguoti besiplečiančius duomenis. Jei prie duomenų rinkinio pridėsite daugiau eilučių, jums nereikės vėl ir vėl koreguoti šaltinio duomenų. Galite tiesiog atnaujinti „Pivot“ lentelę ir ji automatiškai atsižvelgs į naujas eilutes, pridėtas prie šaltinio duomenų.

Blogų šaltinių duomenų dizaino pavyzdžiai

Pažvelkime į keletą blogų šaltinio duomenų dizaino pavyzdžių.

Blogas šaltinio duomenų dizainas - 1 pavyzdys

Tai yra įprastas būdas išlaikyti duomenis, nes juos lengva sekti ir suprasti. Yra dvi šios duomenų tvarkymo problemos:

• Negalite gauti viso vaizdo. Pvz., Pirmojo ketvirčio vidutinių vakarų pardavimų skaičius yra 2924300. Bet ar tai vienas pardavimas, ar keli pardavimai. Jei turite kiekvieną įrašą atskiroje eilutėje, galite atlikti geresnę analizę.
• Jei einate į priekį ir sukuriate „Pivot Table“ naudodami tai (tai galite padaryti), gausite skirtingus laukus skirtingiems ketvirčiams. Kažkas, kaip parodyta žemiau:

Blogas šaltinio duomenų dizainas - 2 pavyzdys

Šį duomenų atvaizdavimą gali gerai priimti „PowerPoint“ pristatymų vadovybė ir auditorija, tačiau jis netinka kurti „Pivot Table“.

Vėlgi, tai yra tokia santrauka, kurią galite lengvai sukurti naudodami „Pivot Table“. Taigi, net jei galiausiai norite, kad jūsų duomenys atrodytų taip, laikykite šaltinio duomenis parengtu „Pivot“ formatu ir sukurkite šį rodinį naudodami „Pivot Table“.

Blogas šaltinio duomenų dizainas - 3 pavyzdys

Tai vėlgi yra išvestis, kurią galima lengvai gauti naudojant „Pivot Table“. Tačiau jo negalima naudoti sukant lentelę.

Duomenų rinkinyje yra tuščių langelių, o ketvirčiai išskleidžiami kaip stulpelių antraštės.

Be to, regionas nurodytas viršuje, nors jis turėtų būti kiekvieno įrašo dalis.

[BYLOS TYRIMAS] Blogai suformatuotų duomenų konvertavimas į „Pivot Table Ready“ šaltinio duomenis

Kartais galite gauti duomenų rinkinį, kuris netinkamas naudoti kaip „Pivot Table“ šaltinio duomenys. Tokiu atveju jums gali nelikti nieko kito, kaip tik konvertuoti duomenis į „Pivot“ pritaikytą duomenų formatą.

Štai blogo duomenų dizaino pavyzdys:

Dabar galite naudoti „Excel“ funkcijas arba „Pivot Query“, norėdami konvertuoti šiuos duomenis į formatą, kuris gali būti naudojamas kaip „Pivot Table“ šaltinio duomenys.

Pažiūrėkime, kaip veikia abu šie metodai.

1 metodas: „Excel“ formulių naudojimas

Pažiūrėkime, kaip naudojant „Excel“ funkcijas konvertuoti šiuos duomenis į parengtą „Pivot Table“ formatą.

• Sukurkite unikalią stulpelio antraštę visoms pradinio duomenų rinkinio kategorijoms. Šiame pavyzdyje tai būtų regionas, kvartalas ir pardavimai.
• Ląstelėje po regiono antrašte naudokite šią formulę: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
  • Vilkite formulę žemyn ir ji pakartos visus regionus.
• Ląstelėje po ketvirčio antrašte naudokite šią formulę: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2)), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
  • Vilkite formulę žemyn ir ji pakartos visus ketvirčius.
• Antraštėje po pardavimais naudokite šią formulę: = INDEKSAS ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0) ))
  • Vilkite žemyn, kad gautumėte visas vertes. Ši formulė naudoja regiono ir ketvirčio duomenis kaip paieškos vertes ir grąžina pardavimo vertę iš pradinio duomenų rinkinio.

Dabar šiuos gautus duomenis galite naudoti kaip „Pivot Table“ šaltinio duomenis.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą.

2 metodas: „Power Query“ naudojimas

„Power Query“ turi funkciją, kuri gali lengvai konvertuoti tokio tipo duomenis į „Pivot“ parengtą duomenų formatą.

Jei naudojate „Excel 2016“, „Power Query“ funkcijos būtų prieinamos grupės „Gauti ir transformuoti“ skirtuke „Duomenys“. Jei naudojate „Excel 2013“ ar ankstesnes versijas, galite jį naudoti kaip papildinį.

Čia yra puikus vadovas, kaip įdiegti „Power Query by Jon“ iš „Excel Campus“.

Dar kartą, atsižvelgiant į tai, kad turite duomenų formatą, kaip parodyta žemiau:

Štai veiksmai, skirti šaltinio duomenims konvertuoti į „Pivot Table ready“ formatą:

• Konvertuokite duomenis į „Excel“ lentelę. Pasirinkite duomenų rinkinį ir eikite į Įterpti -> Lentelės -> Lentelė.
• Dialogo lange Įterpti lentelę įsitikinkite, kad pasirinktas teisingas diapazonas, ir spustelėkite Gerai. Lentelės duomenys bus paversti „Excel“ lentele.
• „Excel 2016“ eikite į Duomenys -> Gauti ir transformuoti -> Iš lentelės.
  • Jei „Power Query Addin“ naudojate ankstesnėje versijoje, eikite į „Power Query“ -> Išoriniai duomenys -> Iš lentelės.
• Užklausų redaktoriuje pasirinkite stulpelius, kuriuos norite atjungti. Šiuo atveju tai yra keturi ketvirčiai. Norėdami pasirinkti visus stulpelius, laikykite nuspaudę klavišą „Shift“, tada pasirinkite pirmąjį ir paskutinį stulpelį.
• Užklausų redagavimo priemonėje eikite į Transformuoti -> Bet koks stulpelis -> Atsukti stulpelius. Stulpelio duomenys bus konvertuoti į „Pivot Table“ pritaikytą formatą.
• „Power Query“ suteikia bendrus stulpelių pavadinimus. Pakeiskite šiuos vardus į norimus. Tokiu atveju pakeiskite atributą į ketvirtį ir vertę į pardavimus.
• Užklausų rengyklėje eikite į Failas -> Uždaryti ir įkelti. Bus uždarytas dialogo langas „Power Query“ redaktorius ir sukurtas atskiras darbalapis, kuriame bus duomenys su nesukurtais stulpeliais.

Dabar, kai žinote, kaip paruošti pradinius duomenis „Pivot Table“, esate pasiruošę „Excel“ „Pivot“ lentelių pasaulyje.

Štai keletas kitų „Pivot“ lentelių vadovėlių, kurie jums gali būti naudingi:

• Kaip atnaujinti „Pivot“ lentelę „Excel“.
• Pjaustyklių naudojimas „Excel“ suvestinėje lentelėje - pradedančiųjų vadovas.
• Kaip grupuoti datas „Excel“ suvestinėse lentelėse.
• Kaip grupuoti skaičius „Pivot“ lentelėje „Excel“.
• „Pivot“ talpykla „Excel“ - kas tai yra ir kaip geriausiai ją naudoti.
• Kaip filtruoti duomenis „Pivot“ lentelėje „Excel“.
• Kaip pridėti ir naudoti „Excel“ suvestinės lentelės apskaičiuotą lauką.
• Kaip taikyti sąlyginį formatavimą „Excel“ suvestinėje lentelėje.
• Kaip pakeisti tuščias langelius nuliais „Excel“ suvestinėse lentelėse.