Az Excel nem csupán adatok tárolására és egyszerű számításokra használható. Komplex elemzéseket is végezhetünk benne, például két vagy több tömb (tartomány) összehasonlítását. Ebben a cikkben azt vizsgáljuk meg, hogyan számolhatjuk meg az azonos elemek számát két Excel tömbben egyetlen képlettel, anélkül, hogy bonyolult VBA kódokhoz kellene folyamodnunk.
Miért fontos tömböket összehasonlítani?
Számos üzleti és tudományos területen szükség lehet arra, hogy két adatlistát (tömböt) összehasonlítsunk. Például:
- Készletkezelés: Ellenőrizni, hogy a raktári nyilvántartás és a tényleges készlet megegyezik-e.
- Vásárlói adatok: Duplikált bejegyzések kiszűrése egy marketing adatbázisban.
- Minőségellenőrzés: Két különböző gyártási sor termékeinek azonossági ellenőrzése.
- Tudományos kutatás: Két mérési sorozat azonos értékeinek meghatározása.
Az Excel ebben a feladatban is hatékony segítséget nyújt, különösen akkor, ha nagyméretű adathalmazokkal dolgozunk.
A SZUMHA (SUMIF) és a DARABTELI (COUNTIF) függvények használata
Az Excel két kulcsfontosságú függvénye, a SZUMHA (SUMIF) és a DARABTELI (COUNTIF), lehetővé teszi, hogy a célunkat elérjük. Mindkét függvény feltételes aggregációt végez, de különböző módon.
A DARABTELI (COUNTIF) függvény megszámolja, hogy egy adott tartományban hány elem felel meg egy adott feltételnek. Például, ha A1:A10 tartalmazza az első tömbünket, és azt szeretnénk megszámolni, hogy az „alma” hányszor szerepel benne, akkor a képlet: =DARABTELI(A1:A10;"alma").
A SZUMHA (SUMIF) függvény ezzel szemben összeadja egy tartomány azon értékeit, amelyek egy adott feltételnek megfelelnek. Bár elsőre nem tűnik egyértelműnek, hogyan kapcsolódik ez az azonosságok kereséséhez, a trükk abban rejlik, hogy a második tömböt használjuk kritériumként a DARABTELI függvényben, majd a SZUMHA függvénnyel összeadjuk a találatokat.
A képlet megértése lépésről lépésre
A következő képletet használjuk az azonos elemek számának meghatározására:
=SZUMHA(B1:B10;A1:A10;1)Magyarázat:
- A1:A10: Ez az első tömb, amelyben keresünk.
- B1:B10: Ez a második tömb, amelynek elemeit használjuk feltételként.
- 1: Ez a tartomány, amely összeadásra kerül. Ebben az esetben minden feltételnek megfelelő elemhez hozzáadunk 1-et. Ez a viselkedés teszi alkalmassá a SZUMHA-t a darabszám meghatározására.
A képlet lényegében azt csinálja, hogy a B1:B10 tömb minden elemére lefuttatja a DARABTELI(A1:A10; elem)-t, és ezeknek az eredményeit összeadja. Ha a B1:B10 tömb tartalmazza pl. az „alma” szót kétszer, és az A1:A10 tömbben az „alma” háromszor szerepel, akkor a képlet kétszer számolja meg az „alma” előfordulásait az A1:A10-ben. Tehát a képlet pontosabban azt adja meg, hogy a B1:B10 minden egyes eleme hányszor szerepel az A1:A10-ben, majd ezeket összeadja. Ez nem feltétlenül jelenti az azonosságok *darabszámát*, hanem a *B tömb elemei hány darab A tömbben szereplő elemmel azonosak*. Ha a célunk az, hogy az A és B tömbben *legalább egyszer* előforduló azonos elemek számát határozzuk meg, akkor a képlet nem alkalmas.
Példa
Tegyük fel, hogy az A1:A5 tartomány a következő értékeket tartalmazza: „alma”, „körte”, „banán”, „alma”, „szilva”. A B1:B5 tartomány pedig: „alma”, „banán”, „narancs”, „alma”, „szőlő”. A =SZUMHA(B1:B5;A1:A5;1) képlet eredménye 4 lesz, mert a B1:B5 tömb elemei közül az „alma” kétszer, a „banán” egyszer, és a „narancs”, „szőlő” egyszer sem szerepel az A1:A5 tömbben, és 2+1+0+0=4.
Fontos megjegyzések és alternatív megoldások
- A fenti képlet érzékeny a kis- és nagybetűkre. Ha nem szeretnénk figyelembe venni a betűméretet, akkor a
NAGYBETŰS(UPPER) vagy aKISBETŰS(LOWER) függvényekkel konvertálhatjuk mindkét tartományt ugyanarra a betűméretre. Például:=SZUMHA(NAGYBETŰS(B1:B10);NAGYBETŰS(A1:A10);1). - Ha a tömbök nem egyforma méretűek, akkor a kisebb tömb méretét kell használnunk a képletben.
- Bonyolultabb esetekben, amikor feltételeket kell figyelembe venni az összehasonlítás során, a SZUMHATÖBB (SUMIFS) függvény is használható.
- Ha az a cél, hogy csak az *egyedi* azonos elemeket számoljuk meg (azaz ha egy elem többször szerepel mindkét tömbben, akkor csak egyszer számoljuk), akkor bonyolultabb tömbképleteket kell alkalmaznunk, ami meghaladja ennek a cikknek a kereteit. Egy lehetséges megoldás a
FREKVENCIA(FREQUENCY) függvény használata. - A COUNTIFS függvény is használható az azonos elemek megszámlálására, ha mindkét tömb azonos méretű és egymás mellett helyezkedik el. Pl.:
=COUNTIFS(A1:A5,B1:B5). Ez a képlet azt számolja meg, hogy hány sorban egyezik meg az A és B oszlop értéke.
Összegzés
A SZUMHA (SUMIF) függvény segítségével egyszerűen és hatékonyan számolhatjuk meg az azonos elemek számát két Excel tömbben. Bár a képlet működése elsőre nem egyértelmű, a fenti magyarázat segítségével könnyen megérthetjük a logikát, és alkalmazhatjuk a saját feladatainkhoz. Ne feledkezzünk meg a speciális esetekről (betűméret érzékenység, eltérő tömbméretek), és használjunk alternatív megoldásokat, ha a feladatunk bonyolultabb.