A PivotTables mestere
GYAKORLATI ÜLÉS №2
A pivot táblákon alapuló adatelemzés
1 Összefoglaló táblák fogalma
Az összefoglaló táblázat (CT) lehetővé teszi, hogy gyorsan törölje a szükséges adatokat a nagy adatbázisokból, mivel képes különböző műveletek (összefoglaló, válogató és szűrő) egyidejű elvégzésére. A CT eszköz használata nagy táblák esetén ajánlott, ahol oszlopokban és sorokban (vagy sorokban) különböző értékek ismétlődnek.
PivotTable varázsló
A CT elkészítéséhez és módosításához használja az összefoglaló táblázatok Mesterét. A Data → Pivot table parancs segítségével hívható. Összefoglaló táblázat létrehozása előtt el kell távolítania az összes korábban létrehozott részösszeget és átfedő szűrőt.
A CT konstrukcióját négy lépésben végezzük (lépések):
Az első lépés az asztal adatforrásának kiválasztása. A CT létrehozása az alábbi források valamelyikén alapulhat:
a) a Microsoft Excel minden lapján vagy adatbázisában.
b) egy külső adatforrás, amely lehet adatbázis, szöveges fájl vagy bármilyen más forrás, kivéve a Microsoft Excel munkafüzetét.
c) a konszolidáció több tartományában, i. egy vagy több Excel-lap több területén. Ebben az esetben a listáknak és a lapoknak ugyanolyan sor- és oszlopfejlécekkel kell rendelkezniük.
d) egy másik összefoglaló táblázatban, amely felhasználható egy új CT létrehozására. Az új CT azonos kezdeti adatokhoz kapcsolódik. A CT forrásadatok frissítésekor egy másik CT is frissül.
A második lépésben a párbeszédpanel meghatározza a listát vagy az adatbázisokat, amelyek összeolvadnak.
Általában a teljes tartomány neve a következőképpen van megadva: [könyv_neve] név_lista_neve!
Ha a CT ugyanabban a könyvben van felépítve, mint a forrás táblában, akkor a könyv neve opcionális.
A harmadik lépésben egy CT-struktúrát hozunk létre a táblázat elrendezésében, és annak funkcióit definiáljuk. Az elrendezés az ablak közepén jelenik meg és a következő területekből áll: sor, oszlop, oldal és adatok. Az elrendezés jobb oldalán minden mezőnév (oszlopfejléc) megjelenik a forrás táblázat megadott időközönként.
Helyezze a mezőket az elrendezés egy bizonyos területére a bal egérgomb lenyomásával. Egy mező eltávolításához az elrendezés területéről húzza ki az elrendezésből. Egy mező törlése az összes függő változót elrejti a CT-ben, de ez nem érinti az eredeti adatokat.
A mezők oszlopában, sorában, oldalában minden mezőt csak egyszer lehet elhelyezni, és az adatterületen meg lehet ismételni különböző összefoglaló funkciókkal.
Az elrendezés minden területe, ahol a forrástábla területei találhatók, a célja, amely meghatározza a CT megjelenését és funkcióit:
A példa összefoglaló táblázatok ismerete.
1. Hozzon létre egy értékesítési táblát a könyv első lapján:
A táblázat azt mutatja, hogy ki, mi, mikor és mennyi eladott. Összefoglaló táblázat létrehozásával megtudjuk, hogy az egyes eladók mennyi eladása mennyi.
1. Kattintson az A1 cellára, hogy az Excel megértse, milyen adatokkal fogunk dolgozni.
2. Válassza ki a Data → Pivot Table menüpontot. Ezután párbeszédablak jelenik meg, az összefoglaló táblázat beállításához.
3. Az első párbeszédablakban meg kell adni a pivot táblázat paramétereit. Ne módosítson semmit, és kattintson a Tovább gombra.
4. A második párbeszédpanel megkéri az adatlapra vonatkozó adatokat. Maga az Excel magában foglalja az egész táblázatot (mivel az A1 cellán található a kurzor), ne változtassunk semmit itt, és kattintsunk a Next gombra.
5. A harmadik párbeszédablakban meg kell adnia, hogy hol hozza létre a pivot táblát. Jelölje be a Meglévő lap jelölőnégyzetet, majd kattintson az F1 cellára, és kattintson a Befejezés gombra.
Most a lapon van egy összefoglaló táblázat és egy mezők listája.
A PivotTable űrlap három területet tartalmaz a mezők húzásához: oszlopokhoz, sorokhoz és adatokhoz.
A PivotTable mezőtábla párbeszédpanelen húzza a Vezetéknév mezőt a Húzási és vázlatmezők mezőbe. Az összefoglaló táblázat formája kissé megváltozott.
Az Excel kiválasztotta a Név oszlop összes egyedi értékét, és elhelyezte őket a jövőbeli jelentésünk soraiban.
Most húzza az Összeg mezőt az adatterületre.
Az Excel kiszámolta az értékesítési összeget minden egyes eladónak. Azonban a PivotTable képességek jobb megértéséhez az oszlopok területét is jelezzük. Ez a terület most már rejtve látszik, de valójában látható. Vigye át a Dátum mezőt a jelentés jobb felső sarkába, itt:
Amint kiadja az egérgombot, a jelentés azonnal megváltozik, és bemutatja az értékesítés dinamikáját naponta minden egyes eladó számára.
A jelentés túlságosan részletesnek bizonyult. Csoportosítsa azt hónapok alatt.
1. Kattintson jobb gombbal a jelentés Dátum mezőjébe, a megjelenő menüben válassza a Csoport és struktúra elemet, majd a Csoportot.
2. A következő párbeszédablakban az Excel arra kéri, hogy határozzuk meg, hogyan kell csoportosítani és azonnal felajánlani a hónapot (kiemelve). Ne módosítson semmit, és kattintson az OK gombra.
Most a jelentés eladási dátuma hónapra van csoportosítva.
Ha egy jelentést szeretne módosítani, például egy sor mező cseréjéhez, egyszerűen húzza ezt a mezőt a jelentéstől. Ezután mozgasson egy másik mezőt, például a Termék mezőben.
A termékek értékesítéséről szóló jelentést hónapok szerint kapta meg.
De mi történik, ha a Dátum mezőt a Vezetéknévvel helyettesítjük.
A pivot asztalok lehetősége korlátlan. Szerezd meg a következő pivot táblát: