Функция MAX в Excel: как найти максимальное значение
В этой статье объясняется функция MAX со множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число на листе.
MAX — одна из самых простых и удобных функций Excel. Тем не менее, у него есть несколько хитростей, которые дадут вам отличные возможности. Можете ли вы рассказать мне, как вы используете функцию MAX с условиями? Или вы знаете, как извлечь наибольшее абсолютное значение? Мы собираемся предложить несколько решений для этих и подобных проблем.
- Функция MAX — основная информация
- Найдите максимум в группе чисел
- Вычислить максимум в несмежных диапазонах
- Как получить максимальную дату (последнюю
- Максимальное значение с условиями
- Наибольшее число без нулей
- Как найти максимальное число игнорируя ошибки
- Определить абсолютное максимальное значение
- Максимум применяется к тексту
- Расчет наибольшей разницы
- Показать адрес ячейки с максимальным значением
- Как выделить наибольшее число
- Потому что не работает?
Функция МАКС
Функция MAX в Excel возвращает максимальное значение в указанном наборе данных.
Его синтаксис следующий:
МАКС(число1, [число2];…)
Где число может быть представлено числовым значением, массивом, именованным диапазоном, ссылкой на ячейку или диапазоном, содержащим числа.
Номер 1 обязателен, номер 2 и последующие аргументы необязательны.
Чтобы создать простейшую формулу для вычисления максимального значения, вы можете ввести числа непосредственно в список аргументов, например:
=МАКС(1, 2, 3)
На практике это очень редкий случай. По большей части вы будете работать с диапазонами и ячейками.
Самый быстрый способ найти наибольшее значение в диапазоне:
- В ячейке введите =MAX(
- Выберите диапазон чисел с помощью мыши.
- Введите закрывающую скобку.
- Нажмите Enter для завершения.
Например, чтобы найти наибольшее значение в диапазоне A1:A6, формула будет выглядеть так:
=МАКС(А1:А6)
Если ваши числа находятся в непрерывном диапазоне (как в этом примере), вы можете сделать так, чтобы Excel автоматически сгенерировал для вас формулу MAX. Вот как:
- Выделите ячейки с вашими номерами.
- На вкладке «Главная» в группе «Форматы» нажмите «Автосумма» и выберите «Максимум» в раскрывающемся списке.
5 фактов, которые вам нужно знать о функции MAX –
- В текущих версиях Excel вы можете обрабатывать до 255 аргументов.
- Если аргументы не содержат числа, возвращается ноль.
- Если аргументы содержат одно или несколько значений ошибки, возвращается ошибка.
- Пустые ячейки игнорируются.
- Обработка логических и текстовых представлений чисел, переданных непосредственно в списке аргументов (ИСТИНА = 1, ЛОЖЬ = 0). Если ссылка на ячейку используется с логическим или текстовым значением, они игнорируются.
Как найти в Excel максимальное число – примеры.
Далее мы покажем некоторые распространенные варианты использования функции MAX.
Во многих случаях для одной и той же задачи существует несколько разных решений, поэтому я рекомендую попробовать все варианты, чтобы выбрать наиболее подходящий.
Как найти максимальное значение в группе чисел.
Чтобы извлечь наибольшее число из группы чисел, передайте эту группу функции MAX в качестве ссылки на диапазон. Диапазон может содержать любое количество строк и столбцов. Например, чтобы получить максимальное значение C2:E7, используйте эту простую формулу:
=МАКС(С2:Е7)
Примечание. Если в области поиска есть пустые ячейки, не беда! Функция MAX просто проигнорирует их и будет работать с имеющимися данными.
Допустим, у нас есть информация об учениках с их оценками, но часть значений оценок — логические, текстовые, а у некоторых еще нет оценок.
Примените функцию:
= МАКС(В2:В10)
Как видите, ячейки с текстом игнорируются, а также пустые, логические значения обнуляются, а затем вычисляется максимальный балл, как показано в таблице выше.
Найдите наибольшее значение в несмежных ячейках или диапазонах
Чтобы найти несмежные ячейки и диапазоны, необходимо включить в формулу ссылку на каждый отдельный адрес. Рассмотрим пошагово:
- Начните вводить функцию MAX в ячейку.
- После того, как вы ввели открывающие скобки, зажмите клавишу Ctrl и выберите нужные ячейки и диапазоны мышью.
- Отпустите Ctrl и введите закрывающую скобку.
- Нажмите Ввод.
Вы получите такое выражение:
=МАКС(С2:Е2;С7:Е7)
Как получить максимальную (последнюю) дату в Excel
Во внутренней системе Excel даты представляют собой не что иное, как порядковые номера дней, поэтому функция MAX прекрасно их обрабатывает.
Например, чтобы найти последнюю дату выполнения C2:C7, введите регулярное выражение, которое вы использовали бы для чисел:
=МАКС(С2:С7)
Поскольку дата также является числом, все работает отлично.
Максимальное значение с условиями.
Очень часто бывает, что из большого количества данных нужно выбрать максимальное значение какой-то его части. Например, чтобы найти самую большую партию определенного товара в огромном списке продаж. В качестве альтернативы можно найти наивысшую оценку по предмету среди большого набора данных оценок, полученных учащимися определенного класса по нескольким предметам в школе.
Если вы хотите получить максимальное значение при соблюдении некоторых условий, вы можете выбрать одну из нескольких формул ниже. Чтобы убедиться, что все они возвращают один и тот же результат, мы проверим их на одном и том же наборе данных.
Задача: Мы хотим найти самую большую продажу для определенного продукта, указанного в F1.
МАКС + ЕСЛИ
Если вы ищете решение, которое работает во всех версиях Excel, используйте функцию ЕСЛИ для проверки условия, а затем передайте полученный массив функции:
=МАКС(ЕСЛИ(B2:B15=F1; C2:C15))
Чтобы это работало, вам нужно одновременно нажать Ctrl + Shift + Enter, чтобы ввести как формулу массива. Если все сделано правильно, Excel заключит ваше выражение в {скобки}, что является визуальным признаком формулы массива.
Вы также можете тестировать несколько условий одновременно, о чем мы поговорим далее.
МАКС + СУММПРОИЗВ
Если вам не нравится использовать формулы массива на листах, объедините МАКС с функцией СУММПРОИЗВ, которая умеет работать с массивами:
=СУММПРОИЗВ(МАКС((B2:B15=F1)*(C2:C15)))
МАКСЕСЛИ
В Excel 2019 и Office 365 есть специальная функция MAXIF, предназначенная для поиска наибольшего значения на основе 126 критериев.
В нашем случае используется только одно условие, поэтому формула очень проста:
=МАКСЕСЛИ(C2:C15;B2:B15;F1)
На следующем снимке экрана показаны все 3 формулы в действии:
Получить максимальное значение без учета нулей
По сути, это разновидность условного MAX, рассмотренного в предыдущем примере. Чтобы удалить нули, используйте логический оператор «не равно» и поместите выражение « 0» в критерии функции МАКСЕСЛИ или в конструкцию МАКС+ЕСЛИ.
Как вы понимаете, проверка этого условия имеет смысл только в том случае, если есть отрицательные числа. Для положительных чисел эта проверка избыточна, поскольку любое положительное число больше нуля.
Для проверки найдем наименьшую скидку в диапазоне C2:C7. Поскольку все скидки представлены отрицательными числами, наименьшая скидка на самом деле является наибольшей величиной.
Обязательно нажмите Ctrl + Shift + Enter, чтобы правильно завершить эту формулу массива:
=МАКС(ЕСЛИ(C2:C70, C2:C7))
И это обычная формула –
=МАКСЕСЛИ(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 может быть хорошим решением, если ваш набор данных содержит хотя бы одно положительное число или нулевое значение:
=МАКСЕСЛИ(A1:B6,A1:B6,»>=0″)
Поскольку формула находит максимальное значение с условием «больше или равно 0», она не будет работать с набором данных, состоящим только из отрицательных чисел.
Также идеальна функция ADD, которая может выполнять ряд операций и игнорировать значения ошибок:
=ДОБАВЛЕНО(4,6;A1:B6)
Число 4 в первом аргументе говорит ему искать максимальное число, шесть во втором аргументе — это опция «игнорировать ошибки», а A1: B6 — ваш целевой диапазон.
Все формулы возвращают один и тот же результат:
Как найти абсолютное максимальное значение в Excel
При работе с диапазоном положительных и отрицательных чисел иногда может потребоваться найти наибольшее абсолютное значение, независимо от знака.
Первая идея, которая приходит в голову, это взять абсолютные значения всех чисел в диапазоне с помощью функции ABS и передать их в MAX:
{= МАКС(АБС(диапазон))}
Это формула массива, поэтому не забудьте подтвердить ее с помощью Ctrl + Shift + Enter. Еще одно предупреждение: он работает только с числами и выдаст ошибку в случае нечисловых данных.
Вам не нравится это решение? Тогда давайте создадим что-то более работоспособное 🙂
Что, если мы найдем минимальное значение, изменим или проигнорируем его знак, а затем вычислим вместе со всеми остальными числами? Да, это будет работать как обычная формула. В качестве дополнительного бонуса это очень хорошо обрабатывает ввод текста и ошибки.
С исходными данными в формате A1:B5 расчеты выглядят следующим образом.
Формула массива (заполняется Ctrl+Shift+Enter):
=МАКС(АБС(А1:В5))
Обычная формула (заполняется клавишей Enter):
=МАКС(МАКС(A1:B5), -МИН(A1:B5))
или
=МАКС(МАКС(A1:B5), АБС(МИН(A1:B5)))
На следующем снимке экрана показаны результаты:
Вернуть максимальное абсолютное значение с сохранением знака
В некоторых ситуациях может потребоваться найти наибольшее абсолютное значение, но вернуть число с исходным знаком, а не его абсолютное значение.
Предполагая, что числа находятся в ячейках A1: B5, вот используемое выражение:
=ЕСЛИ(АБС(МАКС(A1:B5))>АБС(МИН(A1:B5)), МАКС(A1:B5),МИН(A1:B5))
Казалось бы сложная логика довольно проста. Сначала найдите наибольшее и наименьшее числа в диапазоне и сравните их абсолютные значения. Если абсолютное максимальное значение (без знака) больше абсолютного минимального значения, возвращается наибольшее число; в противном случае возвращается наименьшее число.
Поскольку формула возвращает исходное значение, а не абсолютное значение, информация о знаке сохраняется (см снимок экрана выше).
Максимальное значение применительно к тексту.
Предположим, у нас есть список имен и нам нужно вычислить его максимальную длину.
Здесь мы должны применить функцию DLSTR для вычисления длины имени.
=LSTR(A2)
А затем применяем функцию MAX, чтобы узнать максимальную длину полного имени.
=МАКС(В2,В13)
Или вы можете пропустить дополнительный столбец и вычислить максимальную длину текстового значения в ячейке. Для этого необходимо объединить эти формулы, внедрив DLSTR в MAX:
=МАКС(ДЛИНА(A2:A13))
Обратите внимание, что это формула массива. Ведь можно работать не только с диапазоном, но и с массивом числовых значений, который формируется другой функцией.
Вычислить наибольшую разницу в сумме.
Допустим, нам нужно вычислить наибольшую разницу в сумме за любой смежный месяц для каждого товара. Или, другими словами, найти максимальное месячное изменение продаж.
Ниже приведена таблица с небольшой выборкой данных за 4 месяца.
Если решать задачу «в лицо», можно использовать функцию ЕСЛИ со многими условиями:
=ЕСЛИ(И((МАКС(B2:C2)-МИН(B2:C2))>(МАКС(C2:D2)-МИН(C2:D2)), (МАКС(B2:C2)-МИН(B2:C2))))>(МАКС(D2:E2)-МИН(D2:E2))), МАКС(B2:C2)-МИН(B2:C2), ЕСЛИ((МАКС(C2:D2)-МИН(C2:D2)))>(MAX(D2:E2)-MIN(D2:E2)), MAX(C2:D2)-MIN(C2:D2),MAX(D2:E2)-MIN(D2:E2)))
Но с помощью функции MAX можно значительно упростить расчет и не использовать такую длинную и сложную раскладку.
Через функцию ABS мы будем искать максимальное изменение независимо от сигнала. Попробуйте заменить большую формулу на эту:
= МАКС (АБС (B2-C2), АБС (C2-D2), АБС (D2-E2))
Как видно из таблицы ниже, этот вариант намного проще и возвращает те же результаты.
Адрес ячейки с максимальным значением.
Есть ли решение, которое сообщает адрес ячейки с большим номером, а не сам номер? То есть, если A4 равно 9 и это значение самое большое, как мне написать формулу, которая возвращает координаты A4 вместо 9?
Вы можете использовать функции ЯЧЕЙКА, ИНДЕКС и ПОИСКПОЗ в сочетании с функцией МАКС, чтобы вернуть адрес ячейки с наибольшим значением.
Например, если вы хотите найти ячейку с наибольшим значением в столбцах от A1 до A7, вы можете использовать следующий расчет:
=ЯЧЕЙКА(«адрес», ИНДЕКС(A1:A7,ПОИСКПОЗ(МАКС(A1:A7),A1:A7,0)))
В результате получаем абсолютную ссылку $A$4, которая указывает на наибольшее число в этом диапазоне.
Однако обратите внимание, что это выражение работает только с одним столбцом.
А вот еще несколько альтернативных вариантов поиска адреса самого большого числа в столбце, из которых вы можете выбрать наиболее подходящий вам:
Формула массива –
{=АДРЕС(ПОИСКПОЗ(МАКС(F1:F7),F1:F7,0),СТОЛБЦ(F1:F7),4)}
Типичная формула, в которой вам нужно явно указать столбец:
=»F»&ПОИСКПОЗ(МАКС(F1:F7),F1:F7,0)
Другая формула массива
{=ЯЧЕЙКА(«адрес», ИНДЕКС(F2:F7,ПОИСКПОЗ(МАКС(F2:F7),F2:F7,0)))}
На скриншоте видно, что все они возвращают один и тот же адрес:
И еще одна формула массива для вывода адреса, которая работает с диапазоном из нескольких строк и столбцов:
{=АДРЕС(МАКС(ЕСЛИ((C2:E7=МАКС(C2:E7)), СТРОКА(C2:E7))), МАКС(ЕСЛИ((C2:E7=МАКС(C2:E7))) , СТОЛБЦ (С2 :Е7))))}
В данном случае, как видите, мы ищем адрес максимального числа в двумерном диапазоне C2:E7.
Напомню, что формула массива вводится с помощью комбинации клавиш Ctrl+Shift+Enter.
Как выделить максимальное значение в Excel
В ситуации, когда вы хотите определить наибольшее число в исходном наборе данных, самый быстрый способ — выделить его с помощью условного форматирования Excel. Следующие примеры покажут вам два разных варианта.
Выделите максимальное число в диапазоне
Microsoft Excel имеет предопределенное правило для форматирования максимального числа чисел в диапазоне, который идеально соответствует нашим потребностям. Вот как вы можете применить это:
- Выберите диапазон чисел (в нашем случае C2:E8).
- На вкладке «Главная» в группе «Стили» нажмите «Условное форматирование» > «Новое правило».
- В диалоговом окне «Новое правило формата» выберите «Форматировать только первое или последнее значение».
- На нижней панели выберите «первый» из раскрывающегося списка и введите 1 в поле рядом с ним (это означает, что вы хотите выделить только ячейку, содержащую наибольшее значение).
- Нажмите кнопку «Формат» и выберите стиль макета.
- Нажмите OK дважды, чтобы закрыть оба окна.
Как выделить цветом максимальное значение в каждой строке
Поскольку нет встроенного правила для извлечения наибольшего значения из каждой строки, вам нужно будет установить собственное правило на основе функции MAX. Вот как:
- Выберите все строки, которые вы хотите проверить (C3:E8).
- Щелкните Новое правило > Использовать формулу.
- В поле «Значения формата» введите:
=C3=МАКС($C3:$E3)
Где C3 — верхняя левая ячейка, а $C3:$E3 — координаты первой строки. Чтобы правило работало, обязательно задайте координаты столбца в диапазоне со знаком $.
- Нажмите кнопку «Формат» и выберите нужный формат.
- Нажмите ОК дважды.
Наконечник. Таким же образом можно выделить максимальное значение в каждом столбце. Шаги точно такие же, за исключением того, что вы пишете условие диапазона для первого столбца и фиксируете координаты строки:
=C2=МАКС(C$2:C$7)
Дополнительные сведения см в разделе «Как создать правило условного форматирования на основе формул» .
Почему не получается найти максимальное число?
MAX — одна из самых простых в использовании функций Excel. Если, вопреки всем ожиданиям, не работает, скорее всего, это одна из следующих проблем:
МАКС возвращает ноль
Если обычная формула MAX возвращает 0, даже если в указанном диапазоне есть явно большие числа, эти числа, скорее всего, представлены в текстовом формате.
Это особенно верно, когда вы запускаете функцию МАКС для данных, полученных из других формул или импортированных из других программ.
Вы можете проверить это с помощью функции ISNUMBER, например:
=ЧИСЛО(A1)
Если приведенная выше формула возвращает ЛОЖЬ, то значение в ячейке A1 не является числовым. Это означает, что вы должны устранять неполадки в исходных данных, а не в формуле.
МАКС возвращает значение #Н/Д, #ЗНАЧ или другую ошибку.
Пожалуйста, внимательно проверьте указанные ячейки. Если какая-либо из указанных ячеек содержит ошибку, поиск максимума вернет ту же ошибку. Чтобы избежать этого, см. Как получить максимальное значение, игнорируя все ошибки .
Вот как вы можете определить максимальное значение в Excel. Спасибо за чтение!
Как сравнить два столбца на наличие совпадений и различий — чтение этой статьи займет у вас около 10 минут, а в следующие 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные…
Сравнение ячеек в Excel. Вы узнаете, как сравнивать значения в ячейках Excel для точного совпадения или совпадения с учетом регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству…
Как использовать функцию МИН, чтобы найти минимальное значение в Excel — 6 примеров — В этой статье вы узнаете, как использовать функцию МИН в Excel 2007-2019, найти минимальное значение, наименьшее значение по условию и выделить наименьшее число в вашем диапазоне. Сегодня мы расскажем вам…
Как проверить правильность ввода данных в Excel? — Подтвердите правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода по существующему списку допустимых значений. В случае правильной записи в отдельной графе поставить…
Функция ЕСЛИ: проверка условия с текстом. Рассмотрите возможность использования функции ЕСЛИ в Excel, если в ячейке есть текст. Условие ContentTest для полнотекстового сопоставления.IF + MATCHИспользуя функцию ЕСЛИ с частичным сопоставлением текста.IF + SEARCHIF…
Визуализация данных с помощью функции ЕСЛИ: Функцию ЕСЛИ можно использовать для вставки символов в таблицу, которые визуально отображают изменения в данных. Например, мы хотим показать в отдельной колонке таблицы, есть увеличение или уменьшение продаж.…
3 примера того, как функция ЕСЛИ работает с датами. — На первый взгляд может показаться, что функцию ЕСЛИ можно использовать для работы с датами точно так же, как и с числовыми и текстовыми значениями, которые мы только что обсуждали. К сожалению, это…
Функция ЕСЛИ в Excel — варианты использования — на примерах посмотрим, как можно использовать функцию ЕСЛИ в Excel, а также какие задачи мы можем с ее помощью решать