Amikor Excelben keresési képleteket használ (például VLOOKUP, XLOOKUP vagy INDEX/MATCH), a cél az, hogy megtalálja az egyező értéket, és megkapja ezt az értéket (vagy a megfelelő értéket ugyanabban a sorban/oszlopban).
De bizonyos esetekben az érték megszerzése helyett érdemes lehet, ha a képlet visszaadja az érték cellacímét.
Ez különösen akkor lehet hasznos, ha nagy adathalmaza van, és szeretné megtudni a keresési képlet eredményének pontos helyét.
Az Excelben vannak olyan funkciók, amelyek pontosan ezt teszik.
Ebben az oktatóanyagban megmutatom, hogyan lehet keresse meg és adja vissza a cella címét az érték helyett Excelben egyszerű képletek segítségével.
Cellacím keresése és visszaadása az ADDRESS funkció használatával
Az Excel CÍM funkciója pontosan erre szolgál.
Fogja a sort és az oszlopszámot, és megadja az adott cella cellacímét.
Az alábbiakban a CÍM függvény szintaxisa látható:
= CÍM (sorszám, oszlopszám, [absz. Szám], [a1], [lapszöveg])
ahol:
- sor_száma: Annak a cellának a sorszáma, amelyhez cellacímet szeretne megadni
- column_num: Annak a cellának az oszlopszáma, amelynek címét szeretné megadni
- [abs_num]: Nem kötelező argumentum, ahol megadhatja, hogy a cellahivatkozás abszolút, relatív vagy vegyes legyen.
- [a1]: Választható argumentum, ahol megadhatja, hogy a hivatkozást R1C1 vagy A1 stílusban szeretné -e használni
- [sheet_text]: Választható argumentum, ahol megadhatja, hogy a lap nevét a cella címével együtt kívánja -e hozzáadni vagy sem
Most vegyünk egy példát, és nézzük meg, hogyan működik ez.
Tegyük fel, hogy van egy adathalmaz az alábbiak szerint, ahol megvan a munkavállalói azonosító, a nevük és az osztályuk, és gyorsan meg akarom tudni a cellacímet, amely tartalmazza a KR256 alkalmazott azonosító osztályát.
Az alábbiakban ezt a képletet találja meg:
= CÍM (MATCH ("KR256", A1: A20,0), 3)
A fenti képletben a MATCH függvénnyel kerestem meg az adott alkalmazott azonosítóját tartalmazó sorszámot.
És mivel az osztály a C oszlopban van, második érvként a 3 -at használtam.
Ez a képlet remekül működik, de van egy hátránya - nem fog működni, ha az adathalmaz feletti sort vagy az oszlopot az adatkészlet bal oldalán adja hozzá.
Ez azért van, mert amikor a második argumentumot (az oszlopszámot) 3-nak adom meg, az kódolva van, és nem fog változni.
Abban az esetben, ha bármilyen oszlopot hozzáadok az adatkészlet bal oldalán, a képlet 3 oszlopot számolna a munkalap elejétől, és nem az adathalmaz elejétől.
Tehát, ha rögzített adatkészlete van, és egyszerű képletre van szüksége, ez jól fog működni.
De ha azt szeretné, hogy ez nagyobb biztonságban legyen, használja a következő részben leírtakat.
Cellacím keresése és visszaadása a CELL funkció használatával
Míg a CÍM funkció kifejezetten a megadott sor- és oszlopszám cellahivatkozásának megadására készült, van egy másik funkció is, amely ezt teszi.
Ezt hívják CELL függvénynek (és sokkal több információt tud adni a celláról, mint a CÍM funkció).
Az alábbiakban a CELL függvény szintaxisa látható:
= CELL (info_type, [hivatkozás])
ahol:
- info_type: a kívánt cellára vonatkozó információ. Ez lehet a cím, az oszlop száma, a fájl neve stb.
- [referencia]: Választható argumentum, ahol megadhatja azt a cellahivatkozást, amelyhez a cellainformációra van szüksége.
Most nézzünk egy példát, ahol ezt a funkciót használva megkeresheti és megkaphatja a cellahivatkozást.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és gyorsan szeretné tudni a cellacímet, amely a KR256 alkalmazott azonosító osztályát tartalmazza.
Az alábbiakban ezt a képletet találja meg:
= CELL ("cím", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
A fenti képlet meglehetősen egyszerű.
Második érvként az INDEX képletet használtam a KR256 alkalmazott azonosító osztályának lekéréséhez.
És akkor egyszerűen becsomagolta a CELL függvénybe, és kérte, hogy adja vissza ennek az értéknek a cellacímét, amelyet az INDEX képletből kapok.
Most itt van a titok, miért működik - az INDEX képlet a keresési értéket adja vissza, ha megadja az összes szükséges argumentumot. De ugyanakkor visszaadja a kapott cella cellareferenciáját is.
Példánkban az INDEX képlet az „Értékesítés” értéket adja eredményül, de ezzel egyidejűleg azt is megadhatja, hogy az érték cellahivatkozását adja meg maga helyett.
Általában, amikor beírja az INDEX képletet egy cellába, akkor visszaadja az értéket, mert ez várható. De olyan esetekben, amikor cellahivatkozásra van szükség, az INDEX képlet megadja a cellahivatkozást.
Ebben a példában pontosan ezt teszi.
És ennek a képletnek a legjobb használata az, hogy nincs kötve a munkalap első cellájához. Ez azt jelenti, hogy kiválaszthat bármilyen adathalmazt (amely bárhol lehet a munkalapon), használhatja az INDEX képletet a rendszeres kereséshez, és így is megadja a helyes címet.
És ha további sort vagy oszlopot szúr be, a képlet ennek megfelelően módosul, hogy megadja a helyes cellacímet.
Tehát ez két egyszerű képlet, amelyekkel megkeresheti és keresse meg és adja vissza a cella címét az Excel érték helyett.
Remélem, hasznosnak találta ezt az oktatóanyagot.
