Általános képlet
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Összegzés
A sok különböző karakterláncot nem tartalmazó cellák megszámolásához meglehetősen összetett képletet használhat az MMULT függvény alapján. A bemutatott példában az F5 képlete a következő:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
ahol az "adatok" a B5: B14 elnevezett tartomány, az "kizárás" pedig a D5: D7 nevű tartomány.
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni
Előszó
Ezt a képletet bonyolítja a "tartalmaz" követelmény. Ha csak képletre van szükség a sok dologgal nem egyenlő cellák számlálásához, használhat egy egyszerűbb képletet a MATCH függvény alapján. Továbbá, ha korlátozott számú karakterláncot kell kizárnia, akkor a COUNTIFS függvényt használhatja a következőképpen:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Ezzel a megközelítéssel azonban minden kizárandó karakterlánchoz meg kell adnia egy új tartomány / kritérium argumentumot. Ezzel szemben az alábbiakban ismertetett képlet nagyszámú, közvetlenül a munkalapra beírt sztringet képes kezelni.
Végül ez a képlet összetett. Mondja meg, hogy van-e egyszerűbb képlete a javaslatra :)
Magyarázat
Ennek a képletnek a lényege a SZÁM és a KERESÉS:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Itt átültetjük a "kizárás" nevű tartományba tartozó elemeket, majd az eredményt a SEARCH-ra "keresőszövegként", az "adatok" pedig "szövegen belül" betápláljuk. A KERESÉS függvény egy 2D-s TRUE és FALSE érték tömböt ad vissza, 10 sorral, 3 oszloppal, így:
(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)
Az "adatok" minden egyes értékéhez 3 találatunk van (keresési karakterlánconként egy), amelyek #VALUE hibák vagy számok. A számok a megtalált szöveges karakterlánc helyzetét, a hibák pedig a nem található szöveges karakterláncokat jelentik. Egyébként a TRANSPOSE funkcióra van szükség a teljes eredmények 10 x 3 tömbjének előállításához.
Ezt a tömböt betoldják az ISNUMBER-be, hogy IGAZ HAMIS értékeket kapjanak, amelyeket kettős negatív (-) operátorral konvertálunk 1s-re és 0-ra. Az eredmény egy ilyen tömb:
(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)
amely az MMULT függvénybe tömbként1 kerül. A mátrixszorzás szabályait követve az 1. tömb oszlopainak számának meg kell egyeznie a 2. tömb sorainak számával. A 2. tömb előállításához a ROW függvényt használjuk így:
ROW(exclude)^0
Ez 1s tömböt eredményez, 3 sor 1 oszloppal:
(1;1;1)
ami tömb2- ként megy az MMULT-ba . A tömb szorzás után egy tömböt méretezünk, hogy megfeleljen az eredeti adatoknak:
(2;1;0;0;1;1;0;0;0;2)
Ebben a tömbben bármely nem nulla szám olyan értéket képvisel, ahol a kizárt karakterláncok közül legalább az egyik megtalálható. A nullák azt jelzik, hogy nem található kizárt karakterlánc. Az összes nem nulla érték 1-re kényszerítéséhez nullánál nagyobb értéket használunk:
(2;1;0;0;1;1;0;0;0;2)>0
ami még egy tömböt, vagy TRUE és FALSE értékeket hoz létre:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Végső célunk, hogy csak olyan szöveges értékeket számoljunk, ahol nem találhatók kizárt karakterláncok, ezért ezeket az értékeket meg kell fordítanunk. Ezt úgy tesszük, hogy kivonjuk a tömböt az 1-ből. Ez egy példa a logikai logikára. A matematikai művelet az IGAZ és HAMIS értékeket automatikusan 1 és 0 értékre kényszeríti, és végre van egy tömbünk, amely visszatér a SUM függvényhez:
=SUM((0;0;1;1;0;0;1;1;1;0))
A SUM függvény 5-ös végeredményt ad vissza.
