Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016 Office365
A napokban egy céges tréningen az FKERES (VLOOKUP) függvényről beszéltünk. Éppen arról volt szó, hogy ha a függvény utolsó argumentumát nem adjuk meg, vagy 1-est (TRUE,IGAZ, stb..) adunk, és nem talál pontos egyezőséget, akkor a keresett értékhez legközelebbi kisebb értéket adja vissza a keresési listából. Ehhez ráadásul a keresési listának rendezettnek kell lennie.
Egyszer csak az egyik résztvevő megkérdezte, hogy mi van akkor, ha valóban a legközelebbi értéket szeretnénk visszakapni? Tehát ha a keresési listában a következő, de már nagyobb elem közelebb van a keresett értékhez, akkor az legyen a találat. A következő ábrán egy rendezett és egy rendezetlen verziót látunk ugyanabból a listából. A feladat tehát az, hogy ha pl. a 16-ot keresem, akkor a 18-ad adja vissza, mert a lista elemei közül az van hozzá a legközelebb. És hogyha a 70-et keresem, akkor a 66-ot szeretném visszakapni, mert az a legközelebbi. Az FKERES-el csak a rendezett listában lenne értelme keresni, de ott csak a a 12-t lehetne visszakapni, a 18-at soha!
Nézzük először az ötletet!
Kicsit elgondolkoztam a dolgon, kitaláltam egy lehetséges utat. Ezután rákerestem, és a megoldások között megelégedésemre volt olyan, ami szerint jól gondolkodtam. A többféle lehetőség közül végül ezt választottam.
A példában továbbra is a 16-ot keressük.
- A lista minden elemére megnézném, hogy mekkora távolságra van a keresett számtól. tehát minden elemből kivonom a keresett értéket, és veszem az eredmény abszolút értékét.
- Ezután veszem az előbb kapott távolságok minimum értékét,mert a legkisebb távolságot keresem. A példánk esetén a min. távolság a 2, ami a 18-hoz tartozik.
- Majd a HOL.VAN (MATCH) függvénnyel megnézem, hogy a kiszámolt távolságok listájában a minimum érték hányadik. (kilencedik)
- Végül INDEX függvénnyel kiveszem az eredeti lista kilencedik elemét, ami a 18.
Ugyanez egy tömbképlettel
Már megint, IGEN! Nézzétek meg ezt a zseniális képletet, ami a fentieket egy db képletben megfogalmazza és ki is számolja nekünk. Az A2:A21 tartományban keressük a C2-ben lévő értékhez legközelebb eső elemet.
=INDEX(A2:A21;HOL.VAN(MIN(ABS(A2:A21-C2));ABS(A2:A21-C2);0))
- ABS(A2:A21-C2): képzi a távolságok listáját
- MIN(ABS(A2:A21-C2)): Veszi a távolságok minimumát
- A HOL.VAN megmondja, hogy a távolságok listáján ahányadik a legkisebb elem
- Az INDEX pedig visszaadja az annyiadikat az eredeti listából.
Tisztelt Imre/ExcelBázis
Nagyon hasznos ez a kis videó és tutorial (is), azonban volna egy technikai jellegű kérdésem.
Sokadszorra futottam bele abba, hogy a végső megoldás tömbképletet eredményezne, viszont a feladat jellegéből adódóan szükség lenne ennek a makrózására. Makró rögzítővel felveszem a függvényt, de ha meg szeretném írni magamtól (hiába ugyan úgy írom le vba-ban, mint ahogy a rögzítő felveszi) nem működik (eddig azt feltételeztem, hogy a tömbképlet lezárása miatt Ctrl+Shift+Enter, amit nem tudtam megfelelően leírni).
Nem tudom másokban felmerült-e az igény, de én pl szívesen látnám a videókban, hogy hogyan kell megoldani pl ezt a problémát segédoszlop nélkül közvetlenül makrózva (esetleg a kódsort is). Ez a megoldás elegáns de kevésbé automatizálható.
üdvözlettel
szabolcs
Szia Szabolcs!
A megoldások többségében a makró nélküli megoldásokat adom meg.
Ha sima képleteket tudsz makrózni, akkor tömbképleteket is tudsz, csak rá kell keresni, hogy hogy lehet tömbképletet írni egy cellába.
Ha megteszed, nagyon gyorsan kiderül, hogy van egy
FormulaArraymetódus, amivel ezt meg lehet tenni.Tehát próbáld ki a következőt:
1. Rögzítsd a képletet makró rögzítővel
2. Módosítsd a FormulaR1C1-et FormulaArray-re
Imre
Szia Imi! Klassz megoldás. De mi történik, ha a keresett érték pont két adat közé esik? Tegyük fel, hogy a példában szereplő adatok között nem a 16-hoz, hanem a 15-höz legközelebbi értéket keresem? Ebben az esetben a sorrendben előrébb álló elemet fogja a képlet megtalálni (tehát a 12-t), míg a 18 épp olyan jó válasz lenne. Tehát kizárólag azon múlik az eredmény, hogy milyen sorrenben szerepelnek az elemek. Tudom, az élet kegyetlen… 🙂 Van erre nézve valami okos megoldás? (A tömbfüggvény eredménytartománya lehetne két cella is, tehát csak az a kérdés, hogy be lehet-e építeni valahogy azt is a képletbe, hogy vizsgálja meg, hogy egyszer vagy kétszer fordul-e elő a minimum, és ha kétszer, akkor a keresett értékből egyszer vonja ki, egyszer pedig adja hozzá a megtalált legkisebb különbséget.)
Igen,teljesen igazad van. És az élet valóban kegyetlen. Én ezen nem tornázok tovább, tehát ha pont két érték közé esik, azaz mindkettőtől ugyanannyira van, akkor a kisebbet fogja visszaadni.
Lehetne írni egy olyan UDF-et, ahol egy argumentummal lehet szabályozni, hogy ha két eredmény van, akkor melyikre van szükségünk.
szia!
ha több oszlopom van, ahol keresni szeretném az értéket, ott hogy működik a képlet?
köszi!
boni
Ez a képlet sehogy.
Ha 2 oszlopban keresel, akkor külön-külön kivenném a két oszlopból a legközelebbit egy-egy cellába, majd megnézném, hogy ezek közül melyik esik közelebb hozzá.
Ez már csak egy sima IF.
Imre
akkor így lesz! ::) köszönöm szépen!