Tietojen saaminen oikeassa muodossa on ratkaiseva askel luotettavan ja virheettömän pivot-taulukon luomisessa. Jos sitä ei tehdä oikein, pivot -taulukossa voi olla paljon ongelmia.
Mikä on hyvä malli pivot -taulukon lähdetiedoille?
Katsotaanpa esimerkkiä hyvistä lähdetiedoista pivot -taulukolle.
Tästä syystä se on hyvä lähdetietojen suunnittelu:
- Ensimmäinen rivi sisältää otsikot, jotka kuvaavat sarakkeiden tietoja.
- Jokainen sarake edustaa ainutlaatuista tietoluokkaa. Esimerkiksi sarakkeessa C on vain tuotetiedot ja sarakkeessa D ja kuukausitiedot.
- Jokainen rivi on tietue, joka edustaa yhtä tapahtumaa tai myyntiä.
- Tietootsikot ovat ainutlaatuisia, eikä niitä toisteta missään tietojoukossa. Jos sinulla on esimerkiksi myyntinumerot neljällä vuosineljänneksellä vuodessa, sinun EI pitäisi nimetä kaikkia näitä myyntinä. Anna sen sijaan näille sarakeotsikoille yksilöllisiä nimiä, kuten Myynti Q1, Myynti Q2 jne.
- Jos sinulla ei ole yksilöllisiä nimikkeitä, voit silti luoda pivot -taulukon, ja Excel tekee niistä automaattisesti ainutlaatuiset lisäämällä jälkiliitteen (kuten myynti, myynti2, myynti3). Se olisi kuitenkin kauhea tapa valmistaa ja käyttää kääntötaulukkoa.
- Jos sinulla ei ole yksilöllisiä nimikkeitä, voit silti luoda pivot -taulukon, ja Excel tekee niistä automaattisesti ainutlaatuiset lisäämällä jälkiliitteen (kuten myynti, myynti2, myynti3). Se olisi kuitenkin kauhea tapa valmistaa ja käyttää kääntötaulukkoa.
Yleisiä sudenkuoppia, joita vältetään lähdetietojen valmistelun aikana
- Lähdetiedoissa ei saa olla tyhjiä sarakkeita. Tämä on helppo havaita. Jos lähdetiedoissa on tyhjä sarake, et voi luoda pivot -taulukkoa. Se näyttää virheen, kuten alla.
- Lähdetiedoissa ei saa olla tyhjiä soluja/rivejä. Vaikka voit luoda pivot-taulukon onnistuneesti huolimatta tyhjistä soluista tai riveistä, on monia sivuvaikutuksia, jotka voivat purra sinua myöhemmin päivällä.
- Oletetaan esimerkiksi, että myyntisarakkeessa on tyhjä solu. Jos luot pivot -taulukon näiden tietojen perusteella ja asetat myyntikentän sarakealueelle, se näyttää sinulle LASKEN eikä summan. Tämä johtuu siitä, että Excel tulkitsee koko sarakkeen tekstidataksi (vain yhden tyhjän solun vuoksi).
- Oletetaan esimerkiksi, että myyntisarakkeessa on tyhjä solu. Jos luot pivot -taulukon näiden tietojen perusteella ja asetat myyntikentän sarakealueelle, se näyttää sinulle LASKEN eikä summan. Tämä johtuu siitä, että Excel tulkitsee koko sarakkeen tekstidataksi (vain yhden tyhjän solun vuoksi).
- Käytä asianmukaista muotoa lähdetiedon soluihin. Jos sinulla on esimerkiksi päivämääriä (jotka tallennetaan sarjanumeroina Excelin taustaohjelmaan), käytä jotakin hyväksyttävistä päivämäärän muodoista. Tämä auttaisi sinua luomaan pivot -taulukon ja käyttämään päivämäärää yhtenä kriteerinä tietojen yhteenvetoon, ryhmittelyyn ja lajitteluun.
- Jos sinulla on pari sekuntia, kokeile tätä. Muotoile pivot -taulukon päivämäärät numeroiksi ja luo sitten pivot -taulukko näiden tietojen perusteella. Valitse pivot -taulukosta päivämääräkenttä ja katso mitä tapahtuu. Se asettaa sen automaattisesti arvoalueelle. Tämä johtuu siitä, että pivot -taulukko ei tiedä, että nämä ovat päivämääriä. Se tulkitsee nämä luvuiksi.
- Jos sinulla on pari sekuntia, kokeile tätä. Muotoile pivot -taulukon päivämäärät numeroiksi ja luo sitten pivot -taulukko näiden tietojen perusteella. Valitse pivot -taulukosta päivämääräkenttä ja katso mitä tapahtuu. Se asettaa sen automaattisesti arvoalueelle. Tämä johtuu siitä, että pivot -taulukko ei tiedä, että nämä ovat päivämääriä. Se tulkitsee nämä luvuiksi.
- Älä sisällytä lähdetietoihin sarakkeiden kokonaismääriä, rivien summia, keskiarvoja jne. Kun sinulla on pivot -taulukko, saat ne helposti myöhemmin.
- Luo aina Excel -taulukko ja käytä sitä pivot -taulukon lähteenä. Tämä on enemmän hyvä käytäntö eikä sudenkuoppa. Pivot -taulukko toimisi hienosti lähdetiedon kanssa, joka ei myöskään ole Excel -taulukko. Excel -taulukon etuna on, että se voi säätää laajenevia tietoja. Jos lisäät tietojoukkoon lisää rivejä, sinun ei tarvitse säätää lähdetietoja uudelleen ja uudelleen. Voit yksinkertaisesti päivittää pivot -taulukon ja se ottaa automaattisesti huomioon lähdetietoihin lisätyt uudet rivit.
Esimerkkejä huonoista lähdetiedoista
Katsotaanpa joitain huonoja esimerkkejä lähdetietojen suunnittelusta.
Virheellinen lähdetietojen suunnittelu - Esimerkki 1
Tämä on yleinen tapa ylläpitää tietoja, koska niitä on helppo seurata ja ymmärtää. Tässä tietojärjestelyssä on kaksi ongelmaa:
- Et saa täydellistä kuvaa. Voit esimerkiksi nähdä Mid Westin myynnin neljänneksellä 1 2924300. Mutta onko kyseessä yksittäinen myynti vai useita myyntiä. Jos sinulla on jokainen tietue saatavilla erillisellä rivillä, voit tehdä paremman analyysin.
- Jos jatkat ja luot pivot -taulukon käyttämällä tätä (voit), saat erilaisia kenttiä eri vuosineljänneksille. Jotain alla olevan kuvan mukaisesti:
Virheellinen lähdedatan suunnittelu - esimerkki 2
Johto ja PowerPoint -esitysten yleisö voivat vastaanottaa tämän tietojen esityksen hyvin, mutta se ei sovellu pivot -taulukon luomiseen.
Jälleen tämä on sellainen yhteenveto, jonka voit helposti luoda pivot -taulukon avulla. Joten vaikka haluat lopulta tällaisen ulkoasun tiedoillesi, säilytä lähdetiedot Pivot -valmiissa muodossa ja luo tämä näkymä pivot -taulukon avulla.
Virheellinen lähdedatan suunnittelu - esimerkki 3
Tämä on jälleen tulos, joka voidaan helposti saada käyttämällä pivot -taulukkoa. Mutta sitä ei voi käyttää pivot -taulukon luomiseen.
Tietojoukossa on tyhjiä soluja ja neljännekset hajautetaan sarakeotsikoiksi.
Myös alue on määritetty yläreunassa, vaikka sen pitäisi olla osa jokaista tietuetta.
[CASE STUDY] Huonosti muotoiltujen tietojen muuntaminen pivot -taulukon valmiiksi lähdetiedoiksi
Joskus saatat saada tietojoukon, joka ei sovellu käytettäväksi pivot -taulukon lähdetiedoina. Tällaisessa tapauksessa sinulla ei ehkä ole muuta vaihtoehtoa kuin muuntaa tiedot Pivot -ystävälliseen datamuotoon.
Tässä on esimerkki huonosta datasuunnittelusta:
Nyt voit käyttää Excel -toimintoja tai Pivot -kyselyä näiden tietojen muuntamiseen muotoon, jota voidaan käyttää pivot -taulukon lähdetiedoina.
Katsotaanpa, miten molemmat menetelmät toimivat.
Tapa 1: Excel -kaavojen käyttäminen
Katsotaanpa, miten Excel -toimintojen avulla voidaan muuntaa nämä tiedot pivot -taulukon valmiiksi muotoksi.
- Luo ainutlaatuinen sarakeotsikko kaikille alkuperäisen tietojoukon luokille. Tässä esimerkissä se olisi alue, neljännes ja myynti.
- Käytä Alue -otsikon alla olevassa solussa seuraavaa kaavaa: = INDEKSI ($ A $ 2: $ A $ 5, ROUNDUP (RIVIT ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Vedä kaava alas ja se toistaa kaikki alueet.
- Vedä kaava alas ja se toistaa kaikki alueet.
- Käytä neljännesotsikon alla olevassa solussa seuraavaa kaavaa: = INDEKSI ($ B $ 1: $ E $ 1, ROUNDUP (MOD (RIVIT ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
- Vedä kaava alas ja se toistaa kaikki neljännekset.
- Vedä kaava alas ja se toistaa kaikki neljännekset.
- Käytä Myynti -otsikon alla olevaa kaavaa: = INDEKSI ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Vedä se alas saadaksesi kaikki arvot. Tämä kaava käyttää alue- ja vuosineljänneksen tietoja hakuarvoina ja palauttaa alkuperäisen tietojoukon myyntiarvon.
- Vedä se alas saadaksesi kaikki arvot. Tämä kaava käyttää alue- ja vuosineljänneksen tietoja hakuarvoina ja palauttaa alkuperäisen tietojoukon myyntiarvon.
Nyt voit käyttää näitä tuloksia pivot -taulukon lähdetiedoina.
Lataa esimerkkitiedosto napsauttamalla tätä.
Tapa 2: Power Queryn käyttäminen
Power Queryssä on ominaisuus, joka voi helposti muuntaa tällaiset tiedot Pivot -valmiiksi datamuotoon.
Jos käytät Excel 2016: ta, Power Query -ominaisuudet ovat käytettävissä Hae ja muunna -ryhmän Tiedot -välilehdessä. Jos käytät Excel 2013 tai aiempia versioita, voit käyttää sitä apuohjelmana.
Tässä on erinomainen opas Power Query by Jonin asentamisesta Excel Campusista.
Jälleen, kun olet muotoillut tiedot alla esitetyllä tavalla:
Tässä on vaiheet lähdetietojen muuntamiseksi Pivot Table ready -muotoon:
- Muunna tiedot Excel -taulukkoksi. Valitse tietojoukko ja siirry kohtaan Lisää -> Taulukot -> Taulukko.
- Varmista Lisää taulukko -valintaikkunassa, että oikea alue on valittu, ja napsauta OK. Tämä muuttaa taulukkotiedot Excel -taulukkoksi.
- Siirry Excel 2016: ssa kohtaan Tiedot -> Hae ja muunna -> Taulukosta.
- Jos käytät Power Query -laajennusta aiemmassa versiossa, siirry kohtaan Power Query -> Ulkoiset tiedot -> Taulukosta.
- Jos käytät Power Query -laajennusta aiemmassa versiossa, siirry kohtaan Power Query -> Ulkoiset tiedot -> Taulukosta.
- Valitse kyselyeditorissa sarakkeet, jotka haluat poistaa. Tässä tapauksessa nämä ovat neljännestä vuosineljännestä. Jos haluat valita kaikki sarakkeet, pidä Vaihto -näppäintä painettuna ja valitse ensimmäinen sarake ja sitten viimeinen sarake.
- Siirry kyselyeditorissa kohtaan Muunna -> Mikä tahansa sarake -> Poista sarakkeet. Tämä muuntaa sarakkeen tiedot pivot -taulukkoystävälliseen muotoon.
- Power Query antaa sarakkeille yleisnimet. Muuta nämä nimet haluamiisi nimiin. Muuta tässä tapauksessa attribuutiksi neljännesvuosi ja arvo myyntiksi.
- Siirry kyselyeditorissa kohtaan Tiedosto -> Sulje ja lataa. Tämä sulkee Power Query Editor -valintaikkunan ja luo erillisen laskentataulukon, joka sisältää tiedot, joissa ei ole käännettyjä sarakkeita.
Nyt kun tiedät, miten pivot -taulukon lähdetiedot valmistellaan, olet valmis Exceliin pivot -taulukoiden maailmassa.
Seuraavassa on joitain muita Pivot -taulukon opetusohjelmia, joista voi olla hyötyä:
- Pivot -taulukon päivittäminen Excelissä.
- Viipaloiden käyttäminen Excel -pivot -taulukossa - aloittelijan opas.
- Päivämäärien ryhmittely Excelin pivot -taulukoissa.
- Numeroiden ryhmittely Excelin pivot -taulukossa.
- Pivot -välimuisti Excelissä - mikä se on ja miten sitä käytetään parhaiten.
- Tietojen suodattaminen pivot -taulukossa Excelissä.
- Excel -pivot -taulukon lasketun kentän lisääminen ja käyttäminen.
- Ehdollisen muotoilun käyttäminen Excel -pivot -taulukossa.
- Tyhjien solujen korvaaminen nollilla Excelin pivot -taulukoissa.
