Алгоритм решения задачи

 

1. Определим систему нормальных уравнений для нахождения оценок параметров линейной регрессии:

.

.

1.1. В целях удобства расчетов представим таблицу исходных данных следующим образом (рис. 2), которую дополним еще двумя расчетными столбцами: и .

Рис. 2

1.2. В верхнюю ячейку столбца 4 введем формулу и автоматически заполним весь столбец (путем протягивания ячейки с формулой на область заполнения).

=СТЕПЕНЬ(«верхняя ячейка столбца x»;2)

Вызов функции: MS Excel – Вставка – Функция… – Математические

1.3. В верхнюю ячейку столбца 5 введем формулу и автоматически заполним весь столбец (путем протягивания ячейки с формулой на область заполнения).

=«верхняя ячейка столбца x»*«верхняя ячейка столбца y»

1.4. Просуммируем значения столбцов: , , , с помощью функции СУММ, а результат суммирования запишем под столбцом с соответствующими данными (рис. 3).

Получаем систему нормальных уравнений для линейной регрессии:

Рис. 3

Замечание. Данную систему нормальных уравнений можно решать и методом Крамера, и матричным методом. Однако мы будем использовать для ее решения надстройку MS Excel Поиск решения….

2. Решаем систему нормальных уравнений для линейной регрессии.

2.1. Составим исходную табличную модель для решения системы линейных алгебраических уравнений с помощью надстройки Поиск решения... (рис. 4).

Рис. 4

2.2. В блок «Переменные» в первую строку записываем переменные системы алгебраических уравнений.

2.3. В блок «Переменные» во вторую строку записываем произвольные числовые значения (удобнее в качестве числовых значений поставить номера переменных), затем, после выполнения команды Поиск решения..., в этих ячейках получим исходные решения системы.

2.4. В блок «Матрица коэффициентов исходной системы» записываем соответствующую матрицу коэффициентов при переменных , .

2.5. В блок «Значения левых частей уравнений» в верхнюю ячейку вводим формулу:

=СУММПРОИЗВ(«фиксированный диапазон строки значений переменных , »;«диапазон первой строки матрицы коэффициентов исходной системы»)

2.6. Автоматически заполняем весь столбец «Значения левых частей уравнений».

2.7. В блок «Свободные члены исходной системы» в столбец записываем значения правой части исходной системы.

2.8. Вызываем Поиск решения и заполняем форму:

Вызов Поиск решения...: MS Excel – Данные – Поиск решения…

 

Замечание. Если в меню Данные нет команды Поиск решения…, значит, надстройка не подключена. Подключение выполняется в окне Меню - Параметры Excel - Надстройки установкой флажка перед опцией Поиск решения.

 

Установить целевую ячейку – ничего не ставить;

Равной – максимальному значению;

Изменяя ячейки – диапазон строки значений переменных;

Ограничения – диапазон «Значения левых частей уравнений» = диапазон «Свободные члены исходной системы»;

2.8.1. Заполнить форму Результаты поиска решений:

поставить опцию Сохранить найденное решение;

нажать ОК.

Результат выполнения команды Поиск решения… будет следующий (рис. 5)

Рис. 5

2.9. Изменить формат ячеек с полученным решением (строка значений переменных) так, чтобы было три знака после запятой.

3. Записываем уравнение линейной регрессии.

Уравнение линейной регрессии:

Задачи по вариантам

5.1. Исходные данные товарооборота между Россией и Японией за 5 лет (млрд долларов)

5.2. Исходные данные товарооборота между Россией и Японией за 5 лет (млрд дол.)

5.3. Некоторые исходные показатели экономического развития КНР (Источник: МЭ и международные отношения, – 2002. – № 8. – С. 65).

5.4. Некоторые исходные показатели экономического развития КНР (Источник: МЭ и международные отношения. – 2002. – № 8. – С. 65).

5.5. Исходные данные заработной платы от производительности труда.

5.6. Исходные данные товарооборота России и Японии в 1991 – 1995 гг. (Источник: Внешняя торговля. – 1995. – №2-3. – С. 21).

5.7. Исходные данные товарооборота России и Японии в 1991 – 1995 гг. (Источник: Внешняя торговля. – 1995. – №2-3. – С. 21).

5.8. Исходные данные притока прямых иностранных инвестиций в КНР (выбрать любую из пар зависимого и независимого показателей) (Источник: МЭ и международные отношения. – 2002. – № 8. – С. 66).

5.9. Исходные данные макроэкономических итогов политики за 6 лет (выбрать любую из пар зависимого и независимого показателей) (Источник: МЭ и международные отношения. – 2006. – № 2).

5.10. Исходные данные распределения добавленной стоимости по отраслям экономики в ЕС в 2002 г. (выбрать любую из пар зависимого и независимого показателей).