Функция дмакс в excel примеры

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

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

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

Примеры использования функций ДМИН и ДМАКС в Excel по условию

Пример 1. В таблице содержатся данные о сотрудниках фирмы (оформлена с учетом требований к БД). Необходимо определить:

1. Минимальный и максимальный возраст мужчины.
2. Минимальный и максимальны возраст женщины.
3. Максимальный возраст члена коллектива с фамилией, начинающейся на «Б».

Вид таблицы данных:

На этом же листе создадим таблицу условий и таблицу для вывода найденных значений:

В ячейку C2 поместим список с двумя возможными значениями условий выборки из базы данных («М» и «Ж»).

Для нахождения минимального/максимального возраста мужчины используем функции:

=ДМИН(A10:D18;D1;A1:D2)

=ДМАКС(A10:D18;D1;A1:D2)

Описание аргументов:

• A10:D18 – диапазон ячеек, в которых находится БД;
• D1 – ячейка с названием поля данных, в котором хранятся сведения о возрасте;
• A1:D2 – диапазон ячеек, в которых находится таблица условий.

В таблице критериев установим условие значением пола «Ж» и аналогичным способом найдем значения минимального/максимального возраста женщины:

Удалим содержимое из ячейки C2, в ячейке B2 введем строку =»=Б*». Для поиска максимального возраста с установленным критерием используем функцию:

=ДМАКС(A10:D18;4;A1:D2)

Данная запись является избыточной, поскольку после удаления значения из C2 будет произведен автоматический расчет с учетом нового критерия в ячейке D5. Однако, данная функция приведена для наглядности, чтобы продемонстрировать возможность указания второго аргумента в виде числового значения (в данном случае 4 – четвертый столбец, соответствующий полю «Возраст»). Полученный результат:

В результате мы получили все данные после выборки с условием отбора возраста из общей БД в Excel.



Сравнение максимального и минимального значения в Базе Данных Excel

Пример 2. В таблице содержатся данные о сотрудниках, их должности (руководящее звено или штатный сотрудник), зарплатах и дате прима на работу. Найти разницу между минимальной зарплатой руководящего сотрудника и максимальной зарплатой штатного сотрудника, принятых на работу не позже 1.01.2015 года.

Вид таблицы данных:

Создадим две таблицы условий с разными условиями поиска. С помощью функции ДАТА запишем дату 1.01.2015 и установим формат «Общий» для ячейки с результатом, чтобы получить числовое представление даты – 42005. Получим следующее:

Для нахождения разницы зарплат используем следующую формулу:

Полученный результат:

Особенности использования функций ДМАКС и ДМИН в Excel

Обе рассматриваемые функции имеют однотипный синтаксис (список аргументов совпадает):

=ДМАКС(база_данных;поле;условия)

=ДМИН(база_данных;поле;условия)

Описания аргументов:

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

Примечания:

1. Если любой из аргументов функции указан в виде данных недопустимого типа или ссылки на диапазон пустых ячеек, результатом выполнения любой из рассматриваемых функций будет код ошибки #ЗНАЧ!.
2. Если в качестве аргумента поле был указан столбец (поле БД), не содержащий числовые данные, результатом выполнения функции будет значение 0 (нуль).
3. Для удобства указания аргумента условия рекомендуют создавать отдельную таблицу, содержащую не менее двух записей (строк). При этом первая строка должна содержать наименование полей данных, полностью соответствующих наименованиям полей таблицы (списка или БД), где выполняется поиск. Вторая (и последующие) строка должна содержать критерии поиска (логические выражения, данные для сравнения). Такую таблицу условий следует размещать над основной таблицей (БД или списком), поскольку последняя может пополняться новыми записями со временем.
4. При написании условий используются записи следующих видов:

• =»=телевизор» – точное совпадение текстовой строки. Для неточных совпадений можно использовать замещающие знаки, например, «*» – любое число символов, «?» – один любой символ;
• Для числовых данных используют знаки сравнения значений: «>», «<«, «=>», «=<» «<>».

Функция

ДМАКС()

, английский вариант 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)

, которая возвращает ошибку #ЧИСЛО! в случае, если ни одна строка не удовлетворяет критерию.

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

Описание функции ДМАКС

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

Синтаксис

=ДМАКС(база_данных; поле; условия)

Аргументы

Замечания

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

  Например, если диапазон G1:G2 содержит заголовок столбца «Доход» в ячейке G1 и значение 10 000 ₽ в ячейке G2, можно определить диапазон «СоответствуетДоходу» и использовать это имя как аргумент «условия» в функции баз данных.

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

Пример