30 функцій Excel за 30 днів: MATCH

Вчора на марафоні 30 функцій Excel за 30 днів ми знайшли текстові рядки за допомогою функції ПОШУК (ПОШУК), а також використовується ПОМИЛКА (IFERROR) і ІЗНОМ (ISNUMBER) у ситуаціях, коли функція видає помилку.

На 19 день нашого марафону ми вивчимо функцію MATCH (ПОШУК). Він шукає значення в масиві і, якщо значення знайдено, повертає його позицію.

Отже, звернемося до довідкової інформації по функції MATCH (МАТЧ) і подивіться на кілька прикладів. Якщо у вас є власні приклади чи підходи до роботи з цією функцією, поділіться ними в коментарях.

Функція 19: MATCH

функція MATCH (MATCH) повертає позицію значення в масиві або помилку #AT (#N/A), якщо не знайдено. Масив може бути відсортованим або несортованим. функція MATCH (MATCH) не чутливий до регістру.

Як можна використовувати функцію MATCH?

функція MATCH (MATCH) повертає позицію елемента в масиві, і цей результат може бути використаний іншими функціями, наприклад ІНДЕКС (ІНДЕКС) або ВЛООКУП (ВПР). Наприклад:

  • Знайти позицію елемента в несортованому списку.
  • Використовувати с ВИБРАТИ (ВИБРАТИ), щоб конвертувати успішність студента в літерні оцінки.
  • Використовувати с ВЛООКУП (VLOOKUP) для гнучкого вибору стовпців.
  • Використовувати с ІНДЕКС (INDEX), щоб знайти найближче значення.

Синтаксис MATCH

функція MATCH (MATCH) має такий синтаксис:

MATCH(lookup_value,lookup_array,[match_type])

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

  • lookup_value (lookup_value) – може бути текстом, числом або логічним значенням.
  • пошуковий_масив (масив_пошуку) – масив або посилання на масив (сусідні комірки в одному стовпці чи рядку).
  • match_type (match_type) може приймати три значення: -1, 0 or 1. Якщо аргумент опущено, він еквівалентний 1.

Пастки MATCH (MATCH)

функція MATCH (MATCH) повертає позицію знайденого елемента, але не його значення. Якщо ви хочете повернути значення, використовуйте MATCH (MATCH) разом із функцією ІНДЕКС (ІНДЕКС).

Приклад 1: Пошук елемента в несортованому списку

Для несортованого списку можна використовувати 0 як значення аргументу match_type (match_type) для пошуку точної відповідності. Якщо ви хочете знайти точний збіг текстового рядка, ви можете використовувати символи підстановки в пошуковому значенні.

У наступному прикладі, щоб знайти місце місяця в списку, ми можемо написати назву місяця повністю або частково, використовуючи символи підстановки.

=MATCH(D2,B3:B7,0)

=ПОИСКПОЗ(D2;B3:B7;0)

30 функцій Excel за 30 днів: MATCH

Як аргумент пошуковий_масив (lookup_array) ви можете використовувати масив констант. У наступному прикладі потрібний місяць вводиться в клітинку D5, а назви місяців замінюються як другий аргумент функції MATCH (MATCH) як масив констант. Наприклад, якщо ви введете пізніший місяць у клітинку D5, жовтень (жовтень), то результат функції буде #AT (#Н/Д).

=MATCH(D5,{"Jan","Feb","Mar"},0)

=ПОИСКПОЗ(D5;{"Jan";"Feb";"Mar"};0)

30 функцій Excel за 30 днів: MATCH

Приклад 2: Змінити оцінки студента з відсотків на літери

Ви можете конвертувати оцінки студентів у буквену систему за допомогою функції MATCH (МАТЧ) так само, як ви зробили з ВЛООКУП (ВПР). У цьому прикладі функція використовується в поєднанні з ВИБРАТИ (CHOICE), який повертає потрібну оцінку. Аргумент match_type (match_type) встановлено рівним -1, тому що бали в таблиці відсортовані в порядку спадання.

Коли аргумент match_type (тип_відповідності) є -1, результатом є найменше значення, яке більше або еквівалентне бажаному значенню. У нашому прикладі потрібне значення 54. Оскільки такого значення в списку балів немає, повертається елемент, що відповідає значенню 60. Оскільки 60 займає четверте місце в списку, результат функції ВИБРАТИ (ВИБІР) буде значення, яке знаходиться на 4-му місці, тобто клітинка C6, яка містить оцінку D.

=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)

=ВЫБОР(ПОИСКПОЗ(B9;B3:B7;-1);C3;C4;C5;C6;C7)

30 функцій Excel за 30 днів: MATCH

Приклад 3: створення гнучкого вибору стовпців для VLOOKUP (VLOOKUP)

Щоб надати функції більшої гнучкості ВЛООКУП (VLOOKUP) Ви можете використовувати MATCH (MATCH), щоб знайти номер стовпця, а не жорстко вводити його значення у функцію. У наступному прикладі користувачі можуть вибрати область у клітинці H1, це значення, яке вони шукають ВЛООКУП (ВПР). Потім вони можуть вибрати місяць у клітинці H2 і функцію MATCH (MATCH) поверне номер стовпця, що відповідає цьому місяцю.

=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)

=ВПР(H1;$B$2:$E$5;ПОИСКПОЗ(H2;B1:E1;0);ЛОЖЬ)

30 функцій Excel за 30 днів: MATCH

Приклад 4: Пошук найближчого значення за допомогою INDEX (INDEX)

функція MATCH (MATCH) чудово працює в поєднанні з функцією ІНДЕКС (ІНДЕКС), який ми розглянемо докладніше трохи пізніше в цьому марафоні. У цьому прикладі функція MATCH (MATCH) використовується для пошуку найближчого до правильного числа з кількох вгаданих чисел.

  1. функція ABS повертає модуль різниці між кожним вгаданим і правильним числом.
  2. функція MIN (MIN) знаходить найменшу різницю.
  3. функція MATCH (MATCH) знаходить адресу найменшої різниці в списку відмінностей. Якщо в списку є кілька відповідних значень, буде повернено перше.
  4. функція ІНДЕКС (INDEX) повертає назву, що відповідає цій позиції, зі списку імен.

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))

=ИНДЕКС(B2:B5;ПОИСКПОЗ(МИН(ABS(C2:C5-F1));ABS(C2:C5-F1);0))

30 функцій Excel за 30 днів: MATCH

залишити коментар