Построить поверхность в excel
Информация воспринимается легче, если представлена наглядно. Один из способов презентации отчетов, планов, показателей и другого вида делового материала – графики и диаграммы. В аналитике это незаменимые инструменты.
Построить график в Excel по данным таблицы можно несколькими способами. Каждый из них обладает своими преимуществами и недостатками для конкретной ситуации. Рассмотрим все по порядку.
Простейший график изменений
График нужен тогда, когда необходимо показать изменения данных. Начнем с простейшей диаграммы для демонстрации событий в разные промежутки времени.
Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:
Год | Чистая прибыль* |
2010 | 13742 |
2011 | 11786 |
2012 | 6045 |
2013 | 7234 |
2014 | 15605 |
Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:
Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.
Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:
Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.
Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:
Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».
Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» - «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:
Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» - «Переместить диаграмму»).
График с двумя и более кривыми
Допустим, нам нужно показать не только чистую прибыль, но и стоимость активов. Данных стало больше:
Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.
Добавление второй оси
Как добавить вторую (дополнительную) ось? Когда единицы измерения одинаковы, пользуемся предложенной выше инструкцией. Если же нужно показать данные разных типов, понадобится вспомогательная ось.
Сначала строим график так, будто у нас одинаковые единицы измерения.
Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» - «По вспомогательной оси».
Нажимаем «Закрыть» - на графике появилась вторая ось, которая «подстроилась» под данные кривой.
Это один из способов. Есть и другой – изменение типа диаграммы.
Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».
Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.
Всего несколько нажатий – дополнительная ось для другого типа измерений готова.
Строим график функций в Excel
Вся работа состоит из двух этапов:
- Создание таблицы с данными.
- Построение графика.
Пример: y=x(√x – 2). Шаг – 0,3.
Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.
В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.
Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» - «Диаграмма» - «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».
Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.
Жмем ОК и любуемся результатом.
Наложение и комбинирование графиков
Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:
Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».
А вот наши 2 графика функций в одном поле.
Графики зависимости
Данные одного столбца (строки) зависят от данных другого столбца (строки).
Построить график зависимости одного столбца от другого в Excel можно так:
Условия: А = f (E); В = f (E); С = f (E); D = f (E).
Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.
Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.
Готовые примеры графиков и диаграмм в Excel скачать:
Как сделать еженедельный график в Excel вместе с ежедневным.
Пример создания динамического синхронного еженедельного графика вместе с ежедневным. Синхронное отображение двух таймфреймов на одном графике.
Точно так же можно строить кольцевые и линейчатые диаграммы, гистограммы, пузырьковые, биржевые и т.д. Возможности Excel разнообразны. Вполне достаточно, чтобы наглядно изобразить разные типы данных.
Принцип построения поверхностных диаграмм в Excel можно сравнить с рельефными картами. Где положение пункта определяется не только долготой и широтой, но и третьей величиной – высотой.
Данное сравнение поможет понять, как создать на первый взгляд сложную поверхностную диаграмму в Excel и как ее использовать.
Построение поверхностной диаграммы в Excel
Практический пример применения и создания поверхностной диаграммы в Excel.
Напряжение излучения в квадратной комнате определено формулой z=[sin(x)*y] 2 . Начало осей координат расположено центру комнаты.
Визуально сложно определить место в комнате, где наиболее интенсивное излучение. Создадим графическое представление ситуации, которое будет читабельно даже для дилетантов.
Сначала выполним все необходимые расчеты и вычисления в таблице. А поверхностную диаграмму построим на основе уже полученных данных.
- Заполните таблицу как указано на рисунке.
- Выделите диапазон B2:L12 так, чтобы B2 активной ячейкой выделенного диапазона (выделяйте с верхнего левого угла B2 диапазона к нижнему правому углу до ячейки L12).
- Не снимая выделения, введите в строку формул: =(SIN(B$13)*$A2)^2 и нажмите комбинацию клавиш CTRL+Enter. Обратите внимание, как мы используем в аргументах формулы смешанные ссылки на ячейки.
- Между столбцами A и B вставьте новый столбец и заполните его вторую ячейку формулой: =" "&A2 (не забудьте поставить пробел между кавычками). Скопируйте эту формулу во все ячейки столбца до 12-ой строки (то есть заполните этой формулой диапазон ячеек B2:B12).
- Выделите диапазон: B2:M12 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Поверхность».
Теперь четко видно на диаграмме что наибольшая интенсивность излучения находится в углах комнаты.
Чтобы правильно настроить горизонтальную ось X, щелкните по диаграмме, чтобы ее активировать и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
В появившемся окне «Выбор источника данных» в правом разделе «Подписи горизонтальной оси (категории)» щелкните на кнопку «Изменить».
В окне «Подписи оси» измените значение, выделив диапазон ячеек C13:M13 и на всех диалоговых окнах нажмите ОК.
Краткое описание примера
Стоит отметить! При создании поверхностной диаграммы мы изменили числовые значения столбца A в текстовые, поместив их в столбец B с помощью формулы . Если бы мы этого не сделали, то Excel воспринял бы эти числовые значения (столбца A) как данные для построения поверхностной диаграммы, а не как подписи данных.
Если бы мы просто присвоили текстовый формат для значений столбца A (вместо дополнительного столбца с формулами), тогда мы просто получили бы ошибку при расчетах.
Вот в такой нехитрый способ мы красиво сделали подписи для осей диаграммы и не допустили ошибок при расчетах.
Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .
При попытке построить диаграмму типа Поверхность на основе одного ряда данных, MS EXCEL может сказать, что ему требуется как минимум 2 ряда. На самом деле, для более или менее наглядной поверхности потребуется как минимум 10 рядов (лучше 20 – для сложных поверхностей). К счастью, ряды не нужно создавать в ручную – достаточно правильно сформировать таблицу с исходными данными.
Построим график функции z=-sin(x*х+y*y)+1 (см. файл примера ).
В качестве источника данных используем таблицу, в которой в верхней строке указаны значения Х, а в левом столбце Y. В остальных ячейках – значения Z для всех пар (Х,Y)
Чтобы значения Z были более информативны - можно использовать Условное форматирование .
Условное форматирование показывает некий вариант изолиний (см. ниже): близкие значения Z выделены одним цветом. Это также позволяет предварительно оценить вид создаваемой диаграммы.
Теперь выделите любую ячейку таблицы и постройте диаграмму типа Поверхность (вкладка Вставка , группа Диаграммы , Кнопка Другие ).
Полученную фигуру можно повращать (кнопка Поворот объемной фигуры на вкладке Макет ).
Для построения диаграммы используется 3 оси: горизонтальная ось (категорий), вертикальная ось (значений), ось Z (рядов значений). Все 3 названия осей требуют пояснения.
Во-первых, ось Z на самом деле является нашей осью Х и отображает имена рядов. Если имена рядов убрать, то изменятся и подписи оси (убрать имена рядов можно нажав кнопку Изменить в окне Выбор источника данных , которое доступно через вкладку Конструктор ).
Этот факт роднит диаграмму Поверхность с Диаграммой График , где по Х отображаются лишь подписи. Т.е. при попытке построить поверхность с неравномерным шагом по Х (например, 1, 2, 5, 9, 77, 103, ..) получим искажения масштаба: значения по Х будут откладываться последовательно 1, 2, 3, … (конечно, Z будет рассчитаны правильно). Интересно, что формат подписей по оси Z можно настроить только изменяя формат ячеек, на которые ссылаются имена рядов (изменение формата на вкладке Число (в окне Формат Оси) ни к чему не приводит).
Во-вторых, по вертикальной оси (значений) на самом деле откладываются значения Z (это очевидно из рисунка).
И, наконец, в-третьих, горизонтальная ось (категорий) – это значения Y (на самом деле опять только подписи). Подписи, как и для Графика можно удалить или изменить нажав кнопку Изменить в окне Выбор источника данных , которое доступно через вкладку Конструктор (на этот раз нужно нажать кнопку расположенную справа).
Так как диаграмма Поверхность – это по сути трехмерный График , в котором одному Х соответствует один Y, то некоторые объемные фигуры отобразить на диаграмме Поверхность не удастся. Например, не удастся отобразить гиперболоид, т.к. каждой паре (Х;Y) на диаграмме Поверхность соответствует только один Z, а для гиперболоида это не так (каждой паре соответствует 2 значения Z). Поэтому, в MS EXCEL придется довольствоваться только половиной гиперболоида.
Как видно из диаграммы, близкие значения Z выделены одним цветом, которые указаны в Легенде. К сожалению, в MS EXCEL невозможно управлять этими уровнями (даже при повороте фигуры количество уровней и диапазоны значений существенно изменяются). Это приводит к тому, что построение изолиний ( линии уровня, контурный график, англ. Contour plot ) реализовано в MS EXCEL в достаточно ограниченном виде: невозможно указать на диаграмме значение изолинии (диапазоны цветов указаны на Легенде), разбиение на диапазоны по Z производится MS EXCEL автоматически и нет возможности на это повлиять. Ниже приведены изолинии для упомянутого выше гиперболоида. Это особый вид диаграммы – Проволочная контурная .
Практически такой же картинки можно добиться простым поворотом фигуры на диаграмме Поверхность .
Научимся строить изолинии (contour line) в MS EXCEL для одного из частных случаев поверхностей второго порядка: 2*a 12 *x*y+2*a 14 *x+ 2*a 24 *y +2*a 34 *z+a 44 =0 . Изолинии в этом случае выражаются дробно-линейными функциями вида y=(a*x+b)/(c*x+d) .
В статье Изолинии в случае фигуры-плоскости в MS EXCEL было показано как построить изоляции для плоскости в трехмерном пространстве (плоскость задается уравнением A*X+B*Y+C*Z+D=0).
В этой статье построим изолинии (contour line) в MS EXCEL для одного из частных случаев поверхностей второго порядка: 2*a 12 *x*y+2*a 14 *x+2*a 24 *y+2*a 34 *z+a 44 =0. Эта поверхность похожа на лист бумаги, у которого 2 угла загнуты вверх, а 2 вниз.
Примечание : В статье Трехмерные диаграммы (поверхности и изолинии) в MS EXCEL показано как построить изолинии для поверхности задаваемой функцией Z=-sin(X 2 +Y 2 )+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 ряда:
Пусть имеется функция двух переменных Z=f(X;Y). Изолинии (contour line) - это линии, в которой величина Z=const, т.е. изолинии соединяют точки, в которых функция сохраняет одинаковое значение. Часто изолинии используют для отображения 3D поверхностей второго порядка ( эллипсоид, эллиптический параболоид, гиперболический параболоид) на плоскости. В этой статье решим гораздо более скромную задачу: построим изолинии образуемые плоскостью в MS EXCEL . Изолинии в этом случае представляют собой прямые линии.
Обычно изолинии для функции двух переменных Z=f(X;Y) имеют достаточно сложную конфигурацию, т.к. соответствующая пространственная фигура сложна.
В статье Трехмерные диаграммы (поверхности и изолинии) в MS EXCEL показано как построить изолинии для поверхности задаваемой функцией Z=-sin(X 2 +Y 2 )+1 с использованием штатных средств MS EXCEL (см. картинку ниже).
В этой статье рассмотрим построение изолиний для плоскости, которая наклонена к плоскости XY.
Плоскость задается уравнением A*X+B*Y+C*Z+D=0 или Z=d+a*Х+b*Y. Приравняв Z заданной постоянной величине (const), из предыдущего уравнения получим, что изолинии будут представлять собой прямые линии: Y=(const-d-a*Х)/b.
На картинке выше, изолинии построены на диаграмме типа Точечная .
В файле примера приведены формулы для построения таких изолиний, а также диаграмма типа Поверхность, которая помогает представить расположение плоскости относительно плоскости XY.
В принципе изолинии можно построить и штатными средствами MS EXCEL, но в этом случае не удается контролировать количество отображаемых изолиний и их значения Z.
Для полноты картины упомянем еще один способ построения изолиний (ну, почти изолиний? точнее "изо-областей") - с помощью Условного форматирования .
Как видно на рисунке выше, значения ячеек с Z принадлежащих различным интервалам выделяются разными цветами. Это, наверное, простейший способ построения изолиний.
Построение изолиний на диаграмме типа Точечная
Но, вернемся к построению изолиний на диаграмме типа Точечная. В качестве исходных данных используем уравнение плоскости A*X+B*Y+C*Z+D=0 и диапазон изменения Х и Y.
На диаграмме разместим 8 изолиний. Алгоритм построения изолиний следующий:
Названием ряда является соответствующее значение Z.
Покажем, как найти точку пересечения со сторонами красного прямоугольника. Для примера возьмем левую сторону. Нам известно: значение Z (=-16,3), уравнение плоскости (Z=5-0,5*Х-0,5*Y), значение X (для левой стороны Х равен минимальному значению, т.е. Х=0). Решая уравнение относительно Y получим значение 42,7 (см. ячейку Т49 ). Это значение больше максимального значения Y, поэтому делаем вывод, что изолиния с Z=-16,3 не пересекает левую сторону красного прямоугольника. Аналогично находятся точки пересечения изолинии с верхней, нижней и правой стороной (строка 49).
Если изолиния не пересекает сторону прямоугольника, то формула =И(T49$Y$61) вернет ЛОЖЬ, в противном случае - ИСТИНА. Т.к. изолиния пересекает только 2 стороны, то для каждой изолинии получим 2 значения ЛОЖЬ и 2 значения ИСТИНА. Для построения изолиний нам требуются только точки пересечения.
определяет позиции точек пересечения.
Примечание : В вышеуказанной формуле массива использованы идеи из статьи Массив последовательных чисел в MS EXCEL .
И, наконец, формула =СМЕЩ($S49;;(AF49-1)*2) выводит значения Х и Y этих точек в отдельную таблицу ( Q59:V67 ) для построения рядов данных.
В итоге получим следующую диаграмму.
При изменении уравнения плоскости или диапазона изменения Х и Y, изолинии будут перестроены автоматически.
Читайте также: