A kimutatások ismerete és használata jelentősen megkönnyíti a munkádat! Ha már használod alapszinten, mutatok 5+1 hasznos Pivot tábla trükköt, beállítást, amellyel még hatékonyabban fogod tudni használni.
Ha még nem ismered a kimutatások használatát, olvasd el a Pivot tábla készítése 3 perc alatt bejegyzésünket az excelneked.hu oldalunkon.
Töltsd le a fájlt a gyakorláshoz innen.
1. Pivot tábla trükk – Értékösszegzési szempont
Az első példában értékesítőnként szeretném megmutatni az átlagos bevételt. Ehhez a számla nettó összegét, amit automatikusan összegként hoz a kimutatás, átállítjuk átlagra. Legkönnyebben a jobb gombos menübél éred el. Kattints jobb egérgombbal a számla nettó összege mező bármelyik értékére, majd az Értékösszegzési szempontok közül válaszd az átlagot (Summarize Values By)
Megváltoznak az összegek átlagra, és a mező neve is átvált
Láthatod, hogy van még pár lehetőség, nyugodtan próbáld ki őket.
2. Pivot tábla trükk – Számformátum
Az átláthatóság kedvéért állítsd át a számformátumot! Nézzük meg előbb, hogy készült a következő kimutatás. A Számla nettó összegét húztam kétszer az értékekhez, ezáltal az egyiket átállítottam minimumra, a másikat maximumra az értékösszegzési szempontnál.
A számformátum beállításához kattints jobb egérgombbal a mező bármelyik celláján, majd válaszd a Számformátum lehetőséget (Number Format).
A megjelenő ablakban állítsd be a megszokott módon a számformátumot. Pl: Pénznem, Ft, 0 tizedesjegy.
Végezd el ugyanezt a másik mezőre is:
Fontos, hogy ezt a módszert használd, és ne úgy add meg a számformátumot, hogy kijelölöd a cellákat és a Kezdőlapon beállítod a Szám csoportban, vagy a Jobb gombos menüből a Cellaformázásra kattintva. Ezeken a helyeken ugyanis csak a kijelölt cellákat fogja formázni, aminek hátránya, hogyha nagyobb lesz a kimutatásod, és különböző formátumokat használsz, az Excel összezavarodhat és nem úgy fogod látni a számformátumokat, ahogy beállítottad. A jobb módszer tehát a fentiekben írt Számformátum lehetőség alatti beállítás, ami magára a mezőre alkalmazza a beállításokat.
3. Pivot tábla trükk – Mező átnevezése
Az oszlopfejléceket is átnevezheted, egy fontos szabály van, hogy olyan nevet nem használhatsz, ami szerepel már mezőként. Pl: Számla nettó összege
Az Összes nettó bevétel és a Tranzakciók száma már beszédesebb és jobban olvasható is.
Értékösszegzési szempont, számformátum és mezőnév
Az első három beállítás egy ablakban, a Mezőbeállítások ablakban is elérhető. Ezt több helyről eléred. Kattints a mező bármelyik cellájára jobb egérgombbal és válaszd az Értékmező-beállítások lehetőséget (Value Fild Settings)
Másik opció, hogy a kimutatás mezőlistában bal egérgombbal kattintasz a mezőn, majd kiválasztod ugyanezt a menüpontot.
Harmadik opció, ha a kimutatás Elemzés menüjében (fent jelenik meg a menüszalagon), rákattintasz a Mezőbeállítások gombra.
Ugyanahhoz az ablakhoz visz:
Itt először azt állítsd be, hogy mi legyen az összegzés alapja: Összeg, darab, átlag, stb.
Majd a nevet változtasd meg. Ha ezt fordítva végzed el, akkor az összegzés alapja szerinti választás felül fogja írni a korábban beírt nevet.
Majd bal alsó sarokban található a Számformátum gomb, amelyre kattintva megjelenik a formázási ablak. Ezek beállítása után csak okézd le. Használd azt a módszert, amelyik neked kényelmesebb!
4. Pivot tábla trükk – Sorrend módosítása
A kimutatás mezőinek sorrendjét szabadon módosíthatod a Kimutatásmezők ablakban.
Az alábbi kimutatásban szeretném módosítani az oszlopok sorrendjét, és a sorokban is legyen megcserélve a kategória és az értékesítő neve.
Ehhez csak fogd meg az adott mezőt a kimutatásmezők ablakban, és cseréld meg őket a területen belül. Fogd meg például az Értékesítőt és húzd a Kategória alá. Itt fogsz látni egy zöld vonalat, ahova el fogja helyezni, itt engedd el.
Előtte:
Utána:
5. Pivot tábla trükk – Értékek megjelenítése
A következő példában szeretném azt látni, hogy értékesítőnként mekkora volt a teljes bevétel, egy másik oszlopában pedig azt, hogy az adott értékesítőnél megjelenő bevétel hány százaléka az összes bevételnek.
Ehhez kétszer kell behúzni a számla nettó összegét az értékekhez, majd a másodikon kattints jobb egérgombbal és válaszd az Értékek megjelenítése (Show values as) lehetőséget!
Innen válaszd a végösszeg százaléka opciót és már láthatod is az eredményt a kimutatásban.
+1 Pivot tábla trükk: Oszlopszélesség
Plusz egy beállítás, amivel elkerülheted, hogy frissítésnél az Excel automatikusan igazítsa az oszlopszélességeket. Nagyon bosszantó, amikor beállítod a kimutatásoknál az oszlopszélességeket, minden szépen látszik, majd egy frissítésnél az összeset igazítja, ezzel „elrontva” a Te beállításodat.
Kattints a kimutatáson jobb egérgombbal és válaszd a Kimutatás beállításai lehetőséget (PivotTable Options).
Az Elrendezés és formátum fülön (Layout & Format) megtalálod alul az Oszlopszélesség automatikus beállítása frissítéskor (Autofit column widths on update) lehetőséget, innen vedd ki a pipát. Ezt állítsd be a többi kimutatáshoz is.
Kimutatás alapbeállításai (2019+)
Ha Office365, vagy Excel 2019-es verziót használsz, akkor egy nagyon szuper beállítással találkozhatsz. Be tudjuk ugyanis állítani a kimutatások alapbeállításait. Vagyis nem kell egyesével beállítanod a kimutatásoknál, automatikusan úgy fogja létrehozni.
Navigálj a Fájl, Beállítások részére (File -> Options). Majd itt Adatok, azon belül Alapértelmezett elrendezés szerkesztése (Data -> Edit default layout).
A megjelenő ablakban beállíthatod a részösszegekre, végösszegekre és a kimutatás elrendezésére vonatkozó lehetőségeket.
Jobb alul pedig a Kimutatás beállításai gombra kattintva további beállítások érhetők el.
A kimutatás beállításai ablak jelenik meg, ahol beállíthatod például az oszlopszélesség beállítására vonatkozó lehetőséget, a hibaértékek, üres cellák esetén megjelenítendő szöveget, illetve a többi fülön található opciókat.
Ha hasznosnak találtad, kérlek, oszd meg másokkal is!
