Sisu
Pivot lauad on Exceli üks võimsamaid tööriistu. Need võimaldavad teil vaid mõne hiireklõpsuga analüüsida ja summeerida erinevaid kokkuvõtteid suurtest andmemahtudest. Selles artiklis tutvume pivot-tabelitega, mõistame, mis need on, õpime neid looma ja kohandama.
See artikkel on kirjutatud Excel 2010 abil. PivotTable-liigendtabelite kontseptsioon ei ole aastate jooksul palju muutunud, kuid nende loomise viis on igas uues Exceli versioonis veidi erinev. Kui teil on Exceli versioon, mitte 2010, siis olge valmis selleks, et selles artiklis olevad ekraanipildid erinevad ekraanil nähtavatest.
Veidi ajalugu
Arvutustabelitarkvara algusaegadel oli Lotus 1-2-3 reeglipall. Selle domineerimine oli nii täielik, et Microsofti jõupingutused oma tarkvara (Excel) väljatöötamiseks Lotuse alternatiivina tundusid ajaraiskamisena. Nüüd kiirelt edasi 2010. aastasse! Excel domineerib arvutustabelite üle rohkem kui Lotuse kood oma ajaloos kunagi teinud on ja Lotust endiselt kasutavate inimeste arv on nullilähedane. Kuidas see juhtuda sai? Mis oli sündmuste sellise dramaatilise pöörde põhjus?
Analüütikud tuvastavad kaks peamist tegurit:
- Esiteks otsustas Lotus, et see uudne GUI platvorm nimega Windows on lihtsalt mööduv moeröögatus, mis ei kesta kaua. Nad keeldusid koostamast Lotus 1-2-3 Windowsi versiooni (kuid ainult mõneks aastaks), ennustades, et nende tarkvara DOS-versioon on kõik, mida tarbijad kunagi vajavad. Microsoft arendas Exceli loomulikult spetsiaalselt Windowsi jaoks.
- Teiseks tutvustas Microsoft Excelis tööriista PivotTables, mis ei olnud Lotus 1-2-3 jaoks saadaval. Excelile eksklusiivsed PivotTable-liigendtabelid osutusid nii valdavalt kasulikuks, et inimesed kaldusid pigem kinni pidama uuest Exceli tarkvarakomplektist, mitte jätkama Lotus 1-2-3-ga, millel neid polnud.
PivotTable-liigendtabelid koos Windowsi üldise edu alahindamisega mängisid Lotus 1-2-3 surmamarssi ja juhatasid sisse Microsoft Exceli edu.
Mis on pivot-tabelid?
Niisiis, milline on parim viis PivotTable-liigendtabelite iseloomustamiseks?
Lihtsamalt öeldes on pöördetabelid teatud andmete kokkuvõtted, mis on loodud nende andmete analüüsi hõlbustamiseks. Erinevalt käsitsi loodud kogusummadest on Exceli PivotTable-liigendtabelid interaktiivsed. Kui need on loodud, saate neid hõlpsalt muuta, kui need ei anna soovitud pilti. Vaid paari hiireklõpsuga saab kogusummasid pöörata nii, et veergude pealkirjad muutuvad reapealkirjadeks ja vastupidi. Pivot-tabelitega saate teha palju asju. Selle asemel, et püüda sõnadega kirjeldada kõiki pivot-tabelite funktsioone, on lihtsam seda praktikas demonstreerida ...
PivotTable-liigendtabelitega analüüsitavad andmed ei saa olla juhuslikud. See peaks olema töötlemata algandmed, nagu mingi loend. Näiteks võib see olla ettevõtte viimase kuue kuu müügiloend.
Vaadake alloleval joonisel näidatud andmeid:
Pange tähele, et tegemist ei ole toorandmetega, kuna need on juba kokku võetud. Lahtris B3 näeme 30000 30000 dollarit, mis on tõenäoliselt James Cooki jaanuaris saavutatud kogutulemus. Kus on siis algandmed? Kust tuli see XNUMX XNUMX dollari suurus? Kus on algne müügiloend, millest see kuu kogusumma tuletati? On selge, et keegi on teinud suurepärast tööd, korraldades ja sorteerides kõik viimase kuue kuu müügiandmed ning muutnud need meie poolt nähtavaks kogusummade tabeliks. Kui kaua see teie arvates aega võttis? Tund? Kell kümme?
Fakt on see, et ülaltoodud tabel ei ole liigendtabel. See valmistati käsitsi mujal salvestatud toorandmetest ja selle töötlemiseks kulus vähemalt paar tundi. Just sellise kokkuvõtliku tabeli saab pivot-tabelite abil luua vaid mõne sekundiga. Mõtleme välja, kuidas…
Kui läheme tagasi esialgse müüginimekirja juurde, näeks see välja umbes selline:
Võite olla üllatunud, et sellest tehingute loendist saame pivot-tabelite abil ja vaid mõne sekundiga koostada Excelis igakuise müügiaruande, mida eelpool analüüsisime. Jah, me saame sellega hakkama ja rohkemgi veel!
Kuidas luua liigendtabelit?
Esiteks veenduge, et teil on Exceli lehel mõned lähteandmed. Finantstehingute loend on kõige tüüpilisem, mis juhtub. Tegelikult võib see olla nimekiri millest iganes: töötajate kontaktandmed, CD-kogu või teie ettevõtte kütusekulu andmed.
Niisiis, käivitame Exceli ... ja laadime sellise loendi ...
Kui oleme selle loendi Excelis avanud, saame alustada pivot-tabeli loomist.
Valige loendist mis tahes lahter:
Siis vahekaardil sisestamine (Sisesta) valige käsk PivotTable (Pivot tabel):
Ilmub dialoogiboks Loo PivotTable (Pöördtabeli loomine) kahe küsimusega:
- Milliseid andmeid uue liigendtabeli loomiseks kasutada?
- Kuhu pöördtabel panna?
Kuna eelmises etapis oleme juba valinud ühe loendi lahtritest, valitakse pivot-tabeli loomiseks automaatselt kogu loend. Pange tähele, et saame valida erineva vahemiku, erineva tabeli ja isegi mõne välise andmeallika, näiteks Accessi või MS-SQL-i andmebaasitabeli. Lisaks peame valima, kuhu uus pöördetabel paigutada: uuele lehele või mõnele olemasolevale. Selles näites valime valiku – Uus tööleht (uuele lehele):
Excel loob uue lehe ja asetab sellele tühja liigendtabeli:
Niipea, kui klõpsame liigendtabelis mis tahes lahtril, ilmub uus dialoogiboks: PivotTable-liigendtabeli väljade loend (Pivot tabeli väljad).
Dialoogiboksi ülaosas olev väljade loend on kõigi esialgse loendi pealkirjade loend. Neli tühja ala ekraani allosas võimaldavad teil PivotTable-liigendtabelile öelda, kuidas soovite andmed kokku võtta. Kuni need alad on tühjad, pole ka tabelis midagi. Peame vaid lohistama pealkirjad ülevalt alalt allolevatele tühjadele aladele. Samal ajal luuakse meie juhiste kohaselt automaatselt pivot-tabel. Kui teeme vea, saame pealkirjad alumiselt alalt eemaldada või lohistada need asemele teised.
Piirkond Väärtused (Tähendused) on neist neljast ilmselt kõige olulisem. See, milline pealkiri sellele alale paigutatakse, määrab, millised andmed summeeritakse (summa, keskmine, maksimum, miinimum jne) Need on peaaegu alati arvväärtused. Suurepärane kandidaat sellele alale on pealkirja all olevad andmed summa (kulu) meie algse tabeli kohta. Lohistage see pealkiri alale Väärtused (Väärtused):
Pange tähele, et pealkiri summa on nüüd märgitud linnukesega ja piirkonnas Väärtused (Väärtused) ilmus kirje Summa summa (Summa väli Summa), mis näitab, et veerg summa kokkuvõtvalt.
Kui vaatame pivot-tabelit ennast, näeme veeru kõigi väärtuste summat summa originaal laud.
Niisiis, meie esimene pivot-tabel on loodud! Mugav, kuid mitte eriti muljetavaldav. Tõenäoliselt tahame oma andmete kohta saada rohkem teavet, kui meil praegu on.
Pöördume algandmete poole ja proovime tuvastada ühe või mitu veergu, mida saab kasutada selle summa jagamiseks. Näiteks saame oma pivot-tabeli koostada nii, et müügi kogusumma arvutatakse iga müüja kohta eraldi. Need. meie pivot-tabelisse lisatakse read, kus on kirjas iga ettevõtte müüja nimi ja kogu müügisumma. Selle tulemuse saavutamiseks lohistage pealkiri müügiinimene (müügiesindaja) piirkonda Rea sildid (Stringid):
See muutub huvitavamaks! Meie PivotTable-liigendtabel hakkab kuju võtma…
Kas näete eeliseid? Paari klõpsuga lõime tabeli, mille käsitsi loomine oleks võtnud väga kaua aega.
Mida me veel teha saame? Noh, mõnes mõttes on meie pivot tabel valmis. Oleme koostanud algandmetest kasuliku kokkuvõtte. Oluline info juba kätte saadud! Selle artikli ülejäänud osas vaatleme mõningaid viise keerukamate PivotTable-liigendtabelite loomiseks ja õpime neid kohandama.
PivotTable-liigendtabeli seadistamine
Esiteks saame luua kahemõõtmelise pöördetabeli. Teeme seda veeru pealkirja abil Makseviis (Makseviis). Lihtsalt lohistage pealkiri Makseviis piirkonda Veergude sildid (Veerud):
Saame tulemuse:
Väga lahe näeb välja!
Nüüd teeme kolmemõõtmelise tabeli. Kuidas selline laud välja näeks? Vaatame…
Lohistage päis Pakend (Kompleks) piirkonda aruannete filtrid (Filtrid):
Pange tähele, kus ta on…
See annab meile võimaluse filtreerida aruannet „Millise puhkekompleksi eest tasuti” alusel. Näiteks näeme kõigi komplekside jaotust müüjate ja makseviiside lõikes või paari hiireklõpsuga muuta pivot-tabeli vaadet ja kuvada sama jaotust ainult kompleksi tellijatele. Päikeseotsijad.
Seega, kui saate sellest õigesti aru, võib meie pivot-tabelit nimetada kolmemõõtmeliseks. Jätkame seadistamist…
Kui äkki selgub, et pivot-tabelis tuleks kuvada ainult tšeki ja krediitkaardiga maksmine (st sularahata makse), siis saame pealkirja kuvamise välja lülitada Raha (Sularaha). Selleks, kõrval Veergude sildid klõpsake allanoolt ja tühjendage rippmenüüs ruut Raha:
Vaatame, kuidas meie pivot-tabel praegu välja näeb. Nagu näete, veerg Raha kadus tema juurest.
PivotTable-liigendtabelite vormindamine Excelis
PivotTable-liigendtabelid on ilmselgelt väga võimas tööriist, kuid siiani tunduvad tulemused veidi lihtsad ja igavad. Näiteks meie liidetavad numbrid ei näe välja nagu dollarites summad – need on lihtsalt numbrid. Teeme selle korda.
Sellises olukorras on kiusatus teha seda, millega olete harjunud, ja lihtsalt valida kogu tabel (või kogu leht) ja kasutada soovitud vormingu määramiseks tööriistaribal standardseid numbrivormingu nuppe. Selle lähenemisviisi probleem seisneb selles, et kui muudate kunagi tulevikus pivot-tabeli struktuuri (mis juhtub 99% tõenäosusega), läheb vorming kaotsi. Vajame viisi selle (peaaegu) püsivaks muutmiseks.
Esiteks leiame kirje Summa summa in Väärtused (Väärtused) ja klõpsake sellel. Valige kuvatavas menüüs üksus Väärtuse välja seaded (Väärtuse välja valikud):
Ilmub dialoogiboks Väärtuse välja seaded (Väärtusvälja valikud).
press number Format (Numbrivorming), avaneb dialoogiboks. Format Cells (lahtri vorming):
Nimekirjast Kategooria (Numbrivormingud) valige raamatupidamine (Finants) ja määrake komakohtade arv nulliks. Nüüd vajuta paar korda OKet minna tagasi meie liigendtabeli juurde.
Nagu näete, on numbrid vormindatud dollaritena.
Vormindamisega tegeledes seadistame kogu PivotTable-liigendtabeli vormingu. Selleks on mitu võimalust. Kasutame lihtsamat...
kliki PivotTable-liigendtabeli tööriistad: kujundus (PivotTable-liigendtabelitega töötamine: konstruktor):
Järgmisena laiendage menüüd, klõpsates jaotise paremas alanurgas oleval noolel PivotTable-liigendtabeli stiilid (PivotTable Styles), et näha ulatuslikku tekstisiseste stiilide kogu.
Valige sobiv stiil ja vaadake tulemust oma pivot-tabelis:
Muud Exceli PivotTable-liigendtabeli sätted
Mõnikord peate andmeid filtreerima kuupäevade järgi. Näiteks meie tehingute loendis on palju kuupäevi. Excel pakub tööriista andmete rühmitamiseks päeva, kuu, aasta jne järgi. Vaatame, kuidas see tehtud on.
Esmalt eemaldage kirje. Makseviis piirkonnast Veergude sildid (Veerud). Selleks lohistage see tagasi pealkirjade loendisse ja teisaldage selle asemel pealkiri Broneeritud kuupäev (broneerimise kuupäev):
Nagu näete, muutis see meie pivot-tabeli ajutiselt kasutuks. Excel lõi iga tehingu tegemise kuupäeva jaoks eraldi veeru. Selle tulemusena saime väga laia laua!
Selle parandamiseks paremklõpsake mis tahes kuupäeval ja valige kontekstimenüüst GROUP (Grupp):
Ilmub rühmitamise dialoogiboks. Me valime Kuud (kuud) ja klõpsake OK:
Voila! See tabel on palju kasulikum:
Muide, see tabel on peaaegu identne artikli alguses näidatud tabeliga, kus müügisummad koostati käsitsi.
On veel üks väga oluline punkt, mida peate teadma! Saate luua mitte ühe, vaid mitmel tasemel rea (või veeru) pealkirja:
… ja see näeb välja selline …
Sama saab teha veergude pealkirjadega (või isegi filtritega).
Pöördume tagasi tabeli algkuju juurde ja vaatame, kuidas kuvada summade asemel keskmisi.
Alustamiseks klõpsake nuppu Summa summa ja valige kuvatavast menüüst Väärtuse välja seaded (Väärtuse välja valikud):
Nimekiri Väärtuse välja kokkuvõte (Toiming) dialoogiboksis Väärtuse välja seaded (Väärtuse välja valikud) valige Keskmine (Keskmine):
Samas, kui me siin oleme, siis muutugem KohandatudNimi (Kohandatud nimi) koos Keskmine summa (Välja Summa Summa) millekski lühemaks. Sisestage sellele väljale midagi sarnast Keskm:
press OK ja vaata, mis juhtub. Pange tähele, et kõik väärtused on muutunud summadest keskmisteks ja tabeli päis (ülemises vasakpoolses lahtris) on muutunud väärtuseks Keskm:
Soovi korral saad koheselt ühte pivot tabelisse paigutatud summa, keskmise ja arvu (müügi).
Siin on samm-sammult juhis selle kohta, kuidas seda teha, alustades tühja pivot-tabeliga.
- Lohistage päis müügiinimene (müügiesindaja) piirkonda Veergude sildid (Veerud).
- Lohistage pealkirja kolm korda summa (Kulu) piirkonnale Väärtused (Väärtused).
- Esimesele väljale summa muuda pealkirjaks Summa (Summa) ja numbrivorming sellel väljal on raamatupidamine (Rahaline). Kümnendkohtade arv on null.
- Teine väli summa nimi Keskminee, määrake selle jaoks toiming Keskmine (Keskmine) ja numbrivorming sellel väljal muutuvad samuti väärtuseks raamatupidamine (Finants) null komakohaga.
- Kolmanda välja jaoks summa määrake pealkiri Loendama ja operatsioon talle – Loendama (kogus)
- aasta Veergude sildid (Veerud) väli luuakse automaatselt Σ Väärtused (Σ Väärtused) – lohistage see piirkonda Rea sildid (Read)
Siin on see, millega me lõpuks jõuame:
Kogusumma, keskmine väärtus ja müükide arv – kõik ühes pivot-tabelis!
Järeldus
Microsoft Exceli pivot-tabelid sisaldavad palju funktsioone ja sätteid. Nii väikeses artiklis ei ole nad isegi lähedal, et neid kõiki hõlmata. Pivot-tabelite kõigi võimaluste täielikuks kirjeldamiseks kuluks väike raamat või suur veebisait. Julged ja uudishimulikud lugejad saavad jätkata pivot-tabelite uurimist. Selleks tehke lihtsalt paremklõps peaaegu igal pivot-tabeli elemendil ja vaadake, millised funktsioonid ja sätted avanevad. Lindilt leiate kaks vahekaarti: PivotTable-liigendtabeli tööriistad: Valikud (analüüs) ja Disain (Ehitaja). Ärge kartke teha viga, saate PivotTable-liigendtabeli alati kustutada ja otsast alustada. Teil on võimalus, mida kauaaegsetel DOS-i ja Lotus 1-2-3 kasutajatel pole kunagi olnud.