Amiért szeretjük az Excelt - egyetlen probléma, legalább ötféle megoldás

A táblázatkezelős munkával igencsak fejleszthetjük kreativitásunkat

2015. augusztus 27. - Office Guru

Ú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.

huszonharom.jpgGondoljuk 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:

huszonnegy.jpgDe 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.

huszonot.jpgSzó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.

conditional_formatting.jpg

huszonhat.jpgSzí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.

huszonhet.jpgÉ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.

A bejegyzés trackback címe:

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

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.

Atzs · https://adatrendezo.hu 2015.08.27. 23:11:48

Az első megoldás helyesen: =FKERES(F28;D$28:F$32;1;HAMIS)
Egyrészt kimaradtak a dollárjelek, így ha lehúztuk a képletet, akkor a D28:F32-ből D29:F33, D30:F34 stb. lett.
Másrészt nem a keresett értéket adta vissza, hanem szépen megkereste az első listában, majd a második listából a mellette álló elemet írta ki.

Második megoldás: Magyar verzióban DARABTELI a függvény neve,

Harmadik megoldás: Magyar verzióban =HA(HIBÁS(HOL.VAN(D19;$F$19:$F$23;0));"";D19)

Bónusz megoldás: =HAHIBA(INDEX($F$19:$F$23;HOL.VAN(D19;$F$19:$F$23;0));"") :-)

gigabursch 2015.10.01. 11:53:16

Az excell tud számot szöveggé alakítani?
(Office 2013)

Atzs · https://adatrendezo.hu 2015.10.01. 12:15:49

=szöveg(érték;formátum_szöveg)

gigabursch 2015.10.07. 07:45:41

@Atzs:
Köszönöm, de ezzel nem lettem előrébb.
Vagy csak béna vagyok.

Igazában azt, amit itt már megoldott valaki (www.adatkerteszet.hu/2014/08/osszeg-betuvel-szamok-atirasa-szovegge-keplettel/), erre keresek belső függvényt.
(ez egyébként jó, mert ha mást nem, rengeteg szám esetében irányított beillesztés/értéket megoldással megvan a megoldás, de ha van belső függvény, az lenne a tuti.)

Tehát, hogy a 12618 => tizenkettőezer-hatszáztizennyolc legyen.

Atzs · https://adatrendezo.hu 2015.10.07. 08:04:13

@gigabursch: Szia! Nem így értettem a kérdést, így persze a válasz nem lett hasznos.
Az adatkertész megoldás ügyes. Az alapján már lehet VBA-ban írni egy saját függvényt. Ha érdekel ez a megoldás, akkor szólj, és leírom, mit kell tenni hozzá.
Ha az így megokosított fájlt át akarod adni másnak úgy, hogy nála is működjön, az minimálisan elbonyolítja a dolgot.

gigabursch 2015.10.11. 18:23:24

@Atzs:
Köszönöm, de jobb híján elboldogulok így is...
Mindenesetre köszi.

gigabursch 2015.10.13. 09:43:19

Akkor kérdeznék még egyet:
Dátumból hogyan csinálok szám értéket?
Ha csak a cellát átállítom számra, olyan érték jön ki, ami nem értelmezhető.

Atzs · https://adatrendezo.hu 2015.10.13. 12:21:24

@gigabursch: Az az 1900 január elseje óta eltelt napok száma. Pontosítsd kérlek egy példa segítségével, hogy milyen számértéket szeretnél kapni pl. 2015.03.15-ből.

gigabursch 2015.10.13. 13:30:53

A gondom az az, hogy pl egy hrsz beírásánál (09 vagy mondjuk 012/3) az excell reflexből csinál egy dátumot.

A probléma persze ott indul, hogy valakik csinálnak egy adatsort, amit tabulátorral tagolt szövegként (pl csv) beimportálnak ész és értelem nélkül excell alá, majd kapok egy adatsort, ahol vannak kiütköző értékek, mert dátumosak.
(Persze, ha előtte a mezőt lekódolták volna szövegnek, akkor mindez nem lenne gond, de erre általában ezek az emberek képtelenek.)

Szóval az ilyen jellegű dolgok rehabilitálásrára van-e esetleg esély, abból kiindulva, hogy a hónap és nap valamilyen algoritmus alapján lett az ami, és visszafordítíható-e irányítottan ez a folyamat?

A nincs megoldás is megoldás, de ennél jobb a van megoldás...
:-)

Atzs · https://adatrendezo.hu 2015.10.13. 19:09:31

@gigabursch: Ehhez azt kell látni, hogy mit mivé alakíthatott át az Excel. Maradjunk a 12/03-nál. Ebből lesz az én nyelvi beállításaimmal 2015.12.03. Tegyük fel, hogy vannak olyan sorok, ahol nem alakította át, ott ilyeneket látok: 15/07, 02/48, stb. Ezzel megvan az eredeti formátum. Ezt kell visszakapnom valahogyan. Két karakter felvezető nullával, perjel, két karakter felvezető nullával. A függvényem a következő lesz:
SZÖVEG(HÓNAP(A1);"00")&"/"&SZÖVEG(NAP(A1);"00")

Az ÉV, HÓNAP, NAP függvények kiszedik egy dátumból számként a megfelelő részt. A SZÖVEG függvény segítségével be tudom tenni a megfelelő számú felvezető nullát. Ha a formátum szöveg "0000", akkor a négyjegyűnél kisebb számok elé betesz annyi nullát, hogy összesen négy karakteres legyen az eredmény. Az & operátor pedig különböző szövegeket összeragaszt.

Ha az eredeti tartalomra jellemző bármilyen egységes formátum, akkor egy ehhez hasonló képlettel vissza tudod alakítani. Ha az eredeti lehetett 12/3 és 12/03 is, akkor a kettő közül nem fogod tudni megmondani, melyiket alakította át a masina.