Excel 2007: вычисления в сводных таблицах
 | Говоря о сводных таблицах Excel 2007, мы до сих пор касались «внешней» стороны этого инструмента. Нас интересовали главным образом способы формирования макета сводного отчета, группировки и представления данных. Сегодня наша задача — заглянуть внутрь сводных таблиц, т. е. познакомиться с механизмом организации вычислений в сводном отчете. На самом деле этот вопрос для бухгалтера весьма актуален. Не все расчеты нужно делать с помощью формул на рабочем листе, поэтому некоторые операции имеет смысл выполнять именно в сводных таблицах и никак иначе. Тем более что Excel 2007 предоставляет для этого все необходимые возможности. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
При создании сводных отчетов мы уже пользовались операциями суммирования данных и определения количества значений для вычисляемого поля. Сейчас наша задача — посмотреть, какие дополнительные возможности в плане организации вычислений могут предложить бухгалтеру сводные таблицы Excel 2007. И сделаем мы это на примере базы данных продаж из предыдущей статьи (см. «Б & К», 2010, № 11, с. 25).
План наших действий будет такой. За основу мы возьмем базу данных, изображенную на рис. 1. В ней собраны сведения о продажах, зафиксированные за каждую дату отчетного периода. Вначале на примере этой базы мы построим несколько несложных отчетов, поработаем с группой основных операций сводной таблицы и выясним особенности их поведения при использовании нескольких ключевых полей. Затем мы перейдем к дополнительным операциям со сводными отчетами, причем эту часть операций мы рассмотрим в сочетании с приемом создания так называемого рабочего ключа. Мы уже пользовались рабочими ключами при консолидации данных (см. статью «Excel 2007: консолидация данных (ч. 2)» // «Б & К», 2010, № 7) и теперь посмотрим, как это работает в сводных таблицах.
Напомню, что рабочий ключ — это вспомогательное поле (колонка значений), которое можно использовать для специфической группировки информации. Обычно ключ формируют формулой на основе имеющихся значений в базе данных. По своему назначению рабочий ключ похож на режимы группировки сводных таблиц, но между ними есть существенные отличия. Чтобы разобраться, в чем они состоят, мы поступим следующим образом. Вначале создадим несколько сводных отчетов, применяя различные режимы группировки значений в сочетании с дополнительными вычислениями. Затем выполним те же действия, но вместо группировки воспользуемся рабочим ключом и посмотрим, какой способ работы предпочтительнее. Вот такой план. Чтобы приступить к его реализации, вначале нужно подготовить данные. С этого мы и начнем.
Подготовка исходных данных
Открываем базу данных, изображенную на рис. 1. С помощью сводных таблиц мы можем получить из этой базы итоговые значения, но они будут излишне детализированные. Так, собрав итоги по датам, мы увидим объемы реализации за каждый день, а дней этих в нашей базе более 200… Разумеется, анализировать такой объем информации практически нереально. Поэтому, чтобы получить объемы реализации по месяцам или по кварталам (не прибегая к группировке данных!), нам понадобится другое ключевое поле. Предположим, что мы решили объединить данные по месяцам. Для этого в отдельной колонке запишем формулу, которая для каждой даты вернет значение в формате «Номер месяца-Номер года». Это и будет первый рабочий ключ. Используя этот ключ при создании сводной таблицы, мы без проблем определим объемы реализации за каждый месяц отчетного периода. Второй рабочий ключ должен сформировать для каждой даты строку с номером квартала. Имея эти данные, мы сможем мгновенно посчитать объемы реализации за каждый квартал. В принципе для решения такой задачи можно воспользоваться механизмом группировки значений в поле «
Дата», но это не всегда удобно. Во-первых, группировка требует точного определения блока в исходной базе, и мы это уже знаем. Во-вторых, при выполнении вычислений удобнее пользоваться рабочей колонкой, а не сгруппированными значениями, и мы в этом скоро убедимся. А пока делаем так:1) открываем базу данных, изображенную на рис. 1;
2) становимся на ячейку «
H1» и вводим заголовок «Мес.»;3) переходим на ячейку «
H2», сюда вводим формулу: «=ЕСЛИ(МЕСЯЦ(A2)=1;"Янв";ЕСЛИ(МЕСЯЦ(A2)=2;"Фев";ЕСЛИ(МЕСЯЦ(A2)=3;"Март"; ЕСЛИ(МЕСЯЦ(A2)=4;"Апр";ЕСЛИ(МЕСЯЦ(A2)=5; "Май";ЕСЛИ(МЕСЯЦ(A2)=6;"Июнь";ЕСЛИ (МЕСЯЦ(A2)=7;"Июль";"")))))))&ЕСЛИ(МЕСЯЦ (A2)=8;"Авг";ЕСЛИ(МЕСЯЦ(A2)=9;"Сен";ЕСЛИ(МЕСЯЦ(A2)=10;"Окт";ЕСЛИ(МЕСЯЦ(A2)=11;"Ноябрь";ЕСЛИ(МЕСЯЦ(A2)=12;"Дек";"")))))»;4) копируем формулу на всю высоту таблицы. Теперь в колонке «
H» для каждой даты появится название месяца.Пару слов о том, как создать формулу. Она длинная, но по сути простая. Сделайте так:
1) станьте в ячейку «
H1»;2) в строке формул нажмите «
fx» — появится Мастер функций;3) с помощью Мастера вставьте в «
H1» функцию «Если()». Параметры ее не заполняйте, укажите фиктивные значения, например «1», «1», «0», т. е. в первом приближении вы получите формулу «Если(1;1;0)»;4) перейдите в ячейку «
H3»;5) при помощи Мастера функций вставьте в эту ячейку формулу «
Месяц(A2)». Функцию «МЕСЯЦ()» вы найдете в категории «Дата и время»;6) нажмите «
F2», чтобы перейти в режим редактирования ячейки «H3»;7) выделите текст формулы без символа «
=» и скопируйте его в буфер обмена;8) нажмите «
Esc», чтобы вернуться на рабочий лист;9) перейдите на ячейку «
H2», войдите в режим редактирования формулы;10) вместо первого параметра функции «
Если(1;1;0)» (этот параметр выделен полужирным начертанием) вставьте содержимое буфера обмена. В результате должно получиться «Если(Месяц(A2);1;0)»;11) завершите создание условия проверки, дополнив его операцией сравнения «
=1». Вы должны получить выражение «Если(Месяц(A2)=1;1;0)» (изменения выделены полужирным);12) вместо второго параметра функции «
Если()» наберите «Янв». У вас получится «Если(Месяц (A2)=1; "Янв";0)»;13) скопируйте функцию «
Если()» без символа «=» в буфер обмена. Напомню, что сейчас мы находимся в режиме редактирования формулы, поэтому «F2» нажимать не нужно;14) перейдите в конец формулы, удалите третий параметр (сейчас это «
0») и вставьте вместо него содержимое буфера обмена. Вы получите такую формулу: «ЕСЛИ(МЕСЯЦ(A2)=1; "Янв"; ЕСЛИ(МЕСЯЦ(A2)=1;"Янв";0))»;15) во второй (вложенной) функции «
ЕСЛИ()» измените условие сравнения: вместо «=1» нужно ввести «=2». Кроме того, значение «Янв» в этой функции замените на «Фев». В результате вы должны получить такое выражение: «=ЕСЛИ(МЕСЯЦ (A2)=1;"Янв";ЕСЛИ(МЕСЯЦ(A2)=2;"Фев";0))»;16) удалите последний параметр «
0» вложенной функции «Если()», еще раз вставьте выражение из буфера обмена и откорректируйте выражение для третьего месяца. Повторите эти действия семь раз.В результате вы получите часть формулы, которая обрабатывает месяцы с номерами с «
1» по «7». Дальше продолжать наращивать формулу нельзя, так как максимальное количество вложенных функций в Excel 2007 равно семи, но это не страшно. В отдельной ячейке аналогичным образом постройте функцию «Если()» для месяцев с «8» по «12» и затем объедините полученные выражения операцией «&». Рабочий ключ для группировки дат по месяцам мы создали, осталось сделать то же самое для кварталов. Делаем так:1) становимся на ячейку «
I1» и вводим заголовок «Кв.»;2) переходим на ячейку «
H2», сюда вводим формулу: «=ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)=1; "1 кв."; ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)=2; "2 кв."; ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)=3; "3 кв."; ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)=4;"4 кв.";""))))». В этой формуле номер квартала по дате находит формула «=ЦЕЛОЕ((МЕСЯЦ (A2)+2)/3)», а вложенные функции «Если()» нужны для того, чтобы вместо обезличенного номера поставить текстовое значение;3) копируем формулу на всю высоту таблицы. Теперь в колонке «
I» для каждой даты появится номер квартала. Окончательный вид таблицы с исходной базой данных показан на рис. 1.
Группа основных операций в сводной таблице
Получив сводный отчет, вы можете обнаружить, что по итоговым числам нужно выполнить дополнительные вычисления, например определить прирост показателя в процентах или найти его удельный вес в общей сумме. Я не раз был свидетелем того, как для решения такой задачи использовали обычные формулы. И совершенно напрасно. Разумеется, без формул в Excel не обойтись, но в данном конкретном случае есть своя специфика.
Во-первых, создавать ссылки на ячейки сводной таблицы неудобно. Вы наверняка помните, что при создании ссылки на ячейку сводного отчета Excel вставляет не адрес рабочего листа, а функцию «
Получить.данные.сводной.таблицы()», что само по себе не всегда кстати. Во-вторых, внедрение формул непосредственно в сводный отчет невозможно. Для этого придется вначале преобразовать сводную таблицу в обычную и только потом делать в ней вычисления. А как быть, если вы хотите сохранить сводный отчет? Наконец, возникает закономерный вопрос: для чего делать лишнюю работу по созданию и копированию формул, если можно попытаться все получить прямо в сводном отчете? И сейчас мы посмотрим, что в этом направлении можно сделать. Начнем с основных вычислений. Делаем так:1) открываем документ с базой данных. Комбинацией «
Shift+F11» вставляем новый лист (в примере он называется «СВ»). Здесь мы будем формировать сводный отчет;2) ставим указатель активной ячейки на «
A1» листа «СВ»;3) вызываем раздел «
Вставка» основного меню, в группе «Таблицы» щелкаем на иконке «Сводная таблица»;4) в открывшемся меню выбираем пункт «
Сводная таблица». Появится окно «Создание сводной таблицы»;5) щелкаем на параметре «
Таблица или диапазон:». Переходим на лист «Продажи» и обводим диапазон «A:I» так, чтобы захватить рабочие колонки;6) в окне «
Создание сводной таблицы» нажимаем кнопку «ОК». На рабочем листе появится макет сводного отчета и откроется окно настроек с названием «Список полей сводной таблицы»;7) в этом окне поле «
НаимТов» ставим в область строк, в область «Значения» три раза добавляем поле «Сумма» (рис. 2 на с. 32); 
8) щелкаем левой кнопкой на первом поле «Сумма» в области «Значения». Откроется меню, как на рис. 2;
9) выбираем пункт «Параметры полей значений…». Откроется окно «Параметры поля значений» (рис. 3 на с. 32);
10) на закладке «Операция» выбираем вариант «Сумма», в поле «Пользовательское имя:» вводим «Сумма, грн.»;
11) нажимаем «ОК»;
12) щелкаем на втором поле «Сумма» в области «Значения» окна «Список полей сводной таблицы»;
13) для этого поля выбираем операцию «Количество», в области «Пользовательское имя:» вводим «Кол., шт.»;
14) нажимаем «ОК»;
15) щелкаем на третьем поле «Сумма» в области «Значения»;
16) в качестве операции выбираем «Максимум», в области «Пользовательское имя:» вводим «Макс. сумма, грн.»;
17) нажимаем «ОК». В результате получим отчет, как на рис. 4.
Посмотрим, что же мы получили. В первой строке отчета просуммированы объемы продаж по каждому товару. Например, для ТМЦ «Блокнот» эта сумма составила 18165,35 грн. (ячейка «C2»). Строкой ниже находится то же поле «Сумма», но для него мы определили операцию «Количество». Поэтому число в ячейке «С3» — это количество фактов продаж ТМЦ «Блокнот» за весь период. Таких продаж в нашем случае 470. В третьей строке отчета мы показали максимальное значение для поля «Сумма». Значение «88,75» в ячейке «С4» — это максимальная сумма сделки по товару «Блокнот», которая содержится в нашей базе данных.
Нам сталось ответить на последний вопрос: как поведет себя операция поиска максимального значения при создании сводного отчета по двум ключевым полям? Делаем так:
1) открываем сводный отчет, как на рис. 4;
2) ставим в область колонок название квартала (поле «Кв.» на рис. 1). Фрагмент обновленного отчета показан на рис. 5 на с. 33.
Как и следовало ожидать, максимальное значение Excel определил по тем фрагментам исходной базы данных, которые участвовали в расчете показателей конкретной ячейки сводного отчета. Например, в ячейке «С5» записана величина 88,75. Это не что иное, как максимальная сумма сделки по ТМЦ «Блокнот» в I квартале 2009 года. Значение в ячейке «F5» составляет 117,15. Его Excel определил по всем продажам товара «Блокнот», которые есть в исходной базе данных.
Пожалуй, остальные операции из группы основных вычислений рассматривать нет смысла, так как все они работают по одному принципу. На мой взгляд, в практике бухгалтера могут пригодиться такие варианты:
— «Сумма» (определение суммы значений для ключевого поля);
— «Количество» (определение количества значений ключевого поля);
— «Максимум» (поиск максимального значения для ключевого поля);
— «Минимум» (поиск максимального значения для ключевого поля);
— «Среднее» (вычисление среднего арифметического значения для ключевого поля);
— «Произведение» (вычисление произведения значений ключевого поля).
Остальные операции ориентированы на статистическую обработку базы данных и в практике бухгалтерского учета практически не применяются.
Дополнительные вычисления в сводной таблице
Абсолютные значения показателей, безусловно, важны. Однако они не всегда отражают реальную картину происходящего. Иногда нужно анализировать процентные изменения, тенденции, темпы роста и т. п. Такие расчеты широко представлены в сводных таблицах Excel 2007. Получить к ним доступ можно через закладку «
Дополнительные вычисления» в окне «Параметры поля значений» (рис. 3). Сейчас мы посмотрим, как этим воспользоваться, но вначале изменим задачу. Мы построим отчет относительно поквартальных объемов продаж каждым контрагентом. Во-первых, такой отчет будет более компактным. Во-вторых, в нем логичнее выглядят такие показатели, как изменение объемов реализации, доля в общем объеме и т. п. Приступим.
О
перация «Отличие» по элементам строк сводного отчетаС нее мы начнем знакомство с возможностями дополнительных вычислений в сводных таблицах Excel 2007. Делаем так:
1) открываем сводную таблицу, изображенную на рис. 5. Ее мы возьмем за основу;
2) в область строк переносим поле «
Покупатель». Соответственно поле «НаимТов» из области строк удаляем;3) в области колонок оставляем поле «
Кв.» (номера кварталов, полученные из рабочего ключа!);4) в области «
Значения» оставляем поле «Сумма», по которому считается выручка в гривнях (в отчете на рис. 5 эта строка озаглавлена как «Сумма, грн.»);5) остальные поля из области «
Значения» удаляем: нам проще добавить недостающие элементы заново;6) переносим в область «
Значения» поле «Сумма»;7) щелкаем левой кнопкой мыши на этом поле. Откроется меню, как на рис. 2;
8) выбираем пункт «
Параметры полей значений…». Откроется окно «Параметры поля значений» (рис. 3);9) на закладке «
Операция» выбираем вариант «Сумма». В поле «Пользовательское имя:» появится текст «Сумма по полю Сумма» (запомните этот факт!);10) щелкаем левой кнопкой на поле «
Пользовательское имя:», печатаем название «Прирост, грн.»;11) щелкаем на закладке «
Дополнительные вычисления». Окно примет вид, как на рис. 6 на с. 34; 
12) щелкаем на параметре «Дополнительные вычисления». Откроется список, как на рис. 7 на с. 34;
13) выбираем вариант «Отличие»;
14) в левой части окна на рис. 6 находится список «поле:». В этом списке выбираем элемент «Кв.»;
15) в списке «элемент:» указываем значение «1 кв.»;
16) нажимаем «ОК». Отчет примет форму, как на рис. 8.
В этом документе кроме абсолютного значения выручки от продаж мы определили изменение этой суммы относительно I квартала 2009 года. Для проверки полученного значения введите, например, формулу: «=D3-C3». Результат ее работы будет 1261,7 (6924,85 - 5663,15) (рис. 8).
Обобщим этот результат, вернувшись к таблице на рис. 6. Операция в списке «Дополнительные вычисления» указывает на характер расчетов. Значение «Отличие» означает, что мы будем определять разность между определенными элементами сводного отчета.
В списке «поле:» мы указываем одну из переменных, которые расположены в области строк или колонок. В нашем случае мы выбрали поле «Кв.». Исходя из этого Excel «знает», что отличие (пророст/убыль) нужно определять между парами переменных в области данных, которые находятся в одной строке (т. е. между соседними кварталами). Теперь остается указать, какой из кварталов должен служить базой для сравнения. Этот параметр определяет значение из списка «элемент:». На рис. 6 мы выбрали в этом списке значение «1 кв.», а это значит, что отклонение текущего значения будет считаться относительно I квартала 2009 года.
Операция «Отличие» по элементам колонок сводного отчета
Изменим сводную таблицу, изображенную на рис. 8. Теперь наша задача — сравнить объемы реализаций между отдельными контрагентами в пределах каждого квартала. Делаем так:
1) становимся внутрь сводной таблицы, открываем окно «
Список полей сводной таблицы»;2) щелкаем левой кнопкой на поле «
Прирост, грн.» (оно находится в области «Значения»);3) из раскрывшегося меню выбираем «
Параметры полей значений…» (рис. 2). Откроется окно «Параметры поля значений»;4) переходим на закладку «
Дополнительные вычисления»;5) в качестве операции оставляем «
Отличие», в списке «поле:» выбираем «Покупатель»;6) переходим на список «
элемент:» и указываем «ООО «Эталон»»;7) нажимаем «
ОК». Отчет примет форму, как на рис. 9 на с. 35. 
В этом отчете прирост/убыль объемов реализации посчитаны относительно контрагента «ООО "Эталон" ». Например, в I квартале 2009 года сумма продаж по «ООО "Талан" » превысила аналогичный показатель «ООО "Эталон" » на величину: 6988,25 - 5663,15 = 1325,10 грн. (ячейка «С6»).
Выбор базы для операции «Отличие»
Сейчас наша задача — изменить базу расчетов, т. е. мы хотим изменить число, относительно которого Excel считает прирост/убыль заданного показателя. Для этого возвращаемся к сводной таблице на рис. 8 и делаем так:
1) становимся внутрь сводного отчета;
2) открываем окно «
Список полей сводной таблицы»;3) щелкаем на поле «
Прирост, грн.» в области «Значения»;4) из появившегося меню выбираем «
Параметры полей значений…» (рис. 2). Откроется окно «Параметры поля значений»;5) переходим на закладку «
Дополнительные вычисления»;6) на этой закладке заполняем такие параметры: в качестве дополнительных вычислений ставим операцию «
Отличия», в списке «поле:» выбираем «Кв.», в списке «элемент:» указываем «3 кв.»;7) нажимаем «
ОК». Отчет примет форму, как на рис. 10. Теперь все изменения посчитаны относительно показателей III квартала 2009 года. Например, по контрагенту «ООО "Эталон" » объем реализации во II квартале 2009 года был на 5183,50 грн. меньше, чем в III квартале (6924,85 - 12107,9 = -5183,05 грн.). 
Выполним преобразование отчета на рис. 10, выбрав в области вычислений поле «Мес.» (рис. 2). На месте значений прироста/убыли появится текст «#Н/Д».
Важно! При настройке дополнительных вычислений в списке «поле:» окна «Параметры поля значений» (рис. 6) выбирайте только те элементы, которые расположены в области строк или колонок сводного отчета.
Определение динамики или темпов роста
Любопытную возможность при работе со сводными таблицами открывают операции «
(назад)» и «(далее)» (они расположены на закладке «Дополнительные вычисления»; рис. 6). С их помощью можно определить темпы роста значений. Например, если при создании отчета, как на рис. 8, в качестве базы в списке «элемент:» выбрать вариант «(назад)», мы получим таблицу, как на рис. 11. Теперь в строке «Прирост, грн.» Excel покажет темп изменения объемов выручки в текущем месяце по отношению к предыдущему месяцу. Например, в ячейке «D4» значение «1261,70» фактически получено по формуле «=D3-C3», значение «5183,05» в ячейке «E4» посчитано как «=E3-D3» и т. д. Иными словами, для каждого контрагента мы получили динамику изменения объемов продаж по кварталам в стоимостном выражении.
Доля и процентные изменения
Темпы роста в сочетании с показателями изменения значений относительно выбранной базы хорошо демонстрируют динамику и тенденцию изменения показателей. Однако бухгалтеру порой удобнее анализировать эти цифры в процентном выражении. Такой расчет можно сделать прямо в сводной таблице, обратившись к закладке дополнительных вычислений. Делаем так:
1) открываем отчет, как на рис. 11;
2) становимся внутрь сводной таблицы, откроется окно «
Список полей сводной таблицы» (рис. 2). Сейчас наш отчет содержит два поля «Сумма» в области значений. Добавим это же поле в область значений еще два раза. Для этого продолжаем делать так:3) находим элемент «
Сумма» в списке «Выберите поле для добавления в отчет:» и дважды перетаскиваем его в область «Значения» (рис. 2). Теперь в области значений у нас четыре элемента и все они являются одним и тем же полем «Сумма»;4) щелкаем левой кнопкой мыши на третьем поле «
Сумма». Появится выпадающее меню, из которого выбираем пункт «Параметры полей значений…» (рис. 2);5) в открывшемся окне на закладке «
Операция» выбираем «Сумма»;6) в поле «
Пользовательское имя:» пишем «Доля, %»;7) щелкаем на закладке «
Дополнительные вычисления» — окно примет форму, как на рис. 6;8) щелкаем на поле «
Дополнительные вычисления». Откроется список, как на рис. 7;9) выбираем вариант «
Доля»;10) в левой части окна (рис. 6) в списке «
поле:» выбираем элемент «Кв.»;11) в списке «
элемент:» указываем значение «1 кв.»;12) нажимаем «
ОК»;13) щелкаем левой кнопкой мыши на четвертом поле «
Сумма» в области значений (это последнее добавленное нами поле);14) из открывшегося меню выбираем пункт «
Параметры полей значений…» (рис. 2);15) на закладке «
Операция» выбираем вариант «Сумма»;16) в поле «
Пользовательское имя:» пишем «Изменение, %»;17) переходим на закладку «
Дополнительные вычисления». Откроется одноименный список, как на рис. 7;18) из предложенных вариантов выбираем «
Приведенное отличие»;19) в списке «
поле:» ставим «Кв.», в списке «элемент:» указываем значение «1 кв.»;20) нажимаем «
ОК». Отчет примет форму, как на рис. 12. В этом отчете появились две дополнительные строки. Строка «Доля, %» показывает процентное изменение суммы выручки относительно I (базового) квартала. Так, в ячейке «D5» величина «122,28 %» получена как «=D3/C3*100». В строке «Изменение, %» видим прирост выручки относительно I квартала (формула для «D6» выглядит так: «=(1-D3/C3)*100», т. е. (1-6924,85 : 5663,15) х 100 = 22,28 %). 
Думаю, перечисленными вариантами дополнительных вычислений в сводных таблицах Excel 2007 можно ограничиться, для практической работы их более чем достаточно. Что касается других операций над данными сводного отчета, то они приведены в табл. 1.
Таблица 1
Назначение операций закладки «
Дополнительные вычисления»| Операция | Действие |
| 1 | 2 |
| Нет | Отключает дополнительные вычисления в сводной таблице |
| Отличие | Показывает отклонение (разность) текущего значения относительно базового значения. Это значение определяют параметры «поле:» и «элемент:» сводного отчета |
| Доля | Показывает долю в процентах относительно базового значения |
| Приведенное отличие | Показывает отклонение в процентах относительно базового значения |
| С нарастающим итогом в поле | Определяет значения в виде нарастающего итога для базовых элементов, которые определены параметрами «поле:» и «элемент:» сводного отчета |
| Доля от суммы по строке | Определяет долю в процентах для текущего значения относительно общего итога в каждой строке или категории сводного отчета |
| Доля от суммы по столбцу | Отображает долю в процентах для текущего значения относительно общего итога в каждой колонке сводного отчета |
| Доля от общей суммы | Определяет долю (в процентах) для текущей ячейки относительно общей суммы в сводном отчете |
| Указатель | Производит вычисления следующим образом: ((Значение в текущей ячейке) xx (Общий итог)) / ((Итог строки) x (Итог столбца)) |
Дополнительные вычисления и группировки в сводных таблицах
Последний момент, который я хотел бы обсудить в контексте выполнения расчетов в сводных таблицах, — это взаимодействие вычислительных операций с группировкой данных. Теперь наша задача будет такой: найти суммарные объемы продаж в количественном (в штуках) и стоимостном (в гривнях) выражении по каждому контрагенту поквартально. Но в качестве кварталов мы не станем использовать рабочее поле, а укажем даты и сгруппируем их средствами сводных таблиц. Делаем так:
1) нажимаем «
Shift+F11» — добавляем новый рабочий лист;2) двойным щелчком на ярлычке листа открываем его название. Вводим текст. Я назвал лист с будущим сводным отчетом «
СВ_»;3) ставим указатель активной ячейки на «
A1» листа «СВ_»;4) вызываем раздел «
Вставка» основного меню;5) на ленте меню «
Вставка» находим группу «Таблицы», в ней щелкаем на иконке «Сводная таблица»;6) из открывшегося меню выбираем пункт «
Сводная таблица». Появится окно «Создание сводной таблицы»;7) в этом окне щелкаем в поле ввода для параметра «
Таблица или диапазон:»;8) переходим на лист «
Продажи», обводим диапазон колонок «A1:I2445» (в моем примере база данных была именно такого размера);9) в окне «
Создание сводной таблицы» нажимаем кнопку «ОК». На рабочем листе появится макет сводного отчета и откроется окно настроек с названием «Список полей сводной таблицы»;10) перетаскиваем поле «
Дата» в область «Названия строк», поле «Покупатель» — в область «Названия столбцов». В область «Значения» дважды переносим поле «Сумма»;11) в окне «
Список полей сводной таблицы» в области «Значения» щелкаем на первом элементе с полем «Сумма». Появится выпадающее меню (рис. 2);12) из этого меню выбираем «
Параметры полей значений…». Появится окно, как на рис. 3;13) в поле «
Пользовательское имя:» вводим текст «Сумма, грн.». Это будет заголовок первой колонки сводного отчета;14) в списке «
Операция» окна «Параметры поля значений» выбираем вариант «Сумма»;15) в окне «
Параметры поля значений» нажимаем «ОК»;16) щелкаем правой кнопкой мыши на любой ячейке, где записана дата в сводном отчете;
17) из контекстного меню выбираем пункт «
Группировать…». Появится окно «Группирование»;18) в этом окне в списке «
с шагом:» выбираем вариант «Кварталы»;19) в окне «
Группировать…» нажимаем «ОК»;20) выбираем второе поле «
Сумма» в области «Значения»;21) для этого поля задаем такие параметры: в область «
Параметры полей значений…» вводим текст «Прирост, грн.», в качестве операции выбираем «Сумма»;22) в окне «
Параметры поля значений» щелкаем на закладке «Дополнительные вычисления». Появится окно, как на рис. 13; 
23) в списке «Дополнительные вычисления» выбираем «Отличие»;
24) параметр «поле:» ставим в положение «Дата»;
25) в списке «элемент:» выбираем значение «Кв-л1». И здесь нужно быть очень внимательным!
Важно! Обратите внимание на перечень значений в списке «элемент» поля «Дата» (рис. 13). Несмотря на то, что в базе записана информация только по первым трем кварталам, в этом списке есть, например, элемент «Кв-л4». Это следствие работы механизма группировки значений поля «Дата»;
26) в окне «Параметры поля значений» нажимаем «ОК» и заканчиваем создание сводного отчета. Его окончательный вид показан на рис. 14.
Рабочий ключ или группировка?
В принципе все данные в отчете на рис. 14 посчитаны правильно. По строкам расположены названия кварталов, по колонкам — контрагенты. В самом теле отчета показаны объемы продаж, а также изменение суммы по каждому контрагенту в пределах кварталов. И все же этот пример я предложил вам не случайно. Несмотря на то что группировка по дате сработала правильно, при ее использовании есть два неудобства.
Во-первых, чтобы получить доступ к инструментам группировки, нам пришлось указать точный диапазон с данными на рабочем листе. А это в известной мере ограничивает возможность пополнения базы новыми записями.
Во-вторых, в перечень «
элемент:» окна «Параметры поля значений» могут попасть такие, которых нет в исходных данных для создания сводного отчета. Выбор такого элемента может стать дополнительным источником ошибок.Именно поэтому я советую всегда пользоваться рабочим ключом и стараться создавать его самому с помощью формул. В этом случае вы всегда будете держать процесс обработки в сводном отчете под контролем и выполнять группировку полей именно так, как это нужно для вашей задачи.
Важно! При выполнении основных и дополнительных вычислений к сводной таблице можно применить любые режимы группировки данных. Но правильным решением, на мой взгляд, будет использование рабочего ключа.
Последнее действие над нашим сводным отчетом будет таким:
1) открываем сводную таблицу, как на рис. 14;
2) становимся на любое значение в колонке «Дата», например на ячейку «A3»;
3) щелкаем правой кнопкой мыши, из контекстного меню выбираем «
Группировать…»;4) в окне «
Группирование» для поля «Дата» выбираем два значения — «Месяцы» и «Кварталы» (рис. 15 на с. 39). Фрагмент обновленного сводного отчета показан на рис. 16. Как видим, в строке «Прирост, грн.» значения посчитаны неправильно.
Важно! При каждом изменении группировки или макета таблицы проверьте настройку дополнительных вычислений. Скорее всего, параметры этих вычислений придется ввести заново.
Отсюда вывод: вначале хорошо продумайте структуру всего отчета, обратив особое внимание на способ группировки ключевых полей, и только после этого определяйте параметры дополнительных вычислений в области данных.
И еще одно небольшое наблюдение. Откройте отчет, как на рис. 16, и войдите в параметры дополнительных вычислений для поля «Дата». В окне «Параметры поля значений» перейдите на закладку «Дополнительные вычисления», укажите способ расчета «Отличия» и посмотрите на состав списка «элементы:». В нем будут перечислены все названия месяцев (подобно тому, как это было при организации вычислений по кварталам). То есть при наличии нескольких группировок ключевого поля (у нас в данном случае работает группировка по месяцам и кварталам) дополнительные вычисления можно определить только для самого нижнего (подробного) уровня. А вот при работе с рабочим ключом такой проблемы нет! Мы не зря создали колонку «Мес.» в исходной базе данных, и вы сейчас в этом сможете убедиться. Переформируйте сводный отчет, поставив в область строк вначале «Кв.», а затем — поле «Мес.». При этом в области «Названия строк» поле «Мес.» должно стоять ниже поля «Кв.». По форме сводная таблица останется такой же, как на рис. 16, зато в ней вы сможете произвольным образом вычислить отклонения в объемах реализации как по месяцам, так и по кварталам.
На этом мы завершим тему организации вычислений в сводных отчетах и в следующий раз приступим к настройке параметров сводных таблиц и работе с несколькими диапазонами консолидации.
Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
