Excel-formel: Uddrag alle matches med hjælpekolonne -

Indholdsfortegnelse

Generisk formel

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Resumé

En måde at udtrække flere matches i Excel er at bruge INDEX og MATCH med en hjælpekolonne, der markerer matchende data. Dette undgår kompleksiteten af en mere avanceret matrixformel. I det viste eksempel er formlen i H6:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

hvor ct (G3), data (B3: E52) og hjælper (E3: E52) er navngivet områder.

Forklaring

Udfordringen med opslagsformler, der henter mere end et match, er at administrere dubletter (dvs. flere matches). Opslagsformler som VLOOKUP og INDEX + MATCH kan let finde det første match, men det er meget sværere at slå "alle matches" op, når kriterierne finder mere end et match.

Denne formel håndterer denne udfordring ved hjælp af en hjælpekolonne, der returnerer en numerisk værdi, der kan bruges til nemt at udtrække flere matches. Formlen i hjælpekolonnen ser sådan ud:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Hjælpekolonnen tester hver række i dataene for at se, om afdelingen i kolonne C matcher værdien i I3, og bygningen i kolonne D matcher værdien i J3. Begge logiske tests skal returnere SAND, for at AND kan returnere SAND.

For hver række tilføjes resultatet fra AND-funktionen til "værdien over" i hjælpekolonnen for at generere et antal. Den praktiske effekt af denne formel er en inkrementerende tæller, der kun ændres, når der findes et (nyt) match. Derefter forbliver værdien den samme, indtil næste match er fundet. Dette fungerer, fordi de SANDE / FALSE-resultater returneres med AND tvinges til 1/0-værdier som en del af sumoperationen. FALSE resultater tilføjer intet, og SANDE resultater tilføjer 1.

Tilbage i ekstraktionsområdet ser opslagsformlen for Navn i kolonne H sådan ud:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Arbejder indefra og ud, INDEX + MATCH-delen af formlen ser navnet på det første match, der blev fundet, ved hjælp af række nummer i kolonne G som matchværdi:

INDEX(data,MATCH($G6,helper,0),1)

INDEX modtager alle 3 kolonner med data som arrayet (navngivet interval "data"), og MATCH er konfigureret til at matche række nummer inde i hjælpekolonnen (det navngivne område "hjælper") i nøjagtig matchningstilstand (3. argument indstillet til nul) .

Det er her, formlenes kløgtighed bliver tydelig. Hjælpekolonnen indeholder naturligvis duplikater, men det betyder ikke noget, fordi MATCH kun matcher den første værdi. Efter design svarer hver "første værdi" til den rigtige række i datatabellen.

Formlerne i kolonne I og J er de samme som H bortset fra kolonnetallet, som i hvert tilfælde øges med en.

IF-sætningen, der omslutter INDEX / MATCH-formlen, udfører en simpel funktion - den kontrollerer hvert række nummer i ekstraktionsområdet for at se, om række nummer er mindre end eller lig med værdien i G3 (navngivet område "ct"), som er det samlede antal af alle matchende poster. I så fald køres INDEX / MATCH-logikken. Hvis ikke, udsender IF en tom streng ("").

Formlen i G3 (navngivet område "ct") er enkel:

=MAX(helper)

Da den maksimale værdi i hjælpekolonnen er den samme som det samlede antal match, er MAX-funktionen alt, hvad vi har brug for.

Bemærk: Ekstraktionsområdet skal konfigureres manuelt til at håndtere så mange data som nødvendigt (dvs. 5 rækker, 10 rækker, 20 rækker osv.). I dette eksempel er det kun begrænset til 5 rækker for at holde regnearket kompakt.

Jeg lærte denne teknik i Mike Girvins bog Control + Shift + Enter.