A pivot táblák a Microsoft Excel egyik legerősebb jellemzői. Ezek lehetővé teszik, hogy nagy mennyiségű adatot elemezzenek és csak néhány egérkattintással összegezzenek. Ebben a cikkben feltérképeztük a PivotTables-okat, megértettük, hogy mi is, és megtanuljuk létrehozni és testreszabni őket.
Megjegyzés: Ez a cikk az Excel 2010 (Beta) használatával készült. A PivotTable koncepciója kevéssé változott az évek során, de az elkészítés módja az Excel szinte minden egyes iterációjában megváltozott. Ha olyan Excel-verziót használ, amely nem 2010, akkor várjon más képernyőt a cikkben látottaktól.
Egy kis történelem
A táblázatkezelő programok első napjaiban a Lotus 1-2-3 szabályozta a roostot. A dominancia olyan tökéletes volt, hogy az emberek azt gondolták, hogy ez időveszteség a Microsoft számára, hogy zavarja a saját táblázatkezelő szoftver (Excel) kifejlesztését a Lotus-hoz való versenyről. A Flash-előrejelzés 2010-re és az Excel domináns táblázati piaca nagyobb, mint a Lotus, míg a Lotus 1-2-3-at továbbra is futó felhasználók száma nulla. Hogy történt ez? Mi okozta a szerencsejátékok ilyen drámai megfordulását?
Az iparági elemzők két tényezőt vetettek alá: Először is, a Lotus úgy döntött, hogy ez a kedves új "Windows" grafikus felület a múló hangvétel volt, amely soha nem fog felállni. Elutasították, hogy létrehozzák a Lotus 1-2-3 Windows verzióját (néhány évig, egyébként), előrejelezve, hogy a szoftverük DOS-verziója mindenki, akinek valaha is szüksége lenne. A Microsoft természetesen kifejlesztette Excel-et kizárólag a Windows rendszerhez. Másodszor, a Microsoft olyan funkciót fejlesztett ki az Excel számára, amelyet a Lotus nem adott meg 1-2-3-ban PivotTables. Az Excel excl. PivotTables funkciót annyira megdöbbentően hasznosnak tartották, hogy az emberek hajlandóak egy egész új szoftvercsomagot (Excel) tanulni, nem pedig egy olyan programot (1-2-3), amely nem rendelkezett vele. Ez a funkció, valamint a Windows sikertelensége, a Lotus 1-2-3 halálesete és a Microsoft Excel sikere.
A pivot táblák megértése
Tehát mi is pontosan egy PivotTable?
Egyszerűen megfogalmazva, a PivotTable egy összefoglaló egyes adatok létrehozására, amelyek lehetővé teszik az említett adatok egyszerű elemzését. De ellentétben egy kézzel készített összefoglalóval, az Excel PivotTables interaktív. Miután létrehozta az egyiket, könnyen megváltoztathatja, ha nem nyújt pontos betekintést az Ön által remélt adatokba. Néhány kattintásnál az összefoglaló "elforgatható" - oly módon forgatható, hogy az oszlopfejlécek sorsorokká váljanak, és fordítva. Sokkal több is lehet tenni. Ahelyett, hogy megpróbálná leírni a PivotTables összes funkcióját, egyszerűen bemutatjuk őket …
A pivotTable segítségével elemzett adatok nem lehetnek egyszerűek bármilyen adatoknak kell lennie nyers adatok korábban feldolgozatlan (unsummarized) - jellemzően valamiféle lista. Erre példa lehet az elmúlt hat hónapban a cégen belüli értékesítési tranzakciók listája.
Vizsgálja meg az alábbi adatokat:
Figyeld meg, hogy ez így van nem nyers adatok. Valójában ez már valamiféle összefoglaló. A B3 cellában 30 000 dollárt látunk, ami nyilvánvalóan a James Cook teljes értékesítése a januári hónapban. Tehát hol van a nyers adatok? Hogyan érkeztünk meg a 30 000 dollárra? Hol található az értékesítési tranzakciók eredeti listája, hogy ezt az értéket generálták? Nyilvánvaló, hogy valahol valakinek az elmúlt hat hónap során az összes értékesítési tranzakció összeegyeztetésének nehézségei közé kellett mennie a fenti összefoglalóba. Meddig tartod ezt? Egy óra? Tíz?
Valószínűleg igen. Láthatja, a fenti táblázatban valójában szerepel nem egy pivotTable. Manuálisan hozták létre a máshol tárolt nyers adatokból, és valóban pár órát vett igénybe a fordítás. Azonban pontosan ez a fajta összefoglaló tudott létre kell hozni a PivotTables segítségével, amely esetben csak néhány másodperc eltelt volna. Lássuk, hogyan …
Ha az értékesítési tranzakciók eredeti listáját követnénk, akkor valami ilyesmi lehet:
Hogyan hozzunk létre egy pivotTable-t
Először is győződjön meg róla, hogy van néhány nyers adat az Excel munkalapjában. A pénzügyi tranzakciók listája tipikus, de lehet bárkinek is: munkatársi kapcsolattartási adatok, CD-gyűjteménye vagy üzemanyag-fogyasztási adatai a vállalat gépjárműparkjában.
Ezért elindítjuk az Excel-t … és betöltünk egy ilyen listát …
Kattintson a listán szereplő egyetlen cellára:
Ezután a Insert lapon kattintson a Pivot tábla ikon:
A PivotTable létrehozása mező megjelenik, két kérdést kérek: Milyen adatoknak kell az új pivottábla alapulnia, és hol kell létrehozni? Mivel már rákattintottunk egy cellára a listán (a fenti lépésben), a cellát körülvevő teljes lista már kiválasztott nekünk ($ A $ 1: $ G $ 88 a kifizetések lap, ebben a példában). Megjegyezzük, hogy bármely más munkalap bármely más régiójában, vagy akár egy külső adatforrásból, például egy Access adatbázis táblából vagy akár egy MS-SQL Server adatbázis táblából is kiválaszthatunk listát. Azt is meg kell adnunk, hogy szeretnénk-e új PivotTable-t létrehozni egy a új munkalapon vagy egy létező egy. Ebben a példában kiválaszthatjuk a új egy:
Egy másik doboz is megjelenik: A PivotTable mezőlista. Ez a mezőlista akkor jelenik meg, amikor rákattintunk a pivotTable (fent) bármely cellájára:
A mező felső részében található mezők listája valójában az eredeti nyers adatok munkalapja oszlopcímek gyűjteménye. A képernyő alsó részén található négy üres mező lehetővé teszi számunkra, hogy kiválasszuk azt a módot, ahogyan a pivotTable-junk összefoglalja a nyers adatokat. Eddig nincs semmi abban a mezőben, így a PivotTable üres. Mindössze annyit kell tennünk, hogy húzza a mezőket a fent felsorolt listából, és tegye őket az alsó fiókokba. Ezután automatikusan létrehozunk egy pivotTable-t az utasításainknak megfelelően. Ha tévedünk, akkor csak vissza kell húznunk a mezőket, ahonnan származtattuk és / vagy húztuk új mezőket lefelé cserélni.
A értékek a doboz vitathatatlanul a négy legfontosabb. Az ebben a mezőbe húzott mező azokat az adatokat jelenti, amelyeket valamilyen módon össze kell foglalni (összegzéssel, átlagolással, a maximális, a minimális stb. Megtalálásával). Ez szinte mindig számszerű adat. A mintaadatokban ez a doboz tökéletes jelöltje az "Összeg" mező / oszlop. Húzzuk a mezőt a értékek doboz:
Vegye figyelembe, hogy (a) a mezők listájában a "Összeg" mező be van jelölve, és a "Összeg összege" értékek doboz, jelezve, hogy az összeg oszlop összegezve van.
Ha magunk is megvizsgáljuk a PivotTable-t, valóban megtaláljuk az összes "Összeg" érték összegét a nyers adatok munkalapjából:
A mintaadatokra való hivatkozással meg kell határoznunk egy vagy több oszlopcímet, amelyeket elképzelhetően felosztanánk a teljes összeg felosztására. Például eldöntheti, hogy szeretnénk látni az adataink összefoglalását, ahol a sor címe mindegyik cég különböző ügyfeleinek, és egyenként. Ennek eléréséhez mindössze annyit kell tennünk, hogy az "Értékesítő" mezőt húzzuk be sorcímkék doboz:
MostVégül a dolgok érdekesnek tűnnek! A PivotTable alakja megkezdődik ….
Tehát mi mást tehetünk? Hát, egy értelemben a PivotTable teljes. Hasznos összefoglalót készítettünk forrásadatainkról. A fontos dolgok már megtanultak! A cikk hátralevő részében megvizsgáljuk azokat a módokat, amelyekkel összetettebb pivottáblák hozhatók létre, és azokat a módszereket, amelyekkel ezek a pivottáblák testreszabhatók.
Először létrehozhatunk egy két-dimenziós táblázat. Tegyük meg ezt a "Fizetési mód" oszlopfejléc használatával. Egyszerűen húzza a "Fizetési mód" címét a Oszlopcímkék doboz:
Kezdés nagyon menő!
Nézzük meg három-dimenziós táblázat. Milyen lehet egy ilyen asztal? Lássuk …
Húzza a "Csomag" oszlopot / címsorát a Jelentésszűrő doboz:
Ez lehetővé teszi számunkra, hogy szűrjük a jelentésünket, amely alapján "üdülési csomagot" vásároltunk. Például láthatjuk az eladó vagy a fizetési mód szerinti bontást minden csomagok, vagy néhány kattintással megváltoztathatja a "Sunseekers" csomaggal azonos bontást:
Ha kiderül, azt mondjuk, hogy csak látni akarjuk csekk és hitelkártya tranzakciókat (azaz nem készpénzes tranzakciókat), akkor a "Készpénz" elemet törölni lehet az oszlopok fejlécéből. Kattintson a mellette lévő legördülő nyílra Oszlopcímkék, és a "készpénz" eltávolítása:
formázása
Ez nyilvánvalóan nagyon erős rendszer, de eddig az eredmények nagyon egyszerűnek és unalmasnak tűnnek. Kezdetben a számok, amelyeket összegezünk, nem úgy néznek ki, mint a dollárösszegek - egyszerűen csak régi számok. Javítsuk ki.
Kísértés lehet az, amit tennünk, amikor ilyen körülmények között csináltuk, és egyszerűen kiválaszthatjuk az egész táblázatot (vagy az egész munkalapot), és a formázás befejezéséhez használjuk az eszköztár szabványos számformázó gombjait. Az ezzel a megközelítéssel kapcsolatos probléma az, hogy ha valaha is megváltoztatná a PivotTable struktúráját a jövőben (ami valószínűleg 99%), akkor ezek a számformátumok elvészek. Olyan módra van szükségünk, amely véglegesíti őket (félig).
Először a "Összeg összege" bejegyzést keressük a értékek mezőbe, és kattintson rá. Megjelenik egy menü. Kiválasztjuk Értékmezőbeállítások … a menüből:
A Érték mezőbeállítások mező megjelenik.
Kattints a Számformátum gomb és a szabvány Formázás cellák mezőbe Megjelenik:
Tól Kategória lista, válassza ki (mondja) Számvitel, és írja le a tizedesjegyek számát 0-ra. Kattintson a gombra rendben néhányszor, hogy visszatérjenek a PivotTable-be …
Míg a formázás tárgya, formázzuk a teljes pivottáblát. Van néhány módja ennek. Használjunk egy egyszerű …
Kattints a PivotTable eszközök / Tervezés lapon:
Ezután engedje le a nyíl jobb alsó sarkát PivotTable stílusok listát a beépített stílusok hatalmas gyűjteményének megtekintéséhez:
Egyéb opciók
Dátumokkal is dolgozhatunk. Most általában több, sok dátum van a tranzakciós listában, mint amilyet elkezdtünk. Az Excel azonban lehetőséget ad arra, hogy napi, heti, havi, évenként stb. Csoportosítsa az adattartalmakat. Lássuk, hogyan történik ez.
Először töröljük le a "Fizetési mód" oszlopot a Oszlopcímkék doboz (egyszerűen húzza vissza a mező listába), és cserélje le a "Date Booked" oszlopról:
A javításhoz kattintson a jobb gombbal bármely dátumra és válassza ki Csoport… a helyi menüből:
Megjelenik a csoportos mező. Kiválasztjuk hónapok és kattintson az OK gombra:
Voálá! A sokkal több hasznos táblázat:
(Egyébként ez a táblázat gyakorlatilag megegyezik a cikk elején bemutatottal - az eredeti értékesítési összefoglalóval, amelyet manuálisan hoztak létre.)
Egy másik hűvös dolog, hogy tudatában van, hogy több sor sor sorok (vagy oszlopfejlécek) lehetnek:
Egyszerűen megtartva a dolgokat, nézzük meg, hogyan tervezzük meg átlagolt értékeket, nem pedig összegzett értékeket.
Először kattintson az "Összeg összege" lehetőségre, és válassza ki Értékmezőbeállítások … a megjelenő helyi menüből:
Ban,-ben Összegezze értékterületeit a következővel: lista a Érték mezőbeállítások doboz, válassza ki Átlagos:
Bár itt vagyunk, változtassuk meg Egyéni név, az "Átlagos összeget" és egy kicsit tömörebbé. Írja be az "Avg" -t:
Kattints rendben, és nézze meg, hogy néz ki. Vegyük észre, hogy az összes érték az összegzett összegekről az átlagokra változik, és az asztal címe (bal felső sáv) "Avg" értékre változott:
Itt vannak a lépések, hogy valami ilyesmit szerezzen a helyén (kezdődően üres pivotTable):
- Húzza a "Salesperson" -t a Oszlopcímkék
- Húzza a "Összeg" mezőt a értékek doboz háromszor
- Az első "Összeg" mezőnél változtassa meg egyéni nevét "Összesen", és számformátumot Számvitel (0 tizedesjegy)
- A második "Összeg" mezőnél változtassa meg az egyéni nevét "Átlagra", funkcióját a következőre: Átlagos és a szám formátuma Számvitel (0 tizedesjegy)
- A harmadik "Összeg" mezőben nevezze el a nevét "Count" -re és annak funkcióját a következőre: Számol
-
Húzza az automatikusan létrehozott elemet
Image mezőből Oszlopcímkék nak nek sorcímkék
Íme, amivel végül:
Következtetés
A Microsoft Excel által létrehozott PivotTables számos, sok további funkcióval és lehetőséggel rendelkezik - túl sok ahhoz, hogy egy ilyen cikket felsoroljon. A PivotTables potenciáljának teljes körű lefedéséhez egy kis könyv (vagy egy nagy weboldal) szükséges. A bátor és / vagy geeky olvasók könnyedén felfedezhetik a PivotTables-okat: egyszerűen kattintson jobb gombbal mindent, és nézze meg, milyen lehetőségek válnak elérhetővé az Ön számára. Vannak a két szalagfül: PivotTable eszközök / opciók és Tervezés. Nem számít, ha hibázol - egyszerűen törölheted a pivottáblát, és újra elkezdheted a lehetőséget, mert a Lotus 1-2-3 régi DOS-felhasználóinak soha nem volt ilyen.
Ha Office 2007-ben dolgozik, érdemes megnézni cikkünket arról, hogyan hozhatunk létre pivottáblát az Excel 2007 programban.
Beleállítottunk egy Excel munkafüzetet, amelyet letölthetünk a pivotTable készségek gyakorlására. Az Excel minden verzióját a 97-től kezdődően kell működnie.
Töltse le a Gyakorlatunk Excel munkafüzetét
