Naudojant „Excel“ paieškos formules (pvz., „VLOOKUP“, „XLOOKUP“ arba „INDEX/MATCH“), siekiama surasti atitinkančią vertę ir gauti tą vertę (arba atitinkamą vertę toje pačioje eilutėje/stulpelyje).
Tačiau kai kuriais atvejais, užuot gavę vertę, galbūt norėsite, kad formulė grąžintų vertės langelio adresą.
Tai gali būti ypač naudinga, jei turite didelį duomenų rinkinį ir norite sužinoti tikslią paieškos formulės rezultato vietą.
„Excel“ yra keletas funkcijų, skirtų būtent tai padaryti.
Šioje pamokoje aš jums parodysiu, kaip galite suraskite ir grąžinkite langelio adresą, o ne vertę „Excel“ naudojant paprastas formules.
Ieškokite ir grąžinkite langelio adresą naudodami ADDRESS funkciją
Funkcija ADDRESS programoje „Excel“ skirta būtent tai.
Paimamas eilutės ir stulpelio numeris ir pateikiamas tos konkrečios ląstelės adresas.
Žemiau yra funkcijos ADDRESS sintaksė:
= ADDRESS (eilutės numeris, stulpelio numeris, [abs_num], [a1], [sheet_text])
kur:
- row_num: langelio, kuriam norite langelio adreso, eilutės numeris
- column_num: langelio, kuriam norite adreso, stulpelio numeris
- [abs_num]: pasirenkamas argumentas, kuriame galite nurodyti, ar norite, kad langelio nuoroda būtų absoliuti, santykinė ar mišri.
- [a1]: pasirenkamas argumentas, kuriame galite nurodyti, ar norite, kad nuoroda būtų R1C1 ar A1 stiliaus
- [sheet_text]: pasirenkamas argumentas, kuriame galite nurodyti, ar norite pridėti lapo pavadinimą kartu su langelio adresu, ar ne
Dabar paimkime pavyzdį ir pažiūrėkime, kaip tai veikia.
Tarkime, yra duomenų rinkinys, kaip parodyta žemiau, kur turiu darbuotojo ID, jų vardą ir skyrių ir noriu greitai sužinoti langelio adresą, kuriame yra darbuotojo ID KR256 skyrius.
Žemiau yra formulė, kuri tai padarys:
= ADRESAS (MATCH ("KR256", A1: A20,0), 3)
Aukščiau pateiktoje formulėje aš naudoju funkciją MATCH, norėdamas sužinoti eilutės numerį, kuriame yra nurodytas darbuotojo ID.
Kadangi departamentas yra C skiltyje, aš naudoju 3 kaip antrąjį argumentą.
Ši formulė puikiai veikia, tačiau turi vieną trūkumą - ji neveiks, jei pridėsite eilutę virš duomenų rinkinio arba stulpelį duomenų rinkinio kairėje.
Taip yra todėl, kad kai nurodau antrąjį argumentą (stulpelio numerį) kaip 3, jis yra užkoduotas ir nesikeis.
Jei pridėčiau bet kurį stulpelį duomenų rinkinio kairėje, formulė skaičiuotų 3 stulpelius nuo darbalapio pradžios, o ne nuo duomenų rinkinio pradžios.
Taigi, jei turite fiksuotą duomenų rinkinį ir jums reikia paprastos formulės, tai veiks gerai.
Bet jei jums reikia, kad tai būtų labiau neapgalvota, naudokite kitame skyriuje aprašytą.
Ieškokite ir grąžinkite langelio adresą naudodami CELL funkciją
Funkcija ADDRESS buvo sukurta specialiai tam, kad suteiktų jums nurodytos eilutės ir stulpelio numerio langelio nuorodą, tačiau yra ir kita funkcija.
Tai vadinama CELL funkcija (ir ji gali suteikti jums daug daugiau informacijos apie ląstelę nei funkcija ADDRESS).
Žemiau pateikiama funkcijos CELL sintaksė:
= Ląstelė (info_type, [nuoroda])
kur:
- info_type: informacija apie norimą langelį. Tai gali būti adresas, stulpelio numeris, failo pavadinimas ir kt.
- [nuoroda]: Pasirenkamas argumentas, kuriame galite nurodyti langelio nuorodą, kuriai reikia langelio informacijos.
Dabar pažiūrėkime pavyzdį, kuriame galite naudoti šią funkciją, norėdami ieškoti ir gauti langelio nuorodą.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite greitai sužinoti langelio adresą, kuriame yra darbuotojo ID KR256 skyrius.
Žemiau yra formulė, kuri tai padarys:
= CELL ("adresas", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Aukščiau pateikta formulė yra gana paprasta.
Aš naudoju INDEX formulę kaip antrąjį argumentą, kad gaučiau skyriaus darbuotojo ID KR256 skyrių.
Tada tiesiog suvyniokite jį į CELL funkciją ir paprašykite grąžinti šios vertės langelio adresą, kurį gaunu iš INDEX formulės.
Dabar čia yra paslaptis, kodėl tai veikia - INDEX formulė grąžina paieškos vertę, kai pateikiate visus būtinus argumentus. Tačiau tuo pačiu metu ji taip pat grąžintų gautos ląstelės nuorodą.
Mūsų pavyzdyje INDEX formulė grąžina „Pardavimus“ kaip gautą vertę, tačiau tuo pat metu taip pat galite ją naudoti norėdami pateikti tos vertės langelio nuorodą, o ne pačią vertę.
Paprastai įvedus INDEX formulę į langelį, ji grąžina vertę, nes tikimasi, kad tai padarys. Tačiau tais atvejais, kai būtina nurodyti langelio nuorodą, INDEX formulė suteiks langelio nuorodą.
Šiame pavyzdyje būtent tai ir daroma.
Geriausia naudoti šią formulę yra tai, kad ji nėra susieta su pirmuoju darbalapio langeliu. Tai reiškia, kad galite pasirinkti bet kokį duomenų rinkinį (kuris gali būti bet kurioje darbalapio vietoje), naudoti INDEX formulę, kad reguliariai ieškotumėte ir vis tiek gautumėte teisingą adresą.
Ir jei įterpsite papildomą eilutę ar stulpelį, formulė bus atitinkamai pakoreguota, kad gautumėte teisingą langelio adresą.
Taigi tai yra dvi paprastos formulės, pagal kurias galite ieškoti ir suraskite ir grąžinkite langelio adresą vietoj „Excel“ vertės.
Tikiuosi, kad ši pamoka jums buvo naudinga.
