Hogyan készítsünk pivot táblát Excelben. Hogyan kell dolgozni az adatokkal

Az MS Excel Pivot Table egy hatékony adatelemző eszköz. Ez az eszköz lehetővé teszi, hogy adatokat nyerjen ki adatok nagy tömbjéből, bármilyen környezetben, mindössze néhány egérkattintással. Ebben a cikkben megpróbálom egyszerű és érthető nyelven, lépésről lépésre elmagyarázni, mit és hogyan kell tenni annak érdekében, hogy ez a rendkívül kényelmes MS Excel eszköz az Ön rendelkezésére álljon.

A forrásadatokkal szemben támasztott követelmények.

Tehát minden pivot tábla alapja egy helyesen felépített adattömb - a „helyes tábla”. Az alábbi ábrán láthat egy példát egy helyesen kialakított adattömbre:

Nézzük meg valójában mi ennek a táblázatnak a „helyessége”? A helyesség az, hogy:

• Minden oszlop csak azonos típusú adatokat, az oszlopot tartalmazza A oszlopban csak a dátumok BAN BEN oszlopban csak a Dokumentumok VAL VEL csak vásárlók, pénz pénzben, beszállítók beszállítókban, termékkategóriák kategóriákban és így tovább... Egy ilyen táblázatban nagyon egyszerűen használhatjuk Szűrő;
• Az azonos típusú adatokat tartalmazó oszlopok nem ismétlődnek;
• A táblázatban nincsenek összesített sorok, csak „tiszta adatok”;
• A táblázat szöveges adatai között nincsenek üres cellák, minden sor tartalmazza a Megbízó, a Termék, a Szállító, a Vezető és így tovább...

Egyébként egy tömbben egyáltalán nem mindegy, hogy az oszlopok milyen sorrendben helyezkednek el, és milyen mező szerint vannak rendezve az adatok. Ez semmilyen módon nem befolyásolja a Pivot Table felépítését.

És itt van egy példa egy „rossz táblára”, amiből hiába törsz fel, nem épül fel a Pivot Table, sőt ha meg is épül, akkor teljesen lehetetlen lesz az adatokkal dolgozni... A mi “ kedvenc” számviteli rendszerek általában ilyen táblázatokkal „kedvesek” meg minket, amelyek további elemzésre teljesen alkalmatlan riportok formájában adják át őket:

Íme egy másik példa az "Érvénytelen tömb"-re:

Itt egy csomó "szabálytalanság" van:

• Először is, a „Szállító” és a „Kategória” oszlopok üres cellái vannak, így nem tudjuk használni a szűrőt;
• Másodszor, szinte minden oszlopban ott vannak az „Összesen...” sorok, ezek teljesen feleslegesek a Pivot Table felépítéséhez, ráadásul csak akadályoznak;
• Harmadszor, a "Főösszeg" oszlop szintén nem szükséges;
• Negyedszer, ugyanaz az adattípus, nevezetesen a „Pénz” három oszlopban található: „Jan”, „Feb” és „Mar”, ami jelentősen megnehezíti a Pivot Table felépítését, ami azt jelenti, hogy csinálj vele valamit...

De ez mind egy külön beszélgetéshez szól, ha meg akarja tanulni, hogyan lehet gyorsan konvertálni az ilyen „görbe táblázatokat” „helyes tömbökké”, olvassa el a következő cikkeket: „Hogyan készítsünk gyorsan pivot táblát 1C vagy SAP jelentésből?” és "Hogyan lehet gyorsan átalakítani egy táblát pivot táblák tömbjévé?"

Valójában a Pivot Table felépítése:

Vegye ki a „helyes tömböt”, helyezze a kurzort a tömb bármely cellájába, válassza ki a „Beszúrás” fület a főmenüben, a bal sarokban a „Táblázatok” részben kattintson a „Pivot Table” gombra:

A megnyíló "Pivot Table létrehozása" párbeszédpanelen kattintson az "OK" gombra:

Az MS Excel létrehoz egy új lapot, amelyen jelzi a pivot tábla beszúrásának helyét, jobb oldalon pedig egy ablakot jelenít meg a Pivot Table mezők beállítására, amelyben az összes oszlop neve látható. a te tömböd:

Kezdjük el a Pivot Table beállítását. Milyen vágást szeretnénk? Termékkategóriák menedzser szerint - kérem. Vigye az egeret a „Kategória” mezőre, kattintson rá a bal egérgombbal, és húzza a „SOROK” mezőbe. A „Kezelő” mező az „OSZLOPOK”, az „Összeg” pedig az „ÉRTÉKEK” mezőbe kerül:

A pivot táblázatok az MS Excel egyik leghatékonyabb eszközei. Segítségükkel milliónyi adatsort alakíthat át tömör jelentéslé másodpercek alatt. Az eredmények gyors összefoglalása mellett a pivot táblázatok lehetővé teszik, hogy szó szerint módosítsa az elemzés módját menet közben a mezők áthúzásával a jelentés egyik területéről a másikra.

A kimutatástáblák az egyik leginkább alulértékelt Excel-eszköz. A legtöbb felhasználó nincs tisztában a lehetőségekkel, amelyek a kezében vannak. Képzeljük el, hogy a pivot táblákat még nem találták fel. Ön egy olyan cégnél dolgozik, amely termékeit különféle ügyfeleknek értékesíti. Az egyszerűség kedvéért csak 4 termék van a választékban. A termékeket néhány tucat, különböző régiókban található ügyfél vásárolja rendszeresen. Minden tranzakció bekerül az adatbázisba, és külön sort jelent.

Az igazgató arra utasítja, hogy régiónként (régiónként) készítsen rövid jelentést az összes áru értékesítéséről. A probléma a következőképpen oldható meg.

Először hozzunk létre egy táblázatelrendezést, azaz egy fejlécet, amely egyedi értékekből áll a termékekhez és a régiókhoz. Készítsünk másolatot a termékoszlopról, és távolítsuk el az ismétlődéseket. Ezután a speciális insert segítségével transzponáljuk az oszlopot egy sorba. Ugyanezt tesszük a régiókkal is, csak transzponálás nélkül. Megkapjuk a jelentés fejlécét.

Ezt a táblát ki kell tölteni, i.e. összefoglalni az érintett termékek és régiók bevételeit. Ez egyszerűen megtehető a SUMIFS funkcióval. Adjuk hozzá az eredményeket is. Összefoglaló jelentést kap az értékesítésről régiónként és termékenként.

Elvégezte a feladatot, és megmutatja a jelentést az igazgatónak. A táblázatot nézve egyszerre több remek ötletet generál.

— Lehet-e nem bevételről, hanem nyereségről beszámolót készíteni?

— Lehetséges-e a termékek soronkénti, a régiók oszloponkénti megjelenítése?

— Lehetséges-e minden menedzserhez külön-külön létrehozni ilyen táblákat?

Még ha Ön tapasztalt Excel-felhasználó is, az új jelentések elkészítése sok időt vesz igénybe. Nem beszélve az esetleges hibákról. Ha azonban ismeri a pivot táblákat, akkor a válasz: igen, szükségem van 5 percre, talán kevesebbre.

Íme, hogyan történik. Nyissa meg a forrásadatokat. A pivot táblát fel lehet építeni egy szabályos tartomány felhasználásával is, de helyesebb lenne átalakítani. Ez azonnal megoldja az új adatok automatikus rögzítésének problémáját. Jelöljön ki egy cellát, és lépjen a lapra Beszúrás. A szalag bal oldalán két gomb található: és Ajánlott kimutatások.

Ha nem tudja, hogyan kell rendszerezni a meglévő adatokat, használhatja a parancsot Ajánlott kimutatások. Az Excel az adatok alapján megjeleníti a lehetséges elrendezések bélyegképét.

Kattintson a megfelelő opcióra, és a pivot tábla készen áll. Nem marad más hátra, mint eszünkbe juttatni, mert... Nem valószínű, hogy egy standard készítmény teljesen egybeesik az Ön vágyaival. Ha a semmiből kell pivot táblát felépítenie, vagy ha a program régi verziója van, kattintson a gombra. Megjelenik egy ablak, ahol meg kell adnia a forrástartományt (ha aktiválja az Excel tábla bármely celláját, azt maga határozza meg) és a jövőbeni pivot tábla helyét (alapértelmezés szerint egy új lap kerül kiválasztásra).

Itt nagyon gyakran nem kell semmit változtatni. Az OK gombra kattintás után egy új Excel munkalap jön létre üres kimutatással.

Mielőtt rátérnénk a beállításokra, ismerkedjünk meg a felülettel és az alapfogalmakkal. A táblázat elrendezése testreszabható a panelen Pivot tábla mezői, amely a lap jobb oldalán található.

A panel tetején található egy lista az összes elérhető mezőről, azaz a forrásadatok oszlopairól. Ha új mezőt kell hozzáadnia az elrendezéshez, bejelölheti a mellette lévő négyzetet - az Excel maga határozza meg, hogy ezt a mezőt hova kell helyezni. Ez azonban nem mindig helyes, ezért jobb, ha az egeret a kívánt helyre húzza az elrendezésen. A mezők törlhetők a bejelölésük törlésével vagy visszahúzásával is.

A pivot tábla 4 területből áll, amelyek a panel alján találhatók: értékek, sorok, oszlopok, szűrők. Nézzük meg közelebbről a céljukat.

Értékek tartománya– ez a pivot tábla központi része olyan értékekkel, amelyeket a kiválasztott módszerrel a forrásadatok összesítésével kapunk.
A legtöbb esetben az aggregáció azáltal történik Összegzés. Ha a kiválasztott mezőben minden adat numerikus formátumban van, az Excel alapértelmezett összeget rendel hozzá. Ha a forrásadat legalább egy szöveget vagy üres cellát tartalmaz, akkor az összeg helyett az kerül kiszámításra Mennyiség sejteket. Példánkban minden cella a megfelelő régióban található összes megfelelő termék összege.

Más számítási módszereket is használhat a kimutatás celláiban. Körülbelül 20 típusa van (átlag, minimum érték, arány stb.). Számos módja van a számítási módszer megváltoztatásának. A legegyszerűbb, ha jobb gombbal kattintson a kívánt mező bármelyik cellájára magában a pivot táblában, és válasszon egy másik összesítési módszert.

Vonal terület– a bal szélső oszlopban található sorok nevei. Ezek mind a kiválasztott mező (oszlop) egyedi értékei. A sorterületen több mező is lehet, ekkor a táblázat többszintűvé válik. Ide általában olyan minőségi változókat kell elhelyezni, mint a terméknevek, hónapok, régiók stb.

Oszlopok területe– a sorokhoz hasonlóan a kiválasztott mező egyedi értékeit mutatja, csak oszlopokban. Az oszlopcímek általában minőségi mutató is. Például évek és hónapok, termékcsoportok.

Szűrési terület– a neve szerint szűrésre használják. Például maga a jelentés régiónként mutatja be a termékeket. A kimutatástáblázatot egy adott iparágra, időszakra vagy menedzserre kell korlátoznia. Ezután egy szűrőmező kerül a szűrőterületre, és kiválasztható a kívánt érték a legördülő listából.

Mezők hozzáadásával és eltávolításával a megadott területeken másodpercek alatt testreszabhatja az adatok tetszőleges szeletét.

Lássuk, hogyan működik ez a gyakorlatban. Egyelőre hozzuk létre ugyanazt a táblát, amelyet a SUMIFS függvény segítségével már létrehoztunk. Ehhez húzza a területre Értékek mező "Bevétel", a területen Húrok húzza a „Régió” mezőt (értékesítési régió) ide Oszlopok- "Termék".

Ennek eredményeként egy igazi pivot táblát kapunk.

Szó szerint 5-10 másodpercig tartott megépíteni. Egy meglévő pivot tábla módosítása is egyszerű. Lássuk, hogyan valósul meg könnyedén a rendező kívánsága.

Cseréljük a bevételt nyereséggel.

A termékek és a területek az egér húzásával is felcserélhetők.

Számos eszköz áll rendelkezésre a pivot táblák szűrésére. Ebben az esetben egyszerűen elhelyezzük a „Kezelő” mezőt a szűrőterületen.

Minden eltartott néhány másodpercig. Íme, milyen egyszerű valójában a kimutatástáblákkal való munka. Persze nem minden feladat ennyire triviális. Vannak olyan esetek is, amikor kifinomultabb összesítési módszert kell alkalmazni, számított mezők hozzáadása, feltételes formázás stb. De ez a pivot táblák fejlettebb használata.

Kezdeti adatok

A kimutatástáblákkal való sikeres munka érdekében a forrásadatoknak számos követelménynek kell megfelelniük. Előfeltétel, hogy minden mező (oszlop) felett nevek jelenjenek meg, amelyek alapján ezek a mezők azonosíthatók. Most néhány hasznos tipp.

1. Az adatok legjobb formátuma az Excel-táblázat. Ez azért jó, mert minden mezőnek van neve, és új sorok hozzáadásakor azok automatikusan bekerülnek a pivot táblába.

2. Kerülje a csoportok ismétlődését az oszlopokban. Például az összes dátumnak egy mezőben kell lennie, és nem kell külön oszlopokba osztani például hónapok szerint.

3. Távolítsa el a hézagokat és üres cellákat, különben ez a sor kieshet az elemzésből.

4. Alkalmazza a megfelelő formázást a mezőkre. A számoknak numerikus formátumban kell lenniük, a dátumoknak dátumoknak kell lenniük. Ellenkező esetben problémák merülnek fel a csoportosítás és a matematikai feldolgozás során. De itt az Excel segít, mert... Elég jól meghatározza az adatformátumot.

Általában kevés követelmény van, de ezeket ismernie kell.

Ha módosítja a forrást (például új sorokat ad hozzá), a pivot tábla addig nem változik, amíg a jobb egérgombbal nem frissíti.

vagy
a lapon található paranccsal Adatok – Összes frissítése.

Ezt szándékosan tették, mert a pivot tábla sok helyet foglal el a RAM-ban. Ezért a számítógépes erőforrások gazdaságosabb felhasználása érdekében a munka nem közvetlenül a forrással történik, hanem a gyorsítótárral, ahol a forrásadatok pillanatképe található.

A pivot táblák használatával még ezen az alapszinten is jelentősen növelhető a nagy mennyiségű adat feldolgozásának sebessége és minősége. Ha valami nem világos, írd meg a megjegyzésekben.

Pivot táblák az egyik leghatékonyabb Excel eszköz. Lehetővé teszik, hogy néhány kattintással elemezze és összegezze a különböző eredményeket nagy mennyiségű adatra vonatkozóan. Ebben a cikkben megismerkedünk a pivot táblákkal, megértjük, mik azok, megtanuljuk létrehozni és konfigurálni őket.

A cikk írásakor az Excel 2010-et használtam. A pivot táblák koncepciója nagyjából változatlan maradt évek óta, de létrehozásuk módja kissé eltér az Excel minden új verziójában. Ha az Excel 2010-es verziójától eltérő verzióval rendelkezik, készüljön fel arra, hogy a jelen cikkben szereplő képernyőképek eltérnek a képernyőn látottaktól.

Egy kis történelem

A táblázatok készítésére szolgáló programok fejlesztésének hajnalán a Lotus szabályai 1-2-3 voltak. 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ázatkezelő iparágat, mint a Lotus 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 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-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 kifejlesztett egy eszközt az Excelben, az úgynevezett pivot táblákat, amely nem volt elérhető a Lotus 1-2-3-ban. Az Excel exkluzív pivot táblázatai olyan hihetetlenül hasznosak voltak, hogy az emberek hajlamosak voltak megtanulni az új Excel szoftvert, ahelyett, hogy ragaszkodtak volna a Lotus 1-2-3-hoz, amelyben nem voltak ilyenek.

A pivot táblák, a Windows általános sikerének alábecsülésével párosulva, a Lotus 1-2-3 halálmenetét játszották, és a Microsoft Excel sikerének kezdetét jelentették.

Mik azok a kimutatások?

Tehát mi a legjobb módja a pivot táblák leírásának?

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 összesítésekkel ellentétben az Excel kimutatások interaktívak. Létrehozásuk után könnyedén megváltoztathatja őket, ha nem azt a képet adják, ahogyan azt remélte. Néhány kattintással az összegek átfordíthatók, így az oszlopfejlécek sorfejlécekké válnak, és fordítva. A pivot táblákkal sokféle dolgot megtehet. Ahelyett, hogy szavakkal próbálnánk leírni a pivot táblák összes képességét, egyszerűbb a gyakorlatban is bemutatni...

