8291
.pdfЛабораторная работа №6 Тема: Использование инструментов промежуточные итоги и сводные
таблицы для обобщения и анализа данных.
Порядок выполнения работы
1. В новой рабочей книге создайте таблицу, аналогичную приведенной
ниже, и заполните ее |
данными. В поле Сумма реализации |
|
вставьте |
|||||||||||||||||||
расчетную формулу. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Дата |
|
|
Название |
|
|
|
|
|
Тип |
|
|
Цена |
|
|
Кол-во |
|
|
Сумма |
|
|
|
|
|
|
|
|
Автор |
|
|
|
|
одного |
|
|
продан. |
|
|
реализа |
|
|
|||
|
|
продажи |
|
|
книги |
|
|
|
|
издания |
|
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
экз. |
|
|
книг |
|
|
ции |
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
Сага о |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
31.03.2006 |
|
Фарсайтах |
|
|
Дж.Голсуорси |
|
худ. |
|
150р. |
2 |
|
|
300р. |
|
|||||||
|
|
|
|
|
Сумма |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
31.03.2006 |
|
технологий |
|
|
С. Лем |
|
науч. |
|
78р. |
5 |
|
|
390р. |
|
|||||||
|
|
|
|
|
Финансы |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
31.03.2006 |
|
предприятий |
|
|
А.Д.Шеремет |
|
учебн. |
|
56р. |
15 |
|
840р. |
|
||||||||
|
03.04.2006 |
|
|
Пикник на |
|
|
А.Стругацкий |
|
|
|
|
89р. |
4 |
|
|
356р. |
|
|||||
|
|
|
обочине |
|
|
Б.Стругацкий |
|
худ. |
|
|
|
|
||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
03.04.2006 |
|
Финансы |
|
|
|
|
|
|
|
|
|
|
7 |
|
|
392р. |
|
||||
|
|
предприятий |
|
|
А.Д.Шеремет |
|
учебн. |
|
56р. |
|
|
|
||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
03.04.2006 |
|
Сумма |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
технологий |
|
|
С. Лем |
|
науч. |
|
78р. |
1 |
|
|
78р. |
|
||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||
|
03.04.2006 |
|
Сага о |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
Фарсайтах |
|
|
Дж.Голсуорси |
|
худ. |
|
150р. |
5 |
|
|
750р. |
|
||||||||
|
|
|
|
|
|
|
|
|
|
|
|
2. Определите итоговую сумму реализации книг по каждому типу изданий и
в целом по магазину, используя команду Данные Итоги (предварительно
убедитесь, что таблица отсортирована по полю Тип издания).
3.Нажимая кнопки группировки слева от таблицы, последовательно установите отображение:
только общих итогов для всего магазина;
только итогов по типам книг и всему магазину.
4.Покажите результаты Вашей работы преподавателю и отмените вычисление итоговых значений.
5.Определите общее количество книг, проданных в течение дня и среднюю сумму продаж по каждой дате. Для вставки нескольких итоговых функций используйте повторный вызов команды Данные Итоги, отключив при этом опцию Заменить текущие итоги.
|
Дата |
|
|
|
|
|
Тип |
|
Цена |
|
Кол-во |
|
Сумма |
|
|
|
Название книги |
|
Автор |
|
|
одного |
|
продан |
|
реализа |
|
||
|
продажи |
|
|
|
издания |
|
|
|
|
|||||
|
|
|
|
|
|
|
экз. |
|
.книг |
|
ции |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
31.03.2006 |
|
Сага о Фарсайтах |
Дж.Голсуорси |
худ. |
150р. |
2 |
300р. |
|
|
Финансы |
|
|
|
|
|
31.03.2006 |
|
предприятий |
А.Д. Шеремет |
учебн. |
56р. |
15 |
840р. |
31.03.2006 |
|
Сумма технологий |
С. Лем |
науч. |
78р. |
5 |
390р. |
31.03.2006 Итог |
|
|
|
22 |
|
||
31.03.2006 Среднее |
|
|
|
|
510р. |
||
03.04.2006 |
|
Пикник на обочине |
А.Стругацкий, |
|
89р. |
4 |
356р. |
|
Б.Стругацкий |
худ. |
|||||
|
|
|
|
|
|
||
03.04.2006 |
|
Сага о Фарсайтах |
Дж.Голсуорси |
худ. |
150р. |
5 |
750р. |
03.04.2006 |
|
Финансы |
|
|
|
7 |
392р. |
|
предприятий |
А.Д. Шеремет |
учебн. |
56р. |
|||
|
|
|
|
||||
03.04.2006 |
|
Сумма технологий |
С. Лем |
науч. |
78р. |
1 |
78р. |
03.04.2006 Итог |
|
|
|
17 |
|
||
03.04.2006 Среднее |
|
|
|
|
394р. |
||
Общий итог |
|
|
|
|
39 |
|
|
Общее среднее |
|
|
|
|
444р. |
6. Скопируйте Лист1 на Лист2 и переименуйте последний, назвав его
Сводные таблицы. Отмените на скопированном листе вычисление итоговых значений.
7. Создайте сводную таблицу с данными о суммарной стоимости проданных книг различных типов и в целом по магазину. Измените надпись и
формат поля заголовков, как это сделано в приведенной ниже таблице.
Установите для отображения данных в сводной таблице денежный формат.
|
Сумма реализации |
|
|
Тип |
|
|
|
|
|
|
|
издания |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Название книги |
|
|
худ. |
науч. |
учебн. |
Общий итог |
|
Сага о Фарсайтах |
|
1 050р. |
|
|
1 050р. |
|||
Сумма технологий |
|
|
|
468р. |
|
468р. |
||
Финансы |
|
|
|
|
|
|
||
предприятий |
|
|
|
|
1 232р. |
1 232р. |
||
Пикник на обочине |
|
356р. |
|
|
356р. |
|||
Общий итог |
|
1 406р. |
468р. |
1 232р. |
3 106р. |
8. Измените представление данных в созданной Вами сводной таблице,
поменяв местами строки и столбцы.
9.Измените количество книг, проданных 1.03.2006 на 300 и обновите информацию в сводной таблице. Проанализируйте, какие изменения произошли.
10.Восстановите прежнее количество книг, проданных 1.03.2006, и вновь обновите информацию в сводной таблице.
11.Измените представление данных в сводной таблице, переместив поле Название книги в область строк. Добавьте в область строк из списка полей сводной таблицы поле Дата продажи.
Сумма |
|
|
|
|
|
реализации |
|
|
|
|
|
Тип издания |
Название книги |
|
Дата продажи |
|
Итог |
|
|
|
|
|
|
худ. |
Сага о Фарсайтах |
03.04.2006 |
750р. |
||
|
|
31.03.2006 |
300р. |
||
|
Сага о Фарсайтах Итог |
|
|
|
1 050р. |
|
Пикник на обочине |
03.04.2006 |
356р. |
||
|
Пикник на обочине Итог |
|
|
|
356р. |
худ. Итог |
|
|
|
|
1 406р. |
науч. |
Сумма технологий |
03.04.2006 |
78р. |
||
|
|
31.03.2006 |
390р. |
||
|
Сумма технологий Итог |
|
|
|
468р. |
науч. Итог |
|
|
|
|
468р. |
учебн. |
Финансы предприятий |
03.04.2006 |
392р. |
||
|
|
31.03.2006 |
840р. |
||
|
Финансы предприятий Итог |
|
|
|
1 232р. |
учебн. Итог |
|
|
|
|
1 232р. |
Общий итог |
|
|
|
|
3 106р. |
12.Уберите отображение промежуточных итогов, последовательно выбирая команду Скрыть детали для полей Название книги и Тип издания.
13.Восстановите прежний вид сводной таблицы с отображением промежуточных итогов.
14.Отобразите продажи книг по месяцам, применив инструмент группировки к полю Дата продажи.
Сумма |
|
|
|
реалзизации |
|
|
|
Тип издания |
Название книги |
Дата продажи |
Итог |
худ. |
Сага о Фарсайтах |
мар |
300р. |
|
|
апр |
750р. |
|
Сага о Фарсайтах Итог |
|
1 050р. |
|
Пикник на обочине |
апр |
356р. |
|
Пикник на обочине Итог |
|
356р. |
худ. Итог |
|
|
1 406р. |
науч. |
Сумма технологий |
мар |
390р. |
|
|
апр |
78р. |
|
Сумма технологий Итог |
|
468р. |
науч. Итог |
|
|
468р. |
учебн. |
Финансы предприятий |
мар |
840р. |
|
|
апр |
392р. |
|
Финансы предприятий Итог |
|
1 232р. |
учебн. Итог |
|
|
1 232р. |
Общий итог |
|
|
3 106р. |
15.Покажите результаты Вашей работы преподавателю и отмените группировку. Удалите из сводной таблицы поле Дата продажи.
16.Измените итоговую функцию сводной таблицы, получив среднее количество проданных книг. Установите новый формат представления данных,
убрав денежный формат и округлив до целого.
17. Измените итоговую функцию для вычисления общего количества
проданных книг.
Количество |
|
|
проданных книг |
|
|
Тип издания |
Название книги |
Итог |
худ. |
Сага о Фарсайтах |
7 |
|
Пикник на обочине |
4 |
худ. Итог |
|
11 |
науч. |
Сумма технологий |
6 |
науч. Итог |
|
6 |
учебн. |
Финансы предприятий |
22 |
учебн. Итог |
|
22 |
Общий итог |
|
39 |
18.Измените таблицу результатов, переместив поле Тип издания в
область страниц. Выберите отображение данных только по учебной литературе.
19.Покажите результаты Вашей работы преподавателю и сохраните созданную Вами рабочую книгу.
Задания для самостоятельной работы
Вариант 1
Создайте таблицу поступления товаров в магазин со следующими заголовками столбцов: Дата поступления, Наименование товара,
Отдел, Количество, Цена, Стоимость товаров в партии. Заполнить
таблицу данными для двух отделов (Одежда, Обувь) и трех дней поступления
товаров (пальто, костюм, сапоги, туфли).
1.Определите итоговые количество и стоимость товаров, поступивших в магазин и его отделы.
2.Измените отображение данных, оставив только итоги по отделам и магазину в целом. Восстановите прежний вид таблицы.
3.Отмените вычисление итоговых значений.
4.Определите количество партий товаров, поступивших в течение дня, и
среднюю стоимость товаров в партии.
5.На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции суммарную стоимость товаров, поступивших в магазин и его отделы. Отобразите в таблице списки товаров для каждого отдела, и даты продаж.
6.Примените группировку для двух дат поступления товара.
7.С помощью редактирования итоговой функции сводной таблицы получите среднюю стоимость товаров в каждой партии.
8. Измените одно из значений в исходной таблице. Проанализируйте,
изменится ли значение в сводной таблице.
9.Измените таблицу результатов, вынося значение поля Отдел в область страниц. Выберите отображение данных только по отделу Обувь.
10.Покажите работу преподавателю и сохраните рабочую книгу.
Вариант 2
Создайте таблицу успеваемости студентов 456 и 457 групп, содержащую следующие поля: Ф.И.О., № группы, Дата сдачи, Предмет, Балл.
Заполните таблицу данными для четырех студентов, сдающих экзамены по
двум предметам ( Математика, Информатика).
1.С помощью вставки итоговой функции определите для каждой даты количество студентов, сдающих экзамен.
2.Измените отображение данных, оставив только общие и промежуточные итоги. Восстановите прежний вид таблицы.
3.Отмените вычисление итоговых значений.
4.Определить средний балл и количество студентов, сдающих экзамен по каждому предмету.
5.На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общий балл. Отобразите в таблице списки студентов, обучающихся в каждой группе и названия предметов.
6.Примените инструмент группировки для двух групп.
7.С помощью редактирования итоговой функции сводной таблицы получите средний балл по группам.
8.Измените номер группы одного из студентов в исходной таблице.
Проанализируйте, изменится ли значение в сводной таблице.
9.Измените таблицу результатов, вынося значение поля № группы в
область страниц. Выберите отображение данных только по группе 457.
10.Покажите работу преподавателю и сохраните рабочую книгу.
Вариант 3
Создайте таблицу реализации печатной продукции, с заголовками столбцов: Дата реализации, Название, Тип издания (газета, журнал, календарь), Цена одного экземпляра, Количество, Сумма от
реализации. Заполните данными десять строк таблицы для трех дней продаж.
1.Определите итоговые количество и стоимость проданной печатной продукции по каждому типу издания и в целом по магазину.
2.Измените отображение данных, оставив только итоги по каждому типу издания, а затем только общие итого по магазину. Восстановите прежний вид таблицы.
3.Отмените вычисление итоговых значений.
4.Определите количество типов изданий, проданных в течение дня, и
среднюю выручку по каждому дню продаж.
5.На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общую сумму реализации печатной продукции.
Отобразите в таблице списки названий для каждого типа издания, и даты
продаж.
6.Примените группировку для двух типов печатной продукции.
7.С помощью редактирования итоговой функции сводной таблицы получите среднее количество проданных экземпляров.
8.Измените одно из значений в исходной таблице. Проанализируйте,
изменится ли значение в сводной таблице.
9.Измените таблицу результатов, вынося значение поля Дата реализации в область страниц. Выберите отображение данных только по одной дате.
10.Покажите работу преподавателю и сохраните рабочую книгу.
Вариант 4
Создайте таблицу реализации дисков для изучения английского,
немецкого и французского языков со следующими заголовками столбцов:
Месяц продаж, Наименование диска (Английский язык и т.д.), Тип
(разговорный, деловой), Цена одного диска, Кол-во проданных дисков,
Сумма от реализации. Заполнить таблицу данными о продажах за три
месяца (не менее 10 строк).
1.Определите итоговые количество и стоимость проданных дисков каждого типа и в целом по магазину.
2.Измените отображение данных, оставив только итоги по каждому типу издания, а затем только общие итого по магазину. Восстановите прежний вид таблицы.
3.Отмените вычисление итоговых значений.
4.Определите сколько различных наименований дисков было продано в каждом месяце, и среднюю выручку по каждому месяцу продаж.
5.На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общую сумму от реализации дисков. Отобразите в таблице списки названий дисков для каждого типа и даты продаж.
6.Примените группировку для двух типов дисков.
7.С помощью редактирования итоговой функции сводной таблицы получите среднее количество проданных дисков.
8.Измените одно из значений в исходной таблице. Проанализируйте,
изменится ли значение в сводной таблице.
9.Измените таблицу результатов, вынося значение поля Наименование диска в область страниц. Выберите отображение данных только по английскому языку.
10.Покажите работу преподавателю и сохраните рабочую книгу.
Вариант 5
Создайте таблицу поступления в магазин компьютерной продукции,
содержащую следующие поля: Дата поступления, Наименование товара
(системный блок, монитор, принтер), Фирма изготовитель, Цена,
Количество, Стоимость товаров в партии. Заполнить таблицу данными
по двум дням работы магазина и двум фирмам изготовителям (не менее 10
строк).
1.Определите итоговые количество и стоимость товаров по каждой фирме
–изготовителю и в целом по магазину.
2.Измените отображение данных, оставив только общие и промежуточные итоги. Восстановите прежний вид таблицы.
3.Отмените вычисление итоговых значений.
4.Определите количество партий товаров, поступивших в течение дня, и
среднюю стоимость товаров в партии.
5.На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции суммарную стоимость товаров, поступивших в магазин. Отобразите в таблице списки наименований товаров для каждой фирмы производителя, и даты поступления.
6.Примените группировку для двух наименований товаров.
7.С помощью редактирования итоговой функции сводной таблицы получите среднюю стоимость товаров в каждой партии.
8.Измените одно из значений в исходной таблице. Проанализируйте,
изменится ли значение в сводной таблице.
9.Измените таблицу результатов, вынося значение поля Наименование товара в область страниц. Выберите отображение данных только по поступлениям принтеров.
10.Покажите работу преподавателю и сохраните рабочую книгу.
Вариант 6
Создайте таблицу начисления заработной платы сотрудникам за
последние три месяца работы, используя следующие заголовки столбцов:
Ф.И.О., Дата зачисления, Отдел (бухгалтерия, канцелярия), Месяц,
Оклад. Заполнить таблицу данными для четырех сотрудников.
1.Определите общую сумму выплат по каждому месяцу и в целом за квартал.
2.Измените отображение данных, оставив только общие и промежуточные итоги. Восстановите прежний вид таблицы.
3.Отмените вычисление итоговых значений.
4.Вставьте итоговые функции для определения количества сотрудников и среднюю зарплату по каждому отделу.
5.На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общие выплаты работникам (сумму по полю
Оклад). Отобразите в таблице списки сотрудников по каждому отделу, и
наименования месяцев начисления зарплаты.
6.Примените группировку для двух месяцев работы.
7.С помощью редактирования итоговой функции сводной таблицы получите среднюю зарплату каждого сотрудника.
8.Измените одно из значений поля Оклад в исходной таблице.
Проанализируйте, изменится ли значение в сводной таблице.
9.Измените таблицу результатов, вынося значение поля Отдел в область страниц. Выберите отображение данных только по канцелярии.
10.Покажите работу преподавателю и сохраните рабочую книгу.
Вариант 7
Создайте таблицу отгрузки нефтепродуктов со следующими полями:
Дата отгрузки, Покупатель, Наименование товара (нефть, мазут, и т.д.), Количество (т.), Цена, Стоимость отгруженного товара. Заполните