Analiza danych programu Excel - funkcje wyszukiwania

Możesz użyć funkcji programu Excel, aby -

Znajdź wartości w zakresie danych - WYSZUKAJ.PIONOWO i WYSZUKAJ.PIONOWO
Uzyskaj wartość lub odwołanie do wartości z tabeli lub zakresu - INDEKS
Uzyskaj względną pozycję określonego elementu w zakresie komórek - PODAJ

Możesz również łączyć te funkcje, aby uzyskać wymagane wyniki na podstawie posiadanych danych wejściowych.

Korzystanie z funkcji WYSZUKAJ.PIONOWO

Składnia funkcji WYSZUKAJ.PIONOWO to

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Gdzie

lookup_value- to wartość, którą chcesz sprawdzić. Lookup_value może być wartością lub odwołaniem do komórki. Lookup_value musi znajdować się w pierwszej kolumnie zakresu komórek określonego w table_array
table_array- to zakres komórek, w których funkcja WYSZUKAJ.PIONOWO wyszuka wartość lookup_value i wartość zwracaną. table_array musi zawierać
- lookup_value w pierwszej kolumnie i
- wartość zwracana, którą chcesz znaleźć
  
  Note- Pierwsza kolumna zawierająca lookup_value może być sortowana w kolejności rosnącej lub nie. Jednak wynik będzie oparty na kolejności w tej kolumnie.
col_index_num- to numer kolumny w tablicy table_array, która zawiera zwracaną wartość. Liczby zaczynają się od 1 dla skrajnej lewej kolumny tablicy tablicowej
range_lookup- to opcjonalna wartość logiczna określająca, czy funkcja WYSZUKAJ.PIONOWO ma znaleźć dokładne dopasowanie, czy przybliżone dopasowanie. wyszukiwanie_zakresu może być
- pominięty, w takim przypadku przyjmuje się, że jest PRAWDA i funkcja WYSZUKAJ.PIONOWO próbuje znaleźć przybliżone dopasowanie
- PRAWDA, w takim przypadku WYSZUKAJ.PIONOWO próbuje znaleźć przybliżone dopasowanie. Innymi słowy, jeśli dokładne dopasowanie nie zostanie znalezione, zwracana jest następna co do wielkości wartość mniejsza niż wartość lookup_value
- FAŁSZ, w takim przypadku WYSZUKAJ.PIONOWO próbuje znaleźć dokładne dopasowanie
- 1, w takim przypadku przyjmuje się, że jest PRAWDA i WYSZUKAJ.PIONOWO próbuje znaleźć przybliżone dopasowanie
- 0, w takim przypadku przyjmuje się, że jest FALSE i WYSZUKAJ.PIONOWO próbuje znaleźć dokładne dopasowanie

Note- Jeśli argument przeszukiwany_zakres zostanie pominięty, PRAWDA lub 1, funkcja WYSZUKAJ.PIONOWO działa poprawnie tylko wtedy, gdy pierwsza kolumna tabeli tabela_tablica jest posortowana w kolejności rosnącej. W przeciwnym razie może to spowodować nieprawidłowe wartości. W takim przypadku użyj FALSE dla wyszukiwania_zakresu.

Używanie funkcji WYSZUKAJ.PIONOWO z wyszukiwaniem_zakresu PRAWDA

Rozważ listę ocen uczniów. Możesz uzyskać odpowiednie oceny za pomocą funkcji WYSZUKAJ.PIONOWO z tablicy zawierającej przedziały ocen i kategorię zaliczeń.

table_array -

Zwróć uwagę, że pierwsze oznaczenia w kolumnie, na podstawie których uzyskiwane są oceny, są sortowane w kolejności rosnącej. Stąd, używając TRUE dla argumentu przeszukiwany_zakres, można uzyskać przybliżone dopasowanie, które jest wymagane.

Nazwij tę tablicę jako Grades.

Dobrą praktyką jest nazywanie tablic w ten sposób, aby nie trzeba było pamiętać zakresów komórek. Teraz możesz znaleźć ocenę na liście posiadanych ocen w następujący sposób -

Jak widać,

col_index_num - wskazuje, że kolumna zwracanej wartości w table_array to 2
the range_lookup jest prawdziwy
- Pierwsza kolumna zawierająca szukaną wartość w ocenach table_array jest w porządku rosnącym. W związku z tym wyniki będą prawidłowe.
- Możesz również uzyskać wartość zwracaną dla przybliżonych dopasowań. tzn. WYSZUKAJ.PIONOWO oblicza się w następujący sposób -

Znaki	Kategoria przepustki
<35	Zawieść
> = 35 i <50	Trzecia klasa
> = 50 i <60	Druga klasa
> = 60 i <75	Pierwsza klasa
> = 75	Pierwsza klasa z wyróżnieniem

Otrzymasz następujące wyniki -

Używanie funkcji WYSZUKAJ.PIONOWO z wyszukiwaniem_zakresu FAŁSZ

Rozważ listę produktów zawierającą identyfikator produktu i cenę każdego z nich. Identyfikator produktu i cena zostaną dodane na końcu listy za każdym razem, gdy pojawi się nowy produkt. Oznaczałoby to, że identyfikatory produktów nie muszą być w kolejności rosnącej. Lista produktów może wyglądać tak, jak pokazano poniżej -

table_array -

Nazwij tę tablicę jako ProductInfo.

Cenę produktu można uzyskać, podając identyfikator produktu, korzystając z funkcji WYSZUKAJ.PIONOWO, ponieważ identyfikator produktu znajduje się w pierwszej kolumnie. Cena znajduje się w kolumnie 3, a więc col_index_ num powinna wynosić 3.

Użyj funkcji WYSZUKAJ.PIONOWO z wyszukiwaniem_zakresu jako PRAWDA
Użyj funkcji WYSZUKAJ.PIONOWO z wyszukiwaniem_zakresu jako FAŁSZ

Prawidłowa odpowiedź pochodzi z tablicy ProductInfo to 171,65. Możesz sprawdzić wyniki.

Zauważasz, że masz -

Prawidłowy wynik, gdy argument przeszukiwany_zakres ma wartość FALSE, a
Błędny wynik, gdy argument przeszukiwany_zakres ma wartość TRUE.

Dzieje się tak, ponieważ pierwsza kolumna w tablicy ProductInfo nie jest posortowana w kolejności rosnącej. Dlatego pamiętaj, aby użyć FALSE, gdy dane nie są posortowane.

Korzystanie z funkcji HLOOKUP

Możesz użyć HLOOKUP funkcja, jeśli dane są w wierszach, a nie w kolumnach.

Przykład

Weźmy przykład informacji o produkcie. Załóżmy, że tablica wygląda następująco -

Nazwij tę tablicę ProductRange. Cenę produktu podając identyfikator produktu możesz znaleźć dzięki funkcji WYSZUKAJ.POZIOMO.

Składnia funkcji WYSZUKAJ.POZIOMO to

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Gdzie

lookup_value - to wartość, która znajduje się w pierwszym wierszu tabeli
table_array - to tabela informacji, w której wyszukiwane są dane
row_index_num - to numer wiersza w table_array, z którego zostanie zwrócona pasująca wartość
range_lookup - to wartość logiczna, która określa, czy funkcja WYSZUKAJ.POZIOMO ma znaleźć dopasowanie ścisłe, czy przybliżone
range_lookup może być
- pominięty, w takim przypadku przyjmuje się, że jest PRAWDA i WYSZUKAJ.POZIOMO próbuje znaleźć przybliżone dopasowanie
- TRUE, w takim przypadku HLOOKUP próbuje znaleźć przybliżone dopasowanie. Innymi słowy, jeśli dokładne dopasowanie nie zostanie znalezione, zwracana jest następna co do wielkości wartość mniejsza niż wartość lookup_value
- FALSE, w takim przypadku WYSZUKAJ.POZIOMO próbuje znaleźć dokładne dopasowanie
- 1, w takim przypadku przyjmuje się, że jest PRAWDA i WYSZUKAJ.POZIOMO próbuje znaleźć przybliżone dopasowanie
- 0, w takim przypadku przyjmuje się, że jest FALSE i WYSZUKAJ.POZIOMO próbuje znaleźć dokładne dopasowanie

Note- Jeśli argument przeszukiwany_zakres jest pominięty, PRAWDA lub 1, WYSZUKAJ.POZIOMO działa poprawnie tylko wtedy, gdy pierwsza kolumna tabeli tabela_tablica jest posortowana w kolejności rosnącej. W przeciwnym razie może to spowodować nieprawidłowe wartości. W takim przypadku użyj FALSE dla wyszukiwania_zakresu.

Używanie funkcji WYSZUKAJ.POZIOMO z wyszukiwaniem_zakresu FAŁSZ

Cenę produktu można uzyskać podając jego identyfikator za pomocą funkcji WYSZUKAJ.POZIOMO, ponieważ identyfikator produktu znajduje się w pierwszym wierszu. Cena jest w wierszu 3, a zatem row_index_ num powinien wynosić 3.

Użyj funkcji HLOOKUP z wyszukiwaniem_zakresu jako PRAWDA.
Użyj funkcji WYSZUKAJ.POZIOMO z wyszukiwaniem_zakresu jako FAŁSZ.

Prawidłowa odpowiedź z tablicy ProductRange to 171,65. Możesz sprawdzić wyniki.

Zauważasz, że tak jak w przypadku WYSZUKAJ.PIONOWO masz

Prawidłowy wynik, gdy argument przeszukiwany_zakres ma wartość FALSE, a
Błędny wynik, gdy argument przeszukiwany_zakres ma wartość TRUE.

Dzieje się tak, ponieważ pierwszy wiersz w tablicy ProductRange nie jest sortowany w kolejności rosnącej. Dlatego pamiętaj, aby użyć FALSE, gdy dane nie są posortowane.

Używanie funkcji HLOOKUP z wyszukiwaniem_zakresu PRAWDA

Rozważ przykład ocen uczniów używanych w funkcji WYSZUKAJ.PIONOWO. Załóżmy, że masz dane w wierszach zamiast w kolumnach, jak pokazano w tabeli podanej poniżej -

table_array -

Nazwij tę tablicę jako GradesRange.

Zwróć uwagę, że znaczniki w pierwszym wierszu, na podstawie których uzyskiwane są oceny, są sortowane w kolejności rosnącej. W związku z tym, używając WYSZUKAJ.POZIOMO z PRAWDA dla argumentu przeszukiwany_zakres, można uzyskać oceny z przybliżonym dopasowaniem i to jest to, co jest wymagane.

Jak widać,

row_index_num - wskazuje, że kolumna zwracanej wartości w table_array to 2
the range_lookup jest prawdziwy
- Pierwsza kolumna zawierająca szukaną wartość w tabeli table_array Grades jest w porządku rosnącym. W związku z tym wyniki będą prawidłowe.
- Możesz również uzyskać wartość zwracaną dla przybliżonych dopasowań. czyli HLOOKUP oblicza w następujący sposób -

Znaki	<35	> = 35 i <50	> = 50 i <60	> = 60 i <75	> = 75
Kategoria przepustki	Zawieść	Trzecia klasa	Druga klasa	Pierwsza klasa	Pierwsza klasa z wyróżnieniem

Otrzymasz następujące wyniki -

Korzystanie z funkcji INDEKS

Gdy masz tablicę danych, możesz pobrać wartość w tablicy, określając numer wiersza i numer kolumny tej wartości w tablicy.

Weź pod uwagę następujące dane sprzedaży, w których znajdziesz sprzedaż w każdym z regionów północnego, południowego, wschodniego i zachodniego według sprzedawców wymienionych na liście.

Nazwij tablicę jako SalesData.

Używając funkcji INDEKS, możesz znaleźć -

Sprzedaż dowolnego ze sprzedawców w określonym regionie.
Całkowita sprzedaż w regionie przez wszystkich sprzedawców.
Całkowita sprzedaż sprzedawcy we wszystkich regionach.

Otrzymasz następujące wyniki -

Załóżmy, że nie znasz numerów wierszy dla sprzedawców i numerów kolumn dla regionów. Następnie musisz najpierw znaleźć numer wiersza i numer kolumny, zanim pobierzesz wartość za pomocą funkcji indeksu.

Możesz to zrobić za pomocą funkcji MATCH, jak wyjaśniono w następnej sekcji.