Jak naprawić błąd #N/D! w funkcji WYSZUKAJ.PIONOWO

W tym temacie opisano najbardziej typowe powody niepowodzenia działania funkcji WYSZUKAJ.PIONOWO.

Porada : Zobacz Podręczna karta informacyjna: porady dotyczące rozwiązywania problemów z funkcją WYSZUKAJ.PIONOWO, gdzie opisano najbardziej typowe powody występowania problemów z funkcją WYSZUKAJ.PIONOWO w przydatnym pliku PDF (Portable Document Format). Plik PDF możesz udostępnić innym osobom lub wydrukować go do własnego użytku.

Problem: szukana wartość nie znajduje się w pierwszej kolumnie argumentu tabela_tablica

Jednym z największych ograniczeń funkcji WYSZUKAJ.PIONOWO jest to, że może ona wyszukiwać wyłącznie wartości znajdujące się w skrajnej lewej kolumnie tabeli-tablicy. Dlatego też, jeśli szukana wartość nie znajduje się w pierwszej kolumnie tablicy, zobaczysz błąd #N/D!.

W poniższej tabeli chcemy pobrać liczbę sprzedanych jednostek jarmużu.

Błąd #N/D! w funkcji WYSZUKAJ.PIONOWO: szukana wartość nie znajduje się w pierwszej kolumnie w tabeli-tablicy

Występuje błąd, ponieważ szukana wartość „Jarmuż” znajduje się w drugiej kolumnie (Warzywa) w argumencie tabela_tablica A2:C10, a program Excel szuka jej w kolumnie A, a nie B.

Rozwiązanie: Możesz spróbować to naprawić, dopasowując funkcję WYSZUKAJ.PIONOWO tak, aby odwoływała się do poprawnej kolumny. Jeśli jest to niemożliwe, spróbuj przenieść kolumny. Może to być bardzo niepraktyczne w przypadku dużych lub złożonych arkuszy kalkulacyjnych, w których wartości komórek są wynikami innych obliczeń. Mogą też być inne logiczne powody, dla których po prostu nie można dowolnie przenosić kolumn. W ramach rozwiązania można użyć połączenia funkcji INDEKS i PODAJ.POZYCJĘ, które będą wyszukiwać wartość w kolumnie niezależnie od jej położenia w tabeli wyszukiwania.

Używanie funkcji INDEKS/PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO

Funkcji INDEKS/PODAJ.POZYCJĘ można używać, gdy funkcja WYSZUKAJ.PIONOWO nie spełnia wymagań. Największą zaletą połączenia funkcji INDEKS/PODAJ.POZYCJĘ jest możliwość wyszukiwania wartości w kolumnie w dowolnej lokalizacji w tabeli wyszukiwania. Funkcja INDEKS zwraca wartość z konkretnej tabeli/konkretnego zakresu na podstawie jej położenia, a funkcja PODAJ.POZYCJĘ zwraca względną pozycję wartości w tabeli/zakresie. Użycie funkcji INDEKS i PODAJ.POZYCJĘ w jednej formule umożliwia wyszukiwanie wartości w tabeli/tablicy przez określenie położenia względnego wartości w tabeli/tablicy.

