Az Excel egy elképesztően sokoldalú eszköz, amely a mindennapi adatelemzéstől kezdve a komplex pénzügyi modellekig szinte mindenre alkalmas. Gyakran előfordul, hogy egy adott adathalmazból nem csupán az átlagot vagy az összegét szeretnénk kinyerni, hanem mélyebb betekintést nyerünk a benne rejlő mintázatokba. Ilyenkor merül fel a kérdés: mi a leggyakoribb érték? És ami még érdekesebb és kihívást jelentőbb: mi a második leggyakoribb szám egy számsorból?
Elsőre talán egyszerűnek tűnik a feladat, hiszen az Excel rendelkezik a MÓDUSZ.EGY (vagy angolul MODE.SNGL) függvénnyel, amely a leggyakoribb értéket adja vissza. De mi van akkor, ha a második leggyakoribb értékre vagyunk kíváncsiak, vagy ha több érték is ugyanannyiszor fordul elő a legmagasabb gyakorisággal? Ebben a cikkben részletesen bemutatjuk, hogyan birkózhatunk meg ezzel a feladattal, figyelembe véve a különböző Excel verziók képességeit és a lehetséges kihívásokat.
Miért nem elég a „MÓDUSZ” függvény?
Mielőtt belevágnánk a megoldásba, tisztázzuk, miért nem elegendőek az alapvető statisztikai függvények erre a célra. Az MÓDUSZ.EGY függvény (korábbi verziókban egyszerűen MÓDUSZ) csak az egyetlen leggyakoribb értéket adja vissza. Ha több érték is ugyanazzal a legmagasabb gyakorisággal szerepel, ez a függvény csak az elsőt fogja visszaadni a listából.
Létezik emellett az MÓDUSZ.TÖBB (angolul MODE.MULT) függvény is, amely tömbképletként visszaadja az összes olyan értéket, amely a legmagasabb gyakorisággal szerepel. Ez már közelebb visz minket, de még mindig csak a leggyakoribb értékekkel foglalkozik, nem a második leggyakoribbal.
Képzeljük el a következő számsort: [10, 20, 30, 20, 10, 40, 50, 20, 30].
- A
20háromszor fordul elő. - A
10és a30kétszer fordul elő. - A
40és az50egyszer fordul elő.
Ebben az esetben a MÓDUSZ.EGY a 20-at adná vissza. Nekünk azonban a 10-re vagy a 30-ra, azaz a második leggyakoribbra van szükségünk (vagy mindkettőre, ha úgy tetszik). Látható tehát, hogy egy komplexebb megközelítésre van szükség.
A Második Leggyakoribb Szám Megkeresésének Logikája
A probléma megoldásához több lépésben kell gondolkodnunk:
- Egyedi értékek kinyerése: Elsőként ki kell gyűjtenünk a számsorban található összes egyedi (különböző) számot.
- Gyakoriságok számlálása: Ezután meg kell számolnunk, hogy az egyes egyedi számok hányszor fordulnak elő az eredeti adatsorban.
- Gyakoriságok rangsorolása: A megszámlált gyakoriságokat csökkenő sorrendbe kell rendeznünk, majd meg kell keresnünk a második legnagyobb EGYEDI gyakoriságot. Fontos, hogy itt az egyedi gyakoriságokat keressük, nem feltétlenül a második leggyakoribb számot a leggyakoribbak között. Ha például a leggyakoribb érték 5-ször, a következő 5-ször, a harmadik 4-szer fordul elő, akkor a második leggyakoribb gyakoriság még mindig az 5 lesz. Nekünk a második különböző gyakoriságra van szükségünk, ami ebben az esetben a 4.
- Az értékek azonosítása: Végül ki kell szűrnünk azokat az egyedi számokat, amelyeknek a gyakorisága megegyezik a második legnagyobb egyedi gyakorisággal.
Ez a folyamat viszonylag egyszerűvé válik az Excel legújabb, Microsoft 365 verziójában elérhető dinamikus tömbfüggvények segítségével. A régebbi Excel verziókban (Excel 2019, 2016, 2013, 2010 stb.) ez a feladat jóval bonyolultabb, tömbképleteket vagy segédoszlopokat igényel.
Megoldás Excel 365-ben: Dinamikus Tömbökkel
Az Excel 365 hozta el a dinamikus tömbök forradalmát, amelyek nagyban leegyszerűsítik az ilyen típusú feladatokat. Az EGYEDI (UNIQUE), SZŰRŐ (FILTER) és DARABTELI (COUNTIF) függvények kombinálásával elegáns megoldást kapunk.
Tegyük fel, hogy az adataid az A1:A100 tartományban vannak. (Természetesen ezt a tartományt a saját adataidhoz igazíthatod.)
1. Lépés: Az egyedi számok listázása és gyakoriságuk meghatározása
Először is, gyűjtsük ki az egyedi számokat az adatsorból, és számoljuk meg, hányszor fordul elő mindegyik. Ezt megtehetjük két segédoszloppal, vagy egyetlen tömbképlemmel.
=EGYEDI(A1:A100)Ez a képlet listázza az összes egyedi számot. Tegyük fel, hogy ez a lista a C1 cellától kezdődően jelenik meg (ha üres oszlopban van hely). A gyakoriságokhoz használjuk a DARABTELI függvényt:
=DARABTELI(A1:A100;C1#)A C1# hivatkozás (más néven „spill range operator”) azt jelenti, hogy a C1 cellától kezdődően az EGYEDI függvény által kiterjesztett tartományra hivatkozunk. Ez a képlet automatikusan kiszámolja minden egyedi szám gyakoriságát.
Alternatív megoldás, ha egy lépésben akarjuk a gyakoriságokat meghatározni egy segédtáblában:
=LET(
Adatok; A1:A100;
Egyediek; EGYEDI(Adatok);
Gyakorisagok; DARABTELI(Adatok; Egyediek);
RENDEZ.SZERINT(EGYESÍT(Egyediek; Gyakorisagok); 2; -1)
)Ez a LET függvény egy táblázatot hoz létre az egyedi számokkal és azok gyakoriságaival, csökkenő gyakorisági sorrendben rendezve. Ez csak a vizualizációt segíti, de a következő lépésekhez szükségünk lesz a gyakoriságok listájára.
2. Lépés: A második legnagyobb egyedi gyakoriság meghatározása
Most, hogy van egy listánk az egyedi számokról és a hozzájuk tartozó gyakoriságokról (legyenek ezek például a C:D oszlopokban, ahol C az egyedi szám, D pedig a gyakoriság), meg kell találnunk a második legnagyobb EGYEDI gyakoriságot.
=NAGY(EGYEDI(D:D); 2)Vegyük észre az EGYEDI(D:D) részt. Ez azért fontos, mert ha például a leggyakoribb szám 5-ször fordul elő, és a másik leggyakoribb is 5-ször, de egy másik szám is 4-szer, akkor a NAGY(D:D; 2) még mindig az 5-öt adná vissza (mint a második 5-ös gyakoriságot). Nekünk azonban a 4-re van szükségünk, ami a *következő különböző* gyakoriság. Az EGYEDI függvény biztosítja, hogy csak a különböző gyakorisági értékek közül válasszunk.
3. Lépés: A második leggyakoribb szám(ok) kiszűrése
Végül, használjuk a SZŰRŐ függvényt, hogy kiválasszuk azokat az egyedi számokat, amelyeknek a gyakorisága megegyezik az előző lépésben meghatározott második legnagyobb egyedi gyakorisággal.
Tegyük fel, hogy a második legnagyobb egyedi gyakoriság értéke egy cellában van, mondjuk az F1 cellában, és az egyedi számok listája a C1# tartományban van, a gyakoriságok listája pedig a D1# tartományban (ahol ezek az EGYEDI és DARABTELI függvények „spill” eredményei).
=SZŰRŐ(C1#; D1# = F1)Ez a képlet visszaadja az összes olyan számot, amely a második legnagyobb egyedi gyakorisággal rendelkezik.
Egy Összetett Képlet (Összes Lépés Egyben)
Az Excel 365 dinamikus tömbök erejét kihasználva a teljes megoldás egyetlen, elegáns képletbe foglalható a LET függvény segítségével:
=LET(
Adatok; A1:A100;
Egyediek; EGYEDI(Adatok);
Gyakorisagok; DARABTELI(Adatok; Egyediek);
EgyediGyakorisagok; EGYEDI(Gyakorisagok);
MasodikLegnagyobbGyakorisag; NAGY(EgyediGyakorisagok; 2);
SZŰRŐ(Egyediek; Gyakorisagok = MasodikLegnagyobbGyakorisag; "Nincs második leggyakoribb szám")
)Magyarázat lépésről lépésre:
Adatok; A1:A100;: Definiálja az adataink tartományát. Ez rugalmasságot biztosít.Egyediek; EGYEDI(Adatok);: Létrehozza a forrástartományban található összes egyedi érték tömbjét.Gyakorisagok; DARABTELI(Adatok; Egyediek);: Megszámolja, hányszor fordul elő minden egyes egyedi érték az eredetiAdatoktartományban.EgyediGyakorisagok; EGYEDI(Gyakorisagok);: Kigyűjti a gyakoriságok listájából az egyedi gyakorisági értékeket. Ez elengedhetetlen a „valódi” második legnagyobb gyakoriság megtalálásához, elkerülve az azonos gyakoriságú értékek duplikálását.MasodikLegnagyobbGyakorisag; NAGY(EgyediGyakorisagok; 2);: Keresi meg a második legnagyobb értéket azEgyediGyakorisagoklistából. Ez lesz az a gyakorisági szint, amit keresünk.SZŰRŐ(Egyediek; Gyakorisagok = MasodikLegnagyobbGyakorisag; "Nincs második leggyakoribb szám"): Végül kiszűri azEgyediektömbből azokat az értékeket, amelyek gyakorisága megegyezik aMasodikLegnagyobbGyakorisagértékével. Ha nincs ilyen érték (pl. mert nincs második egyedi gyakoriság), akkor a „Nincs második leggyakoribb szám” szöveget adja vissza.
Megoldás Régebbi Excel Verziókban (Tömbképletek / Segédoszlopok)
A dinamikus tömbök hiányában a feladat sokkal bonyolultabbá válik, és gyakran tömbképleteket vagy segédoszlopok sorozatát igényli.
Segédoszlopok alkalmazása (ajánlott régebbi verziókhoz)
Ez a módszer könnyebben átlátható és hibakereshető.
- Egyedi értékek listázása: Ezt manuálisan vagy az „Adatok” menü „Speciális szűrő” funkciójával tehetjük meg, pipa bejelölve a „Csak egyedi rekordok” opciót. Tegyük fel, hogy ez a lista a
Coszlopba kerül. - Gyakoriságok számolása az egyedi értékekhez: A
Doszlopba, aD1cellába írjuk be a következő képletet, majd másoljuk le lefelé az összes egyedi érték mellé:=DARABTELI(A:A; C1)(Feltételezve, hogy az eredeti adatok az
Aoszlopban vannak, és az egyedi értékek aCoszlopban.) - A gyakoriságok egyedi listájának létrehozása és rendezése: Hozzuk létre a
Doszlopban lévő gyakoriságok egyedi listáját (pl. azEoszlopba, ismét „Speciális szűrővel”). Ezt rendezzük csökkenő sorrendbe (Adatok -> Rendezés). - A második legnagyobb egyedi gyakoriság meghatározása: A rendezett egyedi gyakoriságok listájából (mondjuk az
Eoszlopban van) könnyen megtaláljuk a második legnagyobbat (pl.=NAGY(E:E; 2)). Tegyük fel, hogy ez az érték azF1cellában található. - A második leggyakoribb szám(ok) azonosítása: Használjunk egy tömbképletet (
CTRL+SHIFT+ENTER-rel kell befejezni!) az egyedi értékek listájának (Coszlop) és a gyakoriságok listájának (Doszlop) összehasonlítására azF1cellában található értékkel. Például, ha több értéket is vissza szeretnénk kapni, akkor egy további segédoszlopban (Goszlop) a következő képletet használhatjuk (és lefelé másolhatjuk, egészen addig, amíg #SZÁM! hiba meg nem jelenik):=HAHIBA(INDEX($C$1:$C$100; KISTÖMB(HA($D$1:$D$100=$F$1; SOR($D$1:$D$100)-SOR($D$1)+1); SOR(A1))); "")Ez a képlet rendkívül komplex, és hibalehetőségeket rejt magában. Éppen ezért, ha van lehetőséged, erősen ajánlott az Excel 365-ös megoldás használata.
Komplex tömbképlet (csak nagyon haladóknak)
Léteznek egyetlen, rendkívül összetett tömbképletek is, amelyek megpróbálják ezt a feladatot megoldani régebbi Excel verziókban. Ezek azonban rendkívül nehezen érthetőek, hibakereshetőek, és a teljesítményük is jelentősen romolhat nagy adathalmazok esetén. Példaként említem meg a felépítését, de nem javaslom a használatát: magában foglalná a INDEX, HOL.VAN, DARABTELI, HA, OSZLOP, SOR, KISTÖMB és KÜSZÖB (FREQUENCY) függvényeket, tömbként beírva. Az ilyen képletek használata helyett sokkal praktikusabb a segédoszlopok alkalmazása.
Gyakori Helyzetek és Esetek Kezelése
- Ha nincs második leggyakoribb szám: Ha az összes szám egyedi, vagy csak egy szám fordul elő többször, akkor a
NAGY(EGYEDI(Gyakorisagok); 2)hibaüzenetet fog visszaadni. Az Excel 365-ösSZŰRŐfüggvényben ezt kezeltük a harmadik argumentummal ("Nincs második leggyakoribb szám"). Régebbi Excel verziókban ezt a hibát aHAHIBA(IFERROR) függvénnyel lehet kezelni. - Több szám azonos második leggyakoribb gyakorisággal: Ez a leggyakoribb forgatókönyv. Ahogy a példában is láttuk (10 és 30 is 2-szer fordul elő), a megoldásunk (főleg az Excel 365-ös
SZŰRŐfüggvény) automatikusan listázza az összes ilyen számot. Ez általában kívánatos viselkedés. Ha csak egyet szeretnél a listából (pl. a legkisebbet), akkor hozzáadhatsz egyMINfüggvényt aSZŰRŐeredménye köré. - Üres cellák vagy szöveges adatok: A
DARABTELIfüggvény nem számolja meg az üres cellákat. Szöveges értékek esetén a képleteink hibát adhatnak, ha számot várunk. Fontos, hogy a tartomány valóban számokat tartalmazzon. Ha szöveg is lehet benne, akkor aDARABTELIfunkciót kombinálni kell azE.SZÁM(ISNUMBER) függvénnyel a szűréshez.
Összefoglalás
A második leggyakoribb szám (vagy számok) megtalálása egy számsorból az Excelben egy kiváló példa arra, hogy hogyan lehet kombinálni a függvényeket a komplex adatelemzési feladatok megoldására. Míg az Excel 365 dinamikus tömbfüggvényei (EGYEDI, SZŰRŐ, DARABTELI, LET, NAGY) elegáns és könnyen kezelhető megoldást kínálnak, a régebbi Excel verziók felhasználóinak segédoszlopokkal vagy bonyolultabb tömbképletekkel kell dolgozniuk. Bármelyik utat is választod, a kulcs az adatok megértése és a logikai lépések pontos végrehajtása.
Reméljük, ez a részletes útmutató segít neked abban, hogy hatékonyabban dolgozz az adatokkal az Excelben, és mélyebb betekintést nyerj azok tartalmába!