Определение максимального значения. Максимальный и Минимальный по условию в MS EXCEL. Функции для работы с матрицами

Нахождение максимального/ минимального значения - простая задача, но она несколько усложняется, если МАКС/ МИН нужно найти не среди всех значений диапазона, а только среди тех, которые удовлетворяют определенному условию.

Пусть имеется таблица с двумя столбцами: текстовым и числовым.

Для удобства понимания формул создадим два для каждого из столбцов: Текст (A 6: A 30 ) и Числа (B6:B30 ). (см. файл примера ).

Рассмотрим несколько задач:

А. Найдем максимальное значение среди тех чисел, которые соответствуют значению Текст1 (критерий введем в ячейку E6 ).
Т.е. будем искать максимальное значение не среди всех значений столбца Числовые значения , а только среди тех, у которых в той же строке в столбце А текстовое значение равно Текст1 . Напишем (не забудьте при вводе формулы нажать CTRL+SHIFT+ENTER ):
=НАИБОЛЬШИЙ(ЕСЛИ(A6:A30=E6;B6:B30;"");1)

или с Именованными диапазонами :

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

Часть формулы Текст=E6 , вернет {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ: ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} (для просмотра результата выделите эту часть формулы в и нажмите клавишу ). ИСТИНА соответствует строкам, у которых в столбце Текстовые значения содержится значение Текст1 .

Часть формулы ЕСЛИ(Текст=E6;Числа;"") , вернет {10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"": "":"":4:"":"":"":8:"":"":"":""}, где вместо ИСТИНА подставлено значение из числового столбца, а вместо ЛОЖЬ - значение . Вместо "" можно было бы использовать любой текстовый символ (букву) или вообще опустить (в этом случае массив будет выглядеть так {10:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-66: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-37:ЛОЖЬ: ЛОЖЬ: ЛОЖЬ:-5:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:4: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:8: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}).

Функция НАИБОЛЬШИЙ() со вторым параметром =1 использована вместо функции МАКС() , т.к. в случае, если критерию не удовлетворяет ни одна строка, то формула =МАКС({"":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"":"":"": "":"":"":"":"":"":""}) вернет 0!, что может ввести в заблуждение. Функция НАИБОЛЬШИЙ() в этом случае вернет ошибку #ЧИСЛО!

Б. Найдем максимальное значение только среди чисел принадлежащих определенному интервалу значений, например от 5 до 50. Границы можно ввести в ячейки I 14 и J14 . Решением является :
=НАИБОЛЬШИЙ(ЕСЛИ((Числа>=I14)*(Числа<=J14);Числа);1)

В. Найдем с помощью формулы массива минимальное значение среди тех, которые соответствуют значению Текст3 :
=МИН(ЕСЛИ((Текст=E7);Числа;"");1)

Т.е. если в столбце А значение = Текст3 , то учитывается значение в столбце B , если значение <> Текст3 , то учитывается максимальное значение+1, т.е. заведомо НЕ минимальное. Далее функция МИН() возвращает минимальное значение из полученного массива, причем понятно, что ни одно из значений, где <> Текст3, не исказит результат (см. задачу А).

Другое решение с помощью формулы ДМИН() , которая не является формулой массива .
=ДМИН(A5:B30;B5;I8:I9)

Г. Найдем минимальное значение, среди тех, которые больше :
=ДМИН(A5:B30;B5;I10:I11)
где в диапазоне I10:I11 содержится критерий =B6>СРЗНАЧ(Числа)

Д. Найдем максимальное значение по модулю. Из рисунка выше видно, что это -99. Для этого используйте формулу массива :

ЕСЛИ(МАКС(ABS(Числа))=МАКС(Числа);МАКС(Числа);-МАКС(ABS(Числа)))

Е. Найдем минимальное положительное число:

=НАИМЕНЬШИЙ(Числа;СЧЁТЕСЛИ(Числа;"<=0")+1) - обычная формула!

=НАИМЕНЬШИЙ(ЕСЛИ(Числа>0;Числа);1) - формула массива .

СОВЕТ:

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

Множественные условия

Вышерассмотренный подход можно распространить на случаи когда необходимо найти максимальный или минимальный для нескольких текстовых условий.

В этом случае придется записать более сложную формулу массива :

=НАИМЕНЬШИЙ(ЕСЛИ(($A$6:$A$16=E6)*($B$6:$B$16=F6);$C$6:$C$16;"");1)

В файле примера для наглядности настроено . Кроме того, для выбора критериев использован (см. желтые ячейки).

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

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

Для решения этой задачи можно использовать как дополнительный столбец с промежуточной формулой, так и одну формулу массива

1. Решение с дополнительным столбцом
В В1 напишите формулу

ЕСЛИ(A1<>0;A1;"")

В С1 напишите формулу

Она и вернёт искомый результат - число 3

ЕСЛИ(A1<>0;A1;"")

ЕСЛИ(A1>0;A1;"")

МИН(ЕСЛИ(A1:A5<>0;A1:A5))

Как это работает: Вот эта часть формулы A1:A5<>0 формирует массив {ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА} , после чего применяя функцию ЕСЛИ() мы преобразуем массив в следующий: {5:7:ЛОЖЬ:ЛОЖЬ:3} . Так как функция МИН() игнорирует текстовые и логические значения, то в итоге получаем искомое - число 3

Если в диапазоне есть отрицательные числа, то вместо формулы

МИН(ЕСЛИ(A1:A5<>0;A1:A5))

МИН(ЕСЛИ(A1:A5>0;A1:A5))

НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;0)+1)

Как это работает: С помощью функции СЧЁТЕСЛИ мы подсчитываем количество нулей в диапазоне и прибавив к этому количеству единицу мы возвращаем первое наименьшее число более нуля с помощью функции НАИМЕНЬШИЙ

Если в диапазоне есть отрицательные числа, то вместо формулы

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

(аналогична стандартной функции Excel СУММЕСЛИ).

В Excel версии 2016 и выше появилась встроенная функция МИНЕСЛИ , можете пользоваться ей. Если ваш Excel более ранней версии, то эту функцию можно использовать установив надстройку VBA-Excel .

У функции следующие аргументы =МИНЕСЛИ(ДИАПАЗОН;КРИТЕРИЙ;[ДИАПАЗОН_ПОИСКА ])

ДИАПАЗОН - Диапазон проверяемых ячеек.

• КРИТЕРИЙ - Условие в формате числа, выражения или текста, определяющее проверку минимального значения.
• [ ДИАПАЗОН_ПОИСКА ] - Фактический диапазон для определения минимального значения. Если данный параметр не задан, будет использоваться ячейки, задаваемые параметром ДИАПАЗОН .

Пример 1

В качестве критерия можно указывать значения и логические выражения:

1. Рассмотрим следующий пример в котором определяется минимальная оценка по литературе. Для этого в параметр КРИТЕРИЙ указано значение «Литература», а в параметр ДИАПАЗОН - список предметов.
2. Если в качестве критерия указать логическое выражение «<>Русский» , то определится минимальная оценка по всем предметам за исключением русского языка.

Пример 2

В следующем примере параметр ДИАПАЗОН_ПОИСКА не задан, поэтому минимальное значение определяется среди ячеек указанных в параметре ДИАПАЗОН .

В процессе подготовки разного рода ранжирования или анализа показателей успешности, очень удобно если выделить цветом наилучший или наихудший результат. Как выделить максимальное значение в Excel цветом? Здесь существенно поможет условное форматирование. Преимуществом такого подхода является сохранение работоспособности автоматического выделения цветом лучших/худших показателей даже при добавлении или удалении данных из таблицы.

Как выделить цветом максимальное значение в Excel

Для примера возьмем таблицу расходов:

Чтобы молниеносно выделить наибольшие и наименьшие расходы делаем следующее:

В результате мы выделили цветом ячейку с максимальным числовым значением.

Как выбрать минимальное значение в Excel

Чтобы выделить цветом минимальное значение в Excel, повторите весь порядок действий, описанных выше. Только вместо функции МАКС, должна быть функция МИН. Данная функция позволяет выбрать наименьшее значение в таблице Excel. А вместо зеленого цвета заливки выберите красный цвет.

К одному и тому же диапазону должно быть применено 2 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»

Финальный эффект применения двух правил условного форматирования для выделения цветом наибольшего и наименьшего значения столбца B (Расходы):

Принцип действий обоих правил одинаковый. Они отличаются только функциями =МАКС() и =МИН(). Более того аргументы функций идентичны. Первая функция =МАКС() ищет в несменяемом диапазоне с абсолютными ссылками $B$2:$B$10 максимальное значение. После функции стоит оператор сравнения с изменяемой текущей ячейкой которая имеет относительную ссылку начиная от B2. Правило условного форматирования читает данную формулу так: если число больше всех, то выделить цветом. Как только совпадает максимальное число (которое возвращает функция МАКС) со значением текущей ячейки, формула возвращает логическое значение ИСТИНА и сразу же применяется формат со соответствующим цветом заливки. Так же срабатывает правило для умного форматирования ячеек столбца и с функцией МИН.

Выделение цветом трех наименьших значений подряд

В разных ситуациях полезно применяется автоматическое выделение цветом трех ячеек с наименьшими значениями. Условное форматирование со соответственными формулами прекрасно справляется с данной задачей. Таблица с данными для примера:

Усложним немного задачу. Ячейка с наименьшим значением должна автоматически получить красный цвет заливки. Следующая ячейка с вторым наименьшим значением получит цвет заливки оранжевый. А третье наименьшее значение – желтый.

Чтобы добиться данного эффекта следует выполнить следующие действия:

Три наименьшие значения автоматически выделились разными цветами.

Внимание! Если просматриваемый диапазон содержит несколько одинаковых минимальных значений все они будут выделены цветом.

Аналогичным способом можно выделять цветом наибольшие значения. Просто изменить в формуле функцию НАИМЕНЬШИЙ на НАИБОЛЬШИЙ. Например: =НАИБОЛЬШИЙ($B$2:$B$9;3)=B2

Функция наименьший и наибольший в Excel

Функция =НАИМЕНЬШИЙ() ищет в заданном (в первом аргументе) диапазоне очередное наименьшее значение. Очередь указана во втором аргументе функции. То есть если во втором аргументе мы указали число 3, значит функция возвращает третье по очереди наименьшее значение заданного диапазона $B$2:$B$9. Если результат вычисления функции равен текущей ячейке значит к ней присваивается соответственный формат (цвет заливки). Важно отметить что в формуле для просмотра диапазона мы используем абсолютные адреса ссылки, а после оператора сравнения =B2 – относительные, так как значение функции сравнивается с каждой ячейкой выделенного диапазона. А функция НАИБОЛЬШИЙ работает обратно пропорционально.

Полезный совет! Если нет необходимости разбивать 3 наименьших значения на разные цвета, тогда необязательно создавать 3 правила условного форматирования для одного и того же диапазона. Достаточно просто немного изменить формулу добавив всего один символ оператора: =НАИМЕНЬШИЙ($B$2:$B$9;3)>=B2. То есть – больше или равно.

Все описанные способы хороши тем, что при изменении значений в ячейках они все равно будут работать автоматически.

Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты - формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

Это особенно актуально в таблицах с большим объёмом данных . Если в столбце, например, указаны цены на продукцию торгового центра. И вам надо узнать, какой товар самый дешёвый. Если искать его «вручную», уйдёт очень много времени. Но в Экселе это можно сделать буквально за несколько кликов. Утилита также высчитывает среднее арифметическое. Ведь это две простые операции: сложение и деление.

Максимальное и минимальное

Вот как найти максимальное значение в Excel:

1. Поставьте курсор-ячейку в любое место.
2. Перейдите в меню «Формулы».
3. Нажмите «Вставить функцию».
4. В списке выберите «МАКС». Или напишите это слово в поле «Поиск» и нажмите «Найти».
5. В окне «Аргументы» введите адреса диапазона, максимальное значение которого вам нужно узнать. В Excel имена клеток состоят из буквы и цифры («B1», «F15», «W34»). А название диапазона - это первая и последняя ячейки, которые в него входят.
6. Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.
7. Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

Следующий шаг – укажите диапазон значений

Теперь будет легче разобраться, как найти минимальное значение в Excel. Алгоритм действий полностью идентичен. Просто вместо «МАКС» выберите «МИН».

Среднее

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

Вот как найти среднее значение в Excel:

1. Поставьте ячейку курсор в любое свободное место таблицы.
2. Перейдите на вкладку «Формулы».
3. Нажмите на «Вставить функцию».
4. Выберите «СРЗНАЧ».
5. Если этого пункта нет в списке, откройте его с помощью опции «Найти».
6. В области «Число1» введите адрес диапазона. Или напишите несколько цифр в разных полях «Число2», «Число3».
7. Нажмите «OK». В ячейке появится нужное значение.

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

Другие способы

Максимальное, минимальное и среднее можно узнать и другими способами.

1. Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
2. Поставьте курсор в любую ячейку.
3. Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
4. Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
5. Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
6. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

Найти и вычислить поможет Excel-инструмент «Автофункции».

1. Поставьте курсор в ячейку.
2. Найдите кнопку, название которой начинается на «Авто». Это зависит от выбранной в Excel опции по умолчанию («Автосумма», «Авточисло», «Автосмещ», «Автоиндекс»).
3. Нажмите на чёрную стрелочку под ней.
4. Выберите «МИН» (минимальное значение), «МАКС» (максимальное) или «СРЗНАЧ» (среднее).
5. В отмеченной клетке появится формула. Кликните на любую другую ячейку - она будет добавлена в функцию. «Растяните» рамку вокруг неё, чтобы охватить диапазон. Или щёлкайте по сетке с зажатой клавишей Ctrl, чтобы выделять по одному элементу.
6. Когда закончите, нажмите Enter. Результат отобразится в клетке.

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