Pivot-taulukot ovat yksi Microsoft Excelin tehokkaimmista ominaisuuksista. Niiden avulla voidaan analysoida suuria määriä tietoja ja tiivistää vain muutamalla hiiren napsautuksella. Tässä artikkelissa tutustumme Pivot-taulukkoihin, ymmärrämme, mitä he ovat ja opimme luomaan ja muokkaamaan niitä.
Huomautus: Tämä artikkeli on kirjoitettu Excel 2010 (beta) -ohjelmalla. PivotTable-konsepti on muuttunut vähäksi vuosien varrella, mutta luomisen menetelmä on muuttunut lähes kaikissa Excelin iteroinnissa. Jos käytät Excel-versiota, joka ei ole vuosi 2010, odottakaa erilaiset näytöt kuin tässä artikkelissa.
Pieni historia
Laskentataulukko-ohjelmien alkuaikoina Lotus 1-2-3 päätti roost. Sen hallitseva asema oli niin täydellinen, että ihmiset ajattelivat, että Microsoft joutui viettämään aikaa kehittää omaa laskentataulukkojensa ohjelmistoaan (Excel) kilpailemaan Lotusin kanssa. Flash-eteenpäin vuoteen 2010 ja Excelin hallitseva laskentataulukkojen markkinavoima on suurempi kuin Lotus on koskaan ollut, ja Lotus 1-2-3: n käynnissä olevien käyttäjien määrä lähestyy nollaa. Kuinka tämä tapahtui? Mikä aiheutti tällaisen dramaattisen muutoksen?
Teollisuuden analyytikot tekivät sen kahdelle tekijälle: Ensinnäkin Lotus päätti, että tämä hieno uusi GUI-alusta nimeltä "Windows" oli ohimenevä villitys, joka ei koskaan nousisi. He kieltäytyivät luomasta Lotus 1-2-3: n Windows-versiota (muutamaksi vuodeksi, joka tapauksessa), ennustaen, että heidän DOS-versionsa ohjelmistosta oli kaikki, mitä tahansa tarvitsisi koskaan. Microsoft luonnollisesti kehitti Exceliä yksinomaan Windowsille. Toiseksi Microsoft kehitti Excelille ominaisuuden, jota Lotus ei esittänyt 1-2-3: ssä pivot. PivotTables-ominaisuus, Excel-yksikköä lukuun ottamatta, pidettiin yllättävän hyödyllisenä, että ihmiset olivat halukkaita oppimaan koko uuden ohjelmistopaketin (Excel) pikemminkin kuin pysyä ohjelmassa (1-2-3), jolla ei ollut sitä. Tämä yksi ominaisuus, yhdessä Windowsin onnistumisen virheellisen arvioinnin kanssa, oli Lotus 1-2-3: n kuolemantapaus ja Microsoft Excelin menestyksen alku.
Pivot-taulukkojen ymmärtäminen
Joten mikä on PivotTable, tarkalleen?
Yksinkertaisesti pivot-taulukko on yhteenveto tietyistä tiedoista, jotka on luotu tietojen analysoinnin helpottamiseksi. Mutta toisin kuin käsin luotu yhteenveto, Excel PivotTables -ohjelmat ovat vuorovaikutteisia. Kun olet luonut yhden, voit helposti muuttaa sitä, jos se ei tarjoa täsmällisiä tietoja omiin tietoihisi. Pari napsautusta yhteenveto voidaan kääntää "- kääntää siten, että sarakeotsikot tulevat riviosoitteiksi ja päinvastoin. Siellä on paljon muutakin. Sen sijaan, että yritämme kuvailla kaikkia PivotTables-ominaisuuksia, osoitamme heidät vain ...
Pivot-taulukon avulla analysoitavat tiedot eivät voi olla vain minkä tahansa tiedot - sen on oltava raaka aikaisemmin käsittelemättömät tiedot (tyypillisesti jonkinlainen lista). Esimerkkinä tästä voi olla lista yhtiöiden myynnistä viimeisten kuuden kuukauden aikana.
Tutki alla näkyviä tietoja:
Huomaa, että tämä on ei raakadata. Itse asiassa se on jo jonkinlainen yhteenveto. Solussa B3 näemme 30 000 dollaria, mikä ilmeisesti on James Cookin myynti tammikuun aikana. Joten missä raakatiedot ovat? Kuinka saavuimme 30 000 dollarin arvosta? Mistä alkuperäisestä myyntitapahtumaluettelosta tämä luku on peräisin? On selvää, että jonnekin joku on mennyt vaikeuksiin kaikkien viimeisten kuuden kuukauden aikana tapahtuneiden myyntitapahtumien vertailemisesta yllä olevaan yhteenvetoon. Kuinka kauan sinä olet ottanut tämän? Tunti? Kymmenen?
Todennäköisesti kyllä. Näet, edellä oleva laskentataulukko on itse asiassa ei pivottaulukko. Se luotiin manuaalisesti muualta tallennetuista raakatiedoista, ja se kesti parin tunnin kääntämiseen. Se on kuitenkin juuri sellainen tiivistelmä voisi luodaan pivot-taulukkojen avulla, jolloin se olisi kestänyt vain muutaman sekunnin. Selvitetään, miten ...
Jos löydämme alkuperäisen myyntitietoluettelon, se voi näyttää tältä:
Saatat yllättyä oppimaan, että käyttämällä Excelin pivot-taulukko-ominaisuutta voimme luoda kuukausittaisen myynnin yhteenvedon, joka on samanlainen kuin yllä olevassa esimerkissä muutamassa sekunnissa, vain muutamalla hiiren napsautuksella. Voimme tehdä sen - ja paljon enemmän!
Luo pivottaulukko
Ensinnäkin varmista, että Excel-lomakkeessa on joitain raakaa tietoa. Luettelo rahoitustoimista on tyypillistä, mutta se voi olla lista lähes kaikista: työntekijän yhteystiedot, CD-kokoelma tai polttoaineen kulutusluvut yrityksesi autokannasta.
Joten aloitamme Excelin ... ja lataamme tällaisen luettelon ...
Kun Excel-luettelo on avoinna, voimme aloittaa pivot-taulukon luomisen.
Napsauta jotain yksittäistä solua luettelossa:
Sitten, alkaen Insert välilehti, napsauta PivotTable kuvake:
Luo pivottaulukko -ruutu tulee näyttöön, jossa kysytään kahta kysymystä: Mitä tietoja uuden pivottaulukon pitäisi perustua ja mistä se pitäisi luoda? Koska olemme jo napsauttaneet luettelossa olevaa solua (yllä olevassa vaiheessa), kyseistä solua ympäröivä koko luettelo on jo valittu meille ($ A $ 1: $ G $ 88 on maksut tässä esimerkissä). Huomaa, että voisimme valita luettelon mistä tahansa muusta taulukosta tai jopa jonkin ulkoisesta tietolähteestä, kuten Access-tietokantapöydästä tai jopa MS-SQL Server -tietokanta-taulukosta. Meidän on myös valittava, haluammeko uuden pivot-taulukon luomisen a Uusi laskentataulukon tai nykyinen yksi. Tässä esimerkissä valitaan a Uusi yksi:
Uusi laskentataulukko luodaan meille, ja tyhjä laskentataulukko luodaan kyseiseen laskentataulukkoon:
Toinen laatikko tulee näkyviin: Pivot-kentän luettelo. Tämä kenttäluettelo näkyy aina, kun napsautamme minkä tahansa solun pivot-taulukossa (yllä):
Kentän yläosassa olevien kenttien luettelo on itse asiassa alkuperäisen raakatietojen taulukon sarakeotsikot. Neljän tyhjät laatikot näytön alaosassa antavat meille mahdollisuuden valita haluamamme pivottaulukon yhteenvedon raakatiedoista. Toistaiseksi kyseisissä ruutuissa ei ole mitään, joten pivot-taulukko on tyhjä. Meidän on vain vedettävä kentät alhaalta yllä olevasta luettelosta ja pudottavat ne alempaan laatikkoon. Pivot-taulukko luodaan automaattisesti vastaamaan ohjeitamme. Jos saamme sen väärin, tarvitsemme vain vetää kentät takaisin siihen, mistä ne ovat peräisin ja / tai vedä Uusi kentät alas niiden korvaamiseksi.
arvot laatikko on todennäköisesti tärkein neljästä. Tähän ruutuun viedyt kentät edustavat tietoja, jotka on tiivistettävä jollakin tavalla (summaamalla, laskemalla keskiarvo, etsimällä maksimi, vähimmäismäärä jne.). Se on melkein aina numeerinen tiedot. Täydellinen ehdokas tämän kentän näyte tiedot on "Määrä" kenttä / sarake. Vedetään se kenttään arvot laatikko:
Huomaa, että (a) kenttien luettelon "Amount" -kenttä on nyt valittu ja "Summa summa" on lisätty arvot laatikko, joka osoittaa, että summa-sarake on summattu.
Jos tarkastelemme itse pivot-taulukkoa, löydämme todellakin kaikki rahastoesitteen laskentataulukon arvot:
Olemme luoneet ensimmäisen pivot-taulukon! Käytännöllinen, mutta ei erityisen vaikuttava. On todennäköistä, että me tarvitsemme hieman enemmän tietoja täältä.
Viitaten näyte-tietoihimme, meidän on tunnistettava yksi tai useampia sarakeotsikoita, joita voisimme ajatella jakamalla tämä kokonaissumma. Voimme esimerkiksi päättää, että haluamme nähdä yhteenvedon tietomme, jossa meillä on rivin otsikko jokaiselle yrityksellemme eri myyjille, ja jokaiselle. Tämän saavuttamiseksi meidän on vain vedettävä "Salesperson" -kentästä Rivierät laatikko:
Nytlopulta asiat alkavat saada mielenkiintoista! Kääntökuvamme alkaa muotoutua ....
Parin klikkauksen jälkeen olemme luoneet taulukon, joka olisi kestänyt paljon aikaa käsin.
Mitä muuta voimme tehdä? Yhdessä mielessä PivotTable on valmis. Olemme luoneet hyödyllisen yhteenvedon lähdetiedoistamme. Tärkeät asiat ovat jo oppineet! Jäljellemme artikkelissa tarkastelemme joitakin monimutkaisempia pivot-taulukoita ja tapoja, joilla näitä pivot-taulukkoja voidaan muokata.
Ensinnäkin voimme luoda a kaksi-kolmiulotteinen taulukko. Tehdään niin käyttämällä "Maksutapa" sarakkeen otsikoksi. Vedä vain "Maksutapa" otsikko kohteeseen Sarakkeen etiketit laatikko:
Mikä näyttää tältä:
Aloittaminen erittäin viileä!
Tehdään se a kolme-dimensionaalinen taulukko. Millainen tällainen taulukko voisi näyttää? Katsotaanpa ...
Vedä Paketin sarake / otsikko kohteeseen Ilmoita suodatin laatikko:
Huomaa, missä se päättyy ....
Tämän ansiosta voimme suodattaa raporttimme, jonka mukaan "lomapaketti" ostettiin. Esimerkiksi myyjän ja maksutavan jakautuminen näkyy kaikki paketteja tai muutama napsautus, muuta se näyttääksesi saman "Sunseekers" -paketin erittelyn:
Ja niin, jos ajattelet sitä oikealla tavalla, pivot-taulukko on nyt kolmiulotteinen. Jatketaan muokkaamalla ...
Jos käy ilmi, että haluamme vain nähdä sekki ja luottokortti (eli ei käteismaksuja), voimme poistaa valinnan Cash-kohteen kohteesta sarakeotsikoista. Napsauta vieressä olevaa avattavaa nuolta Sarakkeen etiketit, ja poista "Cash":
Katsotaanpa, miltä näyttää ... Kuten näet, "Cash" on poissa.
alustus
Tämä on tietenkin erittäin voimakas järjestelmä, mutta toistaiseksi tulokset näyttävät hyvin tavallisilta ja tylsiltä. Aluksi numerot, joita summataan, eivät näytä olevan dollarin arvoja - vain vanhoja numeroita. Korjaa se.
Houkutus voi olla se, mitä me tottumme tekemään tällaisissa olosuhteissa, ja valitse koko taulukko (tai koko laskentataulukko) ja käytä työkalurivin vakiomuotoisia muotoilupainikkeita lopettaaksesi muotoilun. Tämän lähestymistavan ongelma on se, että jos muutat pivot-tilan rakennetta tulevaisuudessa (mikä on 99% todennäköistä), nämä numeromuodot menetetään. Tarvitsemme tapaa, joka tekee niistä (semi-) pysyviä.
Ensin löydämme Summan summan merkinnän arvot ja napsauta sitä. Valikko tulee näkyviin. Valitsimme Arvokenttäasetukset ... valikosta:
Arvokenttäasetukset -ruutu tulee näkyviin.
Klikkaa Numeroformaatti -painiketta ja standardia Muotoile Cells-ruutu näyttää:
Vuodesta Kategoria luettelo, valitse (sano) Kirjanpitoja laske desimaalipilkun määrä 0. Valitse kunnossa muutama kerta takaisin pivottaulukkoon ...
Kuten näette, numerot on muotoiltu oikein dollarin määrinä.
Kun olemme muotoilun kohteena, muotoilee koko pivottaulukko. Tämä on muutamia tapoja. Käytetään yksinkertaista ...
Klikkaa PivotTable Työkalut / Suunnittelu välilehti:
Sitten pudota nuoli alareunan oikealla puolella PivotTable-tyylit luettelo nähdäksesi laajan kokoelman sisäänrakennetuista tyyleistä:
Valitse jokin, joka valittaa ja tarkastele tulosta pivottaulukossa:
Muita vaihtoehtoja
Voimme työskennellä päivämäärien kanssa. Nyt yleensä on monia, monta päivämäärää tapahtumaluettelossa, kuten alussa. Mutta Excel tarjoaa mahdollisuuden ryhmitellä tietoelementit yhteen päivällä, viikolla, kuukaudella, vuodella jne. Katsotaan, miten tämä tehdään.
Poista ensin "Maksutapa" -kentästä sarakkeesta Sarakkeen etiketit (vedä se takaisin kenttäluetteloon) ja korvaa se päivämääräpäivällä.
Kuten näette, tämä tekee PivotTable-mallistasi välittömästi hyödyttömän, antamalla meille yhden sarakkeen jokaiselle päivälle, jolloin tapahtuma tapahtui - hyvin laaja taulukko!
Voit korjata tämän napsauttamalla hiiren kakkospainikkeella mitä tahansa päivämäärää ja valitsemalla Ryhmä… kontekstivalikosta:
Ryhmittelyruutu tulee näkyviin. Valitsimme Kuukaudet ja napsauta OK:
Voila! paljon hyödyllisempi taulukko:
(Muuten tämä taulukko on käytännöllisesti katsoen samanlainen kuin tämän artikkelin alussa näytetty - alkuperäinen manuaalisesti luotu myyntikatsaus).
Toinen hieno asia, jonka on oltava tietoinen, on, että sinulla voi olla enemmän kuin yksi riviosien (tai sarakeotsikoiden) sarja:
... joka näyttää tältä ....
Voit tehdä samanlaisen asian sarakkeen otsikoilla (tai jopa raportoida suodattimet).
Jotta asiat olisivat yksinkertaisia uudelleen, katsotaanpa, miten piirtää keskimäärin arvojen sijaan summattuja arvoja.
Valitse ensin "Summa summa" ja valitse Arvokenttäasetukset ... näyttöön tulevasta kontekstivalikosta:
Vuonna Yhteenveto arvoalueesta luettelo Arvokenttäasetukset -valintaruutu, valitse Keskiverto:
Kun olemme täällä, vaihdetaan Mukautettu nimi, "Arvon keskiarvosta" jotain hieman suppeampaan. Kirjoita jotain "Avg":
Klikkaus kunnossa, ja katso, miltä se näyttää. Huomaa, että kaikki arvot vaihtelevat yhteenlasketuista summista keskiarvoihin ja taulukon otsikko (ylhäällä vasemmanpuoleinen solu) on muuttunut "Avg":
Jos pidämme, meillä voi olla jopa summia, keskiarvoja ja laskuja (counts = kuinka monta myyntiä oli) samassa pivottaulukossa!
Tässä on vaiheet saada jotain sellaista paikalleen (aloittamalla tyhjästä pivottaulukosta):
kenttä alkaen Sarakkeen etiketit että RivierätSeuraavassa me päätämme:
Yhteensä, keskiarvo ja lasketaan samaan pivottaulukkoon!
johtopäätös
Microsoft Excelin luomaa pivot-taulukkoa varten on monia muita ominaisuuksia ja vaihtoehtoja - aivan liian monta tällaista artikkelia varten. Pivot-taulukkojen potentiaalin täydelliseen kattamiseen tarvitaan pieni kirja (tai suuri verkkosivusto). Brave ja / tai geeky lukijat voivat tutkia pivot-taulukot vielä melko helposti: Napsauta hiiren oikealla painikkeella lähes kaikkea ja näet, mitä vaihtoehtoja on saatavilla. On myös kaksi nauha-välilehteä: PivotTable-työkalut / Lisävarusteet ja Design. Ei ole väliä, jos teet virheen - on helppo poistaa pivottaulukko ja aloittaa uudelleen - mahdollisuus, että vanhat DOS-käyttäjät Lotus 1-2-3: lla eivät koskaan olleet.
Jos työskentelet Office 2007: ssa, kannattaa tutustua artikkeliimme, jossa luodaan pivot-taulukko Excel 2007: ssa.
Olemme sisällyttäneet Excel-työkirjaan, jonka voit ladata käyttämään PivotTable-taitojasi. Sen pitäisi toimia kaikkien Excel-versioiden kanssa 97: sta eteenpäin.
Lataa meidän Practice Excel Workbook
