PivotTables on Microsoft Exceli üks võimsamaid funktsioone. Need võimaldavad suurel hulgal andmeid analüüsida ja kokku võtta vaid mõne hiireklõpsuga. Selles artiklis uurime PivotTables'i, mõista, mis neil on, ja õpime nende loomist ja kohandamist.
Märkus. See artikkel on kirjutatud Excel 2010 (beeta) abil. PivotTable'i mõiste on aastate jooksul vähe muutunud, kuid selle loomise meetod on muutunud peaaegu igal Exceli iteratsioonil. Kui kasutate Exceli versiooni, mis ei kuulu 2010. aastasse, oodake teisi ekraane sellistest artiklitest, mida näete.
Väike ajalugu
Arvutustabeli programmide esimestel päevadel otsustas loto 1-2-3. Tema valitsev seisund oli nii täielik, et inimesed arvasid, et Microsoft oli aja raiskamine, et Microsoft Lotus'iga võistelda hakata oma arvutustabeli tarkvara (Excel) arendama. Välk edasi 2010. aastaks ja Exceli tabelarvuturu turgu valitsev seisund on suurem kui Lotus'il kunagi olnud, samas kui Lotus 1-2-3 kasutavate kasutajate arv läheneb nullini. Kuidas see juhtus? Mis põhjustas sellise dramaatilise õnnestumise ümberpööramise?
Tööstuse analüütikud panid selle alla kaks tegurit: esiteks Lotus otsustas, et see uhke uus GUI-platvorm nimega "Windows" oli möödasõit, mis ei võtaks kunagi ära. Nad loobusid looma Windowsi versiooni Lotus 1-2-3 (mõne aasta jooksul niikuinii), ennustades, et nende DOS-i versioon on kõik, mida iganes vajab. Microsoft loomulikult arendas Exceli välja ainult Windowsi jaoks. Teiseks arendas Microsoft välja Exceli funktsiooni, mida Lotus ei esitanud 1-2-3-s, nimelt PivotTables. Exceli eksklusiivsele funktsioonile PivotTables peeti nii hämmastavalt kasulikuks, et inimesed oleksid valmis õppima terve uue tarkvarapaketti (Exceli), mitte jääma programmi (1-2-3), millel seda ei olnud. See üks omadus koos Windowsi edukuse eksliku hindamisega oli Lotus 1-2-3 surmajuht ja Microsoft Exceli edu algus.
PivotTablesi mõistmine
Mis on PivotTable, täpselt?
Lihtsalt öeldes on PivotTable kokkuvõte mõnedest andmetest, mis on loodud nende andmete hõlpsaks analüüsimiseks. Kuid erinevalt käsitsi koostatud kokkuvõttest on Excel PivotTables interaktiivsed. Kui olete selle loonud, saate seda hõlpsasti muuta, kui see ei paku täpset ülevaadet teie soovitud andmetest. Mõne kliki korral saab kokkuvõtet "pöörata" - pöörata nii, et veergude pealkirjad muutuksid rea pealkirjad ja vastupidi. Seda saab teha ka palju rohkem. Selle asemel, et proovida kirjeldada kõiki PivotTablesi omadusi, näitame neid lihtsalt …
Andmed, mida te analüüsite PivotTable'i abil, ei saa lihtsalt olla kõik andmed - see peab olema toores andmed, varem töötlemata (tühistamata) - tavaliselt mõnevõrra nimekiri. Selle näide võib olla viimase kuue kuu jooksul ettevõtte müügi tehingute loend.
Uurige allpool toodud andmeid:
Pange tähele, et see on mitte algandmed. Tegelikult on see juba mingi kokkuvõte. Lahtris B3 näeme 30 000 dollarit, mis ilmselt on James Cooki müügi kogumaht jaanuari kuul. Nii et kus on toorandmed? Kuidas jõudsime 30 000 dollarini? Kus on esialgne müügitehingute loend, mille alusel see näitaja loodi? On selge, et kusagil peaks keegi olema läinud probleemile, et võrrelda kõiki viimase kuue kuu jooksul toimunud müügitehinguid ülalolevas kokkuvõttes. Kui kaua te arvate, et see läks? Tund? Kümme?
Tõenäoliselt jah. Näete, ülaltoodud arvutustabel on tegelikult mitte PivotTable. See loodi käsitsi mujal salvestatud toorandmetest ja kompileerimiseks kulus paar tundi. Kuid see on just selline kokkuvõte võiks tuleb luua kasutades PivotTables, mille puhul see oleks võtnud vaid paar sekundit. Vaatame, kuidas …
Kui peaksime müügi tehingute esialgse loendi välja selgitama, võib see kujutada endast midagi sellist:
Kuidas luua pivotTable
Esiteks veenduge, et Excelis töölehel on mõni toorandmeid. Finantstehingute nimekiri on tüüpiline, kuid see võib sisaldada peaaegu kõike: töövõtja kontaktandmed, teie CD kogumik või kütusekulu arvud teie ettevõtte autopargi kohta.
Nii käivitame Excel … ja laadime sellist nimekirja …
Kliki ükskõik millisel ükskõik millisel loendis:
Siis, alates Sisesta vahekaardil klõpsake nuppu PivotTable ikoon:
The Loo PivotTable Ilmub kiri, milles küsitakse teilt kaks küsimust: milliseid andmeid peaks teie uus pivotTable aluseks andma ja kus peaks see olema loodud? Kuna me juba klikkinud loendi lahtris (ülaltoodud etapis), on kogu selle lahtri ümbritsev nimekiri meile juba valitud ($ A $ 1: $ G $ 88 kohta Maksed leht, käesolevas näites). Pange tähele, et võime valida nimekirja mis tahes muu töölaua mõnest muust piirkonnast või isegi mõnest välist andmeallikast, näiteks Access andmebaasi tabelist või isegi MS-SQL Serveri andmebaasi tabelist. Samuti peame valima, kas soovime, et meie uus pivotTable luuakse veebisaidil uus tööleht või olemasolevad üks. Selles näites valime a uus üks:
Veel üks kastike: The PivotTable väli nimekiri. See väliloend kuvatakse iga kord, kui klõpsame PivotTable'i (ülalt) mis tahes raamistikus:
Lahtri ülemise osa väljad on tõepoolest esialgse toorandmebaasi veerupäiste kogum. Ekraani alumises osas asuvad neli tühja kast võimaldab meil valida, kuidas meie PivotTable toorandmeid kokku võtta. Siiani pole nendes kastides midagi, seega on PivotTable tühi. Kõik, mida me peame tegema, on lohistage ülaltoodud loendist välja ja jätke need alumises kastis. Seejärel luuakse automaatselt pivotTable, mis vastab meie juhistele. Kui me saame vale, peame lihtsalt lohistama väljad tagasi, kust need tulid ja / või lohista uus välja nende asendamiseks.
The Väärtused kast on vaieldamatult kõige tähtsam neljast. Sellesse kasti kantud väli kujutab endast andmeid, mida tuleb mingil viisil kokku võtta (summeerides, keskmiselt, maksimaalse, minimaalse jne leidmisel). See on peaaegu alati numbriline andmed. Selle valimi andmete täpne kandidaat on väljale / veergu "Summa". Lohistage selle välja väravasse Väärtused kast:
Pange tähele, et a) lauku loendis on väljale "Kogus" märgitud ja "Summa summa" on lisatud Väärtused kasti, mis näitab, et summa veerg on summeeritud.
Kui vaatame PivotTable ise, leiame tõepoolest toorandmete töölehel kõigi väärtuste "Summa" summa kokku:
Viidates meie proovivõtmise andmetele, peame tuvastama ühe või mitu veergude pealkirja, mida me võiksime seda kogust jaotada. Näiteks võime otsustada, et tahaksime näha meie andmete kohta kokkuvõtet, kus meil on rea pealkiri iga meie ettevõtte erinevate müüjate kohta ja kokku igaüks. Selle saavutamiseks peame lihtsalt lohistama väljale Müügimees Rida sildid kast:
Nüüdlõpuks asjad hakkavad huvituma! Meie PivotTable hakkab kujundama …
Mida me veel teeme? Noh, ühel juhul on meie PivotTable täielik. Oleme loonud meie lähteandmete kasuliku kokkuvõtte. Olulised asjad on juba õppinud! Ülejäänud artiklis uurime mõnda moodust, kuidas luua keerukamaid PivotTables'i ja võimalusi, kuidas neid PivotTablesid saab kohandada.
Esiteks, me saame luua a kahe-kolmemõõtmeline tabel. Teeme seda, kasutades veergu "Makseviis". Lihtsalt lohista pealkiri "Makseviis" Veergud kast:
Hakkab saama väga lahe!
Teeme seda a kolm-mõõtmeline laud. Mis võiks selline tabel ilmselt välja näeb? Noh, vaatame …
Lohistage veerus / paketis "Pakett" Teata filtreerimisest kast:
See võimaldab meil filtreerida meie aruande, mille alusel osteti puhkusepakett. Näiteks näeme müügiesindaja ja makseviisi jagunemist kõik paketid või paari hiireklõpsuga muuta seda, et näidata sama jaotust paketi "Sunseekers" jaoks:
Kui selgub, ütle, et me tahame ainult näha kontrollige ja krediitkaarti tehinguid (st sularahatehinguid ei ole), siis võime tühjendada tulude rubriikide kaupa "Raha". Klõpsake kõrval asuvat rippmenüüd Veergud, ja untick "Cash":
Vormindamine
See on ilmselt väga võimas süsteem, kuid siiani on tulemused väga lihtsad ja igav. Alguses olevad numbrid ei paista dollareid - lihtsalt vanad numbrid. Parandame selle.
Kiusatus võib olla sellistes olukordades toimingute tegemine, nagu lihtsalt kogu tabel (või kogu tööleht), ja vorminduse lõpuleviimiseks kasutada tööriistariba standardarvude vormindamise nuppe. Sellise lähenemisviisi probleemiks on see, et kui te muudate PivotTable'i struktuuri tulevikus (mis on tõenäoliselt 99%), siis need numbriformaadid kaovad. Me vajame nii, et need (pool) püsiksid.
Esiteks leiame kirje "Summa Summa" Väärtused kast ja klõpsake seda. Ilmub menüü. Valime Value Field Settings … menüüst:
The Väärtuseribade seaded ilmub kast.
Klõpsake nupul Numbrivorming nupp ja standard Lahtrite vormindamine lahtrisse ilmub:
Alates Kategooria loe, vali (ütle) Raamatupidamineja tühjendage kümnendkoha arv 0. Klõpsake Okei paar korda, et naasta PivotTableile …
Kuigi oleme vormindamise teemal, vormindame kogu PivotTable'i. Selleks on mõned võimalused. Kasutage lihtsat …
Klõpsake nupul PivotTable Tools / Design vahekaart:
Seejärel lohistage noolt allapoole paremale PivotTable stiilid loend, et näha laia valikut sisseehitatud stiilide kogumit:
Muud valikud
Me võime töötada ka kuupäevadega. Nüüd on tavaliselt tehingute loendis palju, palju kuupäevi, nagu me alustasime. Kuid Excel pakub võimalust grupeerida andmeühikud päeva, nädala, kuu, aasta jne järgi. Vaatame, kuidas see on tehtud.
Kõigepealt eemaldame veerust "Makseviis" Veergud kasti (lihtsalt lohistage see tagasi välja nimekirja) ja asendage see veergu "Kuupäev broneeritud":
Selle parandamiseks klõpsake mis tahes kuupäeval paremklõpsuga ja valige Grupp… kontekstimenüüst:
Ilmub rühmituskast. Valime Kuud ja klõpsake OK:
Voila! A. palju kasulikum tabel:
Veel üks asi, mida tuleb teadlik olla, on see, et teil on rohkem kui üks rida päiseid (või veerupäiseid):
Jätkates asju lihtsalt uuesti, vaatame, kuidas proovitükki teha keskmiselt väärtused, mitte kokku väärtused.
Kõigepealt klõpsake "Summa summa" ja valige Value Field Settings … ilmuvast kontekstimenüüst:
Aastal Summeerige väärtuse väli poolt loendis Väärtuseribade seaded kast, valige Keskmine:
Kuigi me oleme siin, muudame seda Kohandatud nimi, alates "keskmisest summast" kuni midagi veidi lühemat. Sisestage midagi sellist nagu "Avg":
Klõpsake Okeija vaata, mis see välja näeb. Pange tähele, et kõik väärtused muutuvad kokku summadelt keskmisteks ja tabeli pealkiri (vasakpoolne vasakpoolne lahtris) on muudetud väärtuseks "Avg":
Siin on sammud, et saada midagi sellist, mis on paigas (alates tühjast pivotTableist):
- Drag "Müügimees" sisse Veergud
- Lohistage väli "Kogus" alla Väärtused kasti kolm korda
- Esimeses väljale "Kogus" muutke oma kohandatud nimeks "Kokku" ja selle numbrivorminguks Raamatupidamine (Kümnendkoha täpsusega)
- Teise arvu "Kogus" väljal muutke oma kohandatud nime väärtuseks "Keskmine", selle funktsioon on Keskmine ja see on numbrivorming Raamatupidamine (Kümnendkoha täpsusega)
- Kolmanda väljal "Kogus" muutke oma nimeks "Count" ja selle funktsiooniks Loendama
-
Lohistage automaatselt loodud
Image välja alates Veergud et Rida sildid
Siin on, mida me lõpuks:
Järeldus
Microsoft Excelis loodud pivottabeleid on palju, palju rohkem funktsioone ja valikuid - liiga palju sellisesse artiklis olevatest loenditest. PivotTables potentsiaali täielikuks katmiseks on vaja väikest raamatut (või suurt veebisaiti). Vaprad ja / või kallid lugejad saavad PivotTablesi veelgi lihtsamalt uurida: lihtsalt paremklõpsake peaaegu kõike ja vaadake, millised võimalused teile kättesaadavaks muutuvad. Samuti on olemas kaks lint-vahelehte: PivotTable Tools / Valikud ja Disain. Pole tähtis, kas teete viga - PivotTable on lihtne kustutada ja uuesti käivitada - see ei võimaldanud Lotus 1-2-3 vanematele DOS-i kasutajatele kunagi olnud.
Kui töötate Office 2007-is, võiksite vaadata meie artiklit, kuidas luua PivotTable Excel 2007-s.
Oleme lisanud Exceli töövihiku, mida saate alla laadida, et oma PivotTable'i oskusi praktiseerida. See peaks töötama kõigi Excel'i versioonidega alates 97-st.
Laadige alla meie praktika Excel töövihik
