Podczas korzystania z formuły WYSZUKAJ.PIONOWO w programie Excel czasami możesz otrzymać brzydki błąd #N/D. Dzieje się tak, gdy formuła nie może znaleźć wartości wyszukiwania.
W tym samouczku pokażę różne sposoby używania funkcji IFERROR z funkcją WYSZUKAJ.PIONOWO do obsługi błędów #N/D, które pojawiają się w arkuszu.
Użycie kombinacji opcji JEŻELI.BŁĄD z funkcją WYSZUKAJ.PIONOWO pozwala wyświetlić coś znaczącego zamiast błędu #N/D. (lub dowolnego innego błędu w tym zakresie).
Zanim przejdziemy do szczegółów dotyczących korzystania z tej kombinacji, przejrzyjmy najpierw funkcję JEŻELI.BŁĄD i zobaczmy, jak to działa.
IFERROR Objaśnienie funkcji
Funkcja JEŻELI.BŁĄD pozwala określić, co ma się stać w przypadku, gdy formuła lub odwołanie do komórki zwróci błąd.
Oto składnia funkcji JEŻELI.BŁĄD.
=JEŻELIBŁĄD(wartość; wartość_jeśli_błąd)
- wartość - to jest argument, który jest sprawdzany pod kątem błędu. W większości przypadków jest to formuła lub odwołanie do komórki. W przypadku używania funkcji WYSZUKAJ.PIONOWO z opcją IFERROR, tym argumentem będzie formuła WYSZUKAJ.PIONOWO.
- value_if_error - jest to wartość zwracana w przypadku błędu. Oceniane są następujące typy błędów: #N/A, #REF!, #DZIEL/0!, #WARTOŚĆ!, #LICZBA!, #NAZWA? i #NULL!.
Możliwe przyczyny zwracania błędu #N/D. WYSZUKAJ.PIONOWO
Funkcja WYSZUKAJ.PIONOWO może zwrócić błąd #N/D z jednego z następujących powodów:
- Wartość wyszukiwania nie została znaleziona w tablicy wyszukiwania.
- W wartości wyszukiwania (lub w tablicy tabeli) występuje wiodąca, końcowa lub podwójna spacja.
- Wystąpił błąd pisowni w wartości wyszukiwania lub wartości w tablicy wyszukiwania.
Wszystkie te przyczyny błędów można obsłużyć za pomocą kombinacji funkcji JEŻELI.BŁĄD i WYSZUKAJ.PIONOWO. Należy jednak zwracać uwagę na przyczynę nr 2 i nr 3 i poprawiać je w danych źródłowych, zamiast pozwolić, aby obsłużyła je funkcja IFERROR.
Uwaga: IFERROR potraktuje błąd niezależnie od tego, co go spowodowało. Jeśli chcesz leczyć tylko błędy spowodowane tym, że funkcja WYSZUKAJ.PIONOWO nie może znaleźć wartości wyszukiwania, użyj zamiast tego funkcji IFNA. Zapewni to, że błędy inne niż #N/D nie będą traktowane i będziesz mógł zbadać te inne błędy.
Spacje wiodące, końcowe i podwójne można traktować za pomocą funkcji TRIM.
Zastępowanie błędu WYSZUKAJ.PIONOWO #N/D znaczącym tekstem
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Jak widać, formuła WYSZUKAJ.PIONOWO zwraca błąd, ponieważ wartości wyszukiwania nie ma na liście. Chcemy uzyskać wynik dla Glena, którego nie ma w tabeli wyników.
Chociaż jest to bardzo mały zestaw danych, możesz uzyskać ogromne zestawy danych, w których musisz sprawdzić występowanie wielu elementów. W każdym przypadku, gdy wartość nie zostanie znaleziona, otrzymasz błąd #N/D.
Oto wzór, którego możesz użyć, aby uzyskać coś sensownego zamiast błędu #N/D.
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D2,$A$2:$B$10,2,0);"Nie znaleziono")
Powyższa formuła zwraca tekst „Nie znaleziono” zamiast błędu #N/D. Możesz również użyć tej samej formuły, aby zwrócić pusty, zero lub dowolny inny znaczący tekst.
Zagnieżdżanie funkcji WYSZUKAJ.PIONOWO z funkcją IFERROR
Jeśli używasz funkcji WYSZUKAJ.PIONOWO, a tabela przeglądowa jest pofragmentowana w tym samym arkuszu lub w różnych arkuszach roboczych, musisz sprawdzić wartość WYSZUKAJ.PIONOWO we wszystkich tych tabelach.
Na przykład w zestawie danych pokazanym poniżej znajdują się dwie oddzielne tabele zawierające nazwiska uczniów i wyniki.
Jeśli muszę znaleźć wynik Grace w tym zestawie danych, muszę użyć funkcji WYSZUKAJ.PIONOWO, aby sprawdzić pierwszą tabelę, a jeśli nie ma w niej wartości, sprawdź drugą tabelę.
Oto zagnieżdżona formuła JEŻELI.BŁĄD, której mogę użyć do wyszukania wartości:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(G3;$A$2:$B$5,2,0);JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(G3;$D$2:$E$5,2,0);"Nie znaleziono"))
Korzystanie z funkcji WYSZUKAJ.PIONOWO z funkcjami IF i ISERROR (wersje wcześniejsze niż Excel 2007)
Funkcja JEŻELI.BŁĄD została wprowadzona w programie Excel 2007 dla systemu Windows i Excel 2016 na komputerze Mac.
Jeśli używasz wcześniejszych wersji, funkcja IFERROR nie będzie działać w twoim systemie.
Funkcjonalność funkcji JEŻELI.BŁĄD można replikować, używając kombinacji funkcji JEŻELI i funkcji CZY.BŁĄD.
Pozwól, że szybko pokażę, jak używać kombinacji JEŻELI i ISERROR zamiast IFERROR.
W powyższym przykładzie, zamiast używać IFERROR, możesz również użyć formuły pokazanej w komórce B3:
=JEŻELI(BŁĄD(A3)”,Nie znaleziono”,A3)
Część ISERROR formuły sprawdza błędy (w tym błąd #N/A) i zwraca TRUE, jeśli zostanie znaleziony błąd, i FALSE, jeśli nie.
- Jeśli jest PRAWDA (co oznacza, że wystąpił błąd), funkcja JEŻELI zwraca określoną wartość (w tym przypadku „Nie znaleziono”).
- Jeśli jest FAŁSZ (co oznacza, że nie ma błędu), funkcja JEŻELI zwraca tę wartość (A3 w powyższym przykładzie).
IFERROR vs IFNA
JEŻELI.BŁĄD traktuje wszystkie rodzaje błędów, podczas gdy JEŻELI JEŻELI traktuje tylko błąd #N/D.
Podczas obsługi błędów spowodowanych przez funkcję WYSZUKAJ.PIONOWO upewnij się, że używasz właściwej formuły.
Użyj IFERROR kiedy chcesz leczyć wszelkiego rodzaju błędy. Teraz błąd może być spowodowany różnymi czynnikami (takimi jak niewłaściwa formuła, błędnie napisany nazwany zakres, nieznalezienie wartości odnośnika i zwrócenie wartości błędu z tabeli odnośników). Nie miałoby to znaczenia dla opcji IFERROR i zastąpiłoby wszystkie te błędy podaną wartością.
Użyj IFNA gdy chcesz leczyć tylko błędy #N/D, które są bardziej prawdopodobne, gdy formuła WYSZUKAJ.PIONOWO nie może znaleźć wartości wyszukiwania.
Przydatne mogą być również następujące samouczki programu Excel:
- Jak rozróżniać wielkość liter w funkcji WYSZUKAJ.PIONOWO.
- WYSZUKAJ.PIONOWO vs. INDEKS / MECZ - Debata kończy się tutaj!
- Użyj funkcji VLookup, aby uzyskać ostatnią liczbę na liście w programie Excel.
- Jak korzystać z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami
- Błąd #NAME w programie Excel - co go powoduje i jak go naprawić!
