A Pivot Tables egy csodálatos beépített jelentéskészítő eszköz az Excelben. Míg általában az adatok összegzésére használják az összegekkel, használhatja őket az értékek közötti változás százalékos arányának kiszámítására is. Még jobb: egyszerű megtenni.
Ezt a technikát mindenféle dolog elvégzésére használhatja – nagyjából bárhol, ahol látni szeretné, hogyan viszonyul az egyik érték a másikhoz. Ebben a cikkben azt az egyszerű példát fogjuk használni, hogy kiszámítjuk és megjelenítjük azt a százalékot, amellyel a teljes értékesítési érték hónapról hónapra változik.
Íme a lap, amit használni fogunk.
Ez egy meglehetősen tipikus példa egy értékesítési lapra, amely megmutatja a rendelés dátumát, az ügyfél nevét, az értékesítési képviselőt, a teljes értékesítési értéket és néhány egyéb dolgot.
Mindehhez először táblázatként formázzuk az értéktartományunkat az Excelben, majd létrehozunk egy kimutatástáblázatot a százalékos változás számításainak elvégzéséhez és megjelenítéséhez.
Tartalomjegyzék
A tartomány formázása táblázatként
Ha az adattartomány még nincs táblázatként formázva, javasoljuk, hogy ezt tegye meg. A táblázatokban tárolt adatok többféle előnnyel járnak a munkalap cellatartományaiban lévő adatokkal szemben, különösen a kimutatások (pivotTables) használatakorTovábbi információ a táblázatok használatának előnyeiről).
Egy tartomány táblázatként való formázásához jelölje ki a cellatartományt, és kattintson a Beszúrás > Táblázat elemre.
Ellenőrizze, hogy a tartomány helyes-e, és hogy vannak-e fejlécek az adott tartomány első sorában, majd kattintson az „OK” gombra.
A tartomány most táblázatként van formázva. A táblázat elnevezése megkönnyíti a hivatkozást a jövőben a kimutatások, diagramok és képletek létrehozásakor.
Kattintson a Táblázateszközök alatt található „Tervezés” fülre, és írjon be egy nevet a szalag elején található mezőbe. Ennek a táblázatnak a neve „Értékesítés”.
Itt módosíthatja az asztal stílusát is, ha akarja.
Hozzon létre egy kimutatást a százalékos változás megjelenítéséhez
Most folytassuk a kimutatás létrehozását. Az új táblázatban kattintson a Beszúrás > Kimutatás elemre.
Megjelenik a PivotTable létrehozása ablak. Ez automatikusan felismeri az asztalt. De ezen a ponton kiválaszthatja azt a táblát vagy tartományt, amelyet a kimutatáshoz használni szeretne.
Csoportosítsa a dátumokat hónapokba
Ezután áthúzzuk a dátummezőt, amely alapján csoportosítani szeretnénk, a kimutatás sorok területére. Ebben a példában a mező neve Rendelés dátuma.
Az Excel 2016-tól kezdve a dátumértékek automatikusan évekre, negyedévekre és hónapokra vannak csoportosítva.
Ha az Ön Excel verziója nem teszi ezt meg, vagy egyszerűen csak módosítani szeretné a csoportosítást, kattintson jobb gombbal egy dátumértéket tartalmazó cellára, majd válassza ki a „Csoport” parancsot.
Válassza ki a használni kívánt csoportokat. Ebben a példában csak az évek és a hónapok vannak kiválasztva.
Az év és a hónap most olyan mezők, amelyeket elemzésre használhatunk. A hónapok továbbra is a Megrendelés dátumaként vannak megnevezve.
Adja hozzá az értékmezőket a kimutatáshoz
Helyezze át az Év mezőt a Sorok közül a Szűrő területre. Ez lehetővé teszi a felhasználó számára, hogy egy évig szűrje a kimutatást, ahelyett, hogy túl sok információval zsúfolná el a kimutatást.
Húzza kétszer az Értékek területre a kiszámítani kívánt értékeket (ebben a példában Teljes értékesítési érték) tartalmazó mezőt, és mutassa be a változást.
Lehet, hogy még nem tűnik soknak. De ez hamarosan megváltozik.
Mindkét értékmező alapértelmezés szerint összege lesz, és jelenleg nincs formázásuk.
Az első oszlopban szereplő értékeket összegként szeretnénk megtartani. Ezek azonban formázást igényelnek.
Kattintson a jobb gombbal egy számra az első oszlopban, és válassza a „Számformázás” lehetőséget a helyi menüből.
Válassza ki a „Számvitel” formátumot 0 tizedesjegygel a Cellák formázása párbeszédpanelen.
A PivotTable most így néz ki:
Hozza létre a Százalékos változás oszlopot
Kattintson a jobb gombbal egy értékre a második oszlopban, mutasson az „Értékek megjelenítése” pontra, majd kattintson a „Számos eltérés a következőtől” lehetőségre.
Válassza ki az „(Előző)” elemet alapelemként. Ez azt jelenti, hogy az aktuális hónap értékét a rendszer mindig összehasonlítja az előző hónapok értékével (Rendelés dátuma mező).
A kimutatás most az értékeket és a százalékos változást is mutatja.
Kattintson a sorcímkéket tartalmazó cellába, és írja be a „Hónap” szót az oszlop fejlécébe. Ezután kattintson a fejléccellájába a második értékoszlophoz, és írja be a „Variance” kifejezést.
Adjon hozzá néhány eltérési nyilat
Ennek a kimutatásnak a tökéletesítéséhez szeretnénk jobban megjeleníteni a százalékos változást néhány zöld és piros nyíl hozzáadásával.
Ezek nagyszerű módja annak, hogy meglássuk, hogy a változás pozitív vagy negatív volt-e.
Kattintson bármelyik értékre a második oszlopban, majd kattintson a Kezdőlap > Feltételes formázás > Új szabály lehetőségre. A megnyíló Formázási szabály szerkesztése ablakban tegye a következőket:
Válassza a „Minden cella, amely a „Szórás” értéket mutatja a rendelés dátumához” lehetőséget.
Válassza az „Ikonkészletek” lehetőséget a Formátumstílus listából.
Válassza ki a piros, borostyánsárga és zöld háromszöget az Ikonstílus listából.
A Típus oszlopban módosítsa a listabeállítást a „Szám” szövegre a százalék helyett. Ezzel az Érték oszlop 0-ra változik. Pontosan azt, amit szeretnénk.
Kattintson az „OK” gombra, és a rendszer alkalmazza a feltételes formázást a kimutatásban.
A kimutatások egy hihetetlen eszköz, és az egyik legegyszerűbb módja az értékek százalékos időbeli változásának megjelenítésére.