Všeobecný vzorec
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Zhrnutie
Ak chcete spočítať bunky, ktoré neobsahujú veľa rôznych reťazcov, môžete použiť pomerne zložitý vzorec založený na funkcii MMULT. V zobrazenom príklade je vzorec v F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
kde „údaj“ je pomenovaný rozsah B5: B14 a „vylúčenie“ je pomenovaný rozsah D5: D7.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter
Predslov
Tento vzorec komplikuje požiadavka „obsahuje“. Ak potrebujete vzorec na počítanie buniek, ktoré sa * nerovnajú * mnohým veciam, môžete použiť priamočiarejší vzorec založený na funkcii MATCH. Ak máte obmedzený počet reťazcov, ktoré chcete vylúčiť, môžete tiež použiť funkciu COUNTIFS takto:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Pri tomto prístupe však musíte pre každý reťazec, ktorý chcete vylúčiť, zadať novú dvojicu argumentov rozsahu / kritérií. Naproti tomu vzorec vysvetlený nižšie dokáže spracovať veľké množstvo reťazcov, ktoré sa majú vylúčiť zadané priamo v hárku.
Nakoniec je tento vzorec zložitý. Ak máte návrh na zjednodušenie, dajte mi vedieť :)
Vysvetlenie
Jadro tohto vzorca je ISNUMBER a VYHĽADÁVANIE:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Tu transponujeme položky v pomenovanom rozsahu „vylúčiť“ a výsledok potom vložíme do VYHĽADÁVANIA ako „vyhľadať text“ s „údajmi“ ako „v texte“. Funkcia SEARCH vráti 2d pole TRUE a FALSE hodnôt, 10 riadkov v 3 stĺpcoch, takto:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Pre každú hodnotu v stĺpci „data“ máme 3 výsledky (jeden na vyhľadávací reťazec), ktoré sú chybami alebo číslami #VALUE. Čísla predstavujú pozíciu nájdeného textového reťazca a chyby predstavujú textové reťazce, ktoré sa nenašli. Mimochodom, na vygenerovanie poľa 10 x 3 úplných výsledkov je potrebná funkcia TRANSPOSE.
Toto pole sa vkladá do čísla ISNUMBER, aby sa získali TRUE FALSE hodnoty, ktoré prevádzame na 1 s a 0 s dvojitým záporným (-) operátorom. Výsledkom je pole ako je toto:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
ktorý ide do funkcie MMULT ako pole1. Podľa pravidiel násobenia matíc sa počet stĺpcov v poli1 musí rovnať počtu riadkov v poli2. Na vygenerovanie array2 používame funkciu ROW takto:
ROW(exclude)^0
Takto sa získa pole 1 s, 3 riadky o 1 stĺpec:
(1;1;1)
ktorý ide do MMULT ako array2 . Po násobení poľa máme pole dimenzované tak, aby zodpovedalo pôvodným údajom:
(2;1;0;0;1;1;0;0;0;2)
V tomto poli predstavuje akékoľvek nenulové číslo hodnotu, kde sa našiel aspoň jeden z vylúčených reťazcov. Nuly naznačujú, že sa nenašli vylúčené reťazce. Ak chcete vynútiť všetky nenulové hodnoty na 1, použijeme viac ako nulu:
(2;1;0;0;1;1;0;0;0;2)>0
ktorý vytvára ďalšie pole alebo TRUE a FALSE hodnoty:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Naším konečným cieľom je spočítať iba textové hodnoty, kde sa nenašli vylúčené reťazce, takže tieto hodnoty musíme obrátiť. Robíme to tak, že odčítame pole od 1. Toto je príklad logickej logiky. Matematická operácia automaticky vynúti TRUE a FALSE hodnoty na 1 s a 0 s a konečne máme pole, ktoré sa vráti k funkcii SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funkcia SUM vráti konečný výsledok 5.
