Функция Match в Excel: как использовать функцию Match с примерами

Функция Match в Excel имеет множество практических применений. Ниже приведены подробности о том, как использовать функцию Match Excel.

Функция Match — популярная функция в функциях Excel, часто используемая при обработке таблиц данных Excel и расчетов. В таблице данных, когда вы хотите найти определенное значение в массиве или диапазоне ячеек, функция Match вернет правильную позицию этого значения в массиве или в диапазоне таблицы данных.

Это помогает пользователям быстро находить нужное им значение, без необходимости ручного поиска, особенно в таблицах с большим количеством данных, что может занять много времени. Статья ниже расскажет вам, как использовать функцию Match в Excel.

Синтаксис функции Match в Excel

Синтаксис функции Match в Excel следующий: =Match(Lookup_value,Lookup_array,(Match_type)).

Там:

  1. Lookup_value: поиск значения в массиве Lookup_array. Это значение может быть числом, текстом, логическим значением или ссылкой на ячейку с числом, текстом или логическим значением, что обязательно.
  2. Lookup_array: массив или диапазон ячеек для поиска, обязательно.
  3. Match_type: тип поиска, не обязательно.

В функции «Поиск» в Excel есть 3 типа поиска:

  1. 1 или пропущено (Меньше чем): функция Match ищет наибольшее значение, которое меньше или равно lookup_value. Если пользователь выбирает этот тип поиска, lookup_array должен быть отсортирован в порядке возрастания.
  2. 0 (Точное совпадение): функция Match будет искать первое значение, которое точно равно lookup_value. Значения в lookup_array можно сортировать по любому значению.
  3. -1 (Больше): Функция Match ищет наименьшее значение, которое больше или равно lookup_value. Значения в lookup_array должны быть отсортированы в порядке убывания.

Обратите внимание при использовании функции Match:

  1. Функция Match возвращает позицию искомого значения в lookup_array, а не само искомое значение.
  2. При поиске текстовых значений можно использовать заглавные или строчные буквы.
  3. Если искомое значение не может быть найдено в lookup_array, функция Match выдаст сообщение об ошибке поискового значения.
  4. В случае, если Match_type равен 0, а значение поиска lookup_value — текст, значение поиска может содержать символы * (для строк символов) и вопросительные знаки (для отдельных символов). Если вы хотите найти вопросительный знак или звездочку, введите тильду перед этим символом.
  5. Если ничего не введено, функция Match по умолчанию равна 1.

Типы функций ПОИСКПОЗ в Excel

Вот различные типы функций ПОИСКПОЗ в Excel:

1. Точное совпадение

Функция ПОИСКПОЗ выполняет точное сопоставление, если тип сопоставления установлен на 0. В примере ниже формула в E3 имеет вид:

=ПОИСКПОЗ(E2,B3:B10,0)

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 1

Функция ПОИСКПОЗ возвращает точное совпадение 4.

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 2

2. Примерное совпадение

MATCH выполнит приблизительное сопоставление значений, отсортированных от AZ, когда тип сопоставления установлен на 1, находя наибольшее значение, которое меньше или равно искомому значению. В примере ниже формула в E3 выглядит следующим образом:

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 3

Функция ПОИСКПОЗ в Excel возвращает приблизительное совпадение 7.

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 4

3. Сопоставление подстановочных знаков

Функция ПОИСКПОЗ может выполнять сопоставление с подстановочными знаками, если тип сопоставления установлен на 0. В примере ниже формула в E3 имеет вид:

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 5

Функция ПОИСКПОЗ возвращает результат подстановочного символа «pq».

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 6

Примечание:

  1. Функция ПОИСКПОЗ не чувствительна к регистру.
  2. Если совпадений не найдено, возвращается ошибка #N/A.
  3. Аргумент lookup_array должен быть в порядке убывания: True, False, ZA,…9,8,7,6,5,4,3,… и т. д.
  4. В lookup_value можно найти подстановочные знаки, такие как звездочки и вопросительные знаки, если match_type равен 0, а lookup_value имеет текстовый формат.
  5. Lookup_value может иметь подстановочные знаки, такие как звездочки и вопросительные знаки, если match_type равен 0, а lookup_value — текст. Звездочка

соответствует любому типу строки символов; Любой отдельный символ соответствует вопросительному знаку (?).

4. Как применять индекс и сопоставление в формуле Excel

Match и Index, объединенные вместе, дадут вам много преимуществ. Например, вычисляйте доход и прибыль от любого приложения из предоставленной базы данных. Вот как это можно сделать:

1. Откройте функцию ИНДЕКС.

2. Выберите ячейки C3:C13 в качестве источника данных о доходах (Доход).

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 7

3. Введите СОВПАДЕНИЕ и нажмите Tab.

