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

Как найти максимальное значение по условию

Описание работы

Найти максимальное значение в Excel не составляет труда. Однако при необходимости искать максимум по условию — проблематично. Для этих целей в надстройку добавлена новая функция =МАКСЕСЛИ (аналогична стандартной функции Excel СУММЕСЛИ).

В Excel версии 2016 и выше появилась встроенная функция МАКСЕСЛИ, можете пользоваться ей. Если ваш Excel более ранней версии, то эту функцию можно использовать установив надстройку VBA-Excel.

У функции следующие аргументы =МАКСЕСЛИ(ДИАПАЗОН;КРИТЕРИЙ;[ ДИАПАЗОН_ПОИСКА ])

ДИАПАЗОН — Диапазон проверяемых ячеек.

  • КРИТЕРИЙ — Условие в формате числа, выражения или текста, определяющее проверку максимального значения.
  • [ ДИАПАЗОН_ПОИСКА ] — Фактический диапазон для определения максимального значения. Если данный параметр не задан, будет использоваться ячейки, задаваемые параметром ДИАПАЗОН.
  • В качестве критерия можно указывать значения и логические выражения:

    1. Рассмотрим следующий пример в котором определяется максимальная оценка по литературе. Для этого в параметр КРИТЕРИЙ указано значение «Литература», а в параметр ДИАПАЗОН — список предметов.
    2. Если в качестве критерия указать логическое выражение «<>Русский», то определится максимальная оценка по всем предметам за исключением русского языка.

    В следующем примере параметр ДИАПАЗОН_ПОИСКА не задан, поэтому максимальное значение определяется среди ячеек указанных в параметре ДИАПАЗОН .

    Поиск наименьшего или наибольшего числа в диапазоне

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

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

    Ячейки, которые находятся в смежных строках или столбцах

    Выделите ячейку, расположенную ниже или справа от чисел, для которых нужно найти наименьшее число.

    На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Автосумма а, выберите пункт мин (вычисление наименьшей) или Max (подсчитывает наибольшее значение), а затем нажмите клавишу ВВОД.

    Если ячейки находятся не на смежных строках или столбцах

    Для выполнения этой задачи используйте функции MIN, MAX, малый или крупный.

    Скопируйте указанные ниже данные на пустой лист.

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

    В 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) :

    Читать еще:  Excel vba макрос

    =МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки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 в том же контексте, однако предназначена для нахождения минимального значения. Таким образом, работает обратно пропорционально первой функции.

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

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

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

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

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

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

    В ячейку 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. При написании условий используются записи следующих видов:
    • =»=телевизор» – точное совпадение текстовой строки. Для неточных совпадений можно использовать замещающие знаки, например, «*» – любое число символов, «?» – один любой символ;
    • Для числовых данных используют знаки сравнения значений: «>», » «, «= «.
    Читать еще:  Файл excel в xml файл

    Функции МАКС и МИН в Excel по условию

    Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

    Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

    Пусть имеются данные

    Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

    Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

    Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

    Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel

    На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

    Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

    Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

    Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

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

    Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.

    Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

    Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

    Похожие статьи

    Ссылка на основную публикацию
    Похожие публикации
    Adblock
    detector