Определение промежуточного значения методом линейной интерполяции

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: некоторые инженерные проблемы проектирования требуют использования таблиц для вычисления значений параметров. Поскольку таблицы являются дискретными, дизайнер использует линейную интерполяцию для получения промежуточного значения параметра. Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр). Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.

Рис. 1. Высота над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр)

Рис. 1. Высота над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр)

Скачать заметку в формате Word или pdf, примеры в формате Excel

Как быть, если существует два управляющих параметра? Можно ли выполнить вычисления с помощью одной формулы? В таблице (рис. 2) показаны значения давления ветра для различных высот и величин пролета конструкций. Требуется вычислить давление ветра на высоте 25 метров и величине пролета 300 метров.

Рис. 2. Исходная таблица для интерполяции по двум управляющим параметрам

Рис. 2. Исходная таблица для интерполяции по двум управляющим параметрам

Решение: проблему решаем путем расширения метода, используемого для случая с одним управляющим параметром. Выполните следующие действия.

Начните с таблицы, изображенной на рис. 2. Добавьте исходные ячейки для высоты и пролета в J1 и J2 соответственно (рис. 3).

Рис. 3. Формулы в ячейках J3_J17 объясняют работу мегаформулы

Рис. 3. Формулы в ячейках J3:J17 объясняют работу мегаформулы

Для удобства использования формул определите имена (рис. 4).

Рис. 4. Определенные имена

Рис. 4. Определенные имена

Проследите за работой формулы последовательно переходя от ячейки J3 к ячейке J17.

Путем обратной последовательной подстановки соберите мегаформулу. Скопируйте текст формулы из ячейки J17 в J19. Замените в формуле ссылку на J15 на значение в ячейке J15: J7+(J8-J7)*J11/J13. И так далее. Получится формула, состоящая из 984 символов, которую невозможно воспринять в таком виде. Вы можете посмотреть на нее в приложенном Excel-файле. Не уверен, что такого рода мегаформулы полезны в использовании.

Резюме: линейная интерполяция используется для получения промежуточного значения параметра, если табличные значения заданы только для границ диапазонов; предложен метод расчета по двум управляющим параметрам.

Комментарии: (1)

[…] Линейная интерполяция Глава из книги "Гуру Excel расширяют горизонты", посвященная определению промежуточных значений методом линейной интерполяции. Тяжеловесная формула, но объяснение очень подробное, так что сложностей с использованием возникнуть не должно. […]


Прокомментировать