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

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

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

Синтаксис функции ДМИН()

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

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

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

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

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

Задачи

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

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

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

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

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

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

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

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

Альтернативное решение - формула =НАИМЕНЬШИЙ(ЕСЛИ(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.5 (4 оценок)

Комментарии

Петр (не проверено)

Отличная формула, то что искал. Спасибо.
Как сделать чтобы вариант с =МИН(...) не учитывал пустые ячейки или ячейки с нулевым значением при выборе наименьшего значения?

Creator

Лучше использовать эту формулу =НАИМЕНЬШИЙ(ЕСЛИ(B6:B11=G6;C6:C11;"");1)

Спасибо за коммент, Ваше замечание улучшило статью (действительно, у ДМИН() есть недостаток - она возвращает 0, когда ни одна строка не удовлетворяет критериям).

Елена (не проверено)

Если можно подскажите пожалуйста как изменить формулу, чтоб она не учитывала 0 при нахождении наименьшего числа?. Спасибо!

Creator

Если речь идет о формуле с функцией НАИБОЛЬШИЙ(), то для игнорирования 0 значений используйте формулу =НАИМЕНЬШИЙ(ЕСЛИ(B6:B15<>0;B6:B15;"");1)

Также можете посмотреть статью http://excel2.ru/articles/maksimalnyy-i-minimalnyy-po-usloviyu-v-ms-excel

Елена (не проверено)

меня интересует именно эта формула =НАИМЕНЬШИЙ(ЕСЛИ(B6:B11=G6;C6:C11;"");1). В данном случае понятно, что диапазон B6:B11 равен условию G6,а вот диапазон C6:C11 должен быть не равен 0, при вводе форумы =НАИМЕНЬШИЙ(ЕСЛИ(B6:B11=G6;C6:C11=F2:"");1),где F2 содержит 0(>0) то в ячейке отображается либо 0 либо 1.
PS: указывала диапазон C6:C11>0 результат аналогичный. :( помогите :((

Creator

Используйте формулу с 2-мя условиями: =НАИМЕНЬШИЙ(ЕСЛИ((B6:B11=G6)*(C6:C11<>0);C6:C11;"");1)

Кирилл (не проверено)

Добрый день! Делаю все как у Вас указано. Но почему то у меня после ввода ДМИН значение "0". Помогите пожалуйста.

Creator

ДМИН() может возвращать 0 во многих случаях. Пришлите, пожалуйста, Ваш файл на creator@excel2.ru или полностью напишите формулу + поясните, что у Вас содержится в диапазоне ячеек.