Поиск в excel по нескольким условиям в excel: Поиск в excel по нескольким условиям в excel

Содержание

Соответствие нескольким критериям Excel — voxt.ru

Соответствие нескольким критериям в Excel

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

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

Как соответствовать нескольким критериям Excel? (Примеры)

Вы можете скачать этот шаблон Excel по нескольким критериям здесь — шаблон Excel по нескольким критериям

Пример # 1 — Логические функции в Excel

Обычно мы используем три логические функции в Excel для выполнения вычислений нескольких критериев, и эти три функции являются условиями ЕСЛИ, И и ИЛИ.

Например, посмотрите на приведенный ниже расчет формулы И.

Посмотрите на формулу ниже; мы проверили, составляет ли значение ячейки A1> 20 или нет, значение ячейки B1> 20 или нет, значение ячейки C1> 20 или нет.

В приведенной выше функции все значения ячеек> 20, поэтому результат ИСТИНА. Теперь я изменю одно из значений ячейки на менее 20.

Значение ячейки B1 изменено на 18, что меньше 20, поэтому конечный результат — ЛОЖЬ, даже если значения двух других ячеек> 20.

Теперь для того же числа я применим функцию ИЛИ в excel.

Для функции ИЛИ любой критерий должен удовлетворять, чтобы получить результат ИСТИНА; это не похоже на функцию И, где нам нужны все условия, чтобы получить ИСТИННЫЙ результат.

Пример # 2 — Несколько логических функций в Excel

Теперь мы увидим, как выполнять несколько логических функций для соответствия нескольким условиям.

  • Посмотрите на данные ниже в Excel.

Исходя из приведенных выше данных, нам необходимо рассчитать сумму «Бонуса» на основе следующих условий.

Если срок службы> 1,5 года и если «Dept.» это «Продажи» или «Поддержка», тогда бонус составляет 8000 долларов США; в противном случае бонус составит 4000 долларов.

Итак, чтобы получить один результат, нам нужно соответствовать нескольким критериям в Excel. В этом случае нам нужно проверить, составляет ли год обслуживания 1,5 года, а также проверить, является ли отдел «продажами» или «поддержкой», чтобы получить сумму бонуса.

  • Первое открытое состояние в Excel.
  • Первое логическое условие для проверки является обязательным, независимо от того, составляет ли год службы более 1,5 лет или нет. Поэтому для получения обязательного ИСТИННОГО результата нам нужно использовать функцию И.

Вышеупомянутый логический тест вернет ИСТИНА, только если год службы> 1,5; иначе в результате мы получим ЛОЖЬ. Внутри этой функции AND нам нужно проверить еще один критерий, то есть, является ли отдел «Продажами» или «Поддержкой», чтобы получить сумму бонуса.

  • В этом случае, если любой отдел соответствует этим критериям, нам нужен ИСТИННЫЙ результат, поэтому нам нужно использовать функцию ИЛИ.

Как вы можете видеть выше, мы использовали функции И и ИЛИ для соответствия единым критериям. Функция И проверяет, что год обслуживания превышает 1,5 года, а функция ИЛИ проверяет, является ли отдел продажей или службой поддержки. Таким образом, если Год обслуживания равен 1,5, а отдел — либо Продажи, либо Поддержка, логический результат теста будет ИСТИНА или ЛОЖЬ.

  • Теперь, если логический тест ИСТИНА, то нам нужна сумма бонуса в размере 8000 долларов. Итак, для Value, если аргумент True, нам нужно указать # 8000.
  • Если логический тест — ЛОЖЬ, то нам нужна сумма бонуса в размере 4000 долларов.
  • Итак, мы закончили с применением формулы, закрываем формулу и применяем формулу к другим ячейкам, чтобы получить результат в других ячейках.

Теперь посмотрим на 4th строка; в этом случае Год службы равен 2,4, а отдел — «Поддержка», поэтому бонус достигает 8000 долларов. Таким образом, мы можем сопоставить несколько критериев в Excel.

То, что нужно запомнить

  • Чтобы проверить несколько критериев для получения единого результата, нам нужно использовать несколько логических функций внутри условия IF.
  • И и ИЛИ — две вспомогательные функции, которые мы можем использовать для проверки нескольких критериев.
  • И вернет ИСТИНА, только если все логические тесты выполнены, но, с другой стороны, ИЛИ требует выполнения хотя бы одного логического теста, чтобы получить ИСТИНА результат.

УЗНАТЬ БОЛЬШЕ >>



Post Views:
718

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

