Задание 5

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

1. Методом наименьших квадратов по табличным данным найти аппроксимирующие (приближаемые) функции, то есть регрессии: линейную, квадратичную, показательную, гиперболическую.

2. В каждом случае найти общую ошибку и среднюю ошибку аппроксимации. Указать функцию лучшей аппроксимации.

3. Построить линии регрессии на одной плоскости вместе с исходными данными.

Таблицу можно считать функцией, заданной таблично.

Решение.

1. Методом наименьших квадратов по табличным данным найдем аппроксимирующие (приближаемые) функции, то есть регрессии:

а) линейную, б) квадратичную, в) показательную, г) гиперболическую.

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

.

Для удобства расчетов представим таблицу исходных данных следующим образом:

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

Решим данную систему, используя надстройку MS Excel Поиск решения.

Составим исходную табличную модель для решения системы линейных алгебраических уравнений.

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

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

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

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

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

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

Запишем уравнение полученной линейной регрессии:

.

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

- параболы второй степени.

Составим вспомогательную таблицу и произведем расчеты, необходимые для составления системы нормальных уравнений.

На основании полученных результатов расчета коэффициентов система нормальных уравнений примет вид:

Решим данную систему, используя надстройку MS Excel Поиск решения.

Составим исходную табличную модель для решения системы линейных алгебраических уравнений.

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

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

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

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

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

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

Запишем уравнение полученной квадратичной регрессии:

.

в) показательная регрессия сводится к нахождению уравнения вида:

Логарифмирование данного уравнения по основанию е приводит к линейному уравнению вида: .

Соответственно оценки параметров могут быть найдены методом наименьших квадратов. Для упрощения и наглядности решения произведем следующие замены переменных: .

Тогда уравнение в новых переменных будет иметь вид:

.

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

Составим таблицу для расчетов необходимых значений:

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

Решим данную систему, используя надстройку MS Excel Поиск решения.

Составим исходную табличную модель для решения системы линейных алгебраических уравнений.

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

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

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

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

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

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

Уравнение регрессии в линейной форме будет иметь вид:

.

Выполнив его потенцирование, вернемся к показательной регрессии:

.

г) гиперболическая регрессия сводится к нахождению уравнения вида:

Для оценки параметров заменим , тогда уравнение будет иметь вид:

.

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

Для расчетов используем данные таблицы:

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

Решим данную систему, используя надстройку MS Excel Поиск решения.

Составим исходную табличную модель для решения системы линейных алгебраических уравнений.

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

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

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

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

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

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

Уравнение регрессии в линейной форме будет иметь вид:

.

Вернемся к гиперболической регрессии:

.

2. Найдем общую ошибку и среднюю ошибку аппроксимации, а затем укажем функцию лучшей аппроксимации.

Итак, получены следующие регрессии:

а) линейная - ,

б) квадратичная - ,

в) показательная - ,

г) гиперболическая - .

Составим вспомогательную таблицу для расчетов необходимых значений.

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

Далее рассчитаем «Квадрат отклонения у-линейное», «Квадрат отклонения у-квадратичное», «Квадрат отклонения у-показательное» и «Квадрат отклонения у-гиперболическое» по соответствующим формулам для каждого х:

=СТЕПЕНЬ((«ячейка столбца y»-«ячейка столбца «у-линейное»);2)

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

=СТЕПЕНЬ((«ячейка столбца y»-«ячейка столбца «у-показательное»);2)

=СТЕПЕНЬ((«ячейка столбца y»-«ячейка столбца «у-гиперболическое»);2)

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

На следующем этапе вычислим средние ошибки аппроксимации.

Для этого рассчитаем значения столбцов «Аппроксимация у-линейное», «Аппроксимация у-квадратичное», «Аппроксимация у-показательное», «Аппроксимация у-гиперболическое» по следующим формулам:

=ABS((«ячейка столбца y»-«ячейка столбца «у-линейное»)/«ячейка столбца y»)

=ABS((«ячейка столбца y»-«ячейка столбца «у-квадратичное»)/«ячейка столбца y»)

=ABS((«ячейка столбца y»-«ячейка столбца «у-показательное»)/«ячейка столбца y»)

=ABS((«ячейка столбца y»-«ячейка столбца «у-гиперболическое»)/«ячейка столбца y»)

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

В ячейках под столбцами «Аппроксимация у-линейное», «Аппроксимация у-квадратичное», «Аппроксимация у-показательное», «Аппроксимация у-гиперболическое» введем формулы и придадим этим ячейкам формат Процентный:

=1/6*СУММ(«диапазон значений ячеек соответствующего столбца»)*100%

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

Таким образом, средние ошибки аппроксимации линейной, квадратичной, показательной, гиперболической регрессий соответственно равны 27,1222%, 28,3994%, 23,774%, 26,6741%.

Далее укажем функцию наилучшей аппроксимации по общей ошибке и по средней ошибке аппроксимации, используя функции ЕСЛИ и МИН.

Для определения регрессии с минимальной общей ошибкой введем формулу:

=ЕСЛИ(«ячейка суммы столбца «у-линейное»=МИН(«диапазон ячеек сумм регрессий»); «у-линейное»; ЕСЛИ(«ячейка суммы столбца «у-квадратичное»= МИН(«диапазон ячеек сумм регрессий»); «у-квадратичное»; ЕСЛИ(«ячейка суммы столбца «у-показательное»=МИН(«диапазон ячеек сумм регрессий»); «у-показательное»; «у-гиперболическое»)))

Для определения регрессии с минимальной средней ошибкой аппроксимации введем формулу:

=ЕСЛИ(«ячейка со значением средней ошибки аппроксимации «у-линейное»= МИН(«диапазон ячеек со значениями всех средних ошибок аппроксимации»); «у-линейное»; ЕСЛИ(«ячейка со значением средней ошибки аппроксимации «у-квадратичное»=МИН(«диапазон ячеек со значениями всех средних ошибок аппроксимации»); «у-квадратичное»; ЕСЛИ(«ячейка со значением средней ошибки аппроксимации «у-показательное»=МИН(«диапазон ячеек со значениями всех средних ошибок аппроксимации»); «у-показательное»; «у-гиперболическое»))).

Таким образом, минимальная общая ошибка и минимальная средняя ошибка аппроксимации у показательной функции.

Следовательно, показательная функция – функция наилучшей аппроксимации.

3. Построим полученные линии регрессии на одной плоскости вместе с исходными данными.

Заметим, что поскольку в предыдущей таблице значения аргумента функции не упорядочены по возрастанию, то мастер диаграмм не отразит правильно полученные регрессии. Для упорядочивания данных столбца необходимо отсортировать его по возрастанию, предварительно выделив все столбцы.