Для исследования статистической зависимости одного результирующего признака от двух и более факторных признаков в Excel есть две возможности: инструмент Регрессия для случая линейной статистической зависимости и непосредственное применение метода наименьших квадратов в случае зависимости любого вида.
Алгоритм применения инструмента Регрессия отличается от описанного выше для случая двумерной модели только количеством исходных данных, размещаемых на рабочем листе и соответственно диапазоном входных параметров, вводимом в диалоговом окне Регрессия. Выходные данные также отличаются только количеством информации при сохранении их смысла.
| Регрессионная статистика | ||||||
| Множественный R | 0,762322 | |||||
| R-квадрат | 0,581135 | |||||
| Нормированный R-квадрат | 0,563682 | |||||
| Стандартная ошибка | 50,23613 | |||||
| Наблюдения | ||||||
| Дисперсионный анализ | ||||||
| df | SS | MS | F | Значимость F | ||
| Регрессия | 168064,8 | 84032,39 | 33,2977 | 8,51E-10 | ||
| Остаток | 121136,1 | 2523,668 | ||||
| Итого | 289200,9 | |||||
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | |
| Y-пересечение | 225,7848 | 27,41026 | 8,237239 | 9,67E-11 | 170,6728 | 280,8968 |
| X8 | 23,38168 | 10,96783 | 2,131842 | 0,038166 | 1,329382 | 45,43398 |
| X4 | -503,93 | 69,72031 | -7,22788 | 3,29E-09 | -644,112 | -363,748 |
Рис.8. Регрессия Y2 на X4,X8.
На рис.8 приведены результаты применения инструмента Регрессия к статистическим данным по признакам X4–X8–Y2.
Оценка линейной функции регрессии y2 на x4,x8 имеет вид:
Значение F–критерия Fрасч =33,2977, что значительно больше Fкр = 3,18 Это означает, что оценка достаточно хорошо согласуется с данными наблюдений. Это подтверждается и достаточно высоким значением коэффициента детерминации R2 = 0,5811351. Расчетные значения t –статистики для свободного члена и коэффициента при x4 больше tкр = 2,009, что подтверждает их значимость. Для коэффициента при x8 tрасч близко к критическому значению, что ставит под сомнение его значимость.
| A | B | C | D | E | F | H | I | |
| X4 | X8 | Y2 | P(x) | ε | ε2 | P2 (x) | ε22 | |
| 0,42 | 0,66 | 13,6 | =A$56+B$56*A2+C$56* B2 | =C2-D2 | =E2^2 | =A$59+B$59*A2+C$59*B2+D$59*A2^2+E$59*B2^2+F$59*A2*B2 | =(C2-H2)^2 | |
| 0,51 | 1,23 | =A$56+B$56*A3+C$56* B3 | =C3-D3 | =E3^2 | =A$59+B$59*A3+C$59*B3+D$59*A3^2+E$59*B3^2+F$59*A3*B3 | =(C3-H3)^2 | ||
| 0,38 | 1,04 | 18,1 | =A$56+B$56*A4+C$56* B4 | =C4-D4 | =E4^2 | =A$59+B$59*A4+C$59*B4+D$59*A4^2+E$59*B4^2+F$59*A4*B4 | =(C4-H4)^2 | |
| 0,51 | 0,24 | 21,9 | =A$56+B$56*A5+C$56* B5 | =C5-D5 | =E5^2 | =A$59+B$59*A5+C$59*B5+D$59*A5^2+E$59*B5^2+F$59*A5*B5 | =(C5-H5)^2 | |
| 0,43 | 2,13 | 26,8 | =A$56+B$56*A6+C$56* B6 | =C6-D6 | =E6^2 | =A$59+B$59*A6+C$59*B6+D$59*A6^2+E$59*B6^2+F$59*A6*B6 | =(C6-H6)^2 | |
| 0,43 | 0,84 | 30,1 | =A$56+B$56*A7+C$56* B7 | =C7-D7 | =E7^2 | =A$59+B$59*A7+C$59*B7+D$59*A7^2+E$59*B7^2+F$59*A7*B7 | =(C7-H7)^2 | |
| 0,34 | 0,68 | 32,3 | =A$56+B$56*A8+C$56* B8 | =C8-D8 | =E8^2 | =A$59+B$59*A8+C$59*B8+D$59*A8^2+E$59*B8^2+F$59*A8*B8 | =(C8-H8)^2 | |
| 0,18 | 1,06 | 34,2 | =A$56+B$56*A9+C$56* B9 | =C9-D9 | =E9^2 | =A$59+B$59*A9+C$59*B9+D$59*A9^2+E$59*B9^2+F$59*A9*B9 | =(C9-H9)^2 |
Рис.9. Размещение информации для МНК.
В случае нелинейной регрессии специального инструмента в Excel нет, необходимо выполнять действия, предусмотренные методом наименьших квадратов(МНК), используя вычислительные возможности Excel. Расположение исходных данных и формул в таблице Excel приведено на рис.9.
Все формулы вводятся только в верхнюю строку, а затем копируются по всему столбцу. На рис.9 приведены расчеты поиска оценок линейной P(x) и квадратичной P2 (x) функции регрессии. Параметры функции регрессии βj расположены в ячейках A56 ч C56 для линейной зависимости и в ячейках A59 ч F59 для квадратичной зависимости (см. рис.10). Ячейки F53 и I53 содержат значения функций Q – суммы квадратов отклонений.
| A | B | C | D | E | F | H | I | |
| 0,02 | 1,14 | 264,8 | =A$56+ B$56*A50+ C$56*B50 | =C50-D50 | =E50^2 | =A$59+B$59*A50+ C$59*B50+D$59*A50^2+E$59*B50^2+F$59*A50*B50 | =(C50-H50)^2 | |
| 0,16 | 4,44 | 267,3 | =A$56+ B$56*A51+ C$56*B51 | =C51-D51 | =E51^2 | =A$59+B$59*A51+ C$59*B51+D$59*A51^2+E$59*B51^2+F$59*A51*B51 | =(C51-H51)^2 | |
| 0,01 | 1,27 | 355,6 | =A$56+ B$56*A52+ C$56*B52 | =C52-D52 | =E52^2 | =A$59+B$59*A52+ C$59*B52+D$59*A52^2+E$59*B52^2+F$59*A52*B52 | =(C52-H52)^2 | |
| Q = | =СУММ(F2: F52) | Q2 = | =СУММ(I2: I52) | |||||
| σ = | =КОРЕНЬ(F53/51) | σ2 = | =КОРЕНЬ(I53/51) | |||||
| β0 | β1 | β2 | ||||||
| 225,78481426 | -503, | 23,381653963 | ||||||
| β0 | β1 | β2 | β3 | β4 | β5 | |||
| 247,96413983 | -930, | 73,537978008 | 1009,39006400157 | -4,446 | -140,188 |
Рис.10. Размещение информации для Поиска решения.
Значения βj находятся с помощью надстройки Excel Поиск решения по такому алгоритму:
– установить курсор на ячейке, содержащей значение функции Q (Q2);
– Сервис – Поиск решения;
– в появившемся диалоговом окне Поиск решения (рис.11) проверить, стоит ли в поле Установить целевую ячейку адрес функции Q (Q2), и если нет, то ввести его;
– в поле Равной щелкнуть пункт минимальному значению;
– в поле Изменяя ячейки ввести диапазон ячеек, которые отведены для значений искомых параметров 
;
– щелкнуть по кнопке Выполнить;
– если решение найдено, сообщение об этом появится в диалоговом окне, где нужно щелкнуть по пункту Сохранить найденное решение. Значения 
найдены и находятся в отведенных для них ячейках (рис.10).
– Значение суммы квадратов отклонений найденной оценки функции регрессии от наблюденных значений результирующего признака, т.е. функции Q для линейной регрессии и функции Q2 для квадратичной регрессии, находятся в ячейках F53 и I53, линейная величина отклонений – в ячейке F54 и в ячейке I54.
Рис.11. Ввод информации для Поиска решения.
Таким образом, коэффициенты линейной функции регрессии P(x) следует считывать из ячеек A56,B56 и С56; коэффициенты нелинейной функции регрессии P2(x) – из ячеек A59 
F59. Для рассматриваемого примера линейная функция регрессии совпадает с полученной с помощью инструмента Регрессия, а квадратичная
P2(x) = 247,9641 – 930,3571x4 + 73,538x8 + 1009,39x42 – 4,44689x82 – 140,1884x4x8
Проверка значимости полученной квадратичной оценки уравнения регрессии выполним так. Определим коэффициент корреляции значений эмпирической функции регрессии и выборочного среднего RyP2(x). Как видно из рис.12, коэффициент корреляции достаточно большой (0,80921). Выполним еще одну проверку значимости P2(x) с помощью коэффициента детерминации, для чего необходимо вычислить значения Sост, Sфакт.
Размещение нужных формул приведено на рис.12, а промежуточные результаты и значения коэффициента детерминации ниже. Поскольку коэффициент детерминации для случая квадратичной регрессии значительно превосходит коэффициент детерминации для случая линейной регрессии и имеет достаточно большое значение (0,472867), делаем вывод, что квадратичная регрессия достаточно хорошо согласуется со статистическими данными.
Выполним оценку значимости полученного приближения функции в целом с помощью критерия Фишера. Для этого найдем значения критерия Фишера по выборке для рассматриваемых двух видов зависимости (см. рис.12 и 13).
| R | S | |||
| RyP(x) | RyP2(x) | |||
| =КОРРЕЛ(C2:C52;D2:D52) | =КОРРЕЛ(C2:C52;H2:H52) | |||
|
| |||
| =F53/48 | =I53/45 | |||
|
| |||
| =L53/48 | =N53/45 | |||
| R2 | R22 | |||
| =1-R5/ (R9 + R5) | =1-S5/ (S9 + S5) | |||
| Fрасч | F2расч | |||
| =R11*(51-2-1)/(1-R11)/2 | =S11*(51-2-1)/(1-S11)/2 | |||
| Fкрит = | 3,205 |
Рис.12.Расчетные формулы
Как видно, расчетное значение F-критерия для квадратичной зависимости значительно превосходит значение Fкрит ,что подтверждает ее значимость. Для линейной зависимости превышение Fрасч не столь велико, что делает снова-таки предпочтительнее квадратичную оценку регрессии y2 на x4 и x8 .
 K
| L |  M
| N | O | Q | R | S | |||
| ^2 | ^2 | RyP(x) | RyP2(x) | ||||||
| 66,0145 | 4357,91 | 52,4372 | 2749,66 | 0,762322 | 0,80921 | |||||
| 98,0407 | 9611,98 | 63,6085 | 4046,04 |
|
| |||||
| 36,9723 | 1366,95 | 39,0068 | 1521,53 | |||||||
| 121,189 | 14686,7 | 59,1584 | 3499,72 | 2523,668 | 2218,362 | |||||
| 36,6828 | 1345,63 | 52,8333 | 2791,36 |
|
| |||||
| 66,8451 | 4468,27 | 52,8975 | 2798,14 | |||||||
| 25,2325 | 636,678 | 31,6051 | 998,881 | |||||||
| -64,2814 | 4132,09 | -63,8871 | 4081,57 | 3501,349 | 4208,353 | |||||
| 3,56772 | 12,7286 | 14,147 | 200,138 | R2 | R22 | |||||
| 43,0760 | 1855,54 | 43,5092 | 1893,05 | 0,581135 | 0,654822 | |||||
| -12,1715 | 148,144 | 4,46566 | 19,9421 | Fрасч | F2расч | |||||
| 37,1816 | 1382,47 | 39,3711 | 1550,09 | 33,29771 | 45,5293 | |||||
| 68,8203 | 4736,24 | 53,556 | 2868,24 | |||||||
| 37,88307 | 1435,127 | 39,90716 | 1592,582 | Fкрит = | 3,205 |
Рис.13.Проверка значимости.
Таким образом, выборочное уравнение регрессии имеет вид:
