Інтерполяція в Excel: особливості, порядок дій і приклади

Табличний процесор Excel дає змогу не тільки швидко проводити різні обчислення, але і вирішувати досить складні завдання. Наприклад, з його допомогою можна здійснювати математичне моделювання на основі набору дискретних значень тієї чи іншої функції, в тому числі знаходити проміжне значення функцій методом інтерполяції. В Excel для цього передбачені різні інструменти, користуватися якими навчить ця стаття.

Метод інтерполяції: що це таке?

В обчислювальній математиці так називають спосіб знаходження невідомих проміжних значень функції Y(X) по дискретному набору вже відомих. Інтерполяція функції Y(X) може здійснюватися тільки для тих аргументів, які знаходяться всередині інтервалу[X0, Xn], такого, що відомі значення Y(X 0 ) і Y(X n ). Якщо X не належить[X0, Xn]можна використовувати метод екстраполяції. У класичній постановці інтерполяційної задачі потрібно знайти наближене аналітичну функцію ?(X), у якій значення у вузлових точках X i збігаються зі значеннями Y(X i ) вихідної таблиці, тобто дотримується умова ? (X i )=Y i (i = 012,n).

Лінійна інтерполяція в Excel

У самому відомому табличному процесорі від Microsoft є вкрай корисний оператор «FORECAST». Розглянемо дані, розміщені в таблиці, поданій нижче.





A



B



C



D



E



1



x



f(x)















2



5



38















3



10



68















4



15



98















5



20



128















6



25



158















7



30



188













У першому стовпчику знаходяться аргументи x, а в другому — відповідні їм значення деякої лінійної функції f(x). Припустимо, що нам потрібно дізнатися значення аргументу x=28. Для цього: виділяють пусту клітинку табличного процесора, куди буде виводитися результат від здійснених дій, наприклад, C1; клікають по значку «fx» («Вставити функцію»), розміщеним зліва від рядка формул; у віконці «Майстра функцій» заходять в категорії «Математичні»; знаходять оператор «FORECAST» і натискають на «OK». У вікні аргументів є 3 поля. В перше вводять з клавіатури значення аргументу (в конкретній задачі це 28). Для того щоб заповнити поле «Відомі значення _ y», натискають на іконку з червоною стрілкою зліва від відповідного віконця і виділяють відповідну область на аркуші. У конкретному випадку це частина - стовпця з адресами з діапазону B2:B7.


Точно так само заповнюють поле «Відомі значення _ x» і натискають на кнопку «Ок». В результаті у виділеній комірці C1 відображається значення 176 є підсумком процедури інтерполяції.

Графічний метод: підготовка

Інтерполяція в Excel, приклад якої наведено вище, далеко не єдиний спосіб, що дозволяє з'ясувати проміжні невідомі значення функції Y(X) по дискретному набору вже відомих. Зокрема, може бути застосовано графічний метод. Він може виявитися корисним, якщо в таблиці до одного з аргументів не зазначено відповідне значення функції, як у тій, що представлена нижче (див. комірку з адресою B9).





A



B



C



D



E



1



x



f(x)















2



5



38















3



10



68















4



15



98















5



20



128















6



25



158















7



30



188















8



35



218















9



40



















10



45



278















11



50



308













Інтерполяція в Excel в такому разі починається з побудови графіка. Для цього: у вкладці «Вставка» виділяють табличний діапазон; у блоці інструментів «Діаграми» вибирають значок «Графік»; у списку вибирають той, який краще підходить для вирішення конкретної завдання. Так як в комірці B9 порожньо, графік вийшов розірваний. Крім того, на ньому присутня додаткова лінія X, в якій немає необхідності, а на горизонтальній осі замість значень аргументу вказані пункти по порядку.

Інтерполяція в Excel: рішення графічним методом

Займемося обробкою графіка. Для цього виділяють суцільну синю лінію і видаляють її натисканням кнопки Delete, яка знаходиться на клавіатурі. Потім: виділяють площину, на якій знаходиться графік; у контекстному меню вибирають кнопку «Вибрати»; у вікні Вибір джерела даних» в правому блоці натискають «Змінити»; натискають на іконку з червоною стрілкою праворуч від поля «Діапазон підписів осей»; виділяють діапазон А2:А11; натискають на кнопку «OK»; знову викликають вікно «Вибір джерела даних»; натискають на кнопку «Приховані і порожні клітинки» в нижньому лівому кутку; у рядку «Показувати порожні клітинки» перемикач переставляють у позицію «Лінія» і натисніть «OK»; підтверджують ці дії тим же способом. Якщо все зроблено правильно, розрив буде видалений, а шляхом наведення курсору на потрібну точку графіка можна буде побачити відповідні значення аргументу і функції.

Використання спеціальної функції НД

Тепер, коли ви знаєте, як зробити інтерполяцію в Excel графічним методом або за допомогою оператора «FORECAST», рішення багатьох практичних завдань для вас не складе великої праці. Однак це ще не все. Табличний процесор Microsoft представляє можливість знайти невідоме значення функції за допомогою функції НД. Припустимо, що графік вже побудований, на ньому вже встановлені коректні підпису шкали. Спробуємо ліквідувати розрив. Для цього: виділяють в таблиці клітинку, в якій відсутня значення функції; вибирають піктограму «Вставити функцію»; у «Майстрі функцій» у віконці «Категорії» знаходять рядок «Повний алфавітний перелік» (в деяких версіях процесора «Перевірка властивостей і значень»); натискають на запис «НД» і тиснуть на кнопку «OK». Після цього в комірці B9 з'являється значення помилки#Н/Д». Однак обрив графіка автоматично усувається.
Ви можете поступити навіть простіше: внести з клавіатури у осередок B9 символи «#Н/Д» (без лапок).

Білінійна інтерполяція

Коло завдань, для вирішення яких можна використовувати моделювання за допомогою функцій однієї змінної, досить обмежений. Тому має сенс розглянути, як використовується формула подвійної інтерполяції в Excel. Приклади можуть бути самими різними. Наприклад: є таблиця (див. нижче).





A



B



C



D



E



F



G



1







200



400



600



800



1000



Проліт



2



20



10



20



160



210



260







3



30



40



60



190



240



290







4



40



130



180



230



280



330







5



50



180



230



280



330



380







6



60



240



290



340



390



440







7



70



310



360



410



460



510







8



80



390



440



490



540



590







9



90



750



800



850



900



950







10



Висота



278





















Потрібно обчислити тиск вітру при величині прольоту 300 м на висоті 25 м. У таблицю додають нові записи так, як показано на малюнку (див. нижче). Як видно, в неї додані комірки для висоти прольоту в J1 і J2. Шляхом зворотного послідовної підстановки «збирають» мегаформулу, необхідну для знаходження тиску вітру при конкретних параметрах. Для цього: копіюють текст формули з комірки з адресою J17 в клітинку J19; замінюють у формулі посилання на J15 значенням у клітинці J15: J7+(J8-J7)*J11/J13; повторюють ці дії до отримання необхідної формули.

Використання spline

Попередній метод досить громіздкий, тому в деяких випадках краще інтерполяція сплайнами. В Excel її суть полягає в знаходженні интерполирующей функції ?(Х) за формулами одного й того ж типу для різних підмножин аргументу. Далі здійснюється стикування значень ?(Х) і її похідних на граничних значеннях аргументів кожної з підмножин. В Excel для цих цілей передбачені спеціальні функції, а також можливе написання макросів на VBA. Однак вони повинні створюватися під конкретну задачу, тому їх вивчення в загальному вигляді не має сенсу. Тепер ви знаєте, як написати формулу подвійний інтерполяції в Excel коректно чи знайти невідоме значення лінійної функції за допомогою вбудованих операторів або графіка. Сподіваємося, що ця інформація допоможе вам у вирішенні багатьох практичних завдань.