Pri uporabi iskalnih formul v Excelu (na primer VLOOKUP, XLOOKUP ali INDEX/MATCH) je namen najti ujemajočo se vrednost in kot rezultat dobiti to vrednost (ali ustrezno vrednost v isti vrstici/stolpcu).
Toda v nekaterih primerih namesto da bi dobili vrednost, boste morda želeli, da formula vrne naslov celice vrednosti.
To je lahko še posebej uporabno, če imate velik nabor podatkov in želite izvedeti natančen položaj rezultata iskalne formule.
V Excelu je nekaj funkcij, namenjenih prav temu.
V tej vadnici vam bom pokazal, kako zmorete poiščite in vrnite naslov celice namesto vrednosti v Excelu z uporabo preprostih formul.
Iskanje in vrnitev naslova celice z uporabo funkcije ADDRESS
Funkcija ADDRESS v Excelu je namenjena prav temu.
Vzame vrstico in številko stolpca in vam posreduje naslov celice te posebne celice.
Spodaj je sintaksa funkcije ADDRESS:
= NASLOV (številka vrstice, številka stolpca, [abs_številka], [a1], [besedilo_pisa])
kje:
- row_num: številka vrstice celice, za katero želite naslov celice
- stolpec_številka: Številka stolpca celice, za katero želite naslov
- [abs_num]: Izbirni argument, kjer lahko določite, ali naj bo sklic na celico absoluten, relativen ali pomešan.
- [a1]: Izbirni argument, kjer lahko določite, ali želite sklic v slogu R1C1 ali A1
- [text_text]: Izbirni argument, kjer lahko določite, ali želite dodati ime lista skupaj z naslovom celice ali ne
Zdaj pa vzemimo primer in poglejmo, kako to deluje.
Recimo, da obstaja niz podatkov, kot je prikazano spodaj, kjer imam ID zaposlenega, njegovo ime in njihov oddelek ter želim hitro izvedeti naslov celice, ki vsebuje oddelek za ID zaposlenega KR256.
Spodaj je formula, ki bo to naredila:
= NASLOV (MATCH ("KR256", A1: A20,0), 3)
V zgornji formuli sem s funkcijo MATCH izvedel številko vrstice, ki vsebuje podani ID zaposlenega.
In ker je oddelek v stolpcu C, sem za drugi argument uporabil 3.
Ta formula deluje odlično, vendar ima eno pomanjkljivost - ne bo delovala, če dodate vrstico nad naborom podatkov ali stolpec levo od nabora podatkov.
Ko drugi argument (številka stolpca) navedem kot 3, je to težko kodirano in se ne bo spremenilo.
V primeru, da levo od nabora podatkov dodam kateri koli stolpec, bi formula štela 3 stolpce od začetka delovnega lista in ne od začetka nabora podatkov.
Torej, če imate fiksni nabor podatkov in potrebujete preprosto formulo, bo to delovalo v redu.
Če pa želite, da je to bolj varno, uporabite tisto, ki je obravnavana v naslednjem razdelku.
Poiščite in vrnite naslov celice s funkcijo CELL
Medtem ko je bila funkcija ADDRESS izdelana posebej za podajanje sklica na celico navedene številke vrstice in stolpca, obstaja še ena funkcija, ki to tudi počne.
Imenuje se funkcija CELL (in vam lahko da veliko več informacij o celici kot funkcija ADDRESS).
Spodaj je sintaksa funkcije CELL:
= CELL (info_type, [reference])
kje:
- info_type: podatki o celici, ki jo želite. To je lahko naslov, številka stolpca, ime datoteke itd.
- [referenca]: Izbirni argument, kjer lahko podate sklic na celico, za katero potrebujete podatke o celici.
Zdaj pa poglejmo primer, kjer lahko s to funkcijo poiščete in dobite referenco celice.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite hitro izvedeti naslov celice, ki vsebuje oddelek za ID zaposlenega KR256.
Spodaj je formula, ki bo to naredila:
= CELL ("naslov", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Zgornja formula je precej preprosta.
Formulo INDEX sem uporabil kot drugi argument za pridobitev oddelka za ID zaposlenega KR256.
Nato sem ga preprosto zavil v funkcijo CELL in ga prosil, naj vrne naslov celice te vrednosti, ki jo dobim iz formule INDEX.
Zdaj je tukaj skrivnost, zakaj deluje - formula INDEX vrne iskalno vrednost, ko ji daste vse potrebne argumente. Hkrati pa bi vrnil tudi referenco celice te nastale celice.
V našem primeru formula INDEX vrne »Prodaja« kot nastalo vrednost, hkrati pa jo lahko uporabite tudi za podajanje sklica na celico te vrednosti namesto same vrednosti.
Običajno, ko v celico vnesete formulo INDEX, ta vrne vrednost, ker se tako pričakuje. Toda v scenarijih, kjer je potrebna referenca celice, vam formula INDEX poda sklic na celico.
V tem primeru točno to počne.
Najboljši del uporabe te formule pa je, da ni vezana na prvo celico na delovnem listu. To pomeni, da lahko izberete kateri koli nabor podatkov (ki je lahko kjer koli na delovnem listu), uporabite formulo INDEX za redno iskanje in še vedno boste dobili pravilen naslov.
Če vstavite dodatno vrstico ali stolpec, se bo formula ustrezno prilagodila, da boste dobili pravilen naslov celice.
To sta torej dve preprosti formuli, ki ju lahko uporabite za iskanje in poiščite in vrnite naslov celice namesto vrednosti v Excelu.
Upam, da vam je bila ta vadnica koristna.
