Slå opp den nærmeste kampen ved å bruke INDEX & MATCH -funksjonen

Anonim

I denne artikkelen vil vi lære hvordan du finner den nærmeste kampen ved hjelp av INDEX & MATCH -funksjonen i Excel.

Scenario:

For eksempel må vi finne den numerisk nærmeste matchen og de tilhørende resultatene fra tabellen. Vi trenger en fast formel som hjelper til med å finne den nærmeste matchen for å få den nødvendige verdien.

Hvordan løse problemet?

For at formelen først skal forstå må vi revidere litt om følgende funksjoner

  1. INDEX -funksjon
  2. MATCH -funksjon

Nå skal vi lage en formel ved å bruke funksjonene ovenfor. MATCH -funksjonen returnerer indeksen for den nærmeste kampen fra området. INDEX -funksjonen tar radindeksen som argument og returnerer de tilsvarende nødvendige resultatene.

Generisk formel:

{= INDEX (data, MATCH (MIN (ABS (data - verdi)), ABS (data - verdi), 0))}

data : rekke datoverdier

verdi : gitt datoværdi der resultatet er nærmest

match_type : 1 (nøyaktig eller nest minste) eller 0 (eksakt match) eller -1 (nøyaktig eller nest største)

IKKE bruk krøllete seler manuelt. Krøllete seler påført med Ctrl + Shift + Enter i stedet for bare Tast inn.

Eksempel:

Uttalelsene ovenfor kan være kompliserte å forstå. Så la oss forstå dette ved å bruke formelen i et eksempel

Her har vi noen tilfeldige datoverdier og en gitt datoverdi.

Navngitt område som brukes for data (D4: D12).
Her brukte vi det navngitte området i stedet for cellereferansearray, ettersom det er lett å forstå. Vi må se etter datoen som er nærmest den oppgitte datoen i G6 -cellen.

Bruk formelen ovenfor for å få den nærmeste datoen.
Bruk formelen:

{ = INDEKS (data, MATCH (MIN (ABS (data - G6)), ABS (data - G6), 0)) }

IKKE bruk krøllete seler manuelt. Krøllete seler påført med Ctrl + Shift + Enter i stedet for bare Tast inn.

Forklaring:

  • ABS -funksjonen returnerer det positive heltallet etter subtraksjonen utført over (data - G6) datoverdier.
  • ABS (data - G6) lager en matrise ved å trekke alle datoverdier fra den gitte datoverdien i G6 -cellen. Den returnerte matrisen er.

{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }

  • MIN (ABS (data - G6)) returnerer den nærmeste verdien fra matrisen som returneres fra trinnet ovenfor.

= INDEKS (data, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))

  • MATCH -funksjonen samsvarer med den nærmeste minsteverdien i den returnerte matrisen og returnerer radindeksen til INDEX -funksjonen.
  • INDEX -funksjonen finner verdien som har returnert ROW -indeksen.


data navngitt område som brukes for datormatrisen. Bruk nå Ctrl + Shift + Enter på plass for den bare Enter -toppen, få resultatet da dette er en matriseformel. Arrayformel er en formel der en rekke verdier går fra ett funksjonsresultat som argument til ytre funksjon.

Som du kan se fra øyeblikksbildet ovenfor, oppnådde vi den nærmeste samsvaringen fra den oppgitte datoen i tabellen. Trekk ut detaljer fra tabellen ved å bruke formelen ovenfor.

Her er noen observasjonsmerker mens du bruker formelen ovenfor.

Merknader:

  1. Funksjonen returnerer #NA -feilen hvis oppslagsarrayargumentet til MATCH -funksjonen ikke har samme lengde som tabellmatrisen.
  2. Formelen returnerer en feil hvis lookup_value ikke samsvarer med verdien i tabellen lookup_array.
  3. Funksjonen samsvarer med eksakt verdi ettersom matchtype -argumentet til MATCH -funksjonen er 0.
  4. Oppslagsverdiene kan gis som cellereferanse eller direkte ved hjelp av sitatsymbol (") i formelen som argumenter.
  5. IKKE bruk krøllete seler manuelt. Krøllete seler påført med Ctrl + Shift + Enter i stedet for bare Tast inn ellers returnerer den feilen.

Håper du har forstått hvordan du søker etter laveste eller minste kamp ved hjelp av INDEX & MATCH -funksjonen i excel. Utforsk flere artikler om Excel -oppslagsverdi og Excel 2019 -funksjoner her. Skriv gjerne dine spørsmål nedenfor i kommentarfeltet. Vi vil sikkert hjelpe deg.

Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post

Bruk INDEX og MATCH til å slå opp verdi : INDEX & MATCH -funksjon for å slå opp verdien etter behov.

SUM -område med INDEX i Excel : Bruk INDEX -funksjonen for å finne SUMMEN av verdiene etter behov.

Slik bruker du INDEX -funksjonen i Excel : Finn INDEX for matrisen ved hjelp av INDEX -funksjonen forklart med eksempel.

Slik bruker du MATCH -funksjonen i Excel : Finn MATCH i matrisen ved å bruke INDEX -verdien inne i MATCH -funksjonen forklart med eksempel.

Hvordan bruke LOOKUP -funksjonen i Excel : Finn oppslagsverdien i matrisen ved å bruke LOOKUP -funksjonen forklart med eksempel.

Slik bruker du VLOOKUP -funksjonen i Excel : Finn oppslagsverdien i matrisen ved å bruke VLOOKUP -funksjonen forklart med eksempel.

Populære artikler

50 Excel -snarvei for å øke produktiviteten

Rediger en rullegardinliste

Absolutt referanse i Excel

Hvis det er betinget formatering

Hvis det er jokertegn

Excel 2019 CONCAT -funksjonen

Excel 2019 MAXIFS -funksjonen