Hogyan készítsünk kimutatási táblázatot az Excelben az adatok szeleteléséhez és felkockázásához

Microsoft Excel 2016, az Office programcsomagban található, a legújabb és legjobb táblázatkezelő alkalmazás Redmondból, és jó oka van annak, hogy sok számtörő ezt használja minden számmegőrző szükségletére. Míg az Excel kiválóan alkalmas egyszerű táblázatok készítésére a kiadások nyomon követésére vagy naptárak készítésére, akkor ez a sajátja, amikor szeletelni kell, majd összetett adatokat mutat be. Itt bemutatjuk, hogyan hozhat létre pivot táblát az Excelben, hogy kihasználhassa az alkalmazás egyik leghatékonyabb eszközét.

Mielőtt elkezdenénk, pontosan mire is jók a pivot táblák? Egyszerűen fogalmazva, a pivot táblák lehetővé teszik, hogy ugyanazokat az adatokat különböző módokon és különböző szögekből tekintse meg, így könnyebbé válik a mélyreható elemzés és a fontos trendek felismerése. Amikor például az értékesítési eredményeket értékeli, érdemes lehet egy személyre, egy adott termékre vagy egy adott időkeretre nézni. A pivot táblával létrehozhat egy információkészletet, majd könnyedén átállíthatja a fókuszt egyik dologról a másikra – ez az elemzés, amelyet unalmas lenne manuálisan elvégezni.

Vegye figyelembe, hogy szüksége lesz a az Excel 2016 teljes verziója pivot táblák használatához. Az univerzális Windows platform (UWP) Az Excel Windows 10 verziója nem tartalmazza ezt és sok mást fejlett Excel funkciók. Végül, ha ki szeretné próbálni az Excel új funkcióit, csatlakozzon a Office Insider program és talán az első, aki megtapasztal egy új pivot tábla funkciót.

1. lépés: Készítse elő adatait

Az Excel kimutatástábláinak használatának talán legfontosabb lépése az adatok gondos rendszerezése. Ennek legegyszerűbb módja a használata Excel táblázatok, amely lehetővé teszi olyan sorok hozzáadását, amelyek minden alkalommal megjelennek a kimutatásban, amikor megnyomja a frissítés gombot. De legalább azt szeretné, hogy az adatok táblázatos formában legyenek informatív oszlopfejlécekkel és konzisztens adatokkal az oszlopokon belül.

Tegyük fel például, hogy elemezni szeretné a vállalata értékesítési adatait. Hat értékesítője van, akik három terméket értékesítenek számos régióban. Termékeit minden vásárló számára személyre szabjuk, ezért az árak eltérőek. Íme egy minta táblázat fiktív információkkal, amelyek segítségével képet kaphat arról, hogyan lehet az adatokat pivot táblát szem előtt tartva rendszerezni.

A táblázat egyszerűen úgy jött létre, hogy először beírta az adatokat, majd kijelölte a teljes tartományt, majd a Beszúrás > Táblázat menüpontot választotta. Ismétlem, nem kell megtennie ezt a lépést, de akkor ajánlott, ha később több adatsort szeretne hozzáadni, és megkönnyíti a kimutatás táblázatának frissítését.

Mark Coppock/Digitális trendek

2. lépés: Próbáljon ki egy ajánlást

Az Excel tele van remek trükkökkel, amelyek megkönnyítik az adatokkal való munkát, és amikor csak lehetséges, megpróbálja kitalálni, mit szeretne elérni, majd automatikusan végrehajt néhány lépést. Ezt a segítőkészséget itt az Ajánlott kimutatások eszköze mutatja be, amely áttekinti az Ön adatait, és logikai lehetőséget kínál a dolgok elemzésére és bemutatására.

Az ajánlott kimutatástáblázat használatához egyszerűen lépjen a Beszúrás > Javasolt kimutatások menüpontra. Az Excel bemutat néhány lehetőséget, amelyeket érdemes megfontolni. Példánkban az Excel 10 különböző pivot tábla létrehozását kínálja, amelyek számos különböző szemszögből tekintik meg értékesítési adatainkat. Vegye figyelembe, hogy az oszlopok címkézése számít; Az Excel beolvassa ezeket a fejléceket, és olyan javaslatokat kínál, amelyek a legértelmesebbek. Ha például az eladási árakat szeretné nézni, ne használja a „költség” kifejezést, mert az Excel ennek alapján fogja meghozni az ajánlást.

Az egyik javaslat az „Összes értékesítés vevőnkénti összege”. Ha ezt a lehetőséget választjuk, akkor az Excel folytatja a pivot tábla létrehozását.

Mark Coppock/Digitális trendek

Figyelje meg, hogy a pivot tábla csak azokat az adatokat jeleníti meg, amelyek jelen elemzésünk szempontjából relevánsak. A jobb oldalon találja azokat a feltételeket, amelyek alapján az Excel létrehozta a kimutatást a Kimutatásmezők párbeszédpanelen. A testreszabás következő szakaszában bemutatjuk, hogy ezek a mezők mit jelentenek.

3. lépés: A pivot tábla testreszabása

A párbeszédpanel minden eleme fontos a pivot tábla működésének meghatározásához. Kattintson a konfigurációs fogaskerékre, hogy módosítsa a párbeszédpanel megjelenését az Ön számára legmegfelelőbbre.

Mezők kiválasztása

Itt választhatja ki, hogy mely oszlopokat vegye fel a kimutatástáblázatba. Az, hogy ezek az adatok hogyan jelennek meg a kimutatástáblában, az általuk képviselt adattípus – Excel – határozza meg ki fogja találni, hogy adjon-e hozzá egy oszlopot a kimutatáshoz, vagy adja hozzá a mező adatait a asztal. Például, ha a „Tételek” lehetőséget választja, az Excel azt feltételezi, hogy hozzá szeretné adni az egyes ügyfelekhez tartozó cikkek számát.

Mark Coppock/Digitális trendek

Másrészt, ha a „Dátum” lehetőséget választja, az Excel elhelyezi az adatokat a táblázatba, és aszerint rendezi az eladásokat, hogy azok bekövetkeztek.

Mark Coppock/Digitális trendek

Miközben a saját kimutatástábláin dolgozik, kísérletezhet, hogy megnézze, hogyan befolyásolja a mezők hozzáadása és eltávolítása a megjelenített adatokat. Látni fogja, hogy az Excel remek munkát végez az értelmes kijelölések kiválasztásában, de megváltoztathatja a dolgokat, ha az Excel választásainak nincs értelme.

Szűrők

Ha korlátozni szeretné, hogy mely adatok jelenjenek meg, húzza át az elemeket a mezőkiválasztási listából a „Szűrők” szakaszba. Például, ha áthúzza az „Ügyfél” elemet a „Szűrők” szakaszba, egyszerűen csak egy vagy egy kiválasztott ügyfél adatait jelenítheti meg.

Mark Coppock/Digitális trendek

Oszlopok

Ha a mezőket az „Oszlopok” szakaszba húzza, kibővítheti az adatok jelentésének módját. Ha egy mezőt ebbe a szakaszba húz, az Excel megpróbálja kitalálni, hogyan szeretné megjeleníteni az adatokat. Például, ha áthúzza a „Dátum” elemet az „Oszlopok” szakaszba, akkor az Excel az eladásokat a leglogikusabb időkeretre összesítve jeleníti meg, ami ebben az esetben havi. Ez hasznos lenne, ha az elsődleges szempont az lenne, hogy mennyit adtak el havonta, figyelembe véve az ügyfelek vásárlási szokásait.

Mark Coppock/Digitális trendek

Sorok

Hasonlóképpen húzhat mezőket a „Sorok” szakaszba, hogy különböző adatokat ágyazzon be a kimutatási táblázat soraiba. Ismét, ha a „Dátum” elemet a „Sorok” részbe húzzuk, az Excel havi bontásban bontja le az eladásokat vásárlók szerint, de az adatokat vevő szerint összesíti, nem pedig hónaponként, mint az előző példában. Ebben az esetben leginkább amiatt aggódik, hogy mennyit adott el az egyes ügyfeleknek, de szeretné észrevenni az időalapú trendeket is.

Mark Coppock/Digitális trendek

Értékek

Végül az „Értékek” szakasz határozza meg, hogyan elemzi adatait. Eddigi összes példánkban a teljes értékesítést vizsgáltuk. Ha ebben a szakaszban a lefelé mutató nyílra kattint, az értékmező beállításait úgy konfigurálhatja, hogy egy másik számszerű számítást nézzen meg.

Mark Coppock/Digitális trendek

Tegyük fel például, hogy a teljes értékesítés helyett az értékesítési átlagokat szeretné nézni. Egyszerűen válassza az „Átlag” lehetőséget az értékmező beállításai párbeszédpanelen. Beállíthatja a számformátumot is, hogy az eredmények a legértelmesebbek legyenek.

Mark Coppock/Digitális trendek

Most ahelyett, hogy figyelembe vennénk a teljes értékesítést vásárlónként, majd egy végösszeget számítanánk ki, a vállalatonkénti átlagos eladásokat, majd a vállalaton belüli átlagos eladásokat vizsgáljuk. Ez hasznos lenne annak értékelésében, hogy például mely ügyfelek az átlag feletti vagy alatti eladások, és ezért melyek érdemlik a legtöbb (vagy legkevesebb) figyelmet. Példánkban talán az Acme Engineering és a Jones Roofing nem érdemel akkora értékesítési figyelmet, mint a többi.

Nyilvánvaló, hogy a pivot táblák számos lehetőséget kínálnak az adatok egyszerű szeletelésére és felkockázására. A pivot táblák hatékony használatának trükkje az, hogy a beállítások alkalmazásának megkezdése előtt döntse el, hogy pontosan mit szeretne látni. Győződjön meg arról is, hogy adatai megfelelnek a táblázat elrendezésének és a fejlécek elnevezésének. Minél körültekintőbben állítja be a dolgokat, annál hatékonyabbak lesznek a pivot táblák.

4. lépés: Készítse el saját pivot tábláit a semmiből

Miután végigjátszotta a pivot table funkciót, és megértette, hogy a különböző lehetőségek hogyan hatnak az adatokra, elkezdheti a pivot tábla létrehozását a semmiből. A folyamat hasonló az ajánlás használatához, csak a Beszúrás > Kimutatás menübe kell lépnie, majd első lépésként manuálisan kiválasztja az adatokat.

Esetünkben ez a Table1 kiválasztását jelenti, de választhatunk egy adattartományt is, vagy külső adatforrásból is lekérhetünk. Eldönthetjük azt is, hogy szeretnénk-e új munkalapot készíteni, vagy a pivot táblát az adataink mellé, a meglévő munkalap egy bizonyos pontjára helyezzük.

Mark Coppock/Digitális trendek

Miután kiválasztottuk, egy üres pivot tábla és a Kimutatásmezők párbeszédpanel jelenik meg.

Mark Coppock/Digitális trendek

A saját pivot tábla létrehozása ezután csak egy egyszerű feladat: kiválasztjuk a mezőket, és meghatározzuk, hogyan szeretnénk az adatokat kiszámítani és megjeleníteni. Tegyük fel, hogy látni szeretnénk, hogyan teljesítettek az értékesítők havonta, az év végösszegével. Kiválasztjuk az „Értékesítő”, „Dátum” és „Összes értékesítés” mezőket, húzzuk az „Értékesítő” mezőt a „Szűrők” szakaszba, és beállítjuk a pénznemként megjelenítendő értékeket. Az Excel automatikusan hozzáadja a megfelelő dátumelemeket a „Sorok” részhez, és feltételezi, hogy összegeket szeretnénk látni.

Mark Coppock/Digitális trendek

Alapértelmezés szerint az Excel az összes értékesítő összes adatát megjeleníti. Kiválaszthatunk egyetlen értékesítőt is, aki csak az ő adatait látja. Ebben az esetben azt látjuk, hogy Judy French mindössze három hónap alatt értékesített, annak ellenére, hogy összesített eladásai jelentősek voltak. Ez azt jelentheti, hogy az értékesítési folyamata hosszabb volt, mert halak helyett bálnákat keresett – ez értékes következtetés, ha pontos. Talán jó ötlet lenne beruházni egy asszisztensbe, hogy segítsen Judynak gyorsabban lezárni az eladásait.

Mark Coppock/Digitális trendek

5. lépés: Fektessen be egy kis tanulásba

Ha szeretnél kapni igazán jól használja az Excel pivot tábláit, fordítson egy kis időt a különféle adattípusok használatának megismerésére. A Microsoft saját képzési forrásokat kínál és van egy sereg külső oktatók megfontolni.

Általában azonban ez azt jelenti, hogy olyan módon kell beleásni magát az Excelbe, amely túlmutat ezen útmutató keretein. Mindazonáltal remélhetőleg most már látni fogja, hogy a pivot táblák milyen hatékony eszközt jelenthetnek az adatok elemzésében, és viszonylag könnyű elkezdeni mindaddig, amíg az adatok a megfelelő típusra vannak konfigurálva asztal. És nem tudjuk eléggé hangsúlyozni, mennyire fontos tudni, hogy mit szeretne elérni a pivot táblával, mielőtt elkezdi.

Szerkesztői ajánlások

• Használja az Office-t? A Microsoft e változása miatt veszélybe kerülhet számítógépe
• Hogyan lehet jelszóval védeni egy Excel fájlt