Поиск максимального значения в столбце в Excel

Наибольший / Больший (Малый / Меньший) в Excel. Бесплатные примеры и статьи.

Наибольший и Малый по условию в MS EXCEL

Больший по условию в MS EXCEL

Функция МАКС() в MS EXCEL

Функция НАИМЕНЬШИЙ() в MS EXCEL

Наибольший по условию с выводом номера позиции в MS EXCEL

Функция МИН() в MS EXCEL

Функция НАИБОЛЬШИЙ() в MS EXCEL

© Copyright 2013 — 2021 Excel2.ru. All Rights Reserved

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

Сообщение от lalaladododo

#2. Решаем задачки в Excel. Поиск большего значения

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

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

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

Итак, сперва мы как и раньше должны найти наибольший возраст. Для этого полностью подойдет функция МАКС, которую мы уже употребляли ранее.

Ну а дальше нам необходимо ассоциировать возраст всякого сотрудника с наибольшим и в случае совпадения выводить имя сотрудника в столбце F ниже.

На 1-ый взор ничего сложного, но не так все просто, как кажется и на данный момент вы в этом удостоверьтесь.

Дело в том, что все обычные функции Экселя, которые сходу приходят на мозг, выдадут только 1-ое значение из спектра, удовлетворяющее условию. Потому нам придется пользоваться формулой массива.

Формула массива является частью обычного функционала Экселя и почти все даже не догадываются о ее существовании.

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

Не буду на данный момент вдаваться в подробности, потому что эта заметка и так будет достаточно длинноватой. Если вы желаете выяснить больше о формуле массива, то напишите о этом в комментах и я раскрою тему подробнее в одной из последующих заметок.

Итак, давайте решим задачку.

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

1-ый ее аргумент — массив значений. Избираем все ячейки с именами служащих.

Как отыскать максимум функции в excel

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

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

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

ДИАПАЗОН — Спектр проверяемых ячеек.

Пример 1

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

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

Пример 2

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

Цель работы: Исследование способностей пакета Ms Excel при решении одномерных задач оптимизации. Приобретение способностей поиска экстремумов одномерной функции средствами пакета.

Решение данной задачки сводится к поиску на данном отрезке такового значения аргумента, которое доставляет наибольшее и (либо) малое значение мотивированной функции.

Последовательность и содержание действий такие же, как и при уточнении корня нелинейного уравнения при помощи надстройки Поиск решения в MS EXcel.

Для поиска максимума переключатель варианта в диалоговом окне Поиск решения установить наибольшему значению, а для минимума переключатель варианта установить минимальному значению.

Дальше задать ограничения для изменяемой ячейки. Порядок установки ограничений последующий:

1. щёлкнуть мышью по кнопочке Добавить в диалоговом окне Поиск решения;

2. в показавшемся окне Добавление ограничения щелчком мыши по ячейке установить абсолютный адресок изменяемой ячейки в окнеСсылка на ячейку:;

3. в среднем окне избрать вид ограничения ( =; =);

4. в окне Ограничение: ввести значение соответственной границы (в решаемой задачке дваограничения);

5. опосля установки ограничения щёлкнуть мышью по кнопочке ОK;

6. в окне Поиск решения щёлкнуть мышью по кнопочке Выполнить.

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

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

Нахождение локальных экстремумов функции

Если функция F(x) непрерывна на отрезке [a, b] и имеет снутри этого отрезка локальный экстремум, то его можно отыскать, используя надстройку Excel Поиск решения. Разглядим последовательность нахождения экстремума функции на примере.

Пример 1. Задана неразрывная функция Y= X 2 +X +2. Требуется отыскать ее экстремум (малое значение) на отрезке [-2, 2].

1. В ячейку А3 рабочего листа введите хоть какое число, принадлежащее данному отрезку, в данной ячейке будет находиться значение Х.

2. В ячейку В3 введите формулу, определяющую заданную многофункциональную зависимость (рис. 18). Заместо переменной Х в данной формуле обязана быть ссылка на ячейку А3: = A2^2 + A2 +2.

3. Сделайте команду меню Сервис — Поиск решения.

4. В открывшемся окне диалога Поиск решения в поле Установить мотивированную ячейку укажите адресок ячейки, содержащей формулу (В3), установите пере-ключатель Минимальному значению, в поле Изменяя значение ячейки укажите адресок ячейки, в которой содержится переменная х.

5. Добавьте два ограничения в соответственное поле: A3>= -2 и A3

В 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) с критериями. Логика работы данной функции последующая:

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

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

Метод 4. Сводная таблица

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

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

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