Pivot -tabelid on Exceli üks võimsamaid funktsioone. Isegi kui olete algaja, saate suure hulga andmeid kasulikuks teabeks purustada. Pivot tabel aitab teil aruandeid koostada minutitega. Analüüsige oma puhtaid andmeid lihtsalt ja kui andmed pole puhtad, võib see aidata teil oma andmeid puhastada. Ma ei taha teid tüditada, nii et lähme asja juurde ja uurime.
Pivot -tabeli loomine
See on lihtne. Valige lihtsalt oma andmed. Avage Lisa. Klõpsake pöördtabelit ja see on tehtud.
Aga oota. Enne pöördtabeli loomist veenduge, et kõigil veergudel oleks pealkiri.
Kui mõni veeru pealkiri jäetakse tühjaks, siis liigendtabelit ei looda ja see kuvab veateate.
Nõue 1: kõikidel veergudel peaks olema pealkiri, et alustada Exceli liigendtabelitega
Te peaksite oma andmed korrektse pealkirjaga korraldama. Kui see on olemas, saate pöördtabeli sisestada.
Pöördtabeli sisestamine lindilt
Pöördtabeli lisamiseks menüüst toimige järgmiselt.
1. Valige oma andmevahemik
2. Minge vahelehele Lisa
3. Klõpsake pöördtabeli ikooni.
4. Ilmub suvandikast Create Pivot Table.
5. Siin näete valitud andmevahemikku. Kui arvate, et see ei ole vahemik, mida soovite valida, muutke oma vahemiku suurust siit, selle asemel et naasta ja andmeid uuesti valida.
6. Järgmisena saate valida, kuhu soovite oma pöördtabeli paigutada. Soovitan kasutada uut töölehte, kuid saate kasutada ka praegust töölehte. Määrake lihtsalt asukoht väljal Asukoht.
7. Kui olete seadetega lõpetanud, klõpsake nuppu OK. Pöördtabel kuvatakse uuel lehel. Valige kokkuvõtete tegemiseks lihtsalt oma väljad. Näeme, kuidas me pöördetabeli abil andmete kokkuvõtet loome, kuid kõigepealt teeme selgeks põhitõed. Selles Exceli pöördtabeli õpetuses saate teada rohkem, kui arvate.
Sisestage liigendtabeli otsetee (Alt> N> V)
See on järjestikune kiirklahv, mille eesmärk on avada Loo Pivot tabel valikukast.
Vajutage nuppu Alt ja vabastage see. Tabas N ja vabastage see. Tabas V ja vabastage see. Avaneb suvandikast Pivot Table loomine.
Nüüd järgige lihtsalt ülaltoodud protseduuri, et luua Excelis liigendtabel.
Sisestage liigendtabeli otsetee vana Exceli otsetee abil (Alt> D> P)
Üks asi, mis mulle Microsoft Exceli juures kõige rohkem meeldib, on see, et igas uues Exceli versioonis tutvustatakse uusi funktsioone, kuid mitte ärge jätke vanu funktsioone kõrvale (nagu MS tegi võiduga 8. See oli haletsusväärne). See võimaldab vanadel kasutajatel normaalselt töötada uute versioonidega, nagu nad töötasid vanemate versioonidega.
Kui vajutate järjestikku ALT, D ja P Klaviatuuril avaneb Excel, et luua pivot tabeli viisard.
Valige sobiv valik. Ülaltoodud ekraanipildil valitud suvand viib meid looma pöördtabeli, nagu me varem lõime.
Vajutage sisestusklahvi või klõpsake nuppu Edasi, kui soovite valitud vahemikku kontrollida.
Vajutage uuesti sisestusklahvi.
Valige uus tööleht või kõikjal, kus soovite, et teie liigendtabel vajutaks sisestusklahvi. Ja see on tehtud.
Aruannete loomine liigendtabelite abil
Nüüd teate, kuidas pöördtabelit sisestada. Alustame aruannete koostamist pöördtabelite abil minutitega.
Meil on statsionaarse tellimuse andmed.
Veeruväljad on järgmised:
Tellimuse kuupäev: Tellimuse kuupäev (ilmselt)
Piirkond: Korrapiirkond riigis
Klient: Kliendi nimi (mis see veel olla saab)
Toode: Tellitud kaup
Ühik: Tellitud kauba ühikute arv
Ühiku maksumus: Ühikuhinna kohta
Kokku: Tellimuse kogumaksumus (ühik*ühiku hind).
Pöördtabelite abil aruannete loomiseks kasutame
Pivot tabeli väljad: Sisaldab teie andmete veergude nimede loendit.
Pöördpiirkonnad: Neid nelja valdkonda kasutatakse teie andmete kombekas kuvamiseks.
FILTRID: Pange siia väljad, millelt soovite aruandesse filtreid rakendada.
VEERGAD: pange aruande veergudesse soovitud väljad: (parem näidata kui seletada)
RIDA: Lohistage välju, mida soovite toorelt näidata, nagu ülaltoodud pildil, olen näidanud regiooni ridades.
VÄÄRTUSED: Valige väli, et saada arv, summa, keskmine, protsent (ja palju muud) jne, mida soovite näha.
Nüüd, kasutades ülaltoodud teavet, oleme koostanud selle kiire pöördaruande, mis näitab, millisest piirkonnast iga kauba kohta tellimusi esitatakse.
Nüüd, kui saate aru oma andmetest ja Pivot -väljadest (olete ju nutikas), vastame pöördetabeli abil kiiresti mõnele nende andmetega seotud küsimusele.
Q1. Mitu tellimust on?
Pöördtabel on esialgu tühi, nagu on näidatud alloleval pildil.
Selle välja kokkuvõtte või üksikasjade nägemiseks peate vastavatel aladel valima väljad (veerunimed).
Nüüd, et vastata ülaltoodud küsimusele, valin väärtusväljadel üksuse (valige ükskõik milline veerg, veenduge, et sellel pole tühja lahtrit).
Valige väljade loendist üksus ja lohistage see väljale Väärtus.
Meil on oma vastus. Pivot tabelid ütlevad mulle, et kokku on 43 tellimust. See on õige.
Nüüd, kui olen valinud Tellimiskuupäev Väärtuste piirkonnas näitab see 42. See tähendab Tellimiskuupäev on tühi lahter aastast me teame, et tellimuste koguarv on 43.
Tuvastage ebaregulaarsed andmed Pivot -tabelite abil ja puhastage need.
Pivot tabel aitab teil andmetest vale teavet leida.
Enamasti koostavad meie andmeid andmesisestusoperaatorid või kasutajad, kes on reeglina ebaregulaarsed ja vajavad täpset aruannet ja analüüsi.
Enne aruannete koostamist peaksite oma andmed alati puhastama ja ette valmistama viisil. Kuid mõnikord saame alles pärast aruande koostamist teada, et meie andmetel on mõningaid ebakorrapärasusi. Tule, ma näitan sulle, kuidas…
Q2: öelge tellimuste arv igast piirkonnast
Nüüd sellele küsimusele vastamiseks:
Valige Piirkond ja lohistage see kohale Ridad Piirkond ja Üksus et Väärtused Piirkond.
Saame piirkondade kaupa jagatud andmed. Me võime vastata, millisest piirkonnast on tulnud palju tellimusi.
Pivot tabeli kohaselt on meie andmetes kokku 4 piirkonda. Kuid oodake, pange tähele Kesk- ja kesklinn piirkonnas. Me teame, et Centrle peaks olema Kesk. Esineb ebakorrapärasus. Peame minema oma andmetele ja puhastama andmeid.
Piirkonna välja andmete puhastamiseks filtreerime välja vale piirkonna nime (Centrle) ja parandame selle (Kesk).
Nüüd pöörduge tagasi pöördandmete juurde.
Paremklõpsake pöördtabeli suvalist kohta ja klõpsake nuppu Värskenda.
Teie aruannet on nüüd värskendatud.
Nüüd näete, et tegelikult on ainult 3 piirkonda.
Pivot -aruande vormindamine kategoriseeritud ridadega.
Mõnikord vajate selliseid aruandeid nagu ülaltoodud pilt. See muudab teie andmete struktureeritud vaatamise lihtsaks. Saate hõlpsasti öelda, millisest piirkonnast on tellitud tooteid. Vaatame, kuidas saate seda teha.
Liiguta Piirkond ja Üksus et RIDA piirkonnas. Veenduge, et piirkond oleks ülaosas ja üksused all, nagu pildil näidatud.
Vedama Üksus eest Väärtus Piirkond.
Selle tulemusena saate selle aruande.
See saab hakkama. Kuid mõnikord soovib teie ülemus aru anda tabelis ilma vahekokkuvõteteta. Selleks peame vormindama oma liigendtabeli.
Eemaldage liigendtabelist vahesummad
Järgige neid samme.
1. Klõpsake liigendtabelis suvalisel kohal.
2. Minge Disain Tab.
3. Klõpsake nuppu vahesummad menüü.
4. Klõpsake nuppu Ära kuva vahesummasid.
Näete, et praegu pole vahekokkuvõtteid.
Hästi. Kuid see pole endiselt tabeli kujul. Piirkonnad ja üksused kuvatakse ühes veerus. Näidake neid eraldi.
Tehke pöördtabel tabelina
Piirkondade ja üksuste kuvamiseks erinevates veergudes toimige järgmiselt.
1. Klõpsake liigendtabelis suvalisel kohal
2. Minge vahekaardile Disain
3. Klõpsake nuppu Aruande paigutus.
4. Klõpsake Show tabelivormi jaoks. Lõpuks näete oma raportist seda keerukat vaadet.
Nüüd teame iga piirkonna kohta iga toote kohta tehtud tellimuste koguarvu. See on näidatud loendis veergudest.
Palun muutke veeru nimi Tellimused.
See näeb nüüd parem välja.
3. küsimus: mitu ühikut igast kaubast tellitakse?
Sellele küsimusele vastamiseks vajame ühikute summat. Selleks teisaldage väli Ühikud väärtustele. See võtab automaatselt kokku iga üksuse ühikute arvu. Kui liigendtabeli veerg sisaldab ainult väärtusi, kuvab liigendtabel vaikimisi nende väärtuste summa. Kuid seda saab muuta väärtusvälja seadest. Kuidas? Näitan teile viimast.
Pivot tabeli väärtuse välja sätted
Q4: iga kauba keskmine hind?
Meie näidisandmetes on ühe kauba hind erinevate tellimuste puhul erinev. Näiteks vt Binders.
Soovin teada iga liigendtabeli üksuse keskmist maksumust. Selle leidmiseks lohistage ühikukulu väärtusväljale. See näitab ühikukulu summat.
Me ei taha Ühiku maksumuse summa, me tahame Ühiku maksumuse keskmine. Selleks…
1. Paremklõpsake liigendtabeli veeru Ühikukulu summat suvalisel kohal
2. Klõpsake nuppu Väärtusvälja seaded
3. Saadud valikute põhjal, valige Keskmine ja vajuta OK.
Lõpuks saate selle Pivoti aruande:
Pivot tabeli arvutatud väljad
Pivot tabeli üks kasulikumaid funktsioone on selle arvutatud väljad. Arvutatud väljad on väljad, mis saadakse teatud toimingute abil saadaolevatel veergudel.
Mõistame arvutusväljade sisestamist liigendtabelisse ühe näitega:
Meie andmete põhjal koostasime selle aruande.
Siin on meil Ühikute summa ja Kogumaksumus. Kolisin kogu veeru väljale Väärtused ja nimetasin selle ümber Kogumaksumus. Nüüd tahan teada iga toote ühiku keskmist hinda iga piirkonna kohta. Ja see oleks:
Keskmine hind = kogukulu / ühikud
Lisame liigendtabelisse välja, mis näitab iga üksuse piirkonna keskmist hinda:
Arvutatud välja sisestamiseks pöördetabelisse järgige neid samme
1. Klõpsake liigendtabeli suvalist kohta ja minge vahekaardile Analüüsi
2. Arvutusrühmas klõpsake väljad, üksused ja komplektid.
3. Klõpsake Arvutatud väljad.
Näete seda arvutusvälja sisestuskasti:
4. Nime sisestuskasti kirjuta keskmine hind või mis iganes sulle meeldib, excelil pole selle vastu midagi. Kirjutage valemi sisestuskasti ja klõpsake nuppu OK.
= Kokku / ühikut
Saate selle kirjutada klaviatuurilt käsitsi või toimingute tegemiseks topeltklõpsata väljade alal loetletud väljade nimedel.
5. Teie arvutatud väli on nüüd liigendtabelisse lisatud. Seda nimetatakse keskmise kulu summaks, kuid see pole summa. Excel käivitab veeru nimetamiseks vaikimisi funktsiooni (nagu rituaal). Nimetage see veerg ümber ja piirake kuvatavate kümnendkohtade arvu.
Ja seal on teil arvutatud väli. Saate selle muuta nii keeruliseks kui soovite. Näiteks võtsin selle lihtsa keskmise toimingu.
Rühmitamine liigendtabelis
See pöördaruanne on teile ette valmistatud.
Nüüd soovin, et seda aruannet jagataks igal aastal. Vaadake allolevat hetktõmmist.
Meil on veerg tellimuse kuupäeva kohta. Liigutage väli OrderDate üleval olevatele ridadele.
See ei näe välja nagu nõutud aruanne. Meil peavad rühmade kuupäevad olema aastaringselt.
Nüüd Exceli liigendtabelis välja rühmitamiseks toimige järgmiselt.
1. Paremklõpsake välja, mille soovite rühmitada.
2. Klõpsake Group. Teil on see suvandikast kohandamiseks. Kuna see on andmete veerg, näitab Excel meile rühmitust vastavalt. Saate valida oma algus- ja lõppkuupäeva.
3. Valige aastaid ja klõpsake nuppu OK. Olete teinud iga -aastase rühmitamise Exceli liigendtabelis.
Pivot tabeli viilutajad
Viilutajad tutvustati Excel 2010 lisandmoodulina. Excelis 2013 ja 2016 on see vaikimisi saadaval nagu filtrid.
Viilutajad pole midagi muud kui filtrid. Erinevalt filtritest näitab viilutaja kõiki teie ees olevaid valikuid. See muudab teie armatuurlaua interaktiivsemaks.
Viilutajate lisamine liigendtabelisse, Excel 2016 ja 2013
Pivot Table Slicers on lihtne lisada. Järgige neid samme.
1. Klõpsake liigendtabeli suvalist kohta ja minge vahekaardile Analüüsi.
2. Klõpsake nuppu Lisa viilutaja. Teil on oma andmete jaoks väljade loend. Valige nii palju kui soovite.
3. Selle näite jaoks valige Piirkond ja klõpsake nuppu OK.
Lisasite aruandesse Sliceri. Nüüd rakendage filter vaid ühe klõpsuga.
Sisestage ajaskaala Excelis 2016 ja 2013
See on üks minu lemmikfunktsioone Exceli liigendtabelitest. See uus funktsioon töötab ainult kuupäevadega. Seda kasutades saate visuaalselt valida ajavahemiku oma andmete filtreerimiseks.
Ajaskaala kursori sisestamiseks toimige järgmiselt.
Ajaskaala lisamine liigendtabelisse, Excel 2016 ja 2013
1. Klõpsake liigendtabeli suvalist kohta ja minge vahekaardile Analüüsi.
2. Klõpsake käsul Lisa ajaskaala filtrirühmast. Kõik veerud, mis sisaldavad lähteandmetes ajaväärtusi, kuvatakse valikukastis. Siin on meil ainult üks. Nii et jah…
2. Valige oma valikud ja vajutage sisestusklahvi või klõpsake nuppu OK. See on tehtud ja teie pöördtabeli ajaskaala on teie ees.
Saate valida, kas näidata seda iga päev, kord kuus, kord kvartalis või aastas. Valisin siin igakuise.
Selles artiklis olen käsitlenud Pivot Tabeli kõige olulisemaid ja kasulikke funktsioone. Uurisime Excelis 2016 ja 2013. aastal Pivot Tabeli uusi funktsioone. Saime teada Excel 2007, 2010 ja vanemates versioonides käivitatud pöördtabeli klassikalisest kasutamisest. Need on endiselt kasulikud. Kui te ei leidnud siin oma pöördtabeliga seotud vastust, küsige kommentaaride jaotises.
On palju muid funktsioone, mida tuleb veel selgitada. Järgmises artiklis õpime täpsemat liigendtabeli funktsiooni. Seni Excel kõik.