Zapobieganie błędom formuł w programie Excel przy użyciu funkcji IFERROR lub IFNA. Są lepsze niż stare ISERROR ISERR i ISNA. Dowiedz się więcej tutaj.
Błędy formuł mogą być częste. Jeśli masz zestaw danych z setkami rekordów, od czasu do czasu pojawi się dzielenie przez zero lub # N / A.
W przeszłości zapobieganie błędom wymagało herkulesowych wysiłków. Kiwaj świadomie głową, jeśli kiedykolwiek straciłeś przytomność =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Oprócz tego, że wpisywanie jest naprawdę długie, rozwiązanie to wymaga od programu Excel wykonania dwukrotnie większej liczby funkcji WYSZUKAJ.PIONOWO. Najpierw wykonaj WYSZUKAJ.PIONOWO, aby sprawdzić, czy WYSZUKAJ.PIONOWO spowoduje błąd. Następnie ponownie wykonaj tę samą funkcję WYSZUKAJ.PIONOWO, aby uzyskać wynik bez błędu.
W programie Excel 2010 wprowadzono znacznie ulepszone = JEŻELI BŁĄD (formuła, wartość w przypadku błędu). Wiem, że IFERROR brzmi jak stare funkcje ISERROR, ISERR, ISNA, ale jest zupełnie inne.
Jest to genialna funkcja: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Jeśli masz 1000 WYSZUKAJ.PIONOWO i tylko 5 zwraca # N / A, 995, które działało, wymaga tylko jednego WYSZUKAJ.PIONOWO. Tylko 5, które zwróciło # N / A, musi przejść do drugiego argumentu IFERROR.
Co dziwne, w programie Excel 2013 dodano funkcję IFNA (). Działa podobnie jak IFERROR, ale wyszukuje tylko błędy # N / A. Można sobie wyobrazić dziwną sytuację, w której wartość w tabeli przeglądowej zostanie znaleziona, ale wynikowa odpowiedź to dzielenie przez 0. Jeśli z jakiegoś powodu chciałeś zachować błąd dzielenia przez zero, to IFNA () zrobi to.
Oczywiście osoba, która zbudowała tabelę przeglądową, powinna była najpierw użyć funkcji IFERROR, aby zapobiec dzieleniu przez zero. Na poniższym rysunku „nm” to kod byłego menedżera oznaczający „nieistotne”.
Podziękowania dla Justina Fishmana, Stephena Gilmera i Excela przez Joe.
Obejrzyj wideo
- W dawnych czasach używałbyś
=IF(ISNA(Formula),0,Formula) - Począwszy od programu Excel 2010,
=IFERROR(Formula,0) - Ale IFERROR traktuje błędy DIV / 0 tak samo jak # N / A! błędy
- Począwszy od programu Excel 2013, użyj
=IFNA(Formula,0)do wykrywania tylko błędów # N / D - Podziękowania dla Justina Fishmana, Stephena Gilmera i Excela przez Joe.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2042 - IFERROR i IFNA!
Będę podcastował wszystkie moje wskazówki z tej książki. Kliknij „i” w prawym górnym rogu, aby przejść do całej listy odtwarzania!
W porządku, wróćmy do dawnych czasów, programu Excel 2007 lub wcześniejszego, jeśli trzeba było zapobiec # N / A! z formuły WYSZUKAJ.PIONOWO, takiej jak ta, robiliśmy tę szaloną rzecz. Wymieszaj wszystkie znaki funkcji WYSZUKAJ.PIONOWO z wyjątkiem znaku =, Ctrl + C, aby umieścić go w schowku, = IF (ISNA (naciśnij klawisz End, aby dotrzeć do końca, jeśli to # N / A!), A następnie powiedz „Nie znaleziono”, przecinek, w przeciwnym razie zrobimy WYSZUKAJ.PIONOWO! Wklejam to tam i zobaczę, jak długa jest ta formuła, Ctrl + Enter, dodaj a) tam, Ctrl + Enter, w porządku, to słodkie. W porządku, ale problem polega na tym, że chociaż rozwiązuje problem # N / A! Każda formuła wykonuje dwukrotnie VOOKUP. Nie chce, abyśmy mówili „Hej, czy to błąd? O nie, nie błąd. Zróbmy to jeszcze raz! ”Wykonanie wszystkich tych funkcji WYSZUKAJ.PIONOWO trwa więc dwa razy dłużej. W programie Excel 2010dają nam niesamowitą, niesamowitą formułę IFERROR!
Teraz wiem, że to brzmi jak to, co mieliśmy wcześniej, ISERROR lub ISERR, ale to jest IFERROR. I tak to działa, weź dowolną formułę, która może zwrócić błąd, a następnie mówisz = JEŻELI BŁĄD, jest formuła, przecinek, co zrobić, jeśli to błąd, więc „Nie znaleziono”. W porządku, piękna rzecz w tym jest, powiedzmy, że masz do wykonania tysiąc funkcji WYSZUKAJ.PIONOWO, a 980 z nich działa. W przypadku 980 po prostu wykonuje WYSZUKAJ.PIONOWO, to nie jest błąd, zwraca odpowiedź, nigdy nie wykonuje drugiego WYSZUKAJ.PIONOWO, to jest piękno IFERROR. W programie Excel 2010 pojawił się błąd IFERROR programu Excel, ale oczywiście jednym z naprawdę głupich problemów jest to, że sama tabela zwraca błąd. Tak, ktoś miał 0, przychód / ilość, więc otrzymujemy # DIV / 0! błąd tam, i ten błąd będzie również wykrywany jako błąd przez IFERROR. W porządku,i będzie wyglądać tak, jakby nie znaleziono, został znaleziony, po prostu ktokolwiek zbudował ten stół, nie wykonał dobrej roboty budując ten stół.
W porządku, wybierz nr 1, Excel 2013 lub nowszy, przełącz się na funkcję, którą dodali w 2013, która nie wyszukuje wszystkich błędów, t szuka tylko # N / A! Ctrl + Enter, a teraz otrzymamy Not Found dla ExcelIsFun, ale zwraca # DIV / 0! błąd. Naprawdę jednak to, co powinniśmy zrobić, jest tutaj w tej formule, powinniśmy obsługiwać tę formułę, aby w pierwszej kolejności zapobiec dzieleniu przez zero. Więc = JEŻELI BŁĄD, to działa w przypadku wszystkich rzeczy, naciśnij klawisz End, aby przejść do końca, przecinek, a następnie co zrobić? Zawsze wstawiałbym tutaj zero jako średnią cenę.
Miałem kiedyś menadżera, Zuzannę (?), „To nie jest matematycznie poprawne, musisz wpisać„ nm ”, aby nie miało znaczenia”. Dokładnie tak, to szalone, jak długo mój menedżer doprowadzał mnie do szału swoją szaloną prośbą, więc skopiujmy to, Wklej specjalne formuły, alt = "" ES F. Teraz otrzymujemy tutaj „nieistotny” błąd, „ Nie znaleziono ”jest wyszukiwane przez IFERROR, a„ nieistotne ”jest w rzeczywistości wynikiem WYSZUKAJ.PIONOWO. W porządku, więc można przejść na kilka różnych sposobów, prawdopodobnie bezpieczną rzeczą do zrobienia tutaj jest użycie IFNA, pod warunkiem, że masz Excel 2013, a wszyscy, którym udostępniasz swój skoroszyt, mają Excel 2013. Ta książka plus cała masa innych są w tej książce, ociekają pikantnymi wskazówkami, 200 stron, łatwe do odczytania, niesamowita, pełna kolorów, niesamowita książka. Sprawdź to, kliknij „I” w prawym górnym rogu,możesz kupić książkę.
W porządku, podsumowanie odcinka: Dawniej używaliśmy długiego formatu = JEŻELI (ISNA (formuła, 0, w przeciwnym razie formuła, formuła została obliczona dwukrotnie, co jest okropne dla funkcji WYSZUKAJ.PIONOWO). Począwszy od programu Excel 2010, = JEŻELI BŁĄD , po prostu wpisz formułę raz, przecinek, co zrobić, jeśli to błąd. Jednak JEŻELI BŁĄD traktuje błędy # DZIEL / 0! tak samo jak błędy # N / D! ale wykryje tylko błędy # N / A !.
Nawiasem mówiąc, ta wskazówka zasugerowana przez czytelników Justina Fishmana, Stephena Gilmera i Excela przez Joe. Dziękuję im za to, że to zasugerowali i dziękujemy za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2042.xlsm
