Наибольший по условию в MS EXCEL

Найти, например, второе наибольшее значение в списке можно с помощью функции НАИБОЛЬШИЙ(). В статье приведено решение задачи, когда наибольшее значение нужно найти не среди всех значений списка, а только среди тех, которые удовлетворяют определенным критериям.

Пусть имеется таблица с двумя столбцами: текстовым и числовым (см. файл примера).

Для удобства создадим два именованных диапазона: Текст (A3:A27) и Числа (B3:B27).

СОВЕТ:
Создание формул для определения минимального и максимального значения с учетом условий рассмотрено в статье Максимальный и Минимальный по условию в MS EXCEL.

Определение наибольшего значения с единственным критерием

Найдем с помощью формулы массива второе наибольшее значение среди тех чисел, которые соответствуют значению Текст2 (находится в ячейке Е6) :

=НАИБОЛЬШИЙ(ЕСЛИ(Текст=E6;Числа);2)

или так

=НАИБОЛЬШИЙ(ЕСЛИ(Текст="Текст2";Числа);2)

После набора формулы не забудьте вместо ENTER нажать CTRL+SHIFT+ENTER.

Чтобы разобраться в работе формулы, выделите в Строке формул выражение ЕСЛИ(Текст=E6;Числа) и нажмите клавишу F9. Выделенная часть формулы будет заменена на результат, т.е. на массив значений:

{ЛОЖЬ:-95:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-66:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-20:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ: 0:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:4:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:9:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}

Значение ЛОЖЬ соответствует строкам, в которых в столбце Текст нет значения Текст2. В противном случае выводится само число. Т.к. функция НАИБОЛЬШИЙ() игнорирует текстовые значения и значения ЛОЖЬ и ИСТИНА, то 2-е наибольшее будет искаться только среди чисел -95; -66; -20; 0; 4; 9. Результат: 4.

СОВЕТ:
Задачу можно решить без использования формулы массива. Для этого потребуется создать дополнительный столбец, в котором будут выведены только те значения, которые удовлетворяют критерию. Затем, среди отобранных значений с помощью функций НАИБОЛЬШИЙ(), определить нужное значение.

Определение наименьшего значения с несколькими критериями

Теперь найдем 3-е наименьшее значение среди тех чисел, которые соответствуют сразу 2-м критериям.

Пусть имеется таблица с тремя столбцами: Название фрукта, Поставщик и Количество.

Таблицу критериев разместим правее таблицы с данными.

Найдем 3-е наименьшее значение среди чисел, находящихся в строках, для которых Название фрукта =Яблоко, а Поставщик =ООО Рога с помощью формулы массива:

=НАИМЕНЬШИЙ(ЕСЛИ(($A$32:$A$42=E32)*($B$32:$B$42=F32);$C$32:$C$42);3)

или так

=НАИМЕНЬШИЙ(ЕСЛИ(($A$32:$A$42="Яблоко")*($B$32:$B$42="ООО Рога");$C$32:$C$42);3)

После набора формулы не забудьте вместо ENTER нажать CTRL+SHIFT+ENTER.

Если ни одна запись в таблице не удовлетворяет одновременно двум критериям (Условие И), то формула возвращает значение ошибки #ЧИСЛО!

СОВЕТ:
Создание формул с множественными критериями подробно рассмотрено в разделах Сложения и Подсчета значений.

 

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)

Комментарии

Костя (не проверено)

а если нужно с условием по двум столбцам? например "текст" и "подтекст", какая тогда будет формула?

Creator

Используйте формулу в виде =НАИМЕНЬШИЙ(ЕСЛИ(($A$32:$A$42=E32)*($B$32:$B$42=F32);$C$32:$C$42);3) Ваш комментарий учтен в статье в разделе Определение наименьшего значения с несколькими критериями