W tym temacie opisano najczęstsze przyczyny błędnego wyniku funkcji WYSZUKAJ.PIONOWO oraz przedstawiono sugestie dotyczące używania funkcji INDEKS i PODAJ.POZYCJĘ .
Porada: Zapoznaj się również z szybką kartą referencyjną: porady dotyczące rozwiązywania problemów z funkcją WYSZUKAJ.PIONOWO, która przedstawia typowe przyczyny problemów z #NA w wygodnym pliku PDF. 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 ograniczeń funkcji WYSZUKAJ.PIONOWO jest to, że może ona wyszukiwać wartości tylko w lewej kolumnie w tablicy tabeli. Jeśli wartość odnośnika nie znajduje się w pierwszej kolumnie tablicy, zostanie wyświetlony błąd #N/D!.
W poniższej tabeli chcemy pobrać liczbę sprzedanych jednostek jarmużu.
Błąd #N/D! wynika z tego, że wartość odnośnika „Jarmuż” jest wyświetlana w drugiej kolumnie (Tworzenie) argumentu table_array A2:C10. W tym przypadku program Excel szuka go w kolumnie A, a nie w kolumnie 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 nie jest to możliwe, spróbuj przenieść kolumny. Może to być również wysoce niepraktyczne, jeśli masz duże lub złożone arkusze kalkulacyjne, w których wartości komórek są wynikami innych obliczeń lub istnieją inne logiczne powody, dla których po prostu nie można 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. Zobacz następną sekcję.
Zamiast tego rozważ użycie funkcji INDEKS/PODAJ.POZYCJĘ
INDEKS i PODAJ.POZYCJĘ są dobrymi opcjami w wielu przypadkach, w których funkcja WYSZUKAJ.PIONOWO nie spełnia Twoich potrzeb. Kluczową zaletą funkcji INDEKS/PODAJ.POZYCJĘ jest możliwość wyszukania wartości w kolumnie w dowolnej lokalizacji w tabeli odnośników. Funkcja INDEKS zwraca wartość z określonej tabeli/zakresu zgodnie z ich położeniem. Funkcja PODAJ.POZYCJĘ zwraca względną pozycję wartości w tabeli/zakresie. Użyj funkcji INDEKS i PODAJ.POZYCJĘ w formule, aby wyszukać wartość w tabeli/tablicy, określając względną pozycję wartości w tabeli/tablicy.
Zamiast funkcji WYSZUKAJ.PIONOWO istnieje kilka korzyści z używania funkcji INDEKS/PODAJ.POZYCJĘ:
-
W przypadku funkcji INDEKS i PODAJ.POZYCJĘ wartość zwracana nie musi znajdować się w tej samej kolumnie co kolumna odnośnika. Różni się to od funkcji WYSZUKAJ.PIONOWO, w której wartość zwracana musi należeć do określonego zakresu. Dlaczego jest to ważne? W przypadku używania funkcji WYSZUKAJ.PIONOWO musisz znać numer kolumny zawierającej zwracaną wartość. Chociaż może się to nie wydawać trudne, może być kłopotliwe, gdy masz dużą tabelę i musisz zliczyć liczbę kolumn. Ponadto jeśli dodasz/usuniesz kolumnę w tabeli, musisz spróbować i zaktualizować argument col_index_num. W przypadku funkcji INDEKS i PODAJ.POZYCJĘ liczenie nie jest wymagane, ponieważ kolumna wyszukiwania różni się od kolumny zwierającej zwracaną wartość.
-
Za pomocą funkcji INDEKS i PODAJ.POZYCJĘ można określić wiersz lub kolumnę w tablicy albo określić 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, w której można wyszukać tylko wartość w pierwszej kolumnie w tabeli, funkcja INDEKS i PODAJ.POZYCJĘ będą działać, jeśli wartość odnośnika znajduje się w pierwszej kolumnie, ostatniej lub w dowolnym miejscu między nimi.
-
INDEKS i PODAJ.POZYCJĘ oferują elastyczność tworzenia dynamicznego odwołania do kolumny zawierającej wartość zwracaną. Oznacza to, że można dodawać kolumny do tabeli bez przerywania funkcji INDEKS i PODAJ.POZYCJĘ. Z drugiej strony funkcja WYSZUKAJ.PIONOWO przerywa działanie, jeśli trzeba dodać kolumnę do tabeli, ponieważ tworzy ona statyczne odwołanie do tabeli.
-
Funkcje INDEKS i PODAJ.POZYCJĘ oferują większą elastyczność w zakresie dopasowań.Funkcja INDEKS i PODAJ.POZYCJĘ może znaleźć dokładne dopasowanie lub wartość większą lub mniejszą niż wartość odnośnika. 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 utworzyć składnię dla funkcji INDEKS/PODAJ.POZYCJĘ, należy użyć argumentu tablicy/odwołania z funkcji INDEKS i zagnieździć wewnątrz niej składnię MATCH. Ma to postać:
=INDEKS(tablica lub odwołanie;PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tablica;[typ_porównania])
Użyj funkcji INDEKS/PODAJ.POZYCJĘ, aby zastąpić funkcję WYSZUKAJ.PIONOWO z powyższego przykładu. Składnia wygląda następująco:
=INDEKS(C2:C10;PODAJ.POZYCJĘ(B13;B2:B10;0))
Mówiąc prosto, oznacza to:
=INDEKS(zwróć wartość z C2:C10, która będzie zgodna(Jarmuż, który znajduje się gdzieś w tablicy B2:B10, w której zwracana wartość jest pierwszą wartością odpowiadającą jarmużowi))
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
Jeśli argument range_lookup ma wartość FALSE, a funkcja WYSZUKAJ.PIONOWO nie może znaleźć dokładnego dopasowania w danych, zwraca błąd #N/D!.
Rozwiązanie: jeśli masz pewność, że odpowiednie dane istnieją w arkuszu kalkulacyjnym, a funkcja WYSZUKAJ.PIONOWO ich nie wychwyci, poświęć trochę czasu, aby sprawdzić, czy komórki, do których istnieją odwołania, nie mają ukrytych spacji ani znaków niedrukowania. Upewnij się również, że komórki są zgodne z prawidłowym typem danych. Na przykład komórki z liczbami powinny być sformatowane jako Liczba, a nie tekst.
Ponadto rozważ użycie funkcji CLEAN lub TRIM w celu oczyszczenia danych w komórkach.
Problem: szukana wartość jest mniejsza niż najmniejsza wartość w tablicy
Jeśli argument range_lookup ma wartość PRAWDA, a wartość odnośnika jest mniejsza niż najmniejsza wartość w tablicy, zostanie wyświetlony 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.
Rozwiązanie:
-
Wymagana jest prawidłowa szukana wartość.
-
Jeśli nie możesz zmienić wartości odnośnika i potrzebujesz większej elastyczności w przypadku pasujących wartości, rozważ użycie funkcji INDEKS/PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO, zobacz sekcję powyżej w tym artykule. 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 range_lookup ma wartość TRUE, a jedna z kolumn odnośników nie jest sortowana w kolejności rosnącej (A–Z), zostanie wyświetlony 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 sortowanie nie jest konieczne.
-
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. (Program Excel przechowuje wartości czasu jako liczby zmiennoprzecinkowe). Program Excel nie może przechowywać liczb z bardzo dużymi liczbami zmiennoprzecinkowymi, więc aby funkcja działała poprawnie, liczby zmiennoprzecinkowe należy zaokrąglić do 5 miejsc dziesiętnych.
Rozwiązanie: Skróć liczby, zaokrąglając je do maksymalnie pięciu miejsc dziesiętnych, za pomocą funkcji ZAOKR.
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w Społecznościach.
