Formula generică
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
rezumat
Pentru a număra celulele care nu sunt egale cu oricare dintre multe lucruri, puteți utiliza o formulă bazată pe funcțiile MATCH, ISNA și SUMPRODUCT. În exemplul prezentat, formula din celula F5 este:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
unde „date” este intervalul numit B5: B16 și „exclude” este intervalul numit D5: D7.
Explicaţie
În primul rând, un mic context. În mod normal, dacă aveți doar câteva lucruri pe care nu doriți să le numărați, puteți utiliza COUNTIFS astfel:
=COUNTIFS(range,"apple",range,"orange")
Dar acest lucru nu se extinde foarte bine dacă aveți o listă cu multe lucruri, deoarece va trebui să adăugați o pereche suplimentară de intervale / criterii pentru fiecare lucru pe care nu doriți să îl numărați. Ar fi mult mai ușor să construiți o listă și să transmiteți o referință la această listă ca parte a criteriilor. Exact asta face formula de pe această pagină.
La bază, această formulă folosește funcția MATCH pentru a găsi celule care nu sunt egale cu „a”, „b” sau „c” cu această expresie:
MATCH(data,exclude,0)
Rețineți că valoarea de căutare și matricea de căutare sunt „inversate” din configurația normală - oferim toate valorile din intervalul numit „date” ca valori de căutare și oferim toate valorile pe care dorim să le excludem în intervalul numit „exclude”. Deoarece acordăm MATCH mai mult de o valoare de căutare, obținem mai multe rezultate într-o matrice ca aceasta:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
În esență, MATCH ne oferă poziția de potrivire a valorilor ca număr și returnează # N / A pentru toate celelalte valori.
Rezultatele # N / A sunt cele care ne interesează, deoarece reprezintă valori care nu sunt egale cu „a”, „b” sau „c”. În consecință, folosim ISNA pentru a forța aceste valori la TRUE, iar numerele la FALSE:
ISNA(MATCH(data,exclude,0)
Apoi folosim un negativ dublu pentru a constrânge ADEVĂRAT la 1 și FALS la zero. Matricea rezultată, din interiorul SUMPRODUCT arată astfel:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Cu o singură matrice de procesat, SUMPRODUCT însumează și returnează un rezultat final, 4.
Notă: Utilizarea SUMPRODUCT în loc de SUM evită necesitatea de a utiliza control + shift + enter.
Numără minus meci
Un alt mod de a număra celulele care nu sunt egale cu oricare dintre mai multe lucruri este de a număra toate valorile și de a scădea potrivirile. Puteți face acest lucru cu o formulă de genul acesta:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Aici, COUNTA returnează un număr al tuturor celulelor care nu sunt goale. Funcția COUNTIF, având în vedere intervalul numit „exclude” va returna trei numărătoare, câte unul pentru fiecare element din listă. SUMPRODUCT adună suma totală, iar acest număr este scăzut din numărul tuturor celulelor care nu sunt goale. Rezultatul final este numărul de celule care nu sunt egale cu valorile din „exclude”.
Literalul conține logică de tip
Formula de pe această pagină contează cu logica „egal cu”. Dacă trebuie să numărați celule care nu conțin multe șiruri, unde conține înseamnă că un șir poate apărea oriunde într-o celulă, veți avea nevoie de o formulă mai complexă.
