Квадратичная и кубическая регрессия в Excel

У меня есть следующая информация:

  Height    Weight

    170     65
    167     55
    189     85
    175     70
    166     55
    174     55
    169     69
    170     58
    184     84
    161     56
    170     75
    182     68
    167     51
    187     85
    178     62
    173     60
    172     68
    178     55
    175     65
    176     70

Я хочу построить квадратичный и кубический регрессионный анализ в Excel. Я знаю, как это сделать с помощью линейной регрессии в Excel, но как насчет квадратичной и кубической? Я искал много ресурсов, но не нашел ничего полезного.

Ответ 1

Вам нужно использовать недокументированный трюк с помощью функции Excel LINEST:

=LINEST(known_y's, [known_x's], [const], [stats])

Фон

Вычисляется регулярная линейная регрессия (с вашими данными) как:

=LINEST(B2:B21,A2:A21)

который возвращает одно значение, линейный наклон (m) в соответствии с формулой:

который для ваших данных:

является:

Недокументированный трюк Номер 1

Вы также можете использовать Excel для расчета регрессии с формулой, которая использует показатель степени x, отличный от 1, например. х ^1.2

используя формулу:

=LINEST(B2:B21, A2:A21^1.2)

который для вас:

является:

Вы не ограничены одним показателем

Функция Excel LINEST также может вычислять несколько регрессий с разными показателями на x в то же время, например:

=LINEST(B2:B21,A2:A21^{1,2})

Примечание:, если locale установлен на европейский (десятичный символ "," ), тогда запятая должна быть заменена точкой с запятой и обратной косой чертой, т.е. =LINEST(B2:B21;A2:A21^{1\2})

Теперь Excel будет вычислять регрессии с использованием как x ¹ так и x ² в то же время:

Как это сделать

Невозможно сложная часть нет очевидного способа увидеть другие значения регрессии. Для этого вам необходимо:

выберите ячейку, содержащую формулу:
расширяем выделение в левом 2 пробелах (вам нужно, чтобы выбор имел ширину не менее 3 ячеек):
нажмите F2
нажмите Ctrl + Shift + Enter

Теперь вы увидите свои 3 регрессионные константы:

  y = -0.01777539x^2 + 6.864151123x + -591.3531443

Бонус-чат

У меня была функция, которую я хотел выполнить регрессию с использованием некоторого показателя:

y = m × x ^k + b

Но я не знал показатель экспоненты. Поэтому я изменил функцию LINEST, чтобы вместо этого использовать ссылку на ячейку:

=LINEST(B2:B21,A2:A21^F3, true, true)

С помощью Excel вы получите полную статистику (4-й параметр до LINEST):

я скажу Solver, чтобы максимизировать R ²:

<Т411 >

И он может определить лучший показатель. Какие данные для вас:

является:

Ответ 2

Я знаю, что этот вопрос немного стар, но я подумал, что я бы предложил альтернативу, которая, на мой взгляд, могла бы быть немного легче. Если вы хотите добавить "временные" столбцы в набор данных, вы можете использовать Excel Analysis ToolPak → Data Analysis → Regression. Секрет выполнения квадратичного или кубического регрессионного анализа определяет диапазон входных данных:.

Если вы делаете простую линейную регрессию, вам нужно всего 2 столбца, X и Y. Если вы выполняете квадратичную форму, вам понадобятся X_1, X_2 и Y, где X_1 - это x, а X_2 - x ^ 2; Аналогично, если вы делаете кубик, вам понадобятся X_1, X_2, X_3 и Y, где X_1 является переменной x, X_2 - x ^ 2 и X_3 x ^ 3. Обратите внимание, что Диапазон ввода X от A1 до B22, охватывающий 2 столбца.

Следующее изображение выводит регрессионный анализ. Я выделил общие выходы, включая значения R-Squared и все коэффициенты.

Ответ 3

Функция LINEST, описанная в предыдущем ответе, - это путь, но более простой способ показать 3 коэффициента вывода - дополнительно использовать функцию INDEX. В одной ячейке введите: = INDEX (LINEST (B2: B21, A2: A21 ^ {1,2}, TRUE, FALSE), 1) (кстати, B2: B21 и A2: A21 я использовал только те же значения, что и первый плакат, который ответил на это,... конечно, вы соответствующим образом изменили бы эти диапазоны, чтобы соответствовать вашим данным). Это дает коэффициент Х ^ 2. В соседней ячейке снова введите ту же формулу, но измените окончательный 1 на a 2... это дает коэффициент X ^ 1. Наконец, в следующей ячейке снова введите ту же формулу, но измените последнее число на 3... это дает константу. Я заметил, что эти три коэффициента очень близки, но не совсем идентичны тем, которые получены с использованием графической функции тренда на вкладке диаграмм. Кроме того, я обнаружил, что LINEST работает только, если данные X и Y находятся в столбцах (а не в строках), без пустых ячеек в пределах диапазона, поэтому имейте в виду, что если вы получаете ошибку #VALUE.