Valahol a VLOOKUP kistestvére, a LOOKUP lehet csak a megmentőnk

Mindezt teszi annak ellenére, hogy van egy kis problémája

2015. szeptember 08. - Office Guru

A következő, egy valós probléma kapcsán felmerült Excelt-kérdést igyekeztem igencsak általános formába önteni az egyszerűbb érthetőség kedvéért, de így is igencsak megdolgoztatja az ember agytekervényeit, hiszen bármennyire is egyszerűnek tűnik a kérdés, a válasz meglelése már okozhat egy kis fejtörést (bár ez a feladat talán Excel-guruknak nem okoz akkora kihívást, viszont mindenki másnak igencsak jó tanulási lehetőség). Szóval a példánk két táblázatát kellene összekapcsolni, méghozzá úgy, hogy a B ("Megoldás") oszlopba kerüljön be az A oszlopban lévő mondatokban szereplő színek alapján a színekhez tartozó szám az E oszlopból.

otvennegy.jpgMár a példa felvázolása is utal arra, hogy itt valamiféle VLOOKUP-jellegű megoldást kell keresni, és ebben nincs is nagy tévedés, hiszen a megoldást a (nem is annyira) nagyszerű LOOKUP rejti magában, amelynek használata nem meglepő módon eléggé visszaszorult, hiszen a VLOOKUP jelentősen egyszerűbb és könnyebb használatot tesz lehetővé.

A LOOKUP ugyanis az első paramétereként megadott értéket fogja keresni egy, a második paramétereként megadott tartományban, majd találat esetén a harmadik paramétereként tartományból adja ki az előbbi tartománnyal egy sorban/oszlopban szereplő értéket.

Azaz a =LOOKUP(5,A2:A5,D2:D5) megnézi, hogy A2:A5 tartományban hol van az ötös érték, majd az ezzel egy sorban lévő értéket dobja nekünk vissza D2:D5 tartományból.

Viszont itt meg kell állnunk egy kicsit, ugyanis a LOOKUP-nak van egy olyan tulajdonsága, amelyet én kifejezetten nem kedvelek és emiatt nem is annyira szeretem használni ezt a formulát, ez pedig az, hogy emelkedő sorrendben kell lenniük a második paraméterként megadott tartományban az értékeknek, hogy helyesen működjön, ugyanis a függvény logikája alapján feltételezi az emelkedő sorrendet.

Lásd a lenti példát, függvényünk az elvárt 1 helyett 3-at ad vissza, de ha jól megnézzük, pont azért, mert nem növekvő a sorrendünk. Ha emelkedőbe vágjuk a színek sorozatát, akkor máris azt kapjuk, amit elvárunk. Ugye milyen vicces?

otvenot.jpg

otvenhat.jpgNa de ugorjunk vissza a kiinduló problémához. A LOOKUP formulával tehát nagyjából megbarátkoztunk, de lehet, hogy sokaknak még nem áll össze, hogy ez pontosan hogy is fog segíteni nekünk, ami érthető is, ugyanis még van egy másik függvény is, amit majd a LOOKUP-ba kell ágyaznunk, ez pedig igencsak szorgos és hasznos kis barátunk, a SEARCH. Szerintem nem nagyon van Excelt rendszeresen használó ember, aki ezt ne ismerné, hiszen kis formulánk az első paramétereként megadott szót/szövegrészt fog egy, a második paramétereként megadott szövegben keresni és visszaadja, hogy hányadik karakternél kezdődik keresett szövegünk.

Például a =SEARCH("Autó",D2) az Autó szót fogja megkeresni a D2 cellában és megadja, hogy az adott szövegben hányadik karakternél kezdődik a szó.

Ezzel pedig már össze is állt a kiinduló kérdésünk megoldásához vezető út, hiszen elsőként meg kell keresnünk hányadik karakternél indulnak az aktuális színek az A oszlopban szereplő szövegekben, majd ezt egy LOOKUP-ba kell ágyaznunk. Igenám, de mit keressünk és hol? Mert az egyértelmű, hogy a LOOKUP harmadik paramétere az E2:E6 tartomány lesz, hiszen itt csücsülnek eredményeink. Hogy hol keresünk, az is nagyjából összeállhat, hiszen itt a SEARCH által létrehozott tartomány lesz a kulcs, azaz a színeket megkeressük a szövegrészekben/mondatokban, majd az így kapott karakterszámokból összeálló tartomány lesz második paraméterünk a LOOKUP-ban. Így már az is adja magát, hogy a keresett értékünk valami jó nagy szám legyen, azaz lefedje az összes lehetséges karakterszámot - ergó, mivel egy cellába emlékeim szerint 32767 karakter fér, adjuk meg ezt a számot (de jelen esetben a leghosszabb mondatunk hossza is megteheti).

Így a megoldásunk:

=LOOKUP(32767,SEARCH(D$2:D$6,A2),E$2:E$6)

otvenhet.jpg

A bejegyzés trackback címe:

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

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.

A kulcslyuk szelleme 2015.09.09. 10:39:53

Kipróbáltam, működik, de nekem nem állt össze, hogy miért.:-(

1. Most akkor mi van sorbarendezéssel a LOOKUP függvénynél? Miért működik a végső megoldás, annak ellenére, hogy a színek oszlop nincs sorrendbe rakva?

2. Azt sem értem, hogy miért 32767 a keresett érték a LOOKUP-ban.

Atzs · https://adatrendezo.hu 2015.09.10. 00:26:43

LOOKUP=KERES, SEARCH=SZÖVEG.KERES

A SZÖVEG.KERES ebben a formában nem egy értéket, hanem egy tömböt ad vissza! (Ha egyszerűen beteszed egy cellába, akkor a tömbnek csak a legelső elemét fogod látni.) Jelen esetben a SZÖVEG.KERES(D$2:D$6,A2) egy négy elemű tömböt ad vissza: Az A2-ben hányadik pozícióban találta meg a D2-t, hányadikban a D3-at, a D4-et majd a D5-öt. Ennek kellene rendezettnek lennie, hiszen ez a KERES második paramétere.
Valójában nem rendezett. Azt használja ki a poszter, hogy a KERES függvény a hibaüzeneteket nem veszi figyelembe. Az első olyan helyen áll meg, ahol valamilyen használható adatot, esetünkben egy számot talál.
A mintapéldája annyiban csalós, hogy minden mondatban van pontosan egy keresett szín. Ha egy sem lenne, hibaüzenetet kapnánk. Ha egynél több, akkor viszont számít a színek sorrendje. Azt adja meg, amelyik leghátrébb van a listában.

A 32767 egy varázsszám, a legnagyobb két bájtos egész. ;-) Poszter is írta, hogy az a lényeg, hogy nagyobb legyen, mint a leghosszabb mondatnak a hossza. Ez pedig a keres függvény miatt van.
Vegyünk egy pelenkaméret táblázatot, hogy hány kilótól jó:
3 S
5 M
7 L
9 XL
A két oszlop a KERES függvény második és harmadik paramétere. Ha az első paraméter mondjuk 5, akkor azt megtalálja az első oszlopban, és a másodikból visszaadja a mellette álló értéket, azaz az M-et.
Ha az első paraméter 7.42, akkor halad előre, majd a 9-nél rá fog jönni, hogy már túlment. Visszalép a 7-esre, majd megadja a mellette levő értéket, az L-t.
Ha az első paraméter 32767, akkor az tuti nagyobb minden értéknél, ami a listában van, tehát a 9-re fog visszamenni, amikor rájött, hogy túlszaladt, és az XL-t adja eredményül. Ha történetesen a 9 helyén hibaüzenet lenne, akkor visszacsúszna a 7-esig.

Ha nincs sorba rakva, pl így:
3 S
7 L
5 M
9 XL
akkor 3.4-et keresve minden rendben lenne, de 5.5-öt keresve visszalépne a 3-ra, amint eléri a 7-et, mert azt hiszi, hogy túlfutott. (Feltéve ha lineárisan keres. Logaritmikus keresésnél is hibázik, csak egy kicsit másképp.)

A kulcslyuk szelleme 2015.09.10. 20:55:30

@Atzs: Köszi a magyarázatot! Így már jóval érthetőbb a dolog.

Office Guru 2015.09.12. 09:11:32

@Atzs: Köszi, mire ide jutottam, láttam, hogy már dobtál is egy tök jó magyarázatot. A mintapéldám tényleg beugratós, hiszen mindegyikben van egy szín és pontosan egy szín - de arra gondoltam, hogy azt már könnyebben meg lehet oldani, mint az első lépcsőfokot megugrani.

Atzs · https://adatrendezo.hu 2015.09.12. 09:34:12

@Office Guru: Szívesen. Érdekes dolgokat írsz, csak így tovább. Látom, beindul lassan a pezsgés is. :-)