Макс значение в excel - компьютерный справочник

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

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

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

Функция баз данных ДМИН

База_данных – вся наша таблица вместе с заголовками.
Поле – название столбца из шапки таблицы, из которого выбирается минимальное значение.
Критерий – таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Источник

Максимальный и Минимальный по условию в MS EXCEL

минимальными значениями. одна из функцийВнимание!

функции МАКС иСОВЕТ:Решением является формула массива. клавишу Нахождение максимального/ минимального значения . необязательно создавать 3 ОК на всех

цветом трех ячеек

функция МИН. Данная она позволяет упорядочивать в числовой формат. ячеек, содержащие данные, посчитать сумму поВ ячейках D6:E6 мы работы с базойЭто формула массива, МИН, которые выбираютВсе вышеуказанные задачи можноТ.е. если в столбцеF9 — простая задача,Дмитрий Дорогобузов правила условного форматирования окнах. с наименьшими значениями. функция позволяет выбрать числовые массивы. Ее Это можно сделать для которых определяется нескольким условиям, смотрите установили функцию ДМАКС, данных в Excel. п. э. для

из таблицы максимальное

решить без использования формул

А). ИСТИНА соответствует строкам, но она несколько: Все получилось! Спасибо! для одного иК одному и тому Условное форматирование со наименьшее значение в можно, например, использовать формулой массива =НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;»»);1) k-ое наибольшее значение. в статье «Сумма

чтобы найти максимальную В ячейке Е7 ввода формулы нажимаем и минимальное значение, массива и функциизначение = Текст3, у которых в усложняется, если МАКС/Igor67 того же диапазона. же диапазону должно соответственными формулами прекрасно таблице Excel. А для сортировки списковНеобходимо помнить особенность функции Также возможен ввод

по условию Excel». цену товара из пишем формулу. сочетание клавиш «Ctrl» но без условий. ДМИН(). Для этого то учитывается значение столбце Текстовые значения МИН нужно найти: Упрощенный вариант, не Достаточно просто немного быть применено 3 справляется с данной вместо зеленого цвета

и таблиц. НАИБОЛЬШИЙ() при работе массива констант, например,Найдем максимальное/ минимальное только ячеек D3 иДля написания формулы, + «Shift» + Здесь мы рассмотрим, потребуется создать дополнительный в столбце содержится значение Текст1. не среди всех

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

среди повторяющихся значений. E3. можно вызвать окно «Enter». как выбрать max столбец, в которомBЧасть формулы ЕСЛИ(Текст=E6;Числа;»»), вернет значений диапазона, а данных, формула массива всего один символ Чтобы проверить выберите данными для примера: цвет. рода ранжирования или среди которых имеютсяkПусть исходный список содержит

Третий вариант. функции. У строкиМы имя столбца и min с будут выведены только

, если значение <> массив <10:»»:»»:»»:-66:»»:»»:»»: -37:»»:»»:»»:-5:»»: только среди тех,=НАИБОЛЬШИЙ(ЕСЛИ(($C2:$M2)=»Да»;$B2:$L2);СТОЛБЕЦ(A1)) оператора: =НАИМЕНЬШИЙ($B$2:$B$9;3)>=B2. То инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«УправлениеУсложним немного задачу. ЯчейкаК одному и тому анализа показателей успешности, повторы. Например, если

— позиция (начиная с повторяющиеся и неповторяющиесяСводная таблица в формул нажимаем на ввели в формулу конкретными условиями. те значения, которые Текст3, то учитывается

«»:»»:4:»»:»»:»»:8:»»:»»:»»:»»>, где вместо которые удовлетворяют определенномуC+S+E это Ctrl+Shift+Ener есть – больше правилами». с наименьшим значением же диапазону должно

очень удобно если

имеется исходный массив наибольшей) в массиве значения. На рисунке

кнопку «Мастер функций»

с помощью функции

У нас такая удовлетворяют критериям. Затем, максимальное значение+1, т.е. ИСТИНА подставлено значение условию. так вводятся формулы или равно.Три наименьшие значения автоматически должна автоматически получить быть применено 2 выделить цветом наилучший <1;2;3; или диапазоне ячеек. снизу выделены повторяющиесяКак сделать и (fx). В появившемся «Использовать в формуле».

Множественные условия

таблица с перечнем среди отобранных значений заведомо НЕ минимальное. из числового столбца,Пусть имеется таблица с

массива.Все описанные способы хороши выделились разными цветами.

красный цвет заливки.