A kimutatástáblákkal elemzett adatok nem lehetnek semmiféle adatok. Ennek nyers adatnak kell lennie, például valamilyen listának. Ez lehet például a vállalatnál az elmúlt hat hónap során végrehajtott értékesítések listája.

Tekintse meg az alábbi ábrán látható adatokat:

Kérjük, vegye figyelembe, hogy ezek nem nyers adatok, mivel azokat már összefoglaltuk. A B3 cellában 30 000 dollárt látunk, ami valószínűleg az a teljes összeg, amit James Cook januárban keresett. Hol vannak akkor a forrásadatok? Honnan jött a 30 000 dollár? Hol van az eredeti eladási lista, amelyből ez a havi végösszeg származott? Nyilvánvaló, hogy valaki mindent megtett 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ő táblázatban való átalakításában. Szerinted mennyi ideig tartott? Óra? Tíz óra?

A helyzet az, hogy a fenti táblázat nem összefoglaló táblázat. Kézzel hozták létre máshol tárolt nyers adatokból, és legalább néhány órát vett igénybe a feldolgozása. Pontosan ilyen összefoglaló tábla hozható létre pivot táblákkal, mindössze néhány másodpercet fordítva rá. 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 tranzakció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 tárgyaltunk. Igen, megtehetjük ezt és még sok mást!

Hogyan készítsünk pivot táblát?

Először is győződjön meg arról, hogy van néhány forrásadata egy Excel-lapon. A pénzügyi tranzakciók listája a legjellemzőbb dolog, ami megtalálható. 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 indítsuk el az Excelt... és töltsük be ezt a 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(Összefoglaló táblázat):

Megjelenik egy párbeszédpanel PivotTable létrehozása(PivotTable létrehozása) két kérdéssel:

• Milyen adatokat használjak új kimutatástábla létrehozásához?
• Hol helyezzem el 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 egy pivot tábla létrehozásához. Megjegyezzük, hogy választhatunk egy másik tartományt, egy másik táblát, vagy akár valamilyen külső adatforrást, 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 lapon):

Az Excel létrehoz egy új munkalapot, é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 segítségével megadhatja a kimutatástáblának, hogyan szeretné összefoglalni az adatokat. Bár ezek a területek üresek, 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. Ebben az esetben egy pivot tábla automatikusan generálódik az utasításainknak megfelelően. Ha hibáztunk, eltávolíthatjuk a címsorokat az alsó részből, vagy másokat húzhatunk helyettük.

Vidék Értékek(Jelenések) valószínűleg a legfontosabb a négy közül. Hogy milyen címsor kerül erre a területre, az határozza meg, hogy milyen adatok kerülnek összegzésre (összeg, átlag, maximum, minimum stb.) Ezek szinte mindig számértékek. Kiváló jelölt ezen a területen - adatok a címszó alatt Összeg(Költség) az eredeti táblázatunkból. Húzzuk 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) bejegyzés jelent meg Összeg összege(Sum by Amount mező), jelezve, hogy az oszlop Összeg foglalta össze

Ha megnézzük magát a pivot táblát, 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 az adatainkkal kapcsolatban, mint amennyivel jelenleg rendelkezünk.

Nézzük meg a forrásadatokat, és próbáljunk meg azonosítani egy vagy több oszlopot, amelyek segítségével ezt az összeget lebonthatjuk. Például elkészíthetjük a pivot táblánkat úgy, hogy a teljes eladási összeget minden eladónál külön-külön számítjuk ki. Azok. Sorok kerülnek hozzáadásra a kimutatási táblázatunkhoz az egyes vállalati értékesítők nevével és teljes értékesítési összegével. Az eredmény eléréséhez csak húzza a címet Eladó sorcímkék(sorok):

Egyre érdekesebb! Pivot táblánk 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 már készen áll. Készítettünk egy hasznos összefoglalót a nyers adatokról. Fontos információk már érkeztek! A cikk további részében meg fogunk tekinteni néhány módot összetettebb pivot táblák létrehozására, és megtanuljuk, hogyan lehet őket testreszabni.

Pivot tábla 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 segítségével Fizetési mód(Fizetési mód). Csak húzza a címet Fizetési mód a régióba 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. Hogyan nézhet ki egy ilyen asztal? Nézzük meg…

Húzza a címet Csomag(Komplex) a területre Jelentésszűrő(Szűrők):

Figyeld meg, hova jutott...

Ez lehetőséget ad a jelentés szűrésére a „Melyik üdülőkomplexumért fizetett” attribútum alapján. Például láthatjuk az eladók és fizetési módok szerinti bontást az összes komplexum esetében, vagy néhány kattintással megváltoztathatjuk az összesítő táblázat nézetét, és ugyanazt a bontást csak azok számára jelenítjü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 konfigurálást...

Ha hirtelen kiderül, hogy csak a csekkel és hitelkártyával történő fizetés (vagyis banki átutalás) kell megjelennie a pivot táblában, akkor letilthatjuk a fejléc megjelenítését Készpénz(Készpénz). Ehhez a mellett Oszlopcímkék kattintson a lefelé mutató nyílra, és a legördülő menüben törölje az elem jelölését 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.

Pivot táblák formázása Excelben

