Функция МАКС в Excel: как найти максимальное значение
В этой статье объясняется функция MAX с множеством примеров формул, показывающих, как найти наибольшее значение в Excel и выделить наибольшее число на листе.
МАКС — одна из самых простых и простых в использовании функций Excel. Тем не менее, в нем есть некоторые настройки, которые предоставят вам отличные возможности. Подскажите, а как использовать функцию MAX с условиями? Или вы знаете, как извлечь максимальную ценность? Мы предложим различные решения этих и других подобных задач.
Функция MAX в Excel возвращает максимальное значение в указанном наборе данных.
Его синтаксис следующий:
МАКС (число1; [число2];…)
Где число может быть числовым значением, массивом, именованным диапазоном, ссылкой на ячейку или диапазоном, содержащим числа.
Число1 является обязательным, число2 и последующие аргументы необязательны.
Чтобы создать простейшую формулу для вычисления максимального значения, вы можете вводить числа непосредственно в список аргументов, например:
= МАКСИМУМ (1; 2; 3)
На практике это очень редкий случай. По большей части вы будете иметь дело с диапазонами и ячейками.
Самый быстрый способ найти наибольшее значение в диапазоне:
Например, чтобы найти наибольшее значение в диапазоне A1: A6, формула будет выглядеть так:
= МАКСИМУМ (A1: A6)
Если ваши числа находятся в непрерывном диапазоне (как в этом примере), вы можете заставить Excel автоматически сгенерировать формулу MAX. Вот как:
5 вещей, которые нужно знать о функции MAX –
Ниже мы покажем некоторые стандартные варианты использования функции MAX.
Во многих случаях для одной и той же задачи существуют разные решения, поэтому я рекомендую вам протестировать все варианты, чтобы найти наиболее подходящий.
Чтобы извлечь наибольшее число в группе чисел, передайте эту группу функции MAX в качестве ссылки на диапазон. Диапазон может содержать любое количество строк и столбцов. Например, чтобы получить максимальное значение из C2: E7, используйте эту простую формулу:
= МАКСИМАЛЬНЫЙ (C2: MI7)
Примечание. Если у вас есть пустые ячейки в области поиска, это не имеет значения! Функция MAX просто проигнорирует их и будет работать с доступными данными.
Предположим, у нас есть информация об учениках с их оценками, но некоторые из них логические, текстовые, а другие вообще не имеют оценок.
Применяем функцию:
= МАКСИМУМ (B2: B10)
Как видите, ячейки с текстом и пустые ячейки игнорируются, логические значения устанавливаются на ноль, а затем вычисляется максимальная оценка, как показано в таблице выше.
Для поиска несмежных ячеек и диапазонов необходимо включить в формулу ссылку на каждый отдельный адрес. Рассмотрим пошагово:
Вы получите такое выражение:
= МАКСИМУМ (C2: E2; C7: E7)
Во внутренней системе Excel даты — это не что иное, как порядковые числа дней, поэтому функция MAX обрабатывает их без каких-либо проблем.
Например, чтобы найти последнюю дату доставки из C2: C7, напишите обычное выражение, которое вы использовали бы для чисел:
= МАКСИМАЛЬНЫЙ (C2: C7)
Поскольку дата также является числом, она отлично работает.
Часто бывает, что из большого количества данных необходимо выбрать максимальное значение из его части. Например, найти самую большую партию определенного продукта в огромном списке продаж. В качестве альтернативы можно найти наивысшую оценку по предмету в большом наборе данных для учащихся данного класса по нескольким предметам в школе.
Если вы хотите получить максимальное значение с учетом некоторых условий, вы можете выбрать одну из нескольких предложенных ниже формул. Чтобы убедиться, что все они возвращают одинаковый результат, мы протестируем их на одном наборе данных.
Проблема: мы хотим найти самую большую продажу определенного продукта, указанного в F1.
Если вы ищете решение, которое работает во всех версиях Excel, используйте функцию ЕСЛИ для проверки условия, а затем передайте полученный массив функции:
= МАКС (ЕСЛИ (B2: B15 = F1; C2: C15))
Чтобы это сработало, вам нужно одновременно нажать Ctrl + Shift + Enter, чтобы вставить формулу массива. Если все сделано правильно, Excel заключит выражение в {фигурные скобки}, что является визуальным указанием формулы массива.
также возможно оценить несколько условий одновременно, что мы обсудим позже.
Если вам не нравится использовать формулы массива на листах, объедините MAX с функцией SUMPRODUCT, которая работает с массивами:
= СУММПРОИЗВ (МАКС ((B2: B15 = F1) * (C2: C15)))
В Excel 2019 и Office 365 есть специальная функция MAXIF, которая выполняет поиск наивысшего значения на основе 126 критериев.
В нашем случае используется только одно условие, поэтому формула очень проста:
= МАКСЕСЛИ (C2: C15; B2: B15; F1)
На скриншоте ниже показаны все 3 формулы в действии:
Фактически, это разновидность условного MAX, рассмотренного в предыдущем примере. Чтобы исключить нули, используйте логический оператор not equal и вставьте выражение « 0» в критерий функции MAXIF или в предложение MAX + IF.
Как вы понимаете, это условие имеет смысл проверять только при наличии отрицательных чисел. Для положительных чисел эта проверка избыточна, потому что любое положительное число больше нуля.
Чтобы доказать это, мы находим самую маленькую скидку в линейке C2: C7. Поскольку все скидки отрицательные, самая низкая скидка фактически самая высокая.
Убедитесь, что вы нажали Ctrl + Shift + Enter, чтобы правильно заполнить эту формулу массива:
= МАКС (ЕСЛИ (DO2: DO7 0; DO2: DO7))
А это обычная формула –
= МАКСИМАЛЬНОЕ ЕСЛИ (C2: C7; C2: C7; « 0″)
И вот что у нас получилось:
Когда вы работаете с большим объемом данных, управляемых разными формулами, есть вероятность, что некоторые из них приведут к ошибкам. В результате функция MAX также вернет ошибку.
В качестве обходного пути можно использовать MAX вместе с ISERROR. Поскольку вы просматриваете диапазон A1: B5, формула принимает следующий вид:
= МАКС (ЕСЛИ (ЕСТЬ ОШИБКА (A1: B6); «»; A1: B6))
Чтобы упростить формулу, используйте функцию ЕСЛИОШИБКА вместо комбинации ЕСЛИ + ЕСТЬ ОШИБКА. Это также сделает логику более очевидной: если есть ошибка в A1: B6, замените ее пустой строкой (»), затем вычислите наибольшее значение в диапазоне:
= МАКСИМАЛЬНЫЙ (ЕСЛИ ОШИБКА (A1: B6; «»))
Обратной стороной является то, что вы должны не забывать нажимать Ctrl + Shift + Enter, потому что это работает только как формула массива.
В Excel 2019 и Office 365 функция MAXIF может быть хорошим решением, если набор данных содержит хотя бы одно положительное число или ноль:
= MAXIF (LA1: B6; A1: B6; «> = 0»)
Поскольку формула ищет максимальное значение с условием «больше или равно 0», она не будет работать для набора данных, содержащего только отрицательные числа.
Также идеально подходит функция AGGREGATE, которая может выполнять серию операций и игнорировать значения ошибок:
= АГРЕГАТ (4; 6; A1: B6)
Число 4 в первом аргументе говорит вам искать максимальное число, шесть во втором аргументе — это опция «игнорировать ошибки», а A1: B6 — ваш целевой диапазон.
Все формулы возвращают одинаковый результат:
При работе с диапазоном положительных и отрицательных чисел иногда может потребоваться найти наибольшее абсолютное значение независимо от знака.
Первая идея, которая приходит в голову, — получить абсолютные значения всех чисел в диапазоне с помощью функции ABS и передать их в MAX:
{= МАКС (АБС (диапазон))}
Это формула массива, поэтому не забудьте проверить ее с помощью Ctrl + Shift + Enter. Еще одно предостережение: это работает только с числами и не работает с нечисловыми данными.
Не нравится это решение? Тогда давайте построим что-нибудь посложнее
