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.

Korzystanie z funkcji MATCH

Jeśli potrzebujesz pozycji elementu w zakresie, możesz użyć funkcji PODAJ.POZYCJĘ. Możesz łączyć funkcje MATCH i INDEX w następujący sposób -

Otrzymasz następujące wyniki -