Excel

Sådan bruges INDEX og MATCH

How Use Index Match

INDEX og MATCH er det mest populære værktøj i Excel til at udføre mere avancerede opslag. Det skyldes, at INDEX og MATCH er utrolig fleksible-du kan foretage vandrette og lodrette opslag, 2-vejs opslag, venstre opslag, store bogstaver, og endda opslag baseret på flere kriterier. Hvis du vil forbedre dine Excel -færdigheder, bør INDEX og MATCH være på din liste.





Denne artikel forklarer i enkle vendinger, hvordan du bruger INDEX og MATCH sammen til at udføre opslag. Det tager en trin-for-trin tilgang, først forklarer INDEX, derefter MATCH, og viser dig derefter, hvordan du kombinerer de to funktioner sammen for at oprette et dynamisk tovejsopslag. Der er mere avancerede eksempler længere nede på siden.

INDEX -funktion | MATCH funktion | INDEX og MATCH | 2-vejs opslag | Venstre opslag | Sagsfølsom | Tætteste kamp | Flere kriterier | Flere eksempler





INDEX -funktionen

INDEX -funktionen i Excel er fantastisk fleksibel og kraftfuld, og du finder den i et stort antal Excel -formler, især avancerede formler. Men hvad gør INDEX egentlig? I en nøddeskal henter INDEX værdien på et givet sted i et område. Lad os f.eks. Sige, at du har en tabel med planeter i vores solsystem (se nedenfor), og du vil have navnet på den 4. planet, Mars, med en formel. Du kan bruge INDEX sådan:

 
= INDEX (B3:B11,4)

Brug INDEX til at få navnet på den 4. planet
INDEX returnerer værdien i 4. række i intervallet.



Video: Sådan ser du tingene op med INDEX

Hvad hvis du vil have Mars -diameteren med INDEX? I så fald kan vi levere både et rækkenummer og et kolonnenummer og give et større område. INDEX -formlen nedenfor anvender hele dataområdet i B3: D11 med et rækkenummer 4 og kolonnenummer 2:

 
= INDEX (B3:D11,4,2)

Brug INDEX til at få diameteren på den 4. planet
INDEX henter værdien i række 4, kolonne 2.

For at opsummere får INDEX en værdi på et givet sted i et celleområde baseret på numerisk position. Når området er endimensionalt, skal du kun angive et rækkenummer. Når området er todimensionalt, skal du angive både række- og kolonnenummer.

På dette tidspunkt tænker du måske 'Så hvad? Hvor ofte ved du egentlig placeringen af ​​noget i et regneark? '

Lige præcis. Vi har brug for en måde at lokalisere placeringen af ​​de ting, vi leder efter.

Indtast MATCH -funktionen.

MATCH -funktionen

MATCH -funktionen er designet til ét formål: Find positionen af ​​et element i et område. For eksempel kan vi bruge MATCH til at få placeringen af ​​ordet 'fersken' i denne liste over frugter som denne:

 
= MATCH ('peach',B3:B9,0)

Brug MATCH til at finde position i et lodret område
MATCH returnerer 3, da 'fersken' er det tredje element. MATCH er ikke store og små bogstaver.

MATCH er ligeglad med, om et område er vandret eller lodret, som du kan se nedenfor:

 
= MATCH ('peach',C4:I4,0)

Brug MATCH til at finde position i et vandret område
Samme resultat med et vandret område, MATCH returnerer 3.

Video: Sådan bruges MATCH til nøjagtige kampe

Vigtigt: Det sidste argument i MATCH -funktionen er matchtype. Matchtype er vigtig og styrer, om matchning er nøjagtig eller omtrentlig. I mange tilfælde vil du bruge nul (0) til at tvinge nøjagtig match adfærd. Match type er som standard 1, hvilket betyder omtrentlig match, så det er vigtigt at angive en værdi. Se MATCH side for flere detaljer.

INDEX og MATCH sammen

Nu hvor vi har dækket det grundlæggende i INDEX og MATCH, hvordan kombinerer vi de to funktioner i en enkelt formel? Overvej nedenstående data, en tabel, der viser en liste over sælgere og månedlige salgstal i tre måneder: Januar, februar og marts.

Salg efter sælger efter måned

Lad os sige, at vi vil skrive en formel, der returnerer salgsnummeret for februar for en given sælger. Fra diskussionen ovenfor ved vi, at vi kan give INDEX en række og et kolonnenummer for at hente en værdi. For eksempel for at returnere februar -salgsnummeret for Frantz giver vi området C3: E11 med en række 5 og kolonne 2:

 
= INDEX (C3:E11,5,2) // returns 94

Men vi vil naturligvis ikke hardcode -numre. I stedet ønsker vi en dynamisk kig op.

Hvordan vil vi gøre det? MATCH -funktionen selvfølgelig. MATCH fungerer perfekt til at finde de stillinger, vi har brug for. Arbejde et trin ad gangen, lad os forlade kolonnen hårdkodet som 2 og gøre rækkenummer dynamisk. Her er den reviderede formel, med MATCH -funktionen indlejret inde i INDEX i stedet for 5:

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

Når vi tager tingene et skridt videre, bruger vi værdien fra H2 i MATCH:

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

INDEX og MATCH for at finde februar -salg for ethvert navn
MATCH finder 'Frantz' og returnerer 5 til INDEX for række.

At opsummere:

  1. INDEX har brug for numeriske positioner.
  2. MATCH finder disse positioner.
  3. MATCH er indlejret inde i INDEX.

Lad os nu tackle kolonnenummeret.

To-vejs opslag med INDEX og MATCH

Ovenfor brugte vi funktionen MATCH til at finde rækkenummer dynamisk, men hardkodede kolonnetallet. Hvordan kan vi gøre formlen fuldt dynamisk, så vi kan returnere salg for en given sælger i en given måned? Tricket er at bruge MATCH to gange - én gang for at få en rækkeposition, og én gang for at få en kolonneposition.

Fra eksemplerne ovenfor ved vi, at MATCH fungerer fint med både vandrette og lodrette arrays. Det betyder, at vi let kan finde positionen for en given måned med MATCH. For eksempel returnerer denne formel positionen i marts, som er 3:

 
= MATCH ('Mar',C2:E2,0) // returns 3

Men vi vil selvfølgelig ikke hardcode nogen værdier, så lad os opdatere regnearket for at tillade indtastning af et månedsnavn, og brug MATCH til at finde det kolonnenummer, vi har brug for. Skærmen herunder viser resultatet:

Dynamisk opslag med INDEX og MATCH
Et fuldt dynamisk, tovejs opslag med INDEX og MATCH.

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

Den første MATCH -formel returnerer 5 til INDEX som rækkenummer, den anden MATCH -formel returnerer 3 til INDEX som kolonnenummer. Når MATCH kører, forenkles formlen til:

 
= INDEX (C3:E11,5,3)

og INDEX returnerer korrekt $ 10.525, salgsnummeret for Frantz i marts.

Bemærk: du kan bruge Data validering for at oprette rullemenuer til valg af sælger og måned.

Video: Sådan laver du et tovejsopslag med INDEX og MATCH

Video: Sådan debugger du en formel med F9 (for at se MATCH -returværdier)

Venstre opslag

En af de vigtigste fordele ved INDEX og MATCH i forhold til VLOOKUP -funktionen er evnen til at udføre et 'venstre opslag'. Kort sagt betyder dette bare et opslag, hvor ID -kolonnen er til ret af de værdier, du vil hente, som det ses i eksemplet herunder:

Venstre opslag med INDEX og MATCH

Læs en detaljeret forklaring her .

Kuffertfølsom opslag

I sig selv er MATCH-funktionen ikke store og små bogstaver. Du bruger dog PRÆCIS funktion med INDEX og MATCH for at udføre et opslag, der respekterer store og små bogstaver, som vist nedenfor:

Kuffertfølsom opslag med INDEX og MATCH

Læs en detaljeret forklaring her .

Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365 .

Tætteste kamp

Et andet eksempel, der viser fleksibiliteten i INDEX og MATCH, er problemet med at finde nærmeste match . I eksemplet herunder bruger vi MIN funktion sammen med ABS funktion til skab en opslagsværdi og et opslagsarray inde MATCH -funktionen. I det væsentlige bruger vi MATCH til at finde den mindste forskel. Derefter bruger vi INDEX til at hente den tilhørende tur fra kolonne B.

Find det nærmeste match med INDEX og MATCH

Læs en detaljeret forklaring her .

Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365 .

Opslag med flere kriterier

Et af de vanskeligste problemer i Excel er et opslag baseret på flere kriterier. Med andre ord et opslag, der matcher på mere end en kolonne på samme tid. I eksemplet herunder bruger vi INDEX og MATCH og boolsk logik der matcher på 3 kolonner: Vare, farve og størrelse:

INDEX og MATCH med flere kriterier

Læs en detaljeret forklaring her .

Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365 .

hvordan man tilføjer en procentdel i excel

Flere eksempler på INDEX + MATCH

Her er nogle mere grundlæggende eksempler på INDEX og MATCH i aktion, hver med en detaljeret forklaring:

Forfatter Dave Bruns Vedhæftede filer Fil exceljet -indeks og match.xlsx


^