Excel vzorec: Extrahujte všechny shody pomocí pomocného sloupce -

Obsah

Obecný vzorec

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

souhrn

Jedním ze způsobů, jak extrahovat více shod v aplikaci Excel, je použít INDEX a MATCH se sloupcem pomocníka, který označuje odpovídající data. Tím se zabrání složitosti pokročilejšího maticového vzorce. V zobrazeném příkladu je vzorec v H6:

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

kde ct (G3), data (B3: E52) a pomocník (E3: E52) jsou pojmenovány rozsahy.

Vysvětlení

Úkolem vyhledávacích vzorců, které načtou více než jednu shodu, je správa duplikátů (tj. Více shod). Vyhledávací vzorce jako VLOOKUP a INDEX + MATCH mohou snadno najít první shodu, ale je mnohem těžší vyhledat „všechny shody“, když kritéria naleznou více než jednu shodu.

Tento vzorec řeší tuto výzvu pomocí pomocného sloupce, který vrací číselnou hodnotu, kterou lze použít k snadné extrakci více shod. Vzorec ve sloupci pomocníka vypadá takto:

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

Pomocný sloupec testuje každý řádek v datech, aby zjistil, zda oddělení ve sloupci C odpovídá hodnotě v I3 a budova ve sloupci D odpovídá hodnotě v J3. Oba logické testy musí vrátit TRUE, aby AND vrátilo TRUE.

Pro každý řádek je výsledek z funkce AND přidán k "hodnotě nad" ve sloupci pomocníka, aby se vygeneroval počet. Praktickým účinkem tohoto vzorce je přírůstkové počítadlo, které se změní, pouze když je nalezena (nová) shoda. Pak hodnota zůstane stejná, dokud nebude nalezena další shoda. Funguje to, protože výsledky TRUE / FALSE se vracejí pomocí AND a jsou vynuceny na hodnoty 1/0 jako součást operace součtu. FALSE výsledky nepřidávají nic a TRUE výsledky přidávají 1.

Zpět v oblasti těžby vypadá vyhledávací vzorec pro Název ve sloupci H takto:

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

Při práci zevnitř ven část vzorce INDEX + MATCH vyhledá název první nalezené shody, přičemž jako hodnotu shody použije číslo řádku ve sloupci G:

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

INDEX přijímá všechny 3 sloupce dat jako pole (pojmenovaný rozsah "data") a MATCH je nakonfigurován tak, aby odpovídal číslu řádku uvnitř pomocného sloupce (pojmenovaný rozsah "pomocník") v režimu přesné shody (3. argument nastaven na nulu) .

To je místo, kde se ukáže chytrost vzorce. Pomocný sloupec samozřejmě obsahuje duplikáty, ale to nevadí, protože MATCH bude odpovídat pouze první hodnotě. Podle návrhu odpovídá každá „první hodnota“ správnému řádku v datové tabulce.

Vzorce ve sloupcích I a J jsou stejné jako H, s výjimkou čísla sloupce, které se v každém případě zvýší o jeden.

Příkaz IF, který obaluje vzorec INDEX / MATCH, provádí jednoduchou funkci - kontroluje každé číslo řádku v oblasti extrakce, aby zjistil, zda je číslo řádku menší nebo rovno hodnotě v G3 (pojmenovaný rozsah „ct“), což je celkový počet všech odpovídajících záznamů. Pokud ano, je spuštěna logika INDEX / MATCH. Pokud ne, IF odešle prázdný řetězec ("").

Vzorec v G3 (pojmenovaný rozsah „ct“) je jednoduchý:

=MAX(helper)

Protože maximální hodnota ve sloupci pomocníka je stejná jako celkový počet shod, je funkce MAX vše, co potřebujeme.

Poznámka: Extrakční oblast musí být ručně nakonfigurována, aby zvládla tolik dat, kolik je potřeba (tj. 5 řádků, 10 řádků, 20 řádků atd.). V tomto příkladu je omezeno pouze na 5 řádků, aby byl list kompaktní.

Tuto techniku jsem se naučil v knize Mika Girvina Control + Shift + Enter.