Прокрутить вверх

Как впр из разных файлов

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

Самая простая функция ВПР означает следующее:

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

Используйте функцию ВПР для поиска значения в таблице.

ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

= ВПР (A2; A10: C20; 2; ИСТИНА)

Значение для поиска. Значение, которое вы хотите найти, должно находиться в первом столбце диапазона ячеек, указанного в аргументе инфо_таблица .

Например, если Таблица-массив охватывает ячейки B2: D7, то искомое_значение должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

Первый столбец в диапазоне ячеек должен содержать Искомое_значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.

Номер столбца (начиная с 1 для самого левого столбца массива « инфо_таблица»), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

Значение, которое вам нужно найти, то есть искомое значение.

Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

Вот несколько примеров использования функции ВПР.

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Неправильное возвращаемое значение

Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.

Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.

#ССЫЛКА! в ячейке

Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате «Иванов» и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

Это конкретная ошибка #SPILL! Обычно это означает, что формула полагается на неявное пересечение для искомого значения и использует в качестве ссылки весь столбец. Например, = ВПР ( а:а, а:к, 2; ложь). Проблему можно устранить, заменив ссылку для подстановки с помощью оператора @ следующим образом: = ВПР ( @ а:а, а:к, 2, ложь). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку, а не на весь столбец: = ВПР ( a2; а:к; 2; ложь).

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

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

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (

) перед искомым символом.

Например, функция = ВПР («Ивано?»; B2: E7; 2; ложь) будет искать все вхождения Иванов с последней буквой, которая может измениться.

Убедитесь, что данные не содержат ошибочных символов.

При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (‘ или «) и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение.

Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

Что такое функция ВПР в Эксель – область применения

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

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

В случаях, когда работников предприятия всего два-три, или товаров – до десятка, можно сделать все вручную. При должной внимательности работать человек будет без ошибок. Но если значений для обработки, например, тысяча, требуется автоматизация работы. Для этого в Excel существует ВПР (анг. VLOOKUP).

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

Исходные данные таблица 1

Ф. И. О.Штраф
Петров12 000,00 ₽
Сидоров200,00 ₽
Иванов38 000,00 ₽

Исходные данные таблица 2

Ф. И. О.З.П.
Иванов20 000 ₽
Петров19 000 ₽
Сидоров21 000 ₽

Объединенные данные таблица 3

Ф. И. О.З.П.Штраф
Иванов20 000 ₽38 000 ₽
Петров19 000 ₽12 000 ₽
Сидоров21 000 ₽200 ₽

Функция ВПР в Excel – как пользоваться

Для того чтобы таблица 1 пришла к конечному виду, в ней вписываем заголовок столбца, например «Штраф». На самом деле, это необязательно, можно написать любой текст, или оставить его незаполненным. Работать функция будет также по клику мыши в поле, где должно появиться найденное в другой таблице значение.

Теперь нужно вызвать функцию. Это можно сделать разными способами:

  1. Прописать функцию вручную.
  2. Нажать «Вставить функцию», выбрать «Ссылки и массивы», выбрать ВПР.
  3. Открыть вкладку «Формулы», выбрать «Ссылки и массивы», выбрать ВПР.

Рекомендуется для простоты не набирать весь синтаксис функции вручную, а воспользоваться формой, открывающейся по пунктам п. 2–3. Откроется диалоговое окно для заполнения значений.

Необходимо заполнить значения для функции ВПР

  1. Искомое_значение – это то, что Эксель будет искать в другой таблице. В нашем случае, Иванов, Петров, Сидоров. Кликаем по первой фамилии в списке это ячейка А2. Удерживаем на клавиатуре клавишу Shift, после чего кликаем по последней фамилии в столбце, ячейка А4. В итоге получаем выделенный список фамилий с которым ВПР будет работать.
  2. Таблица – место где будет осуществлен поиск. В нашем случае вторая таблица лежит в Лист 2. Переходим на него. Здесь необходимо выделить весь интервал поиска. Кликаем по левой верхней ячейке интервала. Удерживаем на клавиатуре Shift и нажимаем левой кнопкой мыши правую нижнюю ячейку. Нажимаем F4. Интервал поиска задан.
  3. Номер_столбца – номер столбца из таблицы, в которой ищутся данные. В нашем случае – 2.
  4. Интервальный_просмотр – это логическое значение. В нашем случае необходимо написать «ЛОЖЬ».
  5. Нажать «ОК»
  6. Теперь необходимо скопировать функцию в каждую пустую ячейку. Можно, например, сделать с помощью копировать — вставить, а можно нажать на ячейку с результатом работы функции, и в правом нижнем углу кнопкой мыши слева нажать на жирный квадратик, не отпуская левую кнопку потянуть вниз.

Результат налицо – в таблице 3 (смотреть выше).

ВПР – инструкция для работы с двумя условиями

В ситуациях, когда необходимо задать критерии поиска не по одному условию, а сразу по нескольким – двум столбцам, функцию ВПР также можно использовать для работы. Для этого исходные данные необходимо доработать.

Пример, необходимо в таблицу 4, вставить цену из таблицы 5.

Характеристики телефонов таблица 4

НазваниеОЗУКамера
ZTE0,55
ZTE15
DNS18
DNS0,58
Alcatel18
Alcatel2562

Характеристики телефонов таблица 5

НазваниеОЗУЦена
ZTE0,51 990 ₽
ZTE13 099 ₽
DNS13 100 ₽
DNS0,52 240 ₽
Alcatel14 500 ₽
Alcatel256450 ₽

Пример выбран на телефонах, но понятно, что данные могут быть совершенно любыми. Как видно из таблиц, марки телефонов не отличаются, а отличаются ОЗУ и Камера. Для создания сводных данных нам нужно выбрать телефоны по марке и ОЗУ. Для работы функции ВПР по нескольким условиям нужно столбцы с условиями объединить.

Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Размножаем с помощью мыши. Получается, как в таблице 6.

Характеристики телефонов таблица 6

ОбъединениеНазваниеОЗУЦена
ZTE 0,5ZTE0,51 990 ₽
ZTE 1ZTE13 099 ₽
DNS1DNS13 100 ₽
DNS 0,5DNS0,52 240 ₽
Alcatel 1Alcatel14 500 ₽
Alcatel 256Alcatel256450 ₽

Таблицу 5 обрабатываем точно так же. После чего функцию ВПР применяем для поиска по одному условию. Условием являются данные из объединенных столбцов. Не забывайте, что номер столбца, откуда берутся данные в функции ВПР изменится. После применения функции получится выборка по двум условиям. Можно объединить не соседние столбцы, а столбцы с маркой телефона и камерой.

Смотрите видеоурок как пользоваться функцией ВПР в Эксель для чайников:

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

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

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

Как работает функция ВПР в Excel: пример

Функция ВПР предназначена для выборки данных из таблицы Excel по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.

  1. Переходим в ячейку второй таблицы под названием столбца «Цена».
  2. Выберите «Формулы»-«Ссылки и массивы»-«ВПР». Ввести функцию ВПР можно и с помощью «мастера функций». Для этого нажмите на кнопку «fx», которая находиться в начале строки формул. Или нажмите комбинацию горячих клавиш SHIFT+F3. В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.
  3. Заполняем аргументы функции.

В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.

Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.

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

Функция ВПР в Excel и две таблицы

Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:

Как видите вторую таблицу так же нужно немного изменить, чтобы не потерять суть задачи.

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

  1. Исходное значение: G3.
  2. Таблица: A2:E7. Диапазон нашей таблицы расширен.
  3. Номер столбца: <3;4;5>. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
  4. Интервальный просмотр: ЛОЖЬ.
  5. Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;<3;4;5>;ЛОЖЬ)).
  6. После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «<>», что свидетельствует о выполнении формулы в массиве.

Теперь вводите в ячейку G3 наименование товара, в ячейке h4 получаем сумму продаж в первом квартале по данному товару.

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

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

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

Другими словами если в нашей таблице повторяются значения «груши», «яблока» мы не сможем просуммировать всех груш и яблок. Для этого нужно использовать функцию ПРОСМОТР(). Она очень похожа на ВПР но умеет хорошо работать с массивами в исходных значениях.

Как выполнять поиск по нескольким критериям в Excel (как по И, так и по ИЛИ)

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

Скачать практическую рабочую тетрадь

Как выполнить поиск по нескольким критериям в Excel

Поиск нескольких критериев типа AND

1. Использование функции ИНДЕКС-ПОИСКПОЗ

2. Использование функции XLOOKUP

3. Использование функции ФИЛЬТР

Поиск нескольких критериев типа OR

1. Использование функции ИНДЕКС-ПОИСКПОЗ

2. Использование функции XLOOKUP

3. Использование функции ФИЛЬТР

Вывод

Дальнейшие чтения

Загрузить рабочую тетрадь

Как искать с несколькими критериями в Excel

Посмотрите на набор данных ниже. У нас есть идентификаторы сотрудников, имена сотрудников, даты вступления и зарплаты компании под названием Jupyter Group.

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

Поиск нескольких критериев типа AND

Прежде всего, давайте попробуем найти несколько критериев типа AND.

Здесь, И введите несколько критериев, означает, что одно значение должно удовлетворять всем выбираемым критериям.

Попробуем найти сотрудника с ID больше 400 и зарплатой больше $40000.

Вы можете выполнить задание тремя различными способами.

1. Использование функции ИНДЕКС-ПОИСКПОЗ

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

Формула INDEX-MATCH для определения сотрудника с ID больше 400 и зарплатой больше $40000 будет следующей:

=ИНДЕКС(C4:C20,ПОИСКПОЗ(1,(B4:B20>400)*(E4:E20>40000),0),1)

[Это формула массива . Так что не забудьте нажать Ctrl + Shift + Enter , если вы не находитесь в Office 365 .]

Смотрите, мы нашли сотрудника с удостоверением личности больше 400 и зарплатой больше 40000 долларов, Ричарда Самуэльсона.

Объяснение формулы

  • В4:В20>400 просматривает все идентификаторы в столбце B и возвращает массив из ИСТИНА и ЛОЖЬ , ИСТИНА , если идентификатор больше 400, ЛОЖЬ в противном случае.
  • E4: E20> 40000 Проходит все зарплаты в столбце E и возвращает массив True и False , True , когда зарплата превышает 40 000 долларов США, False иначе , , , , , , , , .0030 .
  • (B4:B20>400)*(E4:E20>40000) умножает два массива TRUE и FALSE и возвращает 1 , когда ID больше 40 и зарплата превышает 40 000 долларов.

Возвращает 0 в противном случае.

  • ПОИСКПОЗ(1,(B4:B20>400)*(E4:E20>40000),0) проходит через массив (B4:B20>400)*(E4:E20>40000) и возвращает серийный номер первого встречного 1.

В этом случае возвращается 5, потому что первая 1 находится в серийном номере 5 (см. рисунок выше)

  • Наконец, ИНДЕКС(C4:C20,MATCH(1,(B4:B20>400)*(E4:E20>40000),0),1) возвращает имя сотрудника из диапазона C4:C20 , с номером строки, равным выходу функции MATCH , и номером столбца, равным 1.

Требуется сотрудник с идентификатором больше 400 и зарплатой больше 40 000 долларов.

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

Да. Ты прав. Формула будет:

=ИНДЕКС(C4:C20,СООТВЕТСТВИЕ(1,(D4:D20<ДАТА(2009,12,31))*(E4:E20<25000),0),1)

Смотрите, это Анджела Хопкинс.

Подробнее: 7 типов поиска, которые можно использовать в Excel

2. Использование функции XLOOKUP

Мы также можем выполнить предыдущую задачу, используя XLOOKUP функцию Excel.

Но помните, XLOOKUP доступен только в Office 365 .

Прежде чем перейти к главному, вы можете взглянуть на функцию Excel XLOOKUP .

XLOOKUP 9Формула 0031 для определения сотрудника с идентификатором больше 400 и зарплатой больше 40 000 долларов будет выглядеть так:

=XLOOKUP(1,(B4:B20>400)*(E4:E20>40000),C4:C20)

Смотрите, у нас есть тот же сотрудник, что и раньше, Ричард Самуэльсон.

Объяснение формулы

  • (B4:B20>400)*(E4:E20>40000) возвращает массив 1 и 0 , 1 , если идентификатор больше 400, а зарплата больше 40 000 долларов. 0 иначе (см. предыдущий раздел).
  • XLOOKUP(1,(B4:B20>400)*(E4:E20>40000),C4:C20) первый поиск 1 в массиве (B4:B20>400)*(E4:E20 >40000) . Когда он находит его, он возвращает значение из соседней ячейки в диапазоне C4:C20 .

Это имя сотрудника с идентификатором больше 400 и зарплатой больше 40 000 долларов.

Подробнее: Как использовать функцию ПРОСМОТР в Excel (4 подходящих примера)

3. Использование функции ФИЛЬТР

Формула INDEX-MATCH и XLOOKUP имеют одно ограничение. Если заданным критериям удовлетворяет более одного значения, возвращается только первое значение.

Например, в предыдущем примере, если вы внимательно посмотрите, вы обнаружите, что есть два сотрудника с идентификатором больше 400 и зарплатой больше 40 000 долларов.

Ричард Самуэльсон и Усман Малик.

Но формула INDEX-MATCH и XLOOKUP возвращают только первого сотрудника, Ричарда Самуэльсона.

Чтобы получить все значения, удовлетворяющие заданным критериям, вы можете использовать ФИЛЬТР функцию Excel.

Прежде чем перейти к главному, вы можете взглянуть на ФИЛЬТР функцию Excel.

Но помните, что функция ФИЛЬТР также доступна только в офис 365 .

Чтобы найти сотрудников с идентификатором больше 400 и зарплатой больше 40 000 долларов, формула ФИЛЬТР будет выглядеть так:

=FILTER( C4:C20 ,( B4:B20 >400)*( E4:E20 >40000) )

Смотрите, на этот раз у нас есть все сотрудники, которые соответствуют всем критериям, Ричард Самуэльсон и Усман Малик.

Объяснение формулы

  • (B4:B20>400)*(E4:E20>40000) возвращает массив 1 и 0 , 1 , когда идентификатор больше 400 $040,0 и зарплата больше . 0 в противном случае (см. раздел INDEX-MATCH ).
  • ФИЛЬТР( C4:C20 ,( B4:B20 >400)*( E4:E20 >40000)) перебирает все значения в массиве , и когда он находит 1 , он возвращает соседнее значение из диапазона C4:C20 .
  • Таким образом, мы получаем всех сотрудников с идентификатором больше 400 и зарплатой больше 40 000 долларов.

Теперь, если вы это понимаете, можете ли вы сказать мне формулу, чтобы узнать сотрудников, которые присоединились к нам в период с 1 января 2014 года по 31 декабря 2016 года, но получают зарплату не менее 30 000 долларов США.

Да. Ты прав. Формула будет:

=ФИЛЬТР(C4:C20,(D4:D20>=ДАТА(2014,1,1))*(D4:D20<=ДАТА(2016,12,31))*(E4:E20>=30000 ))

Подробнее: Как найти несколько значений в Excel (10 способов)

Поиск нескольких критериев типа OR

Теперь мы попытаемся найти некоторые значения, которые удовлетворяют нескольким критериям типа ИЛИ.

Здесь критерии типа ИЛИ означают, что одно значение должно удовлетворять хотя бы одному критерию среди всех выбираемых критериев.

Попробуем найти сотрудника, который присоединился к нам до 1 января 2010 года или получает зарплату больше 30 000 долларов.

1. Использование функции ИНДЕКС-ПОИСКПОЗ

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

ИНДЕКС-СООТВЕТСТВИЕ 9Формула 0031 будет:

=ИНДЕКС(C4:C20,СООТВЕТСТВИЕ(ИСТИНА,((D4:D20<ДАТА(2010,1,1))+(E4:E20>30000))>0,0),1)

[ Формула массива . Так что не забудьте нажать Ctrl + Shift + Enter , если вы не находитесь в Office 365 .]

Смотрите, у нас есть Джек Симпсон, первый сотрудник с датой приема на работу до 1 января 2010 года или зарплатой более 30 000 долларов.

Но сотрудников гораздо больше. Использование ИНДЕКС-ПОИСКПОЗ, получаем только первый.

Мы соберем всех сотрудников вместе с помощью функции ФИЛЬТР позже.

Объяснение формулы

  • D4:D20 возвращает массив из TRUE и FALSE . TRUE , если дата присоединения в столбце D меньше 1 января 2010 г. FALSE в противном случае.
  • E4:E20>30000 также возвращает массив из TRUE и FALSE . TRUE , если зарплата превышает 30 000 долларов. ЛОЖЬ иначе.
  • (D4:D2030000) добавляет два массива и возвращает другой массив 0, 1 или 2 . 0 , когда не выполняется ни один критерий, 1 , когда выполняется только один критерий и 2 , когда оба критерия удовлетворены.
  • ((D4:D2030000))>0 перебирает все значения массива (D4:D2030000) и возвращает TRUE , если значение больше 0 ( 1 и 2 ), и FALSE в противном случае (

    3 0 ) .

  • ПОИСКПОЗ(ИСТИНА,((D4:D20<ДАТА(2010,1,1))+(E4:E20>30000))>0,0) перебирает все значения в массиве ((D4 :D2030000))>0 и возвращает первый серийный номер, где он получает TRUE .

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

  • Окончательно ИНДЕКС(C4:C20,СООТВЕТСТВИЕ(ИСТИНА,((D4:D20<ДАТА(2010,1,1))+(E4:E20>30000))>0,0),1) возвращает имя сотрудника из диапазона C4:C20 с серийным номером, возвращаемым функцией ПОИСКПОЗ .

Это искомый сотрудник, который соответствует хотя бы одному критерию.

Теперь, если вы это понимаете, можете ли вы сказать мне формулу, чтобы найти сотрудника с идентификатором менее 300 или датой приема на работу менее 1 января 2012 г. или зарплатой более 30 000 долларов США?

Да. Ты прав. Формула будет:

=ИНДЕКС(C4:C20,СООТВЕТСТВИЕ(ИСТИНА,((B4:B20<200)+(D4:D20<ДАТА(2012,1,1))+(E4:E20>30000))>0,0), 1)

Подробнее: Как найти текст в Excel (7 подходящих методов)

2. Использование функции XLOOKUP

Вы можете выполнить ту же задачу, используя XLOOKUP функцию Excel.

Посетите эту ссылку, чтобы узнать о функции XLOOKUP , прежде чем продолжить, если хотите.

( XLOOKUP доступен только в Office 365 )

Формула для определения сотрудника с датой приема на работу до 1 января 2010 г. или зарплатой выше 30 000 долларов США будет выглядеть следующим образом:

=XLOOKUP(ИСТИНА,((D4:D20<ДАТА(2010,1,1))+(E4:E20>30000))>0,C4:C20)

Смотрите, у нас тот же сотрудник, что и раньше, Джек Симпсон.

Но, как и в случае с формулой ИНДЕКС-СООТВЕТСТВИЕ , больше сотрудников удовлетворяют заданным критериям. У нас есть только первый.

Объяснение формулы

  • ((D4:D20<ДАТА(2010,1,1))+(E4:E20>30000))>0 возвращает ИСТИНА , когда выполняется хотя бы один из двух критериев, иначе ЛОЖЬ . См. раздел выше.
  • XLOOKUP(TRUE,((D4:D2030000))>0,C4:C20) затем возвращает имя сотрудника из столбца C4: C20 , где он получает первый TRUE .

Подробнее: Как найти значение на другом листе в Excel (3 простых метода)

3.

Использование функции ФИЛЬТР

Наконец, мы выполним ту же задачу, используя ФИЛЬТР функцию Excel.

Щелкните здесь, чтобы ознакомиться с функцией ФИЛЬТР , прежде чем продолжить, если хотите.

( ФИЛЬТР доступен только в Office 365 )

На этот раз мы получим всех сотрудников, присоединившихся до 1 января 2010 года или получивших зарплату выше 30 000 долларов.

Формула будет:

=ФИЛЬТР(C4:C20,((D4:D20<ДАТА(2010,1,1))+(E4:E20>30000))>0)

Видите ли, на этот раз у нас есть все сотрудники, которые соответствуют нашим заданным критериям, датой приема на работу до 1 января 2010 года или зарплатой более 30 000 долларов США.

Объяснение формулы

  • ((D4:D20<ДАТА(2010,1,1))+(E4:E20>30000))>0 возвращает ИСТИНА , когда выполняется хотя бы один из двух критериев, иначе ЛОЖЬ . См. раздел INDEX-MATCH .
  • ФИЛЬТР(C4:C20,((D4:D2030000))>0) просматривает все ячейки в диапазоне C4:C20 , но возвращает только те, которые встречаются с TRUE .
  • Таким образом, он возвращает всех сотрудников, которые соответствуют хотя бы одному из заданных критериев.

Подробнее: Как найти таблицу в Excel (8 методов)

Заключение

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

Дополнительная литература

  • Как использовать функцию АДРЕС в Excel (7 примеров)
  • Excel ПРОСМОТР и ВПР: с 3 примерами
  • Расширенные функции поиска в Excel (9 примеров)

Как использовать ПОИСКПОЗ ИНДЕКС с несколькими критериями в Excel

Перейти к содержимому Как использовать ПОИСКПОЗ ИНДЕКС с несколькими критериями в Excel

ПОИСКПОЗ ИНДЕКС с несколькими критериями позволяет выполнять успешный поиск при совпадении нескольких значений поиска.

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

Это невозможно с любой другой формулой поиска без вставки вспомогательных столбцов😲

Выполните эти 3 простых шага, чтобы за несколько минут создать собственное ПОИСКПОЗ ИНДЕКС с несколькими критериями.

Если вы хотите присоединиться к нам, загрузите образец файла Excel здесь.

СОДЕРЖАНИЕ