Ha elolvassa ezt az oktatóanyagot, nagy az esélye, hogy hallott az Excel pivot tábláról (vagy akár használta is). Ez az Excel egyik legerősebb funkciója (nem vicc).
A Pivot tábla használatának legjobb része az, hogy még ha nem is tud semmit az Excelben, akkor is nagyon félelmetes dolgokat tehet vele, ha nagyon alaposan megérti.
Kezdjük el.
Kattints ide letölteni a mintaadatokat, és követni.
Mi az a pivot tábla, és miért kell érdekelnie?
A Pivot Table a Microsoft Excel egyik eszköze, amely lehetővé teszi a hatalmas adathalmazok gyors összefoglalását (néhány kattintással).
Még akkor is, ha teljesen új az Excel világában, könnyen használhatja a kimutatástáblát. A jelentések létrehozása olyan egyszerű, mint a sorok/oszlopok fejléceinek húzása.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Ez az értékesítési adat, amely ~ 1000 sorból áll.
Ez tartalmazza az értékesítési adatokat régió, kiskereskedő típusa és vevő szerint.
Most a főnöke szeretne tudni néhány dolgot ezekből az adatokból:
- Mennyi volt a teljes értékesítés a déli régióban 2016 -ban?
- Melyek az eladások szerint az öt legjobb kiskereskedő?
- Hogyan viszonyult a The Home Depot teljesítménye más déli kiskereskedőkhöz?
Mehet, és használhatja az Excel funkciókat, hogy megadja a választ ezekre a kérdésekre, de mi van akkor, ha a főnöke hirtelen további öt kérdésből áll.
Vissza kell térnie az adatokhoz, és új képleteket kell létrehoznia minden alkalommal, amikor változás történik.
Itt igazán hasznosak az Excel pivot táblázatok.
A Pivot Table néhány másodpercen belül válaszol ezekre a kérdésekre (amint alább megtudja).
De az igazi előny az, hogy képes befogadni a finoman adatvezérelt főnökét, ha azonnal válaszol kérdéseire.
Ez olyan egyszerű, lehet, hogy szán néhány percet, és megmutatja a főnökének, hogyan kell ezt megtenni.
Remélhetőleg most már van ötlete arra, hogy miért olyan fantasztikusak a Pivot táblázatok. Menjünk előre, és hozzunk létre egy kimutatástáblát az adathalmaz segítségével (fent látható).
Pivot táblázat beszúrása az Excelbe
Íme a lépések a pivot tábla létrehozásához a fenti adatok alapján:
- Kattintson az adathalmaz bármelyik pontjára.
- Lépjen a Beszúrás -> Táblázatok -> Pivot táblázat menüpontra.
- A Pivot tábla létrehozása párbeszédpanelen az alapértelmezett beállítások a legtöbb esetben jól működnek. Íme néhány dolog, amit ellenőrizni kell:
- Táblázat/tartomány: Alapértelmezés szerint az adathalmaz alapján van kitöltve. Ha az adatok nem tartalmaznak üres sorokat/oszlopokat, az Excel automatikusan azonosítja a helyes tartományt. Ezt manuálisan módosíthatja, ha szükséges.
- Ha egy adott helyen szeretné létrehozni a kimutatástáblát, a „Válassza ki, hogy hová szeretné elhelyezni a kimutatást” pontban adja meg a helyet. Máskülönben egy új munkalap jön létre a Pivot táblával.
- Kattintson az OK gombra.
Amint az OK gombra kattint, új munkalap jön létre a kimutatástábla segítségével.
Amíg a Pivot tábla létrejött, nem lát benne adatokat. Csak a Pivot tábla nevét és egy sor utasítást láthatja a bal oldalon, és a Pivot táblázat mezőit a jobb oldalon.
Mielőtt belekezdenénk az adatok elemzésébe a pivot tábla segítségével, értsük meg, melyek azok a csavarok, amelyek Excel pivot táblát alkotnak.
Az Excel pivot táblázatának anyái és csavarjai
A pivot tábla hatékony használatához fontos, hogy ismerje a pivot táblát létrehozó összetevőket.
Ebben a részben a következőkről tudhat meg:
- Pivot Cache
- Értékek terület
- Sorok terület
- Oszlopok terület
- Szűrőterület
Pivot Cache
Amint létrehoz egy kimutatást az adatok felhasználásával, valami történik a háttérben. Az Excel pillanatképet készít az adatokról, és tárolja a memóriájában. Ezt a pillanatképet Pivot Cache -nak hívják.
Amikor különböző nézeteket hoz létre egy kimutatástábla segítségével, az Excel nem tér vissza az adatforráshoz, hanem a kimutatási gyorsítótár használatával gyors elemzi az adatokat, és megadja az összegzést/eredményeket.
A pivot gyorsítótár létrehozásának oka a pivot tábla működésének optimalizálása. Még akkor is, ha több ezer adatsorral rendelkezik, a pivot táblázat szupergyors az adatok összegzésében. Húzhat elemeket a sorok/oszlopok/értékek/szűrők mezőbe, és azonnal frissíti az eredményeket.
Megjegyzés: A pivot gyorsítótár egyik hátránya, hogy növeli a munkafüzet méretét. Mivel a forrásadatok másolata, a pivot tábla létrehozásakor az adatok másolata a Pivot Cache -ben tárolódik.
Olvass tovább: Mi a Pivot gyorsítótár és hogyan lehet a legjobban használni.
Értékek terület
Az Értékek terület tartalmazza a számításokat/értékeket.
Az oktatóanyag elején látható adatkészlet alapján, ha gyorsan szeretné minden hónapban kiszámítani az összes értékesítést régiónként, beszerezhet egy kimutatástáblát az alábbiak szerint (ezt az oktatóanyag későbbi részében látjuk) .
A narancssárgával kiemelt terület az Értékek terület.
Ebben a példában a havi összes értékesítés szerepel a négy régióban.
Sorok terület
Az Értékek terület bal oldalán található címsorok a Sorok területet alkotják.
Az alábbi példában a Sorok terület tartalmazza a régiókat (pirossal kiemelve):
Oszlopok terület
Az Értékek terület tetején található címsorok az Oszlopok területet alkotják.
Az alábbi példában az Oszlopok terület tartalmazza a hónapokat (pirossal kiemelve):
Szűrőterület
A Szűrők terület egy opcionális szűrő, amellyel tovább részletezheti az adatkészletet.
Például, ha csak a többsoros kiskereskedők eladásait szeretné látni, akkor válassza ki ezt a lehetőséget a legördülő menüből (az alábbi képen kiemelve), és a pivot tábla csak a többsoros kiskereskedők adataival frissül.
Adatok elemzése a pivot tábla segítségével
Most próbáljuk meg megválaszolni a kérdéseket a létrehozott Pivot táblázat segítségével.
Kattints ide letölteni a mintaadatokat, és követni.
Az adatok pivot tábla segítségével történő elemzéséhez el kell döntenie, hogyan szeretné látni az adatösszesítést a végeredményben. Például a bal oldali régiókat és a mellette lévő teljes értékesítést szeretné. Ha szem előtt tartja ezt az egyértelműséget, egyszerűen húzza át a megfelelő mezőket a Pivot táblázatban.
A Pivot Tabe mezők szakaszban vannak a mezők és a területek (az alábbiakban kiemelve):
A mezők a Pivot tábla számára használt háttér -adatok alapján jönnek létre. A Területek szakaszban helyezheti el a mezőket, és attól függően, hogy egy mező hová kerül, az adatok a Pivot táblázatban frissülnek.
Ez egy egyszerű drag and drop mechanizmus, ahol egyszerűen húzhat egy mezőt, és a négy terület egyikébe helyezheti. Amint ezt megteszi, megjelenik a munkalap pivot táblájában.
Most próbáljuk meg megválaszolni a menedzserének kérdéseit a Pivot Table használatával.
Q1: Mennyi volt a teljes értékesítés a déli régióban?
Húzza a Régió mezőt a Sorok területen és a Bevétel mezőt az Értékek területen. Ez automatikusan frissíti a munkalapon lévő pivot táblát.
Ne feledje, hogy amint elhagyja a Bevétel mezőt az Értékek területen, a bevétel összege lesz. Alapértelmezés szerint az Excel összesíti az adott régió összes értékét, és megjeleníti az összes értéket. Ha szeretné, módosíthatja ezt a Szám, Átlag vagy más statisztikai mutatókra. Ebben az esetben az összeg az, amire szükségünk volt.
A válasz erre a kérdésre 21225800 lenne.
2. kérdés Melyek az eladások szerint az öt legjobb kiskereskedő?
Húzza a Vevő mezőt a Sor területen és a Bevétel mezőt az értékek területen. Abban az esetben, ha vannak más mezők a terület részben, és el szeretné távolítani, egyszerűen válassza ki, és húzza ki belőle.
Pivot táblázatot kap az alábbiak szerint:
Vegye figyelembe, hogy alapértelmezés szerint az elemek (ebben az esetben az ügyfelek) ábécé sorrendben vannak rendezve.
Az öt legjobb kiskereskedő eléréséhez egyszerűen rendezheti ezt a listát, és használhatja az első öt vásárlónevet. Ezt csináld meg:
- Kattintson a jobb gombbal az Értékek terület bármely cellájára.
- Lépjen a Rendezés -> Rendezés a legnagyobbtól a legkisebbig menüpontra.
Ez egy rendezett listát ad az összes értékesítés alapján.
3. kérdés: Hogyan viszonyult a Home Depot teljesítménye más déli kiskereskedőkhöz?
Ehhez a kérdéshez sok elemzést végezhet, de itt csak próbáljuk összehasonlítani az eladásokat.
Húzza a Régió mezőt a Sorok területen. Most húzza az Ügyfél mezőt a Régió mező alatti Sorok területen. Amikor ezt megteszi, az Excel megérti, hogy először régió, majd a régiókon belüli ügyfelek szerint szeretné kategorizálni az adatait. Lesz valami az alábbiak szerint:
Most húzza a Bevétel mezőt az Értékek területen, és megkapja az egyes ügyfelek (valamint a teljes régió) értékesítését.
A kiskereskedőket az értékesítési adatok alapján rendezheti az alábbi lépések végrehajtásával:
- Kattintson a jobb gombbal arra a cellára, amely értékesítési értékkel rendelkezik bármely kiskereskedő számára.
- Lépjen a Rendezés -> Rendezés a legnagyobbtól a legkisebbig menüpontra.
Ez azonnal az összes kiskereskedőt az értékesítési érték szerint rendezné.
Most gyorsan áttekintheti a déli régiót, és megállapíthatja, hogy a Home Depot eladások száma 3004600 volt, és ez jobban sikerült, mint négy kiskereskedő a déli régióban.
Ma már több módja is van a macska bőrének. A Régiót a Szűrő területre is teheti, majd csak a Déli régiót.
Kattints ide a mintaadatok letöltéséhez.
Remélem, hogy ez az oktatóanyag alapvető áttekintést nyújt az Excel Pivot táblázatokról, és segít az első lépésekben.
Íme néhány további Pivot Table oktatóanyag, amelyek tetszhetnek:
- Forrásadatok előkészítése a kimutatástáblához.
- Feltételes formázás alkalmazása pivot táblázatban az Excelben.
- A dátumok csoportosítása az Excel pivot tábláiban.
- Számok csoportosítása az Excel kimutatásában.
- Az adatok szűrése a pivot táblázatban az Excelben.
- A szeletelők használata az Excel kimutatásban.
- Hogyan lehet az üres cellákat nullákkal helyettesíteni az Excel pivot táblákban.
- Excel Pivot táblázat számított mezők hozzáadása és használata.
- A pivot táblázat frissítése az Excelben.
