55
Лабораторная работа 10
Сводные таблицы
Цель: Построение сводных таблиц и сводных диаграмм.
Задание на лабораторную работу. Провести анализ реализации товаров различных наименований по каждому клиенту.
Порядок выполнения лабораторной работы Лист 16СвТаб1. Сделайте копию листа продаж на следующий лист
книги. Все сводные таблицы в этой работе будут иметь одинаковую структуру – товары по клиентам, поэтому рекомендуется копировать последнюю полученную сводную таблицу на этот же лист ниже и преобразовывать ее для получения следующей таблицы, сводные диаграммы лучше размещать на отдельные листы диаграмм.
1. Постройте сводную таблицу суммарной выручки по товарам и клиентам, фамилии клиентов по оси строк, наименования товаров по оси столбцов, см. Рис. 36.
Рис. 36. Сводная таблица выручки по товарам и клиентам
2. Постройте сводную таблицу суммарной выручки по товарам и клиентам, суммы в ячейках сводной таблицы в денежном формате с рублями, см. Рис. 37. Денежный формат отображения настраивается через Формат поля данных сводной таблицы: вызвать 
Параметры поля данных сводной таблицы – Формат – Числовые форматы: Денежный – Обозначение: р.
Рис. 37. Образец сводной таблицы 2
56
3. Постройте сводную таблицу количества продаж товаров по клиентам. Для построения этой таблицы необходимо, не изменяя структуру сводной таблицы товары-клиенты, заменить итоговую функцию поля данных на количество значений: Параметры поля – Операция: Количество. Убрать рубли в ячейках сводной таблицы: Параметры поля – Формат – Числовые форматы: Общий. Образец таблицы см. на Рис. 38.
Рис. 38. Сводная таблица по количеству продаж
4. Постройте сводную таблицу суммарного количества килограмм проданных товаров клиентам. Смена поля в области данных осуществляется визуальным перетаскиванием полей с панели Список полей сводной таблицы (запускается одноименной кнопкой 
Список полей с панели инструментов Сводные таблицы). Для отображения весов с кг необходимо настроить пользовательский формат ячеек поля данных сводной таблицы:
Параметры поля данных сводной таблицы – Формат – Числовые форматы: Все форматы – Тип: «0,000" кг"». Образец см. на Рис. 39.
Рис. 39. Сводная таблица по весам
5. Постройте сводную таблицу суммарных выручки и количества проданных товаров клиентам. Несколько полей в области данных сводной таблицы. Добавление полей в сводной таблице осуществляется визуально, перетаскиванием названий полей из списка полей. Удаление поля осуществляется через Параметры поля – Удалить или можно отменить отображение поля в сводной таблице через фильтр столбца Данные, который
57
появляется, когда в таблице выведено несколько полей данных, см. Рис.
40.
Рис. 40. Сводная таблица с двумя полями данных
6. Постройте сводную таблицу суммарной выручки в руб. и суммарного количества проданного в кг. Управление форматами ячеек сводной таблицы осуществляется через 
Параметры соответствующего поля – Формат, образец форматирования см. на Рис. 41.
Рис. 41. Образец форматирования ячеек сводной таблицы
7. Постройте сводную таблицу средней выручки и среднего веса разовой продажи товаров клиентам. Смена функций в полях данных сводной таблицы осуществляется через 
Параметры соответствующего поля – Операция, см. Рис. 42.
58
Рис. 42. Сводная таблица с операциями Среднее
8. Постройте сводную таблицу средних цен товаров для разных клиентов. Вычисляемое поле =Выручка/Количество; рекомендуемое название нового поля СрЦена, см. Рис. 43.
Рис. 43. Сводная таблица с вычисляемым полем
9. Постройте сводную таблицу суммарного налога с продаж по каждому товару и каждому клиенту. Вычисляемое поле Налог=Выручка*0,3, Рис. 44.
Рис. 44. Сводная таблица налога с продаж
10. Постройте сводную таблицу выручки от продажи товаров четырем лучшим клиентам в долях от общей суммы. Использовать дополнительные функции поля данных, сортировку по столбцу Общий итог, фильтрацией по полю Клиент убрать клиента с наименьшей выручкой – Новых, Рис. 45.
59
Рис. 45. Сводная таблица выручки в долях от общей суммы
11. По сводной таблице 10 постройте сводную диаграмму выручки от продаж различных товаров по каждому клиенту в процентах от общей суммы по магазину. Тип диаграммы – накопительная гистограмма. На диаграмме (Рис. 46) должно быть видно, что Бистров предпочитает огурцы, а Кукин не любит капусту, Оптов берет все товары и в большом количестве и т.д. Заголовки, настройка шкалы оси значений, цветовой палитры рядов овощей, подписей значений сделайте по образцу Рис. 46.
Рис. 46. Образец оформления сводной диаграммы 11
12. На основе Сводной таблицы 2 постройте сводную диаграмму выручки в рублях по каждому товару и каждому клиенту. Тип диаграммы – цилиндрическая гистограмма, отображающая объемными цилиндрами группы значений из рядов (товаров) по категориям (клиентам). Отмените на диаграмме показ данных по покупателям Новых и Бистрову. Уберите серые стены и основание. Настройте объемный вид на изометрию, высота – 20 %. Для изображения стопок монет используйте заливку маркеров способом – Узор – Горизонтальная штриховка, см. Рис. 47.
60
Рис. 47. Образец оформления сводной диаграммы 12
13. На основе сводной таблицы 4 постройте сводную диаграмму, отображающую вклад каждого товара в сумму всего купленного каждым клиентом. Тип диаграммы – области с накоплением. Отмените на диаграмме показ данных по клиенту Новых. Настройте порядок рядов так, чтобы картофель лежал внизу, над ним – капуста, еще выше – огурцы, и сверху – помидоры. Подписи шкалы значений с кг, см. Рис. 48.
Рис. 48. Образец оформления сводной диаграммы 13
61
61
Рис. 49. Образец оформления сводной диаграммы 14