IFERROR + XLOOKUP: Tworzenie formuł odpornych na błędy Excel
Kiedy używać argumentu if_not_found IFERROR, IFNA i XLOOKUP — i kiedy ukrywanie błędów jest dokładnie złym posunięciem. Praktyczne wzorce formuł, które głośno zawodzą tylko wtedy, gdy powinny.
#N/A rozsiane po raporcie wygląda na uszkodzone, więc odruchem jest zawinięcie wszystkiego w IFERROR i przejście dalej. Czasami to prawda. Często ukrywa prawdziwy problem — błędne wyszukiwanie, dzielenie przez zero, literówkę w kluczu — pod schludną, pustą komórką.
W tym przewodniku opisano trzy narzędzia do obsługi błędów formuł, różnice między nimi oraz wzorce ukrywające błędy oczekiwano, jednocześnie pozwalając, aby błędy nieoczekiwane pozostały widoczne.
Trzy narzędzia
IFERROR przechwytuje typ błędu co:
=IFERROR(A2/B2, 0)
Jeśli podział daje #DIV/0!, #VALUE!, #REF! — cokolwiek — otrzymasz 0. Ta szerokość jest również jego niebezpieczeństwem: na uwagę zasługuje #REF! z usuniętej kolumny, a nie ciche 0.
IFNA łapie tylko #N/A:
=IFNA(VLOOKUP(A2, Prices!A:B, 2, FALSE), "not listed")
Prawie zawsze właśnie tego oczekujesz przy wyszukiwaniach: „nie znaleziono wartości” jest oczekiwanym warunkiem, podczas gdy #VALUE! lub #REF! z tej samej formuły nadal sygnalizują prawdziwy błąd.
XLOOKUP wbudowany if_not_found stanowi awaryjną część samego wyszukiwania:
=XLOOKUP(A2, Prices!A:A, Prices!B:B, "not listed")
Czystsze niż pakowanie i obsługuje tylko przypadki, w których nie znaleziono — inne błędy nadal się pojawiają.
Ogólna zasada: oczekiwana obsługa, ujawnianie nieoczekiwane
Zadaj jedno pytanie na każdą formułę: który błąd jest tutaj normalny?
- Wyszukiwanie, w którym zgodnie z prawem może brakować → uchwytu
#N/A(alternatywa IFNA lub XLOOKUP). - Stosunek, którego mianownik może zgodnie z prawem wynosić zero → jawnie przetestuj mianownik:
=IF(B2=0, "", A2/B2)
Testowanie warunku jest lepsze niż wychwytywanie błędu: IF(B2=0,…) dokumentuje dlaczego, że istnieje rozwiązanie awaryjne, podczas gdy IFERROR wokół tego samego podziału również połknęłoby #VALUE! spowodowane tekstem w kolumnie B.
- Wszystko inne → niech wystąpi błąd. Widoczny „#REF!” kosztuje minutę; niewidzialny kosztuje cię zły raport.
Klasyczne błędy
Koc IFERROR na całą kolumnę. Wysyłasz raport zawierający 40 zer, z których trzy to zera rzeczywiste, a 37 to arkusz o zmienionej nazwie, którego nikt nie zauważył.
IFERROR(..., "") matematyka zasilania. Pusty ciąg znaków w kolumnie numerycznej powoduje, że dalsze SUM są nieco błędne i dają w arytmetyce #VALUE! — ukryty błąd pojawia się dwie kolumny później, dalej od swojej przyczyny.
Wychwytywanie błędów wskazujących na brudne dane. Jeśli VALUE(A2) zawiera błędy, ponieważ kolumna zawiera tekst i liczby, rozwiązaniem jest czyszczenie kolumny, który nie wychwytuje symptomu.
Kontrola arkusza pełnego ukrytych błędów
Dziedziczysz skoroszyt, w którym każda formuła jest opakowana w IFERROR? Dwa ruchy:
- Policz, co zostało złapane. W kolumnie pomocniczej powtórz formułę wewnętrzną bez opakowania i zlicz błędy z wprowadzoną wartością
=SUM(--ISERROR(...))przekraczającą zakres. - Poproś asystenta o audyt. AI dla Excel może skanować formuły arkusza, wyświetlać listę komórek, które aktualnie ukrywają błędy i jakiego typu są poszczególne błędy, a także odróżniać „brak wyszukiwania, obsługiwany poprawnie” od „uszkodzonego odniesienia, dyskretnie ukryte” — a następnie naprawić te, które zatwierdzisz, korzystając z automatycznej kopii zapasowej przed jakąkolwiek zmianą.
Ten rodzaj audytu formuł jest żmudny i szybki w przypadku narzędzia, które programowo czyta skoroszyt. Jeśli wolisz opisać cel w zdaniu, niż budować kolumny pomocnicze, wypróbuj dodatek za darmo — a aby zapoznać się z prostą angielską drogą do pisania tych formuł, zobacz Generowanie formuł AI.
Często zadawane pytania
Jaka jest różnica między IFERROR a IFNA?
IFERROR przechwytuje każdy typ błędu; IFNA wychwytuje tylko #N/A (błąd „nie znaleziono”). W przypadku wyszukiwań preferuj argument if_not_found IFNA lub XLOOKUP, aby wyeliminować błędy strukturalne, takie jak #REF! bądź widoczny.
Czy powinienem wszędzie używać IFERROR?
Nie. Obsługuj tylko oczekiwane błędy (zwykle brak wyszukiwania i mianowniki zerowe) i pozwól, aby pojawiły się nieoczekiwane błędy. Widoczny błąd to informacja diagnostyczna; ukryty to przyszły nieprawidłowy raport.
Jak znaleźć wszystkie komórki z błędami w programie Excel?
Naciśnij F5 → Specjalne → Formuły → zaznacz tylko Błędy. Excel wybiera każdą komórkę błędu w arkuszu. Asystent AI może pójść dalej i sklasyfikować je według rodzaju błędu i przyczyny.