Excel 2007: группировка данных в сводных таблицах
 | Обычно главной целью создания сводного отчета является представление данных в компактной, обобщенной форме, но так получается не всегда. Если количество разных значений ключевого поля по строке или колонке велико, то сводная таблица может оказаться большой. Анализировать ее будет сложно, а в некоторых случаях и вовсе бессмысленно. В такой ситуации можно применить так называемую группировку данных. Подробнее об этом полезном инструменте применительно к сводным отчетам Excel 2007 мы поговорим в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Операция группировки служит для того, чтобы объединить несколько строк или колонок таблицы в одно целое, сократив тем самым объем анализируемой информации. Группировку можно применить к любому количеству строк или колонок рабочего листа. Работает группировка и применительно к фрагментам сводной таблицы. Причем в каждом из этих случаев есть свои особенности, с которыми нам и предстоит разобраться. Начнем с самого простого.
Группировка данных на рабочем листе
Первый опыт использования группировки мы получим на примере базы данных продаж, изображенной на рис. 1. Делаем так:
1) открываем документ, переходим на лист «
Продажи»;2) выделяем несколько строк таблицы, например со «
2» по «13» (продажи за «03/01/09»);3) вызываем меню «
Данные»;4) в группе «
Структура» щелкаем на иконке «Группировать» (рис. 2). Слева от выделенных ячеек появится символ структуры. В нижней его части виден небольшой прямоугольник со знаком «-» (рис. 1);
5) щелкаем по этому значку левой кнопкой мыши. Строки с «
1» по «13» станут невидимыми. Вместо них в левой части листа останется значок с символом «+». Щелчок на этом символе снова покажет скрытые строки.Важно! Группировка данных на рабочем листе не выполняет никаких вычислений над ячейками электронной таблицы. В данном случае группировка — один из удобных способов организации данных, и не более того.
Где можно использовать группировку? Практически в любой таблице, которая обладает определенной структурой. Например, вы оформляете прайс-лист, в котором записана обширная номенклатура товаров. Эти товары по их назначению можно организовать в группы. Тогда вполне логично внести в прайс отдельные строки с названиями групп, ниже разместить конкретные ТМЦ с их характеристиками и ценами, а затем сгруппировать строки с товарами нижнего уровня. В этом случае при работе с прайсом появится возможность раскрыть интересующую группу товаров и работать только с ней. Вторая область применения группировки — подготовка отчетов к печати. Вы можете сделать подробные вычисления, конечные результаты вынести в отдельную строку, а промежуточные данные сгруппировать. Если свернуть группы, у вас останется краткий отчет. Раскрыв содержимое групп, вы получите доступ ко всем вычислениям.
Совет
Чтобы быстро сгруппировать выделенный блок строк или колонок, используйте комбинацию «Shift + Alt + →». Разгруппировать выделенный блок можно сочетанием клавиш «Shift + Alt + ←».Теперь делаем так:
1) выделяем блок колонок на листе «Продажи». Пусть это будут столбцы «C», «D», «E»;
2) в меню «Данные» находим группу иконок «Структура»;
3) щелкаем на иконке «Группировать». Над выделенными колонками появился элемент группировки данных (рис. 3). С его помощью вы можете скрыть или развернуть сгруппированные столбцы рабочего листа.
Важно!
Операцию группировки можно применить как к строкам, так и к столбцам рабочего листа. Кроме того, можно сгруппировать строки и колонки таблицы одновременно.Вернемся к таблице, изображенной на рис. 3. В ее верхнем левом углу расположены четыре элемента управления: два — для строк и два — для колонок. На рисунке они выглядят как прямоугольники с цифрами «1» и «2». С помощью этих элементов можно управлять степенью детализации отображения сгруппированных данных. Щелчок на значке с цифрой «1» сворачивает группу (показывает первый уровень детализации), а щелчок на значке с цифрой «2» — разворачивает.
Нумерацию этих элементов управления разработчики Excel использовали не случайно. Дело в том, что для элементов рабочего листа можно применить многократную группировку данных. Например, выделить строки «1-13» и сгруппировать их, затем внутри созданной группы выделить строки «5-8» и снова их сгруппировать. В результате получится трехуровневая группировка данных, в которой одна группа вложена в другую. Соответственно появится и дополнительный элемент управления с цифрой «3».
Чтобы отменить группировку данных, делаем так:
1) выделяем блок строк или колонок;
2) вызываем меню «Данные»;
3) в группе «Структура» щелкаем на иконке «Разгруппировать» или нажимаем «Shift + Alt + ← ».
Итак, с принципом группировки мы разобрались, но сделали это применительно к ячейкам рабочего листа. Теперь посмотрим, каковы особенности группировки данных применительно к сводным таблицам.
Группировка в сводных таблицах
Для начала создадим небольшой сводный отчет, форма которого показана на рис. 4. В нем по строкам расположены наименования товаров (поле «
НаимТов)». В области данных посчитана сумма по полю «Сумма» основной базы данных. Делаем так:
1) выделяем несколько ячеек в сводном отчете. Это может быть любой блок ячеек, находящийся внутри сводной таблицы, например «
A3:A4»;2) вызываем меню «
Данные»;3) в группе «
Структура» щелкаем на иконке «Группировать». Сводный отчет примет форму, как на рис. 5.
Слева в отчете появилась дополнительная колонка с названием «
НаимТов2». В третьей и четвертой строках этой колонки Excel 2007 сформировал группу с названием «Группа1». В нее он включил два элемента — «Блокнот» и «Бумага оф.». Все, как мы и указали. Остальные элементы сводной таблицы тоже превратились в группу. Об этом свидетельствует значок «свернуть/развернуть группу» со знаком «-». Но все эти группы состоят только из одного элемента. Дальше делаем так:1) ставим указатель на значок сворачивания группы возле элемента «
Группа 1»;2) щелкаем левой кнопкой мыши. Третья и четвертая строки сводного отчета сольются в одну с названием «
Группа 1» (рис. 6), а итоговое значение суммы продаж по этой группе составляет 55589,05 грн. (4441,05 грн. + 51148,00 грн.).
Важно!
При группировке строк в сводном отчете Excel автоматически формирует итоговые значения (сумму) по всем элементам группы. Это первое отличие группировки ячеек в сводной таблице от такой же операции над ячейками рабочего листа.Вторым очевидным отличием является наличие названия у создаваемой группы (в примере на рис. 5 это «Группа 1»). Правда, это название само по себе малопривлекательное. Делаем так:
1) ставим указатель активной ячейки на «А3» (рис. 5);
2) печатаем любое название группы;
3) нажимаем «Enter».
Важно!
Редактировать название группы сводного отчета можно как обычную ячейку рабочего листа: через клавишу «F2», в строке формул или непосредственно на рабочем листе. Но есть одна особенность: печатать данные прямо в ячейку с названием группы можно, а вот вставить данные из буфера обмена нельзя.Теперь о том, как отменить группировку.
Оставаясь в ячейке «А3», щелкаем на иконке «Разгруппировать» меню «Данные». Excel удалит группировку в сводном отчете. Кстати, выполнить такую операцию можно только для тех ячеек, где создана реальная группа. Например, если поставить активную ячейку в «A5» (рис. 5), то отмена группировки не даст никаких результатов.
Итак, сейчас перед нами исходная сводная таблица без группировок, как на рис. 4. Делаем так:
1) выделяем несколько отдельно стоящих (несмежных) ячеек, например писчие принадлежности «Карандаш» (строка «5») и «Ручка гел.» (строка «7»);
2) вызываем меню «Данные»;
3) в группе «Структура» щелкаем на иконке «Группировать». Сводный отчет примет форму, как на рис. 7. Excel сформировал группу из указанных элементов и правильно определил суммарные объемы реализации.
Важно!
Группировка несмежных ячеек возможна только при работе со сводными таблицами. Выполнить такое действие над ячейками рабочего листа в Excel 2007 нельзя.Как и в случае с рабочим листом, сводные таблицы можно группировать по колонкам. Делаем так:
1) открываем сводный отчет, как на рис. 4;
2) если окно «Список полей сводной таблицы» недоступно, открываем его (это удобно сделать через контекстное меню правой кнопки мыши);
3) находим поле «Дата» и перетаскиваем его в область «Названия столбцов». Теперь таблица показывает объемы реализации каждого ТМЦ по датам;
4) выделяем в сводном отчете (это должны быть именно ячейки отчета!) первые пять элементов. В нашем примере это даты с «01/01/09» по «05/01/09», которые расположены в ячейках «B2:D2»;
5) щелкаем на кнопке «Группировать» меню «Данные»;
6) становимся на ячейку «B2»;
7) печатаем название группы «I»;
8) повторяем группировку для остальных пятидневок сводной таблицы. Результат показан на рис. 8.
Как видим, все отличительные особенности группировки сводных таблиц в данном случае остались те же. Excel сформировал группы, присвоил им имена, элементы управления уровнями группировки он не создал. При сворачивании группы Excel показывает для нее итоговые значения.
Встроенные инструменты группировки сводных таблиц
В зависимости от типа поля, по которому выполняется группировка в сводной таблице, можно выделить два случая: группировка по числам и группировка по датам. Оба этих варианта отличаются незначительно, и все же в каждом есть свои особенности.
Г
руппировка числовых значенийЧтобы рассмотреть этот вопрос, перестроим сводный отчет, как показано на рис. 9. В этом отчете в области строк я разместил два поля — «
НаимТов» (название ТМЦ) и дату. Теперь делаем так:
1) ставим указатель активной ячейки на любую дату в сводном отчете, например на «
B3»;2) щелкаем правой кнопкой мыши, из контекстного меню (рис. 10) выбираем «
Группировать…». Появится окно настроек параметров группировки, как на рис. 11 на с. 26.
Важно!
Часто при формировании сводных таблиц неизвестно заранее точное количество записей в исходной базе данных. Поэтому при определении области с данными принято указывать только индексы колонок. И это обычная практика, так как она позволяет не перестраивать таблицу каждый раз при пополнении базы новыми записями. В случае работы с интервальными группировками по числам (или датам) такой подход неприменим! База данных для построения сводного отчета не должна содержать пустых записей. Если это не так, то по команде группировки окно «Группирование» не появится. Так что если вы собираетесь использовать интервальные группировки, то указывайте блок ячеек с данными для создания сводного отчета явно. В этом есть определенное неудобство, нос ним придется мириться.Вернемся к окну «Группирование» (рис. 11) и посмотрим на его содержимое подробнее. В верхней части окна расположены два параметра — «начиная с:» и «по:», которые определяют диапазон дат, в пределах которого Excel будет делать группировку в сводном отчете. При помощи флажков слева от каждого параметра их действие можно включить или отключить.
В центральной части (ниже подзаголовка «с шагом:»; рис. 11) перечислены семь возможных вариантов группировок по датам. Это «Секунды», «Минуты», «Часы», «Дни», «Месяцы», «Кварталы», «Годы». В нижней части окна «Группирование» находится параметр «количество дней:». С его помощью можно указать произвольный шаг группировки по датам с точностью до одного дня. Как взаимодействуют перечисленные параметры, мы посмотрим, продолжив работу с таблицей на рис. 9. Наша задача — пересчитать объемы товарооборота по месяцам и по кварталам. Делаем так:
1) открываем документ со сводной таблицей, как на рис. 9;
2) ставим указатель активной ячейки на любое значение поля «Дата», например на «B3»;
3) щелкаем правой кнопкой мыши, открываем контекстное меню (рис. 10), выбираем пункт «Группировать…». На экране появится окно «Группирование», как на рис. 11;
4) в этом окне параметры «начиная с:» и «по:» оставляем без изменений. Они определяют минимальное и максимальное значения для интервала группировки (с «03/01/2009» по «01/10/2009»), и нас это полностью устраивает;
5) в списке «с шагом:» выбираем вариант «Кварталы»;
6) нажимаем «ОК». Отчет приобрел форму, как на рис. 12. В нем количество отображаемых значений в колонке «Дата» сократилось до трех (это первые три квартала 2009 года), а все объемы товарооборота Excel перегруппировал по кварталам.
В таком формате намного удобнее анализировать динамику товарооборота за продолжительный период времени. Одного взгляда на отчет достаточно, чтобы увидеть почти двукратный прирост объема в
1) возвращаемся к сводной таблице на рис. 12;
2) щелкаем правой кнопкой мыши на любой ячейке в колонке «
Дата». Откроется контекстное меню, как на рис. 10;3) выбираем пункт «
Группировать…», откроется окно, как на рис. 11. Причем активным элементом в списке «с шагом:» будет «Кварталы»;4) щелкаем на элементе «
Месяцы». Обратите внимание, что при этом элемент «Кварталы» тоже остался подсвечен! То есть мы указали два уровня группировки для поля «Дата»;5) в окне «
Группирование» нажимаем «ОК». Отчет будет выглядеть, как на рис. 13. Excel сделал двойную группировку данных по значениям «Дата», в результате чего мы можем видеть объемы реализации как по месяцам, так и по кварталам.
Советую обратить внимание на вариант группировки дат по дням. При его использовании становится доступным флажок «
количество дней:» (рис. 11). Этот флажок позволяет установить произвольный интервал времени, для которого Excel сгруппирует даты. Таким образом, можно получить итоговые значения, например, за декаду, за каждые 10 дней и т. п.
Группировка числовых значений
Итак, при работе с полем типа «
Дата» в сводных таблицах Excel есть несколько предопределенных вариантов группировок. Логично предположить, что такая же ситуация имеет место при работе с другими типами полей сводной таблицы, например с числовыми. В принципе так оно и есть, только в качестве вариантов группировки Excel предложит воспользоваться интервалами значений ключевого поля. Посмотрим, как это выглядит на примере такой задачи. У нас есть база данных по объемам реализации ТМЦ (рис. 1). Мы хотим узнать, товары какой ценовой категории пользуются максимальным спросом. Для этого нам понадобится выбрать из базы все цены, разбить их на несколько групп (!) и определить объемы реализации (в количественном или стоимостном выражении) по каждой группе. Иными словами, нам понадобится функция группировки, причем группировать мы будем числовые значения.Начнем с того, что открываем файл с базой данных и формируем сводный отчет, как показано на рис. 14. В этом отчете в области строк ставим поле «
Цена». В область «Значения» переносим поле «Сумма», для которого выбираем операцию суммирования. Из отчета видно, что товаров стоимостью «0,1 грн.» за весь период продано на сумму 506,9 грн., а всего диапазон цен охватывает значения от «0,1 грн.» до «32 грн.». Сейчас мы разделим этот диапазон на четыре категории и оценим суммарные объемы реализации в каждой из них. Делаем так:
1) щелкаем правой кнопкой мыши на любом значении в колонке «
Цена»;2) из контекстного меню (рис. 10) выбираем пункт «
Группировать…». Откроется окно, изображенное на рис. 15. В нем по-прежнему есть параметры «начиная с:» и «по:». С их помощью мы определим начальное и конечное значения (интервал) ключевого поля. А вот параметр «с шагом:» теперь представлен единственным числом. Сразу проверяем положение флажков слева от полей «начиная с:» и «по:». Если они включены, Excel выберет интервал группировки самостоятельно. В нашем примере в поле «начиная с:» он поставит минимальную цену (0,1 грн.), а в поле «по:» — максимальную цену (32 грн.). В общем-то ошибки здесь нет, но в большинстве случаев группировать значения лучше самому, что мы сейчас и сделаем;
3) щелкаем левой кнопкой внутри поля «
начиная с:», вводим значение «0»;4) поле «
по:» оставляем без изменений (сейчас здесь находится цена «32»);5) щелкаем левой кнопкой на поле «
с шагом:», вводим сюда значение «1»;6) нажимаем «
ОК». Excel сгруппирует товары и посчитает суммарную выручку по каждому интервалу. Результат группировки сводной таблицы показан на рис. 16.
Из полученного отчета видно, что основную часть оборота формируют товары ценовой категории в диапазоне от 31 до 32 грн. (более 60 % от общей суммы). На второй позиции товары с ценами 1 — 2 грн. (19 % в общем объеме продаж) и т. д. Чтобы получить подробные сведения об этих продажах, делаем так:
1) ставим указатель активной ячейки на «
B6»;2) делаем двойной щелчок мышью на итоговом значении (на рис. 16 это сумма «
91729»). На отдельном листе Excel сформирует фрагмент базы данных продаж ТМЦ стоимостью от 31 до 32 грн. В нашем примере это позиция «Бумага оф.».И последнее замечание, касающееся одного интересного способа последовательной детализации данных при работе со сводными отчетами. Начнем с таблицы на рис 16. Делаем так:
1) становимся на ячейку «
A3». В ней записан интервал цен «0-1»;2) делаем двойной щелчок мышью. На экране появится окно «
Показать детали» (рис. 17), в котором представлен список полей исходной базы данных;
3) выбираем любое нужное нам поле, например «
НаимТов»;4) нажимаем «
ОК». Слева от поля «Цена» появились символы группировки данных. В сводный отчет Excel внедрил поле «НаимТов» (рис. 18). Теперь в этом отчете видны не только интервалы цен, но и товары, которые находятся в указанных ценовых диапазонах, а также объемы реализации этих товаров.
Продолжая действовать по описанной схеме, мы можем дважды щелкнуть на ячейке «
B3» (наименование товара), затем в окне «Показать детали» выберем, например, поле «Покупатель». После этого в сводном отчете будут показаны сведения об объемах реализации ТМЦ по каждому покупателю и т. д.Как видите, группировка данных — очень удобный и гибкий способ модификации сводных таблиц. Советую пользоваться им на практике как можно активнее.
А мы на этом завершим тему группировки данных и в следующий раз рассмотрим, какие способы вычислений предлагают сводные таблицы, в чем их особенности, как можно внедрить формулу в сводный отчет и что это дает бухгалтеру на практике.
Жду ваших вопросов, писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
