В данной теме попробую подобрать набор информации и ответов на вопросы:
Как найти промежуточные значения между заданными?
Как найти максимум функции заданной таблично?
и т.д.
Аппроксимация – под аппроксимационной кривой подразумевается некий полином (как правило, но не обязательно), график которого проходит наиболее близко к известным точкам (степень близости определяется по некоторому закону, как правило методу наименьших квадратов). При этом в известных значениях значения функции не обязательно совпадают с заданными значениями (в общем случае f(Xi) ≠ Yi).
Интерполяция – нахождение неизвестных промежуточных значений некоторой функции, по имеющемуся дискретному набору ее известных значений определенным способом. При этом в известных значениях значения функции совпадают с заданными значениями. Под интерполяционной кривой подразумевается некий полином (в нашем случае), график которого проходит через все известные точки.
Две особенности интерполяции:
- для получения полинома степени «n» требуется «n+1» заданная точка (например, полином первой степени f(x)=a·x+b требует две известные точки, или проще – линия строится по двум точкам, парабола по трём и т.д.);
- применение интерполяции методом «ближайшего соседа» в общем случае недопустимо.
Экстраполяция – особый тип аппроксимации, при котором функция аппроксимируется вне заданного интервала, а не между заданными значениями.
Часть 1. Аппроксимация с использованием встроенного функционала
Начнём с аппроксимации без использования макросов
Для начала - самый простой вариант:
1. Построить точечный график по имеющимся точкам;
2. На график добавить линию тренда с отображением уравнения на диаграмме;
3. Подобрать вид уравнения (степень полинома, вид уравнения...) который нравится/наиболее адекватно по субъективному восприятию отражает тенденцию изменения зависимости;
4. Скопировать уравнение линии тренда в ячейку и заменить "х" на "*А1^", где А1 - адрес ячейки в которой содержится значение аргумента, по которому требуется определить значение функции.
5. Пользоваться...
Если лень копировать уравнение с диаграммы, и хочется получить коэфф-ты полиномов, то можно сделать вот так:
Внимание! Не всегда коэфф-ты найденные нижеописанными способами будут соответствовать коэ-там на уравнении. Но об этом в 4-й части..
Аппроксимация полиномом
| Код |
|---|
Определение коэффициентов линейного уравнения
y=ax+b
b= 1.397235 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7;1);1;2)
a= -0.027554 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7;1);1;1)
Определение коэффициентов квадратичного уравнения
y=ax2+bx+c
c= 1.662612 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7^{1;2};);1;3)
b= -0.071047 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7^{1;2};);1;2)
a= 0.000849 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7^{1;2};);1;1)
Определение коэффициентов кубического уравнения
y=ax3+bx²+cx+d
d= 1.872900 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7^{1;2;3};);1;4)
c= -0.132192 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7^{1;2;3};);1;3)
b= 0.004066 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7^{1;2;3};);1;2)
a= -0.000042 =ИНДЕКС(ЛИНЕЙН($B$2:$B$7;$A$2:$A$7^{1;2;3};);1;1)
|
Поиск коэффициентов для степеней аппроксимирующего полинома бОльших степений аналогичен.
Аппроксимация степенной функцией
| Код |
|---|
Коэф-ты уравнения y = а · Х ^ m m = -0.6320 =ИНДЕКС(ЛИНЕЙН(E3:E8;D3:D8);1) A = 1.1596 =ИНДЕКС(ЛИНЕЙН(E3:E8;D3:D8);2) a = 3.1888 =EXP(H4) |
Аппроксимация логарифмической функцией
| Код |
|---|
Коэф-ты уравнения y = а · ln(x) + b a = -0.4676 =ИНДЕКС(ЛИНЕЙН(B3:B8;D3:D8);1) b = 2.0017 =ИНДЕКС(ЛИНЕЙН(B3:B8;D3:D8);2) |
Использованием макросов
Преимуществом будет отсутствие необходимости использования ячеек листа. Макросы сохранённые в надстройку позволяют пользоваться ими без импорта в лист ну и т.д...
Недостатком - отсутствие визуализации решения.
Скрытый текст |
|---|
| Листинги макросов по личным причинам убрал. Кто хотел - за пару лет мог скопировать. |
За сим первую часть закрываю. Вторая часть будет о кусочной интерполяции ... чуть позже.
Изменено: - 29.09.2024 21:31:25
(личное)
