Efnisyfirlit
Pivot borðum er eitt af öflugustu verkfærunum í Excel. Þeir gera þér kleift að greina og draga saman ýmsar samantektir á miklu magni gagna með örfáum músarsmellum. Í þessari grein munum við kynnast snúningstöflum, skilja hvað þær eru, læra hvernig á að búa til og sérsníða þær.
Þessi grein var skrifuð með Excel 2010. Hugmyndin um PivotTables hefur ekki breyst mikið í gegnum árin, en hvernig þú býrð þær til er aðeins öðruvísi í hverri nýrri útgáfu af Excel. Ef þú ert með útgáfu af Excel ekki 2010, þá vertu viðbúinn því að skjámyndirnar í þessari grein séu frábrugðnar því sem þú sérð á skjánum þínum.
A hluti af sögu
Í árdaga töflureiknahugbúnaðar var Lotus 1-2-3 regluboltinn. Yfirburðir þess voru svo algjörir að tilraunir Microsoft til að þróa eigin hugbúnað (Excel) sem valkost við Lotus virtust tímasóun. Nú er hægt að flýta sér áfram til ársins 2010! Excel drottnar yfir töflureiknum meira en Lotus-kóði hefur nokkru sinni gert í sögu sinni og fjöldi þeirra sem enn nota Lotus er nálægt núlli. Hvernig gat þetta gerst? Hver var ástæðan fyrir svona dramatískum atburðarásum?
Sérfræðingar greina tvo meginþætti:
- Í fyrsta lagi ákvað Lotus að þessi nýmótaði GUI vettvangur sem kallast Windows væri bara tíska sem myndi ekki endast lengi. Þeir neituðu að smíða Windows útgáfu af Lotus 1-2-3 (en aðeins í nokkur ár) og spáðu því að DOS útgáfan af hugbúnaðinum þeirra væri allt sem neytendur myndu þurfa. Microsoft þróaði náttúrulega Excel sérstaklega fyrir Windows.
- Í öðru lagi kynnti Microsoft tól í Excel sem kallast PivotTables sem var ekki fáanlegt í Lotus 1-2-3. PivotTables, eingöngu fyrir Excel, reyndust vera svo yfirþyrmandi gagnlegar að fólk hafði tilhneigingu til að halda sig við nýju Excel hugbúnaðarpakkann frekar en að halda áfram með Lotus 1-2-3, sem hafði þær ekki.
PivotTables, ásamt því að vanmeta velgengni Windows almennt, léku dauðagöngu fyrir Lotus 1-2-3 og hófu velgengni Microsoft Excel.
Hvað eru pivot töflur?
Svo, hver er besta leiðin til að einkenna hvað PivotTables eru?
Í einföldu máli eru snúningstöflur samantektir á sumum gögnum, búnar til til að auðvelda greiningu þessara gagna. Ólíkt handvirkum heildartölum eru Excel PivotTables gagnvirkar. Þegar búið er að búa til geturðu auðveldlega breytt þeim ef þau gefa ekki þá mynd sem þú varst að vonast eftir. Með örfáum músarsmellum er hægt að snúa heildartölum þannig að dálkafyrirsagnir verða línufyrirsagnir og öfugt. Þú getur gert margt með pivot töflum. Í stað þess að reyna að lýsa með orðum öllum eiginleikum snúningstafla, er auðveldara að sýna það í reynd ...
Gögnin sem þú greinir með PivotTables geta ekki verið tilviljunarkennd. Það ætti að vera hrá gögn, eins og listi af einhverju tagi. Til dæmis gæti það verið listi yfir sölu sem fyrirtækið hefur gert á síðustu sex mánuðum.
Skoðaðu gögnin sem sýnd eru á myndinni hér að neðan:
Athugið að þetta eru ekki hrá gögn þar sem þau hafa þegar verið tekin saman. Í reit B3 sjáum við $30000, sem er líklega heildarniðurstaðan sem James Cook gerði í janúar. Hvar eru þá upprunalegu gögnin? Hvaðan komu $30000 talan? Hvar er upphaflegi sölulistinn sem þessi mánaðarlega heildartala var fengin úr? Það er ljóst að einhver hefur staðið sig frábærlega við að skipuleggja og flokka öll sölugögn síðustu sex mánuðina og breyta þeim í heildartöfluna sem við sjáum. Hvað heldurðu að það hafi tekið langan tíma? Klukkutími? Klukkan tíu?
Staðreyndin er sú að taflan hér að ofan er ekki snúningstafla. Það var handsmíðað úr hráum gögnum sem geymd voru annars staðar og tók að minnsta kosti nokkrar klukkustundir að vinna úr því. Einmitt slíka yfirlitstöflu er hægt að búa til með því að nota pivot töflur á örfáum sekúndum. Við skulum reikna út hvernig…
Ef við förum aftur í upprunalega sölulistann myndi hann líta einhvern veginn svona út:
Þú gætir verið hissa á því að af þessum lista yfir viðskipti með hjálp snúningstafla og á örfáum sekúndum getum við búið til mánaðarlega söluskýrslu í Excel, sem við greindum hér að ofan. Já, við getum gert það og fleira!
Hvernig á að búa til snúningstöflu?
Fyrst skaltu ganga úr skugga um að þú hafir nokkur frumgögn í Excel blaði. Listinn yfir fjármálaviðskipti er sá dæmigerðasti sem á sér stað. Reyndar gæti það verið listi yfir hvað sem er: tengiliðaupplýsingar starfsmanna, geisladiskasafn eða upplýsingar um eldsneytisnotkun fyrirtækisins.
Svo, við byrjum Excel ... og hleðum slíkum lista ...
Eftir að við höfum opnað þennan lista í Excel getum við byrjað að búa til snúningstöflu.
Veldu hvaða reit sem er af þessum lista:
Síðan á flipanum Innsetning (Setja inn) veldu skipun PivotTable (Snúningstafla):
Gluggi mun birtast Búðu til snúningsborð (Búa til snúningstöflu) með tveimur spurningum fyrir þig:
- Hvaða gögn á að nota til að búa til nýja snúningstöflu?
- Hvar á að setja snúningstöfluna?
Þar sem við höfum þegar valið eina af listafrumunum í fyrra skrefi, verður allur listinn sjálfkrafa valinn til að búa til snúningstöflu. Athugaðu að við getum valið annað svið, aðra töflu og jafnvel einhvern ytri gagnagjafa eins og Access eða MS-SQL gagnagrunnstöflu. Að auki þurfum við að velja hvar á að setja nýju snúningstöfluna: á nýju blað eða á einu af þeim sem fyrir eru. Í þessu dæmi munum við velja valkostinn - Nýtt vinnublað (á nýtt blað):
Excel mun búa til nýtt blað og setja tóma snúningstöflu á það:
Um leið og við smellum á einhvern reit í snúningstöflunni birtist annar svargluggi: PivotTable sviðslisti (Pivot table reiti).
Listi yfir reiti efst í valmyndinni er listi yfir alla titla af upprunalega listanum. Fjögur tómu svæðin neðst á skjánum gera þér kleift að segja PivotTable hvernig þú vilt draga saman gögnin. Svo lengi sem þessi svæði eru auð er ekkert í töflunni heldur. Allt sem við þurfum að gera er að draga fyrirsagnirnar frá efsta svæðinu yfir á tómu svæðin fyrir neðan. Á sama tíma er pivot tafla sjálfkrafa búin til, í samræmi við leiðbeiningar okkar. Ef við gerum mistök getum við fjarlægt fyrirsagnirnar af neðsta svæðinu eða dregið aðra til að skipta þeim út.
Stærð Gildi (Merking) er líklega mikilvægasta af þessum fjórum. Hvaða fyrirsögn er sett á þetta svæði ákvarðar hvaða gögn verða tekin saman (summa, meðaltal, hámark, lágmark o.s.frv.) Þetta eru nánast alltaf töluleg gildi. Frábær umsækjandi um sæti á þessu sviði eru gögnin undir fyrirsögninni Upphæð (Kostnaður) af upprunalegu borðinu okkar. Dragðu þennan titil á svæðið Gildi (Gildi):
Vinsamlegast athugaðu að titillinn Upphæð er nú merkt með gátmerki, og á svæðinu Gildi (Gildir) færsla hefur birst Summa af upphæð (Upphæð reitur Magn), sem gefur til kynna að dálkurinn Upphæð dregið saman.
Ef við skoðum snúningstöfluna sjálfa munum við sjá summan af öllum gildum úr dálknum Upphæð upprunalega borðið.
Svo, fyrsta snúningsborðið okkar er búið til! Þægilegt, en ekki sérstaklega áhrifamikið. Við viljum líklega fá meiri upplýsingar um gögnin okkar en við höfum nú.
Snúum okkur að upprunalegu gögnunum og reynum að finna einn eða fleiri dálka sem hægt er að nota til að skipta þessari upphæð. Til dæmis getum við myndað snúningstöfluna okkar á þann hátt að heildarupphæð sölu er reiknuð fyrir hvern seljanda fyrir sig. Þeir. línum verður bætt við snúningstöfluna okkar með nafni hvers sölumanns í fyrirtækinu og heildarsöluupphæð þeirra. Til að ná þessari niðurstöðu skaltu bara draga titilinn sölufulltrúa (sölufulltrúi) til svæðisins Raðamerki (Strengir):
Það verður áhugaverðara! PivotTablen okkar er farin að taka á sig mynd...
Sjáðu kosti? Með nokkrum smellum bjuggum við til töflu sem hefði tekið mjög langan tíma að búa til handvirkt.
Hvað annað getum við gert? Jæja, í vissum skilningi er snúningstaflan okkar tilbúin. Við höfum búið til gagnlega samantekt á upprunalegu gögnunum. Mikilvægar upplýsingar hafa þegar borist! Í restinni af þessari grein munum við skoða nokkrar leiðir til að búa til flóknari PivotTables, auk þess að læra hvernig á að sérsníða þær.
Uppsetning PivotTable
Í fyrsta lagi getum við búið til tvívíddar snúningstöflu. Við skulum gera þetta með því að nota dálkafyrirsögnina Greiðslumáti (Greiðslumáti). Dragðu bara titilinn Greiðslumáti að svæðinu Súlumerki (dálkar):
Við fáum niðurstöðuna:
Lítur mjög flott út!
Nú skulum við búa til þrívíddartöflu. Hvernig myndi svona borð líta út? Látum okkur sjá…
Dragðu haus Pakki (Flókið) að svæðinu skýrslusíur (Síur):
Athugið hvar hann er…
Þetta gefur okkur tækifæri til að sía skýrsluna á grundvelli „Hvaða orlofssamstæða var greitt fyrir“. Til dæmis getum við séð sundurliðun eftir seljendum og eftir greiðslumáta fyrir alla fléttur, eða með nokkrum músarsmellum, breytt sýn á snúningstöfluna og sýnt sömu sundurliðun aðeins fyrir þá sem pöntuðu fléttuna Sólleitarmenn.
Svo, ef þú skilur þetta rétt, þá er hægt að kalla snúningstöfluna okkar þrívídd. Höldum áfram að setja upp…
Ef það kemur allt í einu í ljós að aðeins greiðsla með ávísun og kreditkorti (þ.e. staðgreiðslulaus greiðsla) ætti að birtast í snúningstöflunni, þá getum við slökkt á birtingu titilsins Cash (Reiðufé). Fyrir þetta, við hlið Súlumerki smelltu á örina niður og taktu hakið úr reitnum í fellivalmyndinni Cash:
Við skulum sjá hvernig snúningstaflan okkar lítur út núna. Eins og þú sérð, dálkurinn Cash hvarf frá henni.
Forsníða PivotTables í Excel
PivotTables eru augljóslega mjög öflugt tól, en hingað til virðast niðurstöðurnar svolítið látlausar og leiðinlegar. Til dæmis líta tölurnar sem við tökum saman ekki út eins og dollaraupphæðir – þær eru bara tölur. Við skulum laga þetta.
Það er freistandi að gera það sem maður á að venjast í slíkum aðstæðum og velja einfaldlega alla töfluna (eða allt blaðið) og nota staðlaða talnasniðshnappa á tækjastikunni til að stilla æskilegt snið. Vandamálið við þessa nálgun er að ef þú breytir einhvern tíma uppbyggingu snúningstöflunnar í framtíðinni (sem gerist með 99% líkur), mun sniðið glatast. Það sem við þurfum er leið til að gera það (næstum) varanlegt.
Fyrst skulum við finna færsluna Summa af upphæð in Gildi (Gildi) og smelltu á það. Í valmyndinni sem birtist skaltu velja hlutinn Gildisreitastillingar (Valkostir gildissviðs):
Gluggi mun birtast Gildisreitastillingar (Valkostir gildissviðs).
Smelltu á hnappinn Númerasnið (Númerasnið) opnast svargluggi. Sniðið frumur (frumusnið):
Af listanum Flokkur (Tölusnið) veldu Bókhald (Financial) og stilltu fjölda aukastafa á núll. Ýttu nú nokkrum sinnum OKtil að fara aftur í snúningstöfluna okkar.
Eins og þú sérð eru tölurnar sniðnar sem dollaraupphæðir.
Á meðan við erum að forsníða, skulum við setja upp sniðið fyrir alla PivotTable. Það eru nokkrar leiðir til að gera þetta. Við notum þann sem er einfaldari…
Smelltu á PivotTable Verkfæri: Hönnun (Vinnur með Pivot Tables: Smiður):
Næst skaltu stækka valmyndina með því að smella á örina neðst í hægra horninu á hlutanum PivotTable stílar (PivotTable Styles) til að sjá umfangsmikið safn af innbyggðum stílum:
Veldu hvaða stíl sem er við hæfi og skoðaðu niðurstöðuna í snúningstöflunni þinni:
Aðrar PivotTable stillingar í Excel
Stundum þarftu að sía gögn eftir dagsetningum. Til dæmis, á listanum okkar yfir viðskipti eru margar, margar dagsetningar. Excel býður upp á tól til að flokka gögn eftir degi, mánuði, ári og svo framvegis. Við skulum sjá hvernig það er gert.
Fjarlægðu fyrst færsluna. Greiðslumáti frá svæðinu Súlumerki (Dálkar). Til að gera þetta, dragðu það aftur á listann yfir titla og færðu titilinn í staðinn Dagsetning bókuð (bókunardagur):
Eins og þú sérð gerði þetta snúningstöfluna okkar ónýta tímabundið. Excel bjó til sérstakan dálk fyrir hverja dagsetningu sem viðskipti voru gerð. Fyrir vikið fengum við mjög breitt borð!
Til að laga þetta skaltu hægrismella á hvaða dagsetningu sem er og velja úr samhengisvalmyndinni GROUP (Hópur):
Flokkunarglugginn mun birtast. Við veljum Mánuðir (mánuði) og smelltu OK:
Voila! Þessi tafla er miklu gagnlegri:
Við the vegur, þessi tafla er nánast samhljóða þeirri sem sýnd er í upphafi greinarinnar, þar sem sölutölur voru teknar saman handvirkt.
Það er annar mjög mikilvægur punktur sem þú þarft að vita! Þú getur ekki búið til eitt, heldur nokkur stig af fyrirsögnum í röðum (eða dálkum):
… og það mun líta svona út…
Það sama er hægt að gera með dálkafyrirsögnum (eða jafnvel síum).
Snúum okkur aftur að upprunalegu formi töflunnar og sjáum hvernig á að birta meðaltöl í stað upphæða.
Til að byrja skaltu smella á Summa af upphæð og veldu úr valmyndinni sem birtist Gildisreitastillingar (Valkostir gildissviðs):
Listinn Taktu saman gildissvið með (aðgerð) í glugganum Gildisreitastillingar (Valkostir gildissviðs) veldu Meðal (Meðaltal):
Á sama tíma, meðan við erum hér, skulum við breyta CustomName (Sérsniðið nafn) með Meðaltal af upphæð (Magn reitinn Magn) í eitthvað styttra. Sláðu inn í þennan reit eitthvað eins og Meðaltal:
Press OK og sjá hvað gerist. Athugaðu að öll gildi hafa breyst úr samtölum í meðaltöl og töfluhausinn (í efri vinstra hólfinu) hefur breyst í Meðaltal:
Ef þú vilt geturðu strax fengið upphæð, meðaltal og fjölda (sala) sett í eina pivottöflu.
Hér er skref fyrir skref leiðbeiningar um hvernig á að gera þetta, byrjað á tómri snúningstöflu:
- Dragðu haus sölufulltrúa (sölufulltrúi) til svæðisins Súlumerki (Dálkar).
- Dragðu titilinn þrisvar sinnum Upphæð (Kostnaður) á svæði Gildi (Gildi).
- Fyrir fyrsta völlinn Upphæð breyta titlinum í Samtals (Upphæð), og talnasniðið í þessum reit er Bókhald (Fjármála). Fjöldi aukastafa er núll.
- Annar völlur Upphæð nafn Meðaltale, stilltu aðgerðina fyrir það Meðal (Meðaltal) og talnasniðið í þessum reit breytast einnig í Bókhald (Financial) með núll aukastöfum.
- Fyrir þriðja völlinn Upphæð setja titil Telja og aðgerð fyrir hann - Telja (Magn)
- Í Súlumerki (dálkar) reiturinn búinn til sjálfkrafa Σ Gildi (Σ Gildi) – dragðu það á svæðið Raðamerki (Línur)
Hér er það sem við munum enda með:
Heildarupphæð, meðalverðmæti og fjöldi sölu – allt í einni snúningstöflu!
Niðurstaða
Pivot töflur í Microsoft Excel innihalda mikið af eiginleikum og stillingum. Í svona lítilli grein eru þeir ekki einu sinni nálægt því að ná yfir þá alla. Það þyrfti litla bók eða stóra vefsíðu til að lýsa öllum möguleikum snúningstöflunnar til hlítar. Djarfir og forvitnir lesendur geta haldið áfram könnun sinni á snúningstöflum. Til að gera þetta skaltu bara hægrismella á næstum hvaða þætti sem er í snúningstöflunni og sjá hvaða aðgerðir og stillingar opnast. Á borði finnur þú tvo flipa: PivotTable Tools: Valkostir (greining) og hönnun (Smiður). Ekki vera hræddur við að gera mistök, þú getur alltaf eytt PivotTable og byrjað upp á nýtt. Þú hefur tækifæri sem langtímanotendur DOS og Lotus 1-2-3 höfðu aldrei.