Obecný vzorec
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
souhrn
Chcete-li počítat buňky, které se nerovnají žádné z mnoha věcí, můžete použít vzorec založený na funkcích MATCH, ISNA a SUMPRODUCT. V zobrazeném příkladu je vzorec v buňce F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
kde „data“ je pojmenovaný rozsah B5: B16 a „vyloučit“ je pojmenovaný rozsah D5: D7.
Vysvětlení
Nejprve trochu kontextu. Normálně, pokud máte jen pár věcí, které nechcete počítat, můžete použít COUNTIFS takto:
=COUNTIFS(range,"apple",range,"orange")
Ale to se moc dobře nemění, pokud máte seznam mnoha věcí, protože pro každou věc, kterou nechcete počítat, budete muset přidat další pár rozsah / kritéria. Bylo by mnohem snazší vytvořit seznam a předat odkaz na tento seznam jako součást kritérií. Přesně to dělá vzorec na této stránce.
Tento vzorec v jádru používá funkci MATCH k vyhledání buněk, které se nerovnají „a“, „b“ nebo „c“ s tímto výrazem:
MATCH(data,exclude,0)
Všimněte si, že vyhledávací hodnota a vyhledávací pole jsou „obráceny“ z normální konfigurace - poskytujeme všechny hodnoty z pojmenovaného rozsahu „data“ jako vyhledávací hodnoty a všechny hodnoty, které chceme vyloučit, uvádíme v pojmenovaném rozsahu „vyloučit“. Protože dáme MATCH více než jednu vyhledávací hodnotu, dostaneme více než jeden výsledek v poli, jako je tento:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
V zásadě nám MATCH dává pozici odpovídajících hodnot jako číslo a pro všechny ostatní hodnoty vrací # N / A.
Výsledky # N / A nás zajímají, protože představují hodnoty, které se nerovnají „a“, „b“ nebo „c“. Podle toho používáme ISNA k vynucení těchto hodnot na TRUE a čísla na FALSE:
ISNA(MATCH(data,exclude,0)
Pak použijeme dvojitý zápor k vynucení TRUE na 1 a FALSE na nulu. Výsledné pole uvnitř SUMPRODUCT vypadá takto:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
S pouze jedním polem ke zpracování SUMPRODUCT sečte a vrátí konečný výsledek, 4.
Poznámka: Použitím SUMPRODUCT namísto SUM se vyhnete nutnosti používat control + shift + enter.
Počítat minus shodu
Dalším způsobem, jak počítat buňky, které se nerovnají žádné z několika věcí, je spočítat všechny hodnoty a odečíst shody. Můžete to udělat pomocí vzorce, jako je tento:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
COUNTA zde vrací počet všech neprázdných buněk. Funkce COUNTIF vzhledem k pojmenovanému rozsahu „vyloučit“ vrátí tři počty, jeden pro každou položku v seznamu. SUMPRODUCT sčítá součet a toto číslo se odečte od počtu všech neprázdných buněk. Konečným výsledkem je počet buněk, které se nerovnají hodnotám v poli „vyloučit“.
Literal obsahuje logiku typu
Vzorec na této stránce počítá s logikou „rovná se“. Pokud potřebujete počítat buňky, které neobsahují mnoho řetězců, kde obsahuje znamená, že řetězec se může objevit kdekoli v buňce, budete potřebovat složitější vzorec.
