Obecný vzorec
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
souhrn
Chcete-li počítat buňky, které neobsahují mnoho různých řetězců, můžete použít poměrně složitý vzorec založený na funkci MMULT. V zobrazeném příkladu je vzorec v F5:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
kde „data“ je pojmenovaný rozsah B5: B14 a „vyloučit“ je pojmenovaný rozsah D5: D7.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter
Předmluva
Tento vzorec komplikuje požadavek „obsahuje“. Pokud potřebujete vzorec pro počítání buněk, které se * nerovnají * mnoha věcem, můžete použít přímější vzorec založený na funkci MATCH. Pokud máte omezený počet řetězců, které chcete vyloučit, můžete také použít funkci COUNTIFS takto:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
S tímto přístupem však musíte zadat novou dvojici argumentů rozsahu / kritérií pro každý řetězec, který chcete vyloučit. Naproti tomu vzorec vysvětlený níže dokáže zpracovat velké množství řetězců, které se mají vyloučit zadané přímo v listu.
Nakonec je tento vzorec složitý. Dejte mi vědět, pokud máte k dispozici jednodušší vzorec :)
Vysvětlení
Jádrem tohoto vzorce je ISNUMBER a SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Zde transponujeme položky v pojmenovaném rozsahu „vyloučit“ a poté výsledek poslat do SEARCH jako „najít text“ s „daty“ jako „v textu“. Funkce SEARCH vrací 2D pole TRUE a FALSE hodnot, 10 řádků po 3 sloupcích, jako je tato:
(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)
Pro každou hodnotu v „datech“ máme 3 výsledky (jeden na každý vyhledávací řetězec), což jsou chyby #VALUE nebo čísla. Čísla představují pozici nalezeného textového řetězce a chyby představují textové řetězce, které nebyly nalezeny. Mimochodem, funkce TRANSPOSE je potřebná ke generování pole 10 x 3 úplných výsledků.
Toto pole se přivádí do ISNUMBER, aby se získaly hodnoty PRAVDA NEPRAVDA, které převádíme na 1 s a 0 s dvojitým záporným (-) operátorem. Výsledkem je pole jako 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)
který jde do funkce MMULT jako array1. Podle pravidel násobení matic se počet sloupců v poli1 musí rovnat počtu řádků v poli2. K vygenerování pole2 používáme funkci ROW takto:
ROW(exclude)^0
Tím se získá pole 1 s, 3 řádky o 1 sloupec:
(1;1;1)
který jde do MMULT jako array2 . Po násobení pole máme pole dimenzované tak, aby odpovídalo původním datům:
(2;1;0;0;1;1;0;0;0;2)
V tomto poli jakékoli nenulové číslo představuje hodnotu, kde byl nalezen alespoň jeden z vyloučených řetězců. Nuly označují, že nebyly nalezeny žádné vyloučené řetězce. Chcete-li vynutit všechny nenulové hodnoty na 1, použijeme větší než nulu:
(2;1;0;0;1;1;0;0;0;2)>0
který vytváří ještě další pole nebo TRUE a FALSE hodnoty:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Naším konečným cílem je spočítat pouze textové hodnoty, kde nebyly nalezeny žádné vyloučené řetězce, takže musíme tyto hodnoty obrátit. Děláme to odečtením pole od 1. Toto je příklad logické logiky. Matematická operace automaticky vynucuje TRUE a FALSE hodnoty na 1 s a 0 s a konečně máme pole, které se vrátí k funkci SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funkce SUM vrátí konečný výsledek 5.
