Связанные значения в excel

Закрыть ... [X]

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

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

Синтаксис функции

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

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

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

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

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

Задачи

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

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

Вопрос 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 (Первая продажа Белова). В какой день Белов продал первую партию товара?

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

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

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

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

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

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

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

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

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

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


Источник: http://excel2.ru/articles/funkciya-dmin-nahozhdenie-minimalnogo-znacheniya-po-usloviyu-v-ms-excel-dmin


Поделись с друзьями



Рекомендуем посмотреть ещё:



Похожие новости


Вязать пинетки на спицах уроки для начинающих
Способы вязания спицами пятки носков на
Связать кофту летучую мышь схемы
Вязание крючком салфетка невеста
Простая схема вязания крючком с подробным описанием


Связанные значения в excel Связанные значения в excel Связанные значения в excel Связанные значения в excel Связанные значения в excel Связанные значения в excel Связанные значения в excel

ШОКИРУЮЩИЕ НОВОСТИ