Excel: группировки в сводных таблицах
Приветствую всех сотрудников «Б & K»! У меня вопрос по сводным таблицам Excel. Можно ли сделать так, чтобы в пределах одного сводного отчета пересчитывать данные с разной степенью детализации? Например, построить таблицу с итогами по дням, а затем пересчитать ее по месяцам, кварталам, в целом за год и т. д. И как это сделать? Заранее благодарю.
С уважением, С. Цымбалюк, г. Харьков
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Для решения такой задачи есть несколько способов. Первый — добавить к исходным данным рабочее поле (так называемый рабочий ключ) и только затем строить сводный отчет. Например, у вас есть база данных с колонкой «
Дата». Информация в ней представлена с точностью до дня. Вы создаете колонку «Месяц». В ней пишете формулу, которая выделяет из даты номер месяца, и только после этого строите сводный отчет, выбрав в качестве поля группировки «Месяц». Таким образом, в результате вы получите итоговые данные с детализацией по месяцам. Это один подход. У него много преимуществ, но есть и недостаток: конкретная реализация такой схемы зависит от исходных данных. Не видя реальной базы данных, трудно предложить универсальный метод построения рабочего ключа.Но есть и другой способ. Он опирается исключительно на возможности сводных таблиц
MS Excel. В этом подходе тоже есть свои ограничения, но они, на мой взгляд, незначительные. Именно на способе группировки средствами сводных таблиц мы сейчас и остановимся подробнее.Итак, есть база данных, изображенная на рис. 1. В базе представлены сведения о реализации оргтехники, а точнее, печатающих устройств. Каждая строка базы описывает одну продажу. По каждой продаже нам известен:
— номер заказа (поле «Заказ»);
— дата оформления заказа (поле «Дата»);
— название устройства (поле «
Наименование»);— стоимость устройства в конкретном заказе (поле «
Цена»);— количество заказанных экземпляров (поле «
Кол.»);— общая сумма по строке заказа (поле «
Сумма»).Я хочу узнать, товары какой ценовой группы пользуются максимальным спросом. Для этого я должен выбрать из базы все цены, разбить их на группы (!) и посчитать объем заказов по каждой группе. Для решения такой задачи нам как раз и понадобится функция группировки. Причем группировать мы будем числовые значения.
Группировка числовых значений
Прежде, чем применить группировку, нужно иметь то, что группировать. Поэтому первым делом строим сводную таблицу:
1) вызываем «
Данные → Сводная таблица»;2) на запрос об источнике данных выбираем вариант «
в списке или базе данных Microsoft Office Excel»;3) в ответ на предложение указать диапазон с данными выделяем на рабочем листе «
БД» (рис. 1 на с. 61) колонки с «А1» по «F8200» включительно (ни одной пустой строки в базе данных быть не должно!);4) нажимаем кнопку «
Далее». Excel покажет окно с предложением определить параметры сводной таблицы или построить макет. Нажимаем кнопку «Макет»;5) в области строк ставим поле «
Цена». В область данных переносим поле «Сумма»;6) делаем двойной щелчок мышью на поле «
Сумма по полю Сумма». Появится окно «Вычисление поля сводной таблицы» со списком доступных операций. Выбираем из списка операцию «Сумма»;7) в поле «
Имя:» пишем название «Сумма, грн.». В окне «Вычисление поля сводной таблицы» нажимаем «ОК». Форма макета примет вид, как на рис. 2; 
8) в окне создания макета нажимаем «
ОК», попадаем в основное окно Мастера сводных таблиц;9) здесь нажимаем кнопку «
Параметры», откроется окно «Параметры сводной таблицы»;10) в этом окне отключаем флажки «
общая сумма по столбцам», «общая сумма по строкам», «автоформат»;11) нажимаем «
ОК», чтобы вернуться в основное окно Мастера сводных таблиц;12) нажимаем «
Готово».В результате мы получили сводный отчет, форма которого показана на рис. 3. Теперь можно начинать группировку.
Важно!
При работе со сводными таблицами база данных может содержать пустые строки. Этим часто пользуются на практике. Например, вместо точного блока значений с исходными данными вводят только индексы колонок. Это удобно, поскольку не нужно перестраивать таблицу при пополнении базы данных новыми записями. В случае работы с интервальными группировками по числам (или датам) такой подход неприменим! База с группировками не должна содержать пустых записей. Поэтому при формировании сводной таблицы придется явно указывать блок ячеек с исходными данными. Конечно, в этом есть определенное неудобство, но с ним придется смириться.Наша задача — определить, на каких товарах (дешевых, дорогих или находящихся в среднем ценовом диапазоне) предприятие получает основную часть товарооборота. Для этого нужно разбить все множество цен на интервалы и затем пересчитать итоговые значения сводного отчета для каждого интервала в отдельности. В нашем примере стоимость принтеров колеблется от 455 грн. (модель «
Printer Canon pixma ip1900») и до 3585 грн. (модель «Printer Epson Stylus Photo 1410 A3»). Разобьем этот диапазон на восемь интервалов с шагом 500 грн. Делаем так:1) ставим указатель на любом значении поля «
Цена» сводного отчета. Например, на ячейку «A5»;2) щелкаем правой кнопкой мыши. Появится контекстное меню, как на рис. 4 на с. 63;
3) из контекстного меню выбираем «
Группа и структура», затем «Группировать…». Появится окно, как на рис. 5 на с. 63. Это главное окно группировки. В нем можно задать начальное и конечное значение интервала и шаг изменения. Обратите внимание на положение флажков слева от полей «начиная с:» и «по:». Если они включены, Excel выберет интервал группировки цен сам, автоматически. В нашем примере в поле «начиная с:» он поставит минимальное значение цены (455 грн.), в поле «по:» — максимальное значение (3585 грн.). В большинстве случаев это неправильно. Поэтому группировку лучше сделать самому. Исправим значения в окне «Группирование»;4) щелкаем левой кнопкой внутри поля «
начиная с:». Вводим значение «0»;5) щелкаем левой кнопкой на поле «
с шагом:» и печатаем сюда «500»;6) нажимаем «
ОК». Excel сгруппирует товары и посчитает суммарную выручку по каждому интервалу. Результат группировки сводной таблицы показан на рис. 6. 
Из полученного отчета видно, что основную часть оборота формируют товары средней ценовой категории в диапазоне от 1000 до 2000 грн. (более 48 % от общей суммы).
Чтобы отменить группировку и вернуться к исходной форме сводной таблицы, делаем так:
1) ставим указатель активной ячейки на любом значении поля «
Цена»;2) щелкаем правой кнопкой мыши;
3) из контекстного меню (рис. 4) выбираем «Группа и структура → Разгруппировать
». Таблица примет первоначальный вид.
Группировка дат
Возможности группировки данных в сводных таблицах зависит от типа исходных данных. До сих пор мы группировали числа. Сейчас посмотрим, что можно сделать с датами. Наша задача будет такой: пересчитать объемы товарооборота по месяцам и кварталам. Делаем так:
1) открываем документ со сводной таблицей, как на рис. 3;
2) становимся в область сводного отчета, щелкаем правой кнопкой мыши;
3) из контекстного меню (рис. 4) выбираем пункт «
Мастер сводных таблиц». Откроется окно Мастера на третьем шаге;4) нажимаем кнопку «
Макет»;5) ставим указатель мыши на поле «
Цена» (сейчас оно находится в области строк);6) удерживая нажатой левую кнопку мыши, перетаскиваем поле за пределы области макета и отпускаем кнопку мыши;
7) в левой части макета находим поле «
Дата» и переносим его в область строк. Окончательно макет отчета должен выглядеть, как на рис. 7; 
8) нажимаем кнопку «
ОК», затем «Готово». Результат формирования сводной таблицы показан на рис. 8 на с. 64. Пока из этого отчета мы можем увидеть объемы товарооборота по дням; 
9) ставим указатель активной ячейки на любое значение поля «
Дата». Например, на ячейку «A5»;10) щелкаем правой кнопкой мыши, из контекстного меню (рис. 4) выбираем «
Группа и структура → Группировать…». На экране появится окно, как на рис. 9. Это окно для группировки дат. Назначение полей «начиная с:» и «по:» не изменилось. Они определяют минимальное и максимальное значение для интервала группировки. А вот шаг группировки при работе с датами оформлен в виде списка из семи значений: «Секунды», «Минуты», «Часы», «Дни», «Месяцы», «Кварталы», «Годы»; 
11) выбираем значение «
Месяцы». Нажимаем «ОК». Сводная таблица примет форму, как показано на рис. 10. В ней уже информация представлена по месяцам. В таком формате намного удобнее анализировать динамику товарооборота за продолжительный период времени. Одного взгляда на отчет достаточно, чтобы увидеть существенный прирост показателя в мае — июне 2009 года. Движемся дальше; 
12) возвращаемся к сводному отчету. Ставим активную ячейку на «
A5». Вызываем контекстное меню и выбираем «Группа и структура → Группировать…»;13) в настройках параметров группировки (рис. 9) выбираем «
Кварталы». Форма сводного отчета изменится. Она будет выглядеть, как показано на рис. 11: объемы товарооборота Excel перегруппировать по кварталам; 
14) возвращаемся к сводному отчету. Вызываем контекстное меню и выбираем «Группа и структура → Группировать…»;
15) в окне настройки параметров группировки из списка «с шагом:» выбираем ДВА значения, например, «Месяцы» и «Кварталы». Excel выполнит двойную группировку данных, в результате чего мы сможем видеть суммарные значения объемов реализации как по месяцам, так и по кварталам. Пример отчета с такой группировкой показан на рис. 12.
Важно!
Двойной щелчок левой кнопкой на любом значении поля группировки сворачивает группу. Повторный двойной щелчок разворачивает группу (показывает ее состав подробно). Попробуйте сделать это на примере отчета, изображенного на рис. 12. В данном случае щелчок нужно делать на любой ячейке в пределах колонки «Наименование».
Произвольная группировка
На самом деле группировать в сводных таблицах можно не только числа или даты. Сгруппировать можно все что угодно. Для примера построим краткий отчет о продажах по категориям товаров. Делаем так:
1) формируем сводную таблицу, поставив в область строк поле «
Наименование», в области данных оставляем «Сумма». Получаем таблицу, как на рис. 13; 
2) в колонке «
Наименование» (столбец «А») выделяем все элементы, относящиеся к принтерам. В примере это строки «A5:A24»;3) на выделенном блоке щелкаем правой кнопкой мыши;
4) из контекстного меню выбираем «
Группа и структура → Группировать…». Слева от колонки «Наименование» появится еще один столбец с названием «Наименование2». Первым элементом в этом столбце будет объединенная ячейка с названием «Группа1»;5) вместо текста «
Группа1» печатаем «Принтеры». Нажимаем «Enter»;6) делаем двойной щелчок мышью на сгруппированном поле. Excel свернет все записи о принтерах в одну строку. Есть другой способ свернуть группу — щелкнуть правой кнопкой мыши и выбрать из контекстного меню «
Группа и структура → Скрыть детали»;7) повторяем такие же действия для строк с записями о многофункциональных устройствах. Эти элементы я назвал в сгруппированном отчете «
МФУ»;8) дважды щелкаем мышью на заголовке «
Наименование2». Откроется окно «Вычисление поля сводной таблицы»;9) в область «
имя:» печатаем текст «Группа тов.», нажимаем «ОК». Результат нашей работы показан на рис. 14. В этом отчете посчитан объем товарооборота по каждой из двух категорий товаров. Для принтеров он составил 3387195 грн., для МФУ — 2029970 грн. 
Важно!
При ручной группировке в исходной базе данных могут находиться пустые строки.Как видите, группировка данных — исключительно удобный и гибкий способ модификации сводных таблиц. Советую всем пользоваться им на практике как можно активнее.
Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Удачной работы!