Nyilvánvaló, hogy a PivotTables egy 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ó, ha ilyen helyzetben a szokásos műveleteket hajtjuk végre, egyszerűen kijelöljük a teljes táblázatot (vagy a teljes lapot), és az eszköztáron található szabványos számformázó gombokkal állítsuk be a kívánt formátumot. Ezzel a megközelítéssel az a probléma, hogy ha a jövőben valaha megváltoztatja a pivot tábla szerkezetét (és ennek 99%-a az esélye), a formázás elveszik. Szükségünk van egy módra, hogy (majdnem) véglegessé tegyük.

Először is keressük meg a bejegyzést Összeg összege területen Értékek(Értékek), és kattintson rá. A megjelenő menüben válassza ki az elemet Értékmező beállításai(Értékmező beállításai):

Megjelenik egy párbeszédpanel Értékmező beállításai(Értékmező paraméterei).

Kattintson a gombra Számformátum(Számformátum), megjelenik egy párbeszédpanel Cellák formázása(Cellaformátum):

A listából Kategória(Számformátumok) válassza ki Könyvelés(Pénzügyi), és állítsa a tizedesjegyek számát nullára. Most nyomja meg többször rendben hogy visszamenjünk a pivot táblánkhoz.

Amint láthatja, a számok végül dollárösszegként lettek formázva.

Most, hogy elkészültünk a formázással, állítsuk be a teljes pivot tábla formátumát. Ennek többféle módja van. Használjuk az egyszerűbbet...

Nyissa meg a lapot PivotTable eszközök: Tervezés(Munka a pivot táblákkal: Tervező):

Válassza ki a megfelelő stílust, és nézze meg az eredményt a pivot táblázatban:

Egyéb beállítások a pivot táblákhoz az Excelben

Néha az adatokat dátum szerint kell szűrni. Például a kereskedelmi tranzakciók 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ímsorok listájába, és a helyére mozgassa a címsort Foglalt időpont(Foglalási dátum):

Amint látja, ez átmenetileg használhatatlanná tette a kimutatástáblázatunkat. 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 Csoport(Csoport):

Megjelenik a csoportosítási párbeszédpanel. Mi választunk Hónapok(Hónapok), majd nyomja meg a gombot rendben:

Voálá! Ez a táblázat sokkal hasznosabb:

Ez a táblázat egyébként szinte teljesen megegyezik a cikk elején láthatóval, ahol az eladási eredményeket manuálisan állították össze.

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 az eredeti táblázatnézethez, és nézzük meg, hogyan jeleníthetünk meg átlagértékeket összegek helyett.

A kezdéshez kattintson a gombra Összeg összegeés a megjelenő menüből válassza ki Értékmező beállításai(Értékmező beállításai):

A listán Az értékmező összegzése:(Művelet) a párbeszédpanelen Értékmező beállításai(Értékmező beállításai) válassza ki Átlagos(Átlagos):

Ugyanakkor, amíg itt vagyunk, változtassunk Egyéni név(Egyéni név) -val Összeg átlaga(Mennyiség szerint mező) valami rövidebbre. Írjon be valami ehhez hasonlót ebbe a mezőbe Átl:

Kattintson rendbenés nézd meg mi történt. Figyelje meg, hogy az összes érték az összesítésről átlagra változott, és a táblázat címe (a bal felső cellában) a következőre módosult. Átl:

Ha szeretné, azonnal megkaphatja az összeget, az átlagot és a mennyiséget (értékesítést) egy pivot táblában.

Íme egy lépésről lépésre, hogyan kell ezt megtenni, kezdve egy üres pivot táblával:

1. Húzza a címet Eladó(értékesítési képviselő) a régióba Oszlopcímkék(Oszlopok).
2. Húzza háromszor a címet Összeg(Költség) területre Értékek(Értékek).
3. Az első mezőre Összeg változtassa meg a nevet Teljes(összeg), és a számformátum ebben a mezőben a következő Könyvelés(Pénzügyi). A tizedesjegyek száma nulla.
4. Második mező Összeg név Átlag e, állítsa be a műveletet Átlagos(Átlagos), és módosítsa a számformátumot ebben a mezőben Könyvelés(Pénzügyi) nullával egyenlő tizedesjegyekkel.
5. A harmadik mezőre Összegállítsa be a címet Számolés egy műtét neki... Számol(Mennyiség)
6. A területen Oszlopcímkék(Oszlopok) mező automatikusan létrejön ΣÉrtékek(Σ Értékek) – húzza a területre sorcímkék(Húrok)

Ezt kapjuk a végén:

Összes, átlag és eladások száma – mindez egy pivot táblában!

Következtetés

A Microsoft Excel kimutatástáblái sok-sok funkciót és beállítást tartalmaznak. Egy ilyen rövid cikk meg sem közelíti mindet. A pivot táblák képességeinek teljes leírásához kis könyvre vagy nagy webhelyre van szükség. A bátor és kíváncsi 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 Tervezés(Konstruktőr). Ne féljen hibázni; bármikor törölheti a pivot táblát, és újrakezdheti. Olyan lehetőség áll rendelkezésére, amilyenre a régi DOS és Lotus 1-2-3 felhasználók soha nem.

Az Excel kimutatástáblái lehetővé teszik egy nagy tartományba eső adatok elemzését. Sőt, az elemzéshez használt forrástábla Excelben vagy más adatbázisban vagy más dokumentumban is létrehozható.

Csak annyit kell tennie, hogy a meglévők közül kiválasztja a szükséges fejléceket a sorokhoz és oszlopokhoz. Alkalmazhat szűrőt a teljes pivot tábla egészére, vagy csak a sorok és oszlopok nevében feltüntetett értékekre.

