Диапазон условий Excel

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

33575 31.03.2016 Скачать пример

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

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

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

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

Способ 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). В появившемся окне нажмите ОК:

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

Вытаскивать данные из сводной в дальнейшие расчеты теперь можно с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA), которую мы подробно разбирали ранее:

Ссылки по теме

  • Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
  • Выборочное вычисление суммы, среднего и т.д. по одному или нескольким критериям
  • Что такое «умные таблицы» в Excel и как с ними работать

Сравнить диапазоны

MulTEx » 1 Май 2011 Дмитрий 25454 просмотров

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • Часто задаваемые вопросы по MulTEx

Вызов команды:
MulTEx -группа Ячейки/Диапазоны -Диапазоны -Сравнить диапазоны

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

Вверху формы в поле указывается ссылка на диапазон из любой открытой книги. После выбора диапазона необходимо нажать Добавить к сравнению. Диапазон будет помещен в список диапазонов к сравнению.

Удалить выбранные — удаляет указанные диапазоны из списка к сравнению. Чтобы удалить необходимо выделить один или несколько диапазонов, которые необходимо исключить из сравнения и нажать Удалить выбранные.

Вывести список значений:

  • Уникальные для всех диапазонов(как одного объединенного) — на новом листе будет создан список, в котором будут содержаться значения всех диапазонов без повторений (если собрать значения диапазонов в один список и там будет присутствовать значение «пять» более одного раза, то на новом листе это значение будет выведено лишь один раз)
  • Присутствующие во всех диапазонах — на новом листе будет создан список из значений, которые встречаются в каждом из диапазонов. Если какое-либо значение (которое есть в других диапазонах) отсутствует хотя бы в одном из диапазонов — оно не попадет в результирующий список
  • Значения выделенного диапазона, отсутствующие в других — на новом листе будет создан список из значений, которые встречаются в первом выделенном диапазоне, но отсутствует в других диапазонах списка. Перед выполнением команды в данном случае необходимо выделить нужный диапазон в списке диапазонов. Важно: если перед выполнением команды было выделено более одного диапазона — то выделенным диапазоном будет считаться первый из выделенных
  • Значения выделенного диапазона, присутствующие в других — на новом листе будет создан список из значений, которые встречаются и в выделенном диапазоне и во всех других диапазонах списка. Перед выполнением команды в данном случае необходимо выделить нужный диапазон в списке диапазонов. Важно: если перед выполнением команды было выделено более одного диапазона — то выделенным диапазоном будет считаться первый из выделенных

Выделить цветом значения:

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

Примечание: данная команда может изменить форматирование ячеек (цвет заливки, если выбрано Выделить цветом), поэтому если имеется форматирование, которое необходимо сохранить, рекомендуется производить сравнение на копии данных.

Также см.:
Как получить список уникальных(не повторяющихся) значений?
Работа с дубликатами

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *