Прогнозы по трендам

[bond045]

Здравствуйте!

Пытаюсь построить прогнозы по трендам. С линейными вопросов нет, разобрался. А вот с полиномиальными возникла проблема - не могу найти в списке функций те, которые возвращают коэффициенты полиномов по существующему числовому ряду. Графики трендов по числовым рядям строятся и функции с коэффициентами в них выводятся - значит, существуют какие-то функции, позволяющие их считать. Если кто знает, подскажите, плз.
Спасибо!

[Алексей Шмуйлович]

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

[bond045]

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

[Алексей Шмуйлович]

Вот, порылся в архиве, нашел:

MS Excel - Вывод формул из графиков линий тренда

Максим Сергеевич, 24.05.07 15:49:

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


Алексей Шмуйлович, 24.05.07 20:37:

Вопрос очень интересный. Если Вас интересует линейная или экспоненциальная регрессия, то тут все просто. Коэффициенты такой регрессии можно получить с помощью функций ЛИНЕЙН() и ЛГРФПРИБЛ() соответственно. Следует обратить внимание, что указанные функции возвращают не одно значение, а массив значений. А именно - коэффициентов уравнений регрессии. То есть вводить формулу нужно сразу в несколько ячеек в строке с помощью клавиш Ctrl+Shift+Enter, либо с помощью функции Индекс() извлекать из массива нужный коэффициент уравнения регрессии. Если же речь идет о других типах линий тренда, придется создавать пользовательскую функцию в VBA. Примерно так:

Код Выделить Развернуть

Public Function Уравнение()

Уравнение = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1).DataLabel.Text

End Function

Создайте в своей книге новый модуль, скопируйте туда функцию. Теперь можете использовать на листе функцию =Уравнение(). Теперь нужно извлечь коэффициенты. Сделать это можно с помощью текстовых функций Excel. Но и это еще не все. Пользовательская функция не будет пересчитываться автоматически при изменении данных. Чтобы это исправить, измените формулу так: =Уравнение()&СУММ(B3:B9). B3:B9 - диапазон исходных данных. В этом случае при изменении данных в диапазоне и наша функция будет пересчитана.


Максим Сергеевич, 25.05.07 06:58:

Спасибо, Алексей. Используется 2 линии тренда: Полиномиальная (простая парабола) и степенная. Т.е. видимо надо использовать VBA. Я плохо ориентируюсь в VBA, поэтому, по возможности, более детально расскажите что и куда надо написать, какие параметры менять. Я скопировал в модуль указанный код, ввел на листе функцию =Уравнение() и получил ошибку #ЗНАЧ! На листе не один график - их несколько. В одном из них используются линии тренда (сразу 2). Может надо поменять в коде цифры 1 на каким-то образом присвоенное имя графику?


Алексей Шмуйлович, 25.05.07 19:49:

Раз Вы плохо знаете VBA, лучше использовать средство записи макросов для поиска правильной ссылки на нужный график. Включите режим записи макроса (Сервис - Макрос - Начать запись). Выберите нужную диаграмму и сделайте с ней что-нибудь, например передвиньте. Остановите запись. Откройте редактор VBA. В последнем модуле Вы найдете запись макроса с Вашими действиями. Скорее всего в начале макроса Вы найдете что-нибудь, типа: ActiveSheet.ChartObjects(1).Select Вместо 1 будет нужная Вам цифра. Замените в функции 1 на нужный номер диаграммы. Кстати, учтите, функция должна быть размещена в общем модуле, а не модуле книги или листа. То есть Вы должны сами добавить в книгу пустой модуль и уже туду копировать функцию, иначе она будет выдавать ошибку. По поводу двух трендов. Лучше тогда создать две разные функции. Просто скопируйте функцию, измените ее имя, например на \"Уравнение2\" и в коде замените Trendlines(1) на Trendlines(2).


Максим Сергеевич, 26.05.07 09:59:

