Pivot-taulukoiden käyttö Microsoft Excelissä

PivotTables on yksi Microsoft Excelin tehokkaimmista ominaisuuksista. Ne mahdollistavat suurten tietomäärien analysoinnin ja tiivistämisen vain muutamalla hiiren napsautuksella. Tässä artikkelissa tutkitaan PivotTables-tiedostoja, ymmärrämme, mitä ne ovat, ja oppia luomaan ja muokkaamaan niitä.

Huomautus: Tämä artikkeli on kirjoitettu Excel 2010: n (Beta) avulla. PivotTable-konsepti on muuttunut vähän vuosien varrella, mutta menetelmän luominen on muuttunut lähes kaikissa Excelin iteraatioissa. Jos käytät Excel-versiota, joka ei ole 2010, odota eri näyttöruutuja niistä, joita näet tässä artikkelissa.

Pikku historia

Taulukkolaskentaohjelmien alkuvaiheessa Lotus 1-2-3 hallitsi kiveä. Sen määräävä asema oli niin täydellinen, että ihmiset ajattelivat, että Microsoftin ajanhukkaa vaivaa kehittää omaa taulukkolaskentaohjelmistoa (Excel) kilpailemaan Lotusin kanssa. Flash-eteenpäin vuoteen 2010 ja Excelin määräävä asema taulukkolaskentamarkkinoilla on suurempi kuin Lotus koskaan, kun taas Lotus 1-2-3 -käyttöjärjestelmää käyttävien käyttäjien määrä lähestyy nollaa. Kuinka tämä tapahtui? Mikä aiheutti tällaisen dramaattisen omaisuuden kääntymisen?

Teollisuuden analyytikot laskivat sen kahteen tekijään: Ensinnäkin Lotus päätti, että tämä uusi fiksu uusi käyttöliittymä nimeltä Windows on ohi, joka ei koskaan lähtisi. He kieltäytyivät luomasta Windows-versiota Lotus 1-2-3: sta (muutaman vuoden ajan), ennustamalla, että niiden DOS-versio ohjelmistosta oli kaikki kenenkään tarvita. Microsoft kehitti luonnollisesti Exceliä yksinomaan Windowsille. Toiseksi Microsoft kehitti Excelille ominaisuuden, jonka Lotus ei toimittanut julkaisussa 1-2-3 pivot.  Excelin yksinomainen PivotTables-ominaisuus katsottiin niin hämmästyttävän hyödylliseksi, että ihmiset olivat halukkaita oppimaan koko uuden ohjelmistopaketin (Excel) sen sijaan, että se tarttuisi ohjelmaan (1-2-3), jolla ei ollut sitä. Tämä yksi ominaisuus sekä Windows-järjestelmän onnistumisen virheellinen arviointi oli Lotus 1-2-3: n kuolinsatama ja Microsoft Excelin menestyksen alku.

PivotTablesin ymmärtäminen

Joten mikä on PivotTable??

Yksinkertaisesti sanottuna PivotTable on yhteenveto joistakin tiedoista, jotka on luotu sallimaan mainittujen tietojen helppo analysointi. Toisin kuin manuaalisesti luotu yhteenveto, Excel PivotTables ovat vuorovaikutteisia. Kun olet luonut sen, voit helposti vaihtaa sen, jos se ei tarjoa tarkkoja oivalluksia tietosi, joita olet toivonut. Muutamassa napsautuksessa yhteenveto voidaan "kääntää" - kiertää siten, että sarakkeen otsikot tulevat rivin otsikoiksi ja päinvastoin. Myös paljon on mahdollista tehdä. Sen sijaan, että yritettäisiin kuvata kaikkia PivotTablesin ominaisuuksia, näytämme ne yksinkertaisesti…

PivotTableilla analysoitavat tiedot eivät voi olla vain minkä tahansa tiedot - sen on oltava raaka aikaisemmin käsittelemättömät (ei-arvostetut) tiedot - tyypillisesti jokin lista. Esimerkkinä tästä voi olla luettelo yrityksessä tapahtuneista myyntitapahtumista viimeisten kuuden kuukauden aikana.

Tutki alla esitettyjä tietoja:

Huomaa, että tämä on ei raakadata. Itse asiassa se on jo jonkinlainen yhteenveto. Solussa B3 näemme 30 000 dollaria, mikä on ilmeisesti James Cookin tammikuun myynnin kokonaismäärä. Joten missä on raakatiedot? Miten saavuimme 30 000 dollarin arvoon? Missä on alkuperäinen myyntitapahtumien luettelo, josta tämä luku on luotu? On selvää, että jonnekin täytyy joutua vaikeuksiin kerätä kaikki viimeisten kuuden kuukauden myyntitapahtumat yllä olevaan yhteenvetoon. Kuinka kauan oletat, että tämä tapahtui? Tunti? Kymmenen?

