21
Сортировка 20. Верните первоначальный порядок записей, как они были введены в базу. Это достигается сортировкой по полю №.
Форма отчетности по лабораторной работе 3. На отведенном для сортировок листе 5Сортировка продемонстрировать любую из 1–20 сортировок.
Лабораторная работа 4
Подведение итогов
Цель: Освоить средства автоматического подведения итогов (команда Данные – Итоги).
Задание на лабораторную работу. Провести анализ работы с постоянными покупателями, проиллюстрировать результаты на диаграммах.
Порядок выполнения лабораторной работы Лист 6ИтогиКлиентов. Сделайте копию листа продаж на шестой лист
книги. Отсортируйте записи по клиентам в порядке, задаваемом списком постоянных клиентов. Вычислите для каждого клиента общую сумму его покупок в рублях и килограммах, результат сверьте по Рис.12.
Рис.12. Итоговая выручка по клиентам
22
Добавьте для каждого клиента все возможные итоги для проведения анализа потребительских способностей, а именно суммарное количество кг (сколько всего вынес из магазина), количество покупок итоговой функцией «Количество значений» по полю Товар (сколько раз приходил в магазин), см. Рис. 13. Для добавления итогов с разными итоговыми функциями необходимо сбросить флажок «Заменить текущие итоги» в окне Промежуточные итоги.
Рис. 13. Результат промежуточных итоговых функций Сумма по полям Количество и Выручка и Количество по полю Товар
23
Добавьте по каждому покупателю максимальную и минимальную разовую выручку, максимальный и минимальный вес разовой покупки, максимальную и минимальную цену одного килограмма, а также средний вес и среднюю стоимость покупки и среднюю цену одного килограмма, см. Рис.
14.
Рис. 14. Фрагмент таблицы с несколькими итоговыми функциями
24
Используя символы структуры слева от таблицы, уберите детальную информацию о конкретных покупках, оставив только итоговые строки по каждому покупателю, см. Рис. 15.
Рис. 15. Только итоги по клиентам без деталей по продажам
Отобразите только итоговые суммы по количеству и выручке для каждого клиента, см. Рис. 16.
Рис. 16. Итоговые суммы по клиентам
25
Скройте все столбцы между столбцами Клиент и Выручка, см. Рис. 17.
Рис. 17. Итоги со скрытыми пустыми столбцами
Отсортируйте полученные итоги по выручке по убыванию. Первым в списке отобразится лучший покупатель, сделавший покупки на наибольшую сумму, образец см. на Рис. 18.
Лист 7КруговаяДиаграмма. Скопируйте лист 6ИтогиКлиентов на следующий лист и постройте круговую диаграмму по итоговым суммам покупателей. На Рис. 18 показан образец диаграммы, рамками вокруг диапазона B9:G64 показаны исходные данные для диаграммы.
Построение диаграммы осуществляется при помощи мастера диаграмм следующим образом. Кнопкой 
запускается мастер диаграмм. На первом шаге выбирается тип диаграммы – Круговая объемного вида. Во втором окне мастера визуально указывается диапазон ='7КруговаяДиаграмма'!$B$9:$G$64 – в окне предварительного просмотра мастера появится разноцветный пирог из пяти секторов, заголовок диаграммы и легенда с фамилиями клиентов. На третьем шаге добираем заголовок «Выручка по клиентам», заказываем легенду слева, в подписи данных – доли каждого значения в процентах. На четвертом шаге соглашаемся разместить диаграмму на текущем листе. Готово.
Далее в режиме ручного редактирования осуществляется доводка оформления диаграммы до образца на Рис. 18. Заголовок перемещаем влево вверх, назначаем шрифт Times, 20 пунктов, полужирный курсив, в две строки. Увеличиваем угол показа объемной диаграммы до 40˚. Разворачиваем лучшего покупателя (Оптова) влево на 10 часов и выкатываем его сектор из круга. Подписи данных в процентах с одной цифрой после запятой, шрифт полужирный, 16 пунктов, для самого большого сектора (Оптов) – 20 пунктов, для очень маленького (Новых) – на выноске, 14 пунктов. Для экономии места размещаем подписи на фоне широких цветных секторов. На темном фоне сектора (Бистров) пишем белым цветом. Лучше всего использовать цвета стандартной палитры Excel: сине-фиолетовый,
26
26
Рис. 18. Отсортированные итоги и круговая диаграмма
27
вишневый, слоновая кость, светло-бирюзовый, темно-фиолетовый. Убираем рамку вокруг легенды, шрифт Times, 16 пунктов. В левом нижнем углу добавляем надписями с панели рисования выручку по всему магазину.
Лист 8ВторичнаяГистограмма. Постройте круговую диаграмму с детальными значениями по одному из клиентов на вторичной гистограмме. На Рис. 19 показан образец для клиента Торбина и несмежный диапазон исходных данных, отобранный для построителя диаграмм.
Построение круговой диаграммы с вторичной гистограммой. Открыть детальную информацию о покупках Торбина, см. Рис. 19. Запустить мастера диаграмм. В первом окне выбрать тип диаграммы Круговая вида с вторичной гистограммой. Во втором окне указать визуально (обвести курсором) диапазон исходных данных
='8ВторичнаяГистограмма'!$B$9:$G$36;'8ВторичнаяГистограмма'!$B$55:$G$64
Объединение ячеек в несмежный диапазон осуществляется визуально с нажатой клавишей Ctrl. В третьем окне заказать в подписи данных значения. В четвертом окне разместить диаграмму в тот же лист 8ВторичнаяГистограмма.
Далее выполняется ручная настройка диаграммы. Перемещаем визуально всю детальную информацию про Торбина на гистограмму, в круге оставляем пять итоговых значений. Достаем параметры ряда данных и настраиваем расстояние между кругом и гистограммой (на образце 200), размер гистограммы (на образце ~ 80 % от диаметра круговой диаграммы), заказываем соединительные линии. Визуально выделяем итоговую сумму Торбина откаткой его сектора из общего круга вправо. Раскрашиваем клиентов в привычные цвета как на предыдущей диаграмме: Оптов – синефиолетовый, Бистров – вишневый, Торбин – слоновая кость, Кукин – свет- ло-бирюзовый, Новых – темно-фиолетовый. Это делается из формата точки данных или формата ключа легенды. На гистограмме использовать цвета гаммы Торбина: слоновая кость, светло-желтый, желтый, светлокоричневый, золотистый. Оформить заголовок и легенду по образцу.
Форма отчетности по лабораторной работе 4. Лист 6ИтогиКлиентов,
лист 7КруговаяДиаграмма, лист 8ВторичнаяГистограмма.
28
28
Рис. 19. Представление итогов на круговой диаграмме с выноской детальной информации по клиенту Торбину на вторичную гистограмму. Пунктирной рамкой показан несмежный диапазон исходных данных диаграммы (B9:G36;B55:G64)