VLOOKUP, HLOOKUP og INDEX-MATCH er kjente og vanlige måter å slå opp verdier i Excel-tabellen. Men dette er ikke de eneste måtene å slå opp verdier i Excel. I denne artikkelen lærer vi hvordan du bruker OFFSET -funksjonen sammen med MATCH -funksjonen i Excel. Ja du leste det riktig, vi vil bruke den beryktede OFFSET -funksjonen i Excel for å slå opp en bestemt verdi i en Excel -tabell.
Generisk formel,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Les dette nøye:
StartCell:Dette er startcellen i oppslagstabellen. La oss si at hvis du vil slå opp i område A2: A10, vil StartCell være A1.
RowLookupValue: Dette er oppslagsverdien du vil finne i rader underStartCell.
RowLookupRange:Dette er området der du vil slå opp RowLookupValue. Det er området nedenfor StartCell (A2: A10).
ColLookupValue: Dette er oppslagsverdien du vil finne i kolonner (overskrifter).
ColLookupRange:Dette er området du vil slå opp i ColLookupValue. Det er området på høyre side av StartCell (som B1: D1).
Så nok av teorien. La oss komme i gang med et eksempel.
Eksempel: Slå opp salg ved å bruke OFFSET og MATCH -funksjonen fra Excel -tabellen
Her har vi et eksempel på salget som er gjort av forskjellige ansatte i forskjellige måneder.
Nå ber sjefen vår oss om å gi salget gjort med Id 1004 i juni -måneden. Det er mange måter å gjøre det på, men siden vi lærer om OFFSET-MATCH-formelen, vil vi bruke dem til å slå opp ønsket verdi.
Vi har allerede den generiske formelen ovenfor. Vi trenger bare å identifisere disse variablene i denne tabellen.
StartCell er B1 her. RowLookupValue er M1. RowLookupRange er B2: B1o (område under startsellen).
ColLookupValue er i M2 -celle. ColLookupRange er C1: I1 (Header Range til høyre for StartCellen).
Vi har identifisert alle variablene. La oss sette dem inn i en Excel -formel.
Skriv denne formelen i Cell M3 og trykk enter -knappen.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Når du trykker på enter -knappen, får du resultatet umiddelbart. Salget gjort ID 1004 i juni måned er 177.
Hvordan virker det?
OFFSET -funksjonens arbeid er å bevege seg bort fra den gitte startcellen til den gitte raden og kolonnene og deretter returnere verdien fra den cellen. For eksempel, hvis jeg skriver OFFSET (B1,1,1), vil OFFSET -funksjonen gå til celle C2 (1 celle ned, 1 celle til høyre) og returnere verdien.
Her har vi formelOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Den første MATCH -funksjonen returnerer indeksen til M1 (1004) i område B2: B10, som er 4. Nå er formelen,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Neste MATCH -funksjon returnerer indeksen til M2 ('Jun') i område C1: I1, som i 7. Nå er formelenOFFSET (B1,4,7).
Nå flytter OFFSET -funksjonen bare 4 celler ned til cellen B1 som tar den til B5. Deretter flytter OFFSET -funksjonen til 7 venstre til B5, som tar den til I5. Det er det. OFFSET -funksjonen returnerer verdien fra celle I5 som 177.
Fordeler med denne oppslagsteknikken?
Dette er raskt. Den eneste fordelen med denne metoden er at den er raskere enn de andre metodene. Det samme kan gjøres med INDEX-MATCH-funksjonen eller VLOOKUP-funksjonen. Men det er godt å vite noen alternativer. Det kan komme godt med en dag.
Så ja gutta, dette er hvordan du kan bruke OFFSET og MATCH -funksjonen til å SØKE opp verdier i Excel -tabeller. Jeg håper det var forklarende nok. Hvis du er i tvil om denne artikkelen eller et annet Excel/VBA -relatert emne, kan du spørre meg i kommentarfeltet nedenfor.
Bruk INDEX og MATCH til å slå opp verdi:INDEX-MATCH-formelen brukes til å slå dynamisk og presist opp en verdi i en gitt tabell. Dette er et alternativ til VLOOKUP -funksjonen, og den overvinner manglene i VLOOKUP -funksjonen.
Sum etter OFFSET -grupper i rader og kolonner: OFFSET -funksjonen kan brukes til å summere gruppe celler dynamisk. Disse gruppene kan være hvor som helst i arket.
Hvordan hente hver niende verdi i et område i Excel: Ved å bruke OFFSET -funksjonen i Excel kan vi hente verdier fra alternerende rader eller kolonner. Denne formelen tar hjelp av ROW -funksjonen til å veksle mellom rader og COLUMN -funksjonen til å veksle i kolonner.
Slik bruker du OFFSET -funksjonen i Excel: OFFSET -funksjonen er en kraftig Excel -funksjon som er undervurdert av mange brukere. Men eksperter kjenner kraften til OFFSET -funksjonen og hvordan kan vi bruke denne funksjonen til å utføre noen magiske oppgaver i Excel -formelen. Her er det grunnleggende om OFFSET -funksjonen.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du Excel VLOOKUP -funksjon| Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du Excel COUNTIF -funksjon| Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. COUNTIF -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.