4. Выберите G2 в качестве искомого значения, B3:B13 в качестве исходных данных и 0 для полного совпадения.

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 8

5. Нажмите Enter, чтобы получить информацию о выбранном приложении.

6. Выполните описанные выше шаги и замените источник INDEX на D3:D13, чтобы получить Profit.

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 9

7. Теперь эта формула будет реализована в Excel:

=ИНДЕКС(C3:C13,ПОИСКПОЗ(G2,B3:B13,0)) =ИНДЕКС(D3:D13,ПОИСКПОЗ(G2,B3:B13,0))

Выше приведен пример одностороннего использования ИНДЕКСА ПОИСКПОЗ в Excel. Вы также можете выбирать строки и столбцы для выполнения более сложных поисков.

Например, вы можете использовать раскрывающееся меню вместо разделения ячеек для доходов и прибыли. Вы можете выполнить следующие шаги для практики:

1. Выберите поле «Доход» и щелкните вкладку «Данные» на ленте.

2. Нажмите «Проверка данных» и в разделе «Разрешить» выберите «Список».

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 10

3. Выберите заголовок столбца «Доход и прибыль» в качестве источника.

4. Нажмите ОК.

Функция Match в Excel: как использовать функцию Match с примерами Рисунок 11

5. В этой формуле вы используете функцию ПОИСКПОЗ дважды, чтобы извлечь значения как строки, так и столбца для окончательной функции ИНДЕКС.

6. Скопируйте и вставьте следующую формулу рядом с ячейкой «Доход», чтобы получить значение. Просто используйте раскрывающееся меню, чтобы выбрать между «Доход» и «Прибыль».

=ИНДЕКС(C3:D13,ПОИСКПОЗ(G2,B3:B13,0),ПОИСКПОЗ(F4,C2:D2,0))

Пример функции соответствия

Пример 1:

Рассмотрим пример с таблицей общего количества продуктов ниже.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 12

Случай 1: Тип поиска равен 1 или пропущен

Поиск позиции номер 61 в столбце Total в таблице данных, что означает поиск значения, меньшего искомого значения. Вводим формулу =MATCH(64,C2:C6,1) .

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 13

Поскольку значение 64 отсутствует в столбце «Итого», функция вернет позицию ближайшего малого значения, значение которого меньше 64, то есть 63. Результат вернет значение на 2-й позиции в столбце.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 14

Случай 2: Тип поиска — 0

Найдите в таблице данных местоположение значения 70. У нас будет входная формула =ПОИСКПОЗ(70,C2:C6,0), а затем нажмите Enter.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 15

Возвращенным результатом будет позиция значения 70 в столбце «Итого» как 4-я позиция.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 16

Случай 3: Тип поиска -1

У нас будет формула =ПОИСКПОЗ(65,C2:C6,-1), как показано ниже.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 17

Однако, поскольку массив не отсортирован по убыванию, будет выдано сообщение об ошибке, как показано ниже.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 18

Пример 2:

Приведена таблица данных по группам студентов ниже. Найдите порядок класса студента в этой таблице данных, используя порядок, указанный ниже.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 19

Формула поиска заказа =ПОИСКПОЗ(D2,$D$6:$D$8,0), затем нажмите Enter.

Функция Match в Excel: как использовать функцию Match с примерами, рисунок 20

Сразу после этого возвращаемые результаты будут представлять собой точный порядок учащихся по классам, отсортированный в соответствии с заданными правилами.

Функция Match в Excel: Как использовать функцию Match с примерами Рисунок 21

  1. Подводя итог, вот что нужно помнить при использовании функции «Поиск» в Excel:
  2. Цель: Определить положение любого элемента в массиве.
  3. Возвращаемое значение: число, представляющее позицию в lookup_array.
    1. Аргумент:
    2. Lookup_value: Искомое значение в массиве.
    3. Lookup_array: Диапазон ячеек или ссылка на массив.
  4. Match_type – (необязательно) 1 = (по умолчанию) следующее точное или наименьшее значение, 0 = точное совпадение, -1 = следующее наибольшее или точное значение.

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

Выше приведено все, что вам нужно знать о функции ПОИСКПОЗ в Excel. Несмотря на то, что это всего лишь функция для поиска значений, а не для их вычисления, она чрезвычайно полезна, когда вам нужно упорядочить данные разумно и логично.

В принципе, использование функции Match в Microsoft Excel не так уж и сложно, если вы понимаете всю основную информацию выше. Помимо функции Match, Excel имеет множество других полезных функций. Давайте продолжим изучать функции Excel с QuanTriMang в следующих статьях!

Желаю вам успехов!Джессика Таннер

Джессика Таннер

Обновление 10 июля 2024 г.

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *