tartalom
Forgóasztalok az Excel egyik leghatékonyabb eszköze. Lehetővé teszik nagy mennyiségű adat különféle összefoglalóinak elemzését és összegzését néhány egérkattintással. Ebben a cikkben megismerkedünk a pivot táblákkal, megértjük, mik ezek, megtanuljuk létrehozni és testreszabni őket.
Ez a cikk az Excel 2010 használatával készült. A kimutatások koncepciója nem sokat változott az évek során, de a létrehozásuk módja kissé eltér az Excel minden új verziójában. Ha az Excel nem 2010-es verziójával rendelkezik, készüljön fel arra, hogy a cikkben szereplő képernyőképek eltérnek a képernyőn láthatóktól.
Egy kis történelem
A táblázatkezelő szoftver korai napjaiban a Lotus 1-2-3 szabálygömb. A dominanciája olyannyira teljes volt, hogy a Microsoft azon törekvése, hogy a Lotus alternatívájaként saját szoftvert (Excel) fejlesszen ki, időpocsékolásnak tűnt. Most gyorsan előre 2010-re! Az Excel jobban uralja a táblázatokat, mint a Lotus kód története során, és a még mindig Lotust használók száma a nullához közelít. Hogyan történhetett ez meg? Mi volt az oka az események ilyen drámai fordulatának?
Az elemzők két fő tényezőt azonosítanak:
- Először is a Lotus úgy döntött, hogy ez az újszerű, Windows nevű grafikus felhasználói felület csak egy múló hóbort, amely nem tart sokáig. Megtagadták a Lotus 1-2-3 Windows-verziójának elkészítését (de csak néhány évre), azt jósolván, hogy szoftverük DOS-os verziójára lesz minden, amire a fogyasztóknak szüksége lesz. A Microsoft természetesen kifejezetten Windowshoz fejlesztette ki az Excelt.
- Másodszor, a Microsoft bevezette az Excelben a PivotTables nevű eszközt, amely nem volt elérhető a Lotus 1-2-3-ban. Az Excelben kizárólagos PivotTable-ok annyira hasznosnak bizonyultak, hogy az emberek inkább ragaszkodtak az új Excel szoftvercsomaghoz, ahelyett, hogy a Lotus 1-2-3-mal folytatnák, amelyben nem voltak ilyenek.
A PivotTables a Windows sikerének általában véve alábecsülésével együtt a Lotus 1-2-3 halálmenetét játszotta, és bevezette a Microsoft Excel sikerét.
Mik azok a pivot táblák?
Tehát mi a legjobb módszer a kimutatások jellemzésére?
Egyszerűen fogalmazva, a pivot táblák egyes adatok összefoglalásai, amelyeket az adatok elemzésének megkönnyítésére hoztak létre. A manuálisan létrehozott összegekkel ellentétben az Excel kimutatások interaktívak. Létrehozásuk után könnyedén módosíthatja őket, ha nem adják azt a képet, amire számítottál. Néhány egérkattintással az összegek átfordíthatók, így az oszlopfejlécek sorfejlécekké válnak, és fordítva. A pivot táblákkal sok mindent megtehet. Ahelyett, hogy szavakkal próbálnánk leírni a pivot táblák összes jellemzőjét, egyszerűbb a gyakorlatban is bemutatni…
A kimutatástáblákkal elemzett adatok nem lehetnek véletlenszerűek. Nyers nyers adatoknak kell lenniük, például valamilyen listának. Ez lehet például a vállalat által az elmúlt hat hónapban végrehajtott eladások listája.
Tekintse meg az alábbi ábrán látható adatokat:
Vegye figyelembe, hogy ez nem nyers nyers adat, mivel már összefoglaltuk. A B3 cellában 30000 30000 dollárt látunk, ami valószínűleg James Cook januári összeredménye. Hol vannak akkor az eredeti adatok? Honnan jött a XNUMX XNUMX dollár? Hol van az eladások eredeti listája, amelyből ez a havi végösszeg származott? Nyilvánvaló, hogy valaki nagyszerű munkát végzett az elmúlt hat hónap összes értékesítési adatának rendszerezésében és rendezésében, és az általunk látott összesítési táblázatban való átalakításában. Szerinted mennyi ideig tartott? Óra? Tíz óra?
Az a tény, hogy a fenti táblázat nem pivot tábla. Máshol tárolt nyers adatokból kézzel készítették, és legalább néhány órát vett igénybe a feldolgozása. Csak egy ilyen összefoglaló táblázat hozható létre pivot táblákkal néhány másodperc alatt. Találjuk ki, hogyan…
Ha visszamegyünk az eredeti eladási listához, az valahogy így nézne ki:
Meglepődhet, hogy ebből a kereskedési listából pivot táblák segítségével néhány másodperc alatt elkészíthetünk egy havi értékesítési jelentést Excelben, amit fentebb elemeztünk. Igen, megtehetjük ezt és még többet!
Hogyan készítsünk pivot táblát?
Először győződjön meg arról, hogy van néhány forrásadat egy Excel-lapon. A pénzügyi tranzakciók listája a legjellemzőbb. Valójában bármi lehet egy lista: az alkalmazottak elérhetőségei, egy CD-gyűjtemény vagy a vállalat üzemanyag-fogyasztási adatai.
Tehát elindítjuk az Excelt… és betöltünk egy ilyen listát…
Miután megnyitottuk ezt a listát az Excelben, elkezdhetjük a pivot tábla létrehozását.
Válasszon ki egy cellát a listából:
Aztán a lapon beszúrás (Beszúrás) válassza ki a parancsot Pivot tábla (Pivot tábla):
Megjelenik egy párbeszédpanel Hozzon létre PivotTable-t (Pivot tábla létrehozása) két kérdéssel:
- Milyen adatokat kell használni egy új pivot tábla létrehozásához?
- Hová tegye a pivot táblát?
Mivel az előző lépésben már kiválasztottuk az egyik listacellát, a teljes lista automatikusan kiválasztásra kerül a pivot tábla létrehozásához. Vegye figyelembe, hogy választhatunk egy másik tartományt, egy másik táblát, és akár néhány külső adatforrást is, például Access vagy MS-SQL adatbázistáblát. Ezenkívül ki kell választanunk, hogy hova helyezzük el az új pivot táblát: új lapra vagy a meglévők valamelyikére. Ebben a példában a következő lehetőséget választjuk: Új munkalap (új lapra):
Az Excel létrehoz egy új lapot, és elhelyez egy üres pivot táblát:
Amint a pivot táblázat bármely cellájára kattintunk, egy másik párbeszédpanel jelenik meg: PivotTable mezőlista (Pivot tábla mezői).
A párbeszédpanel tetején található mezők listája az eredeti lista összes címének listája. A képernyő alján található négy üres terület lehetővé teszi, hogy megmondja a kimutatásnak, hogyan szeretné összefoglalni az adatokat. Amíg ezek a területek üresek, addig a táblázatban sincs semmi. Csak annyit kell tennünk, hogy a fejléceket a felső területről az alatta lévő üres területekre húzzuk. Ezzel egyidejűleg az utasításainknak megfelelően automatikusan létrejön egy pivot tábla. Ha hibázunk, eltávolíthatjuk a címsorokat az alsó területről, vagy másokat húzhatunk helyettük.
Terület Értékek (Jelenések) valószínűleg a legfontosabb a négy közül. Hogy melyik címsor kerül erre a területre, az határozza meg, hogy milyen adatok kerülnek összesítésre (összeg, átlag, maximum, minimum stb.) Ezek szinte mindig számértékek. Ezen a területen kiváló helyjelölt a címsor alatti adat Összeg (Költség) az eredeti táblázatunkból. Húzza ezt a címet a területre Értékek (Értékek):
Felhívjuk figyelmét, hogy a cím Összeg most pipával van megjelölve, és a területen Értékek (Értékek) egy bejegyzés jelent meg Összeg összege (Amount mező Összeg), jelezve, hogy az oszlop Összeg foglalta össze.
Ha magát a pivot táblát nézzük, látni fogjuk az oszlop összes értékének összegét Összeg eredeti asztal.
Tehát elkészült az első pivot táblánk! Kényelmes, de nem különösebben lenyűgöző. Valószínűleg több információt szeretnénk kapni adatainkról, mint amennyivel jelenleg rendelkezünk.
Térjünk rá az eredeti adatokra, és próbáljunk meg azonosítani egy vagy több oszlopot, amelyekkel ezt az összeget fel lehet osztani. Például elkészíthetjük a pivot táblánkat úgy is, hogy az eladások teljes összegét minden eladóra külön-külön számítjuk ki. Azok. sorok kerülnek hozzáadásra a pivot táblánkhoz a vállalat minden értékesítőjének nevével és teljes értékesítési összegével. Az eredmény eléréséhez csak húzza a címet eladó (értékesítési képviselő) a régióba sorcímkék (Húrok):
Egyre érdekesebb lesz! Kimutatásunk kezd formát ölteni…
Látod az előnyöket? Néhány kattintással létrehoztunk egy táblázatot, amelynek manuális létrehozása nagyon sokáig tartott volna.
Mit tehetünk még? Nos, bizonyos értelemben a pivot táblánk készen áll. Az eredeti adatokból hasznos összefoglalót készítettünk. Fontos információk már megérkeztek! A cikk további részében megvizsgálunk néhány módot összetettebb kimutatások létrehozására, valamint megtudjuk, hogyan lehet testreszabni azokat.
PivotTable beállítása
Először is létrehozhatunk egy kétdimenziós pivot táblát. Tegyük ezt meg az oszlopfejléc használatával Fizetési mód (Fizetési mód). Csak húzza a címet Fizetési mód a területre Oszlopcímkék (Oszlopok):
Megkapjuk az eredményt:
Nagyon jól néz ki!
Most készítsünk egy háromdimenziós táblázatot. Hogy nézne ki egy ilyen asztal? Lássuk…
Húzza a fejlécet Csomag (Komplex) a területre jelentésszűrők (Szűrők):
Jegyezd meg, hol van…
Ez lehetőséget ad a jelentés szűrésére a „Melyik üdülőkomplexumért fizettek” alapján. Például láthatunk eladók és fizetési módok szerinti bontást az összes komplexum esetében, vagy néhány egérkattintással megváltoztathatjuk a pivot tábla nézetét, és ugyanazt a bontást csak azok számára jeleníthetjük meg, akik megrendelték a komplexumot. Napkeresők.
Tehát, ha ezt jól érted, akkor pivot táblánkat háromdimenziósnak nevezhetjük. Folytassuk a beállítást…
Ha hirtelen kiderül, hogy a pivot táblában csak a csekkel és hitelkártyával történő fizetést (vagyis készpénz nélküli fizetést) kell megjeleníteni, akkor kikapcsolhatjuk a cím megjelenítését. Készpénz (Készpénz). Erre, mellé Oszlopcímkék kattintson a lefelé mutató nyílra, és törölje a jelölést a legördülő menüből Készpénz:
Lássuk, hogyan néz ki most a pivot táblánk. Mint látható, az oszlop Készpénz eltűnt előle.
PivotTable-ok formázása Excelben
A PivotTable-ok nyilvánvalóan nagyon hatékony eszköz, de az eredmények eddig kissé egyszerűnek és unalmasnak tűnnek. Például az általunk összeadott számok nem dollárösszegeknek tűnnek – csak számok. Javítsuk ki.
Csábító, hogy megtegye azt, amit ilyen helyzetben megszokott, és egyszerűen válassza ki a teljes táblázatot (vagy a teljes lapot), és használja az eszköztár szabványos számformázó gombjait a kívánt formátum beállításához. Ezzel a megközelítéssel az a probléma, hogy ha a jövőben megváltoztatja a pivot tábla szerkezetét (ami 99%-os eséllyel történik), akkor a formázás elveszik. Amire szükségünk van, az az, hogy ezt (majdnem) állandóvá tegyük.
Először is keressük meg a bejegyzést Összeg összege in Értékek (Értékek), és kattintson rá. A megjelenő menüben válassza ki az elemet Értékmező beállításai (Értékmező opciók):
Megjelenik egy párbeszédpanel Értékmező beállításai (Értékmező opciók).
sajtó számformátum (Számformátum), megnyílik egy párbeszédpanel. Cellák formázása (cellaformátum):
A listából Kategória (Számformátumok) válassza ki számvitel (Pénzügyi), és állítsa a tizedesjegyek számát nullára. Most nyomja meg néhányszor OKhogy visszatérjünk a pivot táblánkhoz.
Amint láthatja, a számok dollárösszegként vannak formázva.
Amíg a formázásnál tartunk, állítsuk be a teljes kimutatás formátumát. Ennek többféle módja van. Mi az egyszerűbbet használjuk…
Kattintson az PivotTable eszközök: Tervezés (Munka a kimutatástáblákkal: konstruktor):
Ezután bontsa ki a menüt a szakasz jobb alsó sarkában lévő nyílra kattintva PivotTable-stílusok (PivotTable Styles), hogy megtekinthesse a soron belüli stílusok kiterjedt gyűjteményét:
Válassza ki a megfelelő stílust, és nézze meg az eredményt a pivot táblázatban:
Egyéb kimutatások az Excelben
Néha az adatokat dátum szerint kell szűrnie. Például a kereskedéseink listáján sok-sok dátum szerepel. Az Excel eszközt biztosít az adatok nap, hónap, év stb. szerinti csoportosítására. Lássuk, hogyan készült.
Először távolítsa el a bejegyzést. Fizetési mód a régióból Oszlopcímkék (Oszlopok). Ehhez húzza vissza a címlistába, és a helyére mozgassa a címet Foglalt időpont (foglalás dátuma):
Amint látja, ez átmenetileg használhatatlanná tette a pivot táblánkat. Az Excel külön oszlopot hozott létre minden egyes kereskedési dátumhoz. Ennek eredményeként egy nagyon széles asztalt kaptunk!
Ennek kijavításához kattintson jobb gombbal bármelyik dátumra, és válassza ki a helyi menüből GROUP (Csoport):
Megjelenik a csoportosítás párbeszédpanel. Mi választunk Hónapok (Hónapok), és kattintson OK:
Voálá! Ez a táblázat sokkal hasznosabb:
Ez a táblázat egyébként szinte megegyezik a cikk elején láthatóval, ahol manuálisan állították össze az eladási összegeket.
Van még egy nagyon fontos pont, amit tudnod kell! Nem egy, hanem több szintű sor (vagy oszlop) fejlécet is létrehozhat:
…és így fog kinézni…
Ugyanezt megtehetjük oszlopfejlécekkel (vagy akár szűrőkkel is).
Térjünk vissza a táblázat eredeti formájához, és nézzük meg, hogyan jelenítsünk meg átlagokat összegek helyett.
A kezdéshez kattintson a gombra Összeg összege és a megjelenő menüből válassza ki a lehetőséget Értékmező beállításai (Értékmező opciók):
A lista Összegezze az érték mezőt (Művelet) a párbeszédpanelen Értékmező beállításai (Értékmező opciók) válassza ki Átlagos (Átlagos):
Ugyanakkor, amíg itt vagyunk, változzunk CustomName (Egyéni név) -val Összeg átlaga (Amount mező Összeg) valami rövidebbre. Írjon be ebbe a mezőbe valami hasonlót Átlagos:
nyomja meg OK és nézd meg, mi történik. Vegye figyelembe, hogy az összes érték összegről átlagra változott, és a táblázat fejléce (a bal felső cellában) a következőre módosult. Átlagos:
Ha szeretné, azonnal megkaphatja az összeget, az átlagot és a számot (eladásokat) egy pivot táblában.
Íme egy lépésről lépésre bemutatott útmutató, hogyan kell ezt megtenni, kezdve egy üres pivot táblával:
- Húzza a fejlécet eladó (értékesítési képviselő) a régióba Oszlopcímkék (Oszlopok).
- Húzza háromszor a címet Összeg (Költség) területre Értékek (Értékek).
- Az első mezőre Összeg változtassa meg a címet Végösszeg (összeg), és a számformátum ebben a mezőben a következő számvitel (Pénzügyi). A tizedesjegyek száma nulla.
- Második mező Összeg név Átlage, állítsa be a műveletet hozzá Átlagos (Átlagos) és a számformátum ebben a mezőben szintén a következőre változik számvitel (Pénzügyi) nulla tizedesjegyekkel.
- A harmadik mezőre Összeg állítson be egy címet Gróf és egy műtét neki – Gróf (Mennyiség)
- A Oszlopcímkék (Oszlopok) mező automatikusan létrejön Σ Értékek (Σ Értékek) – húzza a területre sorcímkék (sorok)
Íme, mi lesz a vége:
Teljes összeg, átlagérték és eladások száma – mindez egy pivot táblában!
Következtetés
A Microsoft Excel kimutatástáblái számos szolgáltatást és beállítást tartalmaznak. Egy ilyen kis cikkben meg sem közelítik, hogy mindegyiket lefedjék. Egy kis könyvre vagy egy nagy webhelyre lenne szükség ahhoz, hogy teljes körűen leírjuk a pivot táblák összes lehetőségét. A merész és érdeklődő olvasók folytathatják a pivot táblák felfedezését. Ehhez kattintson a jobb gombbal a pivot tábla szinte bármely elemére, és nézze meg, milyen funkciók és beállítások nyílnak meg. A szalagon két lapot talál: PivotTable eszközök: Beállítások (elemzés) és Design (Konstruktőr). Ne féljen hibázni, bármikor törölheti a kimutatást, és újrakezdheti. Olyan lehetőség áll rendelkezésére, amelyre a DOS és a Lotus 1-2-3 régóta használói soha nem voltak képesek.