I denne artikel lærer vi, hvordan du finder værdier i 2d-tabel ved hjælp af en INDEX-MATCH-MATCH-funktion i Excel.
Scenarie:
Antag, at du skal foretage flere opslag fra en tabel, der har hundredvis af kolonner. I sådanne tilfælde vil det tage for lang tid at bruge forskellige formler til hvert opslag. Hvad med at oprette en dynamisk opslagsformel, som du kan slå op med det medfølgende overskrift. Ja, vi kan dette. Denne formel kaldes INDEX MATCH MATCH -formel, eller sig en 2d -opslagsformel.
Hvordan løses problemet?
For at formlen først skal forstå, skal vi revidere lidt om følgende funktioner
- INDEX -funktion
- MATCH -funktion
INDEX -funktionen returnerer værdien ved et givet indeks i en matrix.
MATCH -funktionen returnerer indekset for det første udseende af værdien i en matrix (enkeltdimension -array).
Nu laver vi en formel ved hjælp af ovenstående funktioner. Match -funktionen returnerer indekset for opslagsværdien1 i rækkeoverskriftsfeltet. Og en anden MATCH -funktion returnerer indekset for opslagsværdien2 i kolonneoverskriftsfeltet. Indeksnumrene indføres nu i INDEX -funktionen for at få værdierne under opslagsværdien fra 2D -tabeldataene.
Generisk formel:
= INDEX (data, MATCH (opslag_værdi1, rækkehoveder, 0, MATCH (opslag_værdi2, kolonneoverskrifter, 0)))
Data: række værdier inde i tabellen uden overskrifter
opslagsværdi1 : værdi til opslag i row_header.
række_overskrifter : Rækkeindeks -array for at slå op.
opslagsværdi1 : værdi til opslag i column_header.
column_headers : kolonne Indeks array til opslag.
Eksempel:
Ovenstående udsagn kan være komplicerede at forstå. Så lad os forstå dette ved at bruge formlen i et eksempel
Her har vi en liste over scoringer opnået af elever med deres emneliste. Vi skal finde scoren for en bestemt studerende (Gary) og et emne (samfundsfag) som vist i øjebliksbilledet herunder.
Elevværdien1 skal matche rækken Header header, og Subject value2 skal matche Column_header arrayet.
Brug formlen i J6 -cellen:
= INDEX (tabel, MATCH (J5, række, 0, MATCH (J4, kolonne, 0)))
Forklaring:
- MATCH -funktionen matcher Elev -værdien i J4 -cellen med rækkehovedsætningen og returnerer sin position 3 som et tal.
- MATCH -funktionen matcher emneværdien i J5 -cellen med kolonneoverskriftsarrayet og returnerer sin position 4 som et tal.
- INDEX -funktionen tager række- og kolonneindeksnummeret og kigger op i tabeldataene og returnerer den matchede værdi.
- Argumentet MATCH type er fastsat til 0. Da formlen vil udtrække det nøjagtige match.
Her er værdier til formlen angivet som cellereferencer og row_header, tabel og column_header givet som navngivne områder.
Som du kan se i ovenstående snapshot, fik vi den score, som en elev opnåede Gary i Emne Sociale Studier som 36.
Det beviser, at formlen fungerer fint, og for tvivl se nedenstående noter for at forstå.
Nu vil vi bruge den omtrentlige match med rækkeoverskrifter og kolonneoverskrifter som tal. Ca. match tager kun talværdierne, da det ikke er muligt for tekstværdier
Her har vi en pris på værdier i henhold til produktets højde og bredde. Vi skal finde prisen for en specifik højde (34) og bredde (21) som vist i øjebliksbilledet herunder.
Højdeværdien1 skal matche rækken Rækkehoved og Breddeværdi2 skal matche kolonnehovedets matrix.
Brug formlen i K6 -cellen:
= INDEX (data, MATCH (K4, Højde, 1, MATCH (K5, Bredde, 1)))
Forklaring:
- MATCH -funktionen matcher værdien Højde i K4 -cellen med rækkehovedsætningen og returnerer sin position 3 som et tal.
- MATCH -funktionen matcher breddeværdien i K5 -cellen med kolonneoverskriftsarrayet og returnerer sin position 2 som et tal.
- INDEX -funktionen tager række- og kolonneindeksnummeret og kigger op i tabeldataene og returnerer den matchede værdi.
- Argumentet MATCH type er fastsat til 1. Da formlen udtrækker den omtrentlige matchning.
Her er værdier til formlen angivet som cellereferencer og row_header, data og column_header givet som navngivne områder som nævnt i snapshotet ovenfor.
Som du kan se i ovenstående snapshot, har vi prisen opnået efter højde (34) & Bredde (21) som 53.10. Det beviser, at formlen fungerer fint, og for tvivl se noterne herunder for mere forståelse.
Bemærkninger:
- Funktionen returnerer #NA -fejlen, hvis opslagsarrayargumentet til MATCH -funktionen er 2 D -array, der er dataoverskriftsfeltet …
- Funktionen matcher den nøjagtige værdi, da matchtype -argumentet til MATCH -funktionen er 0.
- Opslagsværdierne kan angives som cellereference eller direkte ved hjælp af citatsymbol (") i formlen som argumenter.
Håber du forstod, hvordan du bruger opslag i 2 D -tabellen ved hjælp af INDEX & MATCH -funktionen i Excel. Udforsk flere artikler i Excel -opslagsværdi her. Du er velkommen til at angive dine forespørgsler herunder i kommentarfeltet. Vi vil helt sikkert hjælpe dig.
Brug INDEX og MATCH til at slå op på værdi : INDEX & MATCH -funktion for at slå værdien op efter behov.
SUM -område med INDEX i Excel : Brug INDEX -funktionen til at finde SUMMEN af værdierne efter behov.
Sådan bruges SUM -funktionen i Excel : Find SUM af tal ved hjælp af SUM -funktionen forklaret med eksempel.
Sådan bruges INDEX -funktionen i Excel : Find matrixens INDEX ved hjælp af INDEX -funktionen forklaret med eksempel.
Sådan bruges MATCH -funktionen i Excel : Find MATCH i arrayet ved hjælp af INDEX -værdien inde i MATCH -funktionen forklaret med eksempel.
Sådan bruges LOOKUP -funktion i Excel : Find opslagsværdien i arrayet ved hjælp af LOOKUP -funktionen forklaret med eksempel.
Sådan bruges VLOOKUP -funktionen i Excel : Find opslagsværdien i arrayet ved hjælp af VLOOKUP -funktionen forklaret med eksempel.
Sådan bruges funktionen HLOOKUP i Excel : Find opslagsværdien i arrayet ved hjælp af HLOOKUP -funktionen forklaret med eksempel.
Populære artikler
50 Excel -genvej til at øge din produktivitet
Rediger en rulleliste
Absolut reference i Excel
Hvis med betinget formatering
Hvis det er med jokertegn
Vlookup efter dato
Deltag i for- og efternavn i excel
