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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Экстремум функции

Функция y = f(x) называется возрастающей (убывающей) в некотором интервале, если при x₁< x₂ выполняется неравенство(f(x₁) < f (x₂) (f(x₁) >f(x₂)).

Если дифференцируемая функция y = f(x) на отрезке [a,b] возрастает (убывает), то ее производная на этом отрезке f ‘ (x) > 0 , (f ‘ (x) < 0).

Точка x_о называется точкой локального максимума (минимума) функции f(x), если существует окрестность x_о, для всех точек которой верно неравенство f(x) ≤ f(x_о), (f(x) ≥f(x_о)).

Точки максимума и минимума называются точками экстремума, значения функции в них — ее экстремумами.

Точки экстремума

Необходимые условия экстремума. Если x_о является точкой экстремума функции f(x), то либо f ‘ (x_о) = 0, либо f (x_о) не существует. Такие точки называют критическими, причем сама функция в них определена. Экстремумы функции следует искать среди ее критических точек.

Первое достаточное условие. Пусть x_о — критическая точка. Если f ‘ (x) при переходе через x_о меняет знак плюс на минус, то в точке x_о функция имеет максимум, в противном случае — минимум. Если при переходе через критическую точку производная не меняет знак, то в x_о экстремума нет.

Второе достаточное условие. Пусть функция f(x) имеет производную f ‘ (x) в окрестности x_о и вторую производную f » (x₀) в самой точке x_о. Если f ‘ (x_о) = 0, f » (x₀)>0, (f » (x₀) <0), то x_о является точкой локального минимума (максимума) функции f(x). Если же f » (x₀)=0, то нужно либо пользоваться первым достаточным условием, либо привлекать высшие производные.

На отрезке [a,b] функция y =f(x) может достигать наименьшего или наибольшего значения либо в критических точках, либо на концах отрезка [a,b].

Пример 1. Найти экстремумы функции f(x) = 2x 3 — 15x 2 + 36x — 14.

Задачи на нахождения экстремума функции

Пример 2. Нужно построить прямоугольную площадку возле каменной стены так, чтобы с трех сторон она была отгорожена проволочной сеткой, четвертой стороной примыкала к стене. Для этого имеется a погонных метров сетки. При каком соотношении сторон площадка будет иметь наибольшую площадь?

Пример 3. Требуется изготовить закрытый цилиндрический бак вместимостью V=16p ≈ 50 м 3 . Каковы должны быть размеры бака (радиус R и высота Н), чтобы на его изготовление пошло наименьшее количество материала?

Решение. Площадь полной поверхности цилиндра равна S = 2pR(R+Н). Мы знаем объем цилиндра V = pR 2 Н Þ Н = V/pR 2 =16p/ pR 2 = 16/ R 2 . Значит, S(R) = 2p(R 2 +16/R). Находим производную этой функции:
S ‘ (R) = 2p(2R- 16/R 2 ) = 4p (R- 8/R 2 ). S ‘ (R) = 0 при R 3 = 8, следовательно,
R = 2, Н = 16/4 = 4.

Пример 4. Найти экстремумы функцииf(x) = 2x 3 — 15x 2 + 36x — 14.

Пример 5. Нужно построить прямоугольную площадку возле каменной стены так, чтобы с трех сторон она была отгорожена проволочной сеткой, четвертой стороной примыкала к стене. Для этого имеется c погонных метров сетки. При каком соотношении сторон площадка будет иметь наибольшую площадь?

[expert_bq наибольшее и наименьшее значения функции z 5xy-4 , если переменные x и y положительны и удовлетворяют уравнению связи frac frac-1 0. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Продолжим разбор примеров на нахождение условного экстремума функций нескольких переменных. В первой части мы работали с функциями двух переменных, а здесь обратимся к функциям трёх переменных. Если понадобятся примеры для функций большего количества переменных, отпишите мне, пожалуйста, на форум или в комментариях.

Условный экстремум функций многих переменных. Метод множителей Лагранжа. Первая часть.

Пример 2. Нужно построить прямоугольную площадку возле каменной стены так, чтобы с трех сторон она была отгорожена проволочной сеткой, четвертой стороной примыкала к стене. Для этого имеется a погонных метров сетки. При каком соотношении сторон площадка будет иметь наибольшую площадь?
[expert_bq положение глобального минимума модельной функции , который или принимается в качестве глобального минимума функции f x , или уточняется с помощью какого-либо метода локальной оптимизации. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Рассмотрим одномерную задачу условной глобальной оптимизации: найти минимум одномерной многоэкстремальной функции f ( x ), определенной в замкнутой области допустимых значений D = [ a ; b ] и имеющей в этой области конечное число минимумов (4.1).

Второй способ

Напомню, что значения функции $f(z)$ при заданных условиях связи совпадают с значениями функции $u(x,y,z)$, т.е. найденный экстремум функции $f(z)$ и есть искомым условным экстремумом функции $u(x,y,z)$. В принципе, несложно также указать остальные координаты точки условного экстремума:

XL: Поиск максимума функции

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

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

Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().

За ранее благодарен.

16 ответов

Originally posted by IKor
Господа!
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента X и функции Y.

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

Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().

За ранее благодарен.

Не совсем понял, что должен делать макрос — записать в ячейки значения экстремумов функции, либо построить график, либо ещё что?
В принципе, это-то ведь просто всё — нужно только мат. описание поиска экстремумов (я уже не помню все эти методы математичексих предствалений). А там уже надо их просто описать в коде. И всё. Блин, я в своё время в институте на ЕС такую фигню вообще сделал методом построения графика функции и поиска её макисмумов, ака в массиве ищутся максимумы -и всё.:)

На мой взгляд, ты поставил задачу некорректно уже с точки зрения математики. Например, взяв просто максимум из всех игреков, ты получишь прекрасного кандидата на максимум всей функции. Если рассматривать в общем виде, то очевидно, все зависит от того, какой способ интерполяции ты выберешь. Параболу, например, ты всегда гарантированно проведешь только через три точки. Если брать в общем виде — многочлен n-1-й степени (n — количество точек), то получишь один результат. А можно, например, приближать сплайнами. Если кто не знает, сплайны — это склейки из кривых, как правило многочленов третьей степени. То есть у тебя между каждыми двумя данными точками кривая строится как многочлен 3-й степени. У многочлена 3-й степени 4 степени свободы (4 коэффициента). 2 из них, очевидно, затрачиваются на то, чтобы кривая проходила через данные точки. Еще одно обычно тратят на то, чтобы она была гладкой в точках склеек — то есть, чтобы производная при подходе к точке склейки слева равнялась производной справа. Последнюю сепень свободы тратят по-разному в зависимости от задачи. Иногда — чтобы и вторая производная в точках склейки существовала, иногда — чтобы первая производная в них равнялась 0. Да как угодно.

Сам понимаешь, что во всех вышеописанных случаях максимум/минимум получившейся функции будет попадать в разные места. А если у тебя данные — результат статистических измерений, то неразумно пытаться провести сложную криву точно через точки. Правильным будет взять характер кривой сообразно теоретическим представлениям (например, экспонента) и провести ее что-нибудь вроде методом наименьших квадратов. В общем, сформулируй задачу корректно с точки зрения математики.

Originally posted by IKor
Господа!
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента X и функции
.
Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().

За ранее благодарен.

Алгоритм реализуется с помощью кубического сплайна.
Кривая между соседними точками моделируется у-ем
Y = a0 + a1*X + a2*X^2 + a3*X^3
Четыре неизвестных коэф-та находим из 4-х уравнений. Два берутся по значениям X,Y в двух рассматриваемых точках. Еще два по производным в этих точках. Производная в точке (i) равна наклону
отрезка между точками (i-1) и (i+1).
Это можно реализовать и без программирования в принципе, но больно громоздко.

Если точек у тебя не больше шести удобно воспользоваться трендом на графике. (Там полином не боше 6-й степени)

Можно в принципе определить коэффициенты полинома N-й степени с помощью Анализа в Сервисе, но для этого надо подготовить колонки X^2 X^3 . X^N