8829
.pdfЛабораторная работа №5
Тема: Создание сложных связанных таблиц
Цель работы: Научиться создавать таблицы на нескольких листах, проводить сложные вычисления с использованием встроенных функций, абсолютной и относительной адресации. Освоить условное форматирование, функции СУММ,
СРЗНАЧ, ЕСЛИ, СЧЕТЕСЛИ.
Справка:
1. Использование элемента управления формы Флажок.
Меню Вид Панель инструментов Формы. Выбрать Флажок () и
поместить на Лист. Правой кнопкой мыши щѐлкнуть по Флажку и из раскрывшегося контекстного меню выбрать «Формат объекта». Перейти на вкладку «Элемент управления» и установить целевую ячейку в «Связь с ячейкой».
Если Флажок установлен, то значение в целевой ячейке будет ИСТИНА, иначе -
ЛОЖЬ.
2. Использование элемента управления формы Счётчик.
Меню Вид Панель инструментов Формы. Выбрать Счётчик () и
поместить на Лист. Правой кнопкой мыши щѐлкнуть по Счётчику и из раскрывшегося контекстного меню выбрать «Формат объекта». Перейти на вкладку «Элемент управления» и установить целевую ячейку в «Связь с ячейкой»,
а также Текущее, Минимальное и Максимальное значения и Шаг изменения. В
целевой ячейке будет отображено Текущее значение.
Порядок выполнения работы
1. Создайте новую рабочую книгу и сохраните ее под именем Ведомость.xls. На Листе 1 таблицу Списочный состав группы и заполните ее данными 10
строк таблицы, как показано на рис.3.1.
Для заполнения столбца №п/п используйте прогрессию
(Правка Заполнить Прогрессия). Первый лист книги Лист 1 переименуйте в Список группы.
2. Переименуйте Лист 2, назвав его Математика. На этом листе создайте таблицу
Экзаменационная ведомость и оформите, как показано на рис.3.2. Заполните таблицу данными, следуя указаниям:
а) номер группы и список студентов вставьте как ссылку на лист Список группы, так чтобы при изменении данных на листе Список группы
автоматически менялись данные на листе
Математика;
б) поле Балл заполните произвольно числами от 0 до 5. Установите формат числа - один знак после запятой;
в) графа Оценка должна содержать формулу,
которая в зависимости от балла выдаѐт значение в соответствии с таблицей 3.1. (используйте функцию ЕСЛИ);
Табл. 3.1.
Балл |
Оценка |
Условное |
|
форматирование |
|||
|
|
||
от 4,5 и выше |
отл. |
- |
|
от 3,5 до 4,4 |
хор. |
- |
|
от 2,5 до 3,4 |
удовл. |
зелѐный |
|
меньше 2,5 |
неуд. |
красный курсив |
Рис. 3.1.
Рис. 3.2.
г) к столбцам Балл и Оценка примените условное форматирование
(Условие и Формат см. в таблице 3.1).
д) ниже таблицы проведите автоматический подсчѐт числа человек с оценками: «отлично», «хорошо», «удовлетворительно» и «неудовлетворительно»
(используйте функцию СЧЕТЕСЛИ).
3.Повторите п.2, создав на следующих двух листах экзаменационные ведомости ещѐ по 2 предметам. Переименуйте соответствующие листы так, чтобы их имена совпадали с названиями предметов.
4.Создайте лист Ведомость начисления стипендии и разместите на нем одноимѐнную таблицу (см. рис.3.3). Все данные на этом листе должны заполняться автоматически через формулы и ссылки:
Рис. 3.3.
а) номер группы и список студентов вставьте как ссылки на соответствующие ячейки листа Список группы, для заполнения столбца № п/п
используйте прогрессию (Правка Заполнить Прогрессия);
б) столбцы Предметы заполните ссылками на данные таблиц
Экзаменационные ведомости одноименных листов;
в) в столбце Средний балл должна получиться средняя оценка по всем предметам с точностью до второго знака после запятой;
г) в графе Соц.стип. отмечается, нуждается ли студент в социальной стипендии. Для реализации запроса используйте элемент управления формы
Флажок : если галочка установлена, то студенту выплачивается соц.стип.
Целевой ячейкой является ячейка из графы Соц.стип. ; д) графа Стипендии по успеваемости заполняется без учѐта
социальной стипендии. Стипендия начисляется в процентах от базовой стипендии
(БС) (см. рис.3.3). Если средний балл меньше 3,5, то стипендия равна нулю.
Формат числа – денежный; е) в графе Начисленная стипендия вычислите суммарную величину
Стипендии по успеваемости и Социальной стипендии, если она есть;
ж) размеры базовой стипендии (БС) и социальной стипендии
(Соц.стипендия) должны задаваться элементом управления формы Счётчик .
Целевой ячейкой являются ячейки со значениями БС и Соц.стипендии,
соответственно.
5.В конце таблицы подсчитайте суммарные затраты на выплату стипендий в группе, а так же сколько человек в группе получают отличную, хорошую и пониженную стипендии. А так же, сколько человек получают социальную стипендию (независимо от стипендии по успеваемости).
6.Сохраните рабочую книгу под именем Ведомость.xls и покажите свою работу преподавателю.
Лабораторная работа №6
Тема:Наглядное представление данных.
Цель работы:Научиться представлять табличные данные в виде диаграмм и
графиков различных типов.
Порядок выполнения работы
1. В новой рабочей книге создайте таблицу Годовой бюджет (см. рис.4.1.).
Заполните данными первую строку таблицы (Продажа фруктов) и рассчитайте
значения остальных ячеек, вставив формулы в соответствии со следующими
соотношениями:
Торговые издержки – 30% от продажи фруктов; Затраты на маркетинг – 10% от продажи фруктов; Накладные расходы – 20% от продажи фруктов.
Рассчитайте Общую сумму расходов (сумма по Торговым издержкам,
Затратам на маркетинг и Накладным расходам) и Чистую прибыль
(разностьмежду Продажей фруктов и Общей суммой расходов).
Рис. 4.1.
2. Постройте несколько диаграмм для наглядного представления
полученных данных.
а) Диаграмма № 1: поквартальные расходы по трем категориям (торговые издержки, затраты на маркетинг, накладные расходы) в виде объемной гистограммы. Выделите диапазон данных А5:Е7, вызовите Мастер диаграмм и
укажите нужный тип гистограммы (см. рис.4.2.).
Замечание: чтобы вывести названия кварталов в качестве меток оси Х
следует на втором шаге построения диаграммы перейти на вкладку Ряд и в поле Подписи по Х вести ссылку на диапазон В3:Е3.
б) Диаграмма № 2: продажа фруктов по кварталам. Постройте плоскую гистограмму по диапазону А4:Е4 (чтобы на оси Х отображались названия кварталов следует выделить диапазон А3:Е4). Оформите построенную гистограмму как показано на рис.4.3.
Замечание: изменить любой элемент диаграммы можно в диалоговом окне форматирования, вызвав его двойным щелчком мыши по заданному объекту.
в) Диаграмма № 3: измените тип диаграммы №2 на круговую. Получив круговую диаграмму, следует вставить подписи данных, указав процент продаж в каждом квартале (см. рис.4.4.).
г) Диаграмма № 4: скопируйте диаграмму №3 и преобразуйте в гистограмму. На диаграмме получить поквартальную продажу фруктов и чистую прибыль, для этого к данным диаграммы № 3 следует добавить новые данные: выделить ячейки А9:Е9, выбрать Правка Копировать; щелкнуть на одном из столбцов гистограммы, выбрать Правка Специальная вставка.
Рис. 4.2. |
Рис. 4.3. |
д) Диаграмма № 5: добавьте к диаграмме № 4 легенду (подписи рядов |
данных). Для этого необходимо правой кнопкой мыши щелкнуть по области
диаграммы, выбрать в контекстном меню Параметры диаграммы.
е) Диаграмма № 6: измените Диаграмму № 5 так, чтобы данные по продаже фруктов отображались в виде гистограммы, а чистая прибыль – в виде графика (нестандартный тип диаграммы). Для этого следует изменить тип диаграммы, выбрав на вкладке Нестандартные тип График|гистограмма.
Оформить полученную диаграмму как показано на рис.4.5.
Рис. 4.5. |
|
|
|
Рис. 4.4. |
|
|
2 |
2 |
, где |
7,5 x 7,5 |
x 1,5 |
. |
|
3. Постройте график поверхности z=x -y |
|
|
|
|
||
|
|
|
5 |
y 5, |
y 1 |
|
а) Для построения графика поверхности необходимо сначала создать таблицу данных. Заполните строку 1 значениями переменной x: введите в ячейку В1 число -7,5 (левую границу диапазона), выделите ячейки В1:L1 и выберите Правка Заполнить Прогрессия, указав Шаг 1.5. Аналогично заполните ячейки А2:А12 значениями переменной у - начальное значение -5, Шаг 1. Введите формулу для вычисления z в первую ячейку таблицы: (B2)=B$1^2-$A2^2, и продолжите ее на все ячейки с помощью маркера автозаполнения. До построения графика ячейку А1 следует оставить пустой.
б) Выделите диапазон А1:L12 и постройте график поверхности с помощью Мастера диаграмм. В итоге должен получиться гиперболический параболоид.
Лабораторная работа №7 Тема: Использование таблицы в качестве базы данных
Порядок выполнения работы
1. Подготовьте таблицу по образцу:
Ведомость
учѐта работы транспорта в автохозяйстве за месяц
№ п/п |
Марка |
Номер |
Плановый |
Фактический |
Простой, |
|
автомашины |
пробег, км |
пробег, км |
дн |
|||
|
|
|||||
1 |
ГАЗ-66 |
СР 12-37 |
1000 |
720 |
7 |
|
2 |
ЗИЛ-130 |
ТД 21-18 |
550 |
610 |
6 |
|
3 |
ЗИЛ-130 |
СР 28-12 |
600 |
300 |
14 |
|
4 |
УАЗ-3151 |
НФ 19-67 |
600 |
680 |
5 |
|
5 |
ГАЗ-66 |
АЯ 18-16 |
600 |
0 |
30 |
|
6 |
УАЗ-3962 |
НС 96-12 |
1000 |
1200 |
0 |
|
7 |
УАЗ-3962 |
НС 84-17 |
0 |
0 |
30 |
2.Последовательно выполните в созданной таблице сортировку записей:
-по фактическому пробегу в возрастающем порядке;
-по количеству дней простоя в убывающем порядке;
-по маркам автомобилей, а внутри каждой полученной группы по плановому пробегу.
3.Восстановите первоначальный порядок записей в таблице.
4.Между столбцами Фактический пробег, км и Простой, дн разместите поле Разность, в котором вычислите отклонение фактического пробега от планового.
5.Создайте форму для таблицы (списка).
6.Пролистывая записи с помощью формы найдите сведения об автомобиле с номером СР 28-12.
7.Используя критерии отбора, с помощью формы последовательно определите (после вопросов приводятся правильные ответы):
а) у каких автомобилей фактический пробег составил более 500 км:
№ п/п |
Марка |
Номер |
Плановый |
Фактический |
Простой, |
|
автомашины |
пробег, км |
пробег, км |
дн |
|||
|
|
|||||
1 |
ГАЗ-66 |
СР 12-37 |
1000 |
720 |
7 |
2 |
|
ЗИЛ-130 |
ТД 21-18 |
550 |
|
610 |
6 |
4 |
|
УАЗ-3151 |
НФ 19-67 |
600 |
|
680 |
5 |
6 |
|
УАЗ-3962 |
НС 96-12 |
1000 |
|
1200 |
0 |
|
б) у какого из автомобилей ЗИЛ-130 простой составил менее 10 дней: |
||||||
|
|
|
|
|
|
|
|
№ п/п |
|
Марка |
Номер |
Плановый |
|
Фактический |
Простой, |
|
автомашины |
пробег, км |
пробег, км |
дн |
|||
|
|
|
|||||
2 |
|
ЗИЛ-130 |
ТД 21-18 |
550 |
|
610 |
6 |
7. Используя форму, добавьте в таблицу запись: |
|
|
|||||
|
|
|
|
|
|
|
|
№ п/п |
|
Марка |
Номер |
Плановый |
|
Фактический |
Простой, |
|
автомашины |
пробег, км |
пробег, км |
дн |
|||
|
|
|
|||||
8 |
|
ЗИЛ-130 |
СК 14-18 |
800 |
|
640 |
4 |
8.Удалите введѐнную новую запись и закройте окно формы.
9.Удалите из таблицы поле Разность, км.
10.Применяя Автофильтр, последовательно определите (ниже вопроса приводится правильный результат работы):
а) у каких автомобилей простой составляет от 5 до10 дней включительно:
№ п/п |
|
|
|
Марка |
|
Номер |
|
Плановый |
Фактический |
|
Простой, |
|
|
|
|
автомашины |
|
|
пробег, км |
|
пробег, км |
|
дн |
|
|
||||
|
|
|
|
|
|
|
|
|
||||||
1 |
|
|
|
ГАЗ-66 |
|
СР 12-37 |
1000 |
720 |
|
7 |
|
|
||
2 |
|
|
ЗИЛ-130 |
|
ТД 21-18 |
550 |
610 |
|
6 |
|
|
|||
4 |
|
|
УАЗ-3151 |
|
НФ 19-67 |
600 |
680 |
|
5 |
|
|
|||
|
б) у какого автомобиля плановый пробег составляет 600 км, простой менее |
|||||||||||||
15 дней, фактический пробег – более 500 км: |
|
|
|
|
|
|
||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
||
№ п/п |
|
Марка |
Номер |
|
Плановый |
|
Фактический |
Простой, |
|
|||||
|
автомашины |
|
пробег, км |
пробег, км |
дн |
|
||||||||
|
|
|
|
|
|
|
||||||||
4 |
|
|
|
УАЗ-3151 |
НФ 19-67 |
|
600 |
|
680 |
|
5 |
|
|
|
|
в) у каких автомобилей фактический пробег составляет менее 500 км или |
|||||||||||||
более 1000 км, а простой – менее 15 дней: |
|
|
|
|
|
|
||||||||
|
|
|
|
|
|
|
|
|
|
|
||||
№ п/п |
|
|
|
Марка |
|
Номер |
|
Плановый |
Фактический |
|
Простой, |
|
||
|
автомашины |
|
|
пробег, км |
пробег, км |
|
дн |
|
|
|||||
|
|
|
|
|
|
|
||||||||
3 |
|
|
ЗИЛ-130 |
|
СР 28-12 |
600 |
300 |
|
14 |
|
|
|||
6 |
|
|
УАЗ-3962 |
|
НС 96-12 |
1000 |
1200 |
|
0 |
|
|
|||
11. Применяя Расширенный фильтр и сохраняя критерии отбора выше |
||||||||||||||
таблицы, а |
|
результаты – ниже таблицы, (состав столбцов и приводимая в них |
информация должны соответствовать приведѐнным образцам) последовательно
определите:
а) у какого автомобиля планировался пробег 1000 км, фактический пробег
составил более 500 км, простой – менее 6 дней:
Марка |
|
Номер |
|
Плановый |
|
Фактический |
Простой, |
||
автомашины |
|
|
пробег, км |
|
пробег, км |
дн |
|||
|
|
|
|
||||||
УАЗ-3962 |
|
НС 96-12 |
1000 |
1200 |
|
0 |
|||
б) у каких автомобилей простой составляет 0, 7 или 14 дней: |
|||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
Марка |
|
Номер |
|
Простой, |
|
|
|
|
автомашины |
|
|
дн |
|
|
||
|
|
|
|
|
|
|
|||
|
|
|
ГАЗ-66 |
|
СР 12-37 |
|
7 |
|
|
|
|
|
ЗИЛ-130 |
|
ТД 28-12 |
|
14 |
|
|
|
|
|
УАЗ-3962 |
|
НС 96-12 |
|
0 |
|
|
в) для какого автомобиля ЗИЛ-130 или ГАЗ-66 планировался пробег менее 1000 км, а фактический пробег составил более 500 км:
№ п/п |
|
Марка |
|
|
Номер |
|
|
Плановый |
Фактический |
Простой, |
||||||
автомашины |
|
|
|
пробег, км |
пробег, км |
дн |
||||||||||
|
|
|
|
|
|
|||||||||||
2 |
|
ЗИЛ-130 |
|
ТД 21-18 |
|
550 |
|
610 |
|
6 |
|
|||||
|
г) какие автомобили имеют фактический пробег более 1000 км или |
|||||||||||||||
простой от 5 до 10 дней: |
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
№ п/п |
|
Марка |
|
|
|
Номер |
|
Фактический |
Простой, |
|
|||||
|
автомашины |
|
|
|
пробег, км |
|
дн |
|
||||||||
|
|
|
|
|
|
|
|
|
|
|||||||
|
1 |
|
|
ГАЗ-66 |
|
|
СР 12-37 |
|
|
720 |
|
7 |
|
|||
|
2 |
|
ЗИЛ-130 |
|
ТД 21-18 |
|
|
610 |
|
6 |
|
|||||
|
4 |
|
УАЗ-3151 |
|
НФ 19-67 |
|
|
680 |
|
5 |
|
|||||
|
6 |
|
УАЗ-3962 |
|
НС 96-12 |
|
|
1200 |
|
0 |
|
д) у каких автомобилей фактический пробег превышает плановый:
Марка |
Номер |
Плановый |
Фактический |
|
автомашины |
пробег, км |
пробег, км |
||
|
||||
ЗИЛ-130 |
ТД 21-18 |
550 |
610 |
|
УАЗ-3151 |
НФ 19-67 |
600 |
680 |
|
УАЗ-3962 |
НС 96-12 |
1000 |
1200 |
12.Покажите результаты работы преподавателю.
13.Переименуйте лист с результатами работы в имя «Фильтрация».
14.Завершите работу с MSExcel, сохранив результаты.