Kui kasutate Excelis otsinguvalemeid (nt VLOOKUP, XLOOKUP või INDEX/MATCH), on eesmärk leida sobiv väärtus ja saada see väärtus (või vastav väärtus samas reas/veerus) tulemina.
Kuid mõnel juhul võite väärtuse hankimise asemel soovida, et valem tagastaks väärtuse lahtri aadressi.
See võib olla eriti kasulik, kui teil on suur andmekogum ja soovite välja selgitada otsinguvalemi tulemuse täpse asukoha.
Excelis on mõned funktsioonid, mille eesmärk on täpselt seda teha.
Selles õpetuses näitan teile, kuidas saate leidke ja tagastage väärtuse asemel lahtri aadress Excelis lihtsate valemite abil.
Otsige ja tagastage lahtri aadress, kasutades funktsiooni ADDRESS
Funktsioon AADRESS Excelis on mõeldud just selleks.
See võtab rea ja veeru numbri ning annab teile selle konkreetse lahtri lahtri aadressi.
Allpool on funktsiooni ADDRESS süntaks:
= AADRESS (rea number, veeru number, [abs_number], [a1], [lehe_tekst])
kus:
- row_num: selle lahtri rea number, mille lahtri aadressi soovite
- column_num: selle lahtri veerunumber, mille aadressi soovite
- [abs_num]: valikuline argument, kus saate määrata, kas soovite, et lahtri viide oleks absoluutne, suhteline või segatud.
- [a1]: valikuline argument, kus saate määrata, kas soovite viidet stiilis R1C1 või A1
- [lehe_tekst]: valikuline argument, kus saate määrata, kas soovite lisada lehe nime koos lahtri aadressiga või mitte
Võtame nüüd näite ja vaatame, kuidas see toimib.
Oletame, et allpool on näidatud andmestik, kus mul on töötaja ID, nende nimi ja osakond ning ma tahan kiiresti teada lahtri aadressi, mis sisaldab töötaja ID KR256 osakonda.
Allpool on valem, mis seda teeb:
= AADRESS (MATCH ("KR256", A1: A20,0), 3)
Ülaltoodud valemis olen kasutanud funktsiooni MATCH, et teada saada rea number, mis sisaldab antud töötaja ID -d.
Ja kuna osakond on veerus C, olen teise argumendina kasutanud 3.
See valem töötab suurepäraselt, kuid sellel on üks puudus - see ei tööta, kui lisate andmestiku kohale rea või veeru vasakule.
Seda seetõttu, et kui määran teise argumendi (veeru numbri) väärtuseks 3, on see raskesti kodeeritud ega muutu.
Kui lisan andmestikust vasakule veeru, loeks valem 3 veergu töölehe algusest, mitte andmekogumi algusest.
Seega, kui teil on fikseeritud andmekogum ja vajate lihtsat valemit, töötab see hästi.
Kuid kui soovite, et see oleks lollikindlam, kasutage järgmises jaotises käsitletut.
Otsige ja tagastage lahtri aadress, kasutades funktsiooni CELL
Funktsioon AADRESS tehti spetsiaalselt selleks, et anda teile määratud rea ja veeru numbri lahtri viide, on ka teine funktsioon, mis seda ka teeb.
Seda nimetatakse CELL -funktsiooniks (ja see võib anda teile lahtri kohta palju rohkem teavet kui funktsioon ADDRESS).
Allpool on funktsiooni CELL süntaks:
= CELL (info_tüüp, [viide])
kus:
- info_tüüp: soovitud lahtri teave. See võib olla aadress, veeru number, faili nimi jne.
- [viide]: Valikuline argument, kus saate määrata lahtri viite, mille jaoks vajate lahtri teavet.
Vaatame nüüd näidet, kus saate seda funktsiooni kasutada lahtri viite otsimiseks ja hankimiseks.
Oletame, et teil on allpool näidatud andmekogum ja soovite kiiresti teada lahtri aadressi, mis sisaldab töötaja ID KR256 osakonda.
Allpool on valem, mis seda teeb:
= CELL ("aadress", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Ülaltoodud valem on üsna lihtne.
Olen kasutanud valemit INDEX teise argumendina, et saada osakond töötaja ID KR256 jaoks.
Ja siis pakkis selle lihtsalt CELL funktsiooni sisse ja palus tagastada selle väärtuse lahtri aadressi, mille saan INDEX valemist.
Nüüd on siin saladus, miks see töötab - INDEX valem tagastab otsinguväärtuse, kui esitate sellele kõik vajalikud argumendid. Kuid samal ajal tagastab see ka selle tulemusel saadud lahtri viite.
Meie näites tagastab INDEX valem tulemuseks „Müük”, kuid samal ajal saate seda kasutada ka selle väärtuse lahtri viite andmiseks väärtuse enda asemel.
Tavaliselt, kui sisestate lahtrisse INDEX valemi, tagastab see väärtuse, sest seda oodatakse. Kuid stsenaariumide korral, kus on vaja lahtri viidet, annab INDEX valem teile lahtri viite.
Selles näites teeb see täpselt seda.
Ja parim osa selle valemi kasutamisel on see, et see pole seotud töölehe esimese lahtriga. See tähendab, et saate valida mis tahes andmekogumi (mis võib olla kõikjal töölehel), kasutada INDEX valemit korrapäraseks otsimiseks ja see annaks teile ikkagi õige aadressi.
Ja kui lisate täiendava rea või veeru, kohandatakse valemit vastavalt, et anda teile õige lahtri aadress.
Nii et need on kaks lihtsat valemit, mille abil saate otsida ja otsige ja tagastage Exceli väärtuse asemel lahtri aadress.
Loodan, et leidsite selle õpetuse kasulikuks.