Через запись макроса название присваивается как ChartObjects(\"Диагр. 2\"). При изменении в новой функции цифры 1 на \"Диагр. 2\", выдается та же ошибка #ЗНАЧ! . Однако я поменял цифру 1 на 2 (как вторая диаграмма) и функция выдала значение 0. Ниже я


Максим Сергеевич, 26.05.07 10:12:

Намудрил я в функции немного, все получилось!!! Еще раз большое спасибо Вам Алексей за помощь.


Максим Сергеевич, 28.05.07 07:27:

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


Алексей Шмуйлович, 28.05.07 15:16:

То есть Вы хотите, чтобы функция работала в ячейках другого листа, не того где расположена диаграма?. Я бы не стал, так как в таком случае Вы лишаетесь возможности ссылаться на аналогичную диаграмму на другом листе или для нее нужно писать отдельную функцию. Можно просто ссылками вынести готовое значение на нужный лист. Если все-таки не убедил, то просто замените ActiveSheet на Sheets(1), где 1 - номер листа, где расположена нужная диаграмма.


Алексей Шмуйлович, 28.05.07 15:18:

Кстати, родился такой вариант решения описанных проблем:

Код Выделить Развернуть

Public Function Уравнение(Optional номер_листа = 1, Optional номер_диаграммы As Byte = 1, Optional номер_тренда As Byte = 1)

Уравнение = Sheets(номер_листа).ChartObjects(номер_диаграммы).Chart.SeriesCollection(1).Trendlines(номер_тренда).DataLabel.Text

End Function

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

[nockdown]

Задача у мея та же: "получить значения коэффициентов тренда"
А вот с реализацией пока не получается.

Порядок моих действий:
1) Сервис - Макрос - Редактор Visual Basic - VBAProject(имя файл в котором работаю) - вкладка Modules - правый клик на Modules - Insert Module - вставляю код:

Код Выделить Развернуть

Public Function Уравнение()

Уравнение = ActiveSheet.ChartObjects(33).Chart.SeriesCollection(1).Trendlines(1).DataLabel.Text

End Function
,где ChartObjects(33) - номер диаграммы на листе
Trendlines(1) - номер тренда

2) далее присваиваю какой-то ячейке из этой страницы значение =Уравнение()
3) она выдаёт результат #ЗНАЧ!

Вопросы:
1) правильно ли реализована часть с макрососом?
2) что нужно вводить в ячейках, в которых желаем получить коэффициенты тренда?

[Алексей Шмуйлович]

А Вы уверены в правильности указанного номера диаграммы? Попробуйте выполнить процедуру с записью макроса, описанную в цитате из архива. Или используйте второй вариант кода. Тогда можно указать номер диаграммы прямо в формуле.
Если имеете представление о процессе отладки VBA, сделайте точку останова в модуле в самом начале функции, чтобы, во-первых, понять, работает ли она вообще, во-вторых, если работает, то на чем спотыкается.

[Ingvar]

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

[nockdown]

А Вы уверены в правильности указанного номера диаграммы? Попробуйте выполнить процедуру с записью макроса, описанную в цитате из архива. Или используйте второй вариант кода. Тогда можно указать номер диаграммы прямо в формуле.
Если имеете представление о процессе отладки VBA, сделайте точку останова в модуле в самом начале функции, чтобы, во-первых, понять, работает ли она вообще, во-вторых, если работает, то на чем спотыкается.

Алексей Шмуйлович, а какой синтаксис в =УРАВНЕНИЕ(?)
что в скобках пишем?

[fookx]

Присоединяюсь к последнему вопросу, после создания модуля
Public Function Уравнение(Optional номер_листа = 1, Optional номер_диаграммы As Byte = 1, Optional номер_тренда As Byte = 1)

Уравнение = Sheets(номер_листа).ChartObjects(номер_диаграммы).Chart.SeriesCollection(1).Trendlines(номер_тренда).DataLabel.Text

End Function

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

[Алексей Шмуйлович]

Присоединяюсь к последнему вопросу, после создания модуля
Public Function Уравнение(Optional номер_листа = 1, Optional номер_диаграммы As Byte = 1, Optional номер_тренда As Byte = 1)

Уравнение = Sheets(номер_листа).ChartObjects(номер_диаграммы).Chart.SeriesCollection(1).Trendlines(номер_тренда).DataLabel.Text

End Function

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

Если используется вариант функции Public Function Уравнение(Optional номер_листа = 1, Optional номер_диаграммы As Byte = 1, Optional номер_тренда As Byte = 1) , то в ячейку нужно вводить такую формулу:

=уравнение(1;2;2)

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

Если указать просто =уравнение(), то вы получите первый тренд из первой диаграммы первого листа.

[zs]

почему не
РОСТ(известные_значения_y;известные_значения_x;новые_значения_x;конст)

[Алексей Шмуйлович]

Потому что трендов бывает 6 видов, а РОСТ - это только для линейной регрессии, если я не ошибаюсь

[zs]

в уравнении y = b*m^x.

[Serega100]

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

[Алексей Шмуйлович]

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

Выкладывайте свой файл с диаграммой и линией тренда, а я подключуфункцию