Używanie funkcji INDEKS/PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO daje kilka korzyści:

  • W przypadku zastosowania funkcji INDEKS i PODAJ.POZYCJĘ zwracana wartość nie musi znajdować się w tej samej kolumnie co kolumna wyszukiwania, w przeciwieństwie do funkcji WYSZUKAJ.PIONOWO, w przypadku której zwracana wartość musi znajdować się w określonym zakresie. Dlaczego jest to ważne? W przypadku używania funkcji WYSZUKAJ.PIONOWO musisz znać numer kolumny zawierającej zwracaną wartość. Wydaje się, że to drobnostka, ale może się to okazać kłopotliwe, jeśli masz dużą tabelę i musisz policzyć kolumny. Ponadto w razie dodania/usunięcia kolumny w tabeli należy ponownie je przeliczyć i zaktualizować argument nr_indeksu_kolumny. W przypadku funkcji INDEKS i PODAJ.POZYCJĘ liczenie nie jest wymagane, ponieważ kolumna wyszukiwania różni się od kolumny zwierającej zwracaną wartość.

  • Korzystając z funkcji INDEKS i PODAJ.POZYCJĘ, możesz określić wiersz lub kolumnę w tablicy, a nawet oba te elementy. Oznacza to, że możesz wyszukiwać wartości pionowo i poziomo.

  • Funkcji INDEKS i PODAJ.POZYCJĘ można użyć do wyszukiwania wartości w dowolnej kolumnie. W przeciwieństwie do funkcji WYSZUKAJ.PIONOWO, za pomocą której można wyszukać tylko wartość w pierwszej kolumnie tabeli, funkcje INDEKS i PODAJ.POZYCJĘ będą działać, jeśli szukana wartość znajduje się w pierwszej kolumnie, ostatniej kolumnie lub w dowolnym miejscu między nimi.

  • Funkcje INDEKS i PODAJ.POZYCJĘ zapewniają elastyczność w zakresie tworzenia dynamicznych odwołań do kolumny zawierającej zwracaną wartość. Oznacza to, że możesz dodawać kolumny do tabeli, a działanie funkcji INDEKS i PODAJ.POZYCJĘ nie zakończy się niepowodzeniem. Z kolei działanie funkcji WYSZUKAJ.PIONOWO zakończy się niepowodzeniem, jeśli dodasz kolumnę do tabeli, ponieważ zawiera ona statyczne odwołanie do tabeli.

  • Funkcje INDEKS i PODAJ.POZYCJĘ oferują większą elastyczność w zakresie dopasowań. Funkcje INDEKS i PODAJ.POZYCJĘ mogą wyszukać dokładne dopasowanie, a także wartość większą lub mniejszą od szukanej wartości. Funkcja WYSZUKAJ.PIONOWO będzie wyszukiwać tylko najbliższe dopasowanie do wartości (domyślnie) lub dokładną wartość. Ponadto funkcja WYSZUKAJ.PIONOWO domyślnie zakłada, że pierwsza kolumna w tabeli-tablicy jest sortowana alfabetycznie. Jeśli tabela nie jest skonfigurowana w ten sposób, funkcja WYSZUKAJ.PIONOWO zwróci pierwsze najbliższe dopasowanie w tabeli, które może nie być szukaną wartością.

Składnia

Aby skonstruować składnię dla funkcji INDEKS/PODAJ.POZYCJĘ, musisz użyć argumentu tablica/odwołanie z funkcji INDEKS i zagnieździć składnię funkcji PODAJ.POZYCJĘ wewnątrz tego argumentu. Wygląda to następująco:

=INDEKS(tablica lub odwołanie;PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tablica;[typ_porównania])

Użyjmy funkcji INDEKS/PODAJ.POZYCJĘ, aby zastąpić funkcję WYSZUKAJ.PIONOWO w powyższym przykładzie. Składnia wygląda następująco:

=INDEKS(C2:C10;PODAJ.POZYCJĘ(B13;B2:B10;0))

Mówiąc prosto, oznacza to:

=INDEKS(Chcę zwrócenia wartości z zakresu C2:C10, która będzie pasować do wartości — PODAJ.POZYCJĘ(Jarmuż, która znajduje się gdzieś w tablicy B2:B10, przy czym zwrócona wartość to pierwsza wartość odpowiadająca wartości Jarmuż))

Funkcji INDEKS i PODAJ.POZYCJĘ można używać w zastępstwie funkcji WYSZUKAJ.PIONOWO

Formuła wyszukuje pierwszą wartość w zakresie C2:C10, która odpowiada wartości Jarmuż (w komórce B7) i zwraca wartość w komórce C7 (100), która jest pierwszą wartością pasującą do wartości Jarmuż.

Problem: nie można znaleźć dokładnego dopasowania

Gdy argument przeszukiwany_zakres ma wartość FAŁSZ, a funkcja WYSZUKAJ.PIONOWO nie może znaleźć dokładnego dopasowania w danych, zwróci ona błąd #N/D!.

Rozwiązanie: Jeśli masz pewność, że odpowiednie dane istnieją w arkuszu kalkulacyjnym, a funkcja WYSZUKAJ.PIONOWO nie może ich odnaleźć, upewnij się, że komórki, do których się odwołujesz, nie mają ukrytych spacji ani znaków niedrukowanych. Upewnij się też, że komórki mają poprawny typ danych. Na przykład komórki z liczbami powinny być sformatowane jako liczbowe, a nie tekstowe.

