Używanie funkcji Wyszukaj.poziomo i Wyszukaj.pionowo do wyszukiwania rekordów w dużych arkuszach

Kącik użytkowników zaawansowanych

Autor: Colin Wilcox

Przy pracy z dużymi listami w programie Excel można korzystać z funkcji wyszukiwania do szybkiego pobierania poszczególnych rekordów z tych list. W tym artykule wyjaśniono sposób używania dwóch funkcji: WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO.

Dotyczy programu
Microsoft Office Excel 2003
Microsoft Excel 2000 i 2002

Koleżanka poprosiła mnie o pomoc w następującej sprawie:

„Próbuję użyć funkcji wyszukiwania w programie Excel, ale nie udaje mi się”, powiedziała ze złością. „Czy mógłbyś przedstawić je w przystępny sposób?”

Koleżanka zarządza dużą witryną sieci Web. Korzysta z programu Microsoft Access do przechowywania danych związanych z liczbą trafień witryny i zarządzania nimi. Są one następnie importowane do programu Microsoft Excel w celu przeprowadzenia analizy. Aby ułatwić wyszukiwanie danych, koleżanka umieszcza rekordy w kilku mniejszych arkuszach zamiast w jednym większym. Słyszała, że funkcje wyszukiwania mogą zaoszczędzić sporo czasu dzięki odszukiwaniu danych pokrewnych znajdujących się w różnych arkuszach.

Zacznijmy zatem od podstaw: aby znaleźć rekordy pokrewne w dużych arkuszach, można użyć funkcji wyszukiwania. Działanie funkcji wyszukiwania można opisać w ten sposób: „Oto wartość. Przejdź do innej lokalizacji, wyszukaj elementy pasujące do mojej wartości, a następnie pokaż słowa lub liczby znajdujące się w komórce odpowiadającej tej pasującej wartości”. W razie potrzeby można traktować tę trzecią wartość jako wynik wyszukiwania.

Porady znajdujące się w tym artykule wyjaśniają sposób korzystania z dwóch najpopularniejszych funkcji wyszukiwania: WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO. Nazwy funkcji oddają charakter ich działania. Funkcji WYSZUKAJ.PIONOWO można użyć, aby przeszukać jedną lub kilka kolumn informacji, a funkcji WYSZUKAJ.POZIOMO — aby przeszukać jeden lub kilka wierszy informacji.

Korzystanie z funkcji WYSZUKAJ.PIONOWO do przeszukiwania kolumn danych

Aby rozpocząć, pobierz plik przykładowy programu Excel 2002: Dane przykładowe funkcji wyszukiwania (w języku angielskim). W pliku użyto fikcyjnych danych, które demonstrują problem koleżanki. Zawiera on dwa arkusze: Widoki stron i Strony. Arkusz Widoki stron obejmuje zestaw identyfikatorów, które jednoznacznie identyfikują każdą stronę witryny oraz informacje o liczbie trafień każdej ze stron we wrześniu 2002 roku. Arkusz Strony zawiera identyfikatory stron oraz nazwy stron odpowiadających poszczególnym identyfikatorom.

Identyfikatory strony są wyświetlane w obu arkuszach, ponieważ źródłowa baza danych korzysta ze znormalizowanej struktury danych. W strukturze tego typu identyfikatory umożliwiają użytkownikom wyszukiwanie danych dla danej strony. Wprowadzenie na temat znormalizowanych struktur danych znajduje się w artykule Projektowanie baz danych programu Access przy użyciu zwykłych formularzy i programu Excel (w języku angielskim).

Ponieważ dane znajdują się w kolumnach, użyjemy funkcji WYSZUKAJ.PIONOWO do wpisania identyfikatora strony w pierwszym arkuszu i uzyskania odpowiedniej nazwy strony z drugiego arkusza. Wykonaj następujące czynności:

  1. W arkuszu Widoki stron kliknij komórkę E3 i wpisz WYSZUKAJ.PIONOWO.
  2. W komórce E4 wpisz Wynik.
  3. Kliknij komórkę F4 i wpisz tę formułę w komórce lub na pasku formuły:

=WYSZUKAJ.PIONOWO(F3;Strony!A2:B39;2;FAŁSZ)

 Note   W komórce F4 zostanie wyświetlony błąd #N/D , ponieważ w komórce F3, spodziewana jest wartość, a komórka jest pusta. W kolejnym kroku dodasz wartość do komórki F3. Aby uzyskać więcej informacji na temat usuwania błędów #N/D, zobacz artykuł Naprawa błędu #N/D.

  1. Skopiuj wartość z komórki A4 do komórki F3 i naciśnij klawisz ENTER. W komórce F4 pojawi się wartość Strona główna.
  2. Powtórz kroki 3 i 4, używając wartości z komórki A5. W komórce F4 pojawi się wartość Komiksy i humor.

Uzyskano w ten sposób informacje o witrynach najczęściej odwiedzanych przez użytkowników bez konieczności przechodzenia do drugiego arkusza. To prawdziwa wartość funkcji wyszukiwania. Można ich używać do wyszukiwania rekordów w dużych zestawach danych, oszczędzając sporo czasu i wysiłku.

Opis części funkcji

Funkcja użyta w poprzedniej sekcji spowodowała wykonanie kilku osobnych akcji. Na poniższym rysunku przedstawiono każdą akcję:

Akcje wykonywane przez poszczególne elementy funkcji WYSZUKAJ.PIONOWO.

W poniższej tabeli przedstawiono i opisano argumenty używane z tą funkcją. W razie potrzeby można również skorzystać z informacji, jak poradzić sobie z błędami #ARG i #ADR, które mogą pojawiać się podczas używania tych funkcji. Są one niezbędne, aby korzystać z tej funkcji. Funkcja WYSZUKAJ.POZIOMO używa tej samej składni i argumentów.

Element Wymagany? Zastosowanie
=WYSZUKAJ.PIONOWO() =WYSZUKAJ.POZIOMO() Tak Nazwa funkcji. Tak jak w przypadku wszystkich funkcji w programie Excel nazwę funkcji należy poprzedzić znakiem równości (=) oraz umieścić wymagane informacje (czyli w języku specjalistów: argumenty) w nawiasach za nazwą funkcji. W tym przypadku do oddzielania parametrów lub argumentów należy użyć średników.
F3 Tak Wyszukiwany termin: słowo lub wartość, którą chcesz wyszukać. W tym przypadku wyszukiwanym terminem jest wartość wpisana w komórce F3. Dodatkowo można osadzić jeden z numerów identyfikatora strony bezpośrednio w funkcji. W Pomocy programu Excel ta część funkcji to szukana_wartość.

Jeśli szukana wartość nie zostanie określona lub zostanie podane odwołanie do pustej komórki, program Excel wyświetli komunikat o błędzie #N/D.

Strony!A2:B39 Tak Zakres komórek, który ma zostać przeszukany. W tym przypadku komórki znajdują się w innym arkuszu, więc nazwę arkusza (Strony) poprzedzono wartościami zakresu (A2:B39). Wykrzyknik (!) rozdziela odwołanie do arkusza od odwołania do komórek. Jeśli chcesz tylko przeszukać zakres znajdujący się na tej samej stronie co funkcja, usuń nazwę arkusza i wykrzyknik.

W tym miejscu funkcji można także użyć nazwanego zakresu. Jeśli na przykład do zakresu komórek w arkuszu Strony przypisano nazwę „Dane”, można użyć parametru 'Strony'!Dane. W Pomocy programu Excel ta część funkcji to wartość tabela_tablica.

W przypadku użycia wartości PRAWDA wyszukiwania zakresu należy posortować wartości w pierwszej kolumnie argumentu tabela_tablica w kolejności rosnącej. W innym przypadku funkcja nie będzie zwracać poprawnych wyników.

2 Tak Kolumna w zdefiniowanym zakresie komórek zawierająca wartości, które należy odszukać. Na przykład kolumna B w arkuszu Strony zawiera nazwy stron, które należy odszukać. Jako że kolumna B jest drugą kolumną w zdefiniowanym zakresie komórek (A2:B39), funkcja używa wartości 2. Gdyby zdefiniowany zakres obejmował trzecią kolumnę i znajdowałyby się w niej wartości, które należy odszukać, należałoby użyć wartości 3 i tak dalej.

