Функция ДМАКС() - нахождение максимального значения по условию в MS EXCEL

Функция ДМАКС(), английский вариант DMAX(), находит в столбце максимальное значение, удовлетворяющее заданным условиям.

Функция ДМАКС() относится к той же группе функций, что и БДСУММ(), БСЧЁТ() и др. Поэтому составление формул и задание критериев производится для ДМАКС() аналогично этим функциям.

Синтаксис функции ДМАКС()

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

  • исходная таблица имела заголовки столбцов;
  • критерии должны были оформлены в виде небольшой таблицы с заголовками;
  • заголовки таблицы критериев совпадали с заголовками исходной таблицы (если критерий не задается формулой).

ДМАКС(база_данных;поле;условия)
База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. Верхняя строка таблицы должна содержать заголовки всех столбцов. В Базе_данных строки называются записями, а столбцы — полями.
Поле  — заголовок столбца, в котором ищется максимальное значение, если выполняется условие. Существует несколько вариантов заполнения аргумента Поле:

  • можно ввести текстовое значение, представляющий собой название одного из заголовков Базы_данных (исходной таблицы) . Текст указывается в двойных кавычках, например "Возраст" или "Урожай",
  • можно ввести число (без кавычек), задающее положение столбца в Базе_данных: 1 — для первого столбца, 2 — для второго и т.д.
  • можно ввести ссылку на ячейку, содержащую заголовок столбца.

Условия  — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для ДМАКС() аналогична структуре для Расширенного фильтра и, например, функции БДСУММ().

Задачи

Предположим, что в диапазоне A5:D11 имеется таблица продаж (База_данных), содержащая Поля (столбцы) Товар, Продавец, Продажи и Дата (см. файл примера и рисунок выше).

Сформулируем задачи в виде вопросов.

Вопрос 1 (Определить максимальные продажи Белова). Т.е. найдем строки, в которой в столбце Продавец содержится значение Белов. Если такие строки есть в таблице, то для них выведем максимальное значение из столбца Продажи.

Алгоритм следующий:

  • Создадим в диапазоне G5:G6 табличку критериев (желательно над исходной таблицей или сбоку, чтобы она не мешала добавлению новых данных в таблицу), состоящую из заголовка (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);
  • Условия отбора должны быть записаны в специальном формате: ="=Белов" (будет производиться поиск в столбце Продавец только значений точно совпадающих со словом Белов (или белов, БЕЛОВ, т.е. без учета регистра). Если написать условие не в виде ="=Белов", а просто ввести в ячейку значение Белов, то условию будут удовлетворять текстовые строки, которые содержат слово Белов, например, «Белов Иван», «Иван Белов» и пр.)

Предполагаем, что База_данных (исходная таблица) находится в A5:D11. С5 – это ссылка на заголовок столбца, в котором ищется максимальное значение, если выполняется условие. G5:G6 – ссылка на табличку критериев (см. рисунок выше).

Итоговая формула выглядит так =ДМАКС(B5:C11;C5;G5:G6)

Как видно из исходной таблицы, условиям удовлетворяет 3 строки, поэтому результатом будет максимальное значение из массива (450; 6544; 1245), т.е. 6544.

Альтернативное решение - формула =НАИБОЛЬШИЙ(ЕСЛИ(B6:B11=G6;C6:C11;"");1), которая вводится как формула массива (подробнее см. здесь). В случае, когда ни одна строка не удовлетворяет условиям, то формула вернет ошибку #ЧИСЛО! в отличие от ДМАКС(), которая вернет 0 (это может ввести в заблуждение, т.к. не понятно есть ли в исходной таблице строки, удоблетворяющие критерию или нет. См. раздел Недостаток функции).

Вопрос 2 (Последняя продажа Белова). В какой день Белов продал последнюю партию товара?

=ДМАКС(B5:D11;D5;G5:G6)

Решение аналогично предыдущей задаче, только максимальное значение ищется не в столбце С (Продажи), а в столбце D (Дата).

Вопрос 3 (Максимальные продажи Мяса продавцом Белов)

=ДМАКС(A5:C11;C5;F5:G6)

В данном случае таблица критериев представляет собой 2 столбца Товар и Продавец.

Вопрос 4 (Максимальные продажи среди продаж Белова или Батурина)

=ДМАКС(B5:C11;C5;G5:G7)

В данном случае таблица критериев представляет собой 1 столбец Продавец с 2-мя строками (с фамилиями продацов Белов и Батурин).

В файле примера приведено решение и других задач.

Недостаток функции

В случае, когда ни одна строка не удовлетворяет условиям, то функция ДМАКС() вернет 0. Это может ввести в заблуждение, т.к. не понятно: есть ли на самом деле строки, удовлетворяющие критерию и максимальное значение =0 или в исходной таблице отстутствуют строки, удоблетворяющие критерию.

Как показано на рисунке выше, Параметр3 вообще отстутствует в исходной таблице, но "максимальным" значением является 0!

Лучше использовать альтернативную формулу =НАИБОЛЬШИЙ(ЕСЛИ(A6:A15=D6;B6:B15;"");1), которая возвращает ошибку #ЧИСЛО! в случае, если ни одна строка не удовлетворяет критерию.

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

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