Лабы 1 курс 1 семестр / ЛР №6 Excel. Графики и сводные таблицы
.pdf
Лабораторная работа «Графики и сводки в Excel»
Цель работы: научиться создавать графики и диаграммы, выводить сводные таблицы.
Задание 1. Графически определить, сколько точек пересечения имеют графики функций
f(x) = -x3 + 15x2 – 25 s(x)= 4x2 + 4x + 35
Порядок действий:
1.1.Запустите MS Excel. Создайте новый файл. Создайте (или переименуйте существующий) лист «График 1».
1.2.Запишите в строчку значения x в диапазоне [-10; 15] с шагом 0,5.
Это можно сделать несколькими способами.
а) Создав арифметическую прогрессию. Для этого сначала вручную укажите начальное значение (в нашем случае -10), а затем вызовите диалоговое окно «Прогрессия» (Главная -> Редактирование -> Заполнить -> Прогрессия), в котором задайте настройки: расположение – по строкам, тип – арифметическая, предельное значение – 15, шаг прогрессии – 0,5. Чем меньше шаг прогрессии – тем точнее будет построен график.
б) Используя автозаполнение. Для этого сначала вручную укажите два первых значения (-10 и 9,5). Выделите их и вытяните выделенную область вправо, зажав левую кнопку мыши над правым
нижним углом выделенной области.
в) Используя формулу. Для этого сначала вручную укажите начальное значение (-10). В соседней справа ячейке напишите формулу =ab+0,5, где ab – адрес ячейки слева. При написании формулы адрес ячейки можно указать, щѐлкнув на неѐ левой кнопкой мыши. После введения формулы нажмите «Enter». Должно отобраситься число «-9,5». Выделите и скопируйте ячейку с числом «- 9,5». Выделите справа несколько ячеек в строке и вставьте скопированное.
1.3.Создайте таблицу значений функции f(x). Для этого в ячейку под ячейкой со значением x «-10» занесите формулу для вычисления значения f(x): =-ab^3+15*ab^2-25, где ab – адрес ячейки сверху. В ячейке должно отобразиться значение 2475. Выделите эту ячейку и вытяните выделенную область вправо, зажав левую кнопку мыши над правым нижним углом выделенной области – будут посчитаны значения f(x) и для других значений x.
1.4.В строчке ниже по аналогии создайте таблицу значений функции s(x).
1.5.Добавьте на лист график функции f(x). Для построения графика нужно выделить ячейки со значениями f(x) и на панели инструментов «Вставка» во вкладке «Диаграммы» выбрать «График».
Что бы задать для горизонтальной оси графика именно те значения x, которые были указаны в строке x, нужно щѐлкнуть на нѐм правой кнопкой мыши и в появившемся контекстном меню выбрать «Выбрать данные». После этого нужно в области «Подписи горизонтальной оси» нажать кнопку «Изменить» и выбрать ячейки, где находятся значения x.
Вэтом же окне в области «Элементы легенды (ряды)» нажмите кнопку «Изменить» и в поле «Имя ряда» укажите «f(x)».
1.6.В этом же окне в области «Элементы легенды (ряды)» нажмите кнопку «Добавить» и выберите в качестве диапазона строку со значениями функции s(x), что бы построить еѐ график. Обозначьте этот график как «s(x)». Таким образом в одной области тепеь построены графики функций f(x) и s(x). Сколько точек пересечения они имеют?
1.7.Сохраните файл.
Задание 2. Отобразить графически затухающие колебания. Зависимость текущей амплитуды х от времени t описывается в общем виде как
, где 
. Значения A0, δ, φ0 и ω0 выбрать самим.
Порядок действий:
2.1.Создайте (или переименуйте существующий) лист «График 2».
2.2.Запишите выбранные значения A0, δ, φ0 и ω0, например, как показано на рисунке:
2.3.На свободном месте занесите в строку значения времени t.
2.4.В другую строку занесите формулу для вычисления значения x(t). Для вычисления синуса, квадратного корня и степени числа e в формуле можно воспользоваться функциями SIN(), КОРЕНЬ() и EXP() соответственно. Для закрепления строк и/или столбцов в обозначении адреса ячейки при переносе нужно поставить перед ними символ «$». Для удобства составления формулы промежуточные результаты вычислений можно хранить в отдельных ячейках таблицы.
2.5.Для удобства просмотра ширину столбцов можно уменьшить. Если
при этом значения A0, δ, φ0 и ω0 перестанут помещаться, для их размещения можно объединить несколько ячеек.
2.6.Для построения графика выделите ячейки со значениями x и на панели инструментов «Вставка» во вкладке «Диаграммы» выберите «График». Возможный вариант графика показан на рисунке:
Что бы задать для горизонтальной оси графика именно те значения t, которые были указаны в строке, нужно щѐлкнуть на нѐм правой кнопкой мыши и в появившемся контекстном меню выбрать «Выбрать данные». После этого нужно в области «Подписи горизонтальной оси» нажать кнопку «Изменить» и выбрать ячейки, где находятся значения t.
2.7.Если по приведѐнному графику не видно, что колебания затухающие или что это вообще график колебаний, прежде, чем проверять правильность формулы, попробуйте указать больше значений времени. Чем меньше разность между двумя соседними значениями – тем график получится точнее. Чем больше конечное значение – тем больший период времени будет показан на графике.
Так же посмотрите, как будет меняться график при изменении значений A0, δ, φ0 и ω0.
Оформите получившийся график на ваше усмотрение при помощи контекстного меню его элементов или при помощи панелей инструментов «Конструктор», «Макет» и «Формат» (для их отображения график должен быть выделен).
2.8.Сохраните файл.
Задание 3 Пространственный график.
Постройте изображение трехмерной функции определяемой уравнением: 
3.1.Создайте (или переименуйте существующий) лист «График 3».
3.2.На листе подготовьте экранную форму в соответствии со следующим образцом:
Предполагается, что значения в первой строке и левом столбце таблицы рассчитываются автоматически на основе значения «мин x», «мин y» и «шага». Задайте эти значения по вашему усмотрению.
3.3.Заполните таблицу значениями функции 
для соответствующих x и y.
3.4.Для построения графика нужно выделить таблицу значений функции Z (значения аргументов x и y выделять не нужно) и на панели инструментов «Вставка» во вкладке «Диаграммы» выбрать «Другие», затем – «Поверхность».
3.5.Для изменения формата проекции выделите диаграмму, нажмите правую кнопку мыши и выберите пункт меню «Поворот объѐмной фигуры». В открывшемся окне настройте формат проекции по вашему усмотрению.
3.6.Сохраните файл.
Задание 4 Построение сводной таблицы
Создайте таблицу «Прайс-лист», добавьте фильтры, создайте сводку по средней стоимости товара, по суммарному количеству товара во всех магазинах и по количеству магазинов в которых есть определѐнный товар.
4.1.Создайте (или переименуйте существующий) лист «Сводная таблица».
4.2.Создайте на листе прайс-лист товаров по образцу:
В столбце «Магазин» должно быть указано не менее 5 различных названий магазинов. Для каждого из них должно быть указано не менее 10 наименований товаров. Т.е. общее количество строк с данными должно быть не менее 50. Наборы товаров для разных магазинов сделайте по большей части совпадающими.
4.3.Добавьте фильтры. Для этого выделите все столбцы таблицы и нажмите «Фильтр» на панели инструментов «Данные» в группе «Сортировка и фильтр».
Теперь в таблице можно скрывать или отображать строки с определѐнными наименованиями.
4.4.Создайте сводную таблицу. Для этого выделите созданную таблицу вместе с заголовками столбцов. Пустых строк или столбцов в выделенной области быть не должно. Заголовок должен быть однострочный.
На панели инструментов «Вставка» нажмите «Сводная таблица».
Добавьте сводную таблицу на этот же лист. Разместите еѐ ниже созданной таблицы.
4.5.Настройте сводную таблицу для отображения средней стоимости каждого товара и суммарного количества каждого товара во всех магазинах. Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области «Поля сводной таблицы». Чтобы переместить поле из одной области в другую, перетащите его левой кнопкой мыши в целевую область.
В область «Названия строк» нужно поместить поле «Товар», в область «Значения» - поля «Количество» и «Стоимость». Щѐлкните левой кнопкой мыши на треугольной стрелке поля «Стоимость». В появившемся меню выберите «Параметры полей значений». В открывшемся окне выберите операцию «Среднее». В «Пользовательское имя» также запишите «Среднее».
Теперь в сводной таблице для каждого наименования товара отображается его суммарное количество во всех магазинах и средняя его стоимость по всем магазинам.
4.6.Уберите строку «Общий итог» из сводной таблицы. Для этого щѐлкните на сводной таблице правой кнопкой мыши и в появившемся контекстном меню выберите «Параметры сводной таблицы». В открывшемся окне перейдите на вкладку «Итоги и фильтры» и снимите флажок «Показывать общие итоги для столбцов».
4.7.Для проверки, добавьте или измените несколько значений в таблице и посмотрите, как изменятся значения в сводной таблице.
При добавлении или изменении новых данных в источник необходимо обновить все сводные таблицы, созданные на его основе. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду «Обновить». При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на панели инструментов «Параметры» в группе «Данные», щелкните стрелку под кнопкой «Обновить» и выберите команду «Обновить все».
4.8.Добавьте в сводную таблицу по аналогии с предыдущими ещѐ один столбец – количество магазинов, в которых есть данный товар.
4.9.Сохраните файл.