Obecný vzorec
=FILTER(list1,COUNTIF(list2,list1))
souhrn
Chcete-li porovnat dva seznamy a extrahovat běžné hodnoty, můžete použít vzorec založený na funkcích FILTER a COUNTIF. V zobrazeném příkladu je vzorec v F5:
=FILTER(list1,COUNTIF(list2,list1))
kde list1 (B5: B15) a list2 (D5: D13) jsou pojmenované rozsahy. Výsledek, hodnoty, které se objeví v obou seznamech, se rozlije do rozsahu F5: F11.
Vysvětlení
Funkce FILTER přijímá pole hodnot a argument „include“, který filtruje pole na základě logického výrazu nebo hodnoty.
V tomto případě je pole poskytováno jako pojmenovaný rozsah „list1“, který obsahuje všechny hodnoty v B5: B15. Argument include je doručen funkcí COUNTIF, která je vnořená uvnitř FILTERU:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF je nastaven s list2 jako rozsah a list1 jako kritéria . Protože dáváme COUNTIF jedenáct hodnot kritérií, COUNTIF vrátí jedenáct výsledků v poli, jako je tento:
(1;1;0;1;0;1;0;1;0;1;1)
Všimněte si, že 1 odpovídá položkám v seznamu2, které se objevují v seznamu1.
Toto pole je dodáváno přímo do funkce FILTER jako argument „zahrnout“:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Funkce FILTER filtruje seznam1 pomocí hodnot poskytovaných funkcí COUNTIF. Hodnoty spojené s nulou jsou odstraněny; ostatní hodnoty jsou zachovány.
Konečným výsledkem je pole hodnot, které existují v obou seznamech a které se šíří do rozsahu F5: F11.
Rozšířená logika
Ve výše uvedeném vzorci používáme jako filtr hrubé výsledky z COUNTIF. To funguje, protože Excel vyhodnotí jakoukoli nenulovou hodnotu jako TRUE a nulu jako FALSE. Pokud funkce COUNTIF vrátí počet větší než 1, filtr bude i nadále fungovat správně.
Chcete-li explicitně vynutit výsledky PRAVDA a NEPRAVDA, můžete použít „> 0“ takto:
=FILTER(list1,COUNTIF(list2,list1)>0)
Odeberte duplikáty nebo seřaďte
Chcete-li odstranit duplikáty, stačí vnořit vzorec do funkce UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Chcete-li výsledky seřadit, vnořte je do funkce TŘÍDĚNÍ:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Hodnoty seznamu chybí v seznamu2
Chcete-li vypsat hodnoty v seznamu1 chybí v seznamu2, můžete obrátit logiku takto:
=FILTER(list1,COUNTIF(list2,list1)=0)
