Rozwiązywanie problemów, gdy funkcja VLOOKUP programu Excel wraca #N/A

Funkcje wyszukiwania, takie jak VLOOKUP, mogą być błogosławieństwem w programie Excel, poprawiając integralność i wydajność arkuszy kalkulacyjnych. Już wcześniej pisałem o podstawowych funkcjach VLOOKUP, więc w tym artykule skupię się na rozwiązywaniu problemów z VLOOKUP.

Założę się, że używasz VLOOKUP, aby zwrócić dokładny mecz, ponieważ z natury rzeczy przybliżone mecze są mniej podatne na wyświetlanie frustrującego błędu #N/A.

Rysunek 1 ilustruje wspólną sytuację z VLOOKUP. Formuła w komórce B2 jest poprawnie skonstruowana i widzimy, że konto 40100 pojawia się na liście, której szukamy. Jednak VLOOKUP zwraca #N/A, co oznacza, że będziemy musieli trochę kopać.

Rysunek 1: Niespójności danych mogą spowodować, że VLOOKUP zwróci #N/A.

:

Komórka A4 pokazuje test, który można wykonać w celu potwierdzenia, czy dwie komórki są identyczne:

=A2=D2

Ten wzór porównuje komórkę A2 z komórką D2. Jeśli obie komórki są identyczne, to formuła zwróci PRAWDĘ, ale w tym momencie zwraca FALS. W tym momencie występują dwa prawdopodobne problemy:

  • Albo komórka A2, albo D2 zawiera numer zapisany jako tekst.
  • Jeśli obie komórki zawierają numery zapisane jako tekst, jedna lub druga może mieć spację ciągnącą za numerem.

Najpierw określmy, czy obie komórki zawierają liczby, czy też nie, jak pokazują wzory w komórkach A7 i A10, z których obie korzystają z funkcji ISNUMBER. Jak można przypuszczać, funkcja LICZBA służy do określenia, czy wartość w komórce jest liczbą, czy nie. Funkcja zwraca TRUE, aby wskazać wartość liczbową, lub FALSE, aby wskazać wartość nie-numeryczną. Ponieważ formuła odnosząca się do komórki D2 zwraca FALSE, liczby w kolumnie D są zapisywane jako tekst.

Taka sytuacja pojawia się często podczas korzystania z funkcji wyszukiwania, takich jak VLOOKUP, w celu porównania danych wyeksportowanych z programu księgowego lub innej platformy programowej. Niektóre programy są przeznaczone do przechowywania pewnych liczb wyeksportowanych do programu Excel jako tekst. Jeżeli po prostu sortujesz lub filtrujesz dane, prawdopodobnie nie zauważysz niuansu, który staje się obficie widoczny dzięki funkcjom wyszukiwania.

Na szczęście w tym scenariuszu jest prosta poprawka, jak pokazano na rysunku 2:

  1. Wybierz komórki, które zawierają numer zapisany jako tekst.
  2. Wybierz menu Dane .
  3. Kliknij Tekst do Kolumny .
  4. Kliknij Zakończyć .

Kreator Text to Columns ma trzy ekrany, ale w tym kontekście nie musimy dokonywać żadnych wyborów w kreatorze. Po prostu uruchomienie kreatora i kliknięcie przycisku finish powoduje natychmiastowe przekształcenie liczb zapisanych jako tekst na wartości liczbowe. Jak widać po prawej stronie Rysunku 2, VLOOKUP zwraca teraz wartość dla konta 40100 za miesiąc marzec, formuła porównania w komórce A4 zwraca TRUE, aby oznaczyć komórki są identyczne, a obie funkcje ISNUMBER zwracają TRUE.

Rysunek 2: Text to Columns umożliwia natychmiastową konwersję tekstu na wartości liczbowe.

Teraz zwróćmy uwagę na Rysunek 3, który również ma problem z VLOOKUP. W tym przypadku bezskutecznie szukamy słowa Sprzedaż produktów. Po raz kolejny komórka A5 porównuje dwie komórki, A2 i D2, i zwraca FALSE. Ponieważ wyraźnie obie komórki zawierają wartości tekstowe, nie będziemy musieli używać funkcji ISTEXT, ale zamiast tego użyjemy funkcji LEN, aby określić liczbę znaków w każdej komórce, jak pokazano w komórkach A8 i A11, odpowiednio. Komórka A2 zawiera 13 znaków, natomiast komórka D2 17 znaków.

Czasami błędne spacje wynikają z nieumyślnego naciśnięcia spacji przez użytkownika podczas wprowadzania danych, a czasami programy komputerowe dodają dodatkowe spacje na końcu pola tekstowego.

Wpisz swój adres e-mail, aby zapisać się do naszego newslettera i otrzymywać co tydzień najlepsze informacje o AccountingWEB.Wpisz adres e-mail *Enter adres e-mailSign up

Komórka H2 pokazuje funkcję TRIM, którą możemy wykorzystać do eliminacji przestrzeni zewnętrznych. Funkcja TRIM utrzymuje jedną spację pomiędzy poszczególnymi słowami, ale usuwa wszelkie inne. W tym przypadku, jeśli mielibyśmy skopiować komórki H2:H5 i użyć specjalnego polecenia Wklej Excela, aby wkleić jako wartości nad komórkami D2 do D5, to VLOOKUP zwróciłby wartość, której szukamy.

Inną alternatywą dla używania funkcji TRIM do zmiany oryginalnych danych jest włączenie do VLOOKUP znaku wieloznacznego, jak pokazano w komórce B3 na rysunku 3. Zamiast po prostu szukać zawartości komórki A3, argumentem lookup_value w VLOOKUP jest A3&”*”.

Wzmacniacz i zastępuje funkcję CONCATENATE Excela, która jest często używana do łączenia tekstu razem. Gwiazdka musi być zamknięta w cudzysłowie, ale w tym przypadku instruuje VLOOKUP, aby znaleźć dopasowanie na podstawie komórki, która zaczyna się od zawartości komórki A3. Należy pamiętać, że oznacza to, że nie widzimy już prawdziwie dokładnego dopasowania, ale jeśli jedyną różnicą w danych są spacje ciągnące, to znak wieloznaczny powinien pomóc uniknąć frustracji.

Rysunek 3: Funkcja TRIM lub znak wieloznaczny może być również użyty, jeśli VLOOKUP zwraca błąd #N/A.

Powiązany artykuł:

Jak poprawić integralność arkusza kalkulacyjnego za pomocą VLOOKUP