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

history

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


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

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

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

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

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

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

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

Задачи



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

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

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

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

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

Предполагаем, что База_данных (исходная таблица) находится в A 5: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) , которая возвращает ошибку #ЧИСЛО! в случае, если ни одна строка не удовлетворяет критерию.


Комментарии

Только для авторизованных пользователей

Аноним, 19 августа 2016 г.
(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved