Sisällys
Pivot-pöydät on yksi Excelin tehokkaimmista työkaluista. Niiden avulla voit analysoida ja tehdä yhteenvedon suurista tietomääristä vain muutamalla hiiren napsautuksella. Tässä artikkelissa tutustumme pivot-taulukoihin, ymmärrämme, mitä ne ovat, opimme luomaan ja mukauttamaan niitä.
Tämä artikkeli on kirjoitettu Excel 2010:llä. Pivot-taulukoiden käsite ei ole juurikaan muuttunut vuosien varrella, mutta tapa, jolla ne luot, on hieman erilainen jokaisessa uudessa Excel-versiossa. Jos sinulla on Excelin versio, ei 2010, ole valmis siihen, että tämän artikkelin kuvakaappaukset eroavat näytölläsi näkemistäsi.
Hieman historiaa
Taulukkolaskentaohjelmiston alkuaikoina Lotus 1-2-3 -sääntöpallo. Sen määräävä asema oli niin täydellinen, että Microsoftin pyrkimykset kehittää oma ohjelmisto (Excel) vaihtoehtona Lotukselle vaikuttivat ajanhukkaa. Nyt nopeasti eteenpäin vuoteen 2010! Excel hallitsee laskentataulukoita enemmän kuin Lotus-koodi on koskaan tehnyt historiansa aikana, ja edelleen Lotusta käyttävien ihmisten määrä on lähellä nollaa. Miten tämä voi tapahtua? Mikä oli syy tällaiseen dramaattiseen tapahtumien käänteeseen?
Analyytikot tunnistavat kaksi päätekijää:
- Ensin Lotus päätti, että tämä uusi GUI-alusta nimeltä Windows oli vain ohimenevä muoti, joka ei kestä kauan. He kieltäytyivät rakentamasta Windows-versiota Lotus 1-2-3:sta (mutta vain muutaman vuoden ajan) ja ennustivat, että heidän ohjelmistonsa DOS-versio olisi kaikki mitä kuluttajat koskaan tarvitsevat. Microsoft on luonnollisesti kehittänyt Excelin erityisesti Windowsille.
- Toiseksi Microsoft esitteli Excelissä PivotTables-työkalun, joka ei ollut saatavilla Lotus 1-2-3:ssa. Excelin yksinomaiset pivot-taulukot osoittautuivat niin ylivoimaisesti hyödyllisiksi, että ihmisillä oli tapana pysyä uudessa Excel-ohjelmistopaketissa sen sijaan, että jatkaisivat Lotus 1-2-3:lla, jossa niitä ei ollut.
Pivot-taulukot sekä Windowsin yleisen menestyksen aliarvioiminen pelasivat Lotus 1-2-3:n kuolemanmarssin ja johdattivat Microsoft Excelin menestyksen.
Mitä pivot-taulukot ovat?
Joten mikä on paras tapa luonnehtia Pivot-taulukoita?
Yksinkertaisesti sanottuna pivot-taulukot ovat yhteenvetoja joistakin tiedoista, jotka on luotu helpottamaan näiden tietojen analysointia. Toisin kuin manuaalisesti luodut summat, Excelin pivot-taulukot ovat interaktiivisia. Kun ne on luotu, voit helposti muokata niitä, jos ne eivät anna toivomaasi kuvaa. Vain parilla hiiren napsautuksella summat voidaan kääntää niin, että sarakeotsikoista tulee riviotsikoita ja päinvastoin. Voit tehdä monia asioita pivot-taulukoilla. Sen sijaan, että yrittäisit kuvata sanoin kaikkia pivot-taulukoiden ominaisuuksia, on helpompi demonstroida se käytännössä…
Pivot-taulukoilla analysoimasi tiedot eivät voi olla satunnaisia. Sen pitäisi olla raakadataa, kuten jonkinlainen luettelo. Se voi olla esimerkiksi luettelo yrityksen viimeisten kuuden kuukauden aikana tekemästä myynnistä.
Katso alla olevan kuvan tiedot:
Huomaa, että tämä ei ole raakadataa, koska se on jo tiivistetty. Solussa B3 näemme 30000 30000 dollaria, mikä on luultavasti James Cookin tammikuussa tekemä kokonaistulos. Missä alkuperäiset tiedot sitten ovat? Mistä XNUMX XNUMX dollarin luku tuli? Missä on alkuperäinen myyntiluettelo, josta tämä kuukausittainen kokonaissumma on saatu? On selvää, että joku on tehnyt hienoa työtä organisoidakseen ja lajitellessaan kaikki viimeisen kuuden kuukauden myyntitiedot ja muuntaa ne näkemäänsä kokonaismäärätaulukkoon. Kuinka kauan luulet sen kestäneen? Tunnin? Kello kymmenen?
Tosiasia on, että yllä oleva taulukko ei ole pivot-taulukko. Se tehtiin käsin muualle tallennetuista raakatiedoista, ja sen käsittely kesti vähintään pari tuntia. Juuri tällainen yhteenvetotaulukko voidaan luoda pivot-taulukoiden avulla muutamassa sekunnissa. Selvitetään kuinka…
Jos palaamme alkuperäiseen myyntilistaan, se näyttäisi tältä:
Saatat yllättyä, että tästä kauppaluettelosta voimme pivot-taulukoiden avulla ja muutamassa sekunnissa luoda kuukausittaisen myyntiraportin Excelissä, jota analysoimme yllä. Kyllä, voimme tehdä sen ja enemmän!
Kuinka luoda pivot-taulukko?
Varmista ensin, että sinulla on joitain lähdetietoja Excel-taulukossa. Luettelo rahoitustapahtumista on tyypillisin, mitä tapahtuu. Itse asiassa se voi olla luettelo mistä tahansa: työntekijän yhteystiedot, CD-kokoelma tai yrityksesi polttoaineenkulutustiedot.
Joten käynnistämme Excelin… ja lataamme sellaisen luettelon…
Kun olemme avannut tämän luettelon Excelissä, voimme aloittaa pivot-taulukon luomisen.
Valitse mikä tahansa solu tästä luettelosta:
Sitten välilehdelle lisäys (Lisää) valitse komento Pivot-taulukko (Pivot-taulukko):
Näyttöön tulee valintaikkuna Luo Pivot-taulukko (Pivot-taulukon luominen), jossa on kaksi kysymystä sinulle:
- Mitä tietoja käytetään uuden pivot-taulukon luomiseen?
- Mihin pivot-taulukko sijoitetaan?
Koska edellisessä vaiheessa olemme jo valinneet yhden luettelon soluista, koko luettelo valitaan automaattisesti pivot-taulukon luomiseksi. Huomaa, että voimme valita eri alueen, erilaisen taulukon ja jopa jonkin ulkoisen tietolähteen, kuten Access- tai MS-SQL-tietokantataulukon. Lisäksi meidän on valittava, mihin uusi pivot-taulukko sijoitetaan: uudelle arkille vai jollekin olemassa olevista. Tässä esimerkissä valitsemme vaihtoehdon - Uusi työtaulukko (uudelle arkille):
Excel luo uuden arkin ja sijoittaa siihen tyhjän pivo-taulukon:
Heti kun napsautamme mitä tahansa solua pivot-taulukossa, toinen valintaikkuna tulee näkyviin: Pivot-taulukon kenttäluettelo (Pivot-taulukon kentät).
Valintaikkunan yläosassa oleva kenttäluettelo on luettelo kaikista alkuperäisen luettelon nimikkeistä. Näytön alareunassa olevien neljän tyhjän alueen avulla voit kertoa pivot-taulukolle, kuinka haluat tehdä yhteenvedon tiedoista. Niin kauan kuin nämä alueet ovat tyhjiä, taulukossa ei myöskään ole mitään. Meidän tarvitsee vain vetää otsikot yläalueelta alla oleville tyhjille alueille. Samalla luodaan automaattisesti pivot-taulukko ohjeidemme mukaisesti. Jos teemme virheen, voimme poistaa otsikot ala-alueelta tai vetää muita korvaamaan ne.
alue arvot (Merkitykset) on luultavasti tärkein näistä neljästä. Se, mikä otsikko tälle alueelle sijoitetaan, määrittää, mitkä tiedot summataan (summa, keskiarvo, maksimi, minimi jne.). Nämä ovat lähes aina numeerisia arvoja. Loistava ehdokas paikkaan tällä alueella ovat otsikon alla olevat tiedot Määrä (hinta) alkuperäisen pöytämme. Vedä tämä otsikko alueelle arvot (Arvot):
Huomaa, että otsikko Määrä on nyt merkitty valintamerkillä ja alueella arvot (Arvot) -merkintä on ilmestynyt Summa (Summa-kenttä Summa), mikä osoittaa, että sarake Määrä kiteytti.
Jos katsomme itse pivot-taulukkoa, näemme sarakkeen kaikkien arvojen summan Määrä alkuperäinen pöytä.
Ensimmäinen pivot-taulukkomme on siis luotu! Kätevä, mutta ei erityisen vaikuttava. Haluamme todennäköisesti saada enemmän tietoa tiedoistamme kuin meillä tällä hetkellä on.
Käännytään alkuperäisiin tietoihin ja yritetään tunnistaa yksi tai useampi sarake, jota voidaan käyttää tämän summan jakamiseen. Voimme esimerkiksi muodostaa pivot-taulukkomme siten, että myynnin kokonaissumma lasketaan jokaiselle myyjälle erikseen. Nuo. Pivot-taulukkoomme lisätään rivejä, joissa on yrityksen jokaisen myyjän nimi ja heidän myyntinsä kokonaismäärä. Saavuta tämä tulos vetämällä otsikkoa myyjä (myyntiedustaja) alueelle Rivitarrat (kielet):
Siitä tulee mielenkiintoisempaa! Pivot-taulukkomme alkaa muotoutua…
Näetkö edut? Muutamalla napsautuksella loimme taulukon, jonka manuaalinen luominen olisi kestänyt hyvin kauan.
Mitä muuta voimme tehdä? Tietyssä mielessä pivot-taulukkomme on valmis. Olemme luoneet hyödyllisen yhteenvedon alkuperäisistä tiedoista. Tärkeää tietoa jo saatu! Tämän artikkelin loppuosassa tarkastellaan joitain tapoja luoda monimutkaisempia pivot-taulukoita ja opimme mukauttamaan niitä.
Pivot-taulukon asetukset
Ensinnäkin voimme luoda kaksiulotteisen pivot-taulukon. Tehdään tämä sarakeotsikon avulla Maksutapa (Maksutapa). Vedä vain otsikko Maksutapa alueelle Sarakkeen otsikot (Sarakkeet):
Saamme tuloksen:
Näyttää erittäin siistiltä!
Tehdään nyt kolmiulotteinen taulukko. Miltä tällainen pöytä näyttäisi? Katsotaan…
Vedä otsikkoa Paketti (Monimutkainen) alueelle raporttisuodattimet (Suodattimet):
Huomaa missä hän on…
Tämä antaa meille mahdollisuuden suodattaa raporttia "Mistä lomakeskuksesta maksettiin" perusteella. Voimme esimerkiksi nähdä erittelyn myyjien ja maksutapojen mukaan kaikista komplekseista tai muutamalla hiiren napsautuksella muuttaa pivot-taulukon näkymää ja näyttää saman erittelyn vain niille, jotka tilasivat kompleksin Auringonhakijat.
Joten jos ymmärrät tämän oikein, pivot-taulukkoamme voidaan kutsua kolmiulotteiseksi. Jatketaan määrittämistä…
Jos yhtäkkiä käy ilmi, että vain sekillä ja luottokortilla maksaminen (eli käteismaksu) pitäisi näkyä pivot-taulukossa, voimme poistaa otsikon näyttämisen käytöstä. kassa (Käteinen raha). Tätä varten vieressä Sarakkeen otsikot napsauta alanuolta ja poista valinta ruudusta avattavasta valikosta kassa:
Katsotaanpa, miltä pivot-taulukkomme näyttää nyt. Kuten näette, sarake kassa katosi hänestä.
Pivot-taulukoiden muotoilu Excelissä
Pivot-taulukot ovat tietysti erittäin tehokas työkalu, mutta toistaiseksi tulokset näyttävät hieman yksinkertaisilta ja tylsiltä. Esimerkiksi luvut, jotka laskemme yhteen, eivät näytä dollarimääriltä – ne ovat vain numeroita. Korjataan tämä.
On houkuttelevaa tehdä sellaisessa tilanteessa, mihin olet tottunut ja valitse yksinkertaisesti koko taulukko (tai koko arkki) ja aseta haluamasi muoto työkalupalkin vakiomuotoilupainikkeilla. Tämän lähestymistavan ongelmana on, että jos muutat pivot-taulukon rakennetta tulevaisuudessa (mikä tapahtuu 99 %:n todennäköisyydellä), muotoilu menetetään. Tarvitsemme tavan tehdä siitä (melkein) pysyvä.
Etsitään ensin merkintä Summa in arvot (Arvot) ja napsauta sitä. Valitse kohde näkyviin tulevasta valikosta Arvokentän asetukset (Arvokentän vaihtoehdot):
Näyttöön tulee valintaikkuna Arvokentän asetukset (Arvokentän asetukset).
lehdistö lukumuodon (Numeromuoto), valintaikkuna avautuu. formaatti Cells (solumuoto):
Luettelosta Kategoria (Numeromuodot) valitse kirjanpito (Talous) ja aseta desimaalien määräksi nolla. Paina nyt muutaman kerran OKpalataksesi pivot-taulukkoomme.
Kuten näet, luvut on muotoiltu dollareiksi.
Kun käsittelemme muotoilua, määritetään koko pivot-taulukon muoto. On olemassa useita tapoja tehdä tämä. Käytämme yksinkertaisempaa…
Valitse Pivot-taulukon työkalut: Suunnittelu (Pivot-taulukoiden käyttäminen: Constructor):
Laajenna seuraavaksi valikkoa napsauttamalla osion oikeassa alakulmassa olevaa nuolta Pivot-taulukon tyylit (PivotTable Styles) nähdäksesi laajan sisäisten tyylien kokoelman:
Valitse mikä tahansa sopiva tyyli ja katso tulos pivot-taulukossasi:
Muut Pivot-taulukon asetukset Excelissä
Joskus sinun on suodatettava tiedot päivämäärän mukaan. Esimerkiksi kauppaluettelossamme on monia, monia päivämääriä. Excel tarjoaa työkalun tietojen ryhmittelyyn päivän, kuukauden, vuoden ja niin edelleen. Katsotaan kuinka se tehdään.
Poista ensin merkintä. Maksutapa alueelta Sarakkeen otsikot (Sarakkeet). Voit tehdä tämän vetämällä sen takaisin otsikkoluetteloon ja siirtämällä sen tilalle Varauspäivämäärä (varauspäivä):
Kuten näette, tämä teki pivot-taulukostamme tilapäisesti hyödyttömän. Excel loi erillisen sarakkeen jokaiselle kaupantekopäivälle. Tuloksena saimme erittäin leveän pöydän!
Voit korjata tämän napsauttamalla hiiren kakkospainikkeella mitä tahansa päivämäärää ja valitsemalla pikavalikosta GROUP (Ryhmä):
Ryhmittelyvalintaikkuna tulee näkyviin. Me valitsemme Kk (Kuukaudet) ja napsauta OK:
Voila! Tämä taulukko on paljon hyödyllisempi:
Tämä taulukko on muuten lähes identtinen artikkelin alussa olevan taulukon kanssa, jossa myyntisummat koottiin manuaalisesti.
On toinen erittäin tärkeä seikka, joka sinun on tiedettävä! Voit luoda ei yhden, vaan useita rivi- (tai sarake)-otsikoita:
…ja se tulee näyttämään tältä…
Sama voidaan tehdä sarakeotsikoilla (tai jopa suodattimilla).
Palataan taulukon alkuperäiseen muotoon ja katsotaan kuinka näyttää keskiarvot summien sijaan.
Aloita napsauttamalla Summa ja valitse näkyviin tulevasta valikosta Arvokentän asetukset (Arvokentän vaihtoehdot):
Lista Arvokentän yhteenveto (Toiminta) valintaikkunassa Arvokentän asetukset (Arvokentän asetukset) valitse Keskimäärin (Keskiverto):
Samaan aikaan, kun olemme täällä, muuttukaamme CustomName (Muokattu nimi) kanssa Keskimääräinen määrä (Summa-kenttä Summa) johonkin lyhyempään. Kirjoita tähän kenttään jotain vastaavaa avg:
lehdistö OK ja katso mitä tapahtuu. Huomaa, että kaikki arvot ovat muuttuneet summista keskiarvoiksi ja taulukon otsikko (vasemmassa yläkulmassa) on muuttunut arvoksi avg:
Halutessasi saat heti summan, keskiarvon ja määrän (myynti) sijoitettuna yhteen pivot-taulukkoon.
Tässä on vaiheittainen opas tämän tekemiseen tyhjästä pivot-taulukosta alkaen:
- Vedä otsikkoa myyjä (myyntiedustaja) alueelle Sarakkeen otsikot (Sarakkeet).
- Vedä otsikkoa kolme kertaa Määrä (kustannus) alueelle arvot (Arvot).
- Ensimmäiselle kentälle Määrä muuta otsikko muotoon Yhteensä (Summa), ja tämän kentän numeromuoto on kirjanpito (Taloudellinen). Desimaalien määrä on nolla.
- Toinen kenttä Määrä nimi Keskime, aseta toiminto sille Keskimäärin (Keskiarvo) ja tämän kentän numeromuoto muuttuvat myös muotoon kirjanpito (Talous) nolla desimaalilla.
- Kolmannelle kentälle Määrä aseta otsikko Laskea ja leikkaus hänelle - Laskea (Määrä)
- In Sarakkeen otsikot (Sarakkeet) -kenttä luodaan automaattisesti Σ Arvot (Σ Arvot) – vedä se alueelle Rivitarrat (rivit)
Tähän päädymme:
Kokonaismäärä, keskiarvo ja myyntien määrä – kaikki yhdessä pivot-taulukossa!
Yhteenveto
Microsoft Excelin pivot-taulukot sisältävät paljon ominaisuuksia ja asetuksia. Näin pienessä artikkelissa ne eivät edes kata kaikkia. Vaatii pienen kirjan tai suuren verkkosivuston kuvaamaan täydellisesti kaikki pivot-taulukoiden mahdollisuudet. Rohkeat ja uteliaat lukijat voivat jatkaa pivot-taulukoiden tutkimista. Voit tehdä tämän napsauttamalla hiiren kakkospainikkeella melkein mitä tahansa pivot-taulukon elementtiä ja katsoa, mitä toimintoja ja asetuksia avataan. Nauhassa on kaksi välilehteä: Pivot-taulukkotyökalut: Asetukset (analyysi) ja Malli (Rakentaja). Älä pelkää tehdä virhettä, voit aina poistaa Pivot-taulukon ja aloittaa alusta. Sinulla on mahdollisuus, jollaista DOS:n ja Lotus 1-2-3:n pitkäaikaisilla käyttäjillä ei ole koskaan ollut.