Most arról a követelményekről, amelyeket be kell tartani az Excelben történő létrehozáskor. A forrásadatokat táblázat formájában kell bemutatni, amelynek az oszlopoknak címmel, azaz fejléccel kell rendelkeznie. Erre a célra tökéletes egy intelligens táblázat létrehozása az Excelben. Nem tartalmazhat üres sorokat, oszlopokat vagy cellákat sem. Nem lehetnek rejtett sorok/oszlopok vagy egyesített cellák.

Hogyan kell létrehozni

Most nézzünk egy példát egy kimutatástábla létrehozására. Tegyük fel, hogy adataink vannak egy ruhaüzlet eladásairól: melyik eladó, hány darab, milyen cikkeket adott el és mennyiért.

Pivot táblázat készítéséhez válasszon ki egy cellát az eredetiből, majd lépjen a „Beszúrás” fülre, és kattintson a gombra. "Pivot tábla".

A következő párbeszédpanel jelenik meg. Ebben meg kell adni a tábla tartományát vagy nevét a forrásadatokkal, és itt lehet kiválasztani egy másik forrásból. Ezután jelölje be jelölővel, hogy hol helyezze el a készítendő jelentést. Csináljuk új lapon. Kattintson az OK gombra.

A megnyitott Excel munkafüzetben egy új munkalap jön létre, amelyre a még üres pivot tábla kerül.

A jobb oldalon megjelenik a mezők és területek listája. A mezők az összes oszlopfejléc, amelyek az eredeti tartományban voltak. Az egér segítségével áthúzzuk őket az alábbi négy terület egyikébe. Így egy pivot tábla kialakítása.

A hozzáadott mezőket pipa jelöli. A táblázatterületeken megváltoztathatja a helyüket, hogy elérje azt a megjelenést, amely a legmegfelelőbb a forrásadatok elemzéséhez.

A mezők elrendezése

Most döntsük el, milyen elv alapján elemezzük az adatokat. Például meg kell találni, hogy melyik eladó melyik terméket adott el minden hónapban és milyen összegért.

Az adatokat a kiválasztott tartomány szerint szűrjük az eladók szerint. Vagyis kiválasztunk egy eladót, és a táblázatban az általa értékesített áruk jelennek meg. Kattintson a bal gombbal a „Szállító” mezőre, és húzza a „Jelentésszűrő” területre. A táblázat megváltozott, és a hozzáadott mezőt most pipa jelzi.

A sorokhoz válassza a „Termékek” lehetőséget. Hasonló módon húzza a kívánt mezőt a területre "Sorcímek".

Felhívjuk figyelmét, hogy egy területen több mezőt is beszúrhat. Például a sorokban kiválasztjuk a termékeket és feltüntetjük az árat. A termékek legördülő listákká váltak, amelyek megjelenítik az árat. Ha először az árat, majd a termékeket adja meg, a legördülő listából ár lesz. Itt a mezők sorrendje számít.

Ha az eredeti táblázatban lenne egy „Mértékegységek” oszlopunk. Ekkor ez az összesítő táblázat megmutatná, hogy melyik eladó, melyik hónapban, hány darab árut adott el egy bizonyos áron.

A régióba "Oszlopcímek" Húzza el a „Dátum” mezőt. Ha nem minden napra, hanem például havi bontásban szeretné megjeleníteni az eladásokat, kattintson a jobb gombbal bármelyik dátumra, és válassza a menü „Csoport” menüpontját.

A táblázat a következő formájú lesz.

Most húzza az „Összeg” mezőt az „Értékek” területre.

Amint látható, csak számok jelennek meg, bár az eredeti tartományban az oszlop celláinak numerikus formátuma volt beállítva, ez lehet pénzbeli vagy pénzügyi is. A „Dátum” oszlopban a cellaformátum is megfelelő volt – dátum.

Ennek javításához válassza ki a kívánt cellatartományt az összegzésben, és nyomja meg a jobb egérgombot. majd válassza ki a menüből "Számformátum".

A következő ablakban válassza ki a „Numerical” opciót, és jelölje be a négyzetet "Bitcsoport elválasztó"és kattintson az "OK" gombra.

Hogyan kell dolgozni az adatokkal

Miután az összes szükséges mezőt figyelembe vettük, elkezdhetjük a pivot táblával való munkát Excelben. Eladó kiválasztása: a jelölőnégyzet bejelölésével kiválaszthat egyet, többet vagy az összeset egyszerre "Több elem kiválasztása".

A sorokhoz és oszlopokhoz is használhat szűrőt. A példában ezek termékek és hónapok. Például az öltöny és nadrág négyzetek bejelölésével megtudhatja, hogy mennyiért adta el az összes eladó vagy egy adott eladó.

Az Értékek területen konfigurálhatja a mező beállításait. A példa az értékek összegét mutatja: A romák februárban 1800,00 inget adtak el. lássuk hány darab. Kattintson a bal gombbal a vonalra "Számonkénti összeg..."és válassza ki a menüből "Értékmező opciók".

A következő ablakban válassza ki a „Mennyiség” elemet a listából, válassza ki az adott esetnek megfelelőt, majd kattintson az „OK” gombra.

Most, az értékeket nézve, megérthetjük, hogy a Roma februárban két mezt adott el.

Most készítsük el a táblázat általános szűrőjét hónapok szerint. Változtatjuk a területeket: a „Jelentésszűrő”-be húzzuk be a „Dátum” mezőt "Oszlopcímek"- "Eladó".

