Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция дмин в excel

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

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

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

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

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

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

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

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

Задачи

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Читать еще:  Почему не сортируется столбец в excel

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу — это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

  • Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное
  • Диапазон_проверки — диапазон, который проверяется на выполнение условия
  • Условие — критерий отбора

Например, в нашем случае:

Просто, красиво, изящно. Одна проблема — функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

  • База_данных — вся наша таблица вместе с заголовками.
  • Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.
  • Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

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

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table) . В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:

ДМАКС и ДМИН для максимальных и минимальных значений БД в Excel

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

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

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

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

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

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

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

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

Читать еще:  Считать количество символов excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • база_данных – обязательный, может быть указан в виде ссылки на диапазон ячеек, которые соответствуют БД, списку или таблице, которая соответствует критериям, установленным для баз данных в Excel. В такой таблице строки являются записями, а столбцы – полями. В первой строке должны быть указаны наименования полей (столбцов).
  • поле – обязательный, принимает ссылку на ячейку, содержащую наименование столбца (поля) таблицы, списка или БД, где будет выполняться поиск наибольшего (ДМАКС) или наименьшего (ДМИН) значения соответственно. Может быть также указан в виде текстовых данных или числового значения – соответствующего номера столбца (отсчет начинается с левой части таблицы с числа 1).
  • условия – обязательный, принимает ссылку на диапазон ячеек, в которых указаны критерии поиска. В таком диапазоне должно содержаться хотя бы одно поле, соответствующее полю таблицы, списка или БД, в которых будет выполняться поиск максимального или минимального значения.
  1. Если любой из аргументов функции указан в виде данных недопустимого типа или ссылки на диапазон пустых ячеек, результатом выполнения любой из рассматриваемых функций будет код ошибки #ЗНАЧ!.
  2. Если в качестве аргумента поле был указан столбец (поле БД), не содержащий числовые данные, результатом выполнения функции будет значение 0 (нуль).
  3. Для удобства указания аргумента условия рекомендуют создавать отдельную таблицу, содержащую не менее двух записей (строк). При этом первая строка должна содержать наименование полей данных, полностью соответствующих наименованиям полей таблицы (списка или БД), где выполняется поиск. Вторая (и последующие) строка должна содержать критерии поиска (логические выражения, данные для сравнения). Такую таблицу условий следует размещать над основной таблицей (БД или списком), поскольку последняя может пополняться новыми записями со временем.
  4. При написании условий используются записи следующих видов:
  • =»=телевизор» – точное совпадение текстовой строки. Для неточных совпадений можно использовать замещающие знаки, например, «*» – любое число символов, «?» – один любой символ;
  • Для числовых данных используют знаки сравнения значений: «>», » «, «= «.

Функции баз данных

В данной категории собраны несколько специфические функции для работы с упорядоченными данными. Не зря категория включает в себя упоминание про базы данных: таблицы для работы этих функций должны отвечать определенным требованиям:

  • таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
  • таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
  • в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) — значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

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

Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:

Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля «Дерево» Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле «Возраст» больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6 , , <>0 (не равно нулю), >=7 , . Так же знаки равенства и сравнения можно применять и с текстовыми данными, например: <>«Яблоня» .

Читать еще:  Параметры вычислений в excel 2020

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных — ссылка на ячейки данных таблицы, включая заголовок( A6:E12 ).
поле — в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках («Дерево», «Возраст» или «Урожай»), так и число, задающее положение столбца в таблице: 1 — для первого поля(столбца) в таблице «Дерево», 2 — для второго поля «Высота», 3 — для третьего поля «Возраст» и так далее.
критерий — ссылка на диапазон ячеек с условиями отбора( A1:F3 ). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

    ДСРЗНАЧ (DAVERAGE) — Вычисляет среднее значение выбранных записей базы данных:
    =ДСРЗНАЧ( A6:E12 ;5; A1:F3 )
    =ДСРЗНАЧ( A6:E12 ;»Прибыль»; A1:F3 )
    =DAVERAGE( A6:E12 ,5, A1:F3 ) вернет значение 90 000р. , т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000 .

БСЧЁТ (DCOUNT) — Подсчитывает количество числовых ячеек в базе данных:
=БСЧЁТ( A6:E12 ;5; A1:F3 )
=БСЧЁТ( A6:E12 ;»Прибыль»; A1:F3 )
=DCOUNT( A6:E12 ,5, A1:F3 ) вернет число 2 , т.к. только две строки в таблице отвечают критериям

БСЧЁТА (DCOUNTA) — Подсчитывает количество непустых ячеек в базе данных:
=БСЧЁТА( A6:E12 ;4; A1:F3 )
=БСЧЁТА( A6:E12 ;»Прибыль»; A1:F3 )
=DCOUNTA( A6:E12 ,4, A1:F3 ) вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце «Прибыль»

БИЗВЛЕЧЬ (DGET) — Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
=БИЗВЛЕЧЬ( A6:E12 ;5; A1:F3 )
=БИЗВЛЕЧЬ( A6:E12 ;»Прибыль»; A1:F3 )
=DGET( A6:E12 ,5, A1:F3 ) для заданных условий вернет значение ошибки #ЧИСЛО! (#NUM!) , т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
=БИЗВЛЕЧЬ( A6:E12 ;5; A1:F2 ) то функция вернет значение 75 000р. , т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 — Яблона, высота 5)

ДМАКС (DMAX) — Находит максимальное значение среди выделенных записей базы данных:
=ДМАКС( A6:E12 ;5; A1:F3 )
=ДМАКС( A6:E12 ;»Прибыль»; A1:F3 )
=DMAX( A6:E12 ,5, A1:F3 ) вернет сумму 105 000р. , т.к. это максимальная прибыль из всех отвечающих критериям строк.

ДМИН (DMIN) — Находит минимальное значение среди выделенных записей базы данных:
=ДМИН( A6:E12 ;5; A1:F3 )
=ДМИН( A6:E12 ;»Прибыль»; A1:F3 )
=DMIN( A6:E12 ,5, A1:F3 ) вернет сумму 75 000р. , т.к. это минимальная прибыль из всех строк, отвечающих критериям

БДПРОИЗВЕД (DPRODUCT) — Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
=БДПРОИЗВЕД( A6:E12 ;3; A1:F3 )
=БДПРОИЗВЕД( A6:E12 ;»Возраст»; A1:F3 )
=DPRODUCT( A6:E12 ,3, A1:F3 ) вернет 210 , т.к. будут перемножены все значения столбца «Возраст», отвечающие критериям( 14*15=210 )

ДСТАНДОТКЛ (DSTDEV) — Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
=ДСТАНДОТКЛ( A6:E12 ;4; A1:F3 )
=ДСТАНДОТКЛ( A6:E12 ;»Урожайность»; A1:F3 )
=DSTDEV( A6:E12 ,4, A1:F3 ) вернет 0,707107 , т.е. оценку стандартного отклонения урожайности по указанным критериям.

ДСТАНДОТКЛП (DSTDEVP) — Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
=ДСТАНДОТКЛП( A6:E12 ;4; A1:F3 )
=ДСТАНДОТКЛП( A6:E12 ;»Урожайность»; A1:F3 )
=DSTDEVP( A6:E12 ,4, A1:F3 ) вернет 0,5 , т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.

БДСУММ (DSUM) — Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
=БДСУММ( A6:E12 ;5; A1:F3 )
=БДСУММ( A6:E12 ;»Прибыль»; A1:F3 )
=DSUM( A6:E12 ,5, A1:F3 ) вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
=БДСУММ( A6:E12 ;5; A1:A2 )
=DSUM( A6:E12 ,5, A1:A2 ) вернет сумму прибыли от всех Яблонь, т.е. 225 000р.

БДДИСП (DVAR) — Оценивает дисперсию по выборке из выделенных записей базы данных:
=БДДИСП( A6:E12 ;4; A1:A2 )
=БДДИСП( A6:E12 ;»Урожайность»; A1:A2 )
=DVAR( A6:E12 ,4, A1:A2 ) вернет 0,5 , что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду

  • БДДИСПП (DVARP) — Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
    =БДДИСПП( A6:E12 ;4; A1:A2 )
    =БДДИСПП( A6:E12 ;»Урожайность»; A1:A2 )
    =DVARP( A6:E12 ,4, A1:A2 ) вернет 10,66667 , т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду
  • Ссылка на основную публикацию
    Adblock
    detector