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

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

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

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

Примечание: В статье Трехмерные диаграммы (поверхности и изолинии) в MS EXCEL показано как построить изолинии для поверхности задаваемой функцией  Z=-sin(X2+Y2)+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:АI19;
  • Находим точки пересечения изолиний и прямоугольника, представляющего границу области определения. Если изолиния пересекает какую-либо сторону прямоугольника, то делает это только 1 раз.  Каждая изолиния имеет 2 точки пересечения с прямоугольником: вход и выход, которые расположены на разных сторонах прямоугольника. Точка входа является первой точкой изолинии на диаграмме. Точка имеет координаты (Х;Y). Диапазон BR7:BY19
  • Имея координаты точки входа и зная сторону прямоугольника, на которой она находится, вычисляем границы маленького квадратика (его стороны равны шагу по Х и по 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 асимптоты.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)