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

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

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

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

Синтаксис функции сопоставления в Excel

Синтаксис функции Match в Excel: =Match(искомое_значение,просматриваемый_массив,[тип_соответствия]).

Там:

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

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

• 1 или опущено (меньше): функция Match ищет наибольшее значение, которое меньше или равно искомому_значению. Если пользователь выбирает этот тип поиска, искомый_массив должен быть отсортирован по возрастанию.
• 0 (точное совпадение): функция Match будет искать первое значение, которое точно равно искомому_значению. Значения в Lookup_array можно сортировать по любому значению.
• -1 (Больше чем): функция Match ищет наименьшее значение, которое больше или равно искомому_значению. Значения в Lookup_array необходимо отсортировать по убыванию.

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

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

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

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

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

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

=MATCH(E2,B3:B10,0)

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

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

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

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

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

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

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

Примечание:

• Функция ПОИСКПОЗ не чувствительна к регистру.
• Match возвращает ошибку #N/A, если совпадение не найдено.
• Аргумент искомый_массив должен быть указан в порядке убывания: True, False, ZA,…9,8,7,6,5,4,3,… и т. д.
• В искомом_значении можно найти подстановочные знаки, такие как звездочки и вопросительные знаки, если тип_сопоставления равен 0 , а искомое_значение имеет текстовый формат.
• Искомое_значение может содержать подстановочные знаки, такие как звездочки и вопросительные знаки, если тип_сопоставления равен 0 , а искомое_значение — текст. Звездочка (*) соответствует любому типу символьной строки; Любому отдельному символу соответствует вопросительный знак (?).

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

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

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

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

3. Введите ПОИСКПОЗ и нажмите Tab .

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

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

6. Следуйте инструкциям выше и замените источник INDEX на D3:D13 , чтобы получить прибыль .

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

=INDEX(C3:C13,MATCH(G2,B3:B13,0))

=INDEX(D3:D13,MATCH(G2,B3:B13,0))

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

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

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

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

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

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

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

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

=INDEX(C3:D13,MATCH(G2,B3:B13,0),MATCH(F4,C2:D2,0))

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

Пример 1:

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

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

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

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

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

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

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

Случай 3. Тип поиска – 1.

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

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

Пример 2:

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

Формула поиска ордера: =MATCH(D2,$D$6:$D$8,0), затем нажмите Enter.

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

Подводя итог, следует помнить, что при использовании функции «Сопоставить» в Excel:

• Цель: Определить положение любого элемента массива.
• Возвращаемое значение: число, представляющее позицию в искомом_массиве.
• Аргумент:
  • Lookup_value: значение поиска в массиве.
  • Lookup_array: ссылка на диапазон ячеек или массив.
  • Тип_соответствия — [необязательно] 1 = (по умолчанию) следующее точное или наименьшее значение, 0 = точное совпадение, -1 = следующее наибольшее или точное значение.
• Формула: =MATCH(искомое_значение, искомый_массив, [тип_соответствия])

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

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

• Функция поиска в Excel
• Функция поиска в Excel

Желаю вам успехов!