Pivot tabelitega töötamine Microsoft Excelis

PivotTables on üks Microsoft Exceli kõige võimsamaid omadusi. Need võimaldavad analüüsida ja koondada vaid mõne hiireklõpsuga suure hulga andmeid. Selles artiklis uurime PivotTables'i, mõistame, mis need on, ning õppida, kuidas neid luua ja kohandada.

Märkus: See artikkel on kirjutatud Exceli 2010 (beeta) abil. PivotTable'i kontseptsioon on aastate jooksul vähe muutunud, kuid selle loomise meetod on muutunud peaaegu igas Exceli iteratsioonis. Kui kasutate Exceli versiooni, mis ei ole 2010. aasta, oodake erinevaid kuvasid nendest, mida näete käesolevas artiklis.

Väike ajalugu

Arvutustabeli programmide esimestel päevadel valitses Lotus 1-2-3. Tema domineerimine oli nii täielik, et inimesed arvasid, et Microsoftil oli aega raisata oma arvutustabeli tarkvara (Excel) Lotusega konkureerimiseks. Flash-edasisuunamine 2010. aastani ja Exceli domineerimine arvutustabeli turul on suurem kui Lotusil kunagi olnud, samas kui Lotus 1-2-3-s töötavate kasutajate arv läheneb nullile. Kuidas see juhtus? Mis põhjustas varanduse dramaatilist ümberpööramist?

Tööstusanalüütikud panid selle kahele tegurile: Esiteks otsustas Lotus, et see uus uhke GUI platvorm, mida nimetatakse "Windows", oli mööduv moehullus, mis ei hakka kunagi startima. Nad keeldusid Lotus 1-2-3 Windowsi versiooni loomisest (mõne aasta pärast), ennustades, et nende tarkvara DOS versioon oli kõik keegi, keda kunagi vaja oleks. Microsoft on loomulikult arendanud Exceli ainult Windowsi jaoks. Teiseks töötas Microsoft välja Exceli funktsiooni, mida Lotus ei pakkunud 1-2-3-s, nimelt PivotTables.  Exceli eksklusiivne PivotTables'i funktsioon peeti nii hämmastavalt kasulikuks, et inimesed olid valmis õppima kogu uut tarkvarapaketti (Excel), mitte jääma programmi (1-2-3) juurde, mis seda ei olnud. See üks funktsioon koos Windowsi edu ebaõigest hindamisest oli Lotus 1-2-3 surmajuht ja Microsoft Exceli edu algus.

PivotTables'i mõistmine

Mis on täpselt PivotTable??

Lihtsamalt öeldes on PivotTable mõnede andmete kokkuvõte, mis on loodud nende andmete lihtsaks analüüsiks. Kuid erinevalt käsitsi koostatud kokkuvõttest on Excel PivotTables interaktiivsed. Kui olete selle loonud, saate seda hõlpsasti muuta, kui see ei anna täpset teavet oma andmete kohta, mida loodate. Paari klõpsuga võib kokkuvõtte „pöörata” - pöörata nii, et veerude pealkirjad muutuvad rea pealkirjadeks ja vastupidi. Samuti on palju rohkem, mida saab teha. Selle asemel, et püüda kirjeldada kõiki PivotTablesi omadusi, näitame neid lihtsalt ...

PivotTable'i abil analüüsitud andmed ei saa olla õiged kõik andmed - see peab olema toores varem töötlemata (mittekokkuvõetud) andmed - tavaliselt teatud liiki nimekiri. Selle näiteks võib olla äriühingu viimase kuue kuu müügitehingute loend.

Uurige alltoodud andmeid:

Pange tähele, et see on mitte algandmed. Tegelikult on see juba mingisugune kokkuvõte. Rakus B3 näeme 30 000 dollarit, mis ilmselt on James Cooki jaanuari müügi kogusumma. Niisiis, kus on toorandmed? Kuidas jõudsime 30 000 dollarini? Kus on algne müügitehingute loend, millest see arv on loodud? On selge, et kusagil peab keegi minema hädas, et kõik viimase kuue kuu müügitehingud koondati ülaltoodud kokkuvõttesse. Kui kaua te arvate, et see võttis? Tund? Kümme?

Tõenäoliselt jah. Näete, et ülaltoodud tabel on tegelikult mitte PivotTable. See loodi mujal talletatud toorandmetest käsitsi ja selle koostamiseks kulus paar tundi. Siiski on see just selline kokkuvõte võiks luuakse PivotTables'i abil, mille puhul oleks kulunud vaid paar sekundit. Uurime, kuidas ...

Kui me peaksime müügitehingute algse nimekirja jälgima, võib see tunduda sellisena:

Võite olla üllatunud, kui teadsite, et kasutades Exceli PivotTable funktsiooni, saame mõne sekundi jooksul luua ülaltoodule sarnase igakuise müügi kokkuvõtte, millel on vaid mõned hiireklõpsud. Me saame seda teha - ja palju muud!

Kuidas luua PivotTable

Esiteks veenduge, et teil oleks Excelis töölehel mõned toorandmed. Finantstehingute loend on tüüpiline, kuid see võib olla vaid peaaegu midagi: töötajate kontaktandmed, teie CD-kogumik või teie ettevõtte autopargi kütusekulu näitajad.

Nii et me alustame Exceli ... ja laadime sellise nimekirja ...

Kui meil on Excelis avatud, oleme valmis alustama PivotTable loomist.

Klõpsa loendis ühel üksikul lahtril:

Siis, alates Lisa klõpsake vahekaardil PivotTable ikoon:

The Loo PivotTable kuvatakse kaks küsimust: milliseid andmeid peaks teie uus PivotTable põhinema ja kust seda luua? Kuna me juba klõpsasime loendis olevasse lahtrisse (ülaltoodud sammus), on kogu selle lahtri ümbritsev loend juba valitud ($ A $ 1: $ G $ 88 kohta Maksed selles näites). Pange tähele, et me võime valida mõne muu töölehe mõne muu piirkonna või isegi mõne muu välise andmeallika, näiteks Access andmebaasi tabeli või isegi MS-SQL Serveri andmebaasi tabeli. Samuti peame valima, kas me soovime, et meie uus PivotTable oleks loodud a uus töölehele või olemasolev üks. Selles näites valime a uus üks:

Uus tööleht luuakse meile ja tühi PivotTable luuakse sellel töölehel:

Ilmub veel üks kast: PivotTable Field List.  See väljade loend kuvatakse alati, kui klõpsame ükskõik millises lahtris PivotTable (ülal):

Kasti ülemises osas olevate väljade loend on tegelikult esialgse toorandmete töölehe veergude pealkirjade kogum. Ekraani alumises osas olevad neli tühja kasti võimaldavad meil valida viisi, kuidas meie PivotTable toorandmeid kokku võtaks. Seni pole nendes kastides midagi, nii et PivotTable on tühi. Kõik, mida peame tegema, on ülaltoodud loendist välja tõmmata ja alamkastidesse rippida. Seejärel luuakse automaatselt PivotTable, et see vastaks meie juhistele. Kui saame selle valesti, peame ainult väljad lohistama tagasi sinna, kust nad tulid ja / või lohistasid uus väljade asendamiseks.

The Väärtused kast on vaieldamatult neljast kõige olulisem. Sellesse lahtrisse lohistatav väli näitab andmeid, mis tuleb mingil viisil kokku võtta (summeerides, keskmistades, maksimaalse, minimaalse jne leidmisel). See on peaaegu alati numbriline andmeid. Selle kasti täiuslik kandidaat meie valimisandmetes on väli / kogus. Lohistame selle välja Väärtused kast:

Pange tähele, et (a) väljal olevate väljade „Kogus” on nüüd märgistatud ja „Summa summa” on lisatud Väärtused kast, mis näitab, et summa veerg on summeeritud.

Kui me ise PivotTable'i uurime, leiame tõepoolest kõigi toorandmete töölehelt arvutatud väärtuste summa:

Oleme loonud oma esimese PivotTable! Käepärane, kuid mitte eriti muljetavaldav. On tõenäoline, et vajame veidi rohkem teavet meie andmetest.

Viidates meie prooviandmetele, peame tuvastama ühe või mitu veeru pealkirja, mida me võiksime kasutada selle summa jagamiseks. Näiteks võime otsustada, et tahaksime näha meie andmete kokkuvõtet, kus meil on rea pealkirja iga meie ettevõtte müüja kohta ja kogusumma iga ettevõtte kohta. Selle saavutamiseks on vaja ainult lohistada „Müüja“ välja Ridaetiketid kast:

Nüüd, lõpuks, asjad hakkavad huvituma! Meie PivotTable hakkab kuju võtma ... .

Paari klõpsuga oleme loonud tabeli, mis oleks võtnud käsitsi kaua aega.

Mida me veel teha saame? Noh, ühes mõttes on meie PivotTable valmis. Oleme loonud kasuliku kokkuvõtte meie lähteandmetest. Oluline asi on juba õppinud! Ülejäänud artikli jaoks uurime mõningaid viise, kuidas luua keerukamaid tabeleid, ja viise, kuidas neid PivotTablesi saab kohandada.

Esiteks saame luua a kaks-mõõtmete tabel. Tehkem seda kasutades "Makseviis" veeru pealkirjana. Lihtsalt lohistage makseviis pealkirjale Veeru sildid kast:

Mis näeb välja selline:

Alustades väga jahtuda!

Teeme selle a kolm-mõõtmete tabel. Milline võiks olla selline tabel? Noh, vaatame…

Lohistage veerg "Pakett" / Aruandefilter kast:

Pange tähele, kuhu see jõuab ... .

See võimaldab meil filtreerida meie aruande, mille alusel osteti puhkusepakett. Näiteks näeme müüja ja makseviisi jaotust kõik paketid, või paari klõpsuga muuta seda, et näidata sama jaotust „Sunseekers” paketi jaoks:

Ja nii, kui sa arvad seda õigesti, on meie PivotTable nüüd kolmemõõtmeline. Jätkame kohandamist ...

Kui selgub, et me tahame ainult näha tšekk ja krediitkaart tehingud (s.o sularahatehingud puuduvad), siis saame tühistada „Sularaha” kirje veergude pealkirjadest. Klõpsake rippmenüü kõrval Veeru sildid, ja tühista „Raha“:

Vaatame, mis see välja näeb ... Nagu näete, on „Raha” läinud.

Vorming

See on ilmselt väga võimas süsteem, kuid seni on tulemused väga lihtsad ja igavad. Kõigepealt ei tundu, et numbrid, mida me summeerime, ei näe dollari suurustena - lihtsalt tavalised numbrid. Parandame selle.

Kiusatuseks võib olla teha seda, mida oleme sellistel asjaoludel harjunud, ja lihtsalt vali kogu tabel (või kogu tööleht) ja kasutada vormindamise lõpuleviimiseks tööriistaribal olevaid standardnumbreid. Sellise lähenemise probleemiks on see, et kui sa tulevikus muudad PivotTable'i struktuuri (mis on tõenäoliselt 99%), siis need numbriformaadid kaovad. Me vajame viisi, mis muudab need pooleldi püsivaks.

Esmalt leiame kirje „Summa summa” Väärtused ja klõpsake seda. Ilmub menüü. Me valime Väärtusevälja seaded ... menüüst:

The Väärtusevälja seaded ilmub kast.

Klõpsake nuppu Numbrivorming nupp ja standard Vormindage lahtrid ilmub:

Alates Kategooria loendist valige (ütle) Raamatupidamine, ja kustutage kümnendkohtade arv väärtusele 0. Klõpsake Okei paar korda, et pöörduda tagasi PivotTable'i…

Nagu näete, on numbrid õigesti vormindatud dollarites.

Kuigi oleme vormindamise teemal, vormindame kogu PivotTable. Selleks on mõned viisid. Kasutame lihtsat ...

Klõpsake nuppu PivotTable tööriistad / disain vahekaart:

Seejärel lohistage nool alumise paremas servas PivotTable stiilid loend suurte sisseehitatud stiilide kogumi vaatamiseks:

Valige üks, mis kaebab, ja vaadake oma PivotTable'i tulemust:

Muud valikud

Me võime töötada ka kuupäevadega. Tavaliselt on tehingute nimekirjas palju, palju alguskuupäevi. Aga Excel pakub võimalust rühmitada andmeühikud kokku päeva, nädala, kuu, aasta jne järgi. Vaatame, kuidas seda tehakse.

