Funkcje wyszukiwania, takie jak VLOOKUP, mogą być darem niebios w Excelu, poprawiając integralność i wydajność arkusza kalkulacyjnego. Pisałem już wcześniej o podstawach VLOOKUP, więc w tym artykule skupię się na rozwiązywaniu problemów z VLOOKUP.
Zakładam, że używasz VLOOKUP, aby zwrócić dokładne dopasowanie, ponieważ z natury przybliżone dopasowania są mniej podatne na wyświetlanie frustrującego błędu #N/A.
Rysunek 1 ilustruje typową sytuację z VLOOKUP. Formuła w komórce B2 jest skonstruowana poprawnie i widzimy, że konto 40100 pojawia się na liście, którą przeszukujemy. Jednak VLOOKUP zwraca #N/A, co oznacza, że będziemy musieli trochę poszukać.
Rysunek 1: Niespójności danych mogą spowodować, że VLOOKUP zwróci #N/A.
Komórka A4 pokazuje test, który można wykonać, aby potwierdzić, czy dwie komórki są identyczne:
=A2=D2
Ta formuła porównuje komórkę A2 z komórką D2. Jeśli obie komórki są identyczne, formuła zwróci TRUE, ale w tym momencie zwraca FALSE. W tym momencie istnieją dwa prawdopodobne problemy:
- Komórka A2 lub komórka D2 zawiera liczbę zapisaną jako tekst.
- Jeśli obie komórki zawierają liczby zapisane jako tekst, jedna lub druga może mieć spacje po liczbie.
Najpierw ustalmy, czy obie komórki zawierają liczby, czy nie, jak pokazują formuły w komórkach A7 i A10, z których obie używają funkcji ISNUMBER. Jak można się domyślić, funkcja ISNUMBER służy do określania, czy wartość w komórce jest liczbą, czy nie. Funkcja zwraca wartość TRUE, aby wskazać wartość liczbową, lub FALSE, aby wskazać wartość nienumeryczną. Ponieważ formuła odwołująca się do komórki D2 zwraca FALSE, liczby w kolumnie D są przechowywane jako tekst.
Sytuacja ta często pojawia się podczas korzystania z funkcji wyszukiwania, takich jak VLOOKUP, w celu odniesienia się do danych wyeksportowanych z programu księgowego lub innej platformy oprogramowania. Niektóre programy są zaprojektowane do przechowywania niektórych liczb wyeksportowanych do Excela jako tekst. Jeśli po prostu sortujesz lub filtrujesz dane, prawdopodobnie nie zauważysz tego niuansu, który staje się oczywisty w przypadku funkcji wyszukiwania.
Na szczęście istnieje łatwe rozwiązanie tego scenariusza, jak pokazano na rysunku 2:
- Zaznacz komórki zawierające liczbę zapisaną jako tekst.
- Wybierz menu Dane.
- Kliknij opcję Tekst do kolumn.
- Kliknij przycisk Zakończ.
Kreator Text to Columns ma trzy ekrany, ale w tym kontekście nie musimy dokonywać żadnych wyborów w kreatorze. Samo uruchomienie kreatora i kliknięcie przycisku Zakończ natychmiast przekształca liczby przechowywane jako tekst na wartości liczbowe. Jak widać po prawej stronie rysunku 2, funkcja VLOOKUP zwraca teraz wartość dla konta 40100 dla miesiąca marca, formuła porównania w komórce A4 zwraca wartość TRUE, co oznacza, że komórki są identyczne, a obie funkcje ISNUMBER zwracają wartość TRUE.
Rysunek 2: Funkcja Text to Columns może błyskawicznie konwertować tekst na wartości liczbowe.
Zwróćmy teraz uwagę na rysunek 3, który również ma problem z VLOOKUP. W tym przypadku bezskutecznie szukamy słowa Product Sales. Ponownie komórka A5 porównuje dwie komórki, A2 i D2, i zwraca FALSE. Ponieważ 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 odpowiednio w komórkach A8 i A11. Komórka A2 zawiera 13 znaków, podczas gdy komórka D2 ma 17 znaków.
Czasami błędne spacje wynikają z nieumyślnego naciśnięcia spacji podczas wprowadzania danych lub czasami programy komputerowe dodają dodatkowe spacje na końcu pola tekstowego.
Wprowadź swój adres e-mail, aby zapisać się do naszego newslettera i otrzymywać co tydzień najlepsze informacje z AccountingWEB.Enter email address *Enteremail addressSign up
Komórka H2 pokazuje funkcję TRIM, której możemy użyć do wyeliminowania zbędnych
spacji.Funkcja TRIM zachowuje jedną spację między każdym słowem, ale usuwa wszelkie inne spacje. W tym przypadku, gdybyśmy skopiowali komórki H2:H5 i użyli polecenia Wklej specjalnie programu Excel, aby wkleić wartości do komórek od D2 do D5, wówczas funkcja VLOOKUP zwróciłaby wartość, której szukamy.
Inną alternatywą dla użycia funkcji TRIM do zmiany oryginalnych danych jest włączenie znaku wieloznacznego do VLOOKUP, jak pokazano w komórce B3 na rysunku 3. Zamiast po prostu szukać zawartości komórki A3, argument lookup_value w VLOOKUP to A3&"*".
Ampersand zastępuje funkcję CONCATENATE programu Excel, która jest często używana do łączenia tekstu. Gwiazdka musi być ujęta w podwójne cudzysłowy, ale w tym przypadku instruuje VLOOKUP, aby znalazł 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ż naprawdę dokładnego dopasowania, ale jeśli jedyną różnicą w danych są końcowe spacje, to znak wieloznaczny powinien pomóc uniknąć frustracji.
Rysunek 3: Funkcji TRIM lub symbolu wieloznacznego można również użyć, jeśli funkcja VLOOKUP zwraca błąd #N/A.
Powiązany artykuł:
Jak poprawić integralność arkusza kalkulacyjnego za pomocą VLOOKUP
