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

При необходимости построения графиков зависимости от двух переменных используют тип диаграммы - Поверхность.

1. Построить график изменения числа зарегистрированных браков на 1000 жителей в период с 1977 по 1984 год в зависимости от месяца регистрации по данным, представленным в таблице.

Таблица 9. – Регистрация браков

Для построения поверхности выполнить следующие действия:

· Выделить таблицу данных, включая заголовки.

· Нажать пиктограмму Мастер диаграмм.

· Выбрать тип диаграммы Поверхность (подтип 1).

· Закончить построение диаграммы.

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

· Выполнить щелчок правой кнопкой мыши на области диаграммы и выбрать в контекстном меню команду Объемный вид.

· Установить необходимые параметры. (Внимание! Параметр Перспектива недоступен на объемных диаграммах, в которых установлен флажок Перпендикулярные оси).

3. Форматирование делений диаграммы. Тип делений можно определить в соответствии с желанием пользователя.

· Установить указатель мыши на форматируемую ось и дважды нажатье кнопку мыши. В наборе переключателей Шкала установить следующие параметры: минимальное значение 8, цена основных делений 2.

· Посмотреть, как изменится диаграмма, если установить цену основных делений 0,5.

4.Построить график функции z=x²/4+y², x меняется от -10 до 10 с шагом 1, y меняется от -1 до 1 с шагом 0,1.

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

· Перейти на новый лист.

· Ввести в ячейку А1 «x=», в ячейку А2 – «y=». Ячейка В1 в дальнейшем будет ячейкой ввода для переменной x, а ячейка В2 - для переменной y. (Ячейка ввода - это ячейка, в которую последовательно подставляются значения для заполнения таблицы данных).

· В третьей строке заполнить значения переменной x, начиная с ячейки В3 (в ячейку В3 занести значение -10, в С3 -9 и т.д. до 10).

· Записать значения переменной y в столбец А, начиная с ячейки А4 (ввести в ячейку А4 значение -1, в А5 -0,9 и т.д. до 1).

· В ячейку А3 ввести формулу = B1^2/4+B2^2.

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

· В меню Данные выбрать команду Таблица подстановки.

· В поле Подставлять значения по столбцам в ввести ссылку на ячейку ввода В1, в которую затем будут автоматически последовательно подставляться значения ячеек, расположенных в строке правее формулы. (Переменная x изменяется по столбцам!).

· В поле Подставлять значения по строкам в ввести ссылку на ячейку ввода В2, в которую будут последовательно подставляться значения ячеек, расположенных в столбце ниже формулы. (Переменная y изменяется по строкам!).

· По полученной таблице построить поверхность. Обозначить оси и задать заголовок.

5. Сохранить рабочую книгу на диске.

Лабораторная работа № 6. Расчеты в Excel

Цель работы: освоение некоторых математических функций, процедур Подбор параметра и Поиск решения.

Элементы линейной алгебры

1. Вычислить определитель квадратной матрицы , используя математическую функцию МОПРЕД. Для этого:

· Ввести исходную матрицу (каждый элемент матрицы занести в свою ячейку).

· Установить указатель мыши в свободную ячейку (для результата) и вызвать нужную функцию с помощью Мастера функций.

· В диалоговом окне ввести диапазон значений исходной матрицы.

2. Вычислить обратную матрицу квадратной матрицы, используя математическую функцию МОБР.

Для этого:

· Занести в таблицу исходную матрицу.

· В свободном месте таблицы выделить блок для обратной матрицы (размерность обратной матрицы совпадает с размерностью исходной матрицы).

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

· В диалоговом окне функции указать диапазон, в котором находится исходная матрица, нажать «OK». В выделенном под обратную матрицу блоке появится первый элемент вычисленной обратной матрицы.

· Поставить курсор в строку формул, выполнить комбинацию клавиш «Ctrl»+«Shift»+«Enter». В выделенном блоке появится вычисленная обратная матрица.

