Функция MS Excel ПОИСКПОЗ (MATCH)
Функция ПОИСКПОЗ используется, когда в указанном массиве необходимо найти номер ячейки (начиная сверху), соответствующий условию поиска. Функция ПОИСКПОЗ возвращает не само значение (оно указано в формуле), а позицию в указанном массиве (столбце).
Например, в столбце с данными нужно найти и указать номер ячейки, содержащей число 82.
Мы используем функцию ПОИСК. Его синтаксис следующий.
Lookup_value — это значение, которое нужно искать в списке.
Watched_array — это столбец, в котором отображаются данные.
Collation_type — необязательный аргумент, который может иметь значение 1, 0 или -1. По умолчанию используется тип 1. Однако очень часто требуется 0, потому что функция будет искать точное совпадение с желаемым значением. Если его нет в списке, который вы ищете, функция возвращает ошибку # N / A. Если данные дублируются, то ПОИСКПОЗ указывает первую ячейку сверху, которая соответствует указанным критериям поиска (очень похоже на ВПР). Чтобы выбрать тип -1, вы должны сначала отсортировать список в порядке убывания (или в обратном алфавитном порядке). В этом случае, если нет точного совпадения, функция укажет ячейку с ближайшим более высоким значением. Тип 1 (по умолчанию) работает в обратном порядке. Во-первых, вы должны отсортировать массив в возрастающем (или в алфавитном) порядке. Если нет точного совпадения, функция сообщит номер ячейки, соответствующий ближайшему меньшему значению.
С одной стороны, типы -1 и 1 позволяют искать неправильное совпадение, чтобы расширить возможности поиска, с другой стороны, это требует дополнительных действий (сортировки), что резко снижает уровень автоматизации. Поэтому в большинстве случаев для точного совпадения выбирается 0.
Итак, в приведенном выше примере, чтобы найти ячейку со значением 82, вам нужно будет указать следующую функцию:
Ответ 5, потому что число 82 находится в пятой сверху ячейке. Конечно, вместо 82 можно указать ссылку, по которой находится поисковый запрос.
Функция POPISPOS полностью развита при использовании в сочетании с другими формулами, потому что самого номера ячейки недостаточно.
5 вариантов использования функции ИНДЕКС (INDEX)
У вас бывает: смотришь на человека и думаешь «что за @ # $%) (*?», В общем, добросердечный человек и умница?
Так и в Microsoft Excel: есть несколько похожих функций, на которых фраза «внешность обманчива» работает на 100%. Одна из самых универсальных и полезных функций — функция ИНДЕКС. Не все пользователи Excel знают об этом и еще меньше используют все его возможности. Давайте рассмотрим варианты его применения, ведь их до пяти.
Вариант 1. Извлечение данных из столбца по номеру ячейки
Самый простой вариант использования функции ИНДЕКС — это когда нам нужно извлечь данные из одномерного диапазона столбцов, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет следующим:
= ИНДЕКС (интервал_столбец; порядковый_ячейка)
Этот вариант знаком более опытным пользователям Excel. Поэтому функция ИНДЕКС часто используется вместе с функцией ПОИСКПОЗ, которая возвращает номер желаемого значения в диапазоне. Поэтому эта пара заменяет легендарную ВПР):
но в отличие от ВПР, они могут извлекать значения слева от столбца подстановки, и нет необходимости вычислять номер столбца результатов.
Вариант 2. Извлечение данных из двумерного диапазона
Если диапазон двумерный, например состоит из нескольких строк и столбцов, поэтому наша функция будет использоваться в несколько другом формате:
= ИНДЕКС (диапазон; номер_строки; номер_столбца)
Эта функция извлекает значение из ячейки в диапазоне на пересечении строки и столбца с заданными числами.
несложно понять, что с помощью такой вариации ИНДЕКС и двух функций ПОИСК можно легко реализовать двумерный поиск:
Вариант 3. Несколько таблиц
Если таблицы нет, но их много, функция ИНДЕКС может извлекать данные из необходимой строки и столбца указанной таблицы. В этом случае используется следующий синтаксис:
= ИНДЕКС ((Диапазон1; Диапазон2; Диапазон3); Номер_строки; Номер_столбца; Номер_диапазона)
Обратите внимание, что в этом случае первый аргумент, список диапазонов, заключен в круглые скобки, а диапазоны разделяются точками с запятой.
Вариант 4. Ссылка на столбец /строку
Если во втором варианте использования функции ИНДЕКС номер строки или столбца установлен равным нулю (или просто не указан), функция больше не будет возвращать значение, а будет возвращать ссылку на диапазон столбцов или диапазон строк, соответственно:
Обратите внимание, что, поскольку ИНДЕКС не предоставляет конкретное значение ячейки в этой версии, а представляет собой ссылку на диапазон, для его вычисления вам нужно будет заключить его в дополнительную функцию, такую как СУММ, СРЕДНЕЕ и т.д.
Вариант 5. Ссылка на ячейку
известно, что стандартная ссылка на любой диапазон ячеек в Excel похожа на начало-двоеточие-конец, например A2: B5. Хитрость в том, что если вы возьмете функцию ИНДЕКС в первом или втором варианте и замените ее после двоеточия, наша функция больше не будет возвращать значение, а адрес, и на выходе мы получим полную ссылку на диапазон из начальная ячейка к той, которую нашел ИНДЕКС:
Нечто подобное можно реализовать с помощью функции СМЕЩЕНИЕ, но, в отличие от ИНДЕКСА, она непостоянна, т.е пересчитывается каждый раз, когда изменяется какая-либо ячейка на листе. INDEX, с другой стороны, работает более тонко и начинает пересчет только при изменении его аргументов, что значительно ускоряет вычисления в тяжелых книгах по сравнению с OFFSET.
Один из наиболее распространенных практических сценариев использования ИНДЕКС в этом выпуске — комбинация с функцией СЧЁТ, чтобы автоматически получать диапазоны расширения для раскрывающихся списков, сводных таблиц и т.д.
30 функций Excel за 30 дней: ПОИСКПОЗ (MATCH)
Вчера в марафоне из 30 функций Excel за 30 дней мы нашли текстовые строки с помощью функции ПОИСК, а также использовали SEERROR и ISNUMBER в ситуациях, когда функция выдает ошибку.
В 19 день нашего марафона мы изучим функцию ПОИСКПОЗ. Ищет значение в массиве и возвращает его позицию, если она найдена.
Итак, давайте взглянем на справочник по функциям COMPARE и рассмотрим несколько примеров. Если у вас есть собственные примеры или подходы к работе с этой функцией, поделитесь ими в комментариях.
Функция 19: MATCH (ПОИСКПОЗ)
Функция ПОИСКПОЗ возвращает позицию значения в массиве или ошибку # Н/Д (# Н/Д), если она не найдена. Массив может быть отсортированным или несортированным. Функция ПОИСКПОЗ не чувствительна к регистру.
Как можно использовать функцию MATCH (ПОИСКПОЗ)?
Функция ПОИСКПОЗ возвращает позицию элемента в массиве, и этот результат может использоваться другими функциями, такими как ИНДЕКС или ВПР. Например:
- Находит позицию элемента в неупорядоченном списке.
- Используйте вместе с CHOOSE, чтобы перевести успеваемость учащихся в буквенные оценки.
- Используйте вместе с функцией ВПР для гибкого выбора столбца.
- Используйте вместе с ИНДЕКС, чтобы найти ближайшее значение.
Синтаксис MATCH (ПОИСКПОЗ)
Функция ПОИСКПОЗ имеет следующий синтаксис:
ПОИСКПОЗ (искомое_значение, искомое_массив, [тип_сопоставления])
ПОИСКПОЗ (искомое_значение; искомое_массив; [тип_сопоставления])
- lookup_value (lookup_value) — может быть текстом, числом или логическим значением.
- lookup_array (lookup_array) — это массив или ссылка на массив (соседние ячейки в том же столбце или строке).
- match_type (match_type) — может принимать три значения: -1 или 1. Если аргумент опущен, он равен 1.
Ловушки MATCH (ПОИСКПОЗ)
Функция ПОИСКПОЗ возвращает положение найденного элемента, но не его значение. Если вам нужно вернуть значение, используйте ПОИСКПОЗ вместе с функцией ИНДЕКС).
Пример 1: Находим элемент в несортированном списке
Для неупорядоченного списка вы можете использовать match_type (match_type) в качестве значения аргумента для поиска точного совпадения. Если вам нужно найти точное совпадение текстовой строки, вы можете использовать подстановочные знаки в поисковом значении.
В следующем примере, чтобы найти позицию месяца в списке, мы можем написать название месяца полностью или частично, используя подстановочные знаки.
вы можете использовать массив констант в качестве аргумента для lookup_array (lookup_array). В следующем примере месяц поиска вводится в ячейку D5, а названия месяцев подставляются в качестве второго аргумента функции ПОИСКПОЗ в виде массива констант. Если вы введете следующий месяц в ячейку D5, например, октябрь (октябрь), функция вернет # Н / Д (# Н / Д).
Пример 2: Изменяем оценки учащихся c процентов на буквы
Вы можете преобразовать оценки учащихся в буквенные системы с помощью функции ПОИСКПОЗ, как и в случае с ВПР. В этом примере функция используется вместе с CHOOSE, которая возвращает желаемый результат. Аргумент match_type (match_type) установлен в -1, потому что оценки в таблице отсортированы в порядке убывания.
Когда match_type (match_type) равен -1, результатом является наименьшее значение, большее или эквивалентное желаемому значению. В нашем примере желаемое значение — 54. Поскольку такого значения нет в списке точек, возвращается элемент, соответствующий значению 60. Поскольку 60 находится на четвертой позиции в списке, результат функции ВЫБОР будет быть значением, которое находится в 4-й позиции, то есть в ячейке C6, которая содержит степень D.
Пример 3: Создаем гибкий выбор столбца для VLOOKUP (ВПР)
Чтобы придать функции ВПР большую гибкость, вы можете использовать ПОИСКПОЗ для поиска номера столбца вместо кодирования его значения в функции. В приведенном ниже примере пользователи могут выбрать область в ячейке H1, это значение поиска для ВПР. Затем они могут выбрать месяц в ячейке H2, и функция ПОИСКПОЗ вернет номер столбца, соответствующий этому месяцу.
Пример 4: Находим ближайшее значение при помощи INDEX (ИНДЕКС)
Функция ПОИСКПОЗ прекрасно работает в сочетании с функцией ИНДЕКС, которую мы рассмотрим подробнее чуть позже в этом марафоне. В этом примере функция ПОИСКПОЗ используется для поиска числа, ближайшего к правильному, из числа угаданных чисел.
- Функция ABS возвращает величину разницы между каждым угаданным и правильным числом.
- Функция MIN находит наименьшую разницу.
- Функция ПОИСКПОЗ находит адрес наименьшего различия в списке различий. Если в списке есть несколько совпадающих значений, будет возвращено первое.
- Функция ИНДЕКС возвращает имя, соответствующее этой позиции из списка имен.
ПОИСКПОЗ (функция ПОИСКПОЗ)
Функция ПОИСКПОЗ ищет указанный элемент в диапазоне ячеек и возвращает относительное положение этого элемента в диапазоне. Например, если диапазон A1: A3 содержит значения 5, 25 и 38, формула = MATCH (25; A1: A3; 0) возвращает 2, поскольку 25 — второе в диапазоне.
Совет: используйте функцию ПОИСКПОЗ вместо одной из функций ПОИСК, если вы хотите найти позицию элемента в диапазоне, а не сам элемент. Например, функция ПОИСКПОЗ может использоваться для передачи значения аргумента номер_строки функции ИНДЕКС.
Аргументы функции ПОИСКПОЗ описаны ниже.
Ценность исследования. Тема обязательна. Значение для сравнения со значениями в lookup_array. Например, при поиске номера в телефонной книге имя абонента указывается в качестве значения поиска, а желаемое значение — это номер телефона.
Lookup_value может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую это значение.
Lookup_array Обязательный. Диапазон ячеек для поиска.
Match_type. Необязательный аргумент. Число — -1, 0 или 1. Аргумент match_type указывает, как Microsoft Excel сопоставляет lookup_value со значениями в lookup_array. Значение по умолчанию для этого аргумента — 1.
В следующей таблице описывается, как функция находит значения на основе аргумента match_type.
MS Excel MATCH / ПОИСКПОЗ
Возвращает позицию (номер строки или номер столбца) искомого значения в массиве.
ПОИСКПОЗ (искомое_значение; искомое_массив; совпадение_типа)
0 — поиск точного значения;
1 — поиск точного или ближайшего значения (данные отсортированы по возрастанию);
-1 — поиск точного значения или ближайшего наибольшего значения (данные отсортированы по убыванию).
Пример использования функции.
У нас есть таблица продаж детских товаров:
Мы хотим знать, какой продукт продавался больше всего.
Для этого сначала находим номер строки для продукта, продажи которого выше, чем у других:
lookup_value — искомое значение: максимум из массива «Продано, шт.»: MAX (B2: B7);
lookup_array — поисковый массив запрошенного значения (B2: B7);
match_type — тип поиска: 0 (поиск точного значения).
Результат поиска по номеру строки:
Затем, используя функцию ИНДЕКС () / ИНДЕКС (), мы находим название продукта на основе найденного номера строки:
массив — таблица product_sold (A2: B7);
row_num — номер строки искомого значения: найденного нами с помощью функции ПОИСКПОЗ();
column_num — номер столбца желаемого значения: 1 (столбец с товарами).
