к лабораторной работе № 6

Министерство образования и науки Российской федерации

Федеральное государственное автономное образовательное

Учреждение высшего образования

«Севастопольский государственный университет»

Кафедра информационных технологий и компьютерных систем

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

к лабораторной работе № 6

на тему «Создание диаграмм и решение нелинейных

уравнений в Microsoft Office Excel 2007»

по дисциплине «Информатика»

для студентов направления 38.03.01 – Экономика,

(профиль – «Экономика предприятий и организаций»)

Севастополь

Методические указания разработали доценты кафедры информационных технологий и компьютерных систем Лелеков С.Г., Щепин Ю.Н.

СОДЕРЖАНИЕ

1. Создание диаграммы.. 4

1.1. Диалоговое окно «Форматирование элементов диаграммы». 6

1.2. Изменение отображения осей диаграммы.. 8

1.2.1. Отображение и скрытие осей. 8

1.2.2. Настройка делений и подписей делений оси. 9

1.2.3. Изменение числа категорий между подписями и делениями. 9

1.2.4. Изменение выравнивания и ориентации подписей. 10

1.2.5. Изменение текста подписей категорий. 10

1.2.6. Изменение формата текста и чисел в подписях. 11

1.2.7. Изменение пересчета. Итерации и точности формулы.. 12

1.2.8. Изменение точности вычислений в книге. 13

2. Примеры построения диаграммы.. 14

2.1. Линейный вычислительный процесс. 14

2.2. Построение графика функции с одним условием.. 20

2.3. Построение графика функции с двумя условиями. 21

2.4. Построение двух графиков в одной системе координат. 21

2.5. Построение поверхности. 22

2.6. Нахождение корней нелинейного уравнения. 22

3. Постановка задач. 24

4. Форма отчетности. 24

5. Варианты заданий. 24

Библиографический список. 37

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

1. Создание диаграммы

В Microsoft Excel можно легко и быстро создавать диаграммы, при этом предусмотрен большой выбор типов создаваемых диаграмм.

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

Упорядочьте данные, которые будут использоваться для построения диаграммы:

- для гистограмм, графиков с областями, поверхностей, линейчатых и лепестковых диаграмм упорядочивание проводят по столбцам и строкам;

- для круговых и кольцевых диаграмм (для одного ряда) - в одну строку или столбец данных и в одну строку или столбец подписей;

- для круговых и кольцевых диаграмм - в несколько строк или столбцов данных и, соответственно, в одну строку или столбец подписей;

- для точечных или пузырьковых диаграмм поместите значения оси X в первый столбец, а соответствующие значения оси Y и/или размеры пузырьков — в соседние столбцы;

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

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

В группе Диаграммы на вкладке Вставка (Рисунок 1) выполните одно из следующих действий.

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

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

Рисунок 1. Группа Диаграммы

Если задержать указатель мыши на одном из видов или подвидов диаграммы, появится всплывающая подсказка с названием вида диаграммы.

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

Чтобы изменить размещение диаграммы, щелкните внедренную диаграмму или лист диаграммы для отображения инструментов для работы с диаграммой.

На вкладке Конструктор (Рисунок 2) в группе Расположение нажмите кнопку Переместить диаграмму.

Рисунок 2. Группа Расположение

В разделе Разместить диаграмму выполните одно из следующих действий:

- для вывода диаграммы на лист диаграммы выберите параметр На отдельном листе (если необходимо заменить предложенное имя диаграммы, введите новое имя в поле На отдельном листе);

- для вывода диаграммы в виде внедренной диаграммы на листе выберите параметр На имеющемся листе и выберите лист в поле На имеющемся листе.

Чтобы быстро создать диаграмму на основе типа диаграммы по умолчанию, выберите данные, которые следует использовать для ее построения, и нажмите клавиши ALT+F1 или F11. При нажатии клавиш ALT+F1 диаграмма будет отображена как внедренная диаграмма; при нажатии клавиши F11 - на отдельном листе диаграммы.

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

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

1.1. Диалоговое окно «Форматирование элементов диаграммы»

