Excel

Få nth match med INDEX / MATCH

Get Nth Match With Index Match

Excel -formel: Få nth match med INDEX / MATCHGenerisk formel | _+_ | Resumé

For at hente flere matchende værdier fra et sæt data med en formel kan du bruge IF- og SMALL -funktionerne til at finde ud af rækkenummeret for hver kamp og feed denne værdi tilbage til INDEX. I det viste eksempel er formlen i I7:





{= INDEX (array, SMALL ( IF (vals=val, ROW (vals)- ROW ( INDEX (vals,1,1))+1),nth))}

Hvor navngivne områder er amts (D4: D11), id (I3) og id'er (C4: C11).

Bemærk, at dette er en matrixformel og skal indtastes med Control + Shift + Enter.





Forklaring

I kernen er denne formel simpelthen en INDEX -formel, der henter værdien i et array på en given position. Værdien for n er angivet i kolonne H, og alt det 'tunge' arbejde, formlen gør, er at finde ud af rækken, hvorfra en værdi skal hentes, hvor rækken svarer til 'nth' match.

IF -funktionen udfører arbejdet med at finde ud af, hvilke rækker der indeholder et match, og funktionen SMALL returnerer den nte værdi fra listen. Inde i IF er den logiske test:



 
{= INDEX (amts, SMALL ( IF (ids=id, ROW (ids)- ROW ( INDEX (ids,1,1))+1),H6))}

som giver denne matrix:

hvornår skal man bruge vlookup i Excel
 
ids=id

Bemærk kunde -id -matchene på 1. og 4. position, som vises som SAND. Argumentet 'værdi hvis sandt' i IF genererer en liste over relative rækkenumre med dette udtryk:

 
{TRUEFALSEFALSETRUEFALSEFALSEFALSE}

som producerer denne matrix:

 
 ROW (ids)- ROW ( INDEX (ids,1,1))+1

Denne matrix 'filtreres' derefter af de logiske testresultater, og IF -funktionen returnerer følgende array -resultat:

 
{1234567}

Bemærk, vi har gyldige rækkenumre for række 1 og række 2.

Denne matrix behandles derefter af SMALL, som er konfigureret til at bruge værdier i kolonne H til at returnere 'nth' værdier. SMALL -funktionen ignorerer automatisk de logiske værdier SAND og FALSK i matrixen. I sidste ende reducerer formlerne til:

 
{1FALSEFALSE4FALSEFALSEFALSE}

Håndteringsfejl

Når der ikke er flere matches for et givet id, returnerer SMALL -funktionen en #NUM fejl. Du kan håndtere denne fejl med IFERROR -funktion eller ved at tilføje logik til at tælle tændstikker og afbryde behandlingen, når tallet i kolonne H er større end tællingen. Det eksempel her viser en tilgang.

Flere kriterier

For at tilføje flere kriterier bruger du boolsk logik , som forklaret i dette eksempel .

Forfatter Dave Bruns


^