XL: Поиск максимума функции
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента X и функции Y.
С точки зрения математики, для решения этой задачи требуется аппроксимировать функцию (кривой второго порядка, например), найти производную функции и приравнять её нулю. С большой долей вероятности полученное решение уравнения будет экстремумом функции.
Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().
За ранее благодарен.
Господа!
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента X и функции Y.
С точки зрения математики, для решения этой задачи требуется аппроксимировать функцию (кривой второго порядка, например), найти производную функции и приравнять её нулю. С большой долей вероятности полученное решение уравнения будет экстремумом функции.
Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().
За ранее благодарен.
Не совсем понял, что должен делать макрос - записать в ячейки значения экстремумов функции, либо построить график, либо ещё что?
В принципе, это-то ведь просто всё - нужно только мат. описание поиска экстремумов (я уже не помню все эти методы математичексих предствалений). А там уже надо их просто описать в коде. И всё. Блин, я в своё время в институте на ЕС такую фигню вообще сделал методом построения графика функции и поиска её макисмумов, ака в массиве ищутся максимумы -и всё.:)
Сам понимаешь, что во всех вышеописанных случаях максимум/минимум получившейся функции будет попадать в разные места. А если у тебя данные - результат статистических измерений, то неразумно пытаться провести сложную криву точно через точки. Правильным будет взять характер кривой сообразно теоретическим представлениям (например, экспонента) и провести ее что-нибудь вроде методом наименьших квадратов. В общем, сформулируй задачу корректно с точки зрения математики.
Господа!
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента 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
...В общем, сформулируй задачу корректно с точки зрения математики.
Господа, спасибо за то, что откликнулись!
По-моему, я сформулировал задачу достаточно корректно:
- дана функция, заданная по точкам;
- тербуется её аппроксимировать достаточно простой кривой, например 2 порядка (если бы речь шла о прямой, то вообще бы не было вопроса);
- желательно использовать встроенные функции Excel a-la ПРЕДСКАЗ(), которая позволяет экстраполировать вне заданных пределов аргумента;
Я совершенно согласен со всеми вами в том, что задачу можно решить разными способами: более или менее сложными в зависимости от требуемой точности.
Т.к. в моём случае особенная точность не требовалась. А нужно было всего лишь автоматизировать процесс, я решил её взяв просто максимум из всех игреков на интересующем меня диапазоне.
можно ли чуть подробнее об этом?
В общем, всем спасибо за критику :)
P.S. Если у кого-то возникнут идеи по поводу того, как ещё эту задачу можно решить с помощью стандартных функций, то прошу высказаться.
можно ли чуть подробнее об этом?
Cтроишь точечный график Y=f(X)
На точках - правый клик и указываешь Добавить линию тренда. Выбираешь полиномиальный 6-й степени
В параметрах тренда ставишь показывать уравнение на графике.
Имея уравнение находишь производную, потом максимум.
Просто брать максимум в приципе можно, если функция слабо меняется, иначе ошибки могут быть довольно большими.
Cтроишь точечный график Y=f(X)
На точках - правый клик и указываешь Добавить линию тренда. Выбираешь полиномиальный 6-й степени
В параметрах тренда ставишь показывать уравнение на графике.
Имея уравнение находишь производную, потом максимум.
Просто брать максимум в приципе можно, если функция слабо меняется, иначе ошибки могут быть довольно большими.
Да, а поскольку производная будет полиномом 5-й степени, который, как известно, не решается в радикалах, то находить нули производной придётся каким-нибудь методом типа деления пополам, Ньютона или кого-нибудь еще. Многовато геморроя... ;)
Да, а поскольку производная будет полиномом 5-й степени, ...
Спасибо за дельную идею - я как-то не обращал внимания на возможность вывода уравнения линии тренда.
Но, господа, как я писал выше всё не просто, а очень просто - функция будет кривой 2 порядка (полиномом 2 степени), значит её производная будет прямой (1 степень). Жизнь прекрасна!
Почти...
Я подозреваю, что коэффициенты полинома в ячейки Excel без VBA никак не вытянуть. Видимо, и с использованием VBA, это тоже не просто...
А мне, как писал выше, нужна не точность нажождения максимума (отсюда и 2 степень), а автоматизация процесса.
Моя поточечно заданная кривая зависит от параметра (вообще-то этих кривых несколько, но они все подобны). Поэтому мне не хочется для каждого случая руками вносить значения коэффициентов полинома с графика в ячейки. Уж проще на глаз по графику определить максимум...
Но, господа, как я писал выше всё не просто, а очень просто - функция будет кривой 2 порядка (полиномом 2 степени), значит её производная будет прямой (1 степень). Жизнь прекрасна!
Почти...
Я подозреваю, что коэффициенты полинома в ячейки Excel без VBA никак не вытянуть.
Если ты точно знаешь, что точки описываются параболой, то задача упрощается. Можно использовать ф-ции Excel такие как нахождение определителя матрицы. Если тебе нужен результат, а не сам процесс и не сильно скоро, могу сделать
программку на VBA (желательно бы иметь твой примерчик).
А коэффициенты полинома можно получить с помощью Сервис-Анализ-Регрессия, если предварительно создать колонку X^2.
Если ты точно знаешь, что точки описываются параболой, то задача упрощается. Можно использовать ф-ции Excel такие как нахождение определителя матрицы. Если тебе нужен результат, а не сам процесс и не сильно скоро, могу сделать
программку на VBA (желательно бы иметь твой примерчик).
А коэффициенты полинома можно получить с помощью Сервис-Анализ-Регрессия, если предварительно создать колонку X^2.
Я точно знаю, что точки не описываются прямой :).
Ничего сложнее параболы мне не нужно - т.к. точность получения экспериментальных точек оставляет желать лучшего.
На самом деле, меня уже удовлетворил то результат, о котором я писал выше (respect to Cutty Sark).
Поэтому меня скорее интересует процесс, а не результат. Но, несмотря на мои порывы, освоить VBA даже на примитивном уровне у меня не хватает времени (или мозгов - :)). Поэтому все вопорсы (в основном они не сложные - просто рутинные), я пытаюсь решить с помощью стандартных функций Exсel. Часто мне это удаётся - в сложных случаях я обращаюсь за советом.
Поэтому вопрос, можно ли с помощью ФУНКЦИЙ (не команд меню) получить эти коэффициенты, т.к. понятно, что экстремум функции a*x^2+b*x+с равен -b/2*a.
Я точно знаю, что точки не описываются прямой :).
Ничего сложнее параболы мне не нужно - т.к. точность получения экспериментальных точек оставляет желать лучшего.
Посмотри мой пример с нахождением коэфф-в полинома 2-й степени методом наименьших квадратов.
Использовал только ф-ции Excel. Даже не знал, что их так много.
Посмотри мой пример с нахождением коэфф-в полинома 2-й степени методом наименьших квадратов.
Использовал только ф-ции Excel. Даже не знал, что их так много.
Отличный пример - я вспомнил линейную алгебру :)
Но, если проверить, то полученные коэффициенты не совпадают с коэффициентами уравнения линии тренда (полином второй степени), да и значение максимума функции, судя по графику, должно быть где-то около 1, а не 1.7...
К стати, для того чтобы не вводить дополнительный столбец с X^2 можно использовать формулы массива:
вместо =СУММПРОИЗВ(B2:B30;C2:C30)
если C2=A2^2; C3=A3^2; ... C30=A30^2
можно написать {=СУММ((B2:B30)*(A2:A30)^2)}
Отличный пример - я вспомнил линейную алгебру :)
Но, если проверить, то полученные коэффициенты не совпадают с коэффициентами уравнения линии тренда (полином второй степени), да и значение максимума функции, судя по графику, должно быть где-то около 1, а не 1.7...
К стати, для того чтобы не вводить дополнительный столбец с X^2 можно использовать формулы массива:
вместо =СУММПРОИЗВ(B2:B30;C2:C30)
если C2=A2^2; C3=A3^2; ... C30=A30^2
можно написать {=СУММ((B2:B30)*(A2:A30)^2)}
Что же ты меня Игорек позоришь на весь Интернет :(
Посылаю пример с трендом и его уравнением в свою защиту.
А вариант {=СУММ((B2:B30)*(A2:A30)^2)} выдает ерунду. Думаю, если бы вектора так просто умножались и возводились, не придумали бы ф-цию СУММПРОИЗВ.
Что же ты меня Игорек позоришь на весь Интернет :(
Посылаю пример с трендом и его уравнением в свою защиту.
А вариант {=СУММ((B2:B30)*(A2:A30)^2)} выдает ерунду. Думаю, если бы вектора так просто умножались и возводились, не придумали бы ф-цию СУММПРОИЗВ.
Точно, это я виноват - тупо построил простой график (с равномерным распределением значений функции), а не точечную диаграмму... :)
К слову сказать, меня зовут Илья. Меня уже достали письма для Игоря (Гаррика), которые сыпятся в мой ящик на Yandex'е. Если ты его знаешь, то попроси со мной связаться: [email]IKor@yandex.ru[/email]
По поводу моего варианта:
Нужно использовать формулу массива: т.е. ввести предложенную формулу (без фигурных скобок) в ячейку и нажать Ctrl+Enter (и скобки появятся :)). Проверено - работает.
Функции массива значительно расширяют возможности обычных функций Excel, но, кончно, не до уровня VBA. В принципе, многие функции типа СУММПРОИЗВ() являются простыми частными случаями функций массива.
По поводу моего варианта:
Нужно использовать формулу массива: т.е. ввести предложенную формулу (без фигурных скобок) в ячейку и нажать Ctrl+Enter (и скобки появятся :)). Проверено - работает.
Спасибо за формулу массива. Вот ведь как удобно! А я не подозревал.
Только у меня она вставляется не Ctrl+Enter, а Ctrl+Shift+Enter.
Спасибо за формулу массива. Вот ведь как удобно! А я не подозревал.
Только у меня она вставляется не Ctrl+Enter, а Ctrl+Shift+Enter.
Точно! у меня тоже с шифтом - это я торопился :)