Az Excel adatkezelése 5. (kimutatások)

Elérkeztünk az Excel adatkezeléséről szóló cikksorozat utolsó írásához, mely egészen biztosan a legnagyobb tetszést fogja aratni. A kimutatások segítségével ugyanis valami fantasztikus eszköz kerül a hozzáértők kezébe:

• egyszerre akár négy-öt különféle szempont szerint is összesíthetjük letárolt adatainkat,
• az összesítő függvények teljes köre rendelkezésre áll (összeg, darabszám, átlag, minimum, maximum, szórás, varp, stb.),
• ráadásul a fenti függvényekből egyszerre akár többet  is alkalmazhatunk úgy, hogy minden teljesen áttekinthető lesz,
• stb.

Remélhetően kellően sikerült felcsigázni mindenki érdeklődését a kimutatások (angolul Pivot table)  iránt, úgyhogy vágjunk is bele!

Kimutatás létrehozásának elindítása

Első lépésben álljunk bele az adatokba, majd lapozzunk a "Beszúrás" menüszalagra, végül az annak a bal szélén lévő "Kimutatás" ikonra kattintsunk rá.

Akkor egy párbeszédablak fog megjelenni, amin kattintsunk az OK gombra. Feltéve persze, hogy jól jelent meg az a terület, amelynek adataiból a kimutatást készíteni akarjuk. Ha azonban a művelet elindítása előtt beleálltunk az adatokba, ez biztosan így is lesz.

Ekkor egy új munkalap fog beszúródni, melyen össze kell állítanunk magát a kimutatást.

A kimutatás összeállítása

A következő lépés annak a meghatározása, hogy miként kerüljön elkészítésre maga a kimutatás. Ezt a könnyebb érthetőség kedvéért a korábbi adatkezelésről szóló posztokban is példaként alkalmazott, és innét letölthető, 5.476 tételből álló adatbázison keresztül fogjuk megbeszélni. Ebben a táblázatban gépkocsik fiktív eladási adatai szerepelnek. Egészen konkrétan az autók márkája (16 márka), típusa (márkánként 3-5 típus), eladási ára, az eladás hónapja (1-12) és helye, utóbbi Magyarország EU régióival meghatározva (ebből ugye 7 darab van).

Tételezzük fel, hogy főnökünk ki szeretne velünk tolni, és a következő feladattal bíz meg minket:

• Havi bontásban, minden márka minden típusának eladási adatait kell összesíteni, mégpedig azt, hogy hány darabot adtak le, és mennyi volt az átlagos eladási ár. Ha kell, ezt tudjuk produkálni több, tetszőlegesen kiválasztott, vagy akár csak egyetlen régióra is. Adok erre két napot!

Természetesen hagyományos módszerrel ennek az összesítésnek az elkészítésébe belegebednénk, az egészen biztos. Mindez azonban az Excelt használva legfeljebb 5 perces munka. Nem vicc!

Lássuk csak miről is van szó. A feladat korrektül lett megfogalmazva, és az abban foglaltak szerint az összesítési szempontok a következők:

• régió (7 értéket vehet fel),
• márka (ami 16 érték lehet, és azon belül további 3-5 típus),
• és eladási hónap (ami persze 12 létezik).

Ezeket, mint fejléceket kell megadni (a az Excel program 2010-es verziója "jelentésszűrő", "oszlopmező" és "sormező" néven hivatkozik rájuk). De az sem mindegy, hogy mit minek adunk meg.

• Ami a legtöbb különféle értéket veheti fel, az legyen a "sormező". Mivel jelen feladatban a márka veheti fel a legtöbb értéket, példánkban ez lesz a sormező.
• Ami kevesebb különféle értékű lehet, az legyen majd az "oszlopmező".

Ezekre a javaslatokra az a magyarázat, hogy így könnyebben áttekinthető lesz a táblázat, mert egyszerre sokkal több sort, mint oszlopot láthatunk (hiszen egy cella sokkal szélesebb a magasságánál).

• Amire nézve nem szerepel majd a bontás, csak legfeljebb szűrni kell rá, az legyen a "jelentésszűrő". Példánkban a főnök ezt a régiókkal kapcsolatban kérte ("Ha kell, ezt tudjuk produkálni több, tetszőlegesen kiválasztott, vagy akár csak egyetlen régióra is.").

Annyi csak a dolgunk, hogy a jobb oldalon felsorolt mezőneveket a bal oldalon a megfelelő helyekre húzzuk az egérrel. Ahová több mezőt is kell húzni, ott vigyázzunk arra, hogy egymáshoz képest hogy helyezkedjenek el a mezők: esetünkben a "márkától" jobb, azaz azon belül kell lennie a "típusnak" (de a "sormező" területen). Azt, hogy mit hová kell húzni, az ábra is mutatja:

Az összesítési módszer meghatározása

Amennyiben nem azt az összesítő függvényt akarjuk alkalmazni, mint amelyikkel az összesítést az Excel elkészíti (számokat összeadja a szumma, szöveges mezőket pedig megszámol a darabszám függvénnyel), akkor a következőket tegyük:

• a jobb alsó sarokban nyissuk le azt a függvény+mezőnévből álló legördülő listaelemet, amelyben a függvényt meg szeretnénk változtatni, majd kattintsunk rá az "Értékmező beállítások" elemre,

Itt kell lenyitni a megváltoztatandó függvényt

• adjuk meg az új függvényt.

Itt válasszuk ki az alkalmazni kívánt függvényt

Számformátumok

Ahhoz, hogy áttekinthetőbbek legyenek az összesítések, szerencsés, ha a számok mögött ott lesz a mértékegységük, valamint a nagy számok ezres csoportosításban jelennek meg. Természetesen a tizedesek számát is beállíthatjuk, ha úgy gondoljuk. Ennek menete a következő:

• most is a jobb alsó sarokban kell a módosítandó függvény+mezőnévből álló legördülő listaelemet lenyitni, majd rákattintani az "Értékmező beállítások" elemre,
• a kapott ablakban kattintsunk rá a "Számformátum" gombra,

Itt kell elindítani a számformátum megadását

• az ekkor megjelenő újabb ablakban a bal oldalon válasszuk ki a megfelelő kategóriát (pénznem, százalék),

Ezek közül kell általában választanunk

• végül állítsuk be a tizedesek számát, és addig nyomkodjuk az OK gombokat, amíg vissza nem kapjuk az immáron formázott számokat tartalmazó táblázatunkat.

Adjuk meg a tizedesek számát

Amennyiben mértékegységet szeretnénk a szám mögé írni (pl. darab), akkor válasszuk az "Egyéni" elemet, majd adjuk meg a formátumkódot az alábbiak szerint:

• válasszuk az "Egyéni" számformátumot,
• kattintsunk rá a "# ##0" elemre,
• kattintsunk bele a "Formátumkód mezőbe, majd a végére idézőjelbe írjuk be a mértékegységet.

Így adhatunk meg egy mértékegységet

Nagyon fontos, hogy ha azt akarjuk, hogy a szám és a mértékegység között legyen egy szóköz, annak is az idézőjelen belül kell lennie!

Csoportosítás

Hogyha az adatokat csoportosítani is szükséges, természetesen erre is van lehetőség. Mit kell ez alatt érteni? Lássunk rá két, a mintafeladatbeli példát:

• a hónapokból negyedéves összesítés
• a márkákból nemzeti összesítések (német, olasz, francia márkák)

Ezek után lássuk, hogy miként kell az adatokat csoportosítani:

• jelöljük ki az egy csoportba sorolandó elemeket (az első elemre kattintsunk rá, a többire a Ctrl billentyű nyomása közben),
• kattintsunk rá a "Kijelöltek csoportosítása" ikonra.

Csoport átnevezése, elemeinek elrejtése

Ha egy csoportot át szeretnénk nevezni, akkor egyszerűen álljunk rá a nevére, majd gépeljük be a nevét. Amennyiben pedig nem szeretnénk külön-külön is látni a csoport elemeinek értékét, akkor kattintsunk rá a csoport neve előtt látható mínuszjelre.

Ehhez a témakörhöz ezt a videót ajánlom (46. perctől a videó végéig, kb. 1:18-ig). Aki a videóban szereplő dolgokat maga is ki akarja próbálni, a minta adatokat innét le is lehet tölteni.

Ha tetszett, ne felejtsed el a cikket megosztani! A felső Facebook lájk és megosztás a PC Suli Facebook oldalára vonatkozik, ez pedig a cikkre.

Ha a jobb oldalon fent lévő reklám olyasmiről szól, ami érdekel, kérlek nyissad meg. Ez Neked semmibe nem fog kerülni, a PC Sulit viszont - tényleg csak pár forinttal - támogatja. Előre is köszönöm!