✅ Lähteandmete ettevalmistamine pöördetabeli jaoks

Lang L: none (table-of-contents)

Milline on Pivot Tabeli lähteandmete hea kujundus?
Tavalised lõksud, mida lähteandmete ettevalmistamisel vältida
Näiteid halbadest lähteandmete kujundustest
[JUHTUMIUURING] Halvasti vormindatud andmete teisendamine Pivot Tabeli valmis lähteandmeteks

Andmete õiges vormingus omamine on otsustava tähtsusega samm tugeva ja tõrgeteta liigendtabeli loomisel. Kui seda ei tehta õigesti, võib pöördetabeli kasutamisel tekkida palju probleeme.

Milline on Pivot Tabeli lähteandmete hea kujundus?

Vaatame näidet Pivot -tabeli headest lähteandmetest.

Siin on see hea lähteandmete disain:

Esimene rida sisaldab päiseid, mis kirjeldavad veergude andmeid.
Iga veerg tähistab ainulaadset andmekategooriat. Näiteks veerus C on ainult tooteandmed ning veerus D ja ainult kuuandmed.
Iga rida on kirje, mis esindab tehingu või müügi ühte eksemplari.
Andmepäised on ainulaadsed ja neid ei korrata kuskil andmekogumis. Näiteks kui teil on müüginumbreid neli kvartalit aastas, siis ärge nimetage neid kõiki müügiks. Selle asemel andke nendele veerupäistele ainulaadsed nimed, nagu müük Q1, müük Q2 jne.
- Kui teil pole ainulaadseid pealkirju, saate siiski luua liigendtabeli ja Excel muudab need automaatselt ainulaadseks, lisades järelliite (nt Müük, Müük2, Müük3). See oleks aga kohutav viis pöördetabeli ettevalmistamiseks ja kasutamiseks.

Tavalised lõksud, mida lähteandmete ettevalmistamisel vältida

Lähteandmetes ei tohiks olla tühje veerge. Seda on lihtne märgata. Kui teil on lähteandmetes tühi veerg, ei saa te liigendtabelit luua. See näitab viga, nagu allpool näidatud.
Lähteandmetes ei tohiks olla tühje lahtreid/ridu. Kuigi saate pöördetabeli edukalt luua hoolimata tühjadest lahtritest või ridadest, on palju kõrvaltoimeid, mis võivad teid hiljem päeva jooksul hammustada.
- Oletame näiteks, et müügiveerus on tühi lahter. Kui loote nende andmete põhjal pöördetabeli ja panete müügivälja veergude piirkonda, näitab see teile loendit, mitte summat. Seda seetõttu, et Excel tõlgendab kogu veergu tekstiandmetena (ainult ühe tühja lahtri tõttu).
Rakendage lähteandmete lahtritele asjakohane vorming. Näiteks kui teil on kuupäevad (mis on Exceli taustaprogrammi salvestatud seerianumbrina), kasutage ühte vastuvõetavat kuupäevavormingut. See aitaks teil luua liigendtabeli ja kasutada kuupäeva ühe kriteeriumina andmete kokkuvõtmiseks, rühmitamiseks ja sortimiseks.
- Kui teil on paar sekundit, proovige seda. Vormindage oma liigendtabelis kuupäevad arvudena ja looge nende andmete põhjal pöördetabel. Nüüd valige liigendtabelis kuupäevaväli ja vaadake, mis juhtub. See paigutab selle automaatselt väärtuste alale. Seda seetõttu, et teie liigendtabel ei tea, et need on kuupäevad. See tõlgendab neid numbritena.
Ärge lisage lähteandmete hulka ühtegi veerusummat, ridade summat, keskmist jne. Kui teil on pöördtabel, saate need hiljem hõlpsalt hankida.
Looge alati Exceli tabel ja kasutage seda seejärel pöördtabeli allikana. See on pigem hea tava, mitte lõks. Teie pöördtabel toimiks suurepäraselt ka lähteandmetega, mis pole ka Exceli tabel. Exceli tabeli eeliseks on see, et see saab kohandada laienevaid andmeid. Kui lisate andmekogumile rohkem ridu, ei pea te lähteandmeid ikka ja jälle kohandama. Pivot -tabelit saate lihtsalt värskendada ja see arvestab automaatselt lähteandmetele lisatud uusi ridu.

Näiteid halbadest lähteandmete kujundustest

Vaatame mõningaid halbu näiteid lähteandmete kujundusest.

Halva lähteandmete kujundus - näide 1

See on tavaline viis andmete säilitamiseks, kuna neid on lihtne jälgida ja mõista. Selle andmekorraldusega on kaks probleemi:

