I denne artikel lærer vi, hvordan du finder det nærmeste match ved hjælp af INDEX & MATCH -funktionen i Excel.
Scenarie:
For eksempel skal vi finde det numerisk tætteste match og dets tilsvarende resultater fra tabellen. Vi har brug for en fast formel, som hjælper med at finde det nærmeste match for at få den nødvendige værdi.
Hvordan løses problemet?
For at formlen først skal forstå, skal vi revidere lidt om følgende funktioner
- INDEX -funktion
- MATCH -funktion
Nu laver vi en formel ved hjælp af ovenstående funktioner. MATCH -funktionen returnerer indekset for den nærmeste match fra intervallet. INDEX -funktionen tager rækkeindekset som argument og returnerer de tilsvarende krævede resultater.
Generisk formel:
{= INDEX (data, MATCH (MIN (ABS (data - værdi)), ABS (data - værdi), 0))}
data : matrix af datoværdier
værdi : givet datoværdi, hvor resultatet er tættest på
match_type : 1 (nøjagtig eller næstmindst) eller 0 (nøjagtig match) eller -1 (nøjagtig eller næststørst)
LADE VÆRE MED brug krøllede seler manuelt. Krøllet seler påført ved hjælp af Ctrl + Shift + Enter i stedet for bare Gå ind.
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 nogle tilfældige datoværdier og en given datoværdi.
Navngivet område brugt til data (D4: D12).
Her brugte vi det navngivne område i stedet for cellereferencearray, da det er let at forstå. Vi skal lede efter den dato, der er tættest på den givne dato i G6 -celle.
Brug formlen ovenfor for at få den nærmeste dato.
Brug formlen:
{ = INDEX (data, MATCH (MIN (ABS (data - G6)), ABS (data - G6), 0)) }
IKKE brug krøllede seler manuelt. Krøllet seler påført ved hjælp af Ctrl + Shift + Enter i stedet for bare Gå ind.
Forklaring:
- ABS -funktion returnerer det positive heltal efter subtraktionen udført over (data - G6) datoværdier.
- ABS (data - G6) laver en matrix ved at trække alle datoværdier fra den givne datoværdi i G6 -celle. Det returnerede array er.
{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN (ABS (data - G6)) returnerer den nærmeste værdi fra arrayet, der returneres fra ovenstående trin.
= INDEX (data, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))
- MATCH -funktion matcher det nærmeste minimumværdi -match i det returnerede array og returnerer dets rækkeindeks til INDEX -funktionen.
- INDEX -funktionen finder værdien, der har returneret ROW -indeks.
data navngivet område, der bruges til datormatrisen. Brug nu Ctrl + Shift + Enter på plads til den bare Enter top få resultatet, da dette er en matrixformel. Arrayformel er en formel, hvor en række værdier går fra et funktionsresultat som argument til ydre funktion.
Som du kan se fra ovenstående snapshot, opnåede vi det nærmeste match fra den givne dato i tabellen. Uddrag detaljer fra tabellen ved hjælp af ovenstående angivne formel.
Her er nogle observationsnoterer, mens du bruger ovenstående formel.
Bemærkninger:
- Funktionen returnerer #NA -fejlen, hvis opslagsarrayargumentet til MATCH -funktionen ikke er af samme længde som tabel arrayet.
- Formlen returnerer en fejl, hvis lookup_value ikke matcher værdien i tabellen lookup_array.
- 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.
- IKKE brug krøllede seler manuelt. Krøllet seler påført ved hjælp af Ctrl + Shift + Enter i stedet for bare Gå ind ellers returnerer den fejl.
Håber du forstod, hvordan du søger laveste eller mindste match ved hjælp af INDEX & MATCH -funktionen i excel. Udforsk flere artikler om Excel -opslagsværdi og Excel 2019 -funktioner her. Du er velkommen til at angive dine forespørgsler herunder i kommentarfeltet. Vi vil helt sikkert hjælpe dig.
Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet
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 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.
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
Excel 2019 CONCAT -funktionen
Excel 2019 MAXIFS -funktionen