Mérettáblázat – INDEX és HOL.VAN függvény

A napokban Attilától érkezett a kérdés, hogyan tudna egy mérettáblázatból függvénnyel behivatkozni egy árat, vagyis adott a magasság, a szélesség és ezek alapján automatikusan kerüljön be az ár a cellába. Két haladó függvény szükséges hozzá, mutatom!

A feladatot innen letöltheted.

Két táborba sorolhatók az Excel-t használók, vannak akik az FKERES (pontos egyezés vagy közelítőleges egyezés), míg mások az INDEX és HOL.VAN függvény kombinációjára esküsznek, ha keresésről van szó. A fenti példát viszont csak az egyikkel tudod megoldani.

Az INDEX (angolul: INDEX) függvény „értéket vagy hivatkozás ad vissza egy adott tartomány bizonyos sorának és oszlopának metszéspontjában lévő cellából”. Tehát pont megfelel a mi problémánkra, adott sor és oszlop metszéspontjából szükséges kiszedni az árat.

A függvény behívásához kattints a szerkesztőléc melletti fx gombra és keresd meg az INDEX függvényt (függvények alapvető használatához itt találsz segítséget).

Az INDEX függvénynek két argumentumlistája van, az elsőt fogjuk most használni. A második esetben ugyanúgy sor és oszlop metszéspontjából ad értéket, de az, ahol keresel, lehet több, nem összefüggő táblázat is. Válaszd tehát az elsőt!

=INDEX(Tömb;Sor_szám;Oszlop_szám)

Tömb: az a sor, oszlop vagy táblázat, amelyből az értéket ki akarod keresni

Sor_szám: annak a sornak a száma, amelyikből ki kell venni az értéket

Oszlop_szám: annak az oszlopnak a száma, amelyikből ki kell venni az értéket

Több lehetőséged is van a használatánál:

1. csak egy sort adsz meg, amelyben keresel, ekkor az oszlopszám megadása kötelező, vagyis az, hogy melyik oszlopából akarod látni az eredményt.
2. csak egy oszlopot adsz meg, amelyben keresel, ekkor a sorszám megadása kötelező, vagyis az, hogy melyik sorából vegye az eredményt.
3. egy táblázatot adsz meg (több sor, több oszlop), ekkor a sorszám és az oszlopszám megadása is kötelező, vagyis, melyik sorának és melyik oszlopának metszéspontjából vegye az eredményt.

Ha az INDEX függvényről érdekel egy részletesebb leírás, olvasd el az INDEX függvény magyarázata bejegyzésünket az excellence.hu-n.

A HOL.VAN (angolul MATCH) függvényről egy korábbi bejegyzésünkben olvashatsz bővebben.

Lényegében a függvényben meg kell adnod, hogy mi a keresett kifejezés, azt hol keresed (egy sor vagy egy oszlop) és milyen egyezést keresel, pontosat vagy közelítőlegest. Ehhez egy ki segítség:

A feladatban használt fájlt a blog elején letöltheted, ha még nem tetted.

A feladat tehát az, hogy adott szélességi és magassági adatok alapján kapjuk vissza az árat. (természetesen lehet más típusú is a táblázat: hossz-súly).

Ez az alap táblázat, tartalmazza a méreteket és az árakat:

Ebben a táblázatban vannak a konkrét méretek, ide kellene hozzákeresni az árakat:

A külső függvény az INDEX függvény, ezzel kezdjük a cellában, vagy a szerkesztőléc melletti fx gombra kattintva keresd meg az INDEX függvényt és az első argumentumlistát válaszd.

Cellába írva tehát:

=INDEX(

Ha ezek után is szeretnéd az argumentumlistát látni, kattints most az fx gombra, válaszd ki az első argumentumlistát és már láthatod is. (függvények alapvető használatáról, behívásáról itt olvashatsz)

A tömbnél add meg a táblázat adataid a fejlécek nélkül, tehát C11:E13, majd mivel másolni fogjuk, az F4 billentyűvel dollározd le (ha nem működik, akkor fn + f4). (Dollárjelek használatáról itt olvashatsz)

A Sor_szám argumentumhoz írd be a hol.van( kifejezést zárójellel együtt.

Majd a szerkesztőlécen kattints a hol.van( feliratra bárhova, hogy lásd a HOL.VAN függvény argumentumlistáját.

Mivel itt azt kell megadni, hogy melyik sorából vegyen értéket, így szélességet kell most kikeresnünk. Keresési érték a B17-es cella, a tábla a szélesség oszlopa és az egyezés típusa az 1, mert ha nem pontosan a táblában szereplő érték a szélesség (1000, 1200, vagy 1500), akkor is adjon értéket, mégpedig a hozzá legközelebb eső kisebb értéket.

Ha ez megvan, kattints a szerkesztőlécen az INDEX feliratra és add meg az Oszlop_szám argumentumot is a fentieknek megfelelően: írd be a hol.van függvényt és egy zárójelet, majd kattints fent a szerkesztőlécen.

Magassági adatokat keresünk a táblázat felső sorából és a másolás miatt itt is rögzítjük a táblát.

Ha ezek megvannak, kattints vissza az INDEX feliratra a szerkesztőlécen, ezt fogod látni:

Ezek után kattinthatsz a KÉSZ gombra és másolhatod is az eredményt.

(Ha a KÉSZ-re kattintás után feldob egy hibaüzenetet, hogy hiba van a képletben, javíthatja-e az Excel, itt csak egy zárójelet hiányol a végéről, ha rákattintasz, hogy IGEN, akkor automatikusan javítja és láthatod is az eredményt a cellában)

A gyakorlófájlban van még egy példa alul, próbáld meg egyedül, a megoldást alatta láthatod.

Ha tovább olvasnál még a témában, akkor olvasd el az INDEX függvény magyarázata és a HOL.VAN függvény magyarázat példákkal bejegyzésünket az excellence.hu oldalunkon.

Ha kérdésed van, tedd fel a Facebook oldalon kommentben, és ha tetszett, kérlek, oszd meg másokkal is :)