правила условного форматирования. или наихудший результат.6 Если k ? 0 или значения (см. Файл работать со сводной окне в строке Подробнее об этом

товаров. с помощью функций Далее функция МИН() а вместо ЛОЖЬ двумя столбцами: текстовымМихаил С.

Множественные условия

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

В этом случае придется записать более сложную формулу массива :

= НАИМЕНЬШИЙ(ЕСЛИ(($A$6:$A$16=E6)*($B$6:$B$16=F6);$C$6:$C$16;””);1)

В файле примера для наглядности настроено Условное форматирование для выделения строк, удовлетворяющим критериям . Кроме того, для выбора критериев использован Выпадающий список (см. желтые ячейки).

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

Excel: как найти максимальное значение по группе

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

Например, предположим, что у нас есть следующий набор данных, и мы хотели бы найти максимальное значение «очков» для каждой команды:

В следующем пошаговом примере показано, как это сделать.

Сначала введите значения данных в Excel:

Шаг 2: Найдите уникальные группы

Затем нам нужно использовать функцию =UNIQUE() для создания списка уникальных имен команд.

В нашем примере мы введем следующую формулу в ячейку D2 :

Это создаст список уникальных команд:

Шаг 3: Найдите максимальное значение по группе

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

Мы введем эту формулу в ячейку E2 , а затем скопируем и вставим ее в оставшиеся ячейки в столбце E:

Это говорит нам:

Максимальное количество очков, набранных игроками на Mavs, равно 26 .
Максимальное количество очков, набранных игроками на «Уорриорз», равно 19 .
Максимальное количество очков, набранных игроками «Лейкерс», равно 33 .
Максимальное количество очков, набранных игроками в заезде, равно 19 .
Максимальное количество очков, набранных игроками «Селтикс», составляет 29 .

Примечание.Чтобы рассчитать минимальное количество очков, набранных каждой командой, просто замените MAX в формуле на MIN .

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

Функция НАИБОЛЬШИЙ( ) , английский вариант LARGE(), возвращает k-ое по величине значение из массива данных. Например, формула =НАИБОЛЬШИЙ(A2:B6;1) вернет максимальное значение (первое наибольшее) из диапазона A2:B6 .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В ячейку C2 поместим список с двумя возможными значениями условий выборки из базы данных («М» и «Ж»).

Для нахождения минимального/максимального возраста мужчины используем функции:

A10:D18 – диапазон ячеек, в которых находится БД;
D1 – ячейка с названием поля данных, в котором хранятся сведения о возрасте;
A1:D2 – диапазон ячеек, в которых находится таблица условий.

В таблице критериев установим условие значением пола «Ж» и аналогичным способом найдем значения минимального/максимального возраста женщины:

Удалим содержимое из ячейки C2, в ячейке B2 введем строку =”=Б*”. Для поиска максимального возраста с установленным критерием используем функцию:

Данная запись является избыточной, поскольку после удаления значения из C2 будет произведен автоматический расчет с учетом нового критерия в ячейке D5. Однако, данная функция приведена для наглядности, чтобы продемонстрировать возможность указания второго аргумента в виде числового значения (в данном случае 4 – четвертый столбец, соответствующий полю «Возраст»). Полученный результат:

В результате мы получили все данные после выборки с условием отбора возраста из общей БД в Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первый ее аргумент — массив значений. Выбираем все ячейки с именами сотрудников.

В этой статье мы поговорим и рассмотрим заложенные возможности, и варианты как применяется функция ПОИСКПОЗ. Эта функция является в некоторых случаях альтернативой, в некоторых случаях, функции ВПР в возможностях вертикального поиска значений. В некоторых случаях функция ПОИСКПОЗ более гибкая и имеет свои особенности, которые возводят ее на первое место.

Итак, знакомство начнём прямо сейчас, и первым шагом будет, знакомство с орфографией функции ПОИСКПОЗ. Эта функция ищет значение указанное вами и возвращает позицию полученного значения в диапазоне. К примеру, у нас есть диапазон B1:B3 в котором прописаны значения: Январь, Февраль, Март и последующая формула должна вернуть цифру 2, поскольку значение «Февраль», является вторым элементом в перечне.

Синтаксис функции ПОИСКПОЗ

Как видно из примера функция ПОИСКПОЗ имеет следующий синтаксис, который будем рассматривать более, подробнее:

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;), где:

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Дерево	Высота	Возраст	Урожай	Доход	Высота
=”=Яблоня”	>3				<5
=”=Груша”

Дерево	Высота	Возраст	Урожай	Доход
Яблоня	18	20	14	105 ₽
Груша	12	12	10	96 ₽
Вишня	13	14	9	105 ₽
Яблоня	14	15	10	75 ₽
Груша	9	8	8	77 ₽
Яблоня	8	9	6	45 ₽
Формула	Описание	Результат
=ДМАКС(A5:E11;”Доход”;A1:F3)	Максимальный доход от любой яблони высотой 3–5 метров или любого грушевого дерева. Грушевое дерево в строке 7 удовлетворяет этим условиям.	96 ₽