Ahány Excel-kérdés, annyi értelmezés és annyi megoldás - íme egy tökéletes példa megint

Duplikáció számít? Szövegre is nézzük? Egyező méretű táblák? És kérdések tömege így tovább

2015. szeptember 20. - Office Guru

A következő Excel-probléma már önmagában kérdéseket vet fel az értelmezését tekintve, de én amondó vagyok, hogy ha egyfajta megoldás alaplogikájával tisztában van valaki, akkor onnantól kezdve már rá tudja húzni bármelyik másik értelmezésre is a saját megoldását (amiből persze Excel lévén létezik jópár különböző).

A kérdés tehát roppant egyszerű, mondjuk meg, hogy két oszlopunkban/listánkban hány darab egyező érték van (de lehet több lista is), akár ki is színezhetjük Conditional Formattinggal az érintett cellákat, de a lényeg nekünk most csak egy darabszám. Itt lennének az értelmezésbeli módosulások, hiszen gondolhatunk szöveget tartalmazó cellákra, gondolhatunk arra, hogy a két listánk nem megegyező méretű, egyáltalán ha az egyik listában egy érték sokszor szerepel akkor azt egy egyezésnek vagy többnek számoljuk-e és így tovább - a mostani példamegoldás szövegre, nem azonos méretű listák esetén is működik és nem feltételez duplikációkat.

Ahogy látszik a példaképen, az első listában és a második listában 2 közös értékünk van, egy PAKS és egy VIDEOTON érték (nincs semmi logika a csapatok felsorolásában, annyira nem vagyok oda a fociért), mi lenne a legegyszerűbb módszer arra, hogy ezt a számot visszakapjuk formulával?

kilencven.jpgMivel tömbökkel dolgozunk, előzetesen sanszos, hogy tömbképletre lesz majd szükségünk, ahogy az is biztos, hogy valahol be kell majd építenünk egy IF-et legalább (itt akár lehet COUNTIF vagy SUMIF is a képben, ez attól függ, milyen logika mellett indultunk el).

Elsőként fogjunk egy MATCH függvényt, ami az első paramétereként megadott értéke/tartományt keresi a második paramétereként megadott tartományban - és kérjünk pontos egyezést harmadik paraméterként, valahogy így:

=MATCH(D3:D15,E3:E15,0)

Ennek értelmezése szerintem senkinek nem okozhat problémát, úgyhogy száguldjunk is tovább egy olyan függvénnyel, amelyről itt még nem esett szó, ez pedig az ISNA, ami szimplán annyit tesz, amit a neve is jelent, azaz megvizsgálja, hogy egy cellában/tartományban szereplő érték N/A-e és ennek alapján ad TRUE Vagy FALSE értéket vissza. Ezt futtassuk rá az előbbi MATCH kombinációnkra:

=ISNA(MATCH(D3:D15,E3:E15,0))

Mit is csinál ez? Megnézi, hogy a MATCH által létrehozott keresésnél van-e valahol N/A érték és ez alapján TRUE és FALSE értékeket fog visszadobálni. De miért is volt erre szükség? Azért, mert ezeket a TRUE és FALSE értékeket át tudjuk alakítani 1 vagy 0 értékekre, ami aztán könnyen szummázható és meg is kapjuk szépen az eredményünket. De azért ne rohanjunk előre ennyire, hiszen most kell akkor egy IF függvény, hogy átalakítsuk az ISNA formula TRUE-FALSE válaszait 1-re és 0-ra:

=IF(ISNA(MATCH(D3:D15,E3:E15,0)),0,1)

Ezután pedig ott a tömbünk, tele egyesekkel és nullákkal, egyszerűen adjuk össze tömbképletként (CTRL+SHIFT+ENTER) és that's all!

=SUM(IF(ISNA(MATCH(D3:D15,E3:E15,0)),0,1))

Példánkon ez pedig így néz majd ki:

kilencvenegy.jpg
De ez a probléma tipikusan megint az, amit iszonyú sokféleképpen meg lehet oldani, az én fejemben a fentin kívül még ott van egy Conditional Formattingos megoldás, egy segédcellás megoldás, de például bevethetünk egy COUNTIF-SUMPRODUCT tömbképletet is, szóval hajrá-hajrá, jöhetnek az egyedi megoldások, ötletek, kritikák!

A végére pedig jöjjön egy hasznos tipp, ami azoknak lehet segítség, akik nem angol nyelven használnak Excelt, mégis szeretnék egyeztetni megoldásukat az angollal. Van elég sok weboldal, ahol megtalálhatjuk az angol függvénynevek magyar megfelelőjét, de egy gyors és hatékony, plusz VBA-barát megoldás az ?activecell.Formula parancs, amit úgy tudunk legegyszerűbben lefuttatni, hogy ráállunk a függvényt tartalmazó cellánkra, ALT+F11-el előhívjuk a VBA Editort, majd CTRL+G-vel az Immediate Windowt, majd ott bemásolva a parancsot ENTERT nyomunk és íme, láss csodát!

kilencvenketto.jpg

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr817802474

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

falazodesyka 2015.09.21. 18:40:06

Fasza, de én az ilyeneket VBA-ban oldom meg programmal. Nem kell rajta annyit gondolkodni mert két ciklust egymásba ágyazol és kész. Időben sem több szerintem. De mondom, azért fasza az ötlet.

falazodesyka 2015.09.21. 18:43:12

Azért fasza mert segédcella nélkül csinálja.

Kris* · www.adatkerteszet.hu 2015.09.21. 18:45:12

=SUMPRODUCT(--ISNUMBER(MATCH(D3:D15;E3:E15;0))) --> Sima Enter.
Nem kell tömbképletet alkalmazni, ha nem muszáj.
Meg VBA-t se. :-)

Üdv:
Kris
www.adatkerteszet.hu/

falazodesyka 2015.09.21. 20:18:31

@Kris_: még faszább! Bírom hogy rajtam kívül is léteznek excel megszállottak

Office Guru 2015.09.21. 21:34:36

@Kris_: Köszi...jó látni, hogy tényleg ahány Excel-rajongó, annyi megoldás:)

Axelock 2015.09.21. 21:36:57

...annál már csak az bosszantóbb, ha magyar Excel-ünk van, de az interneten egy nagyon jó trükköt csk angol Excel formulával találunk meg... ;) (Vagyis a legtöbb esetben...)

Ilyenkor hasznos ez - a fenti megoldás "visszafelé":
ActiveCell.Formula="=SUM(IF(ISNA(MATCH(D3:D15,E3:E15,0)),0,1))"

A cellában már meg is lelent a magyar változat:
=SZUM(HA(NINCS(HOL.VAN(D3:D15;E3:E15;0));0;1))

Amit persze közvetlenül is tudunk kezelni a VBA ablakban is, a FormulaLocal tulajdonsággal:
?ActiveCell.FormulaLocal
=SZUM(HA(NINCS(HOL.VAN(D3:D15;E3:E15;0));0;1))

Üdv:
Gábor
www.adatkerteszet.hu

Kris* · www.adatkerteszet.hu 2015.09.21. 21:44:05

@Axelock: Igen, nyelvi eltérés esetén hasznos a "visszafelé" trükk VBA-ban, az idézett példában azonban az array formula miatt .FormulaArray tulajdonságot kell használni.

Formula fordításhoz az alábbi lehet még segítség:
hu.excel-translator.de/translator/

Üdv:
Kris
www.adatkerteszet.hu

Axelock 2015.09.21. 21:55:18

@Kris_: :D Na jól van, de akkor említsük meg azt is, hogy 255 karakternél hosszabb formulát nem lehet VBA-n keresztül a cellába írni.

Üdv:
Gábor
www.adatkerteszet.hu