JEŻELI.BŁĄD w Excelu: kiedy zastąpić błąd, a kiedy go pokazać

JEŻELI.BŁĄD sprawdza wynik formuły. Jeśli to błąd, zwraca wartość, którą sam wskażę. Jeśli nie, oddaje wynik bez zmian. Dzięki temu raport nie zamienia się w ścianę komunikatów #DZIEL/0! i #N/D!.

Ta wygoda ma swoją cenę. Błąd w Excelu często jest jedyną informacją, że dane są niekompletne albo formuła źle wskazuje zakres. Dlatego zanim owinę formułę w JEŻELI.BŁĄD, zastanawiam się, czy chcę błąd ukryć, czy tylko ładniej go opisać.

Składnia JEŻELI.BŁĄD

=JEŻELI.BŁĄD(wartość; wartość_jeżeli_błąd)

• wartość: formuła albo odwołanie, które sprawdzam.
• wartość_jeżeli_błąd: to, co ma się pojawić zamiast błędu. Może to być tekst, liczba, pusty tekst "" albo inna formuła.

Według Microsoft funkcja reaguje na błędy #N/D!, #ARG!, #ADR!, #DZIEL/0!, #LICZBA!, #NAZWA? i #ZERO!. Pusta komórka w którymkolwiek argumencie jest traktowana jak pusty tekst. W angielskiej wersji Excela funkcja nazywa się IFERROR. Separator argumentów zależy od ustawień regionalnych, więc w angielskich poradnikach zwykle zobaczysz przecinek.

Przykład: cena za sztukę

Mam syntetyczną tabelę zamówień w zakresie A1:C6 i liczę cenę za sztukę w kolumnie D formułą =B2/C2:

W wierszu długopisu dzielę przez zero. W wierszu plecaka ktoś wpisał liczbę słownie, więc dzielenie przez tekst daje #ARG!. Zamiast surowych błędów chcę czytelnej informacji:

=JEŻELI.BŁĄD(B2/C2; "sprawdź dane")

Tę formułę wpisuję w nowej kolumnie E. Kolumnę D z surowym ilorazem zostawiam, bo przyda się do obliczeń poniżej. Dla pierwszych trzech wierszy formuła zwraca cenę, a dla dwóch ostatnich napis „sprawdź dane”. Ten pomysł pochodzi ze starszej wersji tego hasła, w której zamiast błędu pokazywałem komunikat „Błędna ilość”. Komunikat jest dobry, bo nie udaje liczby i od razu mówi, gdzie szukać problemu.

Jaką wartość wpisać zamiast błędu

Wybór drugiego argumentu ma większe znaczenie niż sama funkcja. Kieruję się tym, co ktoś zrobi z wynikiem.

• Komunikat tekstowy, na przykład „sprawdź dane” albo „brak w cenniku”, gdy wynik czyta człowiek i ma wiedzieć, że coś wymaga uwagi.
• Pusty tekst "", gdy brak wyniku jest normalny, a komórka ma po prostu zostać pusta. Pusty tekst nie wejdzie do średniej ani do ILE.LICZB.
• Zero tylko wtedy, gdy zero jest prawdziwą odpowiedzią. Cena za sztukę długopisu wcale nie wynosi zero: tego nie wiemy.

Pułapka: średnia z zerami

Chcę poznać średnią cenę za sztukę z kolumny D, czyli z surowych ilorazów razem z błędami. Zwykła średnia z zakresu z błędami sama zwróci błąd, więc kusi taka formuła:

=ŚREDNIA(JEŻELI.BŁĄD(D2:D6; 0))

Wynik to 25,28, bo do 24,90, 12,50 i 89,00 dochodzą dwa zera, a suma 126,40 dzieli się przez pięć. Prawdziwa średnia trzech poprawnych cen wynosi 42,13. Zastąpienie błędów zerem zaniżyło wynik o około 40%.

Mam dwa poprawne sposoby. Pusty tekst, którego ŚREDNIA nie liczy:

=ŚREDNIA(JEŻELI.BŁĄD(D2:D6; ""))

Albo AGREGUJ, w której funkcja 1 to średnia, a opcja 6 pomija wartości błędów:

=AGREGUJ(1; 6; D2:D6)

Obie formuły zwracają 42,13. Pierwsza w starszych wersjach Excela wymaga zatwierdzenia klawiszami Ctrl+Shift+Enter, bo działa na tablicy. Obok takiego wyniku zawsze podaję, ile wierszy pominąłem. Tutaj dwa z pięciu.

Kiedy nie używam JEŻELI.BŁĄD

Przy wyszukiwaniu wolę JEŻELI.ND. Reaguje tylko na #N/D!, czyli na brak dopasowania. Jeśli w WYSZUKAJ.PIONOWO pomylę numer kolumny i dostanę #ADR!, ten błąd zostanie widoczny. JEŻELI.BŁĄD zamieniłby go na „brak w cenniku” i nikt by się nie zorientował, że formuła jest zepsuta.

W X.WYSZUKAJ w ogóle nie potrzebuję dodatkowej funkcji, bo czwarty argument określa wartość na brak dopasowania:

=X.WYSZUKAJ(F2; A2:A6; D2:D6; "brak produktu")

Nie owijam też w JEŻELI.BŁĄD formuł, które nie powinny się mylić. Błąd #NAZWA? oznacza literówkę w nazwie funkcji, a #ADR! usunięty zakres. Takie błędy chcę zobaczyć od razu, a nie odkryć za miesiąc w raporcie.

Jak policzyć ukryte problemy

Jeśli już zastępuję błędy, liczę je obok. CZY.BŁĄD zwraca PRAWDA dla każdej komórki z błędem, a SUMA.ILOCZYNÓW sumuje te wartości po zamianie na liczby:

=SUMA.ILOCZYNÓW(--CZY.BŁĄD(D2:D6))

Wynik to 2. Taka liczba w nagłówku raportu mówi odbiorcy więcej niż dwie estetyczne kreski w tabeli.

Krótkie ćwiczenie

Przepisz tabelę zamówień do arkusza. Policz cenę za sztukę w kolumnie D zwykłym ilorazem, a obok w kolumnie E wersję z JEŻELI.BŁĄD i komunikatem. Potem policz średnią z kolumny D na trzy sposoby: z zerami, z pustym tekstem i przez AGREGUJ. Porównaj wyniki. Na koniec zmień „dwa” na liczbę 2 i sprawdź, jak zmieniła się średnia oraz licznik błędów.