Telefonszámunk: 1-472-0679

Keresés több feltétel szerint – FKERES és társai

2015-07-12 - horvimi - Kategória: Általános tippek, Függvények, Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013

A keresőfüggvények első argumentuma a keresendő érték szokott lenni. Ez egy darab konkrét érték, amit a következő argumentumban megadott tartományban fog keresni.

Ha a keresendő érték többször is szerepel a megadott tartományban, akkor csak az első előfordulását fogja találatként kezelni. Emiatt csak olyan oszlopban van értelme általában a keresésnek, ahol egyedi értékek vannak, azaz nincs benne ismétlődés. Ezeket az oszlopkat kulcs oszlopnak vagy azonosító (ID) oszlopnak nevezzük. Így minden sor megkülönböztethető a többitől.

Ha egy érték további (második, harmadik, n-edik…) előfordulását szeretnéd keresni, nézd meg ezt a korábbi cikket!

Probléma – Nincs kulcs oszlop

Gyakran vannak olyan táblák, amik nem tartalmaznak kulcs oszlopot. Nézzük például ezt a példát:

kereses-tobb-feltetel-alaptabla

kereses-tobb-feltetel-kerdes

Itt egy terméket a neve alapján nem lehet egyértelműen azonosítani. Ha azonban hozzávesszük a méretet is, akkor azt tapasztalhatjuk, hogy a kettő alapján már nincsenek ismétlődések.

Megoldás – Készítsünk kulcs oszlopot

Egy ilyen esetben valahogy gyártanunk kell egy egyedi azonosító oszlopot. Ezt úgy tesszük, hogy addig fűzzük egymás után az egyes tulajdonság oszlopokat, amíg az összefűzött eredmény alapján egyértelműen meg nem tudjuk különböztetni a sorokat.

A példánkban a termék neve és a mérete együtt már egyértelmű azonosító lehet. Az összefűzést a megfelelő függvénnyel (ÖSSZEFŰZ/CONCATENATE) vagy  a „&” művelet jellel végezzük.

kereses-tobb-feltetel-osszefuzott-oszlop

Ez természetesen egy nagyon leegyszerűsített példa, hiszen lehetne még egyéb tulajdonsága a termékeknek( pl. szín vagy márka, stb…), amiket szintén bele lehetne venni az azonosításba.

A kulcs oszlop legyártása után érdemes ellenőrizni, hogy valóban nem tartalmaz ismétlődéseket. Ezt pl. az ismétlődések eltávolítása paranccsal tehetitek meg. Ha azt mondja, hogy nem tartalmaz ismétlődést, akkor nyertünk!

A kulcs oszlop helye

A kulcs oszlopot tehetjük bárhová, de leggyakrabban az elejére vagy a végére szokták tenni.

Az elejére akkor tegyük, ha a keresést az FKERES/VLOOKUP függvénnyel tervezzük, mivel ez a függvény csak az első oszlopban tud keresni, és jobbra lévő oszlopból ad eredményt.

Ha a végére tesszük, akkor csak az INDEX/HOL.VAN megoldás jöhet számításba. Erről bővebben itt olvashatsz.

A kulcs oszlop természetesen elrejthető.

FKERES / VLOOKUP megoldás

A keresési érték a két tulajdonság összefűzése, a keresési tábla pedig a kulcs oszloppal kiegészített alaptábla. Ezt táblázattá alakítottam, és elneveztem „raktar_1” néven. Táblázatokról bővebben itt.

kereses-tobb-feltetel-FKERES

INDEX/HOL.VAN megoldás

Itt a végére tettem a kulcs oszlopot, és „raktar_2” néven alakítottam táblázattá. Ennek oszlopaiban dolgozik a képlet.

kereses-tobb-feltetel-INDEX

Lehetne segédoszlop nélkül is?

Hát, ez jó kérdés. Segédoszlopra, azaz kulcs oszlopra mindenképpen szükség van, de nem feltétlenül kell megjelennie a munkalapon. Ekkor azonban tömbképletet kell alkalmazni, amit beágyazunk az eredeti képletbe.

A letölthető munkafüzetben és a videóban benne van ez a megoldás is.

Letölthető munkafüzetek

Kereses-tobb-feltetel-nyers
Kereses-tobb-feltetel-kesz

Mindez működés közben

  • Keresés több feltétel szerint

2 hozzászólás
  1. Tisztelt Excel-bázis!

    Már jó ideje követem az oldalt, melyet nagyon klassznak találok, azonban ezen cikk kapcsán megjegyezném, hogy igenis lehet segédoszlop nélkül megoldani a feladatot, igaz ez is tömbképletes megoldás.

    A nyers munkafüzet „Adatok” munkalapján, a G13-as cellába az alábbi képlet kerül:

    =INDEX($D:$D;HOL.VAN($F13&G$12;$B:$B&$C:$C;0)) és ezt a képletet automatikus kitöltöm a többi sorba, így a megfelelő eredmény kerül mindenhova.

    Üdv, Attila

  2. Kedves Attila!

    Köszönöm a hozzászólást és az elismerést
    !
    A cikkben én is említem, hogy tömbképlettel megoldható segédoszlop nélkül is, és a videóban is van erre megoldás, igaz elég speciális, mert számított névtartományt használok.

    Szándékosan írok néha egyszerűbb megoldásokat (is), hogy a lehető legtöbben értsék az alkalmazott megoldásokat.

    Performancia tekintetében pedig tény, hogy a tömbképéletes megoldás sok adat esetén jelentősen lassabb.

    A képlet, amit megadtál, jó, csak mindenki kedvéért tegyük hozzá, hogy Ctrl+Shift+Enter-rel kell lezárni.

    Imre

Vélemény, hozzászólás?