Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Определиние коэффициентов тренда через VBA
 
#1
06.03.2010 15:30:52
У меня есть график  с двумя линиями тренда там уравнения второго порядка, имеется макрос на определиние их коэффициентов,  но нижняя линия не совсем точно описует график и необходимо её сделать полиномом третьего порядка, а макрос эти коэффициенты не может пересчитать, помогите изменить макрос для второго уравнения чтоб определял коэффициенты третьего порядка
 
 
 
#2
07.03.2010 00:54:06
Какой смысл определять параметры уравнения линии тренда в VBA, если затем эти параметры нужно записывать в ячейки? Все параметры без потери точности можно определить и без промежуточных преобразований формулами ячеек.    
 
Вот формулы для уравнения тренда с полиномом 3-й степени, где данные аргумента записаны в A2:A37, а данные функции записаны в B2:B37:  
Свободный член уравнения: =ИНДЕКС(ЛИНЕЙН($B$2:$B$37;$A$2:$A$37^{1;2;3});1;4)  
Коэффициент 1-й степени: =ИНДЕКС(ЛИНЕЙН($B$2:$B$37;$A$2:$A$37^{1;2;3});1;3)  
Коэффициент 2-й степени: =ИНДЕКС(ЛИНЕЙН($B$2:$B$37;$A$2:$A$37^{1;2;3});1;2)  
Коэффициент 3-й степени: =ИНДЕКС(ЛИНЕЙН($B$2:$B$37;$A$2:$A$37^{1;2;3});1;1)
 
 
 
#3
07.03.2010 12:34:32
А как будут выглядеть формулы для полинома второй степени?
 
 
 
#4
07.03.2010 16:53:40
Для полинома 2-й степени:  
Свободный член уравнения: =ИНДЕКС(ЛИНЕЙН($B$2:$B$37;$A$2:$A$37^{1;2});1;3)  
Коэффициент 1-й степени: =ИНДЕКС(ЛИНЕЙН($B$2:$B$37;$A$2:$A$37^{1;2});1;2)  
Коэффициент 2-й степени: =ИНДЕКС(ЛИНЕЙН($B$2:$B$37;$A$2:$A$37^{1;2});1;1)
 
 
 
#5
07.03.2010 22:48:18
Спаибо, очень помог
 
 
 
#6
14.09.2010 18:48:59
А как сделать тоже самое, но при расположении данных по строчкам, а не по столбцам?
 
 
 
#7
14.09.2010 20:05:49
Транспонировать массивы с помощью ТРАНСП(), поэтому завершать ввод формулы нужно не с помощью Enter, а Ctrl-Shift-Enter, как для формулы массива.  
 
Например, в post_155283.xls для полинома 2-й степени:  
 
Свободный член уравнения: =ИНДЕКС(ЛИНЕЙН(ТРАНСП($I$6:$N$6);ТРАНСП($I$5:$N$5)^{1;2});1;3)  
 
Коэффициент 1-й степени: =ИНДЕКС(ЛИНЕЙН(ТРАНСП($I$6:$N$6);ТРАНСП($I$5:$N$5)^{1;2});1;2)  
 
Коэффициент 2-й степени: =ИНДЕКС(ЛИНЕЙН(ТРАНСП($I$6:$N$6);ТРАНСП($I$5:$N$5)^{1;2});1;1)  
 
Имейте в виду, что при этом коэффициенты  рассчитываются с максимальной точностью в 15 значащих цифр, хоть и отображаются с их меньшим количеством
 
 
 
#8
14.09.2010 20:55:59
Большое спасибо, я уже почти сам догадался про транспонирование, только про Ctrl-Shift-Enter не догадался.  
Теперь следующая задача, а как посчитать коэффициенты для степенной, экспоненциальной и логарифмической функций?    
И есть ли в функции "линейн" синтаксис для расчета коэффициента корреляции ® и коэффициента детерминации(R^2). Как сделать это через коррел я знаю...
 
 
 
#9
14.09.2010 20:57:59
{quote}{login=}{date=14.09.2010 08:55}{thema=}{post}Большое спасибо, я уже почти сам догадался про транспонирование, только про Ctrl-Shift-Enter не догадался.  
{/post}{/quote}Это в справке есть...
 
 
 
#10
14.09.2010 23:05:38
{quote}{login=}{date=14.09.2010 08:55}{thema=}{post}Теперь следующая задача, а как посчитать коэффициенты для степенной, экспоненциальной и логарифмической функций?    
И есть ли в функции "линейн" синтаксис для расчета коэффициента корреляции ® и коэффициента детерминации(R^2). Как сделать это через коррел я знаю...{/post}{/quote}Все, что можно вытащить из функции ЛИНЕЙН, есть во встроенной справке Excel, обратите внимание на последний параметр [статистика].
 
По трендам с нелинейной зависимостью идея заключается в том, чтобы из нелинейной сначала сделать линейную зависимость (прологарифмировать экспоненциальную и т.п.), затем применить встроенную функцию ЛИНЕЙН() и вытащить из полученного массива коэффициенты с учетом сделанного преобразования.  
 
Чтобы закрыть тему приложил файл с расчетами коэффициентов следующих трендов:  
1) Линейного  
2) Логарифмического  
3) Степенного  
4) Экспоненциального  
5) Полиномиального 2-й степени  
6) Полиномиального 3-й степени  
 
Помимо коэффициентов тренда приведена формула и для R^2
 
 
 
#11
14.09.2010 23:45:57
Большое спасибо!
 
 
 
#12
15.09.2010 23:23:25
Сделал по строчкам и по столбцам, может кому-нибудь пригодится...  
Интересную вещь заметил, коэффициент детерминации R^2 через функцию "линейн" и функцию "коррел" для степенной и єкспоненциальной функции различаются. Очевидно, это связано с точностью вычислений, т.к. когда коэффициенты круглые, отклонений не замечено...
 
 
 
#13
08.07.2011 16:08:20
Спасибо большое за формулы! Очень искал, вы мне очень помогли!
 
 
 
#14
09.07.2011 00:46:53
Спасибо и Вам, что написали свой отзыв. Хоть это и не обязательно было с Вашей стороны, но всегда приятно знать, что ответ помог еще кому-то.
 
 
 
#15
04.08.2011 02:23:06
ZVI, скажите, вот нашел я формулу, что разбирает  функцию ЛИНЕЙН на составляющие для так сказать более лучшего восприятия;) Теперь как из значения функции ЛИНЕЙН превратить значение функции полинома 4-го порядка?
 
 
 
#16
27.10.2011 17:13:39
Народ, Вы можете мне объяснить на пальцах (и формулами эксель :) )  
как получается формула линейного тренда (y = -0.6818x + 8.8182) из значений в колонке В (вроде). Ведь если я поменяю значения, то и формула тренда измениться. Где зависимость ? :)    
почему она имеено такая, а не другие значения.  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#17
28.10.2011 15:39:48
а так?  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#18
28.10.2011 16:38:02
{quote}{login=Марчук}{date=28.10.2011 03:39}{thema=}{post}а так?{/post}{/quote}Так не лучше. Сказывается длительное отсутствие на форуме :-)
 
 
 
#19
29.10.2011 06:01:39
{quote}{login=Марчук}{date=27.10.2011 05:13}{thema=}{post}... Где зависимость ? :)    
почему она имеено такая, а не другие значения.{/post}{/quote}  
 
Уравнение прямой линейного тренда: Y = A + B*X    
В справке Excel используются другие буквы: Y = M * X + B , что не меняет сути дела.  
Для получения наилучшей линейной аппроксимации данных прямая,  а по сути – значения A и B (или М и В), удовлетворяет определенному критерию оптимальности. В Excel для расчета трендов используется метод наименьших квадратов, при этом критерием оптимальности является минимум суммы квадратов отклонений рассчитанных (трендовых) значений Ytrend от тех значений Y, на основе которых построена исходная диаграмма.  
При этом, чем ближе значение R^2 к единице, тем  точнее линейная аппроксимация.  
 
Почитайте справку по функции ЛИНЕЙН, там очень подробно все расписано.  
 
Как рассчитать параметры прямой тренда без диаграммы показано на листе Linear в примере из post_155363.zip выше – см. формулы  ячеек E2 и E3
 
 
 
#20
29.10.2011 06:31:00
{quote}{login=Baklanoff}{date=04.08.2011 02:23}{thema=}{post}ZVI, скажите, вот нашел я формулу, что разбирает  функцию ЛИНЕЙН на составляющие для так сказать более лучшего восприятия;) Теперь как из значения функции ЛИНЕЙН превратить значение функции полинома 4-го порядка?{/post}{/quote}  
Не понял Вашего вопроса, но если речь о формулах для определения коэффициентов полиномиального тренда 4-го порядка, то несложно догадаться, как они выглядят, анализируя формулы полиномиальных трендов 2-го и 3-го порядка.  
Приложил вариант и для полинома 4-го порядка.
 
 
 
#21
23.12.2011 12:32:25
кто подскажет:  
Excel хорошо построил график тренда (полином 5ой степени), но уравнение, которое он предложил,  
а так же уравнение, составленное с помощью функций ЛИНЕЙН(как разъяснил ZVI) не  
дают построить такой график - в начале графика разница в 2 раза, в конце - 100раз!  
не пойму в чем косяк?
 
 
 
#22
02.02.2012 16:40:44
Коэффициенты посчитаны правильно, но в формулу надо было забивать все через "+".  
 
В столбце "Y excel предложил формулу полинома" - вообще какие-то не понятные числа.  
 
Также при построении таких диаграмм надо использовать тип "точеная".
 
 
 
#23
28.02.2012 19:44:05
ZVI, спасибо. В вопросах реализации с помощью формул разобрался (пришлось вспоминать решение матриц методом Гаусса, чтобы понять "как это работает").    
 
Теперь интересует как получить аппроксимацию 4-го порядка для ряда Z=C1:C10 если известно X=A1:A10 и Y=B1:B10 при помощи VBA. Т.е. без расчета самих коэффициентов, только результат.  
На просторах форума нашел пример квадратичной аппроксимации http://www.planetaexcel.ru/forum.php?thread_id=27955, но едва ли смогу приобщить код для 4-го порядка. Возможно у кого-то есть более подходящий вариант.
 
 
 
Страницы: 1
Наверх