Полиномиальная регрессия 4-го и 5-го порядка не работает в Excel

У меня странная проблема с выполнением полиномиальной регрессии в Excel. Как и многие раньше, я пытаюсь получить правильные коэффициенты, которые использует Excel, когда создает полиномиальную линию тренда на графике. Я читал, как это сделать с помощью ЛИНЕЙН, и мне удалось получить ответы, соответствующие формуле линии тренда, при выполнении регрессии 2-го и 3-го порядка... но когда я пробую 4-й или 5-й, ответы сильно отличаются от того, что Excel показывает мне формулу линии тренда.

Вот данные с диаграммой и моя попытка регрессии 5-го порядка:Нажмите, чтобы открыть книгу Excel

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

Спасибо!

-Джон


excel regression polynomials trendline
person Vadaar    schedule 09.04.2015    source источник
comment
Просто некоторые дополнительные детали, которые я только что заметил: когда я выполняю регрессию 4-го или 5-го порядка, 4-й и 5-й коэффициенты равны нулю. Если я на самом деле запускаю формулу, прогноз будет таким же, как регрессия 3-го порядка (даже если отдельные коэффициенты разные).   -  person Vadaar    schedule 09.04.2015
comment
Кажется, проблема с вашим Excel. Если я скачаю ваш файл и открою его, то результатом LINEST будет -1.06569E-14 1.0973E-09 -3.01287E-05 0 0 210463020.6. Теперь я изменяю значение в B2 на 34 и обратно на 35. Теперь лист вычисляет и показывает: 1.52874E-09 -0.000314866 25.94049994 -1068559.361 22008335073 -1.81315E+14что почти точно соответствует тому, что показывает диаграмма.   -  person Axel Richter    schedule 09.04.2015
comment
Конечно, поскольку вы используете даты как значения x (наименьшее значение — 40980), x^5 — действительно большое число. Там будут затронуты ограничения точности Excel.   -  person Axel Richter    schedule 09.04.2015
comment
Если проблемой являются большие числа (например, даты), вы можете использовать функцию STANDARDIZE для нормализации значений перед регрессией.   -  person Byron Wall    schedule 09.04.2015
comment
Спасибо всем - я понял это примерно в то же время, когда все ответили! Я пробовал это и в R, и у меня была та же проблема, так что это не похоже на Excel. Может проблема в адресации памяти? Я оставлю это кому-то другому, чтобы определить.   -  person Vadaar    schedule 09.04.2015
comment
Как говорит @AxelRichter, проблема заключается в использовании сравнительно больших серийных чисел даты для значений X в сочетании с форматом чисел с двойной плавающей запятой ограниченной точности, который Excel использует внутри. См. ниже формулу для обхода этого ограничения.   -  person lori_m    schedule 18.04.2015

Ответы (2)


arrow_upward
2
arrow_downward

Нулевые значения на выходе являются следствием (мульти)коллинеарности. Из справки по функциям MS:

«...Функция ЛИНЕЙН проверяет коллинеарность и удаляет любые избыточные столбцы X из модели регрессии, когда она их идентифицирует. Удаленные столбцы X могут быть распознаны в выходных данных ЛИНЕЙН как имеющие 0 коэффициентов в дополнение к 0 значениям se...."

Для более точных оценок примените ЛИНЕЙН со среднецентрированными значениями x и пост-умножением на матрицу биномиальных коэффициентов. Итак, вместо:

=LINEST(B2:B31,A2:A31^{1,2,3,4,5})

попробуйте вместо этого:

=MMULT(LINEST(B2:B31,(A2:A31-AVERAGE(A2:A31))^{1,2,3,4,5}),IFERROR(COMBIN({5;4;3;2;1;0},{5,4,3,2,1,0})*(-AVERAGE(A2:A31))^({5;4;3;2;1;0}-{5,4,3,2,1,0}),0))

которые согласуются со значениями линии тренда.

См. также: https://newtonexcelbach.wordpress.com/2011/02/04/fitting-high-order-polynomials/ (сообщение и комментарии)

person lori_m    schedule 18.04.2015
comment
Удивительно, но это отлично работает для меня. Большое спасибо. - person Nova; 24.10.2016

arrow_upward
0
arrow_downward

Вы можете переобуваться; на самом деле, я бы сказал, что да, если вы используете полином 5-го порядка. С полиномами выше 3-го порядка (иногда даже выше 1-го порядка!) вы рискуете тем, что ваша «линия тренда» вылетит в космос в конечных точках. Когда вы преодолеваете определенный уровень сложности, вы подгоняете модель к случайным вариациям точек данных, а не к базовому механизму, сгенерировавшему данные.

Один из способов избежать этой проблемы — использовать механизм регуляризации, такой как гребневая регрессия. В книге Кристофера Бишопа Распознавание образов и машинное обучение есть отличное обсуждение этой темы и хороший пример того, как упорядочить полиномиальную регрессию.

Кстати, я бы не стал использовать Excel для статистического анализа. Вот статья о статистических возможностях Excel из журнала Вычислительная статистика и анализ данных (впервые я увидел ссылку на нее в другом посте stackoverflow; но я не помню, где; извиняюсь перед автором статьи за то, что не с указанием соответствующей ссылки): http://www.pages.drexel.edu/~bdm25/excel2007.pdf

person Taiko    schedule 03.06.2015
comment
Это действительно выглядит весьма полезным, но это должен был быть комментарий, а не ответ, поскольку он касается только того, следует ли пытаться сделать это в первую очередь, не решая насущной проблемы. Имея немного больше представителей, вы сможете публиковать комментарии. - person Nathan Tuggy; 03.06.2015