Úgy tűnik, valahogy én sem tudok kibújni az Office-alkalmazások megszokott köréből, azaz a legtöbb kérdés és probléma a leginkább használt alkalmazással, azaz az Excellel kapcsolatban jut el hozzám a mindennapok során, így mai posztom is egy olyan táblázatos ügyet jár körbe, amellyel tegnap talált meg egy kedves sorstárs és amely egy számára igencsak fontos kimutatás elkészítéséhez volt igencsak gyorsan orvosolandó kérdés.
Adott volt két igen hosszú listája Excelben és szerette volna automatikusan és minél egyszerűbben meghatározni, hogy melyek azok a tételek, amelyek egyediek a két listában, azaz mindkét felsorolásból ki kellett emelni az azonos szöveget tartalmazó elemeket. Hogy érthető legyen, csináltam egy kis mintapéldát.
Gondoljuk csak át, hogy mi hogyan csinálnánk meg - a lehetőségek pedig elég széleskörűek, elvileg jópár megoldási módszer létezik egy ilyen kérdés megválaszolására. Az első megoldási javaslat, amit a legtöbben rávágnának a fenti problémára, az a VLOOKUP és nem is tévednek, nem olyan szép megoldás, de hatékony.
Egyszerűen fogjuk az egyik listánk első elemét, majd a két listából együtt létrehozott tartomány bal első oszlopában keressük és ha megtalálja a keresett értéket, akkor írja ki önmagát, azaz a tartomány harmadik oszlopát - értelemszerűen csak teljes egyezést fogadunk el. Így nézne ki ez a példánkon:
=VLOOKUP(F28,D28:F32,3,FALSE)
F28 amit keresünk, D28:F32 a tartomány, amelynek első oszlopában keressük az F28-at és ha megtalálja, akkor a D28:F32 tartomány harmadik oszlopát, azaz az első listában található érték párját fogja kiírni. És ez az eredmény:
De mi van ha kicsit szebben szeretnénk ezt megoldani? Forduljunk az Excel egyik nagy kincséhez, a feltételes formázáshoz (Conditional Formatting), amit a Home fül Styles szekciójában érhetünk el. Mielőtt azonban beleugranánk az új szabály/rule létrehozásába, álljunk csak meg egy pillanatra. Komplex táblázatok és sokfüggvényes, feltételes formázásokat is tartalmazó Excel esetén igencsak javasolt tartományaink elnevezése, hiszen így jóval könnyebb a későbbi hivatkozás, nem kell újra meg újra kijelölgetnünk, vagy cellák sorszámát megjegyezni, elég ha tudjuk, hogy mondjuk az Árlista elnevezésű tartománnyal mit fogunk meghivatkozni.
Tartományt elnevezni a Formulas fül Defined Names szekciójában tudunk - jelöljük ki a tartományunkat, a mi esetünkben az első listát, majd egyszerűen csak kattintsunk a Define Name gombra, nevezzük el tartományunkat és kész is. Így nálam most létrejött List1 az első oszlopra és List2 a másodikra, innentől kezdve teljesen mindegy mennyit dolgozom még a táblázatomban, ezzel a két névvel bármely függvényben hivatkozhatok rájuk.
Szóval most már visszatérhetünk a feltételes formázásunkhoz, amelyet kezdjünk azzal, hogy kijelöljük azt a tartományt/listát, amelyben ezt érvényesíteni akarjuk, azaz mondjuk azt szeretnénk, hogy az első oszlopunkban piros színt kapjanak azok az elemek, amelyek nincsenek benne a második oszlopban. Miután kijelöltük a tartományunkat, kattintsunk a Conditional Formatting gombra majd az alatta megjelenő menüsorban a New Rule menüre.
Jelen esetben most a legalsó lehetőséget kell választanunk, hiszen az engedi meg nekünk, hogy függvény alkalmazásával döntsük el mely cellánkat fogjuk pirosra színezni. Miután ezt kiválasztottuk, a Format values... elnevezésű mezőt kell kitöltenünk egy olyan függvénnyel, amely első listánk értékeit a másodikhoz tudja és fogja is hasonlítani.
Itt jön képbe a COUNTIF függvény, amely roppant egyszerű módon, két paraméter segítségével megszámolja nekünk, hogy az első paraméterként megadott tartományban hány darab olyan cella van, amelynek értéke megfelel a második paraméterként megadott kritériumnak.
Azaz most a következő paraméterezés fog segíteni nekünk:
=COUNTIF(List2,D10)=0
Azaz a List2-ként korábban elnevezett tartományunkban fogja megnézni, hogy van-e a D10-es cella értékével (majd értelemszerűen a tartomány további elemeivel) megegyező érték és pontos egyezés (ezért a nulla) azt szépen a FORMAT rész alatt megadott színre fogja színezni.
Szívemhez azonban a következő megoldás áll a legközelebb, hiszen ez is tisztán függvényes megoldás, ám a VLOOKUP-hoz képest jóval hatékonyabb és tisztább megoldást varázsol elénk.
Három függvényt fogunk összefűzni, hogy elérjük a várt eredményünket, amelyek közül a leginkább fontos most a MATCH, amely az első paramétereként megadott érték helyzetét fogja a második paramétereként megadott tartományból megadni nekünk (a harmadik paraméter a pontos egyezés, kisebb-nagyobb feltétel megadására ad lehetőséget), így értelemszerűen a
=MATCH(D20,F19:F23,0)
meg fogja mondani, hogy a D20-as cella értéke az F19:F23 tartományban hol található pontos egyezés esetén. Ergó jelenlegi példánkra felépítve, ha az egyik lista értékét keressük a másik listában, akkor vagy egy számot (az érték helyzetét) vagy egy #N/A értéket fogunk kapni. Az #N/A miatt jön képbe a második függvény, amit használni fogunk, ez pedig az ISERROR, ami egyetlen paraméteres kis függvény és azt mondja meg, hogy az általunk paraméterként beadott cella értéke #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? vagy #NULL! érték-e, mert ha igen, akkor TRUE értéket dob, ellenkező esetben FALSE-t.
Folytatva az előző felépítést, tehát az
=ISERROR(MATCH(D20,F19:F23,0)) TRUE-t fog dobni, ha a D20-as cella értéke benne van az F19:F23-as tartományban, FALSE-t ha nincs.
Innentől pedig már csak egy IF-re van szükségünk, ami az előző függvénykombinációnk eredménye (TRUE vagy FALSE) alapján kiad egy üzenetet vagy magát a D20-as cellát, attól függően mit szeretnénk:
=IF(ISERROR(MATCH(D19,$F$19:$F$23,0)),"",D19)
Azaz ha ezt lehúzzuk, megkapjuk azokat az értékeket, amelyek benne vannak második listánkban, amelyek nincsenek azok üres mezőt hajítanak vissza.
És ezzel még csak három megoldásról írtam, a sokak által leghatékonyabbnak tartott VBA-t nem is említettem, de nem kevertem bele az Excel összehasonlítós lehetőségét sem - ezekre majd később kerítek sort más problémák kapcsán.
