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