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 kasti.
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 Loo avamiseks Pivot tabel valikukast.
Vajutage Alt -nuppu 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 ja nad ei loobu vanadest funktsioonidest (nagu MS tegi võiduga 8. See oli haletsusväärne). See võimaldab vanemal kasutajal uute versioonidega normaalselt töötada, nagu nad töötasid vanemate versioonidega.
Kui vajutate järjestikku ALT, D ja P klaviatuuril avaneb Excel, et luua pöördtabeli 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 (Ilmselgelt)
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*ühikuhind).
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 Üksus (valige ükskõik milline veerg, veenduge lihtsalt, et selle tühikute vahel poleks tühja lahtrit) väärtusväljadel.
Valige Üksus väljade loendist ja lohistage see väljale Väärtus.
Meil on oma vastus. Pivot tabelid ütlevad mulle, et kokku on 43 tellimust. See on õige.
Pro näpunäide: Peaksite oma andmeid kontrollima, kas need on õiged või mitte. Kui see number ei ühti andmetega, tähendab see, et olete valinud mõne vale vahemiku või sellel väljal on tühjad lahtrid.
Info: Väärtusväli loeb vaikimisi veeru kirjete arvu, kui see sisaldab teksti, ja summeerib, kui väli sisaldab ainult väärtusi. Saate seda muuta väärtusvälja seadetes. Kuidas? Näeme hiljem selles Pivot Tabeli õpetuses.
Nüüd, kui olen valinud Tellimiskuupäev Valuesareas 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 Keskel 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.
INFO: Olenemata sellest, milliseid muudatusi oma lähteandmetes teete, töötab liigendtabel vanade andmetega edasi kuni seda värskendate. Excel loob pöördvahemälu ja sellel töötab vahemälu. Värskendamisel muudetakse vana vahemälu värskete andmetega.
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 oma pöördtabelis suvalisel kohal.
2. Minge Disain Tab.
3. Klõpsake menüüd Vahesummad.
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 valik. 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 üksustest veerud.
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…
- Paremklõpsake liigendtabeli veeru Ühikukulu summat suvalisel kohal
- Klõpsake nuppu Väärtusvälja seaded
- Olemasolevate valikute põhjal vali 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 Pivot Tabeli suvalist kohta ja minge lehele Analüüsi tab
2. Klõpsake nuppu Väljad, üksused ja komplektid arvutusrühmas.
3. Klõpsake nuppu Arvutatud väljad.
Näete seda arvutusvälja sisestuskasti:
4. Kirjutage nime sisestuskasti keskmine Maksumus või mis iganes sulle meeldib, excelil pole selle vastu midagi. Aastal valem sisestuskast, kirjutage ja vajutage Okei.
= Kokku / ühikut
Saate selle kirjutada klaviatuurilt käsitsi või toimingute tegemiseks topeltklõpsata väljade alal loetletud väljade nimedel.