Hivatkozott Excel verzio: Office365 Online
Kimutatások készítésekor gyakran felmerülhet az, hogy kategóriánként vagy csoportonként megszámoltassuk az eltérő (egyedi) értékek darabszámát. Ez egy típus probléma, amire a közelmúltig nem is volt annyira egyszerű választ adni. Ugyanis az Excel sem a sima munkalapfüggvényei között, sem a PIVOT tábla összesítő függvényei között nem tartalmazott olyan függvényt, ami az ismétlődések eltávolításával, csak az egyedi értékeket tartja meg vagy számolja össze.
Mi is itt a probléma?
Először egy kis méretű esettanulmányon mutatnám be, hogy mit is szeretnénk, amit a következő kép illusztrál:
A bal oldali táblázatban két csoport látható sorba rendezve, A és B néven. A hozzájuk tartozó értékek között csoporton belül is van ismétlődés. Az „A” csoportnak 4 értéke van, de csak két különböző, a 10 és a 12. A „B” csoportnak is 4 értéke van, de itt három különböző értéket találunk: 10, 13, 14.
Új függvények
Az Excel 365 desktop és online verziójába folyamatosan érkeznek újabb és újabb függvények. Ezek közül kettőről külön bejegyzésekben már korábban megemlékeztem:
- EGYEDI / UNIQUE
- SZŰRŐ / FILTER
- SORBA.RENDEZ / SORT (Erről eddig még nem írtam)
Most ezek kombinált használatára lesz szükség. A szöveges leírásban ismertetett megoldás csak az újabb függvényeket használja, de a videóban van két másik lehetőség, is, amik korábbi Excel verziókban is működnek.
Megoldás – új függvényekkel
A fenti probléma két részfeladatból áll. Egyszer kell képeznünk a bal oldali tábla első oszlopából az egyediek listáját. Ezt az egyedi függvénnyel készítjük el, az „A2:A9” tartomány alapján. Nem lepődünk meg, hogy az eredmény egy két elemű tömb lesz, ami „A” és „B” csoportneveket tartalmaz.
Ezután jön az a formula, ami az „A” csoportra megmondja, hogy mennyi különböző érték tartozik hozzá. Ez valójában 3 részből áll:
- Értékek szűrése az „A” csoportra
- A kapott értékek tovább szűrése csak az egyediekre
- Az egyediek megszámolása
Nézzük lépésenként:
„Érték” oszlop szűrése
A „B” oszlopban szűrünk, és a szűrő logika az, hogy az „A” oszlopban a mellette lévő, azaz a D2-ben lévő csoportnév van, ami ugye az „A”.
Az eredmény egy tömb, ami csak az „A” csoport, egyelőre még ismétlődő értékeit tartalmazza.
Egyediek megszámoltatása
Az előbb megkapott tömböt odaadjuk az EGYEDI függvénynek, ami kiszűri az ismétlődéseket, majd ennek eredményét odaadjuk a DARAB2 függvénynek, ami megmondja az elemszámot. Mivel a képletet le fogjuk húzni a következő sorra, hogy a „B” csoportra is megcsinálja a megszámlálást, a formulán kicsit javítunk, rögzítjük a tartományokat.
A végleges képletet kimásolható módon is megadom:
=DARAB2(EGYEDI(SZŰRŐ($B$2:$B$9;$A$2:$A$9=D2)))
Letölthető munkafüzet
Csoportosított-egyedi-darabszám
