FKERES függvény több feltétellel

Biztosan találkoztál már olyan esettel, amikor FKERES függvényt (angolul VLOOKUP függvény) szerettél volna használni, de nem volt egyértelmű a keresési érték, vagy nem így szerepelt az adathalmazban. Ebben a bejegyzésben megmutatom, hogy használható az FKERES függvény több feltétellel. Az FKERES függvényről az FKERES függvény (VLOOKUP) típusai című blogposztban olvashatsz bővebben.

Egy gyors segítség lehet egy segédoszlop(ok) létrehozása, amiben azonos/kereshető formára lehet hozni a szükséges értékeket.

A leírásban használt fájlt letöltheted innen: FKERES függvény több feltétellel – Feladat

Látható az alábbi egyszerű példából, hogy az FKERES függvénnyel nem tudunk rákeresni a névre, hiszen az adatok között a név két oszlopban szerepel.

Egy segédoszlopban már át tudunk alakítani olyan formátumra, amire már keresni is tudsz. A segédoszlopban használhatod az ÖSSZEFŰZ függvényt (angolul CONCATENATE), amivel egy cellába össze tudod fűzni a teljes nevet. Az összefűz függvényről részletesen az Összefűzés Excelben – Több lehetőség című bejegyzésünkben olvashatsz. Ezután a FKERES függvénnyel már könnyen meg tudod keresni az adott személy teljesítését.

A függvény: =FKERES(F5;$C$4:$D$7;2;HAMIS)

Biztosan találkoztál már olyan esettel is, amikor egy új oszlop beszúrása után már az FKERES függvény nem a jó eredményt hozta. A HOL.VAN függvénnyel (angolul MATCH) meg tudod keresni az adott oszlop számát. A HOL.VAN függvényről a HOL.VAN függvény bevezető példával  című bejegyzésben tájékozódhatsz.

A függvény: =FKERES(I5;C4:D7;HOL.VAN(D4;C4:D4;0);HAMIS)

Egy másik megoldásként szóba jöhet a tömbképlet. A tömbképlet (részletek: Excel tömbfüggvények) hátránya a segédoszlop megoldásával szemben a következők:

• Nehezebben értelmezhető
• Nagy adattáblákban érezhetően lassabban számol

Ebben az esetben használhatsz tömbképletet.

Látható a szerkesztőlécen, hogy a tömbképlet miatt „{” kezdődik a képlet, de ezt nem kell odaírni, hiszen, ha CSE, azaz Control Shift Enter együttes lenyomásával fejezed be a képlet szerkesztését, akkor az Excel automatikusan kiegészíti a képletet ezzel a karakterrel, illetve a végére is tesz egy „}” karaktert. Office 365 esetén nincs szükség a CSE használatára, hiszen minden olyan új képlet, amely több mint 1 találatot ad vissza, automatikusan átkerül tömbképleti feldolgozásra.

Az Office 365 webes és asztali verziói eltérést mutathatnak, nem biztos, hogy az itt bemutatott példák minden esetben működőképesek Office 365-ös rendszerben.

A képlet:
{=FKERES(G5;VÁLASZT({1\2};ÖSSZEFŰZ($A$5:$A$7;” „;$B$5:$B$7);$C$5:$C$7);2;HAMIS)}

Az FKERES függvény argumentumai: (A dollárjelek használatára ügyelj!)

1. keresett szöveg (G5-ös cella)
2. VÁLASZT (angolul CHOOSE) függvény készít egy tömböt, azaz két (név összefűzés egynek számít) tartomány adataiból tömböt hoz létre, lejjebb részletesebb leírást találsz
3. a létrehozott tömb oszlopindexe
4. HAMIS vagy 0

Ne felejtsd el, hogy CSE együttes lenyomásával kell befejezni a szerkesztést! Ezután a szokott módon másold végig a kívánt cellákba. Ha a dollárjeleket megfelelően használtad, akkor a többi cellába is a megfelelő érték fog kerülni.

A VÁLASZT függvény argumentumai: VÁLASZT({1\2};ÖSSZEFŰZ($A$5:$A$7;” „;$B$5:$B$7);$C$5:$C$7)

1. a „kapcsos” zárójelekbe meg kell adni az 1 és a 2, ezek lesznek az új tömb indexei. Angol verzióban pontosvesszőt „;” kell használni
2. a tömb első adatoszlopának adatait tartományait fűzd össze, szóköz karakterrel elválasztva
3. a tömb második adatoszlopának adatainak tartománya

A létrehozott tömb értékeit a következőképpen tudod leellenőrizni. A szerkesztőlécben jelöld ki a választ függvényt és argumentumait!

A kijelölés után nyomd meg az F9 billentyűt, és megmutatja a tömb tartalmát!

Van olyan eset, amikor még több feltétel alapján kell számolni.

A következő példában már tantárgyak is szerepelnek a táblázatban.

Alapadatok

Látható az alábbi a példából, hogy a sima FKERES függvénnyel nem tudsz rákeresni a névre sem, de több feltételt sem tudsz megadni.

Tömbképlet

Ebben az esetben a keresési értéknek meg kell adnod a tantárgyat is, és a VÁLASZT függvényben is hozzá kell fűznöd.

A képlet: {=FKERES ($G12&”|”&H$11;VÁLASZT({1\2};ÖSSZEFŰZ(ÖSSZEFŰZ($A$12:$A$20;” „;$B$12:$B$20);”|”;$C$12:$C$20);$D$12:$D$20);2;HAMIS)}

Az előző példa alapján, ebben az esetben is fűzd össze a neveket szóközzel elválasztva, és fűzd hozzá a tantárgyat egy speciális karakterrel „|” (Alt Gr w). A lényeg, hogy ugyanaz az elválasztó legyen a keresési érték és a tömb feltöltésekor is.

Az FKERES függvény argumentumai: (A dollárjelek használatára ügyelj!)

1.két szöveg összevonása (G12-es cella, |, H11)

2. VÁLASZT függvény készíti a tömböt

3. a létrehozott tömb oszlopindexe

4. HAMIS vagy 0

Ne felejtsd el, hogy CSE együttes lenyomásával kell befejezni a szerkesztést! Ezután a szokott módon másold végig a kívánt cellákba. Ha a dollárjeleket megfelelően használtad, akkor a többi cellába is a megfelelő érték fog kerülni.

Mutatok arra is példát, amikor még egy feltétellel több van.

Az utolsó példában a teszt megnevezése is szerepel a tantárgyak mellett a táblázatban.

Alapadatok

Tömbképlet

A képlet: {=FKERES($G26&”|”&H$25&”|”&H$24;VÁLASZT({1\2};ÖSSZEFŰZ(ÖSSZEFŰZ($A$25:$A$34;” „;$B$25:$B$34);”|”;$C$25:$C$34;”|”;$D$25:$D$34);$E$25:$E$34);2;HAMIS)}

Ebben az esetben a tesztet is hozzá kellett fűzni a keresési értékhez, illetve a tömbhöz. A metodika ugyanaz, mint az előzőekben.

A végeredményből látható, hogy ahol nem talál adatot a tömbképlet, ott a „#HIÁNYZIK” értéket adja vissza. Ezt célszerű egy HAHIÁNYZIK (angolul IFNA) függvénnyel (Excel 2013+) kiíratni valami más szöveget, például azt, hogy „Nincs”.

A képletet a következőre módosíthatod:

{=HAHIÁNYZIK(FKERES($G26&”|”&H$25&”|”&H$24;VÁLASZT({1\2};ÖSSZEFŰZ(ÖSSZEFŰZ($A$25:$A$34;” „;$B$25:$B$34);”|”;$C$25:$C$34;”|”;$D$25:$D$34);$E$25:$E$34);2;HAMIS);”Nincs”)}

Remélem nem vettem el a kedved, sőt remélem, hogy rávilágítottam arra, hogy milyen csodálatos függvény ez az FKERES, főleg így tömbképletként használva!

A poszthoz felhasználtam Sumit Bansal bejegyzését , illetve Jeff Lenning posztját.

Bízom benne, hogy sikerült megcsinálnod ezt a példát. Ha tetszett, kérlek oszd meg másokkal is!