Pivot -taulukot ovat yksi Excelin tehokkaimmista ominaisuuksista. Vaikka oletkin aloittelija, voit murskata suuria tietomääriä hyödyllisiksi tiedoiksi. Pivot -taulukko voi auttaa sinua tekemään raportteja muutamassa minuutissa. Analysoi puhtaat tiedot helposti ja jos tiedot eivät ole puhtaita, se voi auttaa sinua puhdistamaan tiedot. En halua kyllästyttää sinua, joten hyppäämme siihen ja tutkimme.
Pivot -taulukon luominen
Se on yksinkertaista. Valitse vain tietosi. Siirry Lisää -kohtaan. Napsauta pivot -taulukkoa ja se on valmis.
Mutta odota. Ennen kuin luot pivot -taulukon, varmista, että kaikissa sarakkeissa on otsikko.
Jos jokin sarakkeen otsikko jätetään tyhjäksi, pivot -taulukkoa ei luoda ja se lähettää virheilmoituksen.
Vaatimus 1: Kaikissa sarakkeissa on oltava otsikko, jotta voit aloittaa Excelin pivot -taulukoiden käytön
Sinun pitäisi järjestää tietosi oikealla otsikolla. Kun olet saanut sen, voit lisätä pivot -taulukon.
Aseta pivot -taulukko valintanauhasta
Voit lisätä pivot -taulukon valikosta seuraavasti:
1. Valitse tietoalue
2. Siirry välilehteen
3. Napsauta Pivot Table -kuvaketta.
4. Luo pivot -taulukko -vaihtoehto tulee näkyviin.
5. Täällä näet valitsemasi tietoalueen. Jos luulet, että tämä ei ole alue, jonka haluat valita, muuta alueesi kokoa suoraan täältä sen sijaan, että palaisit ja valitsisit tiedot uudelleen.
6. Seuraavaksi voit valita, mihin haluat kääntötaulukon. Suosittelen uuden laskentataulukon käyttöä, mutta voit käyttää myös nykyistä laskentataulukkoa. Määritä vain sijainti Sijainti -ruudussa.
7. Jos olet nyt tehnyt asetukset, paina OK -painiketta. Saat pivot -taulukon uuteen taulukkoon. Valitse vain kentät yhteenvetoja varten. Katsotaan, miten luomme yhteenvedon tiedoista pivot -taulukon avulla, mutta ensin selvitetään perusasiat. Tässä Excel -pivot -taulukon opetusohjelmassa opit enemmän kuin odotit.
Lisää pivot -taulukon pikakuvake (Alt> N> V)
Tämä on peräkkäinen pikanäppäin, joka avaa Luo Pivot -taulukko lisälaatikko.
Paina Alt -painiketta ja vapauta se. Osuma N ja vapauta se. Osuma V ja vapauta se. Luo pivot -taulukko -valintaruutu avautuu.
Seuraa nyt yllä olevaa menettelyä luodaksesi pivot -taulukon Excelissä.
Lisää pivot -taulukon pikakuvake vanhalla Excel -pikakuvakkeella (Alt> D> P)
Yksi asia, josta pidän eniten Microsoft Excelissä, on se, että jokaisessa uudessa Excel -versiossa ne tuovat uusia ominaisuuksia, mutta ne älä hylkää vanhoja ominaisuuksia (kuten MS teki voitolla 8. Se oli säälittävää). Tämä sallii vanhojen käyttäjien työskennellä normaalisti uusien versioiden kanssa kuten ennenkin vanhempien versioiden kanssa.
Jos painat peräkkäin ALT, D ja P näppäimistöllä Excel avaa ohjatun pivot -taulukon luomisen.
Valitse sopiva vaihtoehto. Yllä olevassa kuvakaappauksessa valittu vaihtoehto johtaa meidät luomaan pivot -taulukon kuten aiemmin.
Paina Enter tai napsauta Seuraava, jos haluat tarkistaa valitsemasi alueen.
Paina Enter uudelleen.
Valitse uusi laskentataulukko tai minne haluat, että pivot -taulukko painaa Enter -näppäintä. Ja se on tehty.
Luo raportteja pivot -taulukoiden avulla
Nyt tiedät, miten pivot -taulukko lisätään. Valmistellaan raportteja pivot -taulukoiden avulla muutamassa minuutissa.
Meillä on tiedot kiinteästä tilauksesta.
Sarakekentät ovat:
Tilauspäivämäärä: Tilauspäivä (ilmeisesti)
Alue: Järjestysalue maassa
Asiakas: Asiakkaan nimi (mitä muuta se voi olla)
Tuote: Tilattu tuote
Yksikkö: Tilatun tuotteen yksikkömäärä
Yksikköhinta: Yksikköhinta
Kaikki yhteensä: Tilauksen kokonaiskustannukset (yksikkö*yksikköhinta).
Käytämme raporttien luomiseen pivot -taulukoita käyttäen
Pivot -taulukon kentät: Sisältää datasi sarakkeiden nimiluettelon.
Kääntöalueet: Näitä neljää aluetta käytetään tietojen näyttämiseen käytetyllä tavalla.
SUODATTIMET: Laita tähän kentät, joista haluat käyttää suodattimia raportissasi.
Sarakkeet: Laita haluamasi kentät tähän raportin sarakkeisiin: (On parempi näyttää kuin selittää)
RIVIT: Vedä kentät, jotka haluat näyttää raakana, kuten yllä olevassa kuvassa, olen näyttänyt alueen ROWS.
ARVOT: Valitse kenttä saadaksesi Laskenta, Summa, Keskiarvo, Prosentti (ja paljon muuta) jne., Joita haluat nähdä.
Nyt käyttämällä edellä olevia tietoja olemme laatineet tämän nopean pivot -raportin, joka näyttää mistä alueesta kuinka monta tilausta kullekin tuotteelle on tehty.
Nyt kun ymmärrät tietosi ja Pivot -kentät (olet älykäs), vastaamme nopeasti näihin tietoihin liittyviin kysymyksiin pivot -taulukon avulla nopeasti.
Q1. Kuinka monta tilausta on?
Pivot -taulukko on aluksi tyhjä, kuten alla olevassa kuvassa näkyy.
Sinun on valittava kentät (sarakkeiden nimet) sopivilta alueilta, jotta näet kyseisen kentän yhteenvedon tai tiedot.
Nyt, vastatakseni yllä olevaan kysymykseen, valitsen arvokentistä kohteen (valitse mikä tahansa sarake, varmista vain, että sen välissä ei ole tyhjää solua).
Valitse kohde kenttäluettelosta ja vedä se Arvo -kenttään.
Meillä on vastauksemme. Pivot -taulukot kertovat minulle, että tilauksia on yhteensä 43. Tämä on oikein.
Nyt kun olen valinnut Tilauspäivämäärä arvot -alueella, se näkyy 42. Tämä tarkoittaa Tilauspäivämäärä on tyhjä solu siitä asti kun tiedämme, että tilausten kokonaismäärä on 43.
Tunnista epäsäännölliset tiedot pivot -taulukoiden avulla ja puhdista ne.
Pivot -taulukko voi auttaa sinua löytämään virheellisiä tietoja tiedoista.
Suurimman osan ajasta tiedot valmistavat tietojen syöttöoperaattorit tai käyttäjät, jotka ovat yleensä epäsäännöllisiä ja tarvitsevat puhdistusta tarkan raportin ja analyysin laatimiseksi.
Sinun on aina puhdistettava ja valmisteltava tiedot käytetyllä tavalla ennen raporttien laatimista. Mutta joskus vasta raportin valmistelun jälkeen saamme tietää, että tietoissamme on jonkin verran epäsäännöllisyyttä. Tule, näytän sinulle kuinka…
Kysymys 2: Kerro tilausten määrä kultakin alueelta
Nyt vastata tähän kysymykseen:
Valitse Alue ja vedä se kohtaan Rivit Alue ja Tuote kohteeseen Arvot Alue.
Saamme Region-Wise-jaetun datan. Voimme vastata alueelta, kuinka monta tilausta on tullut.
Pivot -taulukon mukaan tiedoissamme on yhteensä 4 aluetta. Mutta odota, huomaa se Keski ja Centrle alueella. Tiedämme, että Centrlen pitäisi olla Keski. On epäsäännöllisyyttä. Meidän on siirryttävä tietoihimme ja puhdistettava tiedot.
Puhdistaaksemme aluekentän tiedot suodatamme pois väärä aluenimen (Centrle) ja korjaamme sen (Central).
Palaa nyt pivot -tietoihisi.
Napsauta hiiren kakkospainikkeella mitä tahansa pivot -taulukon kohtaa ja napsauta Päivitä.
Raporttisi on nyt päivitetty.
Nyt voit nähdä, että itse asiassa on vain 3 aluetta.
Pivot -raportin muotoilu luokiteltujen rivien avulla.
Joskus tarvitset raportteja, kuten yllä oleva kuva. Tämä helpottaa tietojen näkemistä jäsennellyllä tavalla. Voit helposti kertoa mistä alueesta kuinka monta tuotetta on tilattu. Katsotaanpa, miten voit tehdä tämän.
Liikkua Alue ja Tuote kohteeseen RIVIT alueella. Varmista, että alue on ylhäällä ja kohteet alhaalla kuvan osoittamalla tavalla.
Raahata Tuote varten Arvo Alue.
Tämän seurauksena saat tämän raportin.
Se onnistuu. Mutta joskus pomosi haluaa raportoida taulukkomuodossa ilman välisummia. Tätä varten meidän on alustettava pivot -taulukko.
Poista välisummat pivot -taulukosta
Toimi seuraavasti:
1. Napsauta mitä tahansa pivot -taulukon kohtaa.
2. Siirry kohtaan Design Välilehti
3. Napsauta välisummat valikko.
4. Napsauta Älä näytä välisummia.
Näet, että nyt ei ole välisummia.
Hieno. Mutta se ei edelleenkään ole taulukkomuodossa. Alueet ja kohteet näkyvät yhdessä sarakkeessa. Näytä ne erikseen.
Tee pivot -taulukosta taulukko
Jos haluat näyttää alueet ja kohteet eri sarakkeissa, toimi seuraavasti:
1. Napsauta mitä tahansa pivot -taulukon kohtaa
2. Siirry Suunnittelu -välilehdelle
3. Napsauta Raportin asettelu.
4. Napsauta Näytä taulukkomuodolle. Lopulta saat tämän hienostuneen kuvan raportistasi.
Nyt tiedämme kullekin tuotteelle tehtyjen tilausten kokonaismäärän kultakin alueelta. Se näkyy Countissa Kohteet -sarakkeista.
Muuta sarakkeen nimeksi Tilaukset.
Nyt näyttää paremmalta.
Kysymys 3: Kuinka monta yksikköä jokaisesta tuotteesta tilataan?
Jotta voimme vastata tähän kysymykseen, tarvitsemme yksiköiden summan. Voit tehdä tämän siirtämällä Yksiköt -kentän arvoihin. Se laskee automaattisesti kunkin kohteen yksiköiden määrän. Jos pivot -taulukon sarake sisältää vain arvoja, pivot -taulukko näyttää oletusarvoisesti näiden arvojen summan. Mutta sen voi muuttaa arvokentän asetuksesta. Miten? Näytän sinulle jälkimmäisen.
Pivot -taulukon arvokentän asetukset
Kysymys 4: Jokaisen tuotteen keskihinta?
Näytetiedoissamme yhden tuotteen hinta on erilainen eri tilauksissa. Katso esimerkiksi Sidokset.
Haluan tietää pivot -taulukon kunkin kohteen keskimääräiset kustannukset. Voit selvittää tämän vetämällä Yksikköhinta arvo -kenttään. Se näyttää yksikkökustannusten summan.
Emme halua Yksikkökustannusten summa, me haluamme Yksikkökustannusten keskiarvo. Tehdä niin…
1. Napsauta hiiren kakkospainikkeella mitä tahansa pivot -taulukon yksikkökustannus -sarakkeen summaa
2. Napsauta Arvokentän asetukset
3. Käytettävissä olevien vaihtoehtojen perusteella, valitse Keskiverto ja paina OK.
Lopuksi saat tämän pivot -raportin:
Pivot -taulukon lasketut kentät
Yksi pivot -taulukon hyödyllisimmistä toiminnoista on sen lasketut kentät. Lasketut kentät ovat kenttiä, jotka saadaan joidenkin käytettävissä olevien sarakkeiden toimintojen avulla.
Ymmärrämme, miten lasketut kentät lisätään pivot -taulukkoon yhdellä esimerkillä:
Laadimme tämän raportin tietojemme perusteella.
Tässä meillä on Yksiköiden summa ja Kokonaiskustannukset. Siirsin koko sarakkeen Arvot -kenttään ja nimesin sen sitten uudelleen Kokonaiskustannukset. Nyt haluan tietää kunkin kohteen yksikön keskihinnan kullakin alueella. Ja se olisi:
Keskihinta = kokonaiskustannukset / yksiköt
Lisätään pivot -taulukkoon kenttä, joka näyttää kunkin nimikealueen keskihinnan:
Lisää laskettu kenttä pivot -taulukkoon seuraavasti
1. Napsauta mitä tahansa pivot -taulukon kohtaa ja siirry Analysoi -välilehteen
2. Napsauta laskentaryhmässä kenttiä, kohteita ja sarjoja.
3. Napsauta Lasketut kentät.
Näet tämän syöttökentän laskukenttään:
4. Kirjoita nimen syöttökenttään keskimääräiset kustannukset tai mitä haluat, Excel ei välitä. Kirjoita kaavan syöttökenttään ja paina OK.
= Yhteensä / yksikköä
Voit kirjoittaa tämän manuaalisesti näppäimistöltä tai kaksoisnapsauttaa Kentät -alueella lueteltuja kenttien nimiä suorittaaksesi toimintoja.
5. Laskettu kenttä on nyt lisätty pivot -taulukkoon. Sitä kutsutaan keskimääräisten kustannusten summaksi, mutta se ei ole summa. Excel suorittaa vain oletustoiminnon sarakkeen nimeämiseksi (kuten rituaali). Nimeä tämä sarake uudelleen ja rajoita näytettäviä desimaalilukuja.
Ja siellä on laskettu kenttä. Voit tehdä siitä niin monimutkaisen kuin haluat. Esimerkin vuoksi otin tämän yksinkertaisen keskimääräisen operaation.
Ryhmittely pivot -taulukossa
Olet valmistanut tämän pivot -raportin.
Nyt haluan, että tämä raportti jaetaan vuosittain. Katso tilannekuva alla.
Meillä on sarake tilauspäivästä. Siirrä OrderDate -kenttä yläreunan riveille.
Se ei näytä lainkaan vaadittavalta raportilta. Meidän on pidettävä ryhmäpäivämäärät vuosittain.
Voit nyt ryhmitellä kentän Excel -pivot -taulukossa seuraavasti:
1. Napsauta hiiren kakkospainikkeella kenttää, jonka haluat ryhmitellä.
2. Napsauta Ryhmä. Sinulla on tämä vaihtoehto ruutu mukauttamista varten. Koska tämä on tietosarake, Excel näyttää ryhmittelyn sen mukaisesti. Voit valita aloitus- ja päättymispäivän.
3. Valitse vuosiksi ja paina OK. Olet tehnyt vuosittaisen ryhmittelyn Excelin pivot -taulukossa.
Pivot -taulukon viipaleet
Viipaloijat esiteltiin Excel 2010: ssä lisäosana. Excel 2013: ssa ja 2016: ssa se on oletuksena käytettävissä aivan kuten suodattimet.
Viipaloijat ovat vain suodattimia. Toisin kuin suodattimet, Slicers näyttää kaikki käytettävissä olevat vaihtoehdot edessäsi. Se tekee kojelaudasta interaktiivisemman.
Leikkureiden lisääminen pivot -taulukkoon, Excel 2016 ja 2013
Pivot Table Slicers on helppo lisätä. Toimi seuraavasti:
1. Napsauta mitä tahansa pivot -taulukon kohtaa ja siirry Analysoi -välilehteen.
2. Napsauta Lisää viipaloija. Sinulla on luettelo tietojesi kentistä. Valitse niin monta kuin haluat.
3. Valitse tässä esimerkissä Alue ja paina OK.
Olet lisännyt Slicerin raporttiisi. Käytä nyt suodatinta yhdellä napsautuksella.
Lisää aikajana Excelissä 2016 ja 2013
Tämä on yksi suosikkitoiminnoistani Excel -pivot -taulukoista. Tämä uusi toiminto toimii vain päivämäärien kanssa. Tämän avulla voit visuaalisesti valita ajanjakson tietojen suodattamiseen.
Voit lisätä aikajanan kohdistimen seuraavasti:
Aikajanan lisääminen pivot -taulukkoon, Excel 2016 ja 2013
1. Napsauta mitä tahansa pivot -taulukon kohtaa ja siirry Analysoi -välilehteen.
2. Napsauta Lisää aikajana suodatinryhmästä. Kaikki sarakkeet, jotka sisältävät aika -arvoja hankituissa tiedoissa, luetellaan valintaruudussa, josta voit valita. Täällä meillä on vain yksi. Joten kyllä…
2. Valitse vaihtoehdot ja paina Enter tai napsauta OK. Se on tehty ja pivot -taulukon aikajana on edessäsi.
Voit näyttää sen päivittäin, kuukausittain, neljännesvuosittain tai vuosittain. Olen valinnut kuukausittaisen täältä.
Tässä artikkelissa olen käsitellyt pivot -taulukon tärkeimpiä ja hyödyllisimpiä toimintoja. Tutkimme pivot -taulukon uusia ominaisuuksia Excelissä 2016 ja 2013. Opimme perinteisen Excel 2007, 2010 ja vanhemmissa versioissa käytetyn pivot -taulukon käytöstä. Ne ovat edelleen hyödyllisiä. Jos et löytänyt pivot -taulukkoosi liittyvää vastaustasi täältä, kysy kommenttikentässä.
On monia muita toimintoja, joita ei ole vielä selitetty. Seuraavassa artikkelissa opimme edistyneen pivot -taulukkotoiminnon. Siihen asti Excel kaikkeen.