Użyj funkcji OCZYŚĆ lub USUŃ.ZBĘDNE.ODSTĘPY, aby oczyścić dane w komórkach.

Problem: szukana wartość jest mniejsza niż najmniejsza wartość w tablicy

Jeśli argument przeszukiwany_zakres ma wartość PRAWDA, a szukana wartość jest mniejsza niż najmniejsza wartość w tablicy, zobaczysz błąd #N/D!. Wartość PRAWDA spowoduje wyszukiwanie przybliżonego dopasowania w tablicy i zwrócenie najbliższej wartości mniejszej od szukanej wartości.

W poniższym przykładzie szukana wartość to 100, ale w zakresie B2:C10 nie ma wartości mniejszych od 100, dlatego zostaje wyświetlony błąd.

Błąd N/D! w funkcji WYSZUKAJ.PIONOWO, gdy szukana wartość jest mniejsza niż najmniejsza wartość w tablicy

Rozwiązanie:

  • Wymagana jest prawidłowa szukana wartość.

  • Jeśli nie możesz zmienić szukanej wartości i potrzebujesz większej swobody w zakresie dopasowywania wartości, rozważ użycie funkcji INDEKS/PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO. Z funkcjami INDEKS/PODAJ.POZYCJĘ możesz wyszukać wartości większe, mniejsze lub równe szukanej wartości. Aby uzyskać więcej informacji o używaniu funkcji INDEKS/PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO, zobacz poprzednią sekcję tego tematu.

Problem: kolumna wyszukiwania nie jest sortowana w kolejności rosnącej

Jeśli argument przeszukiwany_zakres ma wartość PRAWDA, a jedna z kolumn wyszukiwania nie jest sortowana w kolejności rosnącej (A–Z), zobaczysz błąd #N/D!.

Rozwiązanie:

  • Zmień funkcję WYSZUKAJ.PIONOWO, aby wyszukiwała dokładne dopasowanie. Aby to zrobić, ustaw argument przeszukiwany_zakres na wartość FAŁSZ. W przypadku wartości FAŁSZ nie ma konieczności sortowania.

  • Użyj funkcji INDEKS/PODAJ.POZYCJĘ, aby wyszukać wartość w niesortowanej tabeli.

Problem: wartość jest dużą liczbą zmiennoprzecinkową

Jeśli w komórkach są wartości czasu lub duże liczby dziesiętne, program Excel zwraca błąd #N/D! z powodu precyzji zmiennoprzecinkowej. Liczby zmiennoprzecinkowe to cyfry następujące po separatorze dziesiętnym. (Zwróć uwagę, że program Excel przechowuje wartości godziny jako liczby zmiennoprzecinkowe). Program Excel nie może przechowywać liczb o dużych wartościach zmiennoprzecinkowych. Więc aby funkcja działała prawidłowo, liczby zmiennoprzecinkowe muszą zostać zaokrąglone do 5 miejsc dziesiętnych.

Rozwiązanie: Skróć liczby, zaokrąglając je do maksymalnie pięciu miejsc dziesiętnych, za pomocą funkcji ZAOKR.

Masz pytanie dotyczące konkretnej funkcji?

Zadaj pytanie na forum społeczności programu Excel

Pomoc w ulepszaniu programu Excel

Masz sugestie dotyczące możliwości ulepszenia następnej wersji programu Excel? Jeśli tak, zapoznaj się z tematami w witrynie UserVoice dotyczącej programu Excel.

Zobacz też

Naprawianie błędu #N/D!

WYSZUKAJ.PIONOWO: Nigdy więcej błędów #N/D!

Funkcje WYSZUKAJ.POZIOMO, WYSZUKAJ.PIONOWO, WYSZUKAJ zwracają nieprawidłowe wartości w programie Excel

Działania arytmetyczne na liczbach zmiennoprzecinkowych mogą zwracać niedokładne wyniki w programie Excel

Podręczna karta informacyjna: podsumowanie wiadomości o funkcji WYSZUKAJ.PIONOWO

WYSZUKAJ.PIONOWO, funkcja

Omówienie formuł w programie Excel

Jak unikać niepoprawnych formuł

Wykrywanie błędów w formułach przy użyciu opcji sprawdzania błędów

Wszystkie funkcje programu Excel (alfabetycznie)

Wszystkie funkcje programu Excel (według kategorii)

Rozwijaj swoje umiejętności
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×