Egyedi értékek megszámolása

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

Azt a problémát járjuk most körbe, hogy hogyan lehet megszámolni egy oszlopban lévő különböző egyedi értékek számát. Tehát olyan, mintha az ismétlődések eltávolítása után megszámolnánk a maradék elemek számát.

Alapvetően két elv szerint oldható meg a probléma. Az egyik egy segédoszlopoban használ egy képletet, és azt használjuk az összesítéshez. A másik pedig a már-már szokásosnak nevezhető tömbképlet. Ez utóbbiból hármat is összeszedtem, de bizonyára vannak még továbbiak is.

A példában a vizsgált oszlopban számok vannak, de szöveges, vagy vegyes típusú adatok esetén is működnek a megoldások.

Segédoszlopos megoldás

Az ötlet az, hogy a tartomány minden elemére nézzük meg, hogy hányadszor fordul elő az egész oszlopban, és ha először, akkor adjunk vissza egyet, ha már többedszer, akkor nullát. Végül összeadjuk a segédoszlopot, és megkapjuk az egyedi elemek számát.

Van itt egy nagyon érdekes trükk, amit több helyzetben lehet használni, ha megismertük. Ez pedig az a tartományi hivatkozás, ami az elejétől az aktuális sorig hivatkozik. A megoldás az „A2” cellában kezdődő oszlop esetén a következő:

$A$2:A2

Tehát a kezdőcellát lefixáljuk, a második cellát, ami kezdetben ugyanaz, mint a kezdőcella, pedig nem. Ebből következően, ha elkezdjük lefelé húzni, akkor a hivatkozás mindig az A2-n kezdődik, és az aktuális sorban végződik.

=SZUM($A$2:A2)

Lássuk a medvét!

Ha először találkozik egy értékkel a B oszlopban, akkor egyest ad, különben nullát. Ezután nem lesz más dolgunk, mint összeadni a C oszlop celláit, és megvagyunk.

Tömbképletes megoldások

A különböző megoldások abban különböznek, hogy hogyan kezelik a nulla tartalmú és az üres cellákat. Mindhárom képlet az összesítést a SZORZATÖSSZEG ( SUMPRODUCT ) függvénnyel végzi, és nem szükséges Ctrl-Shift-Enterrel lezárni.

Teljesen kitöltött tartomány

Ez a képlet a legegyszerűbb, de bátran alkalmazhatjuk, ha a vizsgálandó tartomány minden cellája tartalmaz értéket.

=SZORZATÖSSZEG(1/DARABTELI(B4:B11;B4:B11))

Minden elemre kiszámolja, hogy mennyiszer van benne a tartományban, és minden eredménynek veszi a reciprokát, majd az így kapott tömb elemeit összegzi. Azon az elven működik, hogy ha egy érték többször szerepel, akkor minden előfordulásánál ugyanaz a szám lesz. Ezek reciprokainak összege pedig 1-et ad.

Vegyük pl. , hogy egy érték háromszor van benne a tartományban. A DARABTELI minden előfordulásra 3-at fog adni, ezek reciproka 1/3. Ezeket összeadva pedig 1-et kapunk.

Üres cellák beleszámolása

Ez a képlet az üres cellát mint egyedi értéket figyelembe fogja venni, tehát a képen látható tartományra 5 különböző értéket fog visszaadni. (6, 7, 9, 10, üres). Ahogy látszik, csak az a különbség az előzőhöz képest, hogy a második tömb elemeihez (így az üres cellákhoz is)  hozzáfűz egy üres sztringet, így kerülve el az ürességet.

=SZORZATÖSSZEG(1/DARABTELI(D4:D11;D4:D11&""))

Üres cellák figyelmen kívül hagyása

Ha az üres cellákat nem szeretnénk beleszámolni az eredménybe, akkor ez lesz a mi barátunk. Az előző képlet van kibővítve úgy, hogy egy megelőző tömbben csak oda tesz IGAZ (1) értéket, ahol nem üres a cella. Ezt a tömböt szorozza a második tömb elemeivel, ahol az előfordulás számok vannak. Az üres cellák helyén nulla lesz, a többi helyen megmarad az előfordulás szám. Tehát a példánkban most 4-et kapunk, az üres cella nem lesz benne az összesítésben.

=SZORZATÖSSZEG((NEM(ÜRES(D4:D11))*(1/DARABTELI(D4:D11;D4:D11&""))))

Letölthető minta munkafüzet

Egyedi-ertekek-megszamolasa

A VIDEÓ mutatja működés közben.