Поиск по нескольким критериям с помощью INDEX и MATCH
При работе с большой базой данных в электронной таблице Excel с несколькими столбцами и заголовками строк всегда сложно найти что-то, что соответствует нескольким критериям. В этом случае вы можете использовать формулу массива с the ИНДЕКС и МАТЧ fсоборов.
Как выполнить поиск по нескольким критериям?
Чтобы узнать ПРОДУКТЫ то есть белый и средний-размерный по цене $18 как показано на рисунке выше, вы можете воспользоваться преимуществом логической логики для создания массива из единиц и нулей, чтобы показать строки, соответствующие критериям. Затем функция ПОИСКПОЗ найдет позицию первой строки, которая соответствует всем критериям. После этого INDEX найдет соответствующий идентификатор продукта в той же строке.
Общий синтаксис
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, из которого вы хотите, чтобы комбинационная формула возвращала идентификатор продукта. Здесь имеется в виду диапазон идентификаторов продуктов.
- критерий_значения: Критерии, используемые для определения позиции идентификатора продукта. Здесь относятся к значениям в ячейках H4, H5 и H6.
- диапазон_критериев: Соответствующие диапазоны, где критерии_значения перечислены. Здесь имеется в виду цвет, размер и ценовой диапазон.
- тип_соответствия 0: Заставляет MATCH найти первое значение, которое точно равно искомое_значение.
Чтобы найти продукт, который белый и средний-размерный по цене $18, скопируйте или введите формулу ниже в ячейку H8 и нажмите Ctrl + Shift + Enter чтобы получить результат:
= ИНДЕКС (B5: B10, ПОИСКПОЗ (1, («Белый»=C5: C10) * ("Середина"=D5: D10) * (18=E5: E10), 0))
Или используйте ссылку на ячейку, чтобы сделать формулу динамической:
= ИНДЕКС (B5: B10, ПОИСКПОЗ (1, (H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10), 0))
Пояснение формулы
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Формула сравнивает цвет в ячейке H4 против всех цветов в ассортименте C5: C10; сравнивает размер в H5 против всех размеров в D5: D10; сравнивает цену в H6 против всех цен в E5: E10. Первоначальный результат такой:
{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ} * {ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ} * {ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ}.
Умножение преобразует ИСТИНА и ЛОЖЬ в 1 и 0:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
После умножения у нас будет такой единственный массив:
{0;0;0;0;1;0}. - ПОИСКПОЗ (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = ПОИСКПОЗ (1,{0;0;0;0;1;0}, 0): Match_type 0 просит функцию ПОИСКПОЗ найти точное совпадение. Затем функция вернет позицию 1 в массиве {0;0;0;0;1;0}, Которая является 5.
- ПОКАЗАТЕЛЬ(B5: B10,ПОИСКПОЗ (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = ИНДЕКС (B5: B10,5): Функция ИНДЕКС возвращает 5значение в диапазоне идентификаторов товаров B5: B10, Которая является 30005.
Связанные функции
Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
Связанные формулы
Найти значение ближайшего соответствия по нескольким критериям
В некоторых случаях может потребоваться поиск ближайшего или приблизительного значения соответствия на основе нескольких критериев. С помощью комбинации функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы можете быстро сделать это в Excel.
Примерное совпадение с ИНДЕКСОМ и ПОИСКПОЗ
Бывают случаи, когда нам нужно найти приблизительные совпадения в Excel, чтобы оценить производительность сотрудников, выставить оценки учащихся, рассчитать почтовые расходы на основе веса и т. Д. В этом руководстве мы поговорим о том, как использовать функции ИНДЕКС и ПОИСКПОЗ для получения результаты нам нужны.
Искать значения из другого листа или книги
Если вы знаете, как использовать функцию ВПР для поиска значений на листе, значения vlookup из другого листа или книги не будут для вас проблемой. В этом учебном пособии вы узнаете, как получить значения vlookup с другого листа в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
