Второй пример создания сводной таблицы
Исходные данные:
Построим вот такую сводную таблицу:
Когда таблица построена, перейдем к ее оформлению. Для начала изменим цветовую схему, применив к ней шаблон. Переходим на вкладку «Главная» и нажимаем на кнопку «Форматировать как таблицу». На экране появится список различных шаблонов форматирования, выбираем понравившийся нам и нажимаем на него. Excel автоматически определит границы таблицы, если они заданы не верно, выделяем таблицу вручную и нажимаем кнопку «Ок» (рис.8). Таблица поменяла цветовую гамму и появилась возможность сортировки параметров.
РИС.8 Оформление сводной таблицы в нужную цветовую гамму
Не всегда удобно просматривать большое число значений и сравнивать их с плановыми. Предположим, что выручка каждого работника в сутки должна составлять не менее 40 рублей. Но не обязательно оценивать показатели вручную, просматривая каждое значение, проще довериться встроенному компоненту Excel. Выделим область данных: по каждому сотруднику данные по выручке без итоговых данных. Переходим во вкладку «Главная – Условное форматирование - Наборы значков» из выпадающего меню выбираем понравившийся шаблон, мне нравиться светофор, так как с ним очень удобно работать. После выбора шаблона «Главная – Условное форматирование – Управление правилами – Изменить правило», откроется окно «Создание правил форматирования». Здесь необходимо напротив этих самых значков ввести данные, при превышении которых работа сотрудника оценивается как: отличная, удовлетворительная и неудовлетворительная. Данные вводятся в параметр «Значение» напротив каждого из кружков, а поле «Тип» в данном случае необходимо изменить с «Процент» на «Числа». Мною были заданы следующие параметры: 40, 20. Третий параметр задается автоматически, он оценивается как все значения меньше удовлетворительного. Нажимаем кнопку «Ок» (рис.9).
РИС.9 Настройка значков условного форматирования
В итоге наша таблица будет выглядеть следующим образом:
В ячейках всех значений появились кружки трех различных цветов. На основе, представленной в таком виде информации намного проще оценить работу сотрудников за определенный период времени. Мы можем сравнить качество работы сотрудников, определить кто из работников добивается наиболее выдающихся результатов, а кто наоборот требует к себе пристального внимания.
Но это еще не последний способ условного форматирования данных. В Excelпоявились такие инфографические элементы как «Гистограммы» и «Цветовые шкалы». Рассмотрим их более подробно. Выделим значения в ячейках и перейдем к «Вставка – Условное форматирование - Гистограммы». В выпадающем меню появится список шаблонов, при наведении на любой из них происходит пред. просмотр результата. Выбираем понравившуюся цветовую схему и видим, что ячейки залиты горизонтальными столбцами разной величины. Они отображают в графическом виде те значения, которые присутствуют в ячейках. Если число будет введено со знаком минус, то график сместится в противоположную сторону от ячейки, указывая на отрицательные величины (рис.10).
РИС.10 Условное форматирование - Гистограммы
Перед выполнением следующего задания удалим правила установки светофоров и гистограмм. Для этого выделим область нашей таблицы, затем выберем пункт «Условное форматирование»- «Удалить правила» - «Удалить правила со всего листа».
Компонент «Цветовые шкалы» заливает ячейки тем цветом, который соответствуют значению, введенному в нее. К примеру, наименьшие значения будет залиты в светло оранжевый цвет, средние - в оранжевый, а наибольшие - в темно оранжевый. Цветовая схема может быть подобрана вами индивидуально, но суть примерно остается тоже, что при использовании «Набор значков».
Итог получится приблизительно таким:
Но это еще не все способности визуализации данных. Рассмотрим еще такую удобную функцию как «Срезы». Допустим, выбранные работники отработали в компании весьма внушительный срок и сложно при формировании сводной таблицы выделить ту или иную дату. Есть два способа обращения к определённой дате. Когда мы строим сводную таблицу в правой части у нас расположены элементы, которые мы можем разместить в различные поля. Обращаемся к элементу «Даты» и вызываем выпадающее меню, путем нажатия на маркер со стрелочкой. Находим пункт «Фильтр по дате». Открывается огромный список с различными вариантами форматирования, но нам нужна помесячная сортировка. Открываем «Все даты за период» и выбираем «Октябрь». Сводная таблиц значительно сократилась, в ней остались значения только за октябрь.
САМОСТОЯТЕЛЬНОЕ ЗАДАНИЕ
Исходные данные – Склад:
| № | Дата | № накладной | Наименование | Получатель | Количество | Цена | Стоимость |
| 04.01.13 | Печенье | Весна | |||||
| 04.01.13 | Вафли | Весна | |||||
| 04.01.13 | Карамель | Лето | |||||
| 04.01.13 | Мармелад | Лето | |||||
| 04.01.13 | Шоколад | Сезам | |||||
| 05.01.13 | Печенье | Сезам | |||||
| 05.01.13 | Вафли | Сезам | |||||
| 05.01.13 | Мармелад | Весна | |||||
| 05.01.13 | Карамель | Весна | |||||
| 05.01.13 | Печенье | Лето | |||||
| 06.01.13 | Шоколад | Лето | |||||
| 06.01.13 | Вафли | Лето | |||||
| 06.01.13 | Печенье | Весна | |||||
| 08.01.13 | Мармелад | Сезам | |||||
| 08.01.13 | Карамель | Сезам | |||||
| 08.01.13 | Шоколад | Весна | |||||
| 08.01.13 | Карамель | Весна | |||||
| 09.01.13 | Газ. вода | Лето | |||||
| 09.01.13 | Печенье | Лето | |||||
| 09.01.13 | Мармелад | Лето | |||||
| 09.01.13 | Газ. вода | Весна | |||||
| 09.01.13 | Печенье | Весна | |||||
| 10.01.13 | Газ. вода | Сезам | |||||
| 10.01.13 | Мармелад | Сезам | |||||
| 10.01.13 | Шоколад | Весна | |||||
| 10.01.13 | Карамель | Весна |
Задания (результат выполнения каждого задания должен быть на отдельном листе)
1) Используя автофильтр, выбрать товары, отпущенные до 8 января 2013 года. Результат скопировать на новый лист и назвать его «Рождество». Отсортировать по дате и наименованию.
2) Определить количество мармелада, полученного одной из фирм.
3) Построить диаграмму изменения спроса на мармелад.
4) Подведение итогов. Составить ежедневный отчет по каждой фирме о стоимости полученного товара.
5) Сводные таблицы. Составить отчет для каждой фирмы о количестве и стоимости товара, полученного со склада, всего и по каждому наименованию.
6) Используя значения, полученные в предыдущем задании, определить скидку в оплате для каждой фирмы. Если стоимость товара больше 7000, скидка 15%, если от 3000 до 7000, то 10%, иначе скидки нет.
7) Сводные таблицы. Когда, сколько и по какой цене получала печенье фирма Весна.
8) Сводные таблицы. Составить отчет для каждой фирмы о количестве и стоимости товаров по каждой накладной.
9) Функция СУММЕСЛИ. Определите стоимость товаров, отпущенных каждой фирме. Результат оформите в виде таблицы.
| Получатель | Стоимость |
| Весна | |
| Лето | |
| Сезам |
10) Используя полученную таблицу, постройте круговую диаграмму «Структура поставок».
11) Функция СЧЕТЕСЛИ. Определите, сколько раз отпускали каждый товар. Результат оформите в виде таблицы:
| Наименование | Количество поступлений |
| Карамель | |
| Вафли | |
| Мармелад | |
| Шоколад | |
| Печенье |