Przechwytywanie błędów za pomocą IFERROR

Funkcja IFERROR jest uniwersalnym zabezpieczeniem. Sprawdza, czy formuła powoduje dowolny błąd, a jeśli tak, wyświetla wybraną przez Państwa przyjazną wartość.

Dzięki temu arkusz wygląda przejrzyście i profesjonalnie. Zamiast niepokojących kodów, takich jak #DIV/0!, rozsianych po raporcie, widzą Państwo pomocny tekst, na przykład pustą wartość lub myślnik.

W tej lekcji poznają Państwo składnię IFERROR oraz sposób stosowania tej funkcji w rzeczywistych formułach.

IFERROR przyjmuje dokładnie dwa argumenty:

• value formuła lub obliczenie, które należy wykonać
• value_if_error wartość wyświetlana, jeśli formuła zwróci błąd

Wzorzec ma postać =IFERROR(your_formula, fallback). Arkusz najpierw wykonuje formułę. Jeśli zadziała, wyświetlany jest rzeczywisty wynik. Jeśli zwróci błąd, zamiast niego wyświetlana jest wartość zastępcza.

Przypomnijmy, że dzielenie przez pustą komórkę powoduje błąd #DIV/0!. Umieszczenie działania dzielenia w funkcji IFERROR pozwala poprawić sposób jego wyświetlania.

Jeśli komórka B2 zawiera zero lub jest pusta, formuła zwróci tutaj 0 zamiast błędu. Jeśli B2 zawiera prawidłową liczbę, zostanie wykonane zwykłe dzielenie.

Można także zwrócić pustą wartość, używając jako wartości zastępczej dwóch znaków cudzysłowu: "".

IFERROR doskonale sprawdza się w przypadku wyszukiwania. Funkcja VLOOKUP, która nie znajdzie dopasowania, zwraca #N/A, co może być mylące dla odbiorców.

Umieszczając funkcję wyszukiwania wewnątrz IFERROR, można zastąpić ten kod jasnym komunikatem, takim jak "Not found". Dzięki temu każda osoba czytająca arkusz od razu rozumie wynik.

Ta pojedyncza technika znacznie ułatwia zaufanie do raportów opartych głównie na wyszukiwaniu oraz ich udostępnianie.

Wartość zastępcza nie musi być zwykłym tekstem. Może nią być inna formuła, wykonywana w przypadku niepowodzenia pierwszej.

Jeśli na przykład podstawowe wyszukiwanie nie zwróci wyniku, można użyć wyszukiwania zastępczego w innej tabeli. Arkusz najpierw próbuje wykonać pierwsze wyszukiwanie, a dopiero w przypadku błędu uruchamia drugie.

Pozwala to płynnie łączyć kolejne próby bez wyświetlania po drodze nieczytelnych błędów.

Ważną cechą IFERROR jest to, że funkcja przechwytuje każdy typ błędu. Nie ma znaczenia, czy formuła zwróci #DIV/0!, #N/A, #VALUE! czy #REF! — dla każdego z nich zostanie wyświetlona wartość zastępcza.

Jest to przydatne, ale stanowi też ostrzeżenie. Ponieważ IFERROR ukrywa wszystkie błędy, może zamaskować rzeczywiste problemy, o których lepiej byłoby wiedzieć.

Jeśli chcą Państwo ukryć wyłącznie brakujące wyniki wyszukiwania, bezpieczniejszym wyborem jest IFNA, którą poznają Państwo w następnej lekcji.

Załóżmy, że obliczają Państwo procent wzrostu jako różnicę między bieżącym a poprzednim okresem, podzieloną przez wartość z poprzedniego okresu. Jeśli wartość z poprzedniego okresu wynosi zero, dla nowych produktów pojawia się błąd #DIV/0!.

Umieszczenie obliczenia w funkcji IFERROR i użycie tekstu "New" jako wartości zastępczej zamienia te błędy w znaczącą etykietę. Produkty istniejące od pewnego czasu pokazują rzeczywisty procent, a zupełnie nowe produkty — New.

Raport jest teraz przejrzysty od początku do końca.

Ponieważ IFERROR działa tak szeroko, należy zachować ostrożność przy jej stosowaniu. Jeśli obejmą Państwo funkcją IFERROR całą złożoną formułę, błąd #VALUE! spowodowany nieprawidłowymi danymi może zostać po cichu zastąpiony.

Można wtedy zaufać liczbie, która w rzeczywistości jest błędna. Najlepszą praktyką jest objęcie funkcją konkretnej części, w której najprawdopodobniej wystąpi błąd, a nie całego obliczenia.

Należy używać IFERROR świadomie, a podczas testowania tymczasowo ją usunąć, aby potwierdzić, że formuła rzeczywiście działa.

Popularnym rozwiązaniem stylistycznym jest zwracanie pustego ciągu znaków w przypadku braku danych, aby komórka po prostu wyglądała na pustą.

Użycie "" jako wartości zastępczej pomaga zachować przejrzystość wykresów i sum, ponieważ większość funkcji traktuje tekst wyglądający na pusty jako niewidoczną zawartość.

Należy jednak pamiętać, że komórka zawierająca "" jest technicznie tekstem, a nie naprawdę pustą komórką, co może wpływać na działanie funkcji COUNT lub wykresów. W przypadku sum zwykle bezpieczniej jest zwrócić 0.

IFERROR należy użyć wtedy, gdy potrzebują Państwo jednego, prostego zabezpieczenia dla formuły i mają pewność, że każdy możliwy błąd jest oczekiwany oraz nieszkodliwy.

Dobrymi przykładami są współczynniki obliczane przez dzielenie, opcjonalne wyszukiwanie oraz obliczenia wzrostu dla nowych elementów.

Należy unikać tej funkcji, gdy trzeba wykrywać nieoczekiwane błędy lub gdy powinien być obsługiwany tylko jeden konkretny typ błędu. W przypadku wyszukiwania większą kontrolę zapewnia IFNA, którą poznają Państwo w dalszej części.

Jedną funkcję IFERROR można umieścić wewnątrz innej, aby kolejno wypróbować kilka wartości zastępczych. Arkusz próbuje wykonać pierwszą formułę, następnie drugą, a na końcu zwraca wartość domyślną.

W tym przypadku najpierw przeszukiwana jest główna tabela, potem tabela zapasowa, a jeśli w obu nie zostanie znaleziony wynik, zwracany jest tekst "Not found". Każda kolejna warstwa jest uruchamiana tylko wtedy, gdy poprzednia formuła zwróci błąd.

Warto ograniczyć zagnieżdżanie do dwóch lub najwyżej trzech poziomów, ponieważ w przeciwnym razie formuła staje się trudna do odczytania i utrzymania.

Sprawdźmy, jak dobrze rozumieją już Państwo działanie IFERROR.

Poznali już Państwo uniwersalny mechanizm obsługi błędów:

• =IFERROR(value, value_if_error) próbuje wykonać formułę i wyświetla wartość zastępczą, jeśli formuła zwróci błąd.
• Funkcja przechwytuje każdy typ błędu, dlatego należy używać jej świadomie.
• Wartością zastępczą może być tekst, liczba, pusta wartość "" albo nawet inna formuła.
• Należy obejmować funkcją ryzykowną część, a nie całe obliczenie, aby rzeczywiste problemy pozostały widoczne.

Następnie poznają Państwo IFNA, która obsługuje wyłącznie błąd wyszukiwania #N/A.