Todennäköisesti kyllä. Yllä oleva laskentataulukko on itse asiassa ei PivotTable. Se luotiin manuaalisesti muualla tallennetuista raaka-aineista, ja se kesti pari tuntia. Se on kuitenkin juuri sellainen yhteenveto voisi luodaan PivotTablesin avulla, jolloin se olisi kestänyt vain muutaman sekunnin. Selvitetään, miten…

Jos haluaisimme seurata myyntitapahtumien alkuperäistä luetteloa, se saattaa näyttää tältä:

Saatat olla yllättynyt siitä, että Excelin PivotTable-ominaisuuden avulla voimme luoda kuukausittaisen myyntiyhteenvedon, joka on samanlainen kuin edellä, muutamassa sekunnissa vain muutamalla hiiren napsautuksella. Voimme tehdä tämän - ja paljon muuta!

Miten luodaan kääntöpöytä

Varmista ensin, että sinulla on joitakin raakatietoja Excel-taulukossa. Rahoitustapahtumien luettelo on tyypillinen, mutta se voi olla luettelo lähes kaikesta: Työntekijän yhteystiedot, CD-kokoelma tai polttoaineenkulutukset yrityksesi autokannasta.

Joten käynnistämme Excelin… ja lataamme tällaisen luettelon…

Kun lista on avattu Excelissä, olemme valmiita luomaan PivotTable-sovelluksen.

Napsauta luettelossa olevaa yksittäistä solua:

Sitten, Insert välilehti, napsauta PivotTable kuvake:

Luo PivotTable ruutu, jossa kysytään kahta kysymystä: Mitä tietoja PivotTable -tietokoneen pitäisi perustua ja mihin se pitäisi luoda? Koska olemme jo napsauttaneet luettelossa olevaa solua (yllä olevassa vaiheessa), koko kyseistä solua ympäröivä luettelo on jo valittu meille ($ A $ 1: $ G $ 88 on maksut tässä esimerkissä). Huomaa, että voisimme valita luettelon mistä tahansa muusta työarkista tai jopa ulkoisesta tietolähteestä, kuten Access-tietokantataulukosta tai jopa MS-SQL Server -tietokannasta. Meidän on myös valittava, haluatko luoda uuden PivotTable: n a Uusi tai laskentataulukosta nykyinen yksi. Tässä esimerkissä valitaan a Uusi yksi:

Uusi laskentataulukko luodaan meille ja tyhjä PivotTable luodaan kyseiselle laskentataulukolle:

Näyttöön tulee myös toinen laatikko: PivotTable-kenttäluettelo.  Tämä kenttäluettelo näytetään, kun napsautamme mitä tahansa solua PivotTable-levyllä (yllä):

Laatikon yläosassa olevien kenttien luettelo on oikeastaan ​​kokoelma sarakkeen otsikoita alkuperäisestä raaka-aineistosta. Näytön alaosassa olevat neljä tyhjää laatikkoa antavat meille mahdollisuuden valita, miten haluaisimme PivotTable: n tiivistää raakatiedot. Toistaiseksi näissä laatikoissa ei ole mitään, joten PivotTable on tyhjä. Meidän tarvitsee vain vetää kentät alas yllä olevasta luettelosta ja pudottaa ne alempiin ruutuihin. PivotTable luodaan sitten automaattisesti vastaamaan ohjeita. Jos saamme sen väärin, meidän on vain vedettävä kentät takaisin paikkaan, josta he tulivat ja / tai vetivät Uusi kentät, jos haluat korvata ne.

arvot laatikko on kiistatta tärkein neljästä. Tähän ruutuun vedetty kenttä edustaa tietoja, jotka on tiivistettävä jollakin tavalla (summaamalla, keskiarvoistamalla, maksimi-, vähimmäisarvot jne.). Se on melkein aina numeerinen tiedot. Täydellinen ehdokas tähän ruutuun näytetiedoissa on "Summa" -kenttä / sarake. Vedetään tämä kenttä arvot laatikko:

Huomaa, että (a) Kenttien luettelon kenttä "Määrä" on nyt merkitty ja "Summa" on lisätty arvot ruutu, joka osoittaa, että määrän sarake on summattu.

Jos tarkastelemme itse PivotTable -versiota, löydämme todellakin kaikkien "Summa" -arvojen summan raakatietojen laskentataulukosta:

Olemme luoneet ensimmäisen PivotTable! Kätevä, mutta ei erityisen vaikuttava. On todennäköistä, että tarvitsemme hieman enemmän tietoa meidän tiedoista.

