Множественная ВПР - поиск по критериям

У вас есть эти варианты

Самый быстрый и лучший способ поиска определенных критериев - использовать функцию ВПР. С его помощью можно найти значение в Microsoft Excel в миллисекундах.

ВПР - вот как это работает с критерием поиска

Чтобы понять, как ВПР работает с несколькими критериями поиска, пользователям Excel сначала необходимо понять, как в основном структурирована функция ВПР. В следующем примере показано количество временных работников, занятых в компании в разные месяцы календарного года.

Следует использовать ВПР, чтобы показать, сколько временных работников было нанято в марте и декабре.

Формула ВПР в примере для декабря:

ВПР (D8; A: B; 2; 0)

Формула ищет соответствующие числа для временных работников следующим образом:

1. Найдите значение D8 (декабрь месяц) в столбцах A и B.
2. Найдите значение столбца B. Это обозначено цифрой 2 в формуле.
3. 0 в формуле означает, что нужно искать точное значение.

Функция ВПР ищет единственный критерий поиска. Матрица формулы - это область, в которой расположены соответствующие значения. Кроме того, столбец ВПР должен содержать искомое значение.

Важный:

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

ВПР с несколькими критериями поиска - как интегрировать функцию ЕСЛИ

В приведенном выше примере был задан вопрос, сколько временных работников работали в компании в определенный месяц. С помощью функции ВПР можно легко получить конкретные значения. В следующем примере вы можете увидеть обширный список статей из 36 статей. [1], из которого вы хотите быстро и легко отфильтровать подходящие номера артикулов по нескольким критериям.

Критерии поиска перечислены в таблице справа. Найденный номер статьи должен появиться в ячейке H8.

Первый шаг - включить формулу с функцией ВПР, которую можно использовать для поиска номера элемента на основе критерия поиска. Затем вы шаг за шагом расширяете эту формулу для других критериев поиска.

Используйте следующую формулу в ячейке H8, чтобы найти номер элемента для размера из ячейки H3: [2]

= ВПР (H3; A3: E40; 5

Формула выполняет поиск соответствующего артикульного номера следующим образом:

1. Найдите значение H3 (размер 142) в столбцах от A до E.
2. Найдите значение в столбце E (номер статьи). Это обозначено цифрой 5 в формуле.

Таким образом, функция ВПР передает полный список, в котором вы ищете контент, в качестве первого аргумента. Критерий поиска, который должен быть найден в первом столбце переданной области, фокусируется как второй аргумент. Третий аргумент определяет столбец, из которого должен быть возвращен результат.

Отправьте список, отсортированный в порядке возрастания на основе первого столбца, чтобы Excel мог найти следующее меньшее значение. Формула находит артикул 2.253 из ячейки E16. Поскольку размер 142 из ячейки H3 не отображается в списке, будет найдено следующее меньшее значение 139.

Включите второй критерий поиска

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

Для этого добавьте функцию ЕСЛИ к формуле в ячейке H8 и вставьте формулу как формулу массива:

= ВПР (H3; ЕСЛИ (B3: B40 = H4; A3: E40; «»); 5)

Информация:

В Microsoft Excel проводится различие между обычными формулами и более мощными матричными формулами. Матричные формулы позволяют пользователям Excel выполнять сложные вычисления. Это невозможно сделать с помощью стандартных формул. Поскольку после ввода формулы необходимо нажать комбинацию клавиш «CTRL + SHIFT + ENTER», матричные формулы во всем мире известны как формулы CSE (CTRL + SHIFT + ENTER).

Подтвердите формулу после ее ввода, используя комбинацию клавиш Ctrl + Shift + Enter в качестве матричной формулы. [3]

В результате поставляется позиция 1.188. Формула находит в столбце «Размер» значение 126. Это наибольшее значение в таблице. Оно меньше или равно искомому значению 142 и имеет содержание C2 по отношению к группе.

Функция ЕСЛИ в формуле проверяет каждую ячейку в диапазоне B3: B40 на соответствие содержимому ячейки H4. Как только есть совпадение, соответствующая строка в области B3: E40 передается в функцию ВПР.

Легко добавлять дополнительные критерии поиска в любое время

Таким же образом вы можете добавить дополнительные критерии поиска. Это можно сделать, вложив в формулу больше функций ЕСЛИ. Чтобы учесть все четыре критерия поиска, включите еще две вложенные функции ЕСЛИ в формулу в ячейке H8:

= ВПР (H3; ЕСЛИ (B3: B40 = H4; ЕСЛИ (C3: C40 = H5; ЕСЛИ (D3: D40 = H6; A3: E40; ««); ««); ««); 5)

После подтверждения формулы матрицы с помощью комбинации клавиш Ctrl + Shift + Enter, ВПР возвращает номер статьи 1.748. Это номер из строки 14 для размера 125. Это строка с наибольшим значением в столбце «Размер», в котором соблюдены все три дополнительных критерия. [4]

Первая функция ЕСЛИ не передает сразу соответствующую строку функции ВПР, если проверка положительна. Вместо этого другая функция ЕСЛИ проверяет соответствие второму дополнительному критерию. Если эта проверка также положительна, используется третья функция ЕСЛИ. Только когда все три функции ЕСЛИ возвращают положительный результат, третья функция ЕСЛИ передает соответствующую строку функции ВПР. Таким образом, вы можете вложить до семи функций ЕСЛИ и, таким образом, проверить до восьми критериев.

Резюме: Вот эффективный способ поиска по нескольким критериям в Excel.

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

Поскольку функция ВПР по умолчанию ограничена одним термином поиска, расширенная формула должна использоваться для нескольких критериев поиска. Чтобы найти несколько условий поиска с помощью функции ВПР, подходит формула массива с функциями ЕСЛИ. Он проверяет отдельные функции ЕСЛИ одну за другой и, если результат положительный, обращается к ВПР. Это гарантирует, что правильный результат будет выводиться для обширных электронных таблиц с различными условиями поиска.

часто задаваемые вопросы

Что умеет функция ВПР?

Функция ВПР в Microsoft Excel - одна из самых важных и эффективных функций в Excel. С помощью ВПР вы можете автоматически искать определенное значение в обширной электронной таблице. Результат редактируется и выводится в другую ячейку.

Когда вы используете функцию ЕСЛИ в EXCEL?

Функция ЕСЛИ - одна из наиболее широко используемых функций в Microsoft Excel. На первом этапе функция ЕСЛИ используется для определения условия. На втором этапе вы связываете IF, например, с функцией THEN и указываете, какая информация должна выводиться. Функцию ELSE также можно использовать для определения альтернативного результата. Практический пример: ЕСЛИ сотрудник A проработал более 240 дней в году, ТО ТОГДА премия A ИНАЧЕ премия B.

В чем разница между формулами массива и стандартными формулами в Excel?

Наиболее известными в Excel являются стандартные стандартные формулы. В Excel матричные формулы - это мощные формулы, с помощью которых можно выполнять сложные вычисления. Для выполнения формулы массива необходимо подтвердить формулу массива после ввода ее с помощью комбинации клавиш «CTRL + SHIFT + ENTER». Сочетание клавиш показывает Excel, что это формула массива.