Изолинии для частного случая поверхностей второго порядка в EXCEL

Научимся строить изолинии (contour line) в MS EXCEL для одного из частных случаев поверхностей второго порядка: 2*a ₁₂ *x*y+2*a ₁₄ *x+ 2*a ₂₄ *y +2*a ₃₄ *z+a ₄₄ =0 . Изолинии в этом случае выражаются дробно-линейными функциями вида y=(a*x+b)/(c*x+d) .

В статье Изолинии в случае фигуры-плоскости в MS EXCEL было показано как построить изоляции для плоскости в трехмерном пространстве (плоскость задается уравнением A*X+B*Y+C*Z+D=0).

В этой статье построим изолинии (contour line) в MS EXCEL для одного из частных случаев поверхностей второго порядка: 2*a ₁₂ *x*y+2*a ₁₄ *x+2*a ₂₄ *y+2*a ₃₄ *z+a ₄₄ =0. Эта поверхность похожа на лист бумаги, у которого 2 угла загнуты вверх, а 2 вниз.

Примечание : В статье Трехмерные диаграммы (поверхности и изолинии) в MS EXCEL показано как построить изолинии для поверхности задаваемой функцией  Z=-sin(X ² +Y ² )+1 с использованием штатных средств MS EXCEL: объемных диаграмм и Условного форматирования . Эти средства дают представления о форме изолиний, но имеют серьезные ограничения. Основное их преимущество состоит в простоте и скорости построения, а также универсальности (можно построить изолинии для любых поверхностей, если известно уравнение).

Изолинии для этой поверхности можно построить с помощью диаграммы типа Проволочная поверхность .

К сожалению, такая диаграмма имеет существенное ограничение - невозможно задать уровни изолиний, т.к. они рассчитываются автоматически.

В этой статье покажем как построить изолинии произвольного уровня для поверхности вида Z=A*X+B*Y+C*X*Y+D. Конечный результат будет выглядеть так:

Перед построением диаграммы исследуем уравнение поверхности. Это поможет нам построить изолинии и убережет от ошибок.

У данной поверхности есть особые точки, точнее 2 асимптоты: Х=-B/C и Y=-A/C.

Каждая изолиния состоит из двух частей (ветвей), расположенных в противоположных четвертях относительно асимптот. Мы будем их отображать одним цветом. Кроме того, для каждой ветви нам потребуется своя таблица с данными.

Исходные данные

Для построения диаграммы нам потребуется совсем не много исходных данных, а именно:

• коэффициенты для задания уравнения поверхности: А, В, С и D;
• начальное и конечное значение для Х и Y (можно задать начальное значение, количество точек и величину шага).

Алгоритм расчета точек изолиний

• Рассчитываем координаты прямоугольника, ограничивающего область построения (выделен на диаграмме бордовым цветом). Диапазон А38:С43 ;
• Вычисляем максимальное и минимальное значение Z для заданного диапазона значений Х и Y. Диапазон В19: В20 ;
• Вычисляем значения для 10-и уровней изолиний (значение Z изменяется равномерно от минимального до максимального). Диапазон АI10: АI 19 ;
• Находим точки пересечения изолиний и прямоугольника, представляющего границу области определения. Если изолиния пересекает какую-либо сторону прямоугольника, то делает это только 1 раз.  Каждая изолиния имеет 2 точки пересечения с прямоугольником: вход и выход, которые расположены на разных сторонах прямоугольника. Точка входа является первой точкой изолинии на диаграмме. Точка имеет координаты (Х;Y). Диапазон BR7: BY 19 ;
• Имея координаты точки входа и зная сторону прямоугольника, на которой она находится, вычисляем границы маленького квадратика (его стороны равны шагу по Х и по Y, т.е. ΔХ и ΔY, обычно равные). Маленький квадратик на диаграмме не отображается, он используется для вычисления точек изолинии. Диапазон S43:Z43 ;
• Находим точку, в которой изолиния пересекает границы этого квадратика - это будет единственная точка, т.к. это следствие того, что мы выбрали именно такой тип поверхности. Эта точка будет следующей точкой диаграммы. Диапазон AF43:AI43 ;
• Повторяем процедуру с маленьким квадратиком: точкой входа в него будет точка, вычисленная на предыдущем шаге. Процедура повторяется пока не будет достигнута граница области определения (бордовый прямоугольник);
• Аналогично строится вторая ветвь изолинии (см. ячейку AN36 ). Для этого в отдельной таблице находится вторая точка входа (Диапазон BR23:BW33 ) и процедура с маленьким квадратиком повторяется.

Для построения одной ветви изолинии используется 40 точек. Если изолиния короткая, то может потребоваться меньше точек.

Как видно из приведенного выше алгоритма - процедура достаточно трудоемка. Кроме того, если необходимо изменить тип поверхности, например, гиперболический параболоид, то расчетные таблицы придется еще усложнить, т.к. в этом случае изолиния может пересекать одну сторону границы области определения сразу в 2-х точках. Но, если Вам требуется построить поверхность типа Z=A*X+B*Y+C*X*Y+D, то вышеуказанный алгоритм будет работать для любых коэффициентов А, В, С, D и для любых границ области определения.

Построение диаграммы

Изолинии в файле примера построены на диаграмме типа Точечная . Всего в диаграмме использовано 23 ряда:

• 10 изолиний по 2 ветви каждая;
• граница области определения;
• 2 асимптоты.