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

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

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

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

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

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

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

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

    В качестве критерия можно указывать значения и логические выражения:

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

    Пример 2

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

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

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

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

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

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

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

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

    Читайте также:  К основным характеристикам центрального процессора относятся

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

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

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

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

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

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

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

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

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

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

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

    Ссылка на основную публикацию
    Как калибровать батарею андроид
    Смартфоны и планшеты работающие на базе ОС Android, имеют одну схожая проблему у многих пользователей. Спустя небольшой промежуток времени после...
    Как зарабатывать на прохождении игр
    Многие любители пасьянсов и тетрисов даже не подозревают, что их увлечение может стать дополнительным источником дохода, принося реальные деньги. Мир...
    Как запустить приложение в окне
    Обычная практика работы на компьютере предполагает пользование несколькими программами одновременно. Их открывают в оконном режиме, с одной на другую тогда...
    Как картинки перевести в один pdf файл
    ������ ������������� JPG � PDF? ������-��������� �������� ��������� � �������. ������������� � PDF ����� ����� ����������� (JPG, PNG, GIF ���...
    Adblock detector