VLOOKUP, HLOOKUP og INDEX-MATCH er berømte og almindelige måder at slå en værdi op i Excel-tabellen. Men det er ikke de eneste måder at søge værdier i Excel på. I denne artikel lærer vi, hvordan du bruger OFFSET -funktionen sammen med MATCH -funktionen i Excel. Ja du læste det rigtigt, vi vil bruge den berygtede OFFSET -funktion i Excel til at slå en bestemt værdi op i en Excel -tabel.
Generisk formel,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Læs dette omhyggeligt:
StartCell:Dette er startcellen i opslagstabellen. Lad os sige, at hvis du vil slå op i område A2: A10, så er StartCellen A1.
RowLookupValue: Dette er den opslagsværdi, du vil finde i rækker underStartCell.
RowLookupRange:Dette er det område, hvor du vil slå RowLookupValue op. Det er området nedenfor StartCell (A2: A10).
ColLookupVærdi: Dette er den opslagsværdi, du vil finde i kolonner (overskrifter).
ColLookupRange:Dette er det område, hvor du vil slå ColLookupValue op. Det er området på højre side af StartCell (som B1: D1).
Så nok af teorien. Lad os komme i gang med et eksempel.
Eksempel: Opslagssalg ved hjælp af OFFSET og MATCH -funktion Fra Excel -tabel
Her har vi et eksempel på en tabel med salg udført af forskellige medarbejdere i forskellige måneder.
Nu beder vores chef os om at give salget af Id 1004 i juni måned. Der er mange måder at gøre det på, men da vi lærer om OFFSET-MATCH-formlen, vil vi bruge dem til at slå den ønskede værdi op.
Vi har allerede den generiske formel ovenfor. Vi skal bare identificere disse variabler i denne tabel.
StartCell er B1 her. RowLookupValue er M1. RowLookupRange er B2: B1o (område under startcellen).
ColLookupValue er i M2 -celle. ColLookupRange er C1: I1 (Header Range til højre for StartCellen).
Vi har identificeret alle variablerne. Lad os sætte dem ind i en Excel -formel.
Skriv denne formel i Cell M3, og tryk på enter -knappen.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Når du trykker på enter -knappen, får du hurtigt resultatet. Salget udført ID 1004 i juni måned er 177.
Hvordan virker det?
OFFSET -funktionens arbejde er at bevæge sig væk fra den givne startcelle til den givne række og kolonner og derefter returnere værdien fra den celle. For eksempel, hvis jeg skriver OFFSET (B1,1,1), går OFFSET -funktionen til celle C2 (1 celle ned, 1 celle til højre) og returnerer værdien.
Her har vi formelOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Den første MATCH -funktion returnerer indekset for M1 (1004) i område B2: B10, som er 4. Nu er formlen,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Næste MATCH -funktion returnerer indekset for M2 ('Jun') i område C1: I1, som i 7. Nu er formlenOFFSET (B1,4,7).
Nu flytter OFFSET -funktionen blot 4 celler ned til cellen B1, som tager den til B5. Derefter flytter OFFSET -funktionen til 7 til venstre til B5, hvilket tager den til I5. Det er det. OFFSET -funktionen returnerer værdi fra celle I5, som 177.
Fordele ved denne opslagsteknik?
Dette er hurtigt. Den eneste fordel ved denne metode er, at den er hurtigere end de andre metoder. Det samme kan gøres ved hjælp af INDEX-MATCH-funktionen eller VLOOKUP-funktionen. Men det er godt at kende nogle alternativer. Det kan komme godt med en dag.
Så ja fyre, sådan kan du bruge OFFSET og MATCH -funktionen til at LOOKUP -værdier i Excel -tabeller. Jeg håber, det var forklarende nok. Hvis du er i tvivl om denne artikel eller et andet Excel/VBA -relateret emne, så spørg mig i kommentarfeltet herunder.
Brug INDEX og MATCH til at slå op på værdi:INDEX-MATCH-formlen bruges til at søge dynamisk og præcist en værdi i en given tabel. Dette er et alternativ til VLOOKUP -funktionen, og det overvinder manglerne i VLOOKUP -funktionen.
Sum ved OFFSET -grupper i rækker og kolonner: OFFSET -funktionen kan bruges til at summere gruppe af celler dynamisk. Disse grupper kan være overalt i arket.
Sådan hentes hver niende værdi i et område i Excel: Ved hjælp af OFFSET -funktionen i Excel kan vi hente værdier fra skiftende rækker eller kolonner. Denne formel tager hjælp af ROW -funktionen til at skifte mellem rækker og COLUMN -funktion til at skifte i kolonner.
Sådan bruges OFFSET -funktionen i Excel: OFFSET -funktionen er en kraftfuld Excel -funktion, der undervurderes af mange brugere. Men eksperter kender kraften i OFFSET -funktionen, og hvordan kan vi bruge denne funktion til at udføre nogle magiske opgaver i Excel -formlen. Her er det grundlæggende i OFFSET -funktionen.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges Excel VLOOKUP -funktion| Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges Excel COUNTIF -funktion| Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. COUNTIF -funktionen er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.