Pri použití vyhľadávacích vzorcov v programe Excel (napríklad VLOOKUP, XLOOKUP alebo INDEX/MATCH) je zámerom nájsť zodpovedajúcu hodnotu a získať túto hodnotu (alebo zodpovedajúcu hodnotu v rovnakom riadku/stĺpci) ako výsledok.
V niektorých prípadoch však namiesto získania hodnoty môžete chcieť, aby vzorec vrátil adresu bunky hodnoty.
To môže byť obzvlášť užitočné, ak máte veľkú množinu údajov a chcete zistiť presnú pozíciu výsledku vyhľadávacieho vzorca.
V programe Excel je niekoľko funkcií, ktoré sú navrhnuté tak, aby to robili presne.
V tomto návode vám ukážem, ako môžete namiesto hodnoty nájdite a vráťte adresu bunky v Exceli pomocou jednoduchých vzorcov.
Adresu bunky vyhľadajte a vráťte pomocou funkcie ADDRESS
Presne na to slúži funkcia ADDRESS v Exceli.
Zoberie číslo riadka a stĺpca a poskytne vám adresu bunky tejto konkrétnej bunky.
Nasleduje syntax funkcie ADDRESS:
= ADDRESS (riadkový_číslo, stĺpcový_číslo, [abs_num], [a1], [list_text])
kde:
- row_num: Číslo riadka bunky, pre ktorú chcete adresu bunky
- column_num: Číslo stĺpca bunky, pre ktorú chcete adresu
- [abs_num]: Voliteľný argument, kde môžete určiť, či má byť odkaz na bunku absolútny, relatívny alebo zmiešaný.
- [a1]: Voliteľný argument, kde môžete určiť, či chcete referenciu v štýle R1C1 alebo A1
- [sheet_text]: Voliteľný argument, kde môžete určiť, či chcete pridať názov hárka spolu s adresou bunky alebo nie
Teraz si vezmime príklad a uvidíme, ako to funguje.
Predpokladajme, že existuje množina údajov, ako je uvedené nižšie, kde mám ID zamestnanca, jeho meno a oddelenie a chcem rýchlo zistiť adresu bunky, ktorá obsahuje oddelenie pre ID zamestnanca KR256.
Nasleduje vzorec, ktorý to urobí:
= ADRESA (ZHODA ("KR256", A1: A20,0), 3)
Vo vyššie uvedenom vzorci som použil funkciu MATCH na zistenie čísla riadka, ktoré obsahuje dané ID zamestnanca.
A keďže oddelenie je v stĺpci C, ako druhý argument som použil 3.
Tento vzorec funguje skvele, ale má jednu nevýhodu - nebude fungovať, ak pridáte riadok nad množinu údajov alebo stĺpec naľavo od množiny údajov.
Dôvodom je, že keď zadám druhý argument (číslo stĺpca) ako 3, bude napevno zakódovaný a nezmení sa.
V prípade, že pridám ľubovoľný stĺpec naľavo od množiny údajov, vzorec by počítal 3 stĺpce od začiatku pracovného hárka a nie od začiatku množiny údajov.
Ak teda máte pevný súbor údajov a potrebujete jednoduchý vzorec, bude to fungovať dobre.
Ak to však potrebujete, aby bolo bezpečnejšie, použite ten, ktorý je popísaný v nasledujúcej časti.
Adresu bunky vyhľadajte a vráťte pomocou funkcie CELL
Aj keď bola funkcia ADDRESS vytvorená špeciálne tak, aby vám poskytla odkaz na bunku zadaného čísla riadka a stĺpca, existuje aj iná funkcia, ktorá to tiež robí.
Hovorí sa mu funkcia CELL (a môže vám poskytnúť oveľa viac informácií o bunke ako funkcia ADDRESS).
Nasleduje syntax funkcie CELL:
= CELL (info_type, [odkaz])
kde:
- info_type: informácie o požadovanej bunke. Môže to byť adresa, číslo stĺpca, názov súboru atď.
- [odkaz]: Voliteľný argument, kde môžete zadať odkaz na bunku, pre ktorú potrebujete informácie o bunke.
Teraz sa pozrime na príklad, kde môžete túto funkciu použiť na vyhľadanie a získanie odkazu na bunku.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete rýchlo poznať adresu bunky, ktorá obsahuje oddelenie pre ID zamestnanca KR256.
Nasleduje vzorec, ktorý to urobí:
= CELL ("adresa", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Vyššie uvedený vzorec je celkom jednoduchý.
Ako druhý argument na získanie oddelenia pre ID zamestnanca KR256 som použil vzorec INDEX.
Potom ho jednoducho zabalil do funkcie CELL a požiadal ho, aby vrátil adresu bunky tejto hodnoty, ktorú získam zo vzorca INDEX.
Teraz tu je tajomstvo, prečo to funguje - vzorec INDEX vráti hľadanú hodnotu, keď jej zadáte všetky potrebné argumenty. Ale zároveň by to tiež vrátilo odkaz na bunku výslednej bunky.
V našom prípade vzorec INDEX vracia ako výslednú hodnotu „Predaj“, ale zároveň ho môžete použiť aj na to, aby vám namiesto samotnej hodnoty poskytol odkaz na bunku tejto hodnoty.
Za normálnych okolností, keď do bunky zadáte vzorec INDEX, vráti hodnotu, pretože sa to očakáva. Ale v scenároch, kde je požadovaný odkaz na bunku, vám vzorec INDEX poskytne odkaz na bunku.
V tomto prípade to je presne to, čo robí.
A najlepšia časť o použití tohto vzorca je, že nie je viazaný na prvú bunku v hárku. To znamená, že môžete vybrať ľubovoľný súbor údajov (ktorý môže byť kdekoľvek v pracovnom hárku), pomocou vzorca INDEX vykonať pravidelné vyhľadávanie a stále vám poskytne správnu adresu.
A ak vložíte ďalší riadok alebo stĺpec, vzorec sa podľa toho upraví, aby vám poskytol správnu adresu bunky.
Toto sú dva jednoduché vzorce, pomocou ktorých môžete vyhľadať a vyhľadajte a vráťte adresu bunky namiesto hodnoty v programe Excel.
Dúfam, že ste našli tento návod užitočný.
