Страницы: 1
RSS
Определить максимум у каждой из двух линий тренда, а затем разность максимумов
 
Здравствуйте! Из массива данных строятся два графика, которые аппроксимируются линией тренда полиномиального типа. Необходимо определить максимум у каждой из двух линий тренда, а затем разность максимумов. Проблема в том, что линии тренда не имеют под собой числовых данных. Можно ли это как-то обойти? В данный момент я делаю так - для каждой линии тренда вывожу уравнение на графике, затем копирую его в ячейку, подставляю нужную переменную в уравнение и затем строю график (который повторяет линию тренда), из которого уже и нахожу максимальное значение. Просто данный способ довольно трудоемкий, да еще с учетом того, что надо обработать несколько сотен графиков. Буду благодарен любым идеям)
Изменено: merks - 06.10.2016 11:29:49
 
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=515
 
Коэффициенты линии тренда можно получить без построения графика через функцию ЛИНЕЙН().
Вы в своем примере не приложили исходные данные, по которым можно былобы посчитать.
С помощью функции ТЕНДЕНЦИЯ() можно найти аппроксимированные значения полиномом и найти максимум
и все это одной формулой

Приложите таблицу с исходными данными, можно будет что-то придумать
 
Спасибо!Буду пробовать.MCH, в прикрепленном файле - исходная таблица, график с двумя функциями и линиями тренда; в строках 45 и 47 значения, вычисленные по уравнениям тренда. Из них уже считал максимальное значение и разность.
Изменено: merks - 20.08.2013 12:47:33
 
Пока данных не было, нарисовал свой файл
Приложено два варианта:
Вариант1 - по имеющимся значениям x, с помощью ТЕНДЕНЦИЯ находим значения аппроксимирующей функции полиномом второго порядка и определяем максимальное значение

Вариант2
Определяем коэффициенты полинома через функцию ЛИНЕЙН для уравнения
y=a*x^2+b*x+c

для нахождения экстремума, берем первую производную, и находим x для y'=0
y'=2*a*x+b
x=-b/(2*a)
подставляем найденный x в аппроксимирующую функцию и находим y через функцию ТЕНДЕНЦИЯ
 
Доброго времени суток!
Построила линию тренда, вывела формулу. Чисто визуально линия тренда очень близка к самому графику. R2=0.9854. Но когда по этой формуле считаю... цифры вообще получаются другие...Даже близко не похожи на саму линию тренда...
Прикладываю сам файл. В желтой таблице - исходные данные, в синий - то, что получается по полученной формуле, ну а в зеленой % отклонения... Аж до 30%.
Может быть я вообще принцип аппроксимации представляю себе неправильно?!!!
https://cloud.mail.ru/public/NAFk/X3fJMErhU
Изменено: Rateastwest - 06.10.2016 10:07:05
 
Rateastwest,
1. У Вас неравномерное распределение значений по оси X, поэтому Вы не имеете права использовать
тип диаграммы "График", в этом случае тип должен быть - "Точечная". То, что у Вас на диаграмме получилось
"красиво" - это случайность (см. Диаграмма2 в моём файле).
2. В формулах расчёта новых Y-значений лучше брать коэффициенты полученные именно расчётом , например
функцией ЛИНЕЙН , а не те, которые показаны на диаграмме, или сразу применить ф. ТЕНДНЦИЯ.
3. Но показатели - отклонения Y-ков, R2 (настоящий), да и график на Диаграмме 2 - получились тоже не очень хорошие.
Для улучшения (показателей) нужно подобрать более подходящую модель уравнения аппроксимации:
возможно использовать в формулах модели не сами исходные значения, а какие то их функции.
В моём файле X-ы "заменены" на обратные значения =1/X (см. Диаграмма3 и Диаграмма4),
и, в последнем  варианте модели, значения Y прологарифмированы (=Ln(Y)),
итого, три модели:
Y = A + B*X + C*X^2
Y = A + B*1/X + C*(1/X)^2
Ln(Y) = A + B*1/X + C*(1/X)^2
      Y = Exp(Ln(Y))
Изменено: С.М. - 06.10.2016 16:51:19
 
Гм... По ходу я еще неделю буду обдумывать Ваш файл...
С.М.! Огромное спасибо!!! Теперь я знаю, куда копать!!!
Изменено: Rateastwest - 06.10.2016 17:49:10
 
Копаю вторую неделю...
С.М.! Если Вы поможете мне разобраться более конкретно, буду очень признательна...
1. В данном случае мы пытаемся получить функциональную зависимость с помощью аппроксимации методом наименьших квадратов;
2. Форма нашего многочлена выглядит следующим образом: a0+a1*x+a2*x^2+a3*x^3+a4*x^4+a5*x^5+a6*x^6... на этом и остановимся...
3.Так что на данный момент наша цель - получить значения переменных а0, а1 и т.д.
4.Я создала таблицу, где указала исходые X  и Y;
5. Потом создала графы 1/x (судя по Вашему файлу, это где-то между х-сами а0 и а1), x^2, x^3 и т.д.
6. Коллениарно им должны быть переменные а1, а2 и т.д.
7. Как я могу с помощью Экселя найти переменные а0, а1, а2 и т.д.?
8. Или алгоритм моих размышлений априори неверный?
9. Файл прикладываю
10. Если вопрос мой совсем глупый, приношу свои извинения...(((((((((((((

https://cloud.mail.ru/public/7XvZ/Mpcz1Fpsj
 
Возможно вы не совсем правильно назвали свой файл
В таких случаях я использую алгоритмы ALGLIB .
Используйте функцию  UDF ReCalck, кнопка считает с шагом 0,01
5% на краю диапазона это нормально,обычно край не принимается в расчет
 
см. вложение
на рис. изображены 3 графика исходный, полином 5 степени и полином 8 степени.
видно, что полином 8-й степени практически повроряет все изгибы исходного графика
известно что при наличии К пар точек значения полинома степени К-1 точно вычисляют У для любого из значений Х
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Добер и Игорь!!!
БОЛЬШОЕ ЧЕЛОВЕЧЕСКОЕ СПАСИБО!!!
Пошла копать дальше... ))))))))))))))
Страницы: 1
Читают тему
Наверх