Excel

INDEX og MATCH med flere kriterier

Index Match With Multiple Criteria

Excel-formel: INDEX og MATCH med flere kriterierGenerisk formel
{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}
Resumé

For at slå værdier op med INDEX og MATCH ved hjælp af flere kriterier kan du bruge en matrixformel. I det viste eksempel er formlen i H8:

 
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

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



Forklaring

Dette er en mere avanceret formel. For grundlæggende, se Sådan bruges INDEX og MATCH .



hvordan man gør e in excel

Normalt er en INDEX MATCH-formel konfigureret med MATCH, der er indstillet til at se gennem et kolonneområde og give et match baseret på givne kriterier. Uden sammenkædning af værdier i a hjælper kolonne eller i selve formlen er der ingen måde at levere mere end et kriterium på.

Denne formel arbejder omkring denne begrænsning ved hjælp af boolsk logik at oprette en array af ener og nuller til at repræsentere rækker, der matcher alle 3 kriterier, og derefter bruge MATCH til at matche den første 1 fundet. Den midlertidige matrix af ens og nuller genereres med dette uddrag:



 
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

Her sammenligner vi varen i H5 med alle varer, størrelsen i H6 mod alle størrelser og farven i H7 mod alle farver. Det oprindelige resultat er tre arrays med SAND / FALSK resultater som denne:

 
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

Tip: Brug F9 til at se disse resultater . Vælg blot et udtryk i formellinjen, og tryk på F9.

Matematikoperationen (multiplikation) transformerer de SANDE FALSKE værdier til 1s og 0s:



hvordan man beregner vægtede scorer i excel
 
{1110001}*{0010010}*{1010001}

Efter multiplikation har vi et enkelt array som dette:

 
{0010000}

som føres ind i MATCH-funktionen som opslagsarray, med en opslagsværdi på 1:

 
 MATCH (1,{0010000})

På dette tidspunkt er formlen en standard INDEX MATCH-formel. MATCH-funktionen returnerer 3 til INDEX:

 
= INDEX (E5:E11,3)

og INDEX returnerer et slutresultat på $ 17,00.

Array visualisering

Arrangementerne forklaret ovenfor kan være vanskelige at visualisere. Billedet nedenfor viser den grundlæggende idé. Kolonner B, C og D svarer til dataene i eksemplet. Kolonne F oprettes ved at multiplicere de tre kolonner sammen. Det er arrayet afleveret til MATCH.

INDEX og MATCH med flere kriterier - array visualisering

Ikke-array version

Det er muligt at tilføje en anden INDEX til denne formel, undgå behovet for at indtaste som en matrixformel med kontrol + shift + enter:

 
= INDEX (rng1, MATCH (1, INDEX ((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))

INDEX-funktionen kan håndtere arrays indbygget, så det andet INDEX tilføjes kun for at 'fange' det array, der er oprettet med den boolske logiske operation, og returnere det samme array igen til MATCH. For at gøre dette er INDEX konfigureret med nul rækker og en kolonne. Nul række trick får INDEX til at returnere kolonne 1 fra arrayet (som alligevel allerede er en kolonne).

Hvorfor vil du have den ikke-array-version? Nogle gange glemmer folk at indtaste en matrixformel med kontrol + shift + enter, og formlen returnerer et forkert resultat. Så en ikke-matrixformel er mere 'skudsikker'. Afvejningen er dog en mere kompleks formel.

Bemærk: I Excel 365 , er det ikke nødvendigt at indtaste matrixformler på en særlig måde.

hvordan man beregner procentvis ændring i excel
Vedhæftede filer Fil INDEX og MATCH med flere kriterier. Xlsx Forfatter Dave Bruns


^