Страницы: 1
RSS
Работа с линией тренда из VBA
 
Создавал тему с нарушение правил, пересоздаю.
Знатоки и умельцы, просьба такая:

Есть набор графиков с линиями тренда, пользователь в юзер форме выделяет 1 (или несколько) графиков и по ним должен построиться прогноз (или прогнозы). На построенном наборе графика все кривые похожи на прямые, задача пользователя выбрать наиболее прямую. Т.е. зависимость у=f(x) линейна, тогда, зная зависимость из линии тренда, задавшись иском можно вычислить игрек или наоборот.
Сформулирую вопросы (они касательно этих графиков, надеюсь, модератор не заругает):

1) Как привязать конкретный график к конкретной галке в юзерформе? Т.е. галка в 1,6 и 7 - используем тренды этих графиков

2) Как "выдернуть" тренд для прогноза? Уважаемый Слэн в старой теме начал было обьяснять, но я не понял, а тему закрыли...хотелось бы увидеть сам код..т.е. пользователь выбрал график 2, с его тренда и надо строить прогноз.

3) Каждый график имеет 100 точек (примерно). Линейность графика может наблюдаться только с 30ой по 70ю, примерно. А до и после - полный хлам. В юзерформе сделал ввод этих самых 30 и 70 пользователем - как теперь заставить перестроиться линию тренда не от всех 100 точек (она будет выглядеть очень не точно), а по заданным точкам, где она будет почти идеальной прямой?

4) Возможно ли автоматическое определение экселем оптимального тренда? Т.е. он "видит", что с 30 по 70ю точку получается отличнейшая прямая - и рекомендует именно ее. Естесвенно, нужно ограничение на минимальный набор точек- если он ее сделает по 2...))) Наверное, это невозможно, нооо вдруг)

очень надеюсь на вашу помощь!!!
 
вот пример использования линейн для построения тренда и оценки точности

первая строка - коэффициенты определяются по всем точкам

вторая и дальше - все меньше точек(см формулы)

последний столбец - коэффициент детерминированности - показывает точность апроксимации
Живи и дай жить..
 
Слэн

супер! а как все это сделать в vba, чтобы пользователь ничего из этого не видел?)

столбцы e и f имеют одинаковые формулы в ячейках, а результаты - разные о0
столбец a  произвольно задан, я правильно понимаю?
Изменено: peat - 23.04.2013 10:56:45
 
ну как.. в общем-то как обычно, потихоньку, строчка за строчкой.

worksheetfunction.linest(y,,,,1) - чтобы получить статистику

менять диапазон ( скользящей или еще как) и искать наибольший коэффициент детерминированности

как нашли берете наклон и константу и по двум точкам строите прямую..
Живи и дай жить..
 
к сожалению, я вас совсем не понимаю(
если диапазона нет на листе, а он вычисляется только в вба как частное массивов  - linest с ним сработает?
 
ну вы же хвалились, что нарисовали график подобным образом, через массив, так же и тут
Живи и дай жить..
 
скольких трудов это стоило - когда знаний 0))
спасибо вам за совет, попробую разобраться.

выше вопрос задавал по вашему примеру - столбцы e и f имеют одинаковые формулы в ячейках, а результаты - разные - как такое может быть? я правильно понимаю, что при уравнении прямой y = kx +b, k = наклону, а b = константе?
 
потому что это формула массива - возвращает массив значений, а именно 2 - наклон и константу прямой., для этого она вводится не просто интером, а интер+шифт+контрол.

может возвращать и больше, как в 3м столбце - там просто мы не сам массив выводим, а лишь 1 элемент на пересечении 3й строки и 1го столбца
Живи и дай жить..
 
вот стыд-то, не смекнул, что это один массив, а не 2..
я правильно понимаю, что при уравнении прямой y = kx +b, k = наклону, а b = константе?
 
да, верно

вот вам примермакроса( см влож)

там берутся точки 30% от начала до 70% - ну просто так, для примера и строится по ним тренд

макрос в коде листа
Живи и дай жить..
 
Цитата
Слэн пишет:
вот вам примермакроса( см влож)

сложновато, с первого взгялда полностью не понял, но поверхносто кажется, что это то, что нужно! огромное спасибо)
 
Слэн

а вы не могли бы оставить комментарий к вашему коду?) там 9 переменных на такой короткий кусок)

Код
Sub t()
    Dim ar, r, r2, x, i&, k&, n&, j&, ar2 'они все задаются,как вариант (кроме &)? а зачем лонг?
    ar = [dat]  'я увидел, что это именованный диапазон, но подстановка вместе dat моего массива VNF (в соседней теме обсуждали) не помогла. код вставлял в существующую процедуру, ругался на повтор переменных, убрал, но проблема не ушла?
    r = WorksheetFunction.LinEst(ar, , , 1)
    n = UBound(ar) * 0.3
    k = UBound(ar) * 0.7
    ReDim x(1 To k - n + 1) 'что за массив х?
    For i = n To k
        j = j + 1
        x(j) = ar(i, 1) 'а зачем здесь 1?
    Next i
    r2 = WorksheetFunction.LinEst(x, , , 1)
    For i = 1 To UBound(ar)
        ar(i, 1) = i * r2(1, 1) + r2(2, 1)
    Next i
End Sub


если я вместо dat подставляю свой массив VNF, то выдается ошибка "Невозможно получить свойство LinEst класса worksheetFunction
Изменено: peat - 23.04.2013 22:07:04
 
Цитата
peat пишет: а зачем лонг?
Когда компьютеры были большими и оочень медленно умножали человеческие ошибки использовали даже byte :)  А теперь (со слов Слэна) все изменилось и можно использовать Long, которая к тому же инициализируется быстрее.
Цитата
ar = [dat]
это считывание данных из ячеек на листе сразу в массив. Получаем в Вашем случае двухмерный массив размерностью (1 to количество данных, 1 to1)
Цитата
ReDim x(1 To k - n + 1) 'что за массив х?
это "линейный" массив с Вашими ограничениями от 30% до 70% исходных данных.
Цитата
'а зачем здесь 1?
указываем из какого столбца массива берем данные. И даже если он всего 1, его надо указать.
Цитата
вместо dat подставляю свой массив VNF,
насколько помню Вы сами рассчитывали данный массив и он был одномерным "линейным в строчку":) Т.е. если его выгрузить на лист такой массив будет в 1 строчку и данные будут по столбцам.
 
Цитата
Igor67 пишет:
насколько помню Вы сами рассчитывали данный массив и он был одномерным "линейным в строчку":) Т.е. если его выгрузить на лист такой массив будет в 1 строчку и данные будут по столбцам.

а как же мне туда засунуть этот мой VNF?(
 
А Вы его не засовывайте, а используйте сразу. После того, как заполнили программно Ваш массив VNF ,  вставляете код от Слэна где меняете ar на VNF и убираете 1 при просмотре массива. Еще надо в модуле объявить Option base 1 что бы массивы инициализировались с 1, а не с 0. Надеюсь что не соврал, но я так думаю ;)
 
Цитата
Igor67 пишет:
После того, как заполнили программно Ваш массив VNF ,вставляете код от Слэна где меняете ar на VNF и убираете 1 при просмотре массива.

так и делал еще вчера, он спотыкается на строчке
Код
r = WorksheetFunction.LinEst(VNF, , , 1)


пробовал оставлять ar:
ar = VNF или ar = [VNF]
тоже самое

пробовал в самой книге создавать именованный диапазон VNF, равный частному двух столбцов - тот же результат. но вот когда я в книге проивзольный столбец ячеек наименовал VNF, то все заработало со скобками:
ar = [VNF]

всегда выдается ошибка "Невозможно получить свойство LinEst класса worksheetFunction"
Изменено: peat - 24.04.2013 11:14:27
 
выложите свой файл - я посмотрю
Живи и дай жить..
 
кнопка на 2ом листе, вставил полностью ваш код, только удалил выше объявленные переменные
 
дело оказалось в пустых значениях - в массивах, подставляемых в linEst, не должно быть пустых значений.

сначала этого не понял и перенес в свой файл для исследования, обратно лень  :)  так что уж что получилось , то и выкладываю
ps так как у вас точечная диаграмма, то и мне пришлось создавать два массива - по X и Y

а новый тренд строится по двум точкам - начальной и конечной по х, подставляя его в полученную с помощью linest формулу
Изменено: Слэн - 24.04.2013 15:30:22
Живи и дай жить..
 
ох, сколько кода) буду разбираться - спасибо большое!

еще остался 1 вопрос:

1) Как привязать конкретный график к конкретной галке в юзерформе? Т.е. галка в 1,6 и 7 - используем тренды этих графиков

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

Цитата
   With ActiveChart
   .Parent.Name = "Chart1"
....

а вызвать вот так:
Цитата
Worksheets("123").ChartObjects("Chart1").Activate
With ActiveChart
   .SeriesCollection.NewSeries
....

всем спасибо за помощь)
Страницы: 1
Читают тему
Наверх