Можно отдельно форматировать следующие элементы диаграммы:

- область диаграммы;

- область построения диаграммы (на плоских диаграммах— ограниченная осями область, содержащая все ряды данных, на объемных диаграммах— ограниченная осями область, содержащая все ряды данных, имена категорий, подписи делений и названия осей);

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

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

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

- подписи данных (подпись с дополнительными сведениями о маркере данных, представляющем одну точку данных или значение ячейки листа);

- легенду (рамка, в которой определяются узоры или цвета рядов или категорий данных на диаграмме).

Щелкните диаграмму.

Появится группа Работа с диаграммами с вкладками Конструктор, Макет и Формат.

На вкладке Формат в группе Текущий фрагмент (Рисунок 3) щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите элемент диаграммы, который нужно форматировать.

Рисунок 3. Группа Текущий фрагмент

Чтобы форматировать выделенный элемент диаграммы, в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента, а затем выберите нужные параметры форматирования.

Чтобы форматировать фигуру выделенного элемента диаграммы, в группе Стили фигур выберите нужный стиль либо нажмите кнопку Заливка фигуры, Контур фигуры или Эффекты для фигур, а затем выберите нужные параметры форматирования.

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

Для обычного форматирования текста в элементах диаграммы нужно выделить этот текст или щелкнуть правой кнопкой мыши, а затем выбрать нужные параметры форматирования на панели Мини-панель инструментов. Можно также воспользоваться кнопками форматирования на ленте (вкладка Главная, группа Шрифт).

На вкладке Макет выполните следующие действия.

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

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

В группе Фон выберите необходимый параметр макета диаграммы.

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

1.2. Изменение отображения осей диаграммы

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

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

1.2.1. Отображение и скрытие осей

Щелкните диаграмму, на которой нужно отобразить или скрыть оси.

Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

На вкладке Макет в группе Оси (Рисунок 4) нажмите кнопку Оси.

Рисунок4. Группа Оси

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

Чтобы скрыть ось, выберите тип оси, которую нужно скрыть, и затем выберите параметр Нет.

1.2.2. Настройка делений и подписей делений оси

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

На вкладке Формат в группе Текущий фрагмент нажмите стрелку рядом с полем Область диаграммы, а затем щелкните ось, которую нужно выбрать.

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

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

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

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

Скрыть деления или подписи делений можно, выбрав параметр Нет.

1.2.3. Изменение числа категорий между подписями и делениями

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

Откройте страницу Параметры оси, если она еще не открыта, а затем выполните по выбору следующее. Чтобы изменить интервал между подписями оси, в разделе Интервал между подписями выберите параметр Укажите единицу интервала, а затем введите нужное число в текстовом поле. Чтобы подписи отображались для каждой категории, введите значение 1, чтобы они отображались через одну категорию, введите 2, для каждой третьей категории — 3, и т.д.

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

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

1.2.4. Изменение выравнивания и ориентации подписей

Изменять выравнивание подписей оси можно как на горизонтальной оси (оси категорий), так и на вертикальной оси (оси значений). При наличии в диаграмме многоуровневых подписей категорий (подписи категорий на диаграмме, отображаемые в виде структуры в зависимости от данных в ячейках на листе. Например, заголовок «Студенты» может отображаться над строкой с заголовками «ЭП-11д», «ЭП-12д») можно изменять выравнивание всех уровней подписей. Также можно изменять расстояние между уровнями подписей на горизонтальной оси (оси категорий).

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

Выделите нужную ось. На вкладке Выравнивание в области Формат текста выберите нужное положение в поле Вертикальное выравнивание.

Чтобы изменить ориентацию текста, выберите нужное положение в поле Направление текста.

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

1.2.5. Изменение текста подписей категорий

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

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

Чтобы изменить подписи категорий в диаграмме, щелкните горизонтальную ось диаграммы или выполните следующие действия для выбора оси в списке элементов диаграммы:

На вкладке Конструктор в группе Данные (Рисунок 5) нажмите кнопку Выбрать данные.

Рисунок 5. Группа Данные

В разделе Подписи горизонтальной оси (оси категорий) нажмите кнопку Изменить.

В поле Диапазон подписей оси задайте диапазон листа, который нужно использовать в качестве подписей оси категорий, или введите нужные подписи, разделенные запятыми, например, Группа ЭП-11д, Группа ЭП-12д.

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

Если текст подписи вводится в поле Диапазон подписей оси, текст подписи оси категорий перестает быть связанным с ячейкой листа.

1.2.6. Изменение формата текста и чисел в подписях

Чтобы отформатировать текст в подписях, выполните следующие действия.

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

Можно также использовать кнопки форматирования на ленте (на вкладке Главная в группе Шрифт).

Чтобы задать формат для чисел в подписях, выполните следующие действия.

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

Выберите значение Формат чисел, а затем в поле Категория выберите нужный формат числа.

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

Чтобы числа оставались связанными с ячейками листа, установите флажок Связь с источником.

1.2.7. Изменение пересчета по формулам. Итерации и точности формулы

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

Необходимо ознакомиться с тремя ключевыми понятиями.

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

Итерация - это многократный пересчет листа до удовлетворения определенного числового условия. Excel не может автоматически рассчитать значение по формуле, которая ссылается (прямо или косвенно) на ячейку, содержащую формулу (это называется циклической ссылкой). Если формула содержит обратную ссылку на одну из своих собственных ячеек, необходимо определить, сколько раз следует пересчитывать формулу. Циклические ссылки могут пересчитываться до бесконечности. Однако существует возможность управления максимальным числом итераций и количеством допустимых изменений.

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

Щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Формулы.

В разделе Параметры вычислений установите флажок Включить итеративные вычисления.

Для установки максимального числа пересчетов введите в поле Предельное число итераций число итераций. Чем больше число итераций, тем больше времени потребуется для пересчета листа.

Для установки максимальной разности между результатами пересчетов введите ее в поле Относительная погрешность. Чем меньше это число, тем точнее результат, и тем больше времени требуется на пересчет листа.

Команды «Поиск решения» и «Подбор параметра» являются частью блока команд, иногда называемого инструментами Анализа «что-если» (процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе). Обе команды для получения требуемых результатов используют управляемые итерации. Если необходимо найти оптимальное значение для одной ячейки, изменяя значения в других ячейках, или установить конкретные ограничения для одного или нескольких значений при вычислениях, воспользуйтесь командой «Поиск решения».

1.2.8. Изменение точности вычислений в книге

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

По умолчанию в вычислениях используются хранимые, а не отображаемые значения. Отображаемые и печатаемые значения зависят от выбора формата и отображения хранимых значений. Например, ячейка, в которой отображается дата «22.11.2012», также содержит порядковый номер, которое является хранимым значением этой даты в этой ячейке. Можно изменить отображение даты на другой формат (например «22-ноябрь-2012»), но хранимое в ячейке значение не изменится.

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

При изменении точности вычислений в книге путем использования отображаемых (форматированных) значений Excel всегда меняет точность хранимых в ячейках значений от полной (15 цифр) до любого другого числа десятичных знаков отображаемого формата. После такого изменения возврат к исходной точности будет невозможен.

Щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Дополнительно.

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

2. Примеры построения диаграммы

2.1. Линейный вычислительный процесс

В качестве примера рассмотрим процедуру построения графика функции y=cos2(px) при хÎ [-0, 5; 0, 5].

Введем в ячейки А1 и В1 названия полей - X и Y, соответственно.

Для построения графика функции необходимо сначала построить таблицу ее значений (протабулировать функцию) при различных значениях аргумента, причем обычно аргумент изменяется с фиксированным шагом. Шаг выбирают небольшим, чтобы таблица значений функции отражала ее поведение на интервале табуляции. В нашем случае будем считать, что шаг изменения аргумента равен 0, 1. Необходимо найти значения функции y при различных значениях аргумента x:

y(-0.5), y(-0.4),..., y(0, 5).

С этой целью в диапазон ячеек А2: А12 введем следующие значения переменной х: -0, 5; -0, 4; …; 0, 5. Отметим, что выбранные нами значения переменной образуют арифметическую прогрессию. Заполнение ячеек членами арифметической прогрессии в Excel можно осуществить двумя способами:

- Первый способ. В ячейки А2 и А3 вводим первый и второй члены арифметической прогрессии и выделяем эти ячейки. После этого устанавливаем указатель мыши на маркере заполнения выделенного диапазона (маленький черный квадратик в правом нижнем углу) и протаскиваем его вниз до тех пор, пока не получится числовой ряд нужной длины.

- Второй способ. В ячейку А2 вводим первый член арифметической прогрессии. На вкладке Главная в группе Редактирование выбираем команду Заполнить, Прогрессия и в открывшемся диалоговом окне Прогрессия в группе Расположение устанавливаем переключатель в положение По столбцам, а в группе Тип — в положение Арифметическая. В поле Шаг вводим значение 0, 1, а в поле Предельное значение - 0, 5. После нажатия кнопки ОК будет выполнено построение прогрессии.

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

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

=СОS(ПИ()*А1)^2

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

Рассмотрим приемы работы с мастером функций на примере ввода вышеупомянутой формулы. Выделим ячейку В2 и нажмем кнопку f_x.

Это окно содержит два списка: Категория - список, включающий 11 категорий функций, и Выберите функцию — список имен функций, входящих в выбранную категорию. Категория Полный алфавитный перечень содержит все встроенные функции Excel, имена которых упорядочены в алфавитном порядке. Категория 10 недавно использовавшихся содержит имена десяти недавно использовавшихся функций, она ускоряет вызов функций, постоянно используемых пользователем.

Функция COS относится к категории Математические. Выберем эту функцию и нажмем кнопку ОК. На экране появится второе диалоговое окно Мастера функций.

В поле Число второго диалогового окна Мастера функций вводится аргумент функции. В рассматриваемом примере это ПИ() *А1. Конечно, его можно ввести с клавиатуры. Однако этот аргумент содержит встроенную функцию ПИ(), поэтому лучше, нажав кнопку Открытие списка, расположенную в Строке формул, найти команду Другие функции и, вернуться в мастер функций, найти функцию ПИ() в категории Математические.

Внимание! Так как функция ПИ() не имеет аргументов, то чтобы вернуться в диалоговое окно функции COS, укажите мышью на уже набранное имя функции COS в строке формул и щелкните левой кнопкой мыши.

В поле Число с помощью клавиатуры введем знак умножения * и, щелкнув ячейку А2 рабочего листа, введем адрес А2. Конечно, ссылку на ячейку можно ввести также с помощью клавиатуры, однако, такой способ ввода (щелчком на соответствующей ячейке) дает дополнительную проверку правильности ввода. После нажатия кнопки Готово в ячейку В2 будет введена формула

=COS(ПИ()*А2)

Теперь с помощью клавиатуры добавьте в строке формул операцию возведения в квадрат ^2. После всех описанных действий в ячейку В2 будет введена формула

=COS(ПИ()*А2)^2

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

Итак, таблица значений функции создана.

Для построения графика функции выделим диапазон ячеек В2: В12, содержащий таблицу значений функции y, и выберем шаблон диаграммы График с маркерами.

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

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

Появившийся на рабочем листе график необходимо отформатировать.

Теперь отформатируйте различные элементы построенной диаграммы в соответствии с приведенными ниже рисунками (например, вид основных меток делений, пересекающих ось категорий, названия оси категорий X, оси значений Y и так далее), каждый раз вызывая контекстное меню наведением указателя мыши на форматируемый элемент диаграммы и щелкая правой кнопкой мыши. Обязательно проверьте вид диаграммы в окне предварительного просмотра (именно таким образом будет выглядеть диаграмма при печати документа). На рисунке 6 представлен вид диаграммы в окне предварительного просмотра. Обратите внимание, что для примера отформатированы элементы диаграммы:

- замените на диаграмме номера категорий (ось x) на значения категорий, для чего в режиме Конструктор в группе Данные щелкните по кнопке Выбрать данные. В диалоговом окне Выбор источника данных (Подписи горизонтальной оси (категории)) щелкните по кнопке Изменить, при открывшемся окне Подписи оси протяните указатель мыши по диапазону А1: А12. Не закрывая окно Выбор источника данных, измените имя легенды (условное обозначение кривой на графике) с Ряд 1 на имя y, щелкните по кнопке ОК;

- ось значений Y пересекает ось категорий Х в категории номер 6 (контекстное меню Формат горизонтальной оси: Вертикальная ось пересекает в категории с номером 6);

- на оси категорий метки делений пересекают ось по делениям (контекстное меню Формат горизонтальной оси: Положение оси по делениям; Основные пересекают ось);

- отдельно (в желтой заливке) представлена подпись значения функции Y при значении аргумента Х=0, 2 (два щелчка по соответствующему маркеру, из контекстного меню Формат точки данных выбрать команду Добавить подпись данных, после чего вызвать контекстное меню для Формата подписи данных выбрать для Число (Числовой формат) Число десятичных знаков, равное 2;

- на вкладке Вставка в группе Иллюстрации из списка Фигуры выбраны прямая со стрелкой и прямая соединительная линия 2, для которой через контекстное меню Формат объекта выбран Тип штриха;

- выбраны размеры шрифта для названия диаграммы, подписей осей, значения точки ряда (0, 65), легенды таким образом, чтобы они соответствовали размеру области построения диаграммы.

Рисунок 6. Окно предварительного просмотра с отформатированной диаграммой функции y=cos2(px) при хÎ [-0, 5; 0, 5]

Для того чтобы скопировать содержимое активного окна, нажмите сочетание клавиш ALT+PRINT SCREEN. Чтобы скопировать все содержимое экрана, отображающееся на мониторе, нажмите клавишу PRINT SCREEN. Чтобы вставить это изображение в файл приложения Microsoft Office, откройте файл, в который нужно вставить изображение.

На рисунке 7 представлено изображение листа с таблицей значений функции y=cos²(px) и построенной диаграммой).

Рисунок 7. Лист с таблицей значений функции y=cos2(px) и диаграммой

Чтобы на листе Excel вместо значений рядов отобразить формулы, по которым эти значения вычислены, в меню Формулы откройте список Зависимости формул, а затем выберите пункт Показать формулы. Для примера на рисунке 8 представлено изображение листа с формулами и диаграммой.

Рисунок 8. Лист с таблицей формул функции y=cos²(px) и диаграммой

Если на вкладке Конструктор в группе Расположение щелкнуть Переместить диаграмму, можно выбрать размещение диаграммы на отдельном листе (Рисунок 9), при этом таблица со значениями данных и рядов не отображается.

Рисунок 9. Диаграмма на отдельном листе

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

2.2. Построение графика функции с одним условием

Рассмотрим пример построения графика функции

=ЕСЛИ(А2< 0, 2; (1+ABS(0, 2-A2))/(1+A2+A2^2); A2^(1/3))

Синтаксис логической функции ЕСЛИ (IF):

ЕСЛИ(логическое выражение; значение, _если_истина; значение, _если_ложь)

Функция ЕСЛИ возвращает значение, _если_истина, если логическое_выражение имеет значение ИСТИНА (TRUE), и значение, _если_ложь — если логическое_выражение имеет значение ложь (FALS).

Функция ЕСЛИ используется для проверки значений формул и организации переходов в зависимости от результатов этой проверки.

2.3. Построение графика функции с двумя условиями

Рассмотрим пример построения графика

График строится так же, как в подразделе 2.1, только в ячейку В2 вводится формула:

=EСЛИ(A2< -0, 2; 1+LN(1+A2); ЕСЛИ(A2> 0, 8; 2*ЕХР(-2*А2); (1+КОРЕНЬ(А2))/(1+А2)))

2.4. Построение двух графиков в одной системе координат

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

g = 2sin(x) и

z = 3cos(2x) — sin(x)

при хÎ [-3, 0].

В диапазон ячеек А2: А17 вводим значения переменной x: от -3 до 0 с шагом 0, 2. В ячейки А1, В1 и С1 вводим X, G и Z, соответственно. В ячейки В2 и С2 вводим формулы:

=2*SIN (A2)

=3*COS (2*A2)-SIN (A2)

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

Для наглядности, графики функций G и Z рекомендуется различать по типу линий.

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

Рассмотрим пример построения поверхности

при х, zÎ [-1, 1].

В диапазон ячеек B1: L1 введем последовательность значений:

-1, -0.8,..., 1 переменной х, а в диапазон ячеек А2: А12 — последовательность значений: -1, -0.8,..., 1 переменной z. В ячейку В2 введем формулу

=$A2^2-B$1^2

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

Знак $, стоящий перед буквой в имени ячейки, дает абсолютную ссылку на столбец с данным именем, а знак $, стоящий перед цифрой — абсолютную ссылку на строку с этим именем. Поэтому при протаскивании формулы из ячейки В2 в ячейки диапазона B2: L12 в них будет найдено значение y при соответствующих значениях х и z. Чтобы набрать абсолютную ссылку (например, $A2), в строке формул установите курсор перед адресом А2 и дважды щелкните по функциональной клавише F4.

Итак, таблица значений функции y при различных значениях переменных создана.

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

2.6. Нахождение корней нелинейного уравнения

Рассмотрим пример нахождения всех корней уравнения

х³ - 0, 01х² - 0, 7044х + 0, 139104 = 0.

Отметим, что у полинома третьей степени имеется не более трех вещественных корней. Для нахождения корней их предварительно нужно локализовать. С этой целью необходимо построить график функции или ее протабулировать. Например, протабулируем наш полином на отрезке [-1, 1] с шагом 0, 2. Для этого в ячейки А2: А11 введем значения х, в ячейку В2 введем следующую формулу:

=A2^3-0.01*A2^2-0.7044*A2+0.139104

Построим график функции, как указано в подразделе 2.1.

Из графика видно, что полином меняет знак на интервалах: [-1, -0, 8], [0, 2, 0, 4] и [0, 6, 0, 8]. Это означает, что на каждом из них имеется корень данного полинома. Поскольку полином третьей степени имеет не более трех действительных корней, значит, мы локализовали все его корни.

Возьмем, например, их средние точки: -0, 9; 0, 3; и 0, 0, 37 и введем их соответственно в диапазон ячеек С2: С4. В ячейку D2 введем формулу

=С2^3-0, 01*С2^2-0, 7044*С2+0, 139104

Выделим эту ячейку и с помощью маркера заполнения протащим введенную в нее формулу в диапазоне D2: D4. Таким образом, в ячейках D2: D4 вычисляются значения полинома при значениях аргумента, введенного в ячейки С2: С4, соответственно.

Зададим относительную погрешность и предельное число итераций, равными 0, 00001 и 1000, соответственно (см. подраздел 1.2.7).

Найдем корни полинома методом последовательных приближений с помощью команды Подбор параметра. Для этого на вкладке Данные в группе Работа с данными выберите Анализ «что-если», Подбор параметра, далее выполните следующие действия, характерные для подбора праметра:

- в поле диалогового окна Установить в ячейке введите ссылку на ячейку D2 (в этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения);

- в поле Значение введите 0 (в этом поле в данном случае указывается значение правой части уравнения). Для нахождения корня уравнения с помощью средства подбора параметров надо записать уравнение так, чтобы его правая часть не содержала переменную;

- в поле Изменяя значение ячейки введите ссылку на ячейку С2 (в этом поле дается ссылка на ячейку, отведенную под переменную).

Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры, открываемого командой Сервис, Параметры.

Вводить ссылки на ячейки в поля диалогового окна Подбор параметра удобнее не с клавиатуры, а щелчком на соответствующей ячейке. При этом Excel автоматически будет превращать их в абсолютные ссылки (в нашем примере $D$2 и $C$2).

После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня, которое помещает в ячейку С2. В данном случае оно равно -0, 9199995441260787.

Аналогично в ячейках СЗ и С4 находим два оставшихся корня. Они равны, соответственно, 0, 2100004312921519 и 0, 719999779247747.

3. Постановка задач

Создайте книгу Excel с именем «Диаграммы». Постройте и отформатируйте диаграммы и найдите корни нелинейного уравнения в соответствии с вариантом (раздел 5). Каждую диаграмму с таблицами данных представьте на отдельном листе, для каждой диаграммы создайте лист с формулами.

4. Форма отчетности

По результатам выполнения работы к защите необходимо представить:

(1) Отчет по установленной форме (с распечаткой созданных в соответствии с заданием листов диаграмм с таблицами и листов диаграмм с формулами).

(2) Созданные таблицы в электронном виде.

5. Варианты заданий

Номер варианта определяется порядковым номером фамилии студента в журнале академической группы.

Вариант 1

1. Построить в разных системах координат при хÎ [-2; 1, 5] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-2; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 2

1. Построить в разных системах координат при хÎ [-1, 5; 1, 5] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-2; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1]:

·

1. Найти все корни уравнения .

Вариант 3

1. Построить в разных системах координат при хÎ [-1, 8; 1, 8] графики функций:

·

·

·

2. Построить в одной системе координат при хÎ [0; 3] графики функций:

·

·

3. Построить поверхность при х, у Î [-1; 1].

4. Найти все корни уравнения .

Вариант 4

1. Построить в разных системах координат при хÎ [-2; 1, 8] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-3; 0] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 5

1. Построить в разных системах координат при хÎ [-1, 7; 1, 5] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-3; 0] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 6

1. Построить в разных системах координат при хÎ [-1, 5; 1, 8] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].

1. Найти все корни уравнения .

Вариант 7

1. Построить в разных системах координат при хÎ [-1, 4; 1, 9] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].

1. Найти все корни уравнения .

Вариант 8

1. Построить в разных системах координат при хÎ [-1, 4; 1, 4] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 9

1. Построить в разных системах координат при хÎ [-1, 8; 1, 8] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 3] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 10

1. Построить в разных системах координат при хÎ [-2; 1, 8] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-3; 0] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 11

1. Построить в разных системах координат при хÎ [-1, 7; 1, 5] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-3; 0] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].

4. Найти все корни уравнения .

Вариант 12

1. Построить в разных системах координат при хÎ [-1, 5; 1, 8] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 13

1. Построить в разных системах координат при хÎ [-1, 4; 1, 9] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 14

1. Построить в разных системах координат при хÎ [-1, 4; 1, 4] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].

1. Найти все корни уравнения .

Вариант 15

1. Построить в разных системах координат при хÎ [-1, 4; 1, 4] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 16

1. Построить в разных системах координат при хÎ [-1, 8; 1, 8] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 3] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 17

1. Построить в разных системах координат при хÎ [-2; 2] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [0; 3] графики функций:

·

·

1. Построить поверхность z=x²-2y², при x, yÎ [-1, 1].
2. Найти все корни уравнения х³ - 2, 92x²+1, 4355х+0, 791136 = 0. .

Вариант 18

1. Построить в разных системах координат при хÎ [-2; 2] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-2; 2] графики функций:

· у = 2sin(px) - 3cos(px),

z=cos²(2px) - 2sin(px)

1. Построить поверхность z=3x² - 2sin²(y)y², при x, yÎ [-1, 1].
2. Найти все корни уравнения х³ - 2, 56x²-1, 325х+4, 39506 = 0.

Вариант 19

1. Построить в разных системах координат при хÎ [-2; 1, 5] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-2; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1].
2. Найти все корни уравнения .

Вариант 20

1. Построить в разных системах координат при хÎ [-1, 5; 1, 5] графики функций:

·

·

·

1. Построить в одной системе координат при хÎ [-2; 2] графики функций:

·

·

1. Построить поверхность при х, у Î [-1; 1]:

·

1. Найти все корни уравнения .

Вариант 21

1. Построить в разных системах координат при хÎ [-1, 8; 1, 8] графики функций:

·

·