Te ei saa täielikku pilti. Näiteks näete 1. kvartali Mid West müüki 2924300. Kuid kas see on üksikmüük või mitu müüki. Kui teil on iga kirje saadaval eraldi real, saate teha parema analüüsi.
Kui jätkate ja loote selle abil Pivot tabeli (mida saate teha), saate erinevate kvartalite jaoks erinevaid välju. Midagi, nagu allpool näidatud:

Halva lähteandmete kujundus - näide 2

PowerPointi esitluste juhtkond ja publik võivad seda andmete esitamist hästi vastu võtta, kuid see ei sobi pöördetabeli loomiseks.

Jällegi, see on selline kokkuvõte, mille saate hõlpsalt Pivot -tabeli abil luua. Nii et isegi kui soovite lõpuks oma andmete sellist väljanägemist, säilitage lähteandmed Pivot -valmis vormingus ja looge see vaade pöördetabeli abil.

Halva lähteandmete kujundus - näide 3

See on jällegi väljund, mida saab hõlpsalt Pivot -tabeli abil saada. Kuid seda ei saa kasutada liigendtabeli loomiseks.

Andmekogumis on tühjad lahtrid ja veerandid jaotatakse veerupäisteks.

Samuti on piirkond üleval, kuigi see peaks olema iga kirje osa.

[JUHTUMIUURING] Halvasti vormindatud andmete teisendamine Pivot Tabeli valmis lähteandmeteks

Mõnikord võite saada andmestiku, mis ei sobi Pivot Tabeli lähteandmetena kasutamiseks. Sellisel juhul ei pruugi teil olla muud valikut kui teisendada andmed Pivot -sõbralikku andmevormingusse.

Siin on näide halvast andmete kujundamisest:

Nüüd saate Exceli funktsioonide või Pivot Query abil need andmed teisendada vormingusse, mida saab kasutada Pivot Tabeli lähteandmetena.

Vaatame, kuidas mõlemad meetodid toimivad.

Meetod: Exceli valemite kasutamine

Vaatame, kuidas kasutada Exceli funktsioone nende andmete teisendamiseks liigendtabeli vormingusse.

Looge ainulaadne veerupäis kõigi algse andmekogumi kategooriate jaoks. Selles näites oleks see piirkond, kvartal ja müük.
Kasutage piirkonna päise all olevas lahtris järgmist valemit: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Lohistage valem alla ja see kordab kõiki piirkondi.
Kasutage kvartali päise all olevas lahtris järgmist valemit: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2)), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
- Lohistage valem alla ja see kordab kõiki veerandeid.
Kasutage müügi all olevas päises järgmist valemit: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Kõigi väärtuste saamiseks lohistage see alla. See valem kasutab otsinguväärtustena piirkonna ja kvartali andmeid ning tagastab müügiväärtuse algsest andmestikust.

Nüüd saate neid saadud andmeid kasutada liigendtabeli lähteandmetena.

Näidisfaili allalaadimiseks klõpsake siin.

2. meetod: Power Query kasutamine

Power Queryl on funktsioon, mis võimaldab sedalaadi andmed hõlpsasti Pivot -valmis andmete vormingusse teisendada.

Kui kasutate Excel 2016, oleksid Power Query funktsioonid saadaval grupi Hangi ja teisenda vahekaardil Andmed. Kui kasutate Excel 2013 või varasemaid versioone, saate seda kasutada lisandmoodulina.

Siin on suurepärane juhend Joni Power Query installimiseks Exceli ülikoolist.

Arvestades, et teil on andmed vormindatud järgmiselt:

Lähteandmete teisendamiseks Pivot Tabeli valmis vormingusse toimige järgmiselt.

Teisendage andmed Exceli tabeliks. Valige andmestik ja minge Lisa -> Tabelid -> Tabel.
Veenduge dialoogiboksis Tabeli lisamine, et valitud on õige vahemik, ja klõpsake nuppu OK. See teisendab tabeliandmed Exceli tabeliks.
Avage Excel 2016 -s Andmed -> Hangi ja teisenda -> Tabelist.
- Kui kasutate Power Query lisandmoodulit eelmises versioonis, avage Power Query -> Välised andmed -> Tabelist.
Valige päringredaktoris veerud, mille tühistamise soovite tühistada. Antud juhul on need neli kvartalit. Kõigi veergude valimiseks hoidke all tõstuklahvi ja valige esimene ja seejärel viimane veerg.
Minge päringiredaktoris jaotisse Teisenda -> Mis tahes veerg -> Veergude tühistamine. See teisendab veeru andmed liigendtabeli sõbralikku vormingusse.
Power Query annab veergudele üldnimed. Muutke need nimed soovitud nimedeks. Sellisel juhul muutke atribuut kvartaliks ja väärtus müügiks.
Minge päringiredaktoris menüüsse Fail -> Sule ja laadige. See sulgeb dialoogiboksi Power Query Editor ja loob eraldi töölehe, millel on andmed koos veergudega.