Viitaten näytetietoihimme, meidän on tunnistettava yksi tai useampi sarakeotsikko, jota voisimme käyttää jakamaan tämän summan. Voimme esimerkiksi päättää, että haluamme nähdä yhteenvedon tiedoistamme, jossa meillä on rivin otsikko jokaiselle yrityksemme eri myyjälle ja kullekin yritykselle. Tämän saavuttamiseksi meidän täytyy vain vetää "Myyjä" -kenttä Riviketjut laatikko:

Nyt, vihdoin asiat alkavat saada mielenkiintoisia! PivotTable-ohjelmamme alkaa muotoutua… .

Muutamalla napsautuksella olemme luoneet taulukon, joka olisi kestänyt kauan tehdä manuaalisesti.

Joten mitä muuta voimme tehdä? No, jossakin mielessä meidän PivotTable on valmis. Olemme luoneet hyödyllisen yhteenvedon lähdetiedoistamme. Tärkeitä asioita on jo opittu! Jäljelle jäävässä artikkelissa tarkastellaan tapoja, joilla voidaan luoda monimutkaisempia PivotTablesia ja tapoja, joilla näitä PivotTables-ohjelmia voidaan muokata.

Ensinnäkin voimme luoda a kaksi-mittataulukko. Tehdään se käyttämällä "Maksutapa" sarakkeen otsikkona. Vedä "Maksutapa" -kohdan otsikko Sarakeetiketit laatikko:

Joka näyttää tältä:

Aloittaminen erittäin viileä!

Tehdään se a kolme-mittataulukko. Mitä tällainen pöytä voisi näyttää? No, katsotaanpa…

Vedä paketti-sarake / nimike kohtaan Raportin suodatin laatikko:

Huomaa, missä se päätyy… .

Näin voimme suodattaa raporttimme, joka perustuu siihen, mitä "lomapakettia" ostettiin. Näemme esimerkiksi myyjän ja maksutavan erittelyn kaikki paketteja, tai muutamalla napsautuksella muuttaaksesi sen osoittamaan samaa erittelyä "Sunseekers" -paketin osalta:

Ja jos ajattelet sitä oikealla tavalla, meidän PivotTable on nyt kolmiulotteinen. Jatketaan mukauttamista…

Jos käy ilmi, että haluamme vain nähdä sekki ja luottokortti transaktiot (eli ei käteisvaroja), niin voimme poistaa rahan kohteen sarakkeiden otsikoista. Napsauta vieressä olevaa avattavaa nuolta Sarakeetiketit, ja poista “Cash”:

Katsotaanpa, miltä se näyttää… Kuten näet, “Cash” on mennyt.

alustus

Tämä on luonnollisesti erittäin tehokas järjestelmä, mutta tulokset ovat toistaiseksi hyvin tavallisia ja tylsiä. Aluksi numerot, joita me summataan, eivät näytä dollarin määrinä - pelkät vanhat numerot. Korjaa se.

Kiusaus voisi olla tehdä se, mitä olemme tottuneet tekemään tällaisissa olosuhteissa, ja valitse vain koko taulukko (tai koko laskentataulukko) ja käytä työkalupalkin vakionumeron muotoilupainikkeita muotoilun loppuunsaattamiseksi. Tämän lähestymistavan ongelmana on se, että jos muutat PivotTable-järjestelmän rakennetta tulevaisuudessa (mikä on 99% todennäköistä), nämä numeromuodot menetetään. Tarvitsemme tavan, jolla ne pysyvät pysyvinä.

Ensinnäkin löydämme "Summan summa" -merkinnän arvot ruutuun ja napsauta sitä. Näyttöön tulee valikko. Valitsemme Arvo-kentän asetukset… valikosta:

Arvo-kentän asetukset tulee näkyviin.

Klikkaa Numeromuoto -painike ja standardi Alusta solut -ruutu näyttää:

Vuodesta Kategoria lista, valitse (sano) Kirjanpito, ja pudota desimaalien määrä arvoon 0. Napsauta kunnossa muutaman kerran palataksesi PivotTableiin…

Kuten näette, numerot on muotoiltu oikein dollarimääräisinä.

Vaikka olemme muotoilussa, muotoile koko PivotTable. Tähän on muutamia tapoja. Käytetään yksinkertaista…

Klikkaa PivotTable Työkalut / Suunnittelu välilehti:

Sitten pudota alas nuoli oikeassa alareunassa PivotTable-tyylit luettelon nähdäksesi suuren valikoiman sisäänrakennettuja tyylejä:

Valitse jokin, joka vetoaa, ja katso PivotTable-tulos:

Muita vaihtoehtoja

Voimme työskennellä myös päivämäärien kanssa. Nyt yleensä on monia, monta päivämäärää tapahtumaluettelossa, kuten aloitimme. Mutta Excel tarjoaa mahdollisuuden ryhmitellä datayksiköt yhteen päivän, viikon, kuukauden, vuoden jne. Mukaan. Katsotaanpa miten tämä tehdään.

