Formula generică
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
rezumat
Pentru a număra celulele care nu conțin multe șiruri diferite, puteți utiliza o formulă destul de complexă bazată pe funcția MMULT. În exemplul prezentat, formula din F5 este:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
unde „date” este intervalul numit B5: B14, iar „exclude” este intervalul numit D5: D7.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter
Prefaţă
Această formulă este complicată de cerința „conține”. Dacă aveți nevoie doar de o formulă pentru a număra celulele care nu * egalează * multe lucruri, puteți utiliza o formulă mai simplă bazată pe funcția MATCH. De asemenea, dacă aveți un număr limitat de șiruri de exclus, puteți utiliza funcția COUNTIFS astfel:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Cu toate acestea, cu această abordare, trebuie să introduceți o nouă pereche de argumente pentru intervale / criterii pentru fiecare șir de exclus. În schimb, formula explicată mai jos poate gestiona un număr mare de șiruri pentru a exclude introduse direct pe foaia de lucru.
În cele din urmă, această formulă este complexă. Spuneți-mi dacă aveți o formulă mai simplă de propus :)
Explicaţie
Nucleul acestei formule este ISNUMBER și SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Aici, transpunem elementele din intervalul numit „exclude”, apoi trimitem rezultatul la CĂUTARE ca „căutare text”, cu „date” ca „în interiorul textului”. Funcția CĂUTARE returnează o matrice 2d de valori ADEVĂRATE și FALSE, 10 rânduri pe 3 coloane, astfel:
(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)
Pentru fiecare valoare din „date”, avem 3 rezultate (unul pe fiecare șir de căutare) care sunt fie erori #VALUE, fie numere. Numerele reprezintă poziția unui șir de text găsit, iar erorile reprezintă șiruri de text care nu au fost găsite. Apropo, funcția TRANSPOSE este necesară pentru a genera o serie de rezultate complete de 10 x 3.
Această matrice este introdusă în ISNUMBER pentru a obține valori FALSE ADEVĂRATE, pe care le convertim în 1s și 0s cu un operator dublu negativ (-). Rezultatul este o matrice ca aceasta:
(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)
care intră în funcția MMULT ca array1. Urmând regulile înmulțirii matricei, numărul de coloane din matrice1 trebuie să fie egal cu numărul de rânduri din matrice2. Pentru a genera array2 , folosim funcția ROW astfel:
ROW(exclude)^0
Aceasta produce o matrice de 1s, 3 rânduri pe 1 coloană:
(1;1;1)
care intră în MMULT ca array2 . După multiplicarea matricei, avem o matrice dimensionată pentru a se potrivi cu datele originale:
(2;1;0;0;1;1;0;0;0;2)
În această matrice, orice număr diferit de zero reprezintă o valoare în care a fost găsit cel puțin unul dintre șirurile excluse. Zero indică faptul că nu au fost găsite șiruri de caractere excluse. Pentru a forța toate valorile diferite de zero la 1, folosim mai mare decât zero:
(2;1;0;0;1;1;0;0;0;2)>0
care creează încă o matrice sau valori ADEVĂRATE și FALSE:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Scopul nostru final este de a număra doar valorile textului în care nu au fost găsite șiruri excluse, deci trebuie să inversăm aceste valori. Facem acest lucru scăzând matricea din 1. Acesta este un exemplu de logică booleană. Operația matematică constrânge automat valorile TRUE și FALSE la 1s și 0s și, în sfârșit, avem o matrice pentru a reveni la funcția SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funcția SUM returnează un rezultat final de 5.
