Funkcja WYSZUKAJ.PIONOWO nie działa – rozwiązywanie problemów N/A, NAME i VALUE

Ta lekcja wyjaśnia, jak szybko radzić sobie z sytuacją, w której funkcja VPR (VLOOKUP) nie chce działać w programie Excel 2013, 2010, 2007 i 2003 oraz jak identyfikować i naprawiać typowe błędy i przezwyciężać ograniczenia VPR.

W kilku poprzednich artykułach badaliśmy różne aspekty tej funkcji VPR w Excelu. Jeśli przeczytałeś je uważnie, powinieneś być teraz ekspertem w tej dziedzinie. Jednak nie bez powodu wielu ekspertów Excela uważa VPR jedna z bardziej złożonych funkcji. Ma szereg ograniczeń i funkcji, które stają się źródłem wielu problemów i błędów.

W tym artykule znajdziesz proste wyjaśnienia błędów #W (#Nie dotyczy), #NAZWA? (#Imię i #WARTOŚĆ! (#VALUE!), które pojawiają się podczas pracy z funkcją VPR, a także techniki i metody radzenia sobie z nimi. Zaczniemy od najczęstszych przypadków i najbardziej oczywistych powodów. VPR nie działa, więc lepiej przestudiować przykłady w kolejności, w jakiej zostały podane w artykule.

• Naprawianie błędu #N/A
• Naprawiam #ARG! w formułach z funkcją WYSZUKAJ.PIONOWO
• Błąd #NAME? w WYSZUKAJ.PIONOWO
• WYSZUKAJ.PIONOWO nie działa (problemy, ograniczenia i rozwiązania)
• WYSZUKAJ.PIONOWO – praca z funkcjami IFERROR i ISERROR

Naprawianie błędu #N/D w funkcji WYSZUKAJ.PIONOWO w programie Excel

W formułach z VPR komunikat o błędzie #W (#N/A) oznacza niedostępne (brak danych) – pojawia się, gdy Excel nie może znaleźć szukanej wartości. Może się tak zdarzyć z kilku powodów.

1. Żądana wartość jest błędnie napisana

Dobry pomysł, aby najpierw sprawdzić ten przedmiot! Literówki często pojawiają się, gdy pracujesz z bardzo dużą ilością danych, składających się z tysięcy wierszy, lub gdy szukana wartość jest zapisana w formule.

2. Błąd #N/D podczas wyszukiwania przybliżonego dopasowania za pomocą funkcji WYSZUKAJ.PIONOWO

Jeśli używasz formuły z przybliżonym warunkiem wyszukiwania, tj. argument Zakres wyszukiwania (range_lookup) ma wartość PRAWDA lub nie została określona, ​​formuła może zgłosić błąd # N / A w dwóch przypadkach:

• Wartość do wyszukania jest mniejsza niż najmniejsza wartość w wyszukiwanej tablicy.
• Kolumna wyszukiwania nie jest posortowana w porządku rosnącym.

3. Błąd #N/D podczas wyszukiwania dokładnego dopasowania za pomocą funkcji WYSZUKAJ.PIONOWO

Jeśli szukasz dokładnego dopasowania, czyli argumentu Zakres wyszukiwania (range_lookup) ma wartość FAŁSZ i nie znaleziono dokładnej wartości, formuła również zgłosi błąd # N / A. Dowiedz się więcej o wyszukiwaniu dokładnych i przybliżonych dopasowań za pomocą funkcji VPR.

4. Kolumna wyszukiwania nie jest skrajnie lewa

Jak zapewne wiesz, jedno z najważniejszych ograniczeń VPR jest to, że nie może być skierowane w lewo, dlatego kolumna odnośnika w tabeli musi znajdować się najbardziej po lewej stronie. W praktyce często o tym zapominamy, co prowadzi do niedziałającej formuły i błędu. # N / A.

Decyzja: Jeśli nie można zmienić struktury danych tak, aby kolumna wyszukiwania znajdowała się najbardziej na lewo, możesz użyć kombinacji funkcji INDEKS (INDEKS) i WIĘCEJ EKSPOZYCYJNE (MATCH) jako bardziej elastyczna alternatywa dla VPR.

5. Liczby są sformatowane jako tekst

Kolejne źródło błędu # N / A w formułach z VPR to liczby w formacie tekstowym w tabeli głównej lub tabeli przeglądowej.

Zwykle dzieje się tak, gdy importujesz informacje z zewnętrznych baz danych lub wpisujesz apostrof przed liczbą, aby zachować wiodące zero.

Najbardziej oczywiste znaki liczby w formacie tekstowym pokazano na poniższym rysunku:

Ponadto liczby mogą być przechowywane w formacie Ogólne (Ogólny). W tym przypadku zauważalna jest tylko jedna cecha – liczby są wyrównane do lewej krawędzi komórki, podczas gdy domyślnie są wyrównane do prawej krawędzi.

Decyzja: Jeśli jest to pojedyncza wartość, po prostu kliknij ikonę błędu i wybierz Konwertuj na liczbę (Konwertuj na numer) z menu kontekstowego.

Jeśli tak jest z wieloma numerami, wybierz je i kliknij prawym przyciskiem myszy wybrany obszar. W wyświetlonym menu kontekstowym wybierz Formatowanie komórek (Formatuj komórki) > tab Numer (liczba) > format Numer (numerycznie) i naciśnij OK.

6. Na początku lub na końcu jest spacja

To jest najmniej oczywisty powód błędu. # N / A w funkcji VPR, ponieważ wizualnie trudno jest dostrzec te dodatkowe spacje, zwłaszcza podczas pracy z dużymi tabelami, gdy większość danych znajduje się poza ekranem.

Rozwiązanie 1: Dodatkowe spacje w tabeli głównej (tam, gdzie znajduje się funkcja WYSZUKAJ.PIONOWO)

Jeśli w tabeli głównej pojawią się dodatkowe spacje, możesz upewnić się, że formuły działają poprawnie, dołączając argument lookup_value (wyszukiwana_wartość) do funkcji TRIM (PRZYCINAĆ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)

=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Rozwiązanie 2: Dodatkowe spacje w tabeli przeglądowej (w kolumnie przeglądowej)

Jeśli w kolumnie wyszukiwania znajdują się dodatkowe spacje – proste sposoby # N / A w formule z VPR nie da się uniknąć. Zamiast VPR Możesz użyć formuły tablicowej z kombinacją funkcji INDEKS (INDEKS), WIĘCEJ EKSPOZYCYJNE (MECZ) č TRIM (PRZYCINAĆ):

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))

=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))

Ponieważ jest to formuła tablicowa, nie zapomnij nacisnąć Ctrl + Shift + Enter zamiast zwykłego Wchodzęaby poprawnie wprowadzić formułę.

Błąd #ARG! w formułach z funkcją WYSZUKAJ.PIONOWO

W większości przypadków Microsoft Excel zgłasza błąd #WARTOŚĆ! (#VALUE!), gdy wartość użyta w formule nie jest zgodna z typem danych. Dotyczący VPR, to zwykle są dwie przyczyny błędu #WARTOŚĆ!.

1. Wartość, której szukasz, jest dłuższa niż 255 znaków

Bądź ostrożny: funkcja VPR nie można wyszukiwać wartości zawierających więcej niż 255 znaków. Jeśli szukana wartość przekroczy ten limit, otrzymasz komunikat o błędzie. #WARTOŚĆ!.

Decyzja: Użyj kilku funkcji INDEKS+DOPASOWANIE (INDEKS + DOPASOWANIE). Poniżej znajduje się formuła, która wystarczy do tego zadania:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))

=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

2. Pełna ścieżka do skoroszytu wyszukiwania nie jest określona

Jeśli pobierasz dane z innego skoroszytu, musisz określić pełną ścieżkę do tego pliku. Dokładniej, nazwę skoroszytu (wraz z rozszerzeniem) należy umieścić w nawiasach kwadratowych [ ], po której następuje nazwa arkusza i wykrzyknik. Cała ta konstrukcja musi być ujęta w apostrofy, na wypadek gdyby nazwa księgi lub arkusza zawierała spacje.

Oto pełna struktura funkcji VPR aby wyszukać w innej książce:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)

=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

Prawdziwa formuła może wyglądać tak:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)

=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Ta formuła wyszuka wartość komórki A2 w kolumnie B na prześcieradle Sheet1 w skoroszycie Nowe ceny i wyodrębnij odpowiednią wartość z kolumny D.

Jeśli jakakolwiek część ścieżki tabeli zostanie pominięta, twoja funkcja VPR nie zadziała i zgłosi błąd #WARTOŚĆ! (nawet jeśli skoroszyt z tabelą przeglądową jest aktualnie otwarty).

Więcej informacji o funkcji VPRodwołując się do innego pliku Excel, zobacz lekcję: Wyszukiwanie innego skoroszytu za pomocą funkcji WYSZUKAJ.PIONOWO.

3. Argument Liczba_kolumny jest mniejsza niż 1

Trudno wyobrazić sobie sytuację, w której ktoś wprowadzi wartość mniejszą niż 1aby wskazać kolumnę, z której należy pobrać wartość. Chociaż jest to możliwe, jeśli wartość tego argumentu jest obliczana przez inną funkcję Excela zagnieżdżoną w VPR.

Tak więc, jeśli zdarzy się, że argument col_index_num (numer_kolumny) mniej niż 1funkcjonować VPR zgłosi również błąd #WARTOŚĆ!.

Jeśli argument col_index_num (liczba_kolumn) jest większa niż liczba kolumn w danej tablicy, VPR zgłosi błąd #ADR! (#SSYL!).

Błąd #NAME? w WYSZUKAJ.PIONOWO

Najprostszy przypadek to pomyłka #NAZWA? (#NAME?) – pojawi się, jeśli przypadkowo wpiszesz nazwę funkcji z błędem.

Rozwiązanie jest oczywiste – sprawdź pisownię!

WYSZUKAJ.PIONOWO nie działa (ograniczenia, zastrzeżenia i decyzje)

Oprócz dość skomplikowanej składni, VPR ma więcej ograniczeń niż jakakolwiek inna funkcja programu Excel. Z powodu tych ograniczeń pozornie proste formuły z VPR często prowadzą do nieoczekiwanych rezultatów. Poniżej znajdziesz rozwiązania dla kilku typowych scenariuszy, w których VPR jest źle.

1. WYSZUKAJ.PIONOWO nie rozróżnia wielkości liter

Funkcjonować VPR nie rozróżnia wielkości liter i akceptuje małe i wielkie litery jako takie same. Dlatego jeśli w tabeli jest kilka elementów, które różnią się tylko wielkością liter, funkcja WYSZUKAJ.PIONOWO zwróci pierwszy znaleziony element, niezależnie od wielkości liter.

Decyzja: Użyj innej funkcji programu Excel, która może przeprowadzić wyszukiwanie pionowe (WYSZUKAJ, SUMPRODUCT, INDEKS i DOPASUJ) w połączeniu z DOKŁADNYA to wyróżnia przypadek. Aby uzyskać więcej informacji, możesz dowiedzieć się z lekcji — 4 sposoby na rozróżnianie wielkości liter w programie Excel w funkcji WYSZUKAJ.PIONOWO.

2. WYSZUKAJ.PIONOWO zwraca pierwszą znalezioną wartość

Jak już wiesz, VPR zwraca wartość z danej kolumny odpowiadającą pierwszemu znalezionemu dopasowaniu. Możesz jednak wyodrębnić drugie, trzecie, czwarte lub dowolne inne powtórzenie żądanej wartości. Jeśli chcesz wyodrębnić wszystkie zduplikowane wartości, będziesz potrzebować kombinacji funkcji INDEKS (INDEKS), NAJMNIEJ (MAŁY) i LINE (WIERSZ).

3. Kolumna została dodana lub usunięta do tabeli

Niestety formuły VPR przestać działać za każdym razem, gdy nowa kolumna zostanie dodana lub usunięta z tabeli przeglądowej. Dzieje się tak, ponieważ składnia VPR wymaga podania pełnego zakresu wyszukiwania i konkretnego numeru kolumny do ekstrakcji danych. Oczywiście zarówno podany zakres, jak i numer kolumny zmieniają się po usunięciu kolumny lub wstawieniu nowej.

Decyzja: I znowu funkcje spieszą z pomocą INDEKS (INDEKS) i WIĘCEJ EKSPOZYCYJNE (MECZ). W formule INDEKS+DOPASOWANIE Oddzielnie definiujesz kolumny wyszukiwania i pobierania, dzięki czemu możesz usuwać lub wstawiać dowolną liczbę kolumn, nie martwiąc się o aktualizowanie wszystkich powiązanych formuł wyszukiwania.

4. Odwołania do komórek są zniekształcone podczas kopiowania formuły

Ten nagłówek wyczerpująco wyjaśnia istotę problemu, prawda?

Decyzja: Zawsze używaj bezwzględnych odwołań do komórek (z symbolem $) na rekordach zakres, na przykład 2 $ A: 100 $ C $ or $ A: $ C. Na pasku formuły możesz szybko zmienić typ linku, klikając F4.

WYSZUKAJ.PIONOWO – praca z funkcjami IFERROR i ISERROR

Jeśli nie chcesz straszyć użytkowników komunikatami o błędach # N / A, #WARTOŚĆ! or #NAZWA?, możesz pokazać pustą komórkę lub własną wiadomość. Możesz to zrobić, umieszczając VPR w funkcję JEŻELI BŁĄD (JEŻELI.BŁĄD) w Excelu 2013, 2010 i 2007 lub użyj kilku funkcji JEŚLI+BŁĄD (JEŻELI+ISERROR) we wcześniejszych wersjach.

WYSZUKAJ.PIONOWO: praca z funkcją JEŻELI.BŁĄD

Składnia funkcji JEŻELI BŁĄD (IFERROR) jest prosty i mówi sam za siebie:

IFERROR(value,value_if_error)

ЕСЛИОШИБКА(значение;значение_если_ошибка)

Oznacza to, że dla pierwszego argumentu wstawiasz wartość, która ma być sprawdzana pod kątem błędu, a dla drugiego argumentu określasz, co zwrócić w przypadku znalezienia błędu.

Na przykład ta formuła zwraca pustą komórkę, jeśli szukana wartość nie zostanie znaleziona:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

Jeśli chcesz wyświetlić własną wiadomość zamiast standardowego komunikatu o błędzie funkcji? VPR, umieść to w cudzysłowie, na przykład:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

WYSZUKAJ.PIONOWO: praca z funkcją ISERROR

Ponieważ funkcja JEŻELI BŁĄD pojawił się w Excelu 2007, podczas pracy we wcześniejszych wersjach będziesz musiał użyć kombinacji IF (JEŚLI) i EOSZIBKA (BŁĄD) w ten sposób:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)

=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Na przykład formuła JEŻELI+BŁĄD+WYSZUKAJ.PIONOWO, podobny do wzoru IFERROR+WYSZUKAJ.PIONOWOpokazane powyżej:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))

=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

To wszystko na dzisiaj. Mam nadzieję, że ten krótki poradnik pomoże Ci uporać się ze wszystkimi możliwymi błędami. VPR i spraw, aby Twoje formuły działały poprawnie.