Podczas pracy z danymi i formułami w programie Excel na pewno napotkasz błędy.
Do obsługi błędów Excel udostępnił przydatną funkcję - funkcję JEŻELI.BŁĄD.
Zanim przejdziemy do mechaniki korzystania z funkcji JEŻELI.BŁĄD w programie Excel, przyjrzyjmy się różnym rodzajom błędów, które można napotkać podczas pracy z formułami.
Rodzaje błędów w Excelu
Znajomość błędów w programie Excel pozwoli lepiej określić możliwą przyczynę i najlepszy sposób ich radzenia sobie.
Poniżej znajdują się typy błędów, które możesz znaleźć w programie Excel.
#N/D Błąd
Nazywa się to błędem „Wartość niedostępna”.
Zobaczysz to, gdy użyjesz formuły wyszukiwania i nie możesz znaleźć wartości (stąd Niedostępne).
Poniżej znajduje się przykład, w którym używam formuły WYSZUKAJ.PIONOWO, aby znaleźć cenę elementu, ale zwraca błąd, gdy nie mogę znaleźć tego elementu w tablicy tabeli.
#DZIAŁ/0! Błąd
Prawdopodobnie zobaczysz ten błąd, gdy liczba zostanie podzielona przez 0.
Nazywa się to błędem dzielenia. W poniższym przykładzie daje #DIV/0! błąd, ponieważ wartość ilości (dzielnik we wzorze) wynosi 0.
#WARTOŚĆ! Błąd
Błąd wartości występuje, gdy w formule używasz nieprawidłowego typu danych.
Na przykład w poniższym przykładzie, gdy próbuję dodać komórki, które mają liczby i znak A, daje błąd wartości.
Dzieje się tak, ponieważ możesz dodawać tylko wartości liczbowe, ale zamiast tego próbowałem dodać liczbę ze znakiem tekstowym.
#REF! Błąd
Nazywa się to błędem odniesienia i zobaczysz go, gdy odwołanie w formule nie będzie już ważne. Może tak być w przypadku, gdy formuła odwołuje się do odwołania do komórki, a odwołanie do komórki nie istnieje (dzieje się tak, gdy usuniesz wiersz/kolumnę lub arkusz, do którego odwołuje się formuła).
W poniższym przykładzie, podczas gdy oryginalna formuła była = A2/B2, kiedy usunąłem kolumnę B, wszystkie odniesienia do niej stały się #REF! a także dał #REF! błąd jako wynik formuły.
#NAZWA BŁĘDU
Ten błąd jest prawdopodobnie wynikiem błędnej pisowni funkcji.
Na przykład, jeśli zamiast funkcji WYSZUKAJ.PIONOWO przez pomyłkę użyjesz funkcji WYSZUKAJ.PIONOWO, pojawi się błąd nazwy.
#NUMER BŁĘDU
Błąd Num może wystąpić, jeśli spróbujesz obliczyć bardzo dużą wartość w programie Excel. Na przykład =187^549 zwróci błąd liczbowy.
Inną sytuacją, w której można uzyskać błąd LICZBA, jest podanie niepoprawnej liczby argumentów do formuły. Na przykład, jeśli obliczasz pierwiastek kwadratowy, jeśli liczba i podasz liczbę ujemną jako argument, zwróci błąd liczby.
Na przykład w przypadku funkcji pierwiastka kwadratowego, jeśli jako argument podasz liczbę ujemną, zwróci ona błąd liczby (jak pokazano poniżej).
Chociaż pokazałem tutaj tylko kilka przykładów, może istnieć wiele innych przyczyn, które mogą prowadzić do błędów w programie Excel. Gdy pojawiają się błędy w programie Excel, nie możesz ich tak po prostu zostawić. Jeśli dane są dalej wykorzystywane w obliczeniach, musisz upewnić się, że błędy są obsługiwane we właściwy sposób.
Funkcja Excel IFERROR to świetny sposób na obsługę wszystkich typów błędów w programie Excel.
Funkcja IFERROR programu Excel - omówienie
Za pomocą funkcji JEŻELI.BŁĄD można określić, co formuła ma zwracać zamiast błędu. Jeśli formuła nie zwróci błędu, zwracany jest jej własny wynik.
IFERROR Składnia funkcji
=JEŻELIBŁĄD(wartość; wartość_jeśli_błąd)
Argumenty wejściowe
- wartość - to jest argument, który jest sprawdzany pod kątem błędu. W większości przypadków jest to formuła lub odwołanie do komórki.
- value_if_error - jest to wartość zwracana w przypadku błędu. Oceniane są następujące typy błędów: #N/A, #REF!, #DZIEL/0!, #WARTOŚĆ!, #LICZBA!, #NAZWA? i #NULL!.
Dodatkowe uwagi:
- Jeśli użyjesz „” jako argumentu wartość_jeżeli_błąd, komórka nie wyświetla nic w przypadku błędu.
- Jeśli argument wartość lub wartość_jeśli_błąd odwołuje się do pustej komórki, funkcja JEŻELI.BŁĄD programu Excel jest traktowana jako pusta wartość ciągu.
- Jeśli argument wartość jest formułą tablicową, funkcja JEŻELI.BŁĄD zwróci tablicę wyników dla każdego elementu z zakresu określonego w wartości.
Funkcja JEŻELI.BŁĄD programu Excel - przykłady
Oto trzy przykłady użycia funkcji JEŻELI.BŁĄD w programie Excel.
Przykład 1 - Zwróć pustą komórkę zamiast błędu
Jeśli masz funkcje, które mogą zwracać błąd, możesz umieścić je w funkcji JEŻELI.BŁĄD i określić puste jako wartość do zwrócenia w przypadku błędu.
W poniższym przykładzie wynikiem w D4 jest #DIV/0! błąd, ponieważ dzielnik wynosi 0.
W takim przypadku możesz użyć następującej formuły, aby zwrócić puste zamiast brzydkiego błędu DIV.
=JEŻELIBŁĄD(A1/A2””)
Ta funkcja IFERROR sprawdzi, czy obliczenia prowadzą do błędu. Jeśli tak, po prostu zwraca puste miejsce określone w formule.
Tutaj możesz również określić dowolny inny ciąg lub formułę do wyświetlenia zamiast pustego.
Na przykład poniższa formuła zwróci tekst „Błąd” zamiast pustej komórki.
=JEŻELIBŁĄD(A1/A2;”BŁĄD”)
Uwaga: Jeśli używasz programu Excel 2003 lub wcześniejszej wersji, nie znajdziesz w nim funkcji JEŻELI.BŁĄD. W takich przypadkach należy użyć kombinacji funkcji JEŻELI i funkcji ISERROR.
Przykład 2 - Zwróć „Nie znaleziono”, gdy WYSZUKAJ.PIONOWO nie może znaleźć wartości
Gdy używasz funkcji WYSZUKAJ.PIONOWO programu Excel i nie może ona znaleźć wartości wyszukiwania w określonym zakresie, zwróci błąd #N/D.
Na przykład poniżej znajduje się zestaw danych obejmujący nazwiska uczniów i ich oceny. Użyłem funkcji WYSZUKAJ.PIONOWO, aby pobrać oceny trzech uczniów (w D2, D3 i D4).
Chociaż formuła WYSZUKAJ.PIONOWO w powyższym przykładzie znajduje nazwiska dwóch pierwszych uczniów, nie może znaleźć imienia Josha na liście, a zatem zwraca błąd #N/A.
Tutaj możemy użyć funkcji JEŻELI.BŁĄD w celu zwrócenia pustego lub znaczącego tekstu zamiast błędu.
Poniżej znajduje się formuła, która zwróci „Nie znaleziono” zamiast błędu.
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D2;$A$2:$B$12,2,0);"Nie znaleziono")
Zauważ, że możesz również użyć IFNA zamiast IFERROR z funkcją WYSZUKAJ.PIONOWO. Podczas gdy JEŻELI BŁĄD traktuje wszystkie rodzaje wartości błędów, JEŻELI JEŻELI działa tylko z błędami #N/D i nie działa z innymi błędami.
Przykład 3 - Zwróć 0 w przypadku błędu
Jeśli nie określisz wartości do zwrócenia przez IFERROR w przypadku błędu, automatycznie zwróci 0.
Na przykład, jeśli podzielę 100 przez 0, jak pokazano poniżej, zwróci błąd.
Jeśli jednak użyję poniższej funkcji IFERROR, zamiast tego zwróci 0. Zauważ, że nadal musisz użyć przecinka po pierwszym argumencie.
Przykład 4 - używanie zagnieżdżonego IFERROR z funkcją WYSZUKAJ.PIONOWO
Czasami podczas korzystania z funkcji WYSZUKAJ.PIONOWO konieczne może być przejrzenie pofragmentowanej tabeli tablic. Załóżmy na przykład, że masz rekordy transakcji sprzedaży w 2 oddzielnych arkuszach i chcesz wyszukać numer towaru i zobaczyć jego wartość.
Wykonanie tego wymaga użycia zagnieżdżonego IFERROR z funkcją WYSZUKAJ.PIONOWO.
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
W takim przypadku, aby znaleźć wynik dla Grace, musisz użyć poniższej zagnieżdżonej formuły JEŻELI.BŁĄD:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(G3;$A$2:$B$5,2,0);JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(G3;$D$2:$E$5,2,0);"Nie znaleziono"))
Ten rodzaj zagnieżdżania formuł zapewnia, że uzyskasz wartość z dowolnej tabeli, a każdy zwrócony błąd zostanie obsłużony.
Zwróć uwagę, że w przypadku, gdy tabele znajdują się w tym samym arkuszu, w przykładzie z życia prawdopodobnie będą to różne arkusze.
Funkcja JEŻELI BŁĄD programu Excel - WIDEO
- Excel I funkcja.
- Excel LUB funkcja.
- Excel NIE Funkcja.
- Funkcja JEŻELI Excela.
- Funkcja Excela IFS.
- Funkcja Excel FAŁSZ.
- Funkcja Excel PRAWDA.
