Všeobecný vzorec
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Zhrnutie
Ak chcete počítať bunky, ktoré sa nerovnajú s mnohými vecami, môžete použiť vzorec založený na funkciách MATCH, ISNA a SUMPRODUCT. V zobrazenom príklade je vzorec v bunke F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
kde „údaj“ je pomenovaný rozsah B5: B16 a „vylúčenie“ je pomenovaný rozsah D5: D7.
Vysvetlenie
Najprv trochu kontextu. Normálne, ak máte iba pár vecí, ktoré nechcete počítať, môžete použiť COUNTIFS takto:
=COUNTIFS(range,"apple",range,"orange")
Ale to sa veľmi dobre nemení, ak máte zoznam mnohých vecí, pretože pre každú vec, ktorú nechcete počítať, budete musieť pridať ďalší pár rozsah / kritérium. Bolo by oveľa jednoduchšie zostaviť zoznam a uviesť odkaz na tento zoznam ako súčasť kritérií. Presne to robí vzorec na tejto stránke.
Tento vzorec v jadre používa funkciu MATCH na nájdenie buniek, ktoré sa nerovnajú „a“, „b“ alebo „c“ s týmto výrazom:
MATCH(data,exclude,0)
Všimnite si, že vyhľadávacia hodnota a vyhľadávacie pole sú „obrátené“ z bežnej konfigurácie - všetky hodnoty z pomenovaného rozsahu „data“ poskytujeme ako vyhľadávané hodnoty a všetky hodnoty, ktoré chceme vylúčiť, uvedieme v pomenovanom rozsahu „vylúčiť“. Pretože dáme MATCH viac ako jednu vyhľadávaciu hodnotu, dostaneme viac ako jeden výsledok v poli ako je tento:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
MATCH nám v zásade poskytuje pozíciu zodpovedajúcich hodnôt ako číslo a pre všetky ostatné hodnoty vráti # N / A.
# N / A výsledky sú tie, ktoré nás zaujímajú, pretože predstavujú hodnoty, ktoré sa nerovnajú „a“, „b“ alebo „c“. Podľa toho používame ISNA na vynútenie týchto hodnôt na TRUE a čísel na FALSE:
ISNA(MATCH(data,exclude,0)
Potom použijeme dvojitý zápor na vynútenie TRUE na 1 a FALSE na nulu. Výsledné pole vo vnútri SUMPRODUCT vyzerá takto:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
S iba jedným poľom na spracovanie, SUMPRODUCT sčíta a vráti konečný výsledok, 4.
Poznámka: Použitím SUMPRODUCT namiesto SUM sa vyhnete nutnosti používať Control + Shift + Enter.
Počet mínusových zhôd
Ďalším spôsobom, ako počítať bunky, ktoré sa nerovnajú niektorej z niekoľkých vecí, je spočítať všetky hodnoty a odčítať zhody. Môžete to urobiť pomocou tohto vzorca:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
COUNTA tu vráti počet všetkých neprázdnych buniek. Funkcia COUNTIF vzhľadom na pomenovaný rozsah „vylúčiť“ vráti tri počty, jedno pre každú položku v zozname. SUMPRODUCT sčíta súčet a toto číslo sa odčíta od počtu všetkých neprázdnych buniek. Konečným výsledkom je počet buniek, ktoré sa nerovnajú hodnotám v poli „vylúčiť“.
Literál obsahuje logiku typu
Vzorec na tejto stránke počíta s logikou „rovná sa“. Ak potrebujete počítať bunky, ktoré neobsahujú veľa reťazcov, kde obsahuje znamená, že sa reťazec môže zobraziť kdekoľvek v bunke, budete potrebovať zložitejší vzorec.
