Při použití vzorců vyhledávání v aplikaci Excel (například VLOOKUP, XLOOKUP nebo INDEX/MATCH) je záměrem najít odpovídající hodnotu a získat tuto hodnotu (nebo odpovídající hodnotu ve stejném řádku/sloupci) jako výsledek.
Ale v některých případech můžete místo získání hodnoty chtít, aby vzorec vrátil adresu buňky hodnoty.
To by mohlo být obzvláště užitečné, pokud máte velkou datovou sadu a chcete zjistit přesnou pozici výsledku vzorce vyhledávání.
Existuje několik funkcí v aplikaci Excel, které byly navrženy tak, aby to přesně dělaly.
V tomto tutoriálu vám ukážu, jak můžete místo hodnoty najděte a vraťte adresu buňky v Excelu pomocí jednoduchých vzorců.
Vyhledejte a vraťte adresu buňky pomocí funkce ADDRESS
Přesně k tomu slouží funkce ADDRESS v Excelu.
Vezme číslo řádku a sloupce a poskytne vám adresu buňky konkrétní buňky.
Níže je syntaxe funkce ADDRESS:
= ADDRESS (číslo_řádku, číslo_sloupce, [abs_num], [a1], [text_ listu])
kde:
- row_num: Číslo řádku buňky, pro kterou chcete adresu buňky
- column_num: Číslo sloupce buňky, pro kterou chcete adresu
- [abs_num]: Volitelný argument, kde můžete určit, zda má být odkaz na buňku absolutní, relativní nebo smíšený.
- [a1]: Volitelný argument, kde můžete určit, zda chcete odkaz ve stylu R1C1 nebo A1
- [sheet_text]: Volitelný argument, kde můžete určit, zda chcete přidat název listu spolu s adresou buňky nebo ne
Nyní si vezmeme příklad a uvidíme, jak to funguje.
Předpokládejme, že existuje níže uvedený soubor dat, kde mám identifikační číslo zaměstnance, jeho jméno a oddělení a chci rychle znát adresu buňky, která obsahuje oddělení pro identifikační číslo zaměstnance KR256.
Níže je uveden vzorec, který to provede:
= ADRESA (MATCH ("KR256", A1: A20,0), 3)
Ve výše uvedeném vzorci jsem pomocí funkce MATCH zjistil číslo řádku, které obsahuje dané ID zaměstnance.
A protože je oddělení ve sloupci C, použil jsem jako druhý argument 3.
Tento vzorec funguje skvěle, ale má jednu nevýhodu - nebude fungovat, pokud přidáte řádek nad datovou sadu nebo sloupec nalevo od datové sady.
Důvodem je, že když zadám druhý argument (číslo sloupce) jako 3, bude pevně zakódován a nezmění se.
V případě, že přidám libovolný sloupec nalevo od datové sady, vzorec by počítal 3 sloupce od začátku listu a ne od začátku datové sady.
Pokud tedy máte pevnou datovou sadu a potřebujete jednoduchý vzorec, bude to fungovat dobře.
Ale pokud to potřebujete, aby to bylo bezpečnější, použijte ten, který je popsán v další části.
Vyhledejte a vraťte adresu buňky pomocí funkce CELL
Zatímco funkce ADDRESS byla vytvořena speciálně za účelem získání odkazu na buňku zadaného čísla řádku a sloupce, existuje i jiná funkce, která to také dělá.
Říká se tomu funkce CELL (a může vám poskytnout mnohem více informací o buňce než funkce ADDRESS).
Níže je syntaxe funkce CELL:
= CELL (info_type, [reference])
kde:
- info_type: informace o požadované buňce. Může to být adresa, číslo sloupce, název souboru atd.
- [odkaz]: Volitelný argument, kde můžete zadat odkaz na buňku, pro kterou potřebujete informace o buňce.
Nyní se podívejme na příklad, kde můžete pomocí této funkce vyhledat a získat odkaz na buňku.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete rychle znát adresu buňky, která obsahuje oddělení pro id zaměstnance KR256.
Níže je uveden vzorec, který to provede:
= CELL ("adresa", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Výše uvedený vzorec je docela přímočarý.
Jako druhý argument jsem použil vzorec INDEX k získání oddělení pro ID zaměstnance KR256.
A pak to jednoduše zabalil do funkce CELL a požádal ji, aby vrátila adresu buňky této hodnoty, kterou získám ze vzorce INDEX.
Nyní je zde tajemství, proč to funguje - vzorec INDEX vrací vyhledanou hodnotu, když jí zadáte všechny potřebné argumenty. Ale zároveň by to také vrátilo odkaz na buňku výsledné buňky.
V našem příkladu vzorec INDEX vrací jako výslednou hodnotu „Prodej“, ale zároveň jej můžete také použít k tomu, aby vám místo samotné hodnoty poskytl odkaz na buňku této hodnoty.
Za normálních okolností, když do buňky zadáte vzorec INDEX, vrátí hodnotu, protože to se od něj očekává. Ale ve scénářích, kde je vyžadován odkaz na buňku, vám vzorec INDEX poskytne odkaz na buňku.
V tomto příkladu to přesně dělá.
A nejlepší na používání tohoto vzorce je, že není svázán s první buňkou v listu. To znamená, že můžete vybrat libovolnou sadu dat (která může být kdekoli v listu), pomocí vzorce INDEX provádět pravidelné vyhledávání a stále vám poskytne správnou adresu.
A pokud vložíte další řádek nebo sloupec, vzorec by se odpovídajícím způsobem upravil, aby vám poskytl správnou adresu buňky.
Jedná se tedy o dva jednoduché vzorce, pomocí kterých můžete vyhledat a místo hodnoty v aplikaci Excel najděte a vraťte adresu buňky.
Doufám, že jste našli tento návod užitečný.
