Jak rozwiązywać błędy #N/A w formułach programu Excel, takich jak funkcja WYSZUKAJ.PIONOWO
Program Microsoft Excel może generować dziwne błędy podczas wprowadzania danych lub uruchamiania formuł, zwłaszcza jeśli nie rozumie, o co pytasz. Błąd #N/A jest dość powszechny podczas wyszukiwania — takiego jak WYSZUKAJ. PIONOWO, WYSZUKAJ. POZIOMO, PODAJ. POZYCJĘ lub podobne funkcje. Zwykle pojawia się, gdy odwoływane dane nie są do końca poprawne lub po prostu ich nie ma. Może to być bardzo frustrujące, ponieważ zakłóca analizę danych lub raport, a czasami po prostu chcesz, aby po cichu wyświetlał puste pola zamiast komunikatów o błędach. Jeśli widzisz #N/A, prawdopodobnie popełniłeś błąd ortograficzny, użyłeś niewłaściwego typu danych lub wartość wyszukiwania faktycznie nie znajduje się w tabeli. Trochę denerwujące, ale istnieją dobre sposoby, aby to naprawić, w zależności od celu — czy chcesz rozwiązać przyczynę błędu, czy po prostu ukryć go przed widokiem.
Rzecz w tym, że te błędy mogą pojawić się z powodu drobnych pomyłek, które mogą umknąć uwadze, jeśli nie zwrócisz na nie uwagi – takich jak dodatkowa spacja czy literówka. Czasami Excel nie może znaleźć tego, o co pytasz, z powodu problemów z formatowaniem lub może przypadkowo odwołałeś się do komórki spoza zakresu danych. To częsty scenariusz, a rozwiązywanie go nie jest takie trudne, gdy już zrozumiesz, co jest nie tak. Oto więc lista poprawek, obejmująca sprawdzanie danych wejściowych, rozwiązywanie typowych problemów i dokładne wychwytywanie błędów bez bałaganu w arkuszu.
Jak naprawić błąd #N/A w programie Excel
Rozwiązanie 1: Sprawdź dokładnie wartości wyszukiwania i zakres tabeli
Jeśli pojawia się błąd #N/A, pierwszą rzeczą, jaką należy zrobić, jest sprawdzenie, czy wartość wyszukiwania dokładnie odpowiada wartości w tabeli. Często jest to drobna literówka lub spacja — Excel jest pod tym względem bardzo wybredny. Aby upewnić się, że dane są czyste, spróbuj użyć funkcji USUŃ. ZBĘDNE. ODSTĘPY w kolumnie wyszukiwania, aby usunąć wszelkie początkowe i końcowe spacje. Na przykład, jeśli lista wyszukiwania znajduje się w kolumnie A, utwórz nową kolumnę pomocniczą i użyj =TRIM(A2)jej w funkcji WYSZUKAJ. PIONOWO. Sprawdź również argumenty table_array i col_index_num — upewnij się, że wskazują na właściwe zakresy. Czasami określasz zakres, np.A2:B7ale dane w rzeczywistości się w nim znajdują A2:B8, lub zapomniałeś poprawić zakres znakami dolara. Zidentyfikowanie tych problemów może nie być efektowne, ale może zaoszczędzić Ci później sporo główkowania.
Osobiście uważam, że jeśli wartość wyszukiwania wygląda poprawnie, ale nadal wyświetla się błąd #N/A, może to oznaczać niezgodność typów danych – liczba kontra tekst to klasyczny przykład. Sprawdź, czy wartość wyszukiwania jest sformatowana jako tekst, ale kolumna tabeli zawiera liczby, czy odwrotnie. Możesz to zrobić, klikając komórkę i sprawdzając format na wstążce; czasami trzeba przekonwertować tekst na liczbę (lub odwrotnie) albo użyć funkcji WARTOŚĆ, aby wyrównać typy danych.
Poprawka 2: Użyj pułapek błędów z IFERROR lub IFNA
Oto mała sztuczka – jeśli naprawa danych źródłowych nie wystarczy lub po prostu masz dość brzydkich błędów #N/D, możesz je schludnie ukryć. Funkcja JEŻELI. BŁĄD to Twój najlepszy przyjaciel. Wychwytuje ona błędy w formułach i pozwala wskazać programowi Excel, co ma wyświetlać zamiast nich – na przykład puste pole, niestandardowy komunikat lub wartość domyślną. Na przykład, zawijając funkcję WYSZUKAJ. PIONOWO w następujący sposób:
=IFERROR(VLOOKUP(E4, B2:C7, 2, FALSE), "Not found")wyświetli „Nie znaleziono” zamiast #N/D, gdy Excel nie znajdzie dopasowania. Jest to bardzo pomocne w pulpitach nawigacyjnych lub raportach, gdzie nie chcesz, aby brzydkie komunikaty o błędach psuły obraz. Funkcja JEŻELI. NA działa podobnie, ale wychwytuje tylko błędy #N/D, więc jest lepsza, jeśli chcesz odróżnić błąd „nie znaleziono” od innych błędów formuł. Pamiętaj jednak, że korzystanie z tych funkcji sprawi, że arkusze będą nieco bardziej przejrzyste, ale nie usunie przyczyny problemu — dlatego warto również sprawdzić dane.
Poprawka 3: Użyj kombinacji ISERROR/IF, aby wychwycić wszystkie błędy
Jeśli masz ochotę na ekstrawagancję lub chcesz mieć większą kontrolę, połączenie ISERROR z IF pozwala na jeszcze większą personalizację obsługi błędów. Na przykład:
=IF(ISERROR(VLOOKUP(E4, B2:C7, 2, FALSE)), "Nope", VLOOKUP(E4, B2:C7, 2, FALSE))Zasadniczo sprawdza, czy wystąpił błąd, a następnie wyświetla niestandardowy komunikat – można tu zrobić praktycznie wszystko. Bonus: Działa ze wszystkimi typami błędów, nie tylko #N/A. Nie wiem dlaczego, ale czasami wydaje się bardziej niezawodny niż zagnieżdżona funkcja JEŻELI. BŁĄD, szczególnie w złożonych formułach.
Rozwiązanie 4: Upewnij się, że typy danych są spójne
To jedna z najbardziej podstępnych przyczyn błędu #N/A. Excel ma problem z tym, czy wyszukiwana wartość jest liczbą zapisaną jako tekst, czy faktycznie liczbą. Aby to sprawdzić, zaznacz komórki i sprawdź format – czasami zobaczysz ikonę z małym zielonym trójkątem, oznaczającą niezgodność. Przekonwertuj tekst na liczby za pomocą opcji Zmień tekst na liczbę lub użyj jej, =VALUE()aby upewnić się, że dane się zgadzają. Jeśli dane są niezgodne, funkcja WYSZUKAJ. PIONOWO i podobne nie znajdą dopasowania, co spowoduje błąd #N/A.
W niektórych konfiguracjach nie jest to od razu oczywiste, ponieważ komórki wyglądają identycznie – dopóki nie spróbujesz ich dopasować. Naprawa typów danych często rozwiązuje wiele możliwych do uniknięcia błędów.
Napraw błędy #N/A w makrach
Jeśli uruchamiasz makra i widzisz błąd #N/A, prawdopodobnie kod VBA odwołuje się do nieistniejących danych lub zakres wyszukiwania jest niepoprawny. Sprawdź obiekty Range, upewnij się, że nazwy arkuszy kalkulacyjnych są poprawne i zweryfikuj parametry przekazywane do funkcji wyszukiwania, takich jak WorksheetFunction. VLookup. Czasami dodanie kontroli w makrze, aby upewnić się, że komórki lub zakresy nie są puste przed uruchomieniem wyszukiwania, może zaoszczędzić Ci sporo kłopotów.
#N/A Naprawiono błędy, a czasami nadal nie
Ogólnie rzecz biorąc, naprawa błędów #N/A nie jest szczególnie skomplikowana, ale wymaga dbałości o szczegóły. Oczyszczenie danych, upewnienie się, że zakresy są poprawne i wychwytywanie błędów za pomocą funkcji takich jak JEŻELI. BŁĄD lub JEŻELI. NA. Czasami błędy są spowodowane niespójnym formatowaniem lub literówkami, a ich naprawa jest kluczowa. Innym razem danych po prostu nie ma i trzeba zdecydować, czy dodać więcej danych, czy odpowiednio dostosować formuły.
Streszczenie
- Sprawdź dokładnie wartości wyszukiwania i zakresy tabeli, zwróć uwagę na literówki
- Użyj TRIM do czyszczenia przestrzeni
- Upewnij się, że typy danych są spójne (liczby kontra tekst)
- Zawiń formuły w JEŻELI. BŁĄD lub JEŻELI. NA, aby ukryć błędy
- Zweryfikuj zakresy i odniesienia, szczególnie w makrach
Podsumowanie
Naprawianie błędów #N/A często sprowadza się do wyłapywania drobnych błędów – takich jak literówka czy spacja – i usprawniania formuł dzięki funkcjom obsługi błędów. Wystarczy odrobina cierpliwości i staranne czyszczenie danych. Miejmy nadzieję, że to skróci czas rozwiązywania problemów. Pamiętaj tylko, że jakość danych jest najważniejsza, a gdy wszystko się ułoży, błędy zazwyczaj znikają.
Powiązane artykuły
Czy ten artykuł był pomocny?