Решение. 1.Дополним табл. 1 столбцами, необходимыми для расчета оценок параметров множественной линейной регрессии

1. Дополним табл. 1 столбцами, необходимыми для расчета оценок параметров множественной линейной регрессии, и строками для расчета сумм и средних значений по столбцам.

Рассчитаем суммы и средние значения по каждому столбцу. Найдём выборочные дисперсии для факторов . Результаты расчетов представлены на рис. 1.

 

Рис. 1. Результаты расчетов сумм, средних значений и дисперсий

Составим определитель системы нормальных уравнений (2) и определители при неизвестных коэффициентах . Вычислим определители, используя функцию МОПРЕД из категории Математические. Найдём коэффициенты уравнения, используя формулы Крамера (3). Полученные значения коэффициентов приведены на рис. 2.

 

Рис. 2. Результаты расчетов коэффициентов уравнения регрессии

Проверим расчеты, используя формулу (4). Для этого составим матрицы и так, как показано в методических указаниях к теме. Вычисление матрицы будем производить поэтапно. Сначала вычислим матрицу , используя встроенную функцию ТРАНСП из категории Ссылки и массивы (для транспонирования матрицы) и МУМНОЖ из категории Математические (для умножения матриц). Выделим диапазон ячеек, в котором будет выведен результат вычислений (для условия примера диапазон имеет размер ). Нажмем клавишу «F2». В первую ячейку для искомой матрицы вызовем функцию МУМНОЖ. В поле Массив 1 введем от руки имя функции ТРАНСП и в скобках укажем диапазон ячеек, содержащий матрицу , в поле Массив 2 введем диапазон ячеек, содержащий матрицу (рис. 3).

Рис. 3. Заполнение полей аргументов функции МУМНОЖ

Еще раз нажмем клавишу «F2» (при этом аргументы функции подсветятся) и затем нажмем одновременно клавиши «Ctrl» + «Shift» + «Enter». Excel поместит рассчитанную матрицу во все выделенные ячейки и автоматически заключит формулы в фигурные скобки, чтобы подчеркнуть, что это формулы массива. (Вручную их вводить нельзя, это будет ошибкой). Результаты расчетов представлены на рис. 4.

Рис. 4. Результаты расчета матрицы Х^ТХ

Для нахождения обратной матрицы используем функцию МОБР из категории Математические. Выделим диапазон ячеек, в котором будет выведен результат вычислений (для условия примера диапазон имеет размер ). Нажмем клавишу «F2». В первую ячейку для искомой матрицы вызовем функцию МОБР. В поле Массив введем диапазон ячеек, содержащий матрицу (рис. 5).

 

Рис. 5. Заполнение полей аргумента функции МОБР

Еще раз нажмем клавишу «F2» (при этом аргумент функции подсветится) и затем нажмем одновременно клавиши «Ctrl» + «Shift» + «Enter». В результате получим обратную матрицу (Х^ТХ)^-1:

Рис. 6. Результаты расчета матрицы (Х^ТХ)^-1

Аналогично вычислению матрицы найдем матрицу (она будет иметь размер ):

 

Рис. 7. Результаты расчета матрицы

Перемножив матрицы и , получим матрицу . Результаты расчетов коэффициентов совпадают.

Вывод: модель зависимости выработки продукции на одного работника от ввода в действие новых основных фондов и удельного веса рабочих высокой квалификации имеет вид:. При увеличении ввода в действие новых основных фондов (при неизменном) на 1% выработка продукции повысится в среднем на 0, 9459 млн. руб., а при увеличении удельного веса рабочих высокой квалификации (при неизменном) на 1% выработка повысится в среднем на 0, 856 млн. руб.

2. Рассчитаем средние коэффициенты эластичности по формуле (5) (рис. 8).

Рис. 8. Результаты расчета средних коэффициентов эластичности

Вывод: при увеличении ввода в действие новых основных фондов (при неизменном) на 1% выработка продукции повысится в среднем на 0, 61%, а при увеличении удельного веса рабочих высокой квалификации (при неизменном) на 1% выработка повысится в среднем на 0, 20%. Таким образом, подтверждается бó льшее влияние на результат фактора, чем фактора.

3. Определим коэффициент множественной детерминации. Для этого дополним табл. 1 столбцами для расчета значений и квадратов отклонений . Заполним столбцы (рис. 9).

 

Рис. 9. Результаты расчета и квадратов отклонений

Вычислим коэффициент множественной детерминации R ² по формуле (6), коэффициент множественной корреляции R = и скорректированный коэффициент множественной детерминации Ř ² по формуле (8). Результаты вычислений приведены на рис. 10.

 

Рис. 10. Результаты расчета коэффициентов

Вывод: значение R ² = 0, 9469 указывает на весьма высокую степень обусловленности изменения результата изменением факторов.

Значение R = 0, 973 указывает на тесную связь между результативным и факторными признаками.

Значение Ř ² = 0, 9407 также указывает на высокую детерминированность результата y в модели факторами и .

Для проверки значимости уравнения в целом найдем фактическое значение критерия Фишера по формуле (9).

Для нахождения критического значения распределения Фишера используем Статистическую функцию FРАСПОБР, заполнение полей которой показано на рис. 11.

 

Рис. 11. Заполнение полей аргументов функции FРАСПОБР

Для сравнения фактического и критического значений используем Логическую функцию ЕСЛИ, заполнение полей которой приведено на рис. 12.

 

Рис. 12. Заполнение полей аргументов функции ЕСЛИ

Результаты расчетов приведены на рис. 13.

 

Рис. 13. Результаты проверки значимости уравнения