W tym samouczku pokazano, jak używać funkcji IFERROR programu Excel do przechwytywania błędów formuły, zastępując je inną formułą, pustą wartością, 0 lub wiadomością niestandardową.
Omówienie funkcji JEŻELI.BŁĄD
Funkcja JEŻELI.BŁĄD Sprawdza, czy formuła nie powoduje błędu. Jeśli FALSE, zwróć oryginalny wynik formuły. Jeśli TRUE, zwróć inną określoną wartość.
IFERROR Składnia
Aby użyć funkcji arkusza kalkulacyjnego IFERROR Excel, zaznacz komórkę i wpisz:=JEŻELIBŁĄD(
Zwróć uwagę, jak pojawiają się dane wejściowe formuły JEŻELI.BŁĄD:
IFERROR Składnia i wejścia funkcji:
| 1 | =JEŻELIBŁĄD(WARTOŚĆ;wartość_jeśli_błąd) |
wartość - Ekspresja. Przykład: 4/A1
value_if_error - Wartość lub Obliczenie do wykonania, jeśli poprzednie dane wejściowe spowodowały błąd. Przykład 0 lub „” (puste)
Co to jest funkcja IFERROR?
Funkcja JEŻELI.BŁĄD należy do kategorii Funkcje logiczne w programie Microsoft Excel, która obejmuje ISNA, CZY.BŁĄD i CZY.BŁĄD. Wszystkie te funkcje pomagają wykrywać i obsługiwać błędy formuł.
IFERROR umożliwia wykonanie obliczeń. Jeśli obliczenia nie skutkować błędem, a następnie wyświetlany jest wynik obliczeń. Jeśli obliczenia czy skutkuje błędem, a następnie wykonywane jest kolejne obliczenie (lub wartość statyczna, taka jak 0, pusta lub wyprowadzany jest jakiś tekst).
Kiedy używałbyś funkcji IFERROR?
- Podczas dzielenia liczb, aby uniknąć błędów spowodowanych dzieleniem przez 0
- Podczas wykonywania wyszukiwań, aby zapobiec błędom, jeśli wartość nie zostanie znaleziona.
- Gdy chcesz wykonać kolejne obliczenia, jeśli pierwsze powoduje błąd (np. Wyszukaj wartość w 2NS tabeli, jeśli nie znajduje się w pierwszej tabeli)
Nieobsługiwane błędy formuł mogą powodować błędy w skoroszycie, ale widoczne błędy powodują również, że arkusz kalkulacyjny jest mniej atrakcyjny.
Jeśli błąd, to 0
Spójrzmy na podstawowy przykład. Poniżej dzielisz dwie liczby. Jeśli spróbujesz dzielić przez zero, otrzymasz błąd:
Zamiast tego wstaw obliczenia w funkcji JEŻELI.BŁĄD, a jeśli podzielisz przez zero, zamiast błędu zostanie wyprowadzone 0:
| 1 | =JEŻELIBŁĄD(A2/B2,0) |
Jeśli błąd, to pusty
Zamiast ustawiać błędy na 0, możesz ustawić je jako „puste” z podwójnymi cudzysłowami („”):
| 1 | =JEŻELIBŁĄD(A2/B2"") |
Przyjrzymy się większej liczbie zastosowań IFERROR za pomocą funkcji WYSZUKAJ.PIONOWO…
IFERROR z funkcją WYSZUKAJ.PIONOWO
Funkcje wyszukiwania, takie jak WYSZUKAJ.PIONOWO, wygenerują błędy, jeśli wartość wyszukiwania nie zostanie znaleziona. Jak pokazano powyżej, możesz użyć funkcji IFERROR, aby zastąpić błędy pustymi („”) lub zerami:
| 1 | =JEŻELIBŁĄD(WYSZUKAJ.PIONOWO(A2,Tabela przeglądowa1!$A$2:$B$4,2,FAŁSZ);"nie znaleziono") |
Jeśli błąd, zrób coś innego
Funkcji IFERROR można również użyć do wykonania drugiego obliczenia, jeśli pierwsze obliczenie zakończy się błędem:
| 12 | =JEŻELIBŁĄD(WYSZUKAJ.PIONOWO(A2,Tabela przeglądowa1!$A$2:$B$4,2,FAŁSZ);WYSZUKAJ.PIONOWO(A2,Tabela przeglądowa2!$A$2:$B$4,2,FAŁSZ)) |
Tutaj, jeśli dane nie zostaną znalezione w „LookupTable1”, zamiast tego zostanie wykonana funkcja WYSZUKAJ.PIONOWO na „LookupTable2”.
Więcej przykładów formuł IFERROR
Zagnieżdżone IFERROR - WYSZUKAJ.PIONOWO wiele arkuszy
Możesz zagnieździć CZY.BŁĄD wewnątrz innej CZY.BŁĄD, aby wykonać 3 oddzielne obliczenia. Tutaj użyjemy dwóch funkcji IFERROR do wykonania funkcji WYSZUKAJ.PIONOWO na 3 oddzielnych arkuszach:
| 123 | =JEŻELIBŁĄD(WYSZUKAJ.PIONOWO(A2,Tabela przeglądowa1!$A$2:$B$4,2,FAŁSZ);JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2,Tabela przeglądowa2!$A$2:$B$4,2,FAŁSZ),WYSZUKAJ.PIONOWO(A2,Tabela przeglądowa3!$A$2:$B$4,2,FAŁSZ))) |
Indeks / Dopasuj i XLOOKUP
Oczywiście funkcja IFERROR będzie również działać z formułami Index / Match i XLOOKUP.
IFERROR XWYSZUKAJ
Funkcja WYSZUKAJ.X to zaawansowana wersja funkcji WYSZUKAJ.PIONOWO.
| 1 | =JEŻELIBŁĄD(XWYSZUKAJ(A2,Tabela przeglądowa1!$A$2:$A$4,Tabela przeglądowa1!$B$2:$B$4);"Nie znaleziono") |
JEŻELI INDEKS / DOPASUJ
INDEKS i PODAJ.POZYCJĘ mogą służyć do tworzenia bardziej zaawansowanych funkcji WYSZUKAJ.PIONOWO (podobnych do działania nowej funkcji WYSZUKAJ.X) w programie Excel.
| 1 | =JEŻELIBŁĄD(INDEKS(Tabela przeglądowa1!$B$2:$B$4,PODZIEL(A3;Tabela przeglądowa1!$A$2:$A$4,0)) "Nie znaleziono") |
IFERROR w tablicach
Formuły tablicowe w programie Excel służą do wykonywania kilku obliczeń za pomocą jednej formuły. Załóżmy, że istnieją trzy kolumny Rok, Sprzedaż i Średnia cena. Możesz sprawdzić całkowitą ilość za pomocą następującego wzoru w kolumnie E.
| 1 | {=SUMA($B$2:$B$4/$C$2:$C$4)} |
Formuła działa dobrze, dopóki nie spróbuje dzielić przez zero, co skutkuje #DIV/0! błąd.
Możesz użyć funkcji JEŻELI.BŁĄD w ten sposób, aby rozwiązać ten błąd:
| 1 | {=SUMA(JEŻELI.BŁĄD($B$2:$B$4/$C$2:$C$4,0))} |
Zwróć uwagę, że funkcja JEŻELI.BŁĄD musi być zagnieżdżona w funkcji SUMA, w przeciwnym razie funkcja JEŻELI.BŁĄD będzie miała zastosowanie do sumy, a nie do każdego pojedynczego elementu w tablicy.
IFNA kontra IFERROR
Funkcja JEŻELI JEŻELI działa dokładnie tak samo jak funkcja JEŻELI JEŻELI, z wyjątkiem tego, że funkcja JEŻELI wychwytuje tylko błędy #N/D. Jest to niezwykle przydatne podczas pracy z funkcjami wyszukiwania: zwykłe błędy formuł będą nadal wykrywane, ale żaden błąd nie pojawi się, jeśli wartość wyszukiwania nie zostanie znaleziona.
| 1 | =JEŻELI (WYSZUKAJ.PIONOWO(A2,Tabela przeglądowa1!$A$2:$B$4,2,FAŁSZ);"Nie znaleziono") |
Jeśli CZY.BŁĄD
Jeśli nadal używasz programu Microsoft Excel 2003 lub starszej wersji, możesz zastąpić IFERROR kombinacją JEŻELI i ISERROR. Oto krótki przykład:
| 1 | =JEŻELI(BŁĄD(A2/B2);0;A2/B2) |
IFERROR w Arkuszach Google
Funkcja IFERROR działa dokładnie tak samo w Arkuszach Google jak w Excelu:
IFERROR Przykłady w VBA
VBA nie ma wbudowanej funkcji IFERROR, ale można również uzyskać dostęp do funkcji Excel IFERROR z poziomu VBA:
| 12 | Dim n tak długon = Application.WorksheetFunction.IfError(Wartość, wartość_jeśli_błąd) |
Aplikacja.Arkusz Funkcja daje dostęp do wielu (nie wszystkich) funkcji Excela w VBA.
Zwykle podczas odczytywania wartości z komórek jest używana opcja IFERROR. Jeśli komórka zawiera błąd, VBA może zgłosić komunikat o błędzie podczas próby przetworzenia wartości komórki. Wypróbuj to za pomocą przykładowego kodu poniżej (gdzie komórka B2 zawiera błąd):
| 1234567891011 | Sub IFERROR_VBA()wym n tak długo, m tak długo„JEŻELI BŁĄD”n = Application.WorksheetFunction.IfError(Zakres("b2").Value, 0)'BEZ JEDNOSTKI'm = Zakres("b2").WartośćNapis końcowy |
Kod przypisuje komórkę B2 do zmiennej. Drugie przypisanie zmiennej generuje błąd, ponieważ wartość komórki to #N/D, ale pierwsze działa poprawnie ze względu na funkcję JEŻELI.BŁĄD.
Możesz również użyć języka VBA, aby utworzyć formułę zawierającą funkcję JEŻELI.BŁĄD:
| 1 | Zakres("C2").FormułaR1K1 = "=JEŻELIBŁĄD(POW[-2]/POW[-1],0)" |
Obsługa błędów w VBA jest zupełnie inna niż w Excelu. Zazwyczaj do obsługi błędów w VBA użyjesz obsługi błędów VBA. Obsługa błędów VBA wygląda tak:
| 12345678910111213141516171819 | Podtest WS()MsgBox DoesWSExist("test")Napis końcowyFunkcja DoesWSExist(wsName As String) jako BooleanDim ws As WorksheetPrzy błędzie Wznów DalejUstaw ws = Arkusze(wsName)„Jeśli błąd WS nie istnieje”Jeśli błąd numer 0 WtedyCzy WSistnieje = FałszW przeciwnym razieCzy WSistnieje = PrawdaZakończ, jeśliW przypadku błędu Przejdź do -1Koniec funkcji |
Zauważ, że używamy Jeśli błąd numer 0 Wtedy aby określić, czy wystąpił błąd. Jest to typowy sposób wyłapywania błędów w VBA. Jednak funkcja JEŻELI.BŁĄD ma pewne zastosowania podczas interakcji z komórkami programu Excel.
