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:
=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:
=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 („”):
=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:
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:
=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:
=JEŻELIBŁĄD(WYSZUKAJ.PIONOWO(A2,Tabela Wyszukaj1!$A$2:$B$4,2,FAŁSZ);IFERROR(WYSZUKAJ.PIONOWO(A2,Tabela Wyszukaj2!$A$2:$B$4,2,FAŁSZ); A2 $: 4,2 $ B $, 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 XLOOKUP to zaawansowana wersja funkcji WYSZUKAJ.PIONOWO.
JEŻELI INDEKS / DOPASUJ
Możesz również wyszukiwać wartości za pomocą funkcji INDEKS i PODAJ.POZYCJĘ w programie Excel.
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.
{=SUMA($B$2:$B$4/$C$2:$C$4)}
Formuła działa dobrze, dopóki zakres dzielników nie otrzyma pustej komórki lub zer. W rezultacie ponownie zobaczysz błąd #DIV/0!.
Tym razem możesz użyć funkcji JEŻELI.BŁĄD w ten sposób:
{=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.
=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:
=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:
Dim n tak długo n = 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):
Sub IFERROR_VBA() Dim n As Long, m As Long 'IFERROR n = Application.WorksheetFunction.IfError(Range("b2").Value, 0) 'Brak IFERROR m = Range("b2").Value End Sub
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:
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:
Sub TestWS() MsgBox DoesWSExist("test") End Funkcja podrzędna DoesWSExist(wsName As String) As Boolean Dim ws As Worksheet On Error Resume Next Ustaw ws = Sheets(wsName) 'Jeśli błąd WS nie istnieje If Err.Number 0 Następnie DoesWSExist = False Else DoesWSExist = Prawda End, jeśli w przypadku błędu Przejdź do -1 End Function
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.
Ćwiczenia praktyczne IFERROR + przykłady
Kliknij dwukrotnie w komórce, aby wyświetlić jej formułę i edytować.
do zrobienia:
usuń na razie przykłady arkuszy kalkulacyjnych u dołu…
przetestować / pomyśleć o:
- obraz vs gif na górze
- zwrócić uwagę na interaktywne przykłady na lekcji?
- zmiana etykiety / zmiana stylu bloków kodu…
- Spis treści na górze? usunąć je z tych stron i ręcznie dodać [TOC]?
- umieść linki VBA „excel, googlesheets” na wierzchu, a może pozbądź się spisu treści? „IFERROR Funkcja dostępna w…”
- co z innymi obrazami… takimi jak ikona Excela, arkusze Google lub VBA, aby wyglądały ładniej?
- tak myślę, że zobacz wersję roboczą wiadomości e-mail, w której używa się logo programu Excel i dodaj to gdzieś… . i zrób z niego fantazyjny nagłówek… to samo z arkuszami g i vba!
- zmniejsz rozmiar czcionki na stronie!
- napraw CSS… Spis treści, obszar składni… itd.!
dodaj przycisk pobierania, aby pobrać arkusz kalkulacyjny + poproś o e-mail PO pobraniu…
lub zrób coś, co robią producenci szablonów… gdzie proszą Cię o wypełnienie ankiety