Kõigepealt eemaldame „Maksemeetodi” veeru Veeru sildid kast (lihtsalt lohistage see väljale väljale) ja asendage veerus „Broneeritud kuupäev”:

Nagu näete, muudab see meie PivotTable'i kohe kasutuks, andes meile ühe veeru iga tehingu toimumise kuupäeva kohta - väga laia tabel!

Selle parandamiseks paremklõpsake suvalisel kuupäeval ja valige Grupp… kontekstimenüüst:

Kuvatakse grupeerimiskast. Me valime Kuud ja klõpsake nuppu OK:

Voila! A palju kasulikum tabel:

(Muide, see tabel on praktiliselt identne käesoleva artikli alguses näidatud algse müügi kokkuvõttega, mis loodi käsitsi.)

Veel üks lahe asi, mida peate teadma, on see, et teil võib olla rohkem kui üks rea rea ​​pealkirjad (või veergude pealkirjad):

… Mis näeb välja selline… .

Sarnast asja saate teha veergude pealkirjadega (või isegi filtreerida).

Jälgige, et asjad oleksid lihtsad, vaatame, kuidas joonistada keskmistatud väärtuste asemel.

Esmalt klõpsake „Summa summa” ja valige Väärtusevälja seaded ... kuvatavast kontekstimenüüst:

In Väärtuste väljade kokkuvõte loendis Väärtusevälja seaded ruut, valige Keskmine:

Kui me siin oleme, muutkem Kohandatud nimi, alates „Keskmine summa” kuni midagi veidi lühemat. Sisestage midagi „Avg”:

Klõpsake nuppu Okei, ja vaata, mis see välja näeb. Pange tähele, et kõik väärtused muutuvad summeeritud kogusummast keskmisteks ja tabeli pealkiri (ülemine vasakpoolne lahtrisse) on muutunud väärtuseks „Avg”:

Kui soovime, siis võib meil olla isegi summad, keskmised ja loendused (loeb = kui palju müüki seal oli) kõik sama PivotTable!

Siin on sammud, mis võimaldavad midagi sellist omada (alates tühjast PivotTable'ist):

1. Lohistage „Müüja“ sisse Veeru sildid
2. Lohistage välja „Kogus” alla Väärtused kolm korda
3. Esimese "Kogus" väljale muutke oma kohandatud nime "Kokku" ja selle numbrivorminguks Raamatupidamine (0 kohta pärast koma)
4. Teise „Kogus“ väljale muutke oma kohandatud nime “Keskmine”, selle funktsiooniks Keskmine ja selle numbrivorming Raamatupidamine (0 kohta pärast koma)
5. Kolmanda "Kogus" väljale muutke selle nimi "Count" ja selle funktsiooniks Krahv
6. Lohistage automaatselt loodud väljalt Veeru sildid kuni Ridaetiketid

Siin on, mida me lõpetame:

Kokku, keskmine ja sama PivotTable!

Järeldus

Microsoft Exceli loodud PivotTables'i jaoks on palju, palju rohkem funktsioone ja võimalusi - see on liiga palju, et seda sellises artiklis loetleda. PivotTables'i potentsiaali täielikuks katmiseks on vaja väikest raamatut (või suurt veebisaiti). Brave ja / või geeky lugejad saavad PivotTables'i veelgi lihtsamalt uurida. Lihtsalt paremklõpsake peaaegu kõigil ja vaadake, millised valikud on teile kättesaadavad. On ka kaks lint-vahekaarti: PivotTable tööriistad / valikud ja Disain.  Pole oluline, kui teete vea - PivotTable'i on lihtne kustutada ja uuesti alustada - võimalus, et Lotus 1-2-3 vanad DOS-i kasutajad pole kunagi olnud.

Kui töötate Office 2007-s, võiksite vaadata meie artiklit, kuidas luua PivotTable Excelis 2007.

Oleme lisanud Exceli töövihiku, mille saate alla laadida, et oma PivotTable'i oskusi harjutada. See peaks töötama kõigi Exceli versioonidega alates 97 aastast.

Laadige alla meie tava Exceli töövihik