Pobierz przykładowy skoroszyt
Ten samouczek pokazuje, jak używać Funkcja WYSZUKAJ.POZIOMO w Excelu w Excelu, aby wyszukać wartość.
Przegląd funkcji WYSZUKAJ.POZIOMO
Funkcja HLOOKUP Hlookup oznacza wyszukiwanie poziome. Wyszukuje wartość w górnym wierszu tabeli. Następnie zwraca wartość określoną liczbę wierszy w dół od znalezionej wartości. Działa to tak samo jak przeglądanie, z wyjątkiem tego, że wyszukuje wartości w poziomie, a nie w pionie.
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
Składnia i dane wejściowe funkcji WYSZUKAJ.POZIOMO:
| 1 | = WYSZUKAJ.POZIOMO(wyszukiwana_wartość,tablica_tabela,numer_indeksu_wiersza,wyszukiwanie_zakresu) |
szukana_wartość - Wartość, którą chcesz wyszukać.
tablica_tabeli -Tabela, z której pobierane są dane.
row_index_num – numer wiersza, z którego pobierane są dane.
Zakres wyszukiwania -[opcjonalne] Wartość logiczna wskazująca dopasowanie dokładne lub przybliżone. Domyślnie = TRUE = przybliżone dopasowanie.
Co to jest funkcja WYSZUKAJ.POZIOMO?
Jako jedna ze starszych funkcji w świecie arkuszy kalkulacyjnych, funkcja WYSZUKAJ.POZIOMO służy do wykonywania hpionowa Wyszukiwania. Ma kilka ograniczeń, które często są pokonywane przez inne funkcje, takie jak INDEKS/PODZIELANIE. Ponadto większość tabel jest budowana w pionie, ale kilka razy warto przeszukiwać w poziomie.
Podstawowy przykład
Przyjrzyjmy się próbce danych z dziennika ocen. Omówimy kilka przykładów wyodrębniania informacji dla konkretnych uczniów.
Jeśli chcemy dowiedzieć się, w jakiej klasie znajduje się Bob, napisalibyśmy formułę:
| 1 | =WYSZUKAJ.POZIOMO("Robert"; A1:E3; 2; FAŁSZ) |
Ważną rzeczą do zapamiętania jest to, że szukany przedmiot (Bob) musi znajdować się w pierwszym wierszu naszego zakresu wyszukiwania (A1:E3). Powiedzieliśmy funkcji, że chcemy zwrócić wartość z 2NS wiersz zakresu wyszukiwania, którym w tym przypadku jest wiersz 2. Na koniec wskazaliśmy, że chcemy wykonać dokładne dopasowanie umieszczając False jako ostatni argument. Tutaj odpowiedzią będzie „Czytanie”.
Porada boczna: Możesz również użyć liczby 0 zamiast False jako ostatniego argumentu, ponieważ mają one tę samą wartość. Niektórzy wolą to, ponieważ pisanie jest szybsze. Po prostu wiedz, że oba są akceptowalne.
Przesunięte dane
Aby dodać nieco wyjaśnienia do naszego pierwszego przykładu, wyszukiwany element nie musi znajdować się w pierwszym wierszu arkusza kalkulacyjnego, tylko w pierwszym wierszu zakresu wyszukiwania. Użyjmy tego samego zestawu danych:
Teraz znajdźmy ocenę dla klasy Nauka. Nasza formuła będzie
| 1 | =WYSZUKAJ.POZIOMO("Nauka"; A2:E3; 2; FAŁSZ) |
Jest to nadal poprawna formuła, ponieważ pierwszy wiersz naszego zakresu wyszukiwania to wiersz 2, w którym znajduje się wyszukiwane hasło „Nauka”. Zwracamy wartość z 2NS wiersz zakresu wyszukiwania, którym w tym przypadku jest wiersz 3. Odpowiedź brzmi „A-”.
Użycie symboli wieloznacznych
Funkcja WYSZUKAJ.POZIOMO obsługuje użycie symboli wieloznacznych „*” i „?” podczas wyszukiwania. Załóżmy na przykład, że zapomnieliśmy, jak przeliterować imię Franka, i po prostu chcieliśmy wyszukać imię, które zaczyna się na „F”. Moglibyśmy napisać formułę
| 1 | =WYSZUKAJ.POZIOMO("F*"; A1:E3; 2; FAŁSZ) |
To byłoby w stanie znaleźć imię Frank w kolumnie E, a następnie zwrócić wartość z 2NS wiersz względny. W tym przypadku odpowiedzią będzie „Nauka”.
Dopasowanie niedokładne
W większości przypadków będziesz chciał się upewnić, że ostatni argument w WYSZUKAJ.POZIOMO to Fałsz (lub 0), aby uzyskać dokładne dopasowanie. Jednak jest kilka sytuacji, w których możesz szukać niedokładnego dopasowania. Jeśli masz listę posortowanych danych, możesz również użyć funkcji WYSZUKAJ.POZIOMO, aby zwrócić wynik dla elementu, który jest taki sam lub następny najmniejszy. Jest to często stosowane, gdy mamy do czynienia z rosnącymi zakresami liczb, na przykład w tabeli podatkowej lub premiach prowizyjnych.
Załóżmy, że chcesz znaleźć stawkę podatku dla dochodu wpisanego do komórki H2. Wzór w H4 może mieć postać:
| 1 | =WYSZUKAJ.POZIOMO(H2; B1:F2; 2; PRAWDA) |
Różnica w tej formule polega na tym, że nasz ostatni argument to „Prawda”. W naszym konkretnym przykładzie widzimy, że kiedy nasza osoba wprowadzi dochód w wysokości 45 000 USD, będzie miała stawkę podatkową w wysokości 15%.
Notatka: Chociaż zwykle zależy nam na dokładnym dopasowaniu z argumentem False, jeśli zapomnisz podać 4NS argument w HLOOKUP, wartością domyślną jest True. Może to spowodować nieoczekiwane wyniki, zwłaszcza w przypadku wartości tekstowych.
Dynamiczny rząd
WYSZUKAJ.POZIOMO wymaga podania argumentu mówiącego, z którego wiersza chcesz zwrócić wartość, ale może się zdarzyć, że nie wiesz, gdzie będzie wiersz, lub chcesz zezwolić użytkownikowi na zmianę wiersza, z którego ma powrócić. W takich przypadkach pomocne może być użycie funkcji PODAJ.POZYCJĘ w celu określenia numeru wiersza.
Rozważmy ponownie nasz przykład dziennika ocen, z pewnymi danymi wejściowymi w G2 i G4. Aby uzyskać numer kolumny, moglibyśmy napisać formułę
| 1 | =DOPASUJ(G2, A1:A3; 0) |
To spróbuje znaleźć dokładną pozycję „Stopień” w zakresie A1:A3. Odpowiedź będzie 3. Wiedząc o tym, możemy podłączyć ją do funkcji WYSZUKAJ.POZIOMO i napisać formułę w G6 w następujący sposób:
| 1 | =WYSZUKAJ.POZIOMO(G4, A1:E3; PODAJ.POZYCJĘ(G2, A1:A3; 0); 0) |
Funkcja PODAJr & D wiersz w zakresie A1:E3. Ogólnie rzecz biorąc, otrzymujemy pożądany wynik „C”. Nasza formuła jest teraz dynamiczna, ponieważ możemy zmienić wiersz do przejrzenia lub nazwę do wyszukania.
Ograniczenia WYSZUKAJ.POZIOMO
Jak wspomniano na początku artykułu, największą wadą funkcji WYSZUKAJ.POZIOMO jest to, że wymaga ona znalezienia wyszukiwanego terminu w skrajnej lewej kolumnie zakresu wyszukiwania. Chociaż istnieje kilka wymyślnych sztuczek, które możesz zrobić, aby to przezwyciężyć, powszechną alternatywą jest użycie indeksu i dopasowania. Ta kombinacja zapewnia większą elastyczność, a czasami może być nawet szybszą kalkulacją.
WYSZUKAJ.POZIOMO w Arkuszach Google
Funkcja WYSZUKAJ.POZIOMO działa dokładnie tak samo w Arkuszach Google jak w Excelu:
Dodatkowe uwagi
Użyj funkcji WYSZUKAJ.POZIOMO, aby wykonać wyszukiwanie poziome. Jeśli znasz już funkcję WYSZUKAJ.PIONOWO, funkcja WYSZUKAJ.POZIOMO działa dokładnie w ten sam sposób, z wyjątkiem tego, że wyszukiwanie odbywa się poziomo, a nie pionowo. Funkcja WYSZUKAJ.POZIOMO wyszukuje dokładne dopasowanie (Zakres wyszukiwania = FALSE) lub najbliższe dopasowanie, które jest równe lub mniejsze niż szukana_wartość (Zakres wyszukiwania = TRUE, tylko wartości liczbowe) w pierwszym wierszu table_array. Następnie zwraca odpowiednią wartość, liczbę n wierszy poniżej dopasowania.
Używając funkcji WYSZUKAJ.POZIOMO do znalezienia dokładnego dopasowania, najpierw zdefiniuj wartość identyfikującą, którą chcesz wyszukać jako szukana_wartość. Ta wartość identyfikująca może być numerem SSN, identyfikatorem pracownika, nazwiskiem lub innym unikalnym identyfikatorem.
Następnie definiujesz zakres (zwany tablica_tabeli), który zawiera identyfikatory w górnym wierszu i dowolne wartości, które ostatecznie chcesz wyszukać w wierszach poniżej. WAŻNE: Unikalne identyfikatory muszą znajdować się w górnym rzędzie. Jeśli tak nie jest, musisz albo przenieść wiersz na górę, albo użyć opcji PODAJ.POZYCJĘ/INDEKSU zamiast funkcji WYSZUKAJ.POZIOMO.
Po trzecie, zdefiniuj numer wiersza (row_index) z tablica_tabeli że chcesz wrócić. Pamiętaj, że pierwszy wiersz zawierający unikalne identyfikatory to wiersz 1. Drugi wiersz to wiersz 2 itd.
Na koniec musisz wskazać, czy szukać dokładnego dopasowania (FAŁSZ) lub najbliższego dopasowania (PRAWDA) w Zakres wyszukiwania. Jeśli wybrano opcję dokładnego dopasowania, a dokładne dopasowanie nie zostanie znalezione, zostanie zwrócony błąd (#N/A). Aby formuła zwracała wartość pustą lub „nie znaleziono” lub dowolną inną wartość zamiast wartości błędu (#N/A), użyj funkcji IFERROR z funkcją WYSZUKAJ.POZIOMO.
Aby użyć funkcji WYSZUKAJ.POZIOMO do zwrócenia przybliżonego zestawu dopasowań: Zakres wyszukiwania = PRAWDA. Ta opcja jest dostępna tylko dla wartości liczbowych. Wartości muszą być posortowane w kolejności rosnącej.
Przykłady HLOOKUP w VBA
Możesz także użyć funkcji WYSZUKAJ.POZIOMO w VBA. Rodzaj:application.worksheetfunction.hlookup(wyszukiwana_wartość,tablica_tabela,wiersz_indeks_num,przeszukiwanie_zakresu)
Wykonywanie następujących instrukcji VBA
| 123456 | Range("G2")=Aplikacja.WorksheetFunction.HLookup(Range("C1")),Range("A1:E3"),1)Range("H2")=Aplikacja.WorksheetFunction.HLookup(Range("C1"),Range("A1:E3"),2)Range("I2")=Aplikacja.WorksheetFunction.HLookup(Range("C1"),Range("A1:E3"),3)Range("G3")=Aplikacja.WorksheetFunction.HLookup(Range("D1")),Range("A1:E3"),1)Range("H3")=Aplikacja.WorksheetFunction.HLokup(Zakres("D1")),Zakres("A1:E3"),2)Range("I3")=Aplikacja.WorksheetFunction.HLookup(Range("D1")),Range("A1:E3"),3) |
przyniesie następujące wyniki
W przypadku argumentów funkcji (wyszukiwana_wartość itp.) można wprowadzić je bezpośrednio do funkcji lub zdefiniować zmienne, które mają być używane w zamian.
Wróć do listy wszystkich funkcji w Excelu
