Пример 2. Вывод ТОП-20 и расчет доли от общей суммы.

Анализ данных с помощью сводных таблиц.

Сводные таблицы – быстрый и изящный способ работы с массивами данных. Инструментарий сводных таблиц настолько велик, что работе с ними посвящены многостраничные справочники. Возможно, именно это и отталкивает большинство пользователей от их использования в повседневной работе. Цель данной статьи – познакомить начинающего специалиста с возможностями сводных таблиц. Ниже приведены пошаговые инструкции по формированию 3 типов отчетов в версиях Excel 2007 и выше.

Источник данных.

Источником данных для построения сводных таблиц выступают таблицы с организованной структурой данных: в каждом столбце расположена однотипная информация, все столбцы должны быть обязательно поименованы (в противном случае, Excel не сможет создать сводную таблицу, и выдаст сообщение об ошибке).

Для наших примеров воспользуемся информацией о продажах клиентам, с указанием, какой продавец оформил заказ, суммы покупки и ранга покупателя в общем списке сделавших покупку за период (см. Таблицу 1).

Таблица 1. Пример источника данных для сводной таблицы.

Покупатель	Продавец	Продажи	Ранг
Акшонова Е	Иванов А.	42,98
Алехина М	Семенов Р.	199,95
Андреев В	Иванов А.	90,45
Баева М	Сидоров Е.	112,20
Балкина Е	Сидоров Е.	371,12
Бекешев Н	Семенов Р.	117,84
Белобородова А	Иванов А.	36,02

 

Пример 1. Представление исходной информации с группировкой.

Желаемый вид первого отчета представлен на рисунке 1:

· В отчете отражена информация о количестве покупателей и сумме продаж по каждому продавцу.

· Покупатели разбиты на 5 категорий в зависимости от их ранга в общей сумме продаж.

· Список продавцов отсортирован в порядке убывания продаж

 

Рисунок 1. Итоговый вид отчета о продажах по продавцам.

 

Приступим к формированию сводной таблицы.

1. Выделяем таблицу с данными (включая заголовки).

2. На вкладке ленты «Вставка» выбираем «Сводная таблица» (рис. 2)

3. В появившемся диалогом окне можно уточнить диапазон данных и выбрать место создания сводной таблицы: поместить ее на новый лист или уже на существующий (рис. 3). Рекомендуется сначала создать таблицу на новом листе, а после приведения таблицы к нужному виду переместить ее в желаемое место итогового отчета.

Рисунок 2. Создание сводной таблицы. Рисунок 3. Выбор места расположение таблицы.

4. В появившемся окне «Список полей сводной таблицы» задаем вид таблицы, перетаскивая названия полей (заголовки столбцов таблицы-источника) в соответствующие области таблицы (рис. 4). Для удобства разобьем формирование сводной таблицы на два этапа: сначала выведем только данные по количеству покупателей и объединим их в 5 групп, а затем добавим данные по продажам.

Рисунок 4. Создание макета сводной таблицы.

 

5. Чтобы сгруппировать ранги, встаем на любое значение ранга (для нашего примера это строка 4), вызываем правым кликом мышки контекстное меню, там выбираем пункт «Группировать» и в появившемся диалоговом окне задаем параметры группировки (рис. 5). В нашем случае это группировка с шагом в 20. Нажимаем «ОК» и видим, что наша длинная таблица из 83 столбцов значительно сократилась и стала более понятной для восприятия.

Рисунок 5. Группировка данных.

6. Теперь добавим в таблицу данные о продажах (рис. 6). Обратите внимание, как задан порядок полей в области «Названия столбцов».

Рисунок 6. Добавление второго поля значений.

7. Последним действием отсортируем продавцов в порядке убывания их продаж. Для этого встаем на любое значение поля «Покупатель», вызываем контекстное меню, в котором выбираем «Сортировка» - «Дополнительные параметры сортировки». В появившемся диалоговом окне выбираем «По убыванию» - «Сумма по полю Продажи» (рис. 7).

 

Рисунок 7. Сортировка данных.

 

8. Отчет готов! Осталось только переименовать названия столбцов и выбрать на вкладке «Конструктор» стиль сводной таблицы.

 

Итак, в 8 шагов Вы привели длинный список данных в удобный и красивый отчет. Теперь подумайте, сколько времени Вам бы потребовалось, чтобы получить подобный отчет, не используя сводные таблицы.

Сводные таблицы хороши еще тем, что однажды задав вид, вы легко можете использовать их для другого диапазона данных с аналогичной структурой (например, для анализа продаж следующего месяца). Чтобы повторить таблицу для другого диапазона данных, встаньте на таблицу и выберите на вкладке «Параметры» пункт «Изменить источник данных».

 

Пример 2. Вывод ТОП-20 и расчет доли от общей суммы.

Для демонстрации некоторых других возможностей сводной таблицы построим отчет, приведенный на рисунке 8:

· В отчете отражена информация о продажах первых 20 клиентов с их долей внутри группы ТОП.

· Продажи Иванова выделены цветом.

 

Рисунок 8. Итоговый вид отчета ТОП-20 клиентов.

 

Скопируем уже построенную таблицу на новый лист и изменим вид отчета:

1. Задаем желаемый макет отчета, как показано на рисунке 9. Видим, что изначально Excel предлагает сжатую форму отчета: все поля, заданные в названии строк, выведены в одном столбце. Иногда это бывает удобно, но в нашем примере мы хотим видеть разнородную информацию в отдельных столбцах.

2. Чтобы разнести данные из разных полей по разным столбцам, изменим макет отчета: на вкладке «Конструктор» выбираем «Макет отчета» - «Показать в табличной форме» (рис. 10).

Рисунок 9. Макет отчета. Рисунок 10. Выбор вида представления информации.

 

3. Чтобы убрать ненужные нам в отчете итоги по каждому клиенту, встаем на любое значение поля Покупатель, вызываем контекстное меню и в пункте «Параметры поля» выбираем пункт «нет» (рис.11). Заметьте, что в этом пункте Вы можете не только отказаться от вывода итогов по полю, но и изменить его тип на любой другой из представленного списка.

Рисунок 11. Отключение вывода итогов по полю Покупатель

 

4. Теперь заменим во втором поле «Продажи» значения в рублях на проценты. Встаем на любое значение поля «Сумма по полю Продажи2», в контекстном меню выбираем пункт «Параметры полей значений», где переходим на вкладку «Дополнительные вычисления» и выбираем в списке пункт «Доля от суммы по столбцу» (рис. 12).

 

Рисунок 12. Использование вкладки «Дополнительные вычисления» для расчета доли продаж.

 

5. Мы получили отчет с данными о продажах и их доле в общей сумме продаж по всем покупателям. Чтобы оставить в нем только первых двадцать покупателей, встаем на поле «Покупатель», в контекстном меню выбираем «Фильтр» - «Первые 10», в появившемся диалоговом окне задаем нужное нам количество значений (рис. 13).

6. Общий вид отчет готов. Осталось только отформатировать его шапку и выделить цветом продавца Иванова. Для этого наводим курсор мышки на ячейку со значением «Иванов А.» влево до тех пор, пока не появится черная стрелка и кликаем левой клавишей мышки. Сводная таблица сама выделит все ячейки, содержащие это значение (рис 14). Выбираем на главном или контекстном меню цвет для выделенных ячеек.

Рисунок 13. Установка фильтра по полю «Покупатель» Рисунок 14. Выделение однотипных значений.