Når du bruker oppslagsformler i Excel (for eksempel VLOOKUP, XLOOKUP eller INDEX/MATCH), er hensikten å finne den matchende verdien og få den verdien (eller en tilsvarende verdi i samme rad/kolonne) som resultatet.
Men i noen tilfeller, i stedet for å få verdien, vil du kanskje at formelen skal returnere celleadressen til verdien.
Dette kan være spesielt nyttig hvis du har et stort datasett, og du vil finne ut den nøyaktige plasseringen av oppslagsformelresultatet.
Det er noen funksjoner i Excel som er designet for å gjøre akkurat dette.
I denne opplæringen vil jeg vise deg hvordan du kan finn og returner celleadressen i stedet for verdien i Excel ved hjelp av enkle formler.
Slå opp og returner celleadressen ved hjelp av ADRESSE -funksjonen
ADRESSE -funksjonen i Excel er ment for akkurat dette.
Den tar raden og kolonnenummeret og gir deg celleadressen til den spesifikke cellen.
Nedenfor er syntaksen til ADRESSE -funksjonen:
= ADRESSE (radnummer, kolumnummer, [abs_nummer], [a1], [arktekst])
hvor:
- row_num: Radnummeret til cellen du vil ha celleadressen for
- column_num: Kolonnenummer for cellen du vil ha adressen for
- [abs_num]: Valgfritt argument hvor du kan angi om cellereferansen skal være absolutt, relativ eller blandet.
- [a1]: Valgfritt argument hvor du kan angi om du vil ha referansen i R1C1 -stilen eller A1 -stilen
- [sheet_text]: Valgfritt argument hvor du kan angi om du vil legge til arknavnet sammen med celleadressen eller ikke
La oss ta et eksempel og se hvordan dette fungerer.
Anta at det er et datasett som vist nedenfor, hvor jeg har ansatt -ID, navn og avdeling, og jeg vil raskt vite celleadressen som inneholder avdelingen for ansatt -ID KR256.
Nedenfor er formelen som gjør dette:
= ADRESSE (MATCH ("KR256", A1: A20,0), 3)
I formelen ovenfor har jeg brukt MATCH -funksjonen for å finne ut radnummeret som inneholder den angitte medarbeider -IDen.
Og siden avdelingen er i kolonne C, har jeg brukt 3 som det andre argumentet.
Denne formelen fungerer bra, men den har en ulempe - den fungerer ikke hvis du legger til raden over datasettet eller en kolonne til venstre for datasettet.
Dette er fordi når jeg angir det andre argumentet (kolonnenummeret) som 3, er det hardkodet og vil ikke endres.
I tilfelle jeg legger til en kolonne til venstre for datasettet, vil formelen telle tre kolonner fra begynnelsen av regnearket og ikke fra begynnelsen av datasettet.
Så hvis du har et fast datasett og trenger en enkel formel, vil dette fungere fint.
Men hvis du trenger dette for å være mer idiotsikkert, kan du bruke det som dekkes i neste avsnitt.
Slå opp og returner celleadressen ved hjelp av CELL -funksjonen
Mens ADRESSE -funksjonen ble laget spesielt for å gi deg cellereferansen til den angitte raden og kolonnummeret, er det en annen funksjon som også gjør dette.
Den kalles CELL -funksjonen (og den kan gi deg mye mer informasjon om cellen enn ADRESSE -funksjonen).
Nedenfor er syntaksen til CELL -funksjonen:
= CELL (info_type, [referanse])
hvor:
- info_type: informasjonen om cellen du vil ha. Dette kan være adressen, kolonnummeret, filnavnet, etc.
- [referanse]: Valgfritt argument hvor du kan spesifisere cellereferansen du trenger celleinformasjonen for.
La oss nå se et eksempel der du kan bruke denne funksjonen til å slå opp og få cellereferansen.
Anta at du har et datasett som vist nedenfor, og du vil raskt vite celleadressen som inneholder avdelingen for ansatt -ID KR256.
Nedenfor er formelen som gjør dette:
= CELL ("adresse", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Formelen ovenfor er ganske grei.
Jeg har brukt INDEX -formelen som det andre argumentet for å få avdelingen for ansatt -ID KR256.
Og så pakket den inn i CELL -funksjonen og ba den returnere celleadressen til denne verdien som jeg får fra INDEX -formelen.
Nå er her hemmelig for hvorfor det fungerer - INDEX -formelen returnerer oppslagsverdien når du gir den alle nødvendige argumenter. Men samtidig ville den også returnere cellereferansen til den resulterende cellen.
I vårt eksempel returnerer INDEX -formelen “Salg” som den resulterende verdien, men samtidig kan du også bruke den til å gi deg cellereferansen til den verdien i stedet for selve verdien.
Normalt, når du angir INDEX -formelen i en celle, returnerer den verdien fordi det er det den forventes å gjøre. Men i scenarier der det er nødvendig med en cellereferanse, vil INDEX -formelen gi deg cellereferansen.
I dette eksemplet er det akkurat det det gjør.
Og det beste med å bruke denne formelen er at den ikke er knyttet til den første cellen i regnearket. Dette betyr at du kan velge et hvilket som helst datasett (som kan være hvor som helst i regnearket), bruke INDEX -formelen for å gjøre et vanlig oppslag, og det vil fortsatt gi deg den riktige adressen.
Og hvis du setter inn en ekstra rad eller kolonne, vil formelen justeres deretter for å gi deg riktig celleadresse.
Så dette er to enkle formler som du kan bruke til å slå opp og finn og returner celleadressen i stedet for verdien i Excel.
Jeg håper du synes denne opplæringen var nyttig.
