Funkcja MATCH w programie Microsoft Excel

Jednym z najpopularniejszych operatorów wśród użytkowników Excela jest funkcja MATCHING . Jego zadaniem jest ustalenie numeru pozycji elementu w danym zbiorze danych. Największa korzyść przynosi w połączeniu z innymi operatorami. Zobaczmy, jaka jest funkcja MATCH i jak można ją wykorzystać w praktyce.

Korzystanie z operatora MAP

Operator MAP należy do kategorii "Łącza i tablice" . Szuka określonego elementu w określonej tablicy i zwraca liczbę jego pozycji w tym zakresie do osobnej komórki. W rzeczywistości nawet jego nazwa wskazuje na to. Ponadto, w połączeniu z innymi operatorami, funkcja ta informuje ich o numerze pozycji danego elementu w celu późniejszego przetworzenia tych danych.

Składnia instrukcji MATCH wygląda następująco:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

Rozważ teraz każdy z tych trzech argumentów osobno.

"Poszukiwanie znaczenia" jest elementem, który należy znaleźć. Może mieć tekstową, numeryczną postać, a także przyjąć wartość logiczną. Ten argument może być także odniesieniem do komórki zawierającej dowolną z powyższych wartości.

"Przeskanowana tablica" to adres zakresu, w którym znajduje się wartość. Jest to pozycja tego elementu w tym zestawie, który musi określić operator MAP .

"Typ dopasowania " wskazuje dokładne dopasowanie, które chcesz wyszukać lub niedokładne. Ten argument może mieć trzy wartości: "1" , "0" i "-1" . Jeśli wartość wynosi "0", operator szuka jedynie dopasowania ścisłego. Jeśli zostanie podana wartość "1" , w przypadku braku dokładnego dopasowania, funkcja MATCH tworzy najbliższy element w porządku malejącym. Jeśli wartość wynosi "-1" , to w przypadku, gdy nie zostanie znalezione dokładne dopasowanie, funkcja tworzy najbliższy element w porządku rosnącym. Ważne jest, jeśli wyszukiwanie nie jest dokładną wartością, ale przybliżoną, tak aby szykująca się do skanowania była uporządkowana w porządku rosnącym (rodzaj odwzorowania "1" ) lub malejąca (rodzaj odwzorowania "-1" ).

Argument "Typ dopasowania" jest opcjonalny. Można go pominąć, jeśli nie jest potrzebny. W tym przypadku jego domyślna wartość to "1" . Zastosowanie argumentu "Typ dopasowania" ma przede wszystkim sens tylko wtedy, gdy przetwarzane są wartości liczbowe, a nie tekst.

Jeśli funkcja MATCH dla określonych ustawień nie może znaleźć żądanej pozycji, operator pokazuje komunikat "# N / D" w komórce.

Podczas przeprowadzania wyszukiwania operator nie rozróżnia rejestrów znaków. Jeśli w macierzy znajduje się kilka dokładnych dopasowań, MATCHING wyświetla pozycję pierwszego w komórce.

Metoda 1: Wyświetl miejsce elementu w zakresie danych tekstowych

Spójrzmy na przykład najprostszego przypadku, gdy używając MATCH, możesz określić położenie określonego elementu w tablicy danych tekstowych. Dowiemy się, które stanowisko w zakresie, w którym znajdują się nazwy towarów, jest słowem "cukier" .

1. Wybierz komórkę, w której zostanie przetworzony przetworzony wynik. Kliknij ikonę "Wstaw funkcję" obok paska formuły.
2. Uruchomi się Kreator funkcji . Otwórz kategorię "Pełna lista alfabetyczna" lub "Linki i tablice" . Na liście operatorów szukamy nazwy "MATCH" . Po znalezieniu i wybraniu, kliknij przycisk "OK" w dolnej części okna.
3. Okno dla argumentów operatora MAP jest aktywne . Jak widać, w tym oknie znajdują się trzy pola w zależności od liczby argumentów. Musimy je wypełnić.

   Ponieważ musimy znaleźć pozycję słowa "Cukier" w zakresie, kierujemy tę nazwę do pola "Wartość wyszukiwania" .

   W polu "Skanowana tablica" należy określić współrzędne samego zakresu. Można go uderzać ręcznie, ale łatwiej jest umieścić kursor w polu i wybrać tę tablicę na arkuszu, przytrzymując lewy przycisk myszy. Następnie jego adres zostanie wyświetlony w oknie argumentów.

   W trzecim polu "Typ dopasowania" wpisujemy numer "0" , ponieważ będziemy pracować z danymi tekstowymi, dlatego potrzebujemy dokładnego wyniku.

   Po ustawieniu wszystkich danych kliknij przycisk "OK" .

4. Program oblicza i wyświetla numer porządkowy pozycji "Cukier" w wybranej macierzy w komórce, którą określiliśmy na pierwszym etapie tej instrukcji. Numer pozycji będzie wynosił "4" .

Lekcja: Kreator funkcji w programie Excel

Metoda 2: Zautomatyzuj użycie operatora MATCH

Powyżej rozważaliśmy najbardziej prymitywny przypadek użycia operatora POSITION , ale nawet on może być zautomatyzowany.

1. Dla wygody na arkuszu dodaj dwa dodatkowe pola: "Określona wartość" i "Numer" . W polu " Przypisana wartość" podajemy nazwę, którą musimy znaleźć. Teraz niech będzie "Mięso" . W polu "Numer" ustaw kursor i przejdź do okna argumentów operatora w taki sam sposób jak opisano powyżej.
2. W oknie argumentów funkcji, w polu "Wartość wyszukiwania" określamy adres komórki, w której wpisane jest słowo "Mięso" . W polach "Zeskanowana tablica" i "Typ odwzorowania" określamy te same dane, co w poprzedniej metodzie - odpowiednio adres zakresu i numer "0" . Następnie kliknij przycisk "OK" .
3. Po wykonaniu powyższych czynności pole "Liczba" wyświetla pozycję słowa "Mięso" w wybranym zakresie. W tym przypadku jest równa "3" .
4. Ta metoda jest dobra, ponieważ jeśli chcemy znać pozycję jakiejkolwiek innej nazwy, nie będziemy musieli ponownie wpisywać ani zmieniać formuły za każdym razem. W polu "Określona wartość" jest dość proste, aby wprowadzić nowe słowo wyszukiwania zamiast poprzedniego. Przetwarzanie i wynik wyniku po tym nastąpi automatycznie.

Metoda 3: Użyj operatora MAP do wyrażeń numerycznych

Teraz przyjrzyjmy się, jak używać dopasowywania do pracy z wyrażeń numerycznych.

Zadanie polega na znalezieniu towarów o wartości realizacji 400 rubli lub najbliższej tej sumie w porządku rosnącym.

1. Przede wszystkim musimy posortować elementy w kolumnie "Kwota" w porządku malejącym. Wybierz tę kolumnę i przejdź do zakładki "Strona główna" . Klikamy ikonę "Sortuj i filtruj" , która znajduje się na wstążce w bloku "Edycja" . Na wyświetlonej liście wybierz "Sortuj od maksimum do minimum" .
2. Po zakończeniu sortowania wybierz komórkę, w której zostanie wyświetlony wynik, i uruchom okno argumentów w taki sam sposób, jak opisano w pierwszej metodzie.

   W polu "Wartość wyszukiwania" podajemy liczbę "400" . W polu "Przeskanowana tablica" podaj współrzędne kolumny "Kwota" . W polu "Typ dopasowania" ustaw wartość na "-1" , ponieważ szukamy równej lub większej wartości od tej, której szukamy. Po zakończeniu wszystkich ustawień kliknij przycisk "OK" .

3. Wynik przetwarzania jest wyświetlany w poprzednio określonej komórce. To jest pozycja "3" . Odpowiada "Ziemniakom" . Rzeczywiście, kwota przychodów ze sprzedaży tego produktu jest najbliższa 400 w porządku rosnącym i wynosi 450 rubli.

Podobnie możesz wyszukać najbliższą pozycję do "400" w malejącej kolejności. Tylko w tym celu konieczne jest filtrowanie danych w porządku rosnącym, aw polu "Typ dopasowania" argumentów funkcji, ustaw wartość na "1" .

Lekcja: Sortowanie i filtrowanie danych w programie Excel

Metoda 4: Używaj w połączeniu z innymi operatorami

Najskuteczniej jest używać tej funkcji z innymi operatorami jako częścią złożonej formuły. Najczęściej jest używany w połączeniu z funkcją INDEX . Ten argument podaje zawartość zakresu określonego przez numer wiersza lub kolumny do podanej komórki. Numeracja, podobnie jak w przypadku operatora POSITION , jest wykonywana nie w odniesieniu do całego arkusza, ale tylko w zakresie. Składnia tej funkcji jest następująca:

=ИНДЕКС(массив;номер_строки;номер_столбца)

W takim przypadku, jeśli tablica jest jednowymiarowa, można użyć tylko jednego z dwóch argumentów: "Numer linii" lub "Numer kolumny" .

Osobliwością połączenia między funkcjami INDEKS i MATCH jest to, że ten drugi może być użyty jako pierwszy argument, to znaczy wskazać pozycję wiersza lub kolumny.

Rzućmy okiem na to, jak można to zrobić w praktyce, używając tej samej tabeli. Mamy zadanie wypisać nazwę towaru w polu dodatkowym arkusza "Towary" , łączna kwota przychodu, z której jest równa 350 rubli, lub najbliższa tej wartości w porządku malejącym. Ten argument jest wskazany w polu "Przybliżona kwota przychodów z arkusza" .

1. Sortuj elementy w kolumnie "Kwota przychodu" w porządku rosnącym. Aby to zrobić, wybierz żądaną kolumnę, a na karcie "Strona główna" kliknij ikonę "Sortuj i filtruj" , a następnie kliknij pozycję "Sortuj z minimum do maksimum" w wyświetlonym menu.
2. Wybierz komórkę w polu "Produkt" i wywołaj Kreatora funkcji w zwykły sposób za pomocą przycisku "Wstaw funkcję" .
3. W otwartym oknie kreatora funkcji z kategorii "Łącza i tablice" szukamy nazwy "INDEX" , wybierz ją i kliknij przycisk "OK" .
4. Następnie otwiera się okno, które oferuje wybór opcji INDEX : dla tablicy lub dla odniesienia. Potrzebujemy pierwszej opcji. Dlatego zostawiamy wszystkie domyślne ustawienia w tym oknie i klikamy przycisk "OK" .
5. Otwiera okno argumentów funkcji INDEX . W polu "Array" podaj adres zakresu, w którym operator INDEX będzie szukał nazwy produktu. W naszym przypadku jest to kolumna "Nazwa produktu" .

   Pole "Numer linii" będzie zawierało zagnieżdżoną funkcję MATCH . Będzie musiał być ręcznie młotkowany przy użyciu składni wspomnianej na samym początku artykułu. Natychmiast zapisz nazwę funkcji - "MECZ" bez cytatów. Następnie otwórz wspornik. Pierwszym argumentem tego operatora jest "wartość odsetek" . Znajduje się na arkuszu w polu "Przybliżona kwota przychodów". Określ współrzędne komórki zawierającej liczbę 350 . Wpisz średnik. Drugi argument to "Skanowana tablica" . MATCHING przyjrzy się zakresowi, w którym dochód zostanie znaleziony, i poszukaj w przybliżeniu 350 rubli. Dlatego w tym przypadku podajemy współrzędne kolumny "Kwota przychodów" . Znowu wstawiamy średnik. Trzeci argument to "Typ dopasowania" . Ponieważ szukamy liczby równej podanej liczbie lub najbliższej mniejszej, ustawiamy tutaj cyfrę "1" . Zamknij nawiasy.

   Trzeci argument funkcji INDEX "Column Number" pozostaje pusty. Następnie kliknij przycisk "OK" .

6. Jak widać, funkcja INDEX przy pomocy operatora POSITION w wybranej komórce wyświetla nazwę "Tea" . Rzeczywiście, kwota ze sprzedaży herbaty (300 rubli) jest najbliższa w porządku malejącym do sumy 350 rubli od wszystkich wartości w przetworzonym stole.
7. Jeśli zmienimy liczbę w polu "Przybliżona kwota przychodu" na inną, zawartość pola "Produkt" zostanie automatycznie ponownie obliczona.

Lekcja: Funkcja INDEKS w programie Excel

Jak widać, operator MAP jest bardzo wygodną funkcją do określania liczby porządkowej określonego elementu w tablicy danych. Ale korzyść z niego jest znacznie większa, jeśli jest używana w złożonych formułach.

• ← Obliczanie NPV w Microsoft Excel
• Funkcja INDEX w programie Microsoft Excel →