Omtrentlig tovejs match med flere kriterier
I denne tutorial vil vi tale om, hvordan man leder efter et omtrentligt match baseret på flere kriterier, der er angivet både i kolonner og rækker i et Excel-regneark, med hjælp of INDEX, MATCHog IF funktioner.
Hvordan udfører man en to-vejs omtrentlig match med flere kriterier i Excel?
For at finde ud af tøj størrelse en kvinde der er 165.5cm høj og vejer 55kg ifølge tabellen ovenfor, kan du tilføje to MATCH-funktioner til formlen: den ene med en HVIS-funktion får rækkenummeret (køn og højde), den anden får kolonnenummeret (vægt). Så vil INDEX finde den tilsvarende størrelse i henhold til koordinaterne. (Bemærk, at rækkenummeret går foran kolonnenummeret i en INDEX-formel.)
Generisk syntaks
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast.
- returområde: Det område, hvor du ønsker, at kombinationsformlen skal returnere størrelsen fra. Her refererer til størrelsesområdet.
- opslagsværdi: Værdien, som formlen brugte til at finde positionen for den tilsvarende størrelse. Her refereres til informationen om det givne køn, højde og vægt.
- opslag_array: Celleområdet med de værdier, der skal sammenlignes med opslagsværdi. Her refererer til køn, højde og vægtintervaller.
- match_type: 1 eller -1.
1 eller udeladt (standard), finder MATCH den største værdi, der er mindre end eller lig med opslagsværdi. Værdierne i opslag_array skal placeres i stigende rækkefølge.
-1, MATCH finder den mindste værdi, der er større end eller lig med opslagsværdi. Værdierne i opslag_array skal placeres i faldende rækkefølge.
For at finde ud af tøj størrelse for kvinde der er 165.5cm høj og vejer 55kg, kopier eller indtast formlen nedenfor i cellen M9, og tryk Ctrl + Flytte + Indtast for at få resultatet:
= INDEX (D5:J16,MATCH(M6,HVIS(B5: B16=M5,C5: C16),1),MATCH(M7,D4:J4, 1))
Forklaring af formlen
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): HVIS-funktionen tjekker hver værdi ind B5: B16 hvis de matcher værdien i cellen M5, Kvinde. Hvis det er tilfældet, vil IF-funktionen beholde de tilsvarende højder opført i C5: C16; Hvis ikke, vender IF tilbage FALSK. Formlen vil returnere et array som dette: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- MATCH(M6,IF(B5:B16=M5,C5:C16), 1) = MATCH(M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 1): match_type 1 tvinger MATCH-funktionen til at returnere positionen for den største værdi, der er mindre end eller lig med 165.5 (værdien i cellen M6) i arrayet. Så MATCH vender tilbage 4, værdiens position 165.
- MATCH(M7;D4:J4,1;XNUMX): MATCH-funktionen vender tilbage 3, da den største værdi, der er mindre end eller lig med værdien i M7, 55, er ved 3rækkens tredje position D4:J4.
- INDEKS(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16), 1),MATCH(M7;D4:J4,1;XNUMX)) = INDEX (D5:J16,4,3): INDEX -funktionen henter værdien ved skæringspunktet mellem 4række og 3rd kolonne i størrelsesområdet D5:J16, som er værdien i cellen F8, M.
Relaterede funktioner
Excel INDEX -funktionen returnerer den viste værdi baseret på en given position fra et område eller en matrix.
Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.
IF-funktionen er en af de enkleste og mest nyttige funktioner i Excel-projektmappe. Den udfører en simpel logisk test, som afhænger af sammenligningsresultatet, og den returnerer en værdi, hvis et resultat er SAND, eller en anden værdi, hvis resultatet er FALSK.
Relaterede formler
To-vejs opslag med INDEX og MATCH
For at søge efter noget på tværs af både rækker og kolonner i Excel, eller vi siger at slå en værdi op i skæringspunktet mellem den specifikke række og kolonne, kan vi bruge hjælpen fra INDEX og MATCH funktioner.
Hvis du vil lede efter det nærmeste match for en opslagsværdi i et numerisk datasæt i Excel, kan du bruge funktionerne INDEX, MATCH, ABS og MIN sammen.
Opslag nærmeste matchværdi med flere kriterier
I nogle tilfælde skal du muligvis søge den nærmeste eller omtrentlige matchværdi baseret på mere end ét kriterium. Med kombinationen af INDEX-, MATCH- og IF -funktioner kan du hurtigt få det gjort i Excel.
Opslag med flere kriterier med INDEX og MATCH
Når man behandler en stor database i et Excel -regneark med flere kolonner og rækketekster, er det altid svært at finde noget, der opfylder flere kriterier. I dette tilfælde kan du bruge en matrixformel med INDEX- og MATCH -funktionerne.
Omtrentlig match med INDEX og MATCH
Der er tidspunkter, hvor vi skal finde omtrentlige kampe i Excel for at evaluere medarbejdernes præstationer, bedømme elevernes score, beregne porto baseret på vægt osv. I denne vejledning vil vi tale om, hvordan man bruger INDEX- og MATCH -funktionerne til at hente resultater, vi har brug for.
De bedste Office-produktivitetsværktøjer
Kutools til Excel - Hjælper dig med at skille dig ud fra mængden
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)
- Et sekund til at skifte mellem snesevis af åbne dokumenter!
- Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
- Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
- Giver effektive faner til Office (inkluderer Excel), ligesom Chrome, Edge og Firefox.