Poista ensin Maksutapa-sarake Sarakeetiketit ruutu (vedä se takaisin kenttäluetteloon) ja korvaa se "Varattu päivämäärä" -sarakkeeseen:

Kuten näette, tämä tekee PivotTable-palveluksestamme heti hyödytön, antamalla meille yhden sarakkeen jokaisesta päivästä, jona tapahtuma tapahtui - hyvin laaja pöytä!

Korjaa tämä napsauttamalla mitä tahansa päivämäärää hiiren kakkospainikkeella ja valitsemalla Ryhmä… kontekstivalikosta:

Ryhmittelylaatikko tulee näkyviin. Valitsemme Kuukaudet ja napsauta OK:

Voila! paljon hyödyllinen taulukko:

(Muuten tämä taulukko on käytännöllisesti katsoen identtinen tämän artikkelin alussa esitetyn kanssa - manuaalisesti luotuun alkuperäiseen myyntiyhteenvetoon.)

Toinen hieno asia, joka on tietoinen, on se, että sinulla voi olla useampi kuin yksi rivinimikkeiden sarja (tai sarakkeiden otsikot):

… Joka näyttää tältä ... .

Voit tehdä samanlaisen asian sarakkeiden otsikoiden kanssa (tai jopa raportoida suodattimia).

Pidä asiat yksinkertaisina, katsotaanpa, miten piirtää keskimäärin arvojen sijaan.

Napsauta ensin ”Summan summa” ja valitse Arvo-kentän asetukset… näkyvästä kontekstivalikosta:

Vuonna Yhteenveto arvokenttä luettelossa Arvo-kentän asetukset ruutuun, valitse Keskiverto:

Kun olemme täällä, muutetaan Mukautettu nimi, "Keskimääräisestä määrästä" johonkin hieman tiiviimpään. Kirjoita jotain "Avg":

Klikkaus kunnossa, ja katso, miltä se näyttää. Huomaa, että kaikki arvot vaihtelevat yhteenlasketuista summista keskiarvoiksi, ja taulukon otsikko (ylhäällä vasemmalla oleva solu) on muuttunut arvoon "Keskiarvo":

Jos haluamme, voimme jopa saada summat, keskiarvot ja laskelmat (laskee = kuinka monta myyntiä siellä oli) samassa PivotTable-sivustossa!

Seuraavassa on ohjeita saadaksesi jotain sellaista, joka on käytössä (alkaen tyhjästä kääntöpöydästä):

1. Vedä “Myyjä” osioon Sarakeetiketit
2. Vedä "Määrä" -kenttä alas arvot kolme kertaa
3. Muuta ensimmäistä "Määrä" -kenttää muuttamalla sen mukautettu nimi "Yhteensä" ja sen numeromuoto Kirjanpito (0 desimaalin tarkkuudella)
4. Toisen ”Summa” -kentän kohdalla voit muuttaa sen mukautettua nimeä ”Keskiarvo”, sen toiminnoksi Keskiverto ja sen numeromuoto Kirjanpito (0 desimaalin tarkkuudella)
5. Kolmanneksi "Määrä" -kenttään vaihda sen nimi "Count" ja sen toiminto Kreivi
6. Vedä automaattisesti luotua kenttä Sarakeetiketit että Riviketjut

Seuraavassa on:

Yhteensä, keskiarvo ja sama PivotTable!

johtopäätös

Microsoft Excelin luomia PivotTables-ohjelmistoja on monia, monia muita ominaisuuksia ja vaihtoehtoja - aivan liian monta luetteloa tällaisessa artikkelissa. PivotTablesin mahdollisuuksien kattamiseksi tarvitaan pieni kirja (tai suuri verkkosivusto). Uskomaton ja / tai geeky lukija voi tutkia PivotTablesia vielä melko helposti: Napsauta hiiren kakkospainikkeella lähes kaikkea, ja katso, mitä vaihtoehtoja voit käyttää. On myös kaksi nauha-välilehteä: PivotTable-työkalut / -asetukset ja Design.  Ei ole väliä, jos teet virheen - PivotTable on helppo poistaa ja aloittaa uudelleen - mahdollisuus, että Lotus 1-2-3: n vanhoja DOS-käyttäjiä ei koskaan ollut.

Jos työskentelet Office 2007: ssä, saatat haluta tarkistaa artikkelin PivotTable-ohjelman luomisesta Excel 2007: ssä.

Olemme sisällyttäneet Excel-työkirjan, jonka voit ladata harjoitellaksesi PivotTable-taitojasi. Sen pitäisi toimia kaikkien Excel-versioiden kanssa vuodesta 97 alkaen.

Lataa käytännön Excel-työkirja