Используйте уравнение регрессии в формуле Excel

3392
a_m0d

У меня есть электронная таблица с несколькими графиками, и я отобразил линии тренда / линии регрессии на этих графиках. Это не простые линии линейной регрессии, а полиномиальные высшие порядки.

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

РЕДАКТИРОВАТЬ: Коэффициенты варьируются, потому что я все еще добавляю данные, и линии регрессии становятся более точными, когда я добавляю больше данных. Данные не будут ограничены - они будут поступать, поэтому я не хочу каждый раз переписывать все формулы.

2
Это должно быть возможно, если функции непрерывны. Вы просто говорите, что коэффициенты степеней в определении функции варьируются? Предположительно они варьируются в зависимости от другой известной функции? Возможно, вы могли бы привести пример? mas 15 лет назад 0

3 ответа на вопрос

1
JDunkerley

Встроенные функции охватывают только коэффициенты для прямой линии.

В прошлом я делал это с помощью матричных функций в Excel и стандартного метода подбора наименьших квадратов.

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

0
Martin

Я не знаю конкретно, но взгляните на объектную модель графического объекта в VBA, чтобы узнать, можете ли вы получить доступ к уравнению регрессии внутри VBA. Затем вы можете поместить коэффициенты обратно в ячейки.

Единственный программный доступ к уравнениям в виде строки. Пример: `? Worksheets (" Sheet1 "). ChartObjects (1) .Chart. SeriesCollection (1) .Trendlines (1) .DataLabel.Text`. Тебе все еще нужно разобрать строку. Есть лучшие методы - см. @JDunkerley DaveParillo 15 лет назад 0
0
Toc

Полиномиальная n-ступенчатая регрессия в одной переменной ~ Линейная регрессия в n переменных

где ~ обозначает «эквивалент».

Итак, в вашей исходной таблице данных добавьте столбцы для расчета степеней «независимых» значений переменных, а затем примените формулу для расчета коэффициентов для линейной регрессии со многими переменными.

Хотите привести простой пример? Не могу понять, о чем ты говоришь. a_m0d 15 лет назад 0
Положите значения y в A1: A10, введите 1s в B1: B10 (коэффициент «перехвата»), укажите значения x в C1: C10, укажите значения x ^ 2 в D1: D10. Затем поместите следующую (матричную) формулу в E1: E3: = MMULT (MMULT (MINVERSE (MMULT (TRANSPOSE (B1: D10), B1: D10)), TRANSPOSE (B1: D10)), A1: A10), как сказано в http://en.wikipedia.org/wiki/Linear_regression Это ваши коэффициенты. Toc 15 лет назад 0

Похожие вопросы