Второй пример создания сводной таблицы

Исходные данные:

 

 

Построим вот такую сводную таблицу:

Когда таблица построена, перейдем к ее оформлению. Для начала изменим цветовую схему, применив к ней шаблон. Переходим на вкладку «Главная» и нажимаем на кнопку «Форматировать как таблицу». На экране появится список различных шаблонов форматирования, выбираем понравившийся нам и нажимаем на него. 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) Функция СЧЕТЕСЛИ. Определите, сколько раз отпускали каждый товар. Результат оформите в виде таблицы:

Наименование	Количество поступлений
Карамель
Вафли
Мармелад
Шоколад
Печенье