Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

Практическая работа № 2 Интерполяция в среде excel, Линия тренда. Задача №1

Цель работы– получить аналитическую зависимость функции от аргумента (задано таблично) в средеExcel.

Последовательность выполнения работы

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

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

1. Отображение анализируемых данных в графическом виде.

2. Построение кривой для рассматриваемой зависимости.

3. Анализ полученной кривой для рассматриваемой зависимости.

Выполнение работы

«Мастер диаграмм – Точечная – Готово». Выбираем значения «Y» и «X», соответственно для «ρ, кг/м 3 » и «p, МПа».

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

Вывод: При выполнении работы по интерполяции в средеEXCELмы по заданным значениям зависимости плотности пара, (кг/м 3 ) от давления р, (МПа) получили аналитическое выражение с помощью линии тренда и построили график линии тренда.

Практическая работа № 3 Интерполяция в среде excel, Линия тренда. Задача №2

Цель работы– получить аналитическую зависимость функции от аргумента (задано графически) в средеExcel.

Последовательность выполнения работы

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

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

1. Отображение анализируемых данных в графическом виде.

2. Построение кривой для рассматриваемой зависимости.

3. Анализ полученной кривой для рассматриваемой зависимости.

Выполнение работы

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

Данные полученные с графика

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

«Мастер диаграмм – Точечная – Готово». Выбираем значения XиY.

Данные представлены на диаграмме в виде отдельных точек – кривая для рассматриваемой зависимости не проведена. Эти данные в области диаграммы нужно выделить, затем щелкнуть правой кнопкой мыши. В раскрывшемся контекстном меню выбрать команду «Добавить линию тренда».

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

2. В открывшемся диалоговом окне можно задать тип линии. Выбираем «Полиномиальную», и задаем «Порядок», равный шести. Затем переходим на вкладку «Параметры» и ставим флажки «Отображать уравнение», а также «Поместить на диаграмму величину достоверности аппроксимации (R^2)».

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

\

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

3. После нажатия кнопки ОК получаем результат. На рисунке не видно выпадающих экспериментальных точек на линии тренда и высока степень достоверности.

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

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

Вывод: При построении линии тренда мы получили аналитическое выражение функции от аргумента. В данном случае степень полинома равна шести, а количество точек – восемнадцати. Достоверность аппроксимацииR 2 = 0,9987 (величина достоверности вполне удовлетворяет).

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
На вход они получают координату в абстрактных для компьютерной подпрограммы отображения на экран координатах, на выход дают в экранных экранные координаты будут для разных операционных систем разными. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Всего точек у нас будет (например, , , ; тогда у нас будет 3 точки). Этому соответствует диапазон входных значений . Значению соответствует , соответствует . Последняя точка имеет индекс . Чему соответствует ?
Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

Интерполяция данных: соединяем точки так, чтобы было красиво / Хабр

Другое традиционное решение, кроме кубических сплайнов дефекта 1 — полиномы Лагранжа. Это полиномы степени n&nbsp–&nbsp1, принимающие заданные значения в заданных точках. То есть членения на сегменты здесь не происходит, вся последовательность описывается одним полиномом.
Но вот что получается:

Как Сделать Интерполяцию в Excel по Таблице

БлогNot. Excel: как построить степенной полином функцией ЛИНЕЙН

Excel: как построить степенной полином функцией ЛИНЕЙН

Сейчас мы хотим, во-первых, построить в Excel интерполирующий полином тоже стандартной функцией, во-вторых, не вдаваясь в детали теории, понять смысл этой простой задачи — как построить кривую, проходящую через несколько известных точек на плоскости.

Итак, по известному набору из N значений функции f(xi)=yi , заданному парой векторов xi, yi=f(xi) , i=1, 2, . N , нужно построить кривую, проходящую через все точки.

Через N различных между собой по оси x точек всегда можно построить кривую, зависящую от x N-1 , её уравнение будет иметь общий вид

В этом уравнении нам неизвестны коэффициенты сi . Из условия, что кривая проходит через все заданные в постановке задачи точки, можно записать систему линейных алгебраических уравнений:

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

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

Решив эту систему уравнений, то есть, найдя обратную к матрице Вандермонда матрицу и умножив её на вектор y , найдём коэффициенты сi . Теперь, подставив их в уравнение (1), мы можем аналитически оценить значение функции в произвольной точке x .

Ниже показано «ручное» решение в Excel и решение с помощью стандартной функции ЛИНЕЙН.

Скриншот файла Excel с решением

Скриншот файла Excel с решением

Пояснение 1. Как растянуть формулу на матрицу значений

1. Введите требуемую формулу и нажмите Enter , на рисунке показан вид экрана перед нажатием:

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

Ввод «матричной» формулы со смешанными ссылками

2. Подведите курсор мыши к нижнему правому уголку ячейки C2 , уголок превратился в чёрный крестик, зажмите левую кнопку мыши и растяните формулу вниз до ячейки C5 .

Как Сделать Интерполяцию в Excel по Таблице • Думаем и экспериментируем

Курсор для растягивания в Excel, «чёрный крестик»

Формула растянута вниз

Формула растянута вниз

3. Отпустите кнопку мыши, снова так же подведите курсор к уголку ячейки C5 (опять чёрный крестик) и при зажатой левой кнопке мыши растяните выделение вправо до столбца F .

Заполнение таблицы формулой в Excel

Заполнение таблицы формулой в Excel

Пояснение 2. Как ввести формулу массива

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

Вид курсора по умолчанию в Excel

Вид курсора по умолчанию в Excel

Мы сами отвечаем за правильность выделения ячеек диапазона результата, например, Excel не обязан знать, что в результате обращения матрицы размерностью 3×3 получится тоже матрица размерностью 3×3 :

Выделение диапазона ячеек результата в Excel

Выделение диапазона ячеек результата в Excel

2. Не снимая выделения, ввести формулу массива в первую ячейку выделенного диапазона, это можно сделать «вручную», просто нажав клавишу F2 и начав набирать формулу со знака » = «, или с помощью Мастера Функций (см. п.3 документа по Excel здесь).

Ввод формулы массива в первую ячейку выделенного диапазона

Ввод формулы массива в первую ячейку выделенного диапазона

3. При зажатых клавишах Ctrl и Shift , нажать клавишу Enter , то есть, ввести комбинацию клавиш Ctrl+Shift+Enter .

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
В общем, надо мне сделать регулятор холостого хода такая штука для автомобиля, которая при холостом ходе в зависимости от температуры двигателя должна поддерживать определенные обороты. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Да, пока не нырнули — спасибо Equation Editor-у от CodeCogs! С их помощью я лихо построил все математические формулы без всяких Microsoft Equation Editor, которые потом надо еще экспортировать в графику со вставкой сюда. Кстати, там есть и русский редактор. В общем, рекомендую!

Формула интерполяции в excel — IT и мир ПК

Для нахождения m+1 коэффициента полинома m-й степени составим систему из m+1 уравнения и решим ее методом обратной матрицы . Для квадратного уравнения (m=2) нам потребовалось вычислить сумму значений х с 1-й до 4-й степени, а для полинома m-й степени необходимо вычислить значения х с 1-й до 2*m степени.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector