Match excel как пользоваться
Вчера в марафоне 30 функций Excel за 30 дней мы находили текстовые строки при помощи функции SEARCH (ПОИСК), а также использовали IFERROR (ЕСЛИОШИБКА) и ISNUMBER (ЕЧИСЛО) в ситуациях, когда функция выдаёт ошибку.
В 19-й день нашего марафона мы займёмся изучением функции MATCH (ПОИСКПОЗ). Она ищет значение в массиве и, если значение найдено, возвращает его позицию.
Итак, давайте обратимся к справочной информации по функции MATCH (ПОИСКПОЗ) и разберем несколько примеров. Если у Вас есть собственные примеры или подходы по работе с этой функцией, пожалуйста, делитесь ими в комментариях.
Функция 19: MATCH (ПОИСКПОЗ)
Как можно использовать функцию MATCH (ПОИСКПОЗ)?
Функция MATCH (ПОИСКПОЗ) возвращает позицию элемента в массиве, и этот результат может быть использован другими функциями, такими как INDEX (ИНДЕКС) или VLOOKUP (ВПР). Например:
- Найти положение элемента в несортированном списке.
- Использовать вместе с CHOOSE (ВЫБОР), чтобы перевести успеваемость учащихся в буквенную систему оценок.
- Использовать вместе с VLOOKUP (ВПР) для гибкого выбора столбца.
- Использовать вместе с INDEX (ИНДЕКС), чтобы найти ближайшее значение.
Синтаксис MATCH (ПОИСКПОЗ)
Функция MATCH (ПОИСКПОЗ) имеет следующий синтаксис:
- lookup_value (искомое_значение) – может быть текстом, числом или логическим значением.
- lookup_array (просматриваемый_массив) – массив или ссылка на массив (смежные ячейки в одном столбце или в одной строке).
- match_type (тип_сопоставления) – может принимать три значения: -1, 0 или 1. Если аргумент пропущен, это равносильно 1.
Ловушки MATCH (ПОИСКПОЗ)
Функция MATCH (ПОИСКПОЗ) возвращает положение найденного элемента, но не его значение. Если требуется вернуть значение, используйте MATCH (ПОИСКПОЗ) вместе с функцией INDEX (ИНДЕКС).
Пример 1: Находим элемент в несортированном списке
Для несортированного списка можно использовать 0 в качестве значения аргумента match_type (тип_сопоставления), чтобы выполнить поиск точного совпадения. Если требуется найти точное совпадение текстовой строки, то в искомом значении допускается использовать символы подстановки.
В следующем примере, чтобы найти положение месяца в списке, мы можем написать название месяца либо целиком, либо частично с применением символов подстановки.
Пример 2: Изменяем оценки учащихся c процентов на буквы
Вы можете преобразовать оценки учащихся в буквенную систему, используя функцию MATCH (ПОИСКПОЗ) так же, как Вы делали это с VLOOKUP (ВПР). В этом примере функция использована в сочетании с CHOOSE (ВЫБОР), которая и возвращает нужную нам оценку. Аргумент match_type (тип_сопоставления) принимаем равным -1, поскольку баллы в таблице отсортированы в порядке убывания.
Когда аргумент match_type (тип_сопоставления) равен -1, результатом будет наименьшее значение, которое больше искомого или эквивалентное ему. В нашем примере искомое значение равно 54. Поскольку такого значения нет в списке баллов, то возвращается элемент, соответствующий значению 60. Так как 60 стоит на четвёртом месте списка, то результатом функции CHOOSE (ВЫБОР) будет значение, которое находится на 4-й позиции, т.е. ячейка C6, в которой находится оценка D.
Пример 3: Создаем гибкий выбор столбца для VLOOKUP (ВПР)
Чтобы придать больше гибкости функции VLOOKUP (ВПР), Вы можете использовать MATCH (ПОИСКПОЗ) для поиска номера столбца, а не жестко вписывать его значение в функцию. В следующем примере пользователи могут выбрать регион в ячейке H1, это искомое значение для VLOOKUP (ВПР). Далее, они могут выбрать месяц в ячейке H2, и функция MATCH (ПОИСКПОЗ) возвратит номер столбца, соответствующий этому месяцу.
Пример 4: Находим ближайшее значение при помощи INDEX (ИНДЕКС)
Функция MATCH (ПОИСКПОЗ) отлично работает в сочетании с функцией INDEX (ИНДЕКС), которую мы рассмотрим более пристально чуть позже в рамках данного марафона. В этом примере функция MATCH (ПОИСКПОЗ) использована для того, чтобы найти из нескольких угаданных чисел ближайшее к правильному.
В Microsoft Excel есть так много различных поисков / справочных функций, которые могут помочь вам найти определенное значение в диапазоне ячеек, и MATCH - одна из них. По сути, он идентифицирует относительную позицию или местоположение любого элемента в диапазоне ячеек. Тем не менее, матч может сделать гораздо больше, чем его чистая сущность.
Функция MATCH используется для поиска местоположения искомого значения в таблице или столбце строки. MATCH находит приблизительные и точные совпадения и подстановочные знаки (*?) Для ограниченных совпадений. Большую часть времени функция INDEX интегрирована с функцией MATCH для получения значения в местоположении, возвращаемом MATCH.
МАТЧ Формула в Excel
Функция MATCH проверяет определенное значение в диапазоне ячеек и возвращает соответствующее местоположение этого значения.
Формула для функции MATCH выглядит следующим образом:
- Lookup_value (обязательно) - значение, которое вы ищете. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.
- Lookup_array (обязательно) - поиск по диапазону ячеек.
- Match_type (необязательно) - объясняет тип соответствия. Это может быть любое из следующих значений: 1, 0, -1. Аргумент match_type при установке значения 0 возвращает точное совпадение, в то время как два других типа значений допускают приблизительное совпадение.
1 или опущено (по умолчанию) - поиск наибольшего значения в массиве поиска, которое меньше или равно значению поиска. Требуется сортировка массива поиска в порядке возрастания, от наименьшего к наибольшему или от А до Я.
0 - находит первое значение в массиве, которое абсолютно равно значению поиска. Сортировка не требуется.
-1 - находит наименьшее значение в массиве, которое равно или превышает значение поиска. Массив поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от Z до A.
MATCH в Excel - Использование
Используйте функцию MATCH, чтобы получить соответствующее местоположение элемента в массиве. Match имеет разные типы режимов соответствия, что делает его более универсальным, чем функции поиска. Обычно, когда функция MATCH объединяется с INDEX, она может получить значение зафиксированной позиции.
Информация о типе соответствия
- Если 1 соответствует match_type, MATCH находит наибольшее значение, которое равно или меньше, чем lookup_value. Массив lookup_array должен быть отсортирован в порядке возрастания.
- Если 0 соответствует match_type, MATCH находит первое значение, точно равное lookup_value. lookup_array не требует никакой сортировки.
- Если -1 соответствует match_type, MATCH дает наименьшее значение, которое равно или больше
искомое_значение. Массив lookup_array должен быть отсортирован в порядке убывания.
- Если match_type опущен, он принимается равным 1.
Примечание: все типы совпадений обычно находят точное совпадение.
Типы функции MATCH в Excel
1. Точное совпадение
Функция MATCH выполняет точное совпадение, когда тип совпадения установлен на ноль. В приведенном ниже примере формула в E3:
Вы можете скачать этот шаблон Excel функции MATCH здесь - Шаблон Excel функции MATCH
= ПОИСКПОЗ (E2, B3: B10, 0)
Функция MATCH возвращает точное совпадение как 4 .
2. Приблизительное совпадение
MATCH будет выполнять приблизительное сопоставление для значений, отсортированных по AZ, когда тип соответствия установлен на 1, находя наибольшее значение, которое меньше или равно значению поиска. В приведенном ниже примере формула в E3:
MATCH в Excel возвращает приблизительное совпадение как 7.
3. Подстановочный знак
Функция MATCH может выполнять сопоставление с использованием подстановочных знаков, когда тип совпадения установлен на ноль. В приведенном ниже примере формула в E3:
Функция MATCH возвращает результат подстановочных знаков как «pq».
Примечания:
Как использовать функцию MATCH в Excel?
Функция MATCH в Excel очень проста и удобна в использовании. Давайте рассмотрим работу функции Match в Excel с некоторыми примерами.
Пример № 1
Чтобы лучше понять функцию MATCH, давайте составим простую формулу, основанную на этих данных: имена планет в столбце A с их позициями. Чтобы выяснить, где находится конкретная планета (скажем, Марс), используйте следующую простую формулу:
Функция MATCH возвращает позицию MARS как 4.
Как вы видите на скриншоте выше, имена планет вводятся в произвольном порядке, и поэтому мы устанавливаем аргумент match_type в 0 (точное совпадение), потому что только этот тип совпадения не требует сортировки значений в массиве поиска. Технически, формула соответствия возвращает относительное положение Марса в диапазоне поиска.
Пример № 2 - Если ячейка содержит одну из многих вещей
Общая формула: (= INDEX (результаты, MATCH (TRUE, ISNUMBER (SEARCH (вещи, A1))), 0)))
Объяснение: Для проверки ячейки на одну из многих вещей и выдачи пользовательского результата для первого найденного соответствия можно использовать функцию INDEX / MATCH, сформированную в функции ПОИСК.
В приведенном ниже примере формула в ячейке C5:
(= INDEX (результаты, MATCH (TRUE, ISNUMBER (ПОИСК (вещи, В5)), 0)))
Поскольку выше приведена формула массива, ее следует вводить с помощью клавиш Control + Shift + Enter .
Как работает эта формула?
Эта формула использует два диапазона имен: E5: E8 назван как «вещи», а F5: F8 назван как «результаты». Убедитесь, что вы используете диапазоны имен с одинаковыми именами (в зависимости от данных). Если вы не хотите использовать диапазоны имен, используйте как абсолютные ссылки.
Основной частью этой формулы является следующий фрагмент:
ISNUMBER (SEARCH (вещи, B5)
Это основано на другой формуле, которая проверяет ячейку на наличие одной подстроки. Если в ячейке есть подстрока, формула дает значение ИСТИНА. Если нет, формула дает ЛОЖЬ.
Пример № 3 - Поиск с использованием наименьшего значения
Общая формула = INDEX (диапазон, MATCH (MIN (значения), значения, 0))
Объяснение: Для поиска информации, связанной с самым низким значением в таблице, вы можете использовать формулу в зависимости от функций MATCH, INDEX и MIN.
В примере, показанном ниже, формула используется, чтобы найти имя подрядчика, который имеет самую низкую ставку. Формула в F6:
= ИНДЕКС (В5: В9, MATCH (MIN (С5: С9), С5: C9, 0)))
Как работает эта формула?
Работая изнутри, функция MIN обычно используется, чтобы найти самую низкую ставку в диапазоне C5: C9:
Результат, 99500, подается в функцию MATCH в качестве значения поиска:
Match затем возвращает позицию этого значения в диапазоне 4, который переходит в INDEX в качестве номера строки вместе с B5: B9 в качестве массива:
= INDEX (B5: B9, 4) // возвращает тарелку
Затем функция INDEX возвращает значение в этой позиции: Cymbal .
Ошибки функции соответствия
Кроме того, некоторые пользователи испытывают следующую общую проблему с функцией соответствия:
Вывод
Функция MATCH - это популярная функция, которую большинство из нас использует, не углубляясь в ее механику. Даже после своей тупой цели (чтобы вернуть позицию значения в массиве), функция явно имеет ряд жизнеспособных применений в Excel.
Рекомендуемые статьи
Это было руководство по МАТЧ в Excel. Здесь мы обсуждаем формулу MATCH и как использовать функцию MATCH в Excel с функцией INDEX вместе с практическими примерами функций MATCH и загружаемыми шаблонами Excel. Вы также можете взглянуть на эти другие функции поиска и ссылки в Excel -
Функция соответствия VBA выполняет поиск позиции или номера строки искомого значения в массиве таблиц, то есть в основной таблице Excel. Например, VLOOKUP, HLOOKUP, MATCH, INDEX и т. Д. Это функции поиска, которые важнее других. К сожалению, у нас нет тех же функций, доступных в VBA для упрощения работы. Тем не менее, мы можем использовать эти функции в качестве функций рабочего листа в скрипте VBA, чтобы облегчить нашу жизнь.
Сегодня мы собираемся изучить функцию MATCH, которую можно использовать в качестве функции рабочего листа в VBA.
VBA Match используется так же, как формула соответствия в Excel. Эта функция в MS Excel VBA находит совпадение в массиве со ссылкой на значение поиска и печатает его позицию. Эта функция становится полезной, когда вам нужно оценить данные на основе определенных значений. Например, VBA MATCH полезна, если у вас есть данные о зарплате сотрудников, и вам необходимо узнать числовую позицию сотрудника в ваших данных, у которого зарплата меньше / больше / равна некоторому значению. Это действительно полезно при анализе данных, а также одна строка кода может автоматизировать все для вас.
Синтаксис функции соответствия в Excel VBA
VBA Match имеет следующий синтаксис:
- Arg1 - Lookup_value - значение, которое вам нужно найти в данном массиве.
- Arg2 - Lookup_array - массив строк и столбцов, которые содержат возможные Lookup_value.
- Arg3 - Match_type - тип соответствия, который принимает значение -1, 0 или 1.
Если match_type = -1 означает, что функция MATCH обнаружит наименьшее значение, которое больше или равно lookup_value. Чтобы это произошло, lookup_array должен быть отсортирован в порядке убывания.
Если match_type = 0, это означает, что функция MATCH найдет значение, которое точно совпадает со значением lookup_value.
Если match_type = +1, это означает, что функция MATCH найдет наибольшее значение, которое меньше или равно lookup_value. Чтобы это произошло, lookup_array должен быть отсортирован в порядке возрастания. Значением по умолчанию для типа совпадения является +1.
Как использовать функцию соответствия Excel VBA?
Мы научимся использовать функцию VBA Match Excel на нескольких примерах.
Вы можете скачать этот шаблон VBA Match Excel здесь - Шаблон VBA Match Excel
Предположим, у нас есть данные, как показано ниже:
Нам нужно выяснить, у кого из этого списка зарплата 30 000 евро вместе с должностью в Excel.
Хотя в этом наборе данных мы можем настроить это вручную, подумайте о более широкой картине, что если у вас есть миллионы строк и столбцов?
Выполните следующие шаги, чтобы использовать функцию MATCH в VBA.
Шаг 1: Определите подпроцедуру, дав имя макросу.
Код:
Шаг 2: Теперь мы хотим, чтобы наш вывод был сохранен в ячейке E2. Поэтому начните писать код как Range («E2»). Значение =
Это определяет выходной диапазон для нашего результата.
Код:
Шаг 3: Используйте WorksheetFunction, чтобы использовать функции VBA.
Код:
Шаг 4: WorksheetFunction имеет множество функций, которые могут быть доступны и использоваться в VBA. После «WorksheetFunction» поставьте точку (.), И вы сможете получить доступ к функциям. Выберите функцию MATCH из выпадающего списка.
Код:
Шаг 5: Теперь передайте аргументы функции MATCH. Как Lookup_value. Наше значение Lookup_value хранится в ячейке D2, как показано на снимке экрана ниже. Вы можете получить к нему доступ через функцию MATCH, используя функцию Range.
Код:
Шаг 6: Второй аргумент - Lookup_array. Это диапазон таблицы, в котором вы хотите узнать позицию Lookup_value. В нашем случае это (B1: B11). Предоставьте этот массив, используя функцию Range.
Код:
Шаг 7: Последний аргумент для этого кода - Match_type. Мы хотели иметь точное совпадение для Lookup_value в заданном диапазоне> Поэтому, дайте Zero (0) в качестве совпадающего аргумента.
Код:
Шаг 8: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.
Вы можете видеть в ячейке E2, есть числовое значение (6), которое показывает положение значения от ячейки D2 до диапазона B1: B11.
Пример №2 - Функция соответствия VBA с циклами
Это легко, когда у вас есть только одно значение для поиска во всем диапазоне. Но что, если вам нужно проверить положение на количество ячеек? Человек, который добавляет, попросил бы написать отдельные коды для каждой ячейки.
В таких случаях функцию MATCH можно использовать с циклом (особенно в нашем случае с циклом For). Посмотрите следующие шаги, чтобы получить представление о том, как мы используем функцию MATCH с циклом.
Шаг 1: Определите подпроцедуру, дав имя макросу.
Код:
Шаг 2: Определите целое число, которое может содержать значение для нескольких ячеек в цикле.
Код:
Шаг 3: Используйте цикл For для целого числа, чтобы использовать различные значения поиска, положение которых можно сохранить в столбце E.
Код:
Шаг 4: Теперь используйте тот же метод, который мы использовали в примере 1, просто вместо Range мы будем использовать функцию Cells и будем использовать двумерный массив (строки и столбцы) в отличие от первого примера.
Код:
Здесь Cells (i, 5) .Value = сохраняет значение результирующих позиций в каждой строке от 2 до 6 (строка i) в столбце E (столбец № 5). В функции Match Cells (i, 4) .Values проверяет каждое значение Lookup_value, присутствующее в строках со 2 по 6 в 4- м столбце. Это значение поиска затем ищется в массиве B2: B11 в листе Excel, где имеются данные, и относительные позиции могут быть сохранены в каждой строке столбца 5 (столбец E).
Шаг 5: Запустите этот код, нажав F5 или кнопку Run одновременно и посмотрите результат. Это почти вытянет магию в куске кода в одну строку.
В этой статье мы узнали, как мы можем использовать функцию MATCH в VBA как особый случай WorksheetFunction.
То, что нужно запомнить
Рекомендуемые статьи
Это руководство по функции соответствия VBA. Здесь мы обсуждаем VBA Match и как использовать функцию Excel VBA Match вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Формула роста в Excel является статистической функцией. Формула роста возвращает прогнозируемый экспоненциальный темп роста на основе существующих значений, указанных в excel. Он находится под формулами
Это функция листа. Формула роста доступна во всех версиях Excel.
Эта функция используется для статистического и финансового анализа. Это помогает в прогнозировании и прогнозировании доходов и целей продаж компаний. Формула роста также используется в регрессионном анализе в Excel.
Проще говоря, функция роста вычисляет значения Y на основе экспоненциальной скорости роста данных значений X.
Ниже приведена формула для роста в Excel:
Формула РОСТА имеет следующие аргументы.
* Кривая экспоненциального роста в Excel:
Для формулы роста Y = b * m ^ X
Он представляет экспоненциальную кривую, в которой значение Y зависит от значения X, m является основанием с X в качестве его показателя степени, а b являются постоянными.
- Const: это также необязательный аргумент. Это может быть True или false. Когда это правда, b рассчитывается. Когда это False, то b принимается за 1. Когда этот аргумент не задан, он принимается как True и рассчитывается как обычно.
Это очень полезная функция для оценки или прогнозирования роста, который происходит в геометрической прогрессии.
Как рассчитать рост в Excel, используя формулу?
Это очень просто и легко. Давайте разберемся, как рассчитать РОСТ в Excel на нескольких примерах.
Вы можете скачать этот шаблон Формула Excel РОСТ здесь - Шаблон Формула Excel РОСТ
Формула роста Excel - пример № 1
Мы напишем формулу, как показано ниже:
Теперь мы нажмем клавишу Enter, чтобы увидеть предполагаемый рост.
Формула роста Excel - пример № 2
У нас есть выборочные данные, которые показывают месяцы и соответствующие доходы. Теперь мы оценим выручку за декабрь (12) по формуле роста в Excel.
Мы напишем формулу, как показано ниже:
Нажмите клавишу Enter, чтобы увидеть результат.
Таким образом, предполагаемый доход за 12 месяцев составляет $ 65 014, 97.
Формула роста Excel - пример № 3
Предположим, мы хотим знать, можно ли достичь определенного показателя продаж в наступающем финансовом году. Итак, мы хотим добиться продаж за 12 миллионов долларов.
У нас есть пример таблицы данных:
Мы напишем формулу:
Следовательно, в 2019 году, скорее всего, продажи превысят предполагаемые 12 миллионов.
Формула роста Excel - пример № 4
Принимая вышеприведенную формулу, если мы хотим знать предполагаемые продажи на 2019, 2020 и 2021 год.
Теперь напишем формулу:
Здесь ячейки known_y B2: B10, known_x - это годы, за которые у нас есть данные о продажах. Здесь это будет A2: A10. Теперь new_x будет от ячеек A11 до A13.
Теперь мы будем нажимать CTRL + SHIFT + ENTER, чтобы выполнить его как функцию массива. Отсюда вы увидите фигурные скобки.
Теперь мы оценили объем продаж на 2019 год = 1 499 186, 53 долл. США, 2020 год = 2 035 556, 33 долл. США и 2021 год = 2 763 825, 23 долл. США
Формула роста Excel - пример № 5
Чтобы сделать вещи более понятными, давайте возьмем другой пример.
У нас есть годовые данные о доходах компании, и мы хотим прогнозировать доход на текущий год.
Теперь напишем формулу в ячейку B6:
Нажмите клавишу Enter, чтобы получить вывод.
Предполагаемая выручка за 2019 год составит 1 886 637, 22 долл. США.
Формула роста Excel - пример № 6
Теперь рассмотрим пример экспоненциального роста. У нас есть образец таблицы данных.
Теперь мы напишем формулу, как показано ниже:
Здесь known_y - это ячейки B2: B10, known_x - это ячейки A2: A10, new_x - это ячейки D2: D10, а const равны TRUE (до E8). Мы введем диапазоны ячеек и заблокируем их, нажав F4.
Как только мы напишем формулу, мы нажмем CTRL + SHIFT + ENTER, чтобы выполнить массив.
Теперь мы напишем формулу для оставшихся лет, где const является False .
Нажмите одновременно клавиши CTRL + SHIFT + ENTER, чтобы получить вывод.
Что нужно помнить о формуле роста в Excel
Используя формулу роста Excel, мы должны быть осторожны при правильном написании формулы. Если мы введем неправильный синтаксис, мы не получим правильный результат. Это скорее даст некоторые ошибки. Некоторые из которых упомянуты ниже. Поэтому помните о нижеследующих моментах при использовании функции.
Рекомендуемые статьи
Это было руководство для формулы роста в Excel. Здесь мы обсуждаем Как рассчитать рост в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: