A Pivot Tables használata a Microsoft Excel programban

A PivotTables a Microsoft Excel egyik legerősebb tulajdonsága. Ezek lehetővé teszik a nagy mennyiségű adat elemzését és összegzését néhány kattintással. Ebben a cikkben felfedezzük a PivotTables-et, megértjük, hogy melyek vannak, és megtanulják, hogyan hozhatók létre és testreszabhatók.

Megjegyzés: Ez a cikk az Excel 2010 (Beta) használatával készült. A PivotTable fogalma az évek során kevéssé változott, de az egyik módszert az Excel szinte minden iterációjában megváltoztatták. Ha nem 2010-es Excel-verziót használ, várjon különböző képernyőket az e cikkben láthatóaktól.

Egy kis történelem

A táblázatkezelő programok első napjaiban a Lotus 1-2-3 uralkodott. Az uralkodása annyira teljes volt, hogy az emberek azt gondolták, hogy az időpocsékolás a Microsoft számára, hogy zavarja saját táblázatkezelő szoftverének (Excel) fejlesztését, hogy versenyezzen a Lotus-szal. A 2010-ig terjedő flash-előrejelzések és az Excel dominanciája a táblázatkezelő piacon nagyobb, mint a Lotus valaha, míg a Lotus 1-2-3-at még futtató felhasználók száma közeledik a nullához. Hogy történt ez? Mi okozott ilyen drámai visszafordulást a vagyonban?

Az iparági elemzők két tényezőre tettek szert: Először is, a Lotus úgy döntött, hogy ez a divatos új GUI-platform, a „Windows”, egy olyan haladó hóbort volt, amely soha nem indulna el. Elutasították a Lotus 1-2-3 Windows verziójának létrehozását (néhány éven át), előrejelezve, hogy a szoftver DOS-verziója mindenki számára valaha is szüksége lenne. A Microsoft természetesen kizárólag Excelre fejlesztette az Excel-t. Másodszor, a Microsoft kifejlesztett egy olyan funkciót az Excel számára, amelyet a Lotus nem szolgáltatott 1-2-3-ban PivotTables.  A PivotTables funkciót, amely kizárólag az Excel-hez tartozik, annyira lenyűgözően hasznosnak tartották, hogy az emberek hajlandóak voltak egy teljes új szoftvercsomagot (Excel) tanulni, nem pedig egy olyan programot (1-2-3), amelyiknek nem volt rá szüksége. Ez az egyik funkció, a Windows sikerének helytelen megítélése mellett a Lotus 1-2-3 halálkötege volt, és a Microsoft Excel sikereinek kezdete..

A PivotTables értelmezése

Tehát pontosan mi a PivotTable?

Egyszerűen fogalmazva, a PivotTable egy olyan adat összefoglalása, amely az adatok egyszerű elemzéséhez készült. Azonban a manuálisan létrehozott összefoglalótól eltérően az Excel PivotTables interaktív. Miután létrehozta az egyiket, könnyen megváltoztathatja azt, ha nem nyújtja be a pontos betekintést az adatokba, amelyeket remélett. Néhány kattintással az összefoglaló „elfordítható” - elforgatható oly módon, hogy az oszlopfejlécek sorfejlécekké váljanak, és fordítva. Sokkal többet lehet tenni. Ahelyett, hogy megpróbálnánk leírni a PivotTables összes funkcióját, egyszerűen bemutatjuk őket ...

A PivotTable segítségével elemzett adatok nem lehetnek igazak bármilyen adatoknak kell lennie nyers a korábban feldolgozatlan (nem megfogalmazott) adatok - általában egyfajta lista. Erre példa lehet egy vállalatnál az elmúlt hat hónapra vonatkozó értékesítési tranzakciók listája.

Vizsgálja meg az alábbi adatokat:

Figyeljük meg, hogy ez az nem nyers adatok. Valójában már valamilyen összefoglaló. A B3 cellában 30 000 dollárt láthatunk, ami nyilvánvalóan a James Cook teljes árbevételének összege a január hónapban. Szóval hol vannak a nyers adatok? Hogyan érkeztünk meg a 30.000 dolláros értékre? Hol található az eredeti értékesítési tranzakciók listája? Nyilvánvaló, hogy valahol valakinek el kellett mennie ahhoz, hogy az elmúlt hat hónap összes értékesítési tranzakcióját összegyűjtse a fenti összefoglalóban. Meddig tartod ezt? Egy óra? Tíz?

Valószínűleg igen. Látod, a fenti táblázat a ténylegesen nem egy PivotTable. A máshol tárolt nyers adatokról manuálisan jöttek létre, és valóban pár órát vett igénybe. Azonban pontosan ez a fajta összefoglaló tudott a PivotTables segítségével hozható létre, amely esetben csak néhány másodpercet vett igénybe. Nézzük meg, hogy…

Ha az eladási tranzakciók eredeti listáját nyomon követnénk, úgy nézhet ki ilyesmi:

Meglepődhet, hogy megtudta, hogy az Excel PivotTable szolgáltatásával néhány másodperc alatt létrehozhatunk egy, a fentiekhez hasonló havi értékesítési összefoglalót, csak néhány kattintással. Ezt megtehetjük - és sokkal többet is!

PivotTable létrehozása

Először 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ármit is tartalmazó lista: az alkalmazottak elérhetőségei, a CD-gyűjteménye, vagy az üzemanyag-fogyasztási adatai a vállalat flottájához.

Tehát elindítjuk az Excel-t… és betöltünk egy ilyen listát ...

Miután megnyitottuk a listát az Excelben, készen állunk a PivotTable létrehozására.

Kattintson a listán lévő egyetlen cellára:

Ezután a Insert lapon kattintson a Pivot tábla ikon:

A PivotTable létrehozása megjelenik a következő két kérdés: Milyen adatokat kell alapulnia az új PivotTable-nek, és hol kell létrehozni? Mivel már rákattintottunk egy listán lévő cellára (a fenti lépésben), a cellát körülvevő teljes lista már kiválasztott számunkra ($ A $ 1: $ G $ 88 a kifizetések ebben a példában). Ne feledje, hogy bármely más munkalap bármely más régiójában, vagy akár külső adatforrásban, például Access adatbázis-táblában, vagy akár egy MS-SQL Server adatbázis-táblában is kiválaszthatunk egy listát. Azt is ki kell választanunk, hogy új PivotTable-ot szeretnénk-e létrehozni a új munkalapon vagy egy létező egy. Ebben a példában a új egy:

Az új munkalap hozzánk létrejön, és egy üres PivotTable van létrehozva a munkalapon:

Megjelenik egy másik doboz: A PivotTable Field List.  Ez a mezőlista akkor jelenik meg, ha a PivotTable (fent) bármelyik cellájára kattintunk:

A mező felső részén található mezők listája valójában az eredeti nyersadat-munkalap oszloposzlopainak gyűjteménye. A képernyő alsó részén található négy üres doboz lehetővé teszi, hogy kiválasszuk a PivotTable-nek a nyers adatok összegzésének módját. Eddig ezekben a dobozokban nincs semmi, így a PivotTable üres. Mindössze annyit kell tennünk, hogy húzza le a mezőket a fenti listából, és dobja őket az alsó dobozokba. A PivotTable automatikusan létrehozásra kerül, hogy megfeleljen az utasításoknak. Ha tévedünk, akkor csak a mezőket vissza kell húznunk oda, ahonnan jöttek és / vagy húztak új mezők helyett őket.

A értékek a négyes közül a doboz a vitathatatlanul a legfontosabb. Az ebbe a mezőbe húzott mező az adatokat, amelyeket valamilyen módon kell összefoglalni (összegzéssel, átlagolással, a maximális, minimális stb. Megállapításával). Majdnem mindig számszerű adat. A mintaadatokban ez a mező tökéletes jelöltje az "Összeg" mező / oszlop. Húzza a mezőt a értékek doboz:

Figyeljük meg, hogy (a) a mezők listájában az „Összeg” mező már be van jelölve, és az „Összegösszeg” hozzáadásra került a értékek mező, jelezve, hogy az összeg oszlopot összegezték.

Ha megvizsgáljuk a PivotTable-ot, valóban megtaláljuk az összes "Összeg" érték összegét a nyersadatlapon:

Létrehoztuk az első PivotTable-ot! Kényelmes, de nem különösebben lenyűgöző. Valószínű, hogy ehhez egy kicsit több információra van szükségünk.

A mintaadatokra hivatkozva egy vagy több oszlopfejlécet kell azonosítanunk, amelyeket elképzelhető módon felhasználhatnánk a teljes összeg megosztására. Például eldönthetjük, hogy szeretnénk összefoglalni adatait, ahol van egy sorfejléc a cégünk minden egyes értékesítőjéhez, és egyenként mindegyikhez. Ennek eléréséhez mindössze annyit kell tennünk, hogy a „Értékesítő” mezőt a sorcímkék doboz:

Most, végül, a dolgok elkezdődnek érdekes! A PivotTable elkezd formázni… .

Néhány kattintással létrehoztunk egy táblázatot, amely hosszú időt vett igénybe manuálisan.

Tehát mi mást tehetünk? Nos, egy bizonyos értelemben a PivotTable teljes. Létrehoztunk egy hasznos összefoglalót forrásadatainkról. A fontos dolgokat már megtanulták! A cikk többi részén megvizsgáljuk néhány módját, hogy a bonyolultabb PivotTables-eket létrehozhassuk, és hogyan lehet ezeket a PivotTables-ket testreszabni.

Először is létrehozhatunk egy két-dimenziós táblázat. Tegyük meg ezt, ha a „Fizetési mód” oszlopot használjuk. Egyszerűen húzza a „Fizetési mód” címet a Oszlopcímkék doboz:

Amely így néz ki:

Elkezd kezdeni nagyon menő!

Hozzuk létre három-dimenziós táblázat. Mit jelenthet egy ilyen asztal? Nos, nézzük…

Húzza a „Csomag” oszlopot / fejlécet a Jelentésszűrő doboz:

Figyelje meg, hol ér véget… .

Ez lehetővé teszi számunkra, hogy szűrjük a jelentésünket, amely alapján megvásároltuk az „üdülési csomagot”. Például láthatjuk az eladó és a fizetési mód közötti bontást minden csomagokkal, vagy néhány kattintással változtassa meg, hogy a „Sunseekers” csomaghoz hasonló

És ha így gondolod a helyes utat, a PivotTable most háromdimenziós. Folytassuk a testreszabást…

Ha kiderül, hogy csak látni akarjuk csekk és hitelkártya tranzakciók (azaz készpénzes tranzakciók nélkül), akkor törölhetjük a „Készpénz” elemet az oszlopfejlécekből. Kattintson a melletti legördülő nyílra Oszlopcímkék, és jelölje ki a „Készpénzt”:

Lássuk, hogy mi néz ki ... Ahogy láthatjuk, a „Cash” eltűnt.

formázása

Ez nyilvánvalóan egy nagyon erős rendszer, de eddig az eredmények nagyon egyszerűek és unalmasak. Kezdetben a számok, amiket összegezünk, nem úgy néz ki, mint a dollár összegek - csak a régi számok. Javítsuk ki.

A kísértés lehet az, hogy az, amit szoktunk ilyen körülmények között elvégezni, egyszerűen válassza ki a teljes táblázatot (vagy a teljes munkalapot), és a formázás befejezéséhez használja az eszköztár szabványos számformázási gombjait. Ennek a megközelítésnek az a problémája, hogy ha a jövőben megváltoztatja a PivotTable szerkezetét (ami valószínűleg 99%), akkor ezek a számformátumok elvészek. Szükségünk van arra, hogy (félig) állandó legyen.

Először keressük meg a "Összegösszeg" bejegyzést a értékek mezőbe, és kattintson rá. Megjelenik egy menü. Kiválasztjuk Érték mezőbeállítások… a menüből:

A Érték mezőbeállítások jelenik meg.

Kattints a Számformátum gomb és a szabvány Cellák formázása mező Megjelenik:

Tól Kategória lista, válassza ki (mondja) Számvitel, és húzza le a tizedesjegyek számát 0. Kattintson rendben néhányszor, hogy visszatérjen a PivotTable-hez ...

Mint látható, a számok megfelelően lettek formázva, mint dollárösszegek.

Miközben a formázás tárgya van, formázzuk az egész PivotTable-ot. Van néhány módja ennek. Használjunk egy egyszerű ...

Kattints a PivotTable eszközök / tervezés lapon:

Ezután a jobb alsó sarokban található nyíl lenyomása PivotTable stílusok a beépített stílusok nagy gyűjteményének megtekintéséhez:

Válasszon egyet, amely fellebbez, és nézze meg az eredményt a PivotTable-ben:

Egyéb opciók

Dátumokkal is dolgozhatunk. Most általában sok, sok dátum szerepel egy olyan tranzakciós listán, mint amilyet elkezdtünk. De az Excel lehetőséget biztosít az adatok csoportosítására nap, hét, hónap, év stb. Szerint. Nézzük meg, hogyan történik ez.

Először távolítsa el a „Fizetési mód” oszlopot a Oszlopcímkék mezőbe (egyszerűen húzza vissza a mezőlistára), és cserélje ki azt a „Foglalás dátuma” oszlopra:

Amint láthatod, ez a PivotTable-ot azonnal használhatatlanná teszi, megadva nekünk egy oszlopot minden tranzakció dátumára vonatkozóan - egy nagyon széles asztal!

A javításhoz kattintson a jobb gombbal bármelyik dátumra, és válassza a lehetőséget Csoport… a helyi menüből:

Megjelenik a csoportosítási mező. Kiválasztjuk hónapok és kattintson az OK gombra:

Voálá! A sokkal hasznosabb 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 kézzel készítettek.)

Egy másik jó dolog, amiről tudomásul kell venni, hogy több sorfejléc (vagy oszlopfejléc) is lehet:

… Ami így néz ki ... .

Hasonló dolgot tehet az oszlopfejlécekkel (vagy akár a szűrőkkel is).

Egyszerűen megtartva a dolgokat, nézzük meg, hogyan kell ábrázolni átlagolt értékek helyett az összegzett értékeket.

Először kattintson az „Összeg összege” elemre, majd válassza a lehetőséget Érték mezőbeállítások… a megjelenő helyi menüből:

Ban,-ben Az érték mező összegzése listát a Érték mezőbeállítások mezőben válassza ki a lehetőséget Átlagos:

Míg itt vagyunk, változtassuk meg Egyéni név, az „Összeg átlagától” egy kicsit tömörebbé. Írjon be valamit, mint az „Átlagos”:

Kattints rendben, és nézd meg, hogy néz ki. Figyeljük meg, hogy az összes érték az összesített összegekről az átlagokra változik, és a táblázat címe (felső bal oldali cella) „Átlag” -ra változott:

Ha úgy tetszik, még ugyanazon PivotTable-on is lehetnek összegek, átlagok és számok (számít = hány értékesítés volt)!

Íme a lépések ahhoz, hogy valami hasonlót hozzon létre (egy üres PivotTable-ről kezdve):

1. Húzza az „Eladó” -ta a Oszlopcímkék
2. Húzza le az „Összeg” mezőt a értékek háromszor
3. Az első „Mennyiség” mezőben változtassa meg az egyéni nevet „Teljes” értékre és a számformátumot Számvitel (0 tizedesjegy)
4. A második „Összeg” mezőben változtassa meg az egyéni nevét „Átlagos” -ra, annak funkciójára Átlagos és ez a számformátum Számvitel (0 tizedesjegy)
5. A harmadik „Mennyiség” mezőben változtassa meg a nevét „Számlálás” -ra és annak funkcióját Számol
6. Húzza az automatikusan létrehozott fájlt mező Oszlopcímkék nak nek sorcímkék

Íme, amit végül a következővel találunk:

Összesen, átlagos és számíthat ugyanazon PivotTable-re!

Következtetés

A Microsoft Excel által létrehozott PivotTables számos, sok más funkcióval és opcióval rendelkezik - túl sok ahhoz, hogy egy ilyen cikkben szerepeljen. A PivotTables potenciáljának teljes lefedéséhez szükség van egy kis könyvre (vagy egy nagy weboldalra). A bátor és / vagy geeky olvasók könnyebben felfedezhetik a PivotTables-et: Egyszerűen kattintson jobb gombbal mindent, és nézze meg, milyen lehetőségek állnak rendelkezésére. A két szalaglap is van: PivotTable eszközök / beállítások és Tervezés.  Nem számít, hogy hibát követ el - könnyű a PivotTable törlése és újraindítás - a Lotus 1-2-3 régi DOS-felhasználói lehetőségei soha nem voltak.

Ha az Office 2007 programban dolgozik, érdemes megnézni a PivotTable Excel 2007 programban való létrehozásáról szóló cikkünket.

Olyan Excel munkafüzetet is mellékeltünk, amelyet letölthet a PivotTable képességeinek gyakorlására. 97-től kezdődően az Excel összes verziójával kell működnie.

Töltse le a gyakorlati Excel munkafüzetünket