Интерполация в Excel

Интерполацията e метод, при който на базата на съществуващи данни се построяват нови, въз основа на дискретни данни (матрици, получени в резултат на експериментни опити, изследвания, изчисления), като целта е минимална загуба на качество и възможно най-голяма близост до оригинала.

В тази статия ще разгледаме линейната интерполация, която произлиза от общата интерполация на Нютон.

Единственото ограничение, при което може да се използва интерполация е, че желаната стойност трябва да е в масива от данни и да не надхвърля нейната граница.

Интерполацията е изключително използвана в сферата на инженерните науки, математиката, физиката, химията, икономиката, статистиката и много други.

Интерполиращата линейна функция y (x) се намира като се напише уравнението на права, минаваща през две точки (x₀,y₀) и (x₁,y₁):

В нашия пример имаме таблица със стойности, отразяващи плътността на водата при определена температура. Иска се да намерим при измерена 5.3 температура, колко точно е плътността на водата. Очевидно е, че ще използваме интерполация.

Първата колона с температурата е типичен пример за независими известни X стойности, а колоната с плътността – зависими, известни Y стойности.

Ако трябва математически да се изчисли, ще използваме следната формула:
=B8+(D4-A8)/(A9-A8)*(B9-B8) . Като за всяка нова търсена стойност е необходима ръчна редакция.

За да създадем автоматична формула, ще използваме комбинация от функциите FORECAST, OFFSET и MATCH.

За да работи този метод, се изисква диапазонът от известни X да бъде изброен във възходящ ред.

FORECAST изчислява или прогнозира бъдещата стойност с помощта на линейна регресия. Бъдещата стойност е стойност на y за дадена стойност на x.

Функцията FORECAST има следния синтаксис:

Където x e 5.3 , което попада между стойностите 5 и 6 от таблицата, представящи known_xs, а техните плътности са стойностите, които трябва да бъдат посочени като known_ys.

Функцията MATCH търси конкретна стойност в диапазон от клетки и след това връща относителната позиция на стойността в диапазона.

Тя ще има следния вид:

=MATCH(D4;A3:A13;1)

Чрез тази формула се търси стойността от клетка D4 в областта A3-A13. Числото 1 в края на функцията MATCH показва, че ще търси приблизително съвпадение (т.е. най-близката стойност под стойността на търсене). 5 е най-близката стойност под 5.3 . Тъй като 5 е на шеста по ред позиция в областта A3-A13, MATCH връща стойност 6.

Ще я вградим във функцията OFFSET, която реферира към диапазон, намиращ се на зададен брой редове и колони от клетка или диапазон от клетки. Върнатата препратка може да бъде единична клетка или диапазон от клетки.

Тя ще има следните два варианта:

=OFFSET(B3:B13;MATCH(D4;A3:A13,1)-1;0;2)  – за намирането на Known_y’s параметъра на FORECAST.

Чрез тази функция се прави рефериране към стойностите за плътността, между които попада търсената от нас стойност x (5.3). От номера на реда, на който е намерено най-близкото съвпадение с MATCH, изваждаме 1, за да се изместим не с 6, а с 5 клетки спрямо най-горната клетка B3. 0 показва броя колони на изместване, а в параметър height на функцията Offset записваме 2, за да се вземат стойностите на плътността, намиращи се на 5 и 6 позиция спрямо клетката B3. Т.е. намираме y_o=0.99981, y₁=0.99980

=OFFSET(A3:A13;MATCH(D4;A3:A13;1)-1;0;2) )  за намирането на Known_x’s параметъра на FORECAST

Чрез тази функция се прави рефериране към стойностите за температурата, между които попада търсената от нас стойност x (5.3). От номера на реда, на който е намерено най-близкото съвпадение с MATCH изваждаме 1, за да се изместим не с 6, а с 5 клетки спрямо най-горната клетка A3. 0 показва броя колони на изместване, а в параметър Height на функцията Offset записваме 2, за да се вземат стойностите на плътността намиращи се на 5 и 6 позиция спрямо клетката A3. Т.е. намираме x_o=5, x₁=6

А крайният изглед на универсалната функция за интерполация има следния вид:

=FORECAST(D4;OFFSET(B3:B13;MATCH(D4;A2:A12;1)
-1;0;2);OFFSET(A2:A12;MATCH(D4;A2:A12;1)-1;0;2))

Резултът за x=5.3 e y=0.999807.

Можете да свалите примерния файл от тук:

За повече информация за обученията ни по Microsoft Excel – последвайте линка.