Obecný vzorec
=FILTER(list1,COUNTIF(list2,list1))
souhrn
Chcete-li filtrovat data a extrahovat odpovídající hodnoty ve dvou seznamech, můžete použít funkci FILTER a funkci COUNTIF nebo COUNTIFS. V zobrazeném příkladu je vzorec v F5:
=FILTER(list1,COUNTIF(list2,list1))
kde list1 (B5: B16) a list2 (D5: D14) jsou pojmenované rozsahy. Výsledek vrácený funkcí FILTER zahrnuje pouze hodnoty v seznamu1, které se zobrazí v seznamu2 .
Poznámka: FILTER je nová funkce dynamického pole v aplikaci Excel 365.
Vysvětlení
Tento vzorec spoléhá na funkci FILTER k načtení dat na základě logického testu vytvořeného pomocí funkce COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
při práci zevnitř ven se funkce COUNTIF používá k vytvoření skutečného filtru:
COUNTIF(list2,list1)
Všimněte si, že používáme list2 jako argument rozsahu a list1 jako argument kritérií. Jinými slovy, žádáme COUNTIF, aby spočítal všechny hodnoty v seznamu1, které se objevují v seznamu2. Protože dáváme COUNTIF více hodnot pro kritéria, dostaneme zpět pole s více výsledky:
(1;1;0;1;0;1;0;0;1;0;1;1)
Všimněte si, že pole obsahuje 12 počtů, jeden pro každou hodnotu v seznamu1 . Nulová hodnota označuje hodnotu v seznamu1, která se nenachází v seznamu2 . Jakékoli jiné kladné číslo označuje hodnotu v seznamu1, která se nachází v seznamu2 . Toto pole je vráceno přímo do funkce FILTER jako argument include:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Funkce filtru používá pole jako filtr. Jakákoli hodnota v seznamu1 spojená s nulou je odstraněna, zatímco jakákoli hodnota spojená s kladným číslem přežije.
Výsledkem je pole 7 shodných hodnot, které se šíří do rozsahu F5: F11. Pokud se data změní, FILTER přepočítá a vrátí nový seznam odpovídajících hodnot na základě nových dat.
Neshodné hodnoty
Chcete-li extrahovat neodpovídající hodnoty ze seznamu1 (tj. Hodnoty v seznamu1 , které se nezobrazí v seznamu2 ), můžete do vzorce přidat funkci NOT takto:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funkce NOT efektivně obrátí výsledek z COUNTIF - jakékoli nenulové číslo se stane FALSE a jakákoli nulová hodnota se stane TRUE. Výsledkem je seznam hodnot v seznamu1, které nejsou v seznamu2 .
S INDEX
Je možné vytvořit vzorec pro extrakci shodných hodnot bez funkce FILTER, ale vzorec je složitější. Jednou z možností je použít funkci INDEX ve vzorci, jako je tento:
Zkopírovaný vzorec v G5 je:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Poznámka: Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter, kromě Excel 365.
Jádrem tohoto vzorce je funkce INDEX, která jako argument pole přijímá list1 . Většina zbývajícího vzorce jednoduše vypočítá číslo řádku, které se použije pro shodu hodnot. Tento výraz generuje seznam relativních čísel řádků:
ROW(list1)-ROW(INDEX(list1,1,1))+1
který vrací pole 12 čísel představujících řádky v seznamu1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Ty jsou filtrovány pomocí funkce IF a stejné logiky použité výše v FILTER, založené na funkci COUNTIF:
COUNTIF(list2,list1) // find matching values
Výsledné pole vypadá takto:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Toto pole se doručuje přímo do funkce MALÉ, která se používá k načtení dalšího odpovídajícího čísla řádku při kopírování vzorce dolů do sloupce. Hodnota k pro MALÉ (myslím n-té) se počítá s rozšiřujícím se rozsahem:
ROWS($G$5:G5) // incrementing value for k
Funkce IFERROR se používá k zachycení chyb, ke kterým dochází při kopírování vzorce a vyčerpání odpovídajících hodnot. Další příklad této myšlenky najdete v tomto vzorci.