Egy ilyen összefoglaló táblázat megmutatja, hogy az egyes eladók mennyit és milyen árut adtak el a teljes időszakra vagy egy adott hónapra vonatkozóan.

Ügyeljen a szalagra is. Ha kijelöl egy cellát egy kimutatástáblából, egy lap jelenik meg rajta "Munka a kimutatástáblákkal" két „Paraméterek” és „Tervező” allappal.

A probléma, amellyel mindannyian szembesülünk, nem az adatok hiánya; éppen ellenkezőleg – hatalmas mennyiségű adatról van szó! Ezért javaslom egy nagyszerű Excel funkció használatát - Pivot táblák hogy összefoglalja és elemezze adatait.

A kimutatások erejének megismerése olyan egyszerű, mint az eredeti adatok összehasonlítása a kimutatás egy verziójával; Néhány kattintással meg tudtam nézni egy cikk átlagárát államonként.

Pénzügyi szakemberként genetikailag hajlamos vagyok a táblázatok kedvelésére. De azt is tapasztaltam, hogy táblázatokat használok kreatív és szabadúszó munkáim megszervezéséhez. Függetlenül attól, hogy mire használja a táblázatokat, a pivot tábla segítségével több értelmet nyerhet adataiból.

Ezt a leckét a miénk szerint építjük fel az adatokkal való jobb munka érdekében. Megmutatom öt kedvenc kimutatási táblázatomat.

Ebben az oktatóanyagban a Microsoft által ezen az oldalon megadott mintaadatokat fogom használni. Használja ezeket az adatokat a példák újbóli létrehozásához vagy az általam bemutatott függvények teszteléséhez.

A fejlett kimutatási technikák használata Excelben (rövid videó)

Szeretek képernyőközvetítéseket tanítani, amelyek lehetőséget adnak arra, hogy lépésről-lépésre nézzétek meg, hogyan használom a funkciókat. Tekintse meg az alábbi rövid videót, amely öt kedvenc speciális Excel-kimutatási funkciómat ismerteti:

5 fejlett kimutatási trükk az Excelben

Folytassa az olvasást, hogy megtudja, hogyan használhatja mind az öt funkciót az alábbi leckében, mint például: Szeletelők, Idővonal, Táblázatnézet, Számított mezők és Kiemelt kimutatástáblák. Kezdjük el.

1. Szakaszok

Szeletek- Mutass és kattints eszköz az Excel kimutatástáblájában szereplő adatok finomításához. Helyezzen be egy szeletelőt, és könnyen módosíthatja a pivot táblában szereplő adatokat.

Ebben a példában beszúrtam egy szeletet a típushoz Tétel. Miután rákattintottam Hátizsák, a pivot tábla csak ezt a paramétert mutatja a táblázatban.

Sokszor készítek irányítópult-jelentéseket, amelyeket mások is használni fognak. Szeletelők hozzáadása segíthet a végfelhasználónak a jelentés testreszabásában.

Szeletelő hozzáadásához kattintson a kimutatástáblázatra, és keresse meg a lapot Elemzés az Excel szalagon.

Jelöljön be több jelölőnégyzetet, hogy belefoglalja ezeket az oszlopokat, amelyek mindegyike egy szelet lesz.

Tart Ctrl a billentyűzeten több szeletelőelem kiválasztásához, amelyek több kijelölést tartalmaznak egy oszlopból kimutatás-adatként.

2. Idővonal

Idővonalak Ez egy speciális típusú szeletelő, amely a kimutatásadatok részeként szereplő dátumok módosítására szolgál. Ha az adatok dátumokat tartalmaznak, akkor valóban ki kell próbálnia az Idővonalat, hogy kiválasszon adatokat bizonyos időszakokból.

Ugrás a szakaszra Elemzés > Idővonal beszúrása idővonal hozzáadásához, egy speciális típusú szeletelőhöz, amely dátum alapján kezeli a fejlett Excel kimutatástáblázatban szereplő adatokat.

Tipp: Ha ez a funkció nem működik az Ön számára, győződjön meg arról, hogy a forrásadatok dátumként formázott dátumot tartalmaznak a táblázatban.

Idővonal hozzáadásához válassza ki a kimutatást (kattintson benne), majd kattintson a gombra Elemzés > Idővonal beszúrása az Excel szalagon. A felugró ablakban válasszon ki egy dátumoszlopot (vagy több oszlopot), és kattintson a gombra rendben idővonal létrehozásához.

Kattintson és húzza az Idővonal ablakban egy adott időtartomány kiválasztásához a kimutatáshoz.

Az idővonal beszúrása után kattintson és mozgassa az idővonal fogantyúit, hogy módosítsa a pivot táblázat tartalmát.

Az idővonal működését a jobb alsó sarokban található legördülő listára kattintva módosíthatja. Módosíthatja az idővonalat, hogy például negyedéves vagy éves adatokat jelenítsen meg konkrét dátumok helyett.

3. Táblázatos nézet

A Kimutatások standard nézete az Excelben úgy néz ki, mint egy vízesés; Ahogy több mezőt húz a sorokra, az Excel több „réteget” hoz létre az adatokban.

A probléma az, hogy a standard nézetben nehéz képleteket illeszteni a kimutatásokba. Ha vannak adatok a kimutatásban, de normál táblázatként szeretné megtekinteni, akkor táblázatnézetet kell használnia a kimutatáshoz.

Válassza ki Tervező > Jelentés elrendezése > Megjelenítés táblázatos formában hogy a pivot táblával normál táblaként működjön.

Miért érdemes táblázatnézetet használni? A PivotTable adatainak klasszikus táblázatos stílusba állítása lehetővé teszi az adatképletek egyszerűbb bevitelét vagy külön jelentésbe történő beillesztését.

Használja a Táblázat nézetet, hogy a kimutatás jobban hasonlítson a szabványos sorokhoz és oszlopokhoz.

Legtöbbször inkább az Excel táblázatnézetét használom. Inkább úgy néz ki, mint egy szabványos táblázat, és könnyebb a képleteket írni és az adatokkal dolgozni. Azt is megtehetem ezt a nézetet, és beillesztem egy új lapra.

4. Számított mezők

A kiszámított mezők segítségével egy olyan oszlopot adhatunk hozzá egy kimutatáshoz, amely nem szerepel a forrásadatokban. A szabványos matematikai műveletek segítségével teljesen új mezőket hozhat létre. Vegyünk két meglévő oszlopot, és a matematikával hozzon létre teljesen újakat.

Tegyük fel, hogy értékesítési adataink vannak egy táblázatban. Megvan az eladott cikkek száma és az egyes tételek eladási ára. Ez az ideális időpont egy számított mező használatára a rendelés végösszegének kiszámításához.

A számított mezőkkel való munka megkezdéséhez kattintson a kimutatás belsejébe, majd keresse meg a szalagon Elemzés. Kattintson a menüre Mezők, elemek és halmazok majd válassza ki Számított mező.

Használat Elemzés > Mezők, tagok és halmazok > Számított mező a kiszámított mező beillesztéséhez a kimutatástáblába.

Az új felugró ablakban először adjon nevet a számított mezőnek. az én esetemben úgy fogom hívni Teljes rendelés. A rendelés teljes értéke a mennyiség szorozva az egyes egységek árával. Ezután ebben az ablakban a mezők listájában duplán kattintottam az első címmezőre (mennyiség).

A rendelés teljes költségének kiszámításához megszoroztam a meglévő egységár mezőt ( Egységár) a mennyiség mezőben ( Mennyiség).

A mező nevének hozzáadása után hozzáadok egy szorzójelet * , majd kattintson duplán a teljes mennyiségre (mennyiség). Menjünk tovább és kattintsunk rendben.

Az Excel most frissítette a kiterjesztett kimutatásomat, és hozzáadott egy új számított mezőt. A Mezőlistában a kimutatások listája is megjelenik, így bármikor áthúzhatja őket a jelentésben bárhová, amikor csak szüksége van rájuk.

Ha nem kíván kétoszlopos aritmetikát használni, akkor saját számtani értékeit is megadhatja a számított mezőben. Például, ha egyszerűen csak 5% forgalmi adót szeretnék hozzáadni minden rendeléshez, akkor a következőt írhatom a számított mezőbe:

A teljes rendelési költséget megszoroztam 1,05-tel, hogy kiszámítsam a forgalmi adót tartalmazó költséget; numerikus értékeket használhat a meglévő mezőkkel együtt.

Elvileg a számított mezők bármilyen szabványos matematikai operátort tartalmazhatnak, például összeadást, kivonást, szorzást és osztást. Használja ezeket a számított mezőket, ha nem szeretné frissíteni az eredeti adatokat.

Menj Beszúrás > hogy kipróbálja ezt a funkciót.

Ez a funkció annyira könnyen használható, hogy magától értetődik. Segítségével gyorsan létrehozhat speciális kimutatási táblázatokat az Excelben. Csak jelölje ki adatait, lépjen a lapra Beszúrás az Excel szalagon, és válassza ki Ajánlott kimutatások.

A felugró ablak számos lehetőséget tartalmaz a forrásadatokból pivot tábla létrehozására. Kattintson az ablak bal oldalán található miniatűrökre az Excel által ajánlott kimutatás-beállítások megtekintéséhez.

A kimutatás-ajánlás szolgáltatás számos lehetőséget kínál az adatok egy kattintással történő elemzésére.

Annak ellenére, hogy ez egy speciális funkció, amellyel csak kevesen ismerik, nagyszerű eszköz a pivot táblák futtatásához. Semmi sem akadályozza meg, hogy módosítsa a PivotTable-t úgy, hogy saját maga cserélje ki a mezőket, de ez jó kiindulópont.

Ezenkívül ez a funkció adatfeltáró eszközként is tetszik. Ha nem tudom, mit keresek, amikor elkezdem az adatok feltárását, az ajánlott Excel kimutatástáblák gyakran jobban áttekinthetőek, mint én!

Ismételje meg és folytassa a tanulást (még több Excel leckével)

Ez a haladó Excel-lecke segített mélyebben elmerülni a kimutatásban, amely az egyik kedvenc szolgáltatásom az Excel-táblázatok elemzéséhez és megtekintéséhez. Pivot táblákat használok, hogy értelmet találjak a nagy adathalmazokban, így jobb döntéseket hozhatok és intézkedhetek.

Ezek a leckék a következő szintre emelik az Excel és a Pivot Tables készségeit. Nézd meg őket:

• Az ExcelZoo nagyszerű áttekintést nyújt a kimutatás technikáiról a 10 lecke a kimutatások elsajátításához című cikkében (angol nyelven).
• Mi az Envato Tuts+-nál a pivot táblákat borítottuk egy kezdő oktatóanyaggal.
• A Microsoft Excel egyszerűbb bemutatásához tekintse meg oktatóanyag-sorozatunkat.

Mit szeretne még tudni a pivot táblákról? Ossza meg ötleteit vagy kérdéseit az oktatóanyag alatti megjegyzésekben.