Найти значение ближайшего соответствия по нескольким критериям
В некоторых случаях вам может потребоваться найти ближайшее или приблизительное значение соответствия на основе более чем одного критерия. С комбинацией ИНДЕКС, МАТЧ и IF функции, вы можете быстро выполнить это в Excel.
Как найти значение ближайшего соответствия по нескольким критериям?
Как показано на скриншоте ниже, вам нужно найти подходящего человека для работы на основе двух критериев: основной Компьютер»И« опыт работы 15 лет ».
Внимание: Для того, чтобы это работало правильно, если там есть повторяющиеся специальности, опыт работы этих дублированных специальностей должен быть отсортирован в порядке возрастания.
1. Выберите пустую ячейку для вывода результата, затем скопируйте в нее формулу ниже и нажмите Ctrl + Shift + Enter ключи, чтобы получить результат.
=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))
Ноты: в этой формуле:
- D3: D8 содержит ли диапазон столбцов результат, который вы ищете;
- G5 содержит второй критерий (номер опыта 15), на основе которого вы будете искать значение;
- G4 содержит первый критерий (компьютер), по которому вы будете искать значение;
- B3: B8 - диапазон ячеек, соответствующих первому критерию;
- C3: C8 - диапазон ячеек, соответствующих второму критерию;
- Номер регистрации 1 является приблизительным поиском, что означает, что если точное значение не может быть найдено, будет найдено наибольшее значение, которое меньше, чем значение поиска;
- Эта формула должна быть введена как формула массива с Ctrl + Shift + Enter ключи.
Как работает эта формула
Эту формулу можно разбить на несколько компонентов:
- IF(B3:B8=G4,C3:C8): функция ЕСЛИ здесь возвращает результат как {9;13;FALSE;FALSE;FALSE;FALSE}, который получен в результате тестирования значений в B3: B8, чтобы убедиться, что они соответствуют значению в G4. Если есть совпадение, возвращает соответствующее значение, в противном случае возвращает FALSE. Здесь находит два совпадения и четыре несовпадения.
- Формула массива =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): функция ПОИСКПОЗ находит позицию числа 15 (значение в G5) в диапазоне C3: C8. Поскольку число 15 не может быть найдено, оно соответствует следующему наименьшему значению 13. Итак, результат здесь 2.
- И, =INDEX(D3:D8,2): Функция ИНДЕКС возвращает значение второй ячейки в диапазоне D3: D8. Итак, конечный результат - Эми.
Связанные функции
Функция ЕСЛИ в Excel
Функция ЕСЛИ - одна из самых простых и полезных функций в книге Excel. Он выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.
Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ в Microsoft Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения.
Функция ИНДЕКС в Excel
Функция ИНДЕКС возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Статьи по теме
Средние ячейки на основе нескольких критериев
В Excel большинство из нас могут быть знакомы с функциями СЧЁТЕСЛИ и СУММЕСЛИ, они могут помочь нам подсчитать или суммировать значения на основе критериев. Но пробовали ли вы когда-нибудь вычислить среднее значение на основе одного или нескольких критериев в Excel? В этом руководстве подробно представлены примеры и формулы, которые помогут легко это сделать.
Нажмите, чтобы узнать больше ...
Подсчет ячеек, если выполнено одно из нескольких критериев
В этом руководстве рассказывается о способах подсчета ячеек, если они содержат X, Y или Z… и т. Д. В Excel.
Нажмите, чтобы узнать больше ...
Подсчет уникальных значений на основе нескольких критериев
В этой статье приводятся несколько примеров для подсчета уникальных значений на основе одного или нескольких критериев на листе с подробными пошаговыми методами.
Нажмите, чтобы узнать больше ...
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
