Табличный процессор Excel позволяет не только быстро производить различные вычисления, но и решать достаточно сложные задачи. Например, с его помощью можно осуществлять математическое моделирование на основе набора дискретных значений той или иной функции, в том числе находить промежуточное значение функций методом интерполяции. В Excel для этого предусмотрены различные инструменты, пользоваться которыми научит эта статья.
Метод интерполяции: что это такое?
В вычислительной математике так называют способ нахождения промежуточных неизвестных значений функции Y(X) по дискретному набору уже известных.
Интерполяция функции Y(X) может осуществляться только для тех ее аргументов, которые находятся внутри интервала [X0, Xn], такого, что известны значения Y(X0) и Y(Xn).
Если X не принадлежит [X0, Xn], то можно использовать метод экстраполяции.
В классической постановке интерполяционной задачи требуется найти приближенную аналитическую функцию φ(X), у которой значения в узловых точках Xi совпадают со значениями Y(Xi) исходной таблицы, т. е. соблюдается условие φ (Xi)=Yi (i = 0,1,2,...,n).
Линейная интерполяция в Excel
В самом известном табличном процессоре от Microsoft присутствует крайне полезный оператор «ПРЕДСКАЗ».
Рассмотрим данные, размещенные в в таблице, представленной ниже.
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» («Вставить функцию»), размещенному слева от строки формул;
- в окошке «Мастера функций» заходят в категорию «Математические»;
- находят оператор «ПРЕДСКАЗ» и нажимают на «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 графическим методом или посредством оператора «ПРЕДСКАЗ», решение многих практических задач для вас не составит большого труда. Однако это еще не все. Табличный процессор от 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 корректно или найти неизвестное значение линейной функции посредством встроенных операторов или графика. Надеемся, что эта информация поможет вам в решении множества практических задач.
