Задание 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. Построим полученные линии регрессии на одной плоскости вместе с исходными данными.

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

Date: 2015-09-25; view: 854; Нарушение авторских прав