Obsługa brakujących wyników za pomocą IFNA

IFERROR jest potężną funkcją, ale ukrywa każdy błąd. Czasami jest to zbyt wiele. Jeśli wyszukiwanie nie powiedzie się z powodu błędnych danych, a nie braku dopasowania, powinni Państwo zobaczyć ten problem, zamiast go ukrywać.

Rozwiązaniem jest funkcja IFNA. Obsługuje ona wyłącznie błąd #N/A, a wszystkie pozostałe błędy pozostawia widoczne.

Dzięki temu jest precyzyjnym narzędziem do wyszukiwania, w którym #N/A jest oczekiwanym i nieszkodliwym błędem, natomiast każdy inny błąd wskazuje na rzeczywisty problem, który warto zobaczyć.

IFNA wygląda podobnie do IFERROR i przyjmuje dwa argumenty:

• value formuła, którą należy wykonać
• value_if_na wartość wyświetlana wyłącznie wtedy, gdy wynik to #N/A

Wzorzec ma postać =IFNA(your_formula, fallback). Jeśli formuła zwróci #N/A, wyświetlana jest wartość zastępcza. Jeśli zwróci dowolny inny błąd, pozostaje on widoczny.

Ta różnica ma znaczenie. Rozważmy wyszukiwanie, w którym nieprawidłowy indeks kolumny powoduje błąd #REF!.

• IFERROR zastąpiłaby #REF! wartością zastępczą, ukrywając rzeczywisty błąd.
• IFNA pozostawiłaby #REF! widoczny, dzięki czemu będzie wiadomo, że formułę należy naprawić.

W przypadku rzeczywistego braku dopasowania obie funkcje działają tak samo i zwracają wybrany przez Państwa przyjazny komunikat. IFNA po prostu nie pozwala zamaskować błędów.

Oto typowe zastosowanie. Wyszukują Państwo nazwę klienta i chcą wyświetlić jasną etykietę, gdy nazwy nie ma w tabeli.

Jeśli klienta rzeczywiście brakuje, otrzymają Państwo tekst "Unknown customer". Jeśli jednak przypadkowo wskażą Państwo niewłaściwą tabelę lub uszkodzą indeks kolumny, pojawi się podstawowy błąd, dzięki czemu będzie można go naprawić.

Chroni to przed bezrefleksyjnym zaufaniem nieprawidłowym liczbom.

Funkcja XLOOKUP również zwraca #N/A, gdy nie znajdzie dopasowania, dlatego dobrze współpracuje z IFNA.

Chociaż XLOOKUP ma własny wbudowany argument if_not_found, IFNA jest przydatna podczas edytowania starszych formuł lub gdy potrzebna jest spójność w wielu wyszukiwaniach.

Oba rozwiązania zapewniają przejrzysty wynik w przypadku braku dopasowania, a jednocześnie pozostawiają prawdziwe błędy widoczne.

Wartością zastępczą może być również liczba. Jeśli brakujące dopasowanie powinno być uwzględniane jako zero w kolejnych obliczeniach, należy zwrócić 0 zamiast tekstu.

Na przykład wyszukiwany rabat, który nie ma zastosowania, można rozsądnie zastąpić wartością 0, aby sumy nadal działały.

Zwrócenie tekstu takiego jak "None" w kolumnie liczbowej powodowałoby później błędy #VALUE!, dlatego typ wartości zastępczej należy dopasować do sposobu wykorzystania komórki.

Podczas testowania warto stosować IFNA zamiast IFERROR w trakcie tworzenia formuły.

Ponieważ IFNA ukrywa tylko oczekiwany błąd #N/A, każdy nieoczekiwany błąd, taki jak #VALUE! lub #REF!, od razu zwróci Państwa uwagę.

Później można przełączyć się na IFERROR, jeśli rzeczywiście chcą Państwo wyciszyć wszystkie błędy, ale rozpoczęcie pracy z IFNA pomaga wcześnie wykrywać pomyłki.

IFNA można umieścić wokół funkcji wyszukiwania, której wynik jest wykorzystywany w większej formule. W tym przypadku brakująca cena domyślnie przyjmuje wartość zero, a następnie jest mnożona przez ilość.

Jeśli cena istnieje, otrzymają Państwo rzeczyczną sumę pozycji. Jeśli produktu nie ma w tabeli cen, cena przyjmie wartość 0, a suma pozycji również wyniesie 0, natomiast każdy błąd strukturalny nadal zostanie ujawniony.

Dzięki temu arkusz sprzedaży pozostaje zarówno przejrzysty, jak i wiarygodny.

IFNA jest dostępna w nowoczesnych wersjach programu Excel oraz w Arkuszach Google, dlatego działa w większości używanych obecnie arkuszy kalkulacyjnych.

W bardzo starych wersjach programu Excel może jej nie być. W takim przypadku można naśladować jej działanie, łącząc IF z ISNA, która sprawdza konkretnie, czy wystąpił błąd #N/A.

W następnej lekcji poznają Państwo funkcje z rodziny IS służące do testowania błędów, które zapewniają jeszcze większą kontrolę.

IFNA jest szczególnie przydatna podczas wypełniania formuły wyszukiwania w setkach wierszy. Niektóre klucze zostaną dopasowane, a inne nie, dlatego warto wyświetlać czytelną etykietę przy brakujących dopasowaniach.

Wypełnienie tej formuły w dół zwróci rzeczywisty region dla znanych sklepów oraz "Region TBD" dla każdego sklepu, którego nie ma jeszcze na głównej liście.

Ponieważ IFNA pozostawia inne błędy widoczne, pojedyncze błędne odwołanie na początku kolumny nadal zwróci uwagę, zamiast ukryć się za etykietą.

Przy wyborze można kierować się prostą zasadą:

• IFNA należy stosować w wyszukiwaniach, gdy chcą Państwo obsłużyć brak dopasowania, ale nadal widzieć rzeczywiste błędy.
• IFERROR należy stosować, gdy każdy błąd jest oczekiwany i nieszkodliwy, na przykład w przypadku ilorazów wynikających z dzielenia.

IFNA jest ostrożniejszym i bardziej precyzyjnym rozwiązaniem. Ukrywa dokładnie jeden rodzaj błędu, pozostawiając Państwu naprawienie pozostałych.

Sprawdźmy Państwa rozumienie działania IFNA.

Poznali Państwo precyzyjną funkcję pomocniczą do wyszukiwań:

• =IFNA(value, value_if_na) obsługuje wyłącznie błąd #N/A.
• Wszystkie pozostałe błędy pozostają widoczne, więc rzeczywiste błędy w formule nie zostają ukryte.
• To bezpieczny wybór w przypadku brakujących dopasowań funkcji VLOOKUP i XLOOKUP.
• Typ wartości zastępczej — tekstowy lub liczbowy — należy dopasować do sposobu dalszego użycia komórki.

W następnej kolejności poznają Państwo funkcję ISERROR i funkcje pokrewne, które pozwalają sprawdzać błędy przed podjęciem działania.