Pamiętaj, że fizyczna pozycja kolumny na arkuszu nie ma znaczenia. Jeśli zakres komórek zaczyna się od kolumny R i kończy na kolumnie T, użyjesz wartości 1, aby odwołać się do kolumny R, wartości 2, aby odwołać się do kolumny R i tak dalej.

W Pomocy programu Excel ta część funkcji to wartość nr_indeksu_kolumny. W przypadku użycia funkcji WYSZUKAJ.POZIOMO ta wartość nosi nazwę nr_indeksu_wiersza w Pomocy programu Excel i należy postępować zgodnie z tymi samymi wskazówkami co w przypadku poprzedniej funkcji.

 Note   W przypadku użycia nieprawidłowej wartości w tym argumencie program Excel wyświetli komunikat o błędzie. Może to być jeden z następujących błędów:

  • Jeśli wartość jest mniejsza niż 1, program Excel wyświetli komunikat #ARG!. Aby rozwiązać ten problem, wpisz wartość 1 lub większą. Aby uzyskać więcej informacji na temat błędów #ARG!, zobacz artykuł Naprawa błędu #ARG!.
  • Jeśli wartość przekroczy liczbę kolumn w zakresie komórek, program Excel wyświetli błąd #ADR!, ponieważ formuła nie może odnosić się do określonej liczby kolumn. Aby uzyskać więcej informacji na temat rozwiązywania problemów z błędami #ADR!, zobacz artykuł Naprawa błędu #ADR!.
Fałsz Opcjonalny Dokładne dopasowanie. Jeśli użyjesz wartości Fałsz, funkcja WYSZUKAJ.PIONOWO zwróci dokładne dopasowanie. Jeśli program Excel nie może odnaleźć dokładnego dopasowania, zostanie wyświetlony komunikat o błędzie #N/D. Aby uzyskać więcej informacji na temat rozwiązywania problemów z błędami #N/D, zobacz artykuł Naprawa błędu #N/D.

Jeśli ustawisz wartość PRAWDA lub zostawisz puste miejsce, funkcja WYSZUKAJ.PIONOWO zwróci wynik najbliższy wyszukiwanego terminu. Jeśli ustawisz wartość PRAWDA, musisz posortować wartości w pierwszej kolumnie tabeli-tablicy w kolejności rosnącej.

W Pomocy programu Excel ta część funkcji to wartość przeszukiwany_zakres.

Ogólne zasady korzystania z funkcji WYSZUKAJ.PIONOWO

Podczas korzystania z funkcji WYSZUKAJ.PIONOWO należy pamiętać o następujących kwestiach:

  • Jeśli chcesz, aby funkcja zwracała dokładne dopasowania, musisz posortować wartości w tabeli-tablicy w kolejności rosnącej. W przeciwnym razie funkcja nie będzie działać.
  • Funkcja rozpoczyna wyszukiwania od komórki położonej w lewym górnym rogu zdefiniowanego zakresu komórek i przeszukuje kolumny znajdujące się po prawej stronie punktu początkowego.
  • Wszystkie argumenty muszą być oddzielane od siebie średnikami.

Korzystanie z funkcji WYSZUKAJ.POZIOMO do przeszukiwania wierszy danych

W krokach opisanych w poprzedniej sekcji była używana funkcja WYSZUKAJ.PIONOWO, ponieważ dane znajdowały się w kolumnach. W krokach w tej sekcji wyjaśniono sposób korzystania z funkcji WYSZUKAJ.POZIOMO do wyszukiwania danych w jednym lub kilku wierszach.

  1. W arkuszu Strony skopiuj dane z zakresu A2 do B39.
  2. Przewiń arkusz do początku, kliknij prawym przyciskiem myszy komórkę D2, a następnie kliknij polecenie Wklej specjalnie.
  3. W oknie dialogowym Wklejanie specjalne wybierz pozycję Transpozycja, a następnie kliknij przycisk OK. Program Excel wklei dane do dwóch wierszy, od komórki D2 do komórki AO3.
  4. W arkuszu Widoki stron wpisz WYSZUKAJ.POZIOMO w komórce E6, wpisz Wynik w komórce E7, a następnie wpisz następującą formułę do komórki F7:

=WYSZUKAJ.POZIOMO(F6;Strony!D2:AO3;2;FAŁSZ)

  1. W komórce F6 wpisz identyfikator z komórki A4, a następnie naciśnij klawisz ENTER. W komórce F6 zostanie wyświetlona wartość Strona startowa. Uzyskano wynik tego samego typu, ale dzięki wyszukiwaniu zestawu wierszy, a nie kolumn.

Funkcja WYSZUKAJ.POZIOMO korzysta z tych samych argumentów co funkcja WYSZUKAJ.PIONOWO. Jednak zamiast deklarowania kolumny zawierającej szukane wartości należy zadeklarować wiersz.

Przyjrzymy się teraz ważnej zasadzie stosowanej w obu funkcjach. Przejdź do arkusza Strony i wykonaj następujące czynności:

  1. W komórkach od D4 do M4 wpisz dowolne wartości. Możesz wpisać co tylko chcesz — wpisz do tych komórek dowolny tekst lub liczby.
  2. W arkuszu Widoki stron zmień formułę WYSZUKAJ.POZIOMO w następujący sposób:

=WYSZUKAJ.POZIOMO(F6;Strony!D2:AO4;3;FAŁSZ)

Kiedy skończysz zmienianie formuły, pojawi się wartość wpisana wcześniej w komórce D4. Oto zasada, o której należy pamiętać: szukana wartość nie musi znajdować się w komórce obok pasującej wartości. Może być w dowolnej liczbie kolumn po prawej stronie pasującej wartości lub w dowolnej liczbie wierszy poniżej pasującej wartości. Po prostu upewnij się, że rozwinięto argumenty tabela_tablica i nr_indeksu_kolumny lub nr_indeksu_wiersza, aby obejmowały wartości, które chcesz odnaleźć.

Ogólne zasady korzystania z funkcji WYSZUKAJ.POZIOMO

Podczas korzystania z funkcji WYSZUKAJ.POZIOMO należy pamiętać o następujących kwestiach:

  • Funkcja rozpoczyna wyszukiwania od komórki położonej w lewym górnym rogu zdefiniowanego zakresu komórek i przeszukuje wiersze znajdujące się poniżej i po prawej stronie punktu początkowego.
  • Wszystkie argumenty muszą być oddzielane od siebie średnikami.
  • Jeśli chcesz, aby funkcja zwracała dokładne dopasowania, musisz posortować wartości danych w kolejności rosnącej. Tak, sortowanie w poziomie jest możliwe. Aby to zrobić, wykonaj następujące czynności:
    1. W arkuszu Strony kliknij komórkę D2.
    2. W menu Dane kliknij polecenie Sortuj.
    3. W oknie dialogowym Sortowanie kliknij przycisk Opcje.
    4. W oknie dialogowym Opcje sortowania kliknij opcję Sortuj od lewej do prawej, a następnie naciśnij przycisk OK.
    5. W oknie dialogowym Sortowanie kliknij przycisk OK, aby posortować dane.

W następnym artykule dla użytkowników zaawansowanych

W następnym artykule dla użytkowników zaawansowanych, Inne sposoby korzystania z funkcji Wyszukaj.pionowo i Wyszukaj.poziomo (w języku angielskim), wyjaśniono, w jaki sposób:

  • używać etykietek w pisaniu funkcji;
  • używać kilku względnych i bezwzględnych odwołań do komórek jednocześnie, aby zwracać wiele rekordów;
  • debugować swoje funkcje;
  • używać Kreatora odnośników. Kreator automatyzuje proces wyszukiwania danych, ale korzysta z funkcji INDEKS i PODAJ.POZYCJĘ, a nie z funkcji WYSZUKAJ.POZIOMO i WYSZUKAJ.PIONOWO.

Więcej informacji

  • Aby uzyskać więcej informacji na temat korzystania z funkcji WYSZUKAJ.POZIOMO i WYSZUKAJ.PIONOWO wraz z przykładami kodu, zobacz Pomoc programu Excel.

O autorze

Colin Wilcox jest autorem artykułów w zespole Pomocy pakietu Office. Poza pisaniem artykułów do działu Kącik użytkowników zaawansowanych Colin tworzy artykuły i samouczki do programu Microsoft Data Analyzer.


 
 
Dotyczy:
Excel 2003