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

Обновлено: 04.07.2024

Табличные процессоры. Лабораторная работа 9.
Диаграммы. Поверхности. Таблица подстановки

Линии занимают особое положение в геометрии. Используя линии, можно создать наглядные модели многих процессов и проследить их течение во времени. Линии позволяют установить и исследовать функциональную зависимость между различными величинами. С помощью линий удаётся решать многие научные и инженерные задачи, решение которых аналитическим путём часто приводит к использованию чрезвычайно громоздкого математического аппарата. Линии широко используются при конструировании поверхностей различных технических форм.

Поверхность – это плоская, двухмерная форма.
Поверхность – это площадь, которая окружает и ограничивает тело.
Поверхность – это геометрическое место линии, движущейся в пространстве по определённому закону. Эту линию называют образующей. Она может быть прямой, тогда образованную ей поверхность относят к классу линейчатых. Если образующая – кривая линия, поверхность считают нелинейчатой. Линию, по которой перемещают образующую, называют направляющей. В качестве последней иногда используют след поверхности.

Поверхности классифицируют следующим образом:

Поверхности вращения линейчатые (Все поверхности этого класса образованы вращением прямой линии вокруг другой прямой. Две прямые могут занимать относительно друг друга три различных положения. Каждому из них соответствует своя поверхность вращения).
- Конус (участвует в образовании формы диаграммы направленности антенны, поверхности положения объекта в пространстве, антенны и её облучателя, диффузора громкоговорителя, резонатора, отражателя радиоволн, электроннолучевых трубок и электронных ламп, световода, деталей вакуумных установок и так далее).
- Цилиндр (применяют при образовании формы волноводов, антенн, амортизаторов приборов, зеркал лазеров, корпусов датчиков и так далее).
- Однополостный гиперболоид (Форму однополостного гиперболоида имеют некоторые радиомачты. Он также образует форму вибрационных питателей, используемых в промышленной автоматике, кулачков, соединителей контактов и так далее).
Поверхности вращения нелинейчатые (К этому классу относят в основном поверхности, образованные вращением кривых второго порядка).
- Шар (образуют вращением окружности вокруг её диаметра).
- Тор (круговой, параболический, эллиптический). Образуют вращением окружности (или эллипса) вокруг оси, лежащей в плоскости этой окружности и не являющейся её диаметром.
- Эллипсоид (вытянутый и сжатый).
- Двуполостный гиперболоид.
- Параболоид.
- Поверхность вращения общего вида.
Поверхности с плоскостью параллелизма.
- Цилиндроид.
- Коноид (геликоид).
- Гиперболический параболоид (образуют вращением параболы вокруг её фокальной оси).
Поверхности, задаваемые каркасом (К ним относятся поверхности, образование которых не подчинено определённому геометрическому закону. Эти поверхности задают каркасом – семейством линий, принадлежащих им и параллельных координатным плоскостям).

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

Для построения поверхности необходимо задать значения переменных по 3 осям – X,Y,Z, т.е. решить уравнение с тремя неизвестными графическим путем. Рассмотрим последовательно построение различных поверхностей.

Построение плоскости

Плоскость задается уравнением 1 порядка, например: 2Z-4X+Y=0. Откуда Z=(4X-Y)/2. Сначала заполняют таблицу для построения. В ячейки А2-А11 вносим значения Х, в ячейки В1-К1 – значения Y. Формулу для Z записываем в ячейку А1. Затем выделяем блок ячеек, которые в дальнейшем будут изображены на оси Z. (Для скорейшего заполнения таблицы используем меню Данные-Таблица подстановки. В формуле записаны пустующие ячейки А12 и L1. Затем выделяем блок ячеек А1-К11. После вызова диалогового окна Таблица подстановки, подставляем значения по столбцам и строкам в соответствующие ячейки). (Для 2007: Меню Формулы – группа Решения—Поиск. Если ее нет на экране, то кН. Оффис – параметры Excel – Надстройки – в строке (внизу!) Управление: надстройки Excel – кн. Перейти – выбрать Мастер подстановок (установить флажок O) – ОК – установить компонент).

И таблица заполняется значениями Z, по которой и строится Поверхность.

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

Уравнение конуса имеет вид , где а – радиус основания конуса, с – высота конуса. (Поверхность построена при а=2, с=2).

Как сделать поверхность в excel?

Помогите построить поверхность следующей функции в Excel
Наведите на правильную дорогу!

И, если память не изменяет, Ексель строит любой график по точкам.
А не абстрактной функции.

например, можно создать матрицу точек поверхности(разумеется, рассчитанных с каким-то шагом) и построить по ней поверхность

в данном конкретном случае можно записать функцию поверхности f(x,y) с условным оператором(посмотри как это в хелпе делается)
z^2=1-x^2-y^2
типа
z=f(x,y)=if ( (1-x^2-y^2) >= 0 and (1-x^2-y^2)

Если мне не изменяет память, то x*x + y*y + z*z = 1 — уравнение сферы единичного радиуса. Что-то я сомневаюсь, что Excel умеет по точкам сферы строить.

Это Excel так отрисовывает? Я ж говорю — не умеет.

> [5] default © (07.11.06 19:36)
Как же это может быть не сфера, если приведена формула геометрического места точек, равноудалённых от центра? %-)

Причем, это сфера с центром в начале координат.
Ибо смещения не наблюдается.

z=f(x,y)=if (1-x^2-y^2) >= 0
then return sqrt(1-x^2-y^2)
else return 0
только

> [3] default © (07.11.06 19:27)

> (1-x^2-y^2) then return sqrt(1-x^2-y^2)

Щаз! А отрицательный результат не учитываем? По математике (да и по совести) надо бы +-sqrt().

Чапаев © (07.11.06 20:03) [12]
просто функция распадётся на две функции(поверхности) с условным оператором да и всё
строим две поверхности на одном графике
а если ещё режим xor возможен, то тогда сфера в чистом виде предстанет

> с условным оператором
Куда там условный оператор? Придётся отдельно верхнюю и нижнюю полусферы отрисовывать. Да, не проблема космических масштабов, но и хорошего мало.

Чапаев © (07.11.06 20:13) [14]
я об этом и говорю
условный оператор необходим всё равно

автор, строй короче по расчётным точкам, подход с условным оператором дал плохую картинку

x(u, v) = cos(u) * cos(v)
y(u, v) = sin(u) * cos(v)
z(u, v) = sin(v)
параметрическое уравнение. Ёксель должен его осилить.

ferr © (07.11.06 21:45) [17]
ага
интересно, сам вывел?
(это просто, но вариантов параметризации не один)

Всем спасибо за ответы!
НО!

Ssory.
Не тот файл залил!

> [10] Чапаев © (07.11.06 19:45)
> > [3] default © (07.11.06 19:27)
>
>
> > (1-x^2-y^2)
> А бывает и больше единицы.

Бывает, но только в военное время, или когда x или y комплексные.

> [22] SergP © (13.11.06 05:28)
Тогда уж не комплексные, а мнимые. 😉

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

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

Никто не может без Excel

Раскрывая широкие возможности Excel 2000 в статье подобного плана, бывает достаточно сложно не увязнуть в описании бесконечных возможностей программы. Поэтому рассмотрим весьма типичный, но конкретный пример. В качестве анализируемой функции выберем температурное поле.

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

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

Поле температур описывается следующим выражением и является стационарным, то есть не зависит от времени:

где l — коэффициент теплопроводности материала;

q — мощность источника.

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

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

Задание сеточной области и расчет значений функции в узловых точках

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

В ячейках B1 и С1 поместим имена параметров: мощность источника — q и теплопроводность материала — lam, а в ячейках B2 и С2 соответственно — значения этих параметров.

К примеру, выберем теплопроводность 0,4 (Вт/(смЅград)), что соответствует теплопроводности стали, а источник теплоты возьмем мощностью 1000 Вт.

Далее определим сетку X, Y. Для этого в ячейках с D3 по N3 зададим значения Х с шагом 0,2 см, при этом во избежание деления на ноль в формуле сместим сетку относительно начала координат на 0,001 см, то есть вместо значения –1 см по оси Х, начнем со значения –1,001 см.

На рис. 1 видно, что в каждой ячейке на сеточной области, ограничиваемой строкой, где заданы значения X, и столбцом, где заданы значения Y, посчитаны значения температуры.

Значения в каждой ячейке посчитаны по одной и той же формуле для своего значения X и Y . Так, для ячейки E4 формула показана в строке формул (рис. 1):

Данная формула является записью приведенного выше выражения с учетом правил, принятых в Excel. В частности:

запись $B$2 означает, что переменная в ячейке с адресом B2 (в данном случае — мощность источника) является константой и соответственно не зависит от адреса ячейки, в которой она вычисляется (знак доллара, указанный перед номером строки и столбца, обозначает, что ссылка на данную ячейку является абсолютной и по строке, и по столбцу);
запись $С$2 означает, что переменная в ячейке с адресом B2 (в данном случае — теплопроводность материала) не зависит от адреса ячейки, в которой она вычисляется;
E$3 означает, что переменная (в данном случае — X) в ячейке E4 будет иметь относительный адрес столбца — Е (относительный в том смысле, что в ячейках разных столбцов он разный и зависит от индекса столбца) и абсолютный адрес строки — 3, то есть для любой ячейки значение переменной Х выбирается из строки 3 (на рис. 1 выделена коричневым цветом);
$C4 означает, что переменная (в данном случае — Y) в ячейке E4 будет иметь абсолютный адрес — столбец С (абсолютный в том смысле, что для любой ячейки значение переменной Y выбирается из столбца с индексом С; на рис. 1 столбец выделен желтым цветом) и относительный адрес строки 4 (относительный в том смысле, что в ячейках разных строк он разный и зависит от номера строки).

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

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

Для построения графика искомой функции прежде всего следует выделить область, которая включает значения температур в узлах сетки и значения X и Y, как показано на рис. 2.

Затем следует вызвать панель мастера диаграмм (Chart Wizard) и выбрать в этой панели необходимый тип представления диаграммы, в данном случае — двухмерную поверхность.

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

По нажатии кнопки Далее (Next) появится новая панель помощника построения диаграмм, содержащая несколько вкладок. Первая — Названия (Titles) позволяет дать название графику и сделать обозначения всех осей.

Последний шаг из четырех предложенных помощником построения диаграмм заключается в выборе расположения подготовленного вами графика: на отдельном листе или рядом с табличными данными.

После выбора соответствующих масштабов и шрифтов для надписей, а также перемещения отдельных элементов на поле графика, распределение функции может принять, например, такой вид, как показано на рис. 7.

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

К примеру, если вас интересует только та часть температурного поля, которая относится к твердой зоне, вы можете ограничить рассматриваемую часть температурного поля изотермой температуры плавления (например, для стали это 1500 °С).

Для этого достаточно щелкнуть мышью по оси температур, в результате чего появится панель Форматирование оси (Format Axis), в которой необходимо выбрать вкладку масштаб (Scale). В данной вкладке можно изменить диапазон температур, в котором будет представлена функция.

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

Характер представления данных можно изменить в любой момент. Для этого достаточно щелкнуть мышью на пустом месте рядом с графиком, в результате чего появится панель, в которой можно поменять тип графика (Chart Type), область определения исходных данных (Source Data), параметры диаграммы (Chart Options), место расположения графика (Location).

Здесь же имеется опция, позволяющая менять угол зрения на построенную поверхность и деформировать ее как трехмерный объект (3D View).

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

Если вас не устраивает трехмерный объект — к примеру, вас интересует размер изотермы плавления, а на объемной поверхности этот размер определять неудобно, — нет проблем! Вернуться к плоскому отображению данных очень легко.

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

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

Неcмотря на искажения, по графику достаточно легко определить примерные размеры изотермы плавления (рис. 11 она составляет около 2 мм в диаметре).

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

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

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

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

Функция - это множество точек (x, y), удовлетворяющее выражению y=f(x).

1) График линейной функции: y=5x-2

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

Для построения графика необходимо выделить полученную таблицу и выбирать опцию меню: Вставка - Точечная – Точечная с гладкими кривыми и маркерами.

Замечание. Для изменения параметров осей используйте опцию меню Конструктор – Выбрать данные

2) График квадратичной функции – параболы y=2x 2 -2

П араболу по двум точкам уже не построить, в отличии от прямой.

Создадим таблицу, выполним вычисления:

Зададим интервал на оси x, на котором будет строиться парабола, например [-5; 5].

Зададим шаг (чем меньше шаг, тем точнее будет построенный график), например, 0,2.

Рассчитаем столбец значений у.

Действуем аналогично построению графика линейной функции.

П олучим:

Замечание. Чтобы не было точек на графике, поменяйте тип диаграммы на Точечная с гладкими кривыми.

Р ассмотрим это на примере функции у=1/х.

Функция определена на интервалах (- беск;0) и (0; +беск)

Создадим график функции на интервалах: [-4;0) и (0; 4].

Подготовим две таблицы, где х изменяется с шагом 0,2:

Находим значения функции от каждого аргумента х аналогично примерам выше.

На диаграмму необходимо добавить два ряда - для первой и второй таблички соответственно.

Далее нажимаем кнопку Добавить и заполняем таблицу Изменение ряда значениями из второй таблицы.

Рассмотрим пример построения поверхности 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.

Построение графиков функции в 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 .