Formuła ogólna
=SUMPRODUCT(--NOT(ISERROR(range)))
Podsumowanie
Aby policzyć liczbę komórek, które nie zawierają błędów, możesz użyć funkcji CZY.BŁĄD i NIE, opakowanych w funkcję SUMPRODUCT. W pokazanym przykładzie formuła w E5 to:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Wyjaśnienie
W tym przykładzie celem jest policzenie liczby komórek w zakresie, które nie zawierają błędów.
Pracując od wewnątrz, najpierw używamy funkcji ISERROR na całym zakresie:
ISERROR(B5:B14) // check all 10 cells
Ponieważ w zakresie B5: B14 znajduje się dziesięć komórek, CZY.BŁĄD zwraca tablicę z dziesięcioma wynikami w następujący sposób:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tutaj każda wartość PRAWDA wskazuje wartość komórki, która jest błędem. Ponieważ celem jest zliczanie komórek, które nie zawierają błędów, odwracamy te wyniki za pomocą funkcji NIE:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
która zwraca:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Zauważ, że każda wartość PRAWDA odpowiada teraz komórce, która nie zawiera błędu. Ta tablica ma teraz poprawny format - wartości PRAWDA wskazują komórki bez błędów, wartości FAŁSZ wskazują komórki z błędami.
Ponieważ SUMPRODUCT działa tylko z danymi liczbowymi, następnym krokiem jest przekonwertowanie wartości PRAWDA i FAŁSZ na ich odpowiedniki numeryczne, 1 i 0. Robimy to z podwójnym ujemnym (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Wynikowa tablica wygląda następująco:
(1;0;1;1;1;0;1;1;1;0)
Na koniec SUMPRODUCT sumuje elementy w tej tablicy i zwraca sumę, która w przykładzie jest liczbą 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Funkcja ISERR
Podobnie jak funkcja ISERROR, funkcja ISERR zwraca wartość TRUE, gdy wartość jest błędem. Różnica polega na tym, że ISERR ignoruje błędy # N / A. Jeśli chcesz policzyć komórki, które nie zawierają błędów, i zignorować błędy # N / A, możesz zastąpić ISERR ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
SUMA
Możesz także użyć funkcji SUMA do zliczania błędów. Struktura wzoru jest taka sama:
=SUM(--NOT(ISERROR(B5:B14)))
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
