Практическая работа № 2 Интерполяция в среде excel, Линия тренда. Задача №1
Цель работы– получить аналитическую зависимость функции от аргумента (задано таблично) в средеExcel.
В Excelесть ряд встроенных утилит, которые можно использовать для решения задач по интерполяции и по аппроксимации зависимостей.
Анализ возможностей Excelв данной области начнем с графических утилит. В частности, исследуем вопрос о добавлении линии тренда, которая строится на основе экспериментальных данных и является аппроксимирующей или интерполяционной функцией, в зависимости от выбора типа кривой. Последовательность выполнения:
1. Отображение анализируемых данных в графическом виде.
2. Построение кривой для рассматриваемой зависимости.
3. Анализ полученной кривой для рассматриваемой зависимости.
МНК: Приближение полиномом в EXCEL. Примеры и описание
Интерполя́ция , интерполи́рование (от лат. inter–polis — «разглаженный, подновлённый, обновлённый; преобразованный ») — в вычислительной математике способ нахождения промежуточных значений величины по имеющемуся дискретному набору известных значений. Термин «интерполяция» впервые употребил Джон Валлис в своём трактате «Арифметика бесконечных» (1656).
МНК: Приближение полиномом в EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью полинома (до 6-й степени включительно).
В основной статье про МНК было рассмотрено приближение линейной функцией. В этой статье рассмотрим приближение полиномиальной функцией (с 3-й до 6-й степени) следующего вида: y=b 0 +b 1 x+b 2 x 2 +b 3 x 3 +…+b 6 x 6
Примечание : В инструменте MS EXCEL Линия тренда , который доступен для диаграмм типа Точечная и График , можно построить линию тренда на основе полинома с максимальной степенью 6. В файле примера продемонстрировано полное совпадение линии тренда диаграммы и линии, вычисленной с помощью формул.
Покажем, как вычислить коэффициенты b линии тренда, заданной полиномом.
Как известно, квадратичная зависимость y=b 0 +b 1 x+b 2 x 2 , подробно рассмотренная в статье МНК: Квадратичная зависимость в MS EXCEL , является частным случаем полиномиальной y=b 0 +b 1 x+b 2 x 2 +b 3 x 3 +… зависимости (в этом случае степень полинома равна 2). Соответственно, используя тот же подход (приравнивание к 0 частных производных), можно вычислить коэффициенты любого полинома.
Примечание : Существует еще один метод вычисления коэффициентов – замена переменных, который рассмотрен в конце статьи.
Для нахождения m+1 коэффициента полинома m-й степени составим систему из m+1 уравнения и решим ее методом обратной матрицы . Для квадратного уравнения (m=2) нам потребовалось вычислить сумму значений х с 1-й до 4-й степени, а для полинома m-й степени необходимо вычислить значения х с 1-й до 2*m степени.
Примечание : Для удобства суммы степеней значений х можно вычислить в отдельном диапазоне ( файл примера столбцы К:М).
В файле примера создана универсальная форма для вычисления коэффициентов полиномов.
Выбрав с помощью элемента управления Счетчик нужную степень полинома, автоматически получим аппроксимацию наших данных выбранным полиномом (будет построен соответствующий график).
Как видно из расчетов, в MS EXCEL этот путь является достаточно трудоемким. Гораздо проще в MS EXCEL реализовать другой подход для вычисления коэффициентов полинома — с помощью замены переменных.
С помощью замены переменных x i =x i полиномиальную зависимость y=b 0 +b 1 x+b 2 x 2 +b 3 x 3 +… можно свести к линейной. Теперь переменная y зависит не от одной переменной х в m разных степенях, а от m независимых переменных x i . Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .
Формула для интерполяции. Применение экстраполяции в Microsoft Excel
Итак, мы рассмотрели простейшие примеры экстраполяции для таблиц и для графиков. В первом случае используется функция ПРЕДСКАЗ , а во втором – линия тренда. Но на основе этих примеров можно решать и гораздо более сложные задачи прогнозирования.
Графический метод: подготовка
Интерполяция в Excel в таком случае начинается с построения графика. Для этого:
- во вкладке «Вставка» выделяют табличный диапазон;
- в блоке инструментов «Диаграммы» выбирают значок «График»;
- в появившемся списке выбирают тот, который лучше подходит для решения конкретной задачи.
Так как в ячейке B9 пусто, график получился разорванный. Кроме того, на нем присутствует дополнительная линия X, в которой нет необходимости, а на горизонтальной оси вместо значений аргумента указаны пункты по порядку.
Способ 2: интерполяция графика с помощью его настроек
- выделяют плоскость, на которой находится график;
- в контекстном меню выбирают кнопку «Выбрать данные…»;
- в окне «Выбор источника данных» в правом блоке нажимают «Изменить»;
- нажимают на иконку с красной стрелкой справа от поля «Диапазон подписей осей»;
- выделяют диапазон А2:А11;
- нажимают на кнопку «OK»;
- вновь вызывают окно «Выбор источника данных»;
- нажимают на кнопку «Скрытые и пустые ячейки» в нижнем левом углу;
- в строке «Показывать пустые ячейки» переключатель переставляют в позицию «Линия» и нажимают «OK»;
- подтверждают эти действия тем же способом.
Реализация алгоритма интерполяции начинается, как и при ручных вычислениях с записи формул для вычисления коэффициентов qi На рис. 9 приведена столбцы таблицы с заданными значениями аргумента, интерполируемой функции и коэффициентовqi. Справа от этой таблицы приведены формулы, записываемые в ячейки столбцаС для вычисления значений коэффициентовqi.
