Как найти значение с двумя или несколькими критериями в Excel?
Обычно вы можете легко узнать значение с помощью Найдите функция в Excel. Но что, если найти значение по критериям? А с двумя или более критериями? Эта статья представит вам несколько решений.
- Найти значение с двумя или несколькими критериями с помощью формулы массива
- Найдите значение по двум или нескольким критериям с помощью расширенного фильтра
Найти значение с двумя или несколькими критериями с помощью формулы массива
Предположим, у вас есть таблица продаж фруктов, как показано на скриншоте ниже, вам нужно узнать сумму, основанную на нескольких критериях. Здесь этот метод представит некоторые формулы массива, чтобы легко найти значения на основе этих заданных критериев.
Формула массива 1: найти значение с двумя или несколькими критериями в Excel
Основное выражение этой формулы массива отображается следующим образом:
{= ИНДЕКС (массив, ПОИСКПОЗ (1, (критерий 1 = поиск_массив 1) * (критерий 2 = поиск_массив 2)… * (критерий n = поиск_массив n), 0))}
Допустим, вы хотите узнать объем продаж манго происходит на 9/3/2019, вы можете ввести приведенную ниже формулу массива в пустую ячейку и затем нажать Ctrl + Shift + Enter вместе.
=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0))
Внимание: В приведенной выше формуле F3: F22 - это столбец суммы, в котором вы найдете значение, B3: B22 - столбец даты, C3: C22 - столбец фруктов, J3 - дата, указанная в качестве первого критерия, J4 - это имя фрукта, указанное как второй критерий.
В соответствии с выражением формулы массива вы можете легко добавить необходимые критерии. Например, теперь вы ищете сумму продаж манго происходит на 9/3/2019, а вес манго 211, вы можете добавить критерии и lookup_array в разделе MATCH следующим образом:
=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22)*(J5 = E3: E22), 0))
И нажмите Ctrl + Shift + Enter ключи, чтобы узнать сумму продажи.
Формула массива 2: найти значение с двумя или несколькими критериями в Excel
Основное выражение этой формулы массива отображается следующим образом:
= ИНДЕКС (массив; ПОИСКПОЗ (критерий1 и критерий2… & критерийN, поиск_массив1 и поиск_массив2… & поиск_массивN, 0), 0)
Например, вы хотите узнать объем продаж фрукта, вес которого 242 и происходит на 9/1/2019, вы можете ввести формулу ниже в пустую ячейку и нажать Ctrl + Shift + Enter ключи вместе.
=INDEX(F3:F22,MATCH(J3&J4,B3:B22&C3:C22,0),0)
Внимание: В приведенной выше формуле F3: F22 - это столбец суммы, в котором вы найдете значение, B3: B22 - столбец даты, E3: E22 - столбец веса, J3 - дата, указанная в качестве первого критерия, J5 - значение веса, указанное как второй критерий.
Если вы хотите найти значение на основе трех или более критериев, вы можете легко добавить свои критерии и lookup_array в раздел MATCH. Обратите внимание, что критерии и lookup_array должны быть в одном порядке.
Например, вы хотите узнать объем продаж груши с весом 242, который произошел 9, вы можете добавить критерии и lookup_array следующим образом:
= ИНДЕКС (F3: F22; ПОИСКПОЗ (J3 &J4& J5, B3: B22 &C3: C22& E3: E22,0), 0)
И нажмите Ctrl + Shift + Enter ключи для определения суммы продаж.
Найдите значение по двум или нескольким критериям с помощью расширенного фильтра
Помимо формул, вы также можете применять Расширенный фильтр возможность найти все значения с двумя или несколькими критериями в Excel. Пожалуйста, сделайте следующее:
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 60-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Нажмите Данные > Фильтр для включения функции расширенного фильтра.
2. В диалоговом окне Advanced Filter сделайте следующее:
(1) Проверьте Скопировать в другое место вариант в Действие раздел;
(2) В Диапазон списка поле, выберите диапазон, в котором вы найдете значения (A1: E21 в моем случае);
(3) В Диапазон критериев поле, выберите диапазон, в котором вы найдете значения (H1: J2 в моем случае);
(4) В Скопировать в выберите первую ячейку целевого диапазона, в который вы поместите отфильтрованные строки (H9 в моем случае).
3, Нажмите OK .
Теперь отфильтрованные строки, соответствующие всем перечисленным критериям, копируются и помещаются в целевой диапазон. Смотрите скриншот:
Статьи по теме:
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!