Az Excel Pivot táblája az egyik leghatékonyabb eszköz, amely segít a nagy adatkészletek hatékony összefoglalásában és elemzésében.
A pivot tábla az Excel egyik leghatékonyabb és leghasznosabb adatösszesítő eszköze, amely lehetővé teszi nagy adatkészletek gyors összegzését, rendezését, átszervezését, elemzését, csoportosítását és megszámlálását.
A pivot tábla lehetővé teszi a táblában tárolt adatok elforgatását (vagy elforgatását), hogy azokat különböző perspektívákból tekintse meg, és világosan megértse a nagy adatkészleteket.
Ez az oktatóanyag lépésről lépésre útmutatást ad a pivot táblák Excelben történő létrehozásához és használatához.
Rendszerezze adatait
Pivot tábla létrehozásához az adatoknak táblával vagy adatbázis-struktúrával kell rendelkezniük. Tehát az adatokat sorokba és oszlopokba kell rendeznie. Az adattartomány táblázattá alakításához válassza ki az összes adatot, lépjen a „Beszúrás” fülre, és kattintson a „Táblázat” gombra. A Táblázat létrehozása párbeszédpanelen kattintson az „OK” gombra az adatkészlet táblázattá alakításához.
Ha Excel-táblázatot használ forrásadatkészletként a kimutatástábla létrehozásához, dinamikussá teszi a kimutatási táblát. Amikor bejegyzéseket ad hozzá vagy eltávolít az Excel-táblázatból, a pivotban lévő adatok ezzel együtt frissülnek.
Tételezzük fel, hogy az alábbiak szerint nagy adatkészlettel rendelkezik, amely több mint 500 rekordból és 7 mezőből áll. Dátum, régió, kiskereskedő típusa, cég, mennyiség, bevétel és nyereség.
Pivot Table beszúrása
Először jelölje ki az összes adatot tartalmazó cellát, lépjen a „Beszúrás” fülre, és kattintson a „PivotChart” gombra. Ezután válassza ki a „PivotChart & PivotTable” opciót a legördülő menüből.
Megnyílik egy PivotTable létrehozása párbeszédpanel. Az Excel automatikusan azonosítja és kitölti a megfelelő tartományt a „Táblázat/Tartomány mezőben”, ellenkező esetben kiválasztja a megfelelő táblázatot vagy cellatartományt. Ezután adja meg az Excel pivot tábla célhelyét, ez lehet „Új munkalap” vagy „Meglévő munkalap”, majd kattintson az „OK” gombra.
Ha az „Új munkalap” lehetőséget választja, egy új munkalap egy üres pivot táblával és egy pivot diagrammal jön létre egy külön munkalapon.
Készítse el kimutatási táblázatát
Az új munkalapon az Excel ablak bal oldalán egy üres pivot tábla, az Excel ablak jobb szélén pedig egy "Pivot Table Fields" panel látható, ahol minden lehetőségek a pivot tábla konfigurálásához.
A PivotTable Fields panel két vízszintes részre van osztva: Mezők szakasz (a panel teteje) és Elrendezés szakasz (a panel alja)
- A Mező szakasz felsorolja a táblázathoz hozzáadott összes mezőt (oszlopot). Ezek a mezőnevek a forrástáblázat összes oszlopának neve.
- A Elrendezés szakasz 4 területtel rendelkezik, azaz szűrők, oszlopok, sorok és értékek, amelyekkel elrendezheti és átrendezheti a mezőket.
Mezők hozzáadása a kimutatáshoz
Pivot tábla létrehozásához húzza át a mezőket a Mező szakaszból az Elrendezés szakasz területeire. A mezőket a területek között is húzhatja.
Sorok hozzáadása
Kezdjük azzal, hogy hozzáadjuk a „Cég” mezőt a Sorok szakaszhoz. Általában nem numerikus mezőket adnak hozzá az elrendezés Sor területéhez. Csak húzza a „Cég” mezőt a „Sor” területre.
A forrástáblázat „Cég” oszlopában szereplő összes cégnév sorként bekerül a pivot táblába, és növekvő sorrendbe rendeződik, de a Sorcímkék cellában található legördülő gombra kattintva módosíthatja a sorrendet.
Értékek hozzáadása lehetőségre
Hozzáadott egy sort, most adjunk hozzá egy értéket ahhoz a táblázathoz, hogy egydimenziós legyen. Egydimenziós pivot táblát készíthet úgy, hogy csak a sor vagy az oszlop címkéit és a hozzájuk tartozó értékeket adja hozzá a területekhez. Az értékterület az, ahol a számítások/értékek tárolódnak.
A fenti példa képernyőképen van egy sor cégünk, de szeretnénk megtudni az egyes cégek teljes bevételét. Ennek eléréséhez egyszerűen húzza át a „Bevétel” mezőt az „Érték” mezőbe.
Ha el szeretne távolítani bármely mezőt a Területek részből, egyszerűen törölje a jelölést a Mezők részben a mező melletti négyzetből.
Most van egy egydimenziós táblázatunk a cégekről (sorcímkék) a bevételek összegével együtt.
Oszlop hozzáadása
Kétdimenziós táblázat
A sorok és oszlopok együttesen kétdimenziós táblázatot hoznak létre, és kitöltik a cellákat az értékek harmadik dimenziójával. Tegyük fel, hogy pivot táblát szeretne létrehozni úgy, hogy a cégek neveit sorokként listázza, és oszlopokat használ a Dátumok megjelenítéséhez, és a cellákat kitölti a teljes bevétellel.
Ha hozzáadja a „Dátum” mezőt az „Oszlop” területhez, az Excel automatikusan hozzáadja a „Negyedéves” és „Évek” mezőket az oszlopmezőkhöz az adatok kiszámítása és jobb összegzése érdekében.
Most van egy kétdimenziós táblázatunk három dimenziós értékkel.
Szűrők hozzáadása
Ha a „Régió” alapján szeretné kiszűrni az adatokat, a „Régió” mezőt húzza át a Szűrő területre.
Ez egy legördülő menüt ad a kimutatástáblázat fölé a kiválasztott „Szűrőmezővel”. Ezzel régiónként minden évre kiszűrheti a cégek bevételét.
Alapértelmezés szerint az összes régió ki van jelölve, törölje a jelölést, és csak azt a régiót válassza ki, amely szerint szűrni szeretné az adatokat. Ha több bejegyzés alapján szeretné szűrni a táblázatot, jelölje be a „Több elem kiválasztása” jelölőnégyzetet a legördülő menü alján. És válassza ki a több régiót.
Az eredmény:
Válogatás
Ha növekvő vagy csökkenő sorrendben szeretné rendezni a táblázat értékét, kattintson a jobb gombbal bármelyik cellára a Bevétel összege oszlopban, majd bontsa ki a „Rendezés” elemet, és válassza ki a sorrendet.
Az eredmény:
Csoportosítás
Tegyük fel, hogy a kimutatástáblájában hónapok szerint vannak felsorolva az adatok, de nem szeretné havonta látni, hanem át szeretné rendezni az adatokat pénzügyi negyedévekbe. Ezt megteheti a pivot táblában.
Először válassza ki az oszlopokat, és kattintson rá jobb gombbal. Ezután válassza a „Csoport” lehetőséget a legördülő menüből.
A Csoportosítás ablakban válassza a „Negyedek” és az „Évek” lehetőséget, mert azt szeretnénk, hogy ezeket minden év pénzügyi negyedéveibe rendezzék. Ezután kattintson az „OK” gombra.
Mostantól adatait minden év pénzügyi negyedéveibe rendezik.
Értékmező beállításai
Alapértelmezés szerint a pivot tábla a numerikus értékeket összegzi az Összeg függvénnyel. De módosíthatja az Értékek területen használt számítás típusát.
Az összefoglaló funkció megváltoztatásához kattintson a jobb gombbal a táblázat bármely adatára, kattintson az „Értékek összegzése szerint” lehetőségre, és válassza ki a kívánt lehetőséget.
Alternatív megoldásként kattintson a mezőrész értékterületén a „Sum of...” melletti lefelé mutató nyílra, és válassza az „Értékmező beállításai” lehetőséget.
Az „Értékmező beállításai” részben válassza ki a függvényt az adatok összegzéséhez. Ezután kattintson az „OK” gombra. Példánkban a „Számlálás” lehetőséget választjuk a nyereség számának számításához.
Az eredmény:
Az Excel pivot táblái lehetővé teszik az értékek különböző módokon történő megjelenítését is, például a végösszegeket százalékban vagy az oszlopok összegét százalékban vagy a sorösszegeket százalékos formában, vagy az értékeket a legkisebbtől a legnagyobbig és fordítva, még sok más módon is megjelenítheti.
Az értékek százalékos megjelenítéséhez kattintson a jobb gombbal a táblázat tetszőleges pontjára, majd kattintson az „Értékek megjelenítése másként” lehetőségre, és válassza ki a kívánt lehetőséget.
Ha az „Oszlop összesen %-át” választjuk, az eredmény a következő lesz:
Frissítse a Pivot táblát
Bár a kimutatástábla-jelentés dinamikus, a forrástáblázat módosításakor az Excel nem frissíti automatikusan a kimutatásban lévő adatokat. Az adatok frissítéséhez manuálisan kell „frissíteni”.
Kattintson bárhová a pivot táblázatban, lépjen az „Elemzés” fülre, majd kattintson a „Frissítés” gombra az Adatcsoportban. Az aktuális pivot tábla frissítéséhez a munkalapon kattintson a „Frissítés” lehetőségre. Ha frissíteni szeretné a munkafüzet összes pivot tábláját, kattintson az „Összes frissítése” gombra.
Alternatív megoldásként kattintson a jobb gombbal a táblázatra, és válassza a „Frissítés” lehetőséget.
Ez az. Reméljük, hogy ez a cikk részletes áttekintést nyújt az Excel kimutatástábláiról, és segít létrehozni egyet.