Изолинии в случае фигуры-плоскости в MS EXCEL

Пусть имеется функция двух переменных Z=f(X;Y). Изолинии (contour line) - это линии, в которой величина Z=const, т.е. изолинии соединяют точки, в которых функция сохраняет одинаковое значение. Часто изолинии используют для отображения 3D поверхностей второго порядка (эллипсоид, эллиптический параболоид, гиперболический параболоид) на плоскости. В этой статье в качестве поверхности рассмотрим простую плоскость. Изолинии в этом случае представляют собой прямые линии. Научимся строить изолинии в MS EXCEL.

Обычно изолинии для функции двух переменных Z=f(X;Y) имеют достаточно сложную конфигурацию, т.к. соответствующая пространственная фигура сложна.

В статье Трехмерные диаграммы (поверхности и изолинии) в MS EXCEL показано как построить изолинии для поверхности задаваемой функцией  Z=-sin(X2+Y2)+1 с использованием штатных средств MS EXCEL (см. картинку ниже). 

В этой статье рассмотрим построение изолиний для плоскости, которая наклонена к плоскости XY.

В простейшем случае поверхность представляет собой плоскость, которая задается уравнением A*X+B*Y+C*Z+D=0 или Z=d+a*Х+b*Y. В этом случае изолинии будут представлять собой прямые линии.

На картинке выше, изолинии построены на диаграмме типа Точечная.

В файле примера приведены формулы для построения таких изолиний, а также диаграмма типа Поверхность, которая помогает представить расположение плоскости относительно плоскости XY.

В принципе изолинии можно построить и штатными средствами MS EXCEL, но в этом случае не удается контролировать количество отображаемых изолиний и их значения Z.

Для полноты картины упомянем еще один способ построения изолиний (ну, почти изолиний? точнее "изо-областей") - с помощью Условного форматирования.

Как видно на рисунке выше, значения ячеек с Z принадлежащих различным интервалам выделяются разными цветами. Это, наверное, простейший способ построения изолиний.

Построение изолиний на диаграмме типа Точечная

Но, вернемся к построению изолиний на диаграмме типа Точечная. В качестве исходных данных используем уравнение плоскости A*X+B*Y+C*Z+D=0 и диапазон изменения Х и Y. 

На диаграмме разместим 8 изолиний. Алгоритм построения изолиний следующий:

  • Определяем диапазон изменения Z (ячейки В16:В18);
  • Определяем значения Z для каждой из 8-и изолинии (ячейки R48:R56);
  • Отображаем на диаграмме прямоугольник, соответствующий диапазонам изменения X и Y (красный прямоугольник), (ячейки Х60:Y64);
  • Для каждого Z (т.е. для каждой изолинии) находим точки пересечения со сторонами прямоугольника (строки 49:56);
  • Каждая изолиния пересекает какие-то 2 стороны прямоугольника, поэтому имеем 2 точки пересечения; 
  • Для каждой изолинии создаем на диаграмме отдельный ряд.

Названием ряда является соответствующее значение Z.

Покажем, как найти точку пересечения со сторонами красного прямоугольника. Для примера возьмем левую сторону. Нам известно: значение Z (=-16,3), уравнение плоскости (Z=5-0,5*Х-0,5*Y), значение X (для левой стороны Х равен минимальному значению, т.е. Х=0). Решая уравнение относительно Y получим значение 42,7 (см. ячейку Т49). Это значение больше максимального значения Y, поэтому делаем вывод, что изолиния с Z=-16,3 не пересекает левую сторону красного прямоугольника. Аналогично находятся точки пересечения изолинии с верхней, нижней и правой стороной (строка 49).

Если изолиния не пересекает сторону прямоугольника, то формула =И(T49<$Y$62;T49>$Y$61) вернет ЛОЖЬ, в противном случае - ИСТИНА. Т.к. изолиния пересекает только 2 стороны, то для каждой изолинии получим 2 значения ЛОЖЬ и 2 значения ИСТИНА. Для построения изолиний нам требуются только точки пересечения.

Формула массива 

=НАИБОЛЬШИЙ((AA49:AD49=ИСТИНА)*(СТОЛБЕЦ(AA49:AD49))-СТОЛБЕЦ($AA$49)+1;{1;2}) 

определяет позиции точек пересечения.

Примечание: В вышеуказанной формуле массива использованы идеи из статьи Массив последовательных чисел в MS EXCEL.

И, наконец, формула =СМЕЩ($S49;;(AF49-1)*2) выводит значения Х и Y этих точек в отдельную таблицу (Q59:V67) для построения рядов данных. 

В итоге получим следующую диаграмму.

При изменении уравнения плоскости или диапазона изменения Х и Y, изолинии будут перестроены автоматически.

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

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