Как построить поверхностную диаграмму в excel

Диаграмма поверхности в Excel | Примеры создания эпюры поверхности в Excel

Поверхностная диаграмма — это трехмерная диаграмма Excel, на которой точки данных отображаются в трех измерениях. Вы можете видеть сетку поверхности, которая помогает нам найти оптимальную комбинацию между двумя типами точек данных. Типичная поверхностная диаграмма состоит из трех переменных точек данных, назовем их «X, Y и Z». Из этих трех доступных переменных мы можем разделить их на два набора, то есть независимые и зависимые переменные. Две переменные будут независимыми переменными, а одна — зависимой переменной.

Таким образом, диаграмма поверхности показывает взаимосвязь между точками данных через поверхность сетки.

Примеры создания диаграммы поверхности (графика) в Excel

Пример # 1

Рассмотрите приведенные ниже данные, чтобы создать свою первую диаграмму поверхности в Excel.

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

Шаг 1. Сначала откройте таблицу Excel.

Шаг 2: Скопируйте приведенную выше таблицу данных на один из рабочих листов. И выберите диапазон данных.

Шаг 3: Теперь на вкладке ленты выберите опцию «ВСТАВИТЬ».

Шаг 4: На вкладке ВСТАВИТЬ вы увидите раздел «Диаграмма». В этом разделе выберите Stock, Surface или Radar Chart в excel. В этой категории диаграмм выберите диаграмму «3D Surface» .

Шаг 5: Как только вы нажмете на «Трехмерную диаграмму поверхности», вы увидите приведенную ниже диаграмму поверхности по умолчанию на своем рабочем листе.

Шаг 6: Это не окончательная поверхностная диаграмма конечного пользователя. Нам нужно настроить диаграмму, чтобы это было лучше видно.

Выберите диаграмму и нажмите сочетание клавиш Ctrl + 1, чтобы открыть ОБЛАСТЬ ФОРМАТИРОВАНИЯ ДИАГРАММЫ справа от электронной таблицы Excel (Excel 2013 и далее).

Шаг 7: В этой ОБЛАСТИ ФОРМАТИЧЕСКОЙ ДИАГРАММЫ вы можете изменить цвет сетки, настройку горизонтальной и вертикальной оси и многое другое.

Чтобы изменить цвет сетки, вы можете сделать это на самой вкладке ДИЗАЙН. Выбрав диаграмму, вы увидите две вкладки «Инструменты диаграммы», то есть «Дизайн и формат».

Выберите вкладку «Дизайн», перейдите в «Изменить цвет» и выберите цвет по своему усмотрению.

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

Шаг 8: Следующее, что нам нужно сделать, это «переключить строку / столбец» . Поскольку во время вставки диаграммы Excel предполагает, что горизонтальные объекты — это ряды данных, а вертикальные — категории.

Поэтому нам нужно изменить строку / столбец на вкладке «Дизайн».

Как только вы выберете эту опцию, наша диаграмма автоматически изменится и теперь выглядит так, как показано ниже.

Пример # 2

А теперь посмотрим еще на один пример Surface Chart. Для этого рассмотрим набор данных ниже.

Поверхностная диаграмма в Excel и пример ее построения

Принцип построения поверхностных диаграмм в Excel можно сравнить с рельефными картами. Где положение пункта определяется не только долготой и широтой, но и третьей величиной – высотой.

Данное сравнение поможет понять, как создать на первый взгляд сложную поверхностную диаграмму в Excel и как ее использовать.

Построение поверхностной диаграммы в Excel

Практический пример применения и создания поверхностной диаграммы в Excel.

Напряжение излучения в квадратной комнате определено формулой z=[sin(x)*y] 2 . Начало осей координат расположено центру комнаты.

Визуально сложно определить место в комнате, где наиболее интенсивное излучение. Создадим графическое представление ситуации, которое будет читабельно даже для дилетантов.

Сначала выполним все необходимые расчеты и вычисления в таблице. А поверхностную диаграмму построим на основе уже полученных данных.

1. Заполните таблицу как указано на рисунке.
2. Выделите диапазон B2:L12 так, чтобы B2 активной ячейкой выделенного диапазона (выделяйте с верхнего левого угла B2 диапазона к нижнему правому углу до ячейки L12).
3. Не снимая выделения, введите в строку формул: =(SIN(B$13)*$A2)^2 и нажмите комбинацию клавиш CTRL+Enter. Обратите внимание, как мы используем в аргументах формулы смешанные ссылки на ячейки.
4. Между столбцами A и B вставьте новый столбец и заполните его вторую ячейку формулой: =» «&A2 (не забудьте поставить пробел между кавычками). Скопируйте эту формулу во все ячейки столбца до 12-ой строки (то есть заполните этой формулой диапазон ячеек B2:B12).
5. Выделите диапазон: B2:M12 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Поверхность».

Теперь четко видно на диаграмме что наибольшая интенсивность излучения находится в углах комнаты.

Чтобы правильно настроить горизонтальную ось X, щелкните по диаграмме, чтобы ее активировать и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».

В появившемся окне «Выбор источника данных» в правом разделе «Подписи горизонтальной оси (категории)» щелкните на кнопку «Изменить».

В окне «Подписи оси» измените значение, выделив диапазон ячеек C13:M13 и на всех диалоговых окнах нажмите ОК.

Краткое описание примера

Стоит отметить! При создании поверхностной диаграммы мы изменили числовые значения столбца A в текстовые, поместив их в столбец B с помощью формулы . Если бы мы этого не сделали, то Excel воспринял бы эти числовые значения (столбца A) как данные для построения поверхностной диаграммы, а не как подписи данных.

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

Вот в такой нехитрый способ мы красиво сделали подписи для осей диаграммы и не допустили ошибок при расчетах.

Трехмерные диаграммы (поверхности и изолинии) в EXCEL

Для начинающих пользователей 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 автоматически и нет возможности на это повлиять. Ниже приведены изолинии для упомянутого выше гиперболоида. Это особый вид диаграммы – Проволочная контурная .

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

Как построить поверхность в excel

Поверхностная диаграмма в Excel и пример ее построения

Принцип построения поверхностных диаграмм в Excel можно сравнить с рельефными картами. Где положение пункта определяется не только долготой и широтой, но и третьей величиной – высотой.

Данное сравнение поможет понять, как создать на первый взгляд сложную поверхностную диаграмму в Excel и как ее использовать.

Построение поверхностной диаграммы в Excel

Практический пример применения и создания поверхностной диаграммы в Excel.

Напряжение излучения в квадратной комнате определено формулой z=[sin(x)*y] 2 . Начало осей координат расположено центру комнаты.

Визуально сложно определить место в комнате, где наиболее интенсивное излучение. Создадим графическое представление ситуации, которое будет читабельно даже для дилетантов.

Сначала выполним все необходимые расчеты и вычисления в таблице. А поверхностную диаграмму построим на основе уже полученных данных.

1. Заполните таблицу как указано на рисунке.
2. Выделите диапазон B2:L12 так, чтобы B2 активной ячейкой выделенного диапазона (выделяйте с верхнего левого угла B2 диапазона к нижнему правому углу до ячейки L12).
3. Не снимая выделения, введите в строку формул: =(SIN(B$13)*$A2)^2 и нажмите комбинацию клавиш CTRL+Enter. Обратите внимание, как мы используем в аргументах формулы смешанные ссылки на ячейки.
4. Между столбцами A и B вставьте новый столбец и заполните его вторую ячейку формулой: =" "&A2 (не забудьте поставить пробел между кавычками). Скопируйте эту формулу во все ячейки столбца до 12-ой строки (то есть заполните этой формулой диапазон ячеек B2:B12).
5. Выделите диапазон: B2:M12 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Поверхность».

Теперь четко видно на диаграмме что наибольшая интенсивность излучения находится в углах комнаты.

Чтобы правильно настроить горизонтальную ось X, щелкните по диаграмме, чтобы ее активировать и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».

В появившемся окне «Выбор источника данных» в правом разделе «Подписи горизонтальной оси (категории)» щелкните на кнопку «Изменить».

В окне «Подписи оси» измените значение, выделив диапазон ячеек C13:M13 и на всех диалоговых окнах нажмите ОК.

Краткое описание примера

Стоит отметить! При создании поверхностной диаграммы мы изменили числовые значения столбца A в текстовые, поместив их в столбец B с помощью формулы . Если бы мы этого не сделали, то Excel воспринял бы эти числовые значения (столбца A) как данные для построения поверхностной диаграммы, а не как подписи данных.

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

Вот в такой нехитрый способ мы красиво сделали подписи для осей диаграммы и не допустили ошибок при расчетах.

Построение графиков функции и поверхностей ?в MS EXCEL

Рассмотрим пример построения поверхности z = x2 + y2 при x, y Є [-1,1].

1. В диапазон ячеек A2:A12 введем последовательность значений: –1, –0.8, …, 1 переменной x, а в диапазон ячеек B1:L1 – последовательность значений: –1, –0.8, …, 1 переменной y. В ячейку В2 введем формулу

2.Выделим эту ячейку, установим указатель мыши на её маркере заполнения и протащим его так, чтобы заполнить диапазон B2:L12.

Просмотр содержимого документа
«Построение графиков функции и поверхностей ?в MS EXCEL»

Построение графиков функции в EXCEL

Задание. Построить график функции у = sin x на отрезке [– 2; 2] с шагом

№ 1 Построить график функции у = х 3 на отрезке

[– 3; 3] с шагом h = 0,5.

№ 2 Построить график функции y= x 3 +2x на отрезке [-2;2 ] с шагом h=0,5

№ 3 Построить график функции y=2х 3 +1 на отрезке [-1;2] с шагом h=0,25

№ 4 Построить график функции y=2х 3 – 1,5х + 3 на отрезке [-5;4] с шагом h=1,5

№ 5 Построить график функции y=2х 3 -10 на отрезке [-1;2] с шагом h=0,25

Решение уравнений графическим способом в EXCEL

Задание. Решить уравнение Х 2 -5Х+6=0 графическим способом.

У=Х 2 -5Х+6

Ответ: х1=2 и х2=3

Х 2 -5Х+6=0

Х 2 -5Х= -6

У=Х 2 -5Х

Решить уравнения графическим способом

х 2 -2х-3=0

х 2 + х – 6 = 0

– х 2 + 6х – 5 = 0

Решить систему уравнений

х 2 +у 2 =9

х 2 +у 2 -1=0

х 2 +у 2 =4

у=0,5х 2 +2

Построение поверхностей в EXCEL

Рассмотрим пример построения поверхности z = x 2 + y 2 при x, y Є [-1,1].

1. В диапазон ячеек A2:A12 введем последовательность значений: –1, –0.8, …, 1 переменной x , а в диапазон ячеек B1:L1 – последовательность значений: –1, –0.8, …, 1 переменной y . В ячейку В2 введем формулу

2. Выделим эту ячейку, установим указатель мыши на её маркере заполнения и протащим его так, чтобы заполнить диапазон B2:L12 .

Выделим диапазон ячеек A1:L12 и вызовем мастер диаграмм. Тип диаграммы – Поверхность и вид – Поверхность .

Задание: Построить поверхность

и шаг табуляции 1.

В ячейки A2:A18 ввести значения переменной X.

В ячейки B1:R1 ввести значения переменной Y.

В ячейку B2 ввести формулу

=КОРЕНЬ(64-$A2*B$1), скопировать ее в ячейки B3:B18, затем в С2:R18 5.

Выделить диапазон B2:R18 и в мастере диаграмм выбрать тип диаграммы поверхность

Задание. Построить гиперболический параболоид

и шаг табуляции 0,5.

Построить поверхность

z = -sin(x 2 + y 2 )+1 при x, y Є [-1,1].

Построение поверхностей в MS EXCEL.

«Календарь счастливой жизни:
инструменты и механизм работы
для достижения своих целей»

Сертификат и скидка на обучение каждому участнику

Выбранный для просмотра документ Surf.pptx

«Управление общеобразовательной организацией:
новые тенденции и современные технологии»

Свидетельство и скидка на обучение каждому участнику

Описание презентации по отдельным слайдам:

1 слайд

ПОСТРОЕНИЕ ПОВЕРХНОСТЕЙ В MS EXCEL Муниципальное автономное общеобразовательное учреждение «Начальная общеобразовательная школа № 1» Автор: Заслуженный учитель школы Российской Федерации Никифоров Юрий Васильевич Пермский край, г. Губаха, 2013 год

2 слайд

Данная разработка урока является исследованием таких возможностей MS Excel, которые нигде не рассматриваются, но которые позволяют взглянуть на MS Excel с непривычной для многих стороны. Этот слайд можно скрыть.

3 слайд

Ключом для создания поверхностей является прямоугольная таблица, состоящая из ячеек со смешанной адресацией. =$F$1*SIN(D$3/$I$1)*COS($A11/$L$1)

4 слайд

ПРИМЕРЫ Качество прорисовки Эффект «лесенки» Более 5 млн. Блики, тень Интерференция Логические функции ЕСЛИ/И

5 слайд

ФОРМУЛЫ В файле «Формулы.xlsx» представлена таблица с формулами и условными названиями поверхностей. Имеются графические примечания.

6 слайд

АССОЦИАЦИИ В презентации «Ассоциации.pptx» представлены поверхности, которые ассоциируются с конкретными объектами окружающего мира.

Выбранный для просмотра документ Ассоциации.pptx

«Управление общеобразовательной организацией:
новые тенденции и современные технологии»

Свидетельство и скидка на обучение каждому участнику

Описание презентации по отдельным слайдам:

1 слайд

Муниципальное автономное общеобразовательное учреждение «Начальная общеобразовательная школа № 1» Автор: Заслуженный учитель школы РФ Никифоров Юрий Васильевич Пермский край, г. Губаха, 2013 год «АССОЦИАЦИИ»

2 слайд

3 слайд

4 слайд

ГИПЕРБОЛИЧЕСКИЙ ПАРАБОЛОИД ( С Е Д Л О )

5 слайд

МИТОХОНДРИЯ (Энергетический центр живой клетки организма)

6 слайд

ПИРАМИДЫ НАРОДНОСТИ МАЙЯ (Центральная Америка)

7 слайд

ЭПИТЕЛИЙ (внутренние стенки сосудов)

8 слайд

9 слайд

10 слайд

КУНГУРСКАЯ ЛЕДЯНАЯ ПЕЩЕРА СТАЛАКТИТЫ СТАЛАГМИТЫ

11 слайд

12 слайд

ЦВЕТЫ ТЮЛЬПАНЫ РОЗЫ

13 слайд

14 слайд

15 слайд

16 слайд

17 слайд

18 слайд

Выбранный для просмотра документ Библиотека поверхностей.docx

Выбранный для просмотра документ НикифоровЮВ_разработка_урока.docx

ОПИСАНИЕ РАБОТЫ

Название работы: построение поверхностей в MS Excel .

Номинация: сценарий медиаурока с компьютером.

Предмет: информатика + математика + воображение.

Участники: ученики 9-11 классов.

Цели и задачи: раскрыть графические возможности MS Excel при построении поверхностей, удивить их многообразием этих возможностей и научить строить их, обратить внимание учеников на красоту этих поверхностей.

Задачи:

— обучающие: повторение синтаксиса формулы логического условия, повторить понятие смешанной адресации, тюнинг поверхностей;

— развивающие: сформировать у учащихся ассоциации между графическими объектами и объектами окружающего мира;

— воспитательные: научить учащихся удивляться красоте поверхностей, находить «схожесть» с реальными объектами, развивать фантазию и воображение.

Данная разработка урока имеет своей целью раскрыть графические возможности табличного процессора MS Excel , которые в большинстве справочной и учебной литературы игнорируются или освещаются очень бедно.

Все эти возможности очень ярко проявляются при построении поверхностей.

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

При построении этих поверхностей не покидает чувство: неужели всё это возможно сделать в обычном и привычном табличном процессоре? Ничего подобного нигде не встречается. Самое первое чувство, которое испытываешь при работе над этой темой, это удивление. Учеников надо удивить многообразием самых фантастических форм. Вы словно открываете ящик Пандоры. Конечно, это эмоции. Но, зная, что за этой красотой и изяществом форм скрываются обычные математические формулы, ученики должны понять, что окружающий нас мир можно описать не только поэтическим языком, но и сухим языком формул.

Кому может пригодиться данная разработка? Наверное, творческим учителям, будущим художникам и дизайнерам, программистам. Всем тем, у

кого богатое воображение и абстрактное мышление тесно переплетены. Данная тема может стать основой для научно-исследовательской работы.

Автор: Никифоров Юрий Васильевич, педагог дополн. образования

Место работы: Пермский край, г. Губаха, Муниципальное автономное общеобразовательное учреждение «Начальная общеобразовательная школа № 1» (МАОУ «НОШ № 1»).

Построение поверхности

Продемонстрируем технологию построения поверхностей на примере следующей функции, зависящей от двух аргументов:

z=x 2 -y 2 при x [-2; 2], y[-1; 1].

Прежде, чем воспользоваться мастером диаграмм, надо построить таблицу значений функции z по обоим ее аргументам, например, по аргументу x от -2 до 2 с шагом 0.2, а по y от -1 до 1 с шагом 0.2. Для этого:

• 1. Введите в ячейку A2 значение — 2, а в ячейку A3 — значение -1.8. Выберите диапазон ячеек A2 :A3. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон A4 :А22. Таким образом, значения аргумента x протабулируем от -2 до 2 с шагом 0.2.
• 2. Введите в ячейку B1 значение -1, а в ячейку C1 значение -0.8. Выберите диапазон ячеек B1:C1. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон D1:L1 Значения аргумента y протабулированы от -1 до 1 с шагом 0.2.
• 3. В ячейку B2 введите формулу: =$A2^2-B$1^2
• 4. Выберите ячейку В2, расположите указатель мыши на маркере ее заполнения и протяните его вниз на диапазон В2:L22.

На левом рабочем листе рис. 2.17 показан результат табуляции функции, зависящей от двух аргументов, а на правом — часть формул, которые были введены в ячейки при копировании формулы, введенной в ячейку B2, перемещением маркера заполнения на диапазон B2:L22.

Перейдем к конструированию поверхности по результатам табуляции. Для этого:

Рисунок 2.17 Использование в формуле абсолютной ссылки на строку и столбец существенно. Напомним, что знак $ в имени ячейки, стоящий перед номером строки, создает абсолютную ссылку на строку, а перед именем столбца — абсолютную ссылку на столбец. Поэтому при копировании формулы из ячейки B2 на диапазон B2:L22, в ячейках этого диапазона будут найдены значения функции z при соответствующих значениях аргументов x и y.