3. Вычислить произведение матриц , используя математическую функцию МУМНОЖ. Для этого выполнить действия аналогично предыдущему пункту.

4. Решить систему линейных уравнений

методом обратной матрицы.

Метод обратной матрицы заключается в следующем: пусть дана система линейных уравнений вида A*X=B, где A - матрица коэффициентов при неизвестных; X - вектор неизвестных; B - вектор свободных членов.

Тогда , где - обратная матрица матрицы A.

Для решения необходимо выполнить следующее:

· Занести в таблицу исходную матрицу коэффициентов при неизвестных.

· Занести в таблицу столбец свободных членов.

· Вычислить обратную матрицу матрицы коэффициентов при неизвестных.

· Выделить диапазон ячеек для столбца неизвестных.

· Умножить обратную матрицу на столбец свободных членов.

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

Работа с процедурой Подбор параметра

Таблица 10. – Вклады

Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле: =(1+процентная ставка)^(срок возврата вклада).

Сумма возврата вклада вычисляется по формуле:

=(размер вклада)*(коэффициент увеличения вклада)

· Скопировать таблицу. В копии, используя Подбор параметра, рассчитайте процентную ставку, при которой сумма возврата вклада будет составлять 8000 рублей.

· Скопировать исходную таблицу. В копии, используя Подбор параметра, рассчитайте срок вклада, при котором сумма возврата вклада будет составлять 8000 рублей.

Работа с процедурой Поиск решения

Таблица 11. – Штатное расписание

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

При заполнении диалогового окна Поиска решения учесть следующее:

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

· Изменяемыми ячейками (т.е. ячейками результата) являются ячейки, содержащие исходную з/п санитарки и количество санитарок, медсестер и врачей.

· В качестве ограничений нужно указать допустимый диапазон варьирования изменяемых ячеек. Например, количество санитарок по условию задачи должно быть больше 5, но меньше 7, а также целым. Ограничения заполняются посредством кнопки Добавить, с помощью которой выводится дополнительное окно, в котором нужно указать в левой части адрес изменяемой ячейки (например, адрес ячейки, содержащей количество санитарок), выбрать нужный знак (например, - <), а в правой части ввести предельно допустимое значение (например, - 5). Аналогично заполняются все другие ограничения.

2. Используя Поиск решения решить систему линейных уравнений (см. п. 4 в разделе Элементы линейной алгебры). Для этого:

· В ячейки А1:А4 ввести соответственно тексты «х1=», «х2=», «х3=», «х4=».

· В ячейки B1:B4 ввести начальные значения неизвестных (любые).

· В ячейки А6:А9 ввести формулы, соответствующие левым частям уравнений (в качестве неизвестных указывать ссылки на ячейки, содержащие начальные значения неизвестных).

· В ячейки B6:B9 ввести текст «=».

· В ячейки С6:С9 ввести столбец свободных членов.

· В ячейку А11 ввести формулу = x₁*x₂*x₃*x₄. (Внимание! В качестве x₁,x₂,x₃,x₄ в формуле указываются адреса ячеек, содержащих начальные значения неизвестных), которая будет являться формальной функцией цели.

· Запустить программу Поиск решения. Задать направление функции цели (max или min). Решение системы обеспечивается заданием жестких ограничений. В качестве ограничений использовать равенство левой и правой части каждого уравнения системы.

3. Используя Поиск решения, подберите значения срока вклада и процентной ставки, при которых сумма вклада будет составлять 8000 рублей, учитывая, что величина процентной ставки не может превышать 7% (по данным задачи 3 см. Табл.10). Для этого скопировать исходную таблицу на другой лист.

Лабораторная работа № 7. Использование сводных таблиц

Цель работы: освоение приемов создания и редактирования сводных таблиц. Работа с надстройкой Мастер сводных таблиц.

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

2. Переименовать ярлычки листов – Квартал1, Квартал2, Квартал3, Квартал4.

3. Склеить рабочие листы. Для этого нужно щелкнуть кнопкой мыши на листе Квартал1, затем нажать клавишу «Shift» и, не отпуская ее, указать мышкой оставшиеся ярлычки. Склеенные листы используются для одновременного ввода одинаковой информации на несколько листов. Ярлычки склеенных листов помечаются белым цветом.

4. Перейти на лист Квартал1. Ввести следующую шапку для таблицы:

· В ячейки В2:D2 ввести последовательно названия супермаркетов - «Европа», «Эдельвейс», «Балтика».

· В ячейки А3:А6 ввести по порядку – «Продовольственные товары», «Хозяйственные товары», «Бытовая техника», «Косметика и парфюмерия».

5. Выделить диапазон будущей таблицы – А2:D6, вызвать команду Автоформат меню Формат, в предлагаемом списке выбрать по желанию любой вид таблицы, например, Цветной 2.

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

7. Заполнить готовые шаблоны таблиц на листах исходными числовыми данными.

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

· Вызвать Мастер сводных таблиц с помощью меню Данные и команды Сводная таблица.

· В диалоговом окне Шаг 1 из 3 в разделе Создать таблицу на основе данных.. активизировать переключатель В нескольких диапазонах консолидации, так как данные расположены на различных листах рабочей книги. В разделе Вид создаваемого отчета выбрать Сводная диаграмма. В этом случае будет автоматически сформирована не только сводная таблица, но и построена сводная диаграмма по данным сводной таблицы. Нажать кнопку Далее.

· В диалоговом окне Шаг 2а из 3 выбрать переключатель Создать одно поле страницы, так как информация на листах рабочей книги отличается только одним параметром – Квартал. Перейти к следующему шагу.

· В диалоговом окне Шаг 2б из 3 указать диапазон данных таблицы. Для этого нужно щелкнуть мышкой в поле Диапазон, перейти на лист Квартал 1, выделить ячейки А2:D6, нажать кнопку Добавить. Затем перейти на лист Квартал 2 и т.д.

· Внимание! В поле Список диапазонов введенные адреса с различных листов располагаются по порядку ввода (Квартал 1, Квартал 2 и т.д.), в соответствии с которым затем заполняются строки сводной таблицы. Если ярлычки листов имеют только символьные наименования (например, Июнь, Июль, Август), то адреса диапазонов в списке будут расположены в алфавитном порядке, а не в порядке их ввода. Следовательно, и строки в сводной таблицы будут располагаться также (например, Август, Июль, Июнь).

Таблица 12. – Объем продаж товаров в крупнейших супермаркетах города, млн. руб.

· Перейти в диалоговое окно Шаг 3 из 3 и задать поле вывода сводной таблицы – Новый рабочий лист. В этом же окне вызвать кнопку Макет для организации информации в сводной таблице. По умолчанию установлен следующий макет сводной таблицы – в столбцах выводятся наименования супермаркетов, в строках – наименование товара. В этом случае суммирование объема продаж будет производиться по кварталам. По условию нужно получить сводную таблицу по общим объемам продаж по видам товаров. Для изменения алгоритма суммирования в макете таблицы нужно поменять местами поля Строка и Страница. Для этого нужно захватить мышкой нужный элемент из ряда кнопок, расположенных справа, а затем нанести его на нужное место - область строки или столбца, или страницы. После создания макета можно выбрать кнопку Параметры и задать заголовок сводной таблицы.

Цель работы: создание шаблонов и документов на их основе.

Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...

ЧТО ТАКОЕ УВЕРЕННОЕ ПОВЕДЕНИЕ В МЕЖЛИЧНОСТНЫХ ОТНОШЕНИЯХ? Исторически существует три основных модели различий, существующих между...

ЧТО ПРОИСХОДИТ ВО ВЗРОСЛОЙ ЖИЗНИ? Если вы все еще «неправильно» связаны с матерью, вы избегаете отделения и независимого взрослого существования...

Что делает отдел по эксплуатации и сопровождению ИС? Отвечает за сохранность данных (расписания копирования, копирование и пр.)...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: