Excel: вычисления в сводных таблицах
Приветствую всех сотрудников «Б & K»! Журнал ваш читаю регулярно. А теперь решился еще и написать. Подскажите, какие операции, кроме суммирования, можно выполнить в сводных таблицах? И самое главное, как их применить? Думаю, материалы по такому важному инструменту Excel, как сводные таблицы, будут интересны многим. Заранее благодарю.
Петренко В., г. Днепропетровск
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Несомненно, в арсенале
Excel сводные таблицы для бухгалтера —инструмент № 1. Они дают в его распоряжение неоценимую возможность: мгновенно получать отчеты любой формы, с произвольной степенью детализации. К сожалению, на практике потенциал сводных таблиц зачастую используют не в полной мере. В основном это касается способа организации вычислений в сводном отчете. Например, при расчете итоговых показателей выбор ограничивают только операцией суммирования, отбросив в сторону все остальные варианты... А ведь сводные таблицы предлагают в этом смысле гораздо более широкие возможности! Разобраться с этими возможностями я предлагаю на примере базы данных, изображенной на рис. 1.
В базе есть сведения относительно объемов реализации ТМЦ по трем контрагентам: «
ЧП ″Град″», «ЧП ″Креол″» и «ЧП ″Фин″». Данные собраны по месяцам. Объемы реализации за месяц по каждому виду ТМЦ показаны в тыс. грн. Начнем с «классической» сводной таблицы. Посмотрим, какие объемы выручки были у каждого контрагента за месяц по всем товарным позициям. Делаем так:1. Вызываем «
Данные → Сводная таблица».2. На запрос об источнике данных, выбираем вариант «
в списке или базе данных Microsoft Office Excel».3. В ответ на предложение указать диапазон с данными, выделяем на рабочем листе «
База» колонки с «А» по «D» включительно (рис. 1). Нажимаем кнопку «Далее».4. Excel покажет окно с предложением определить параметры сводной таблицы или построить макет. Нажимаем кнопку «
Макет».5. В окне формирования макета расставляем поля, как показано на рис. 2. То есть поле «
Месяц» переносим в область строк, поле «Фирма» — в область колонок. Поле «Сумма» переносим в область данных. Теперь для этого поля нам осталось выбрать тип операции.
6. Делаем двойной щелчок мышью на поле «
Сумма по полю Сумма». Появится окно «Вычисление поля сводной таблицы», как на рис. 3. В левой части этого окна есть список доступных операций над вычисляемым полем. У нас таким полем является «Сумма». А текущей операцией для этого поля является суммирование. В данном случае нас это устраивает: чтобы узнать выручку по контрагентам и по месяцам, нужно просуммировать соответствующие значения из базы данных. В поле «Имя:» видим малосимпатичное название: «Сумма по полю Сумма». Таким его создал Excel по умолчанию. Смотрится плохо, в отчете выглядит еще хуже. Но сейчас название не меняем. Мы это сделаем в следующем примере. А пока движемся дальше.
7. В окне «
Вычисление поля сводной таблицы» жмем «ОК».8. В окне «
Мастер сводных таблиц и диаграмм» (рис. 2) нажимаем «ОК», затем кнопку «Готово». Результат работы показан на рис. 4.
Все верно. Сумма выручки по контрагенту «
ЧП ″Креол″» за январь месяц составила 150 тыс. грн. Это легко проверить, отфильтровав и просуммировав данные в исходной базе.Важно!
По умолчанию для чисел Excel выбирает операцию суммирования. Для нечисловых данных он применит функцию подсчета количества значений. Это произойдет даже в том случае, если в исходных данных попадется хоть одна пустая ячейка или число в текстовом формате.
Факты продаж — вещь упрямая...
Изменим задачу: я хочу узнать не объем выручки, а количество сделок с каждым контрагентом. То есть меня интересует не объем товарооборота, а свершившиеся факты продаж. Причем видеть эту информацию мне нужно по месяцам. Для этого перестроим нашу таблицу так:
1. Ставим активную ячейку на любое число в области данных сводного отчета. Например, на сумму «
150» (ячейка «B4»).2. Щелкаем правой кнопкой мыши. Появится контекстное меню для управления сводной таблицей (рис. 5).
3. Из этого меню выбираем «
Параметры поля...» и сразу попадаем в окно «Вычисление поля сводной таблицы» (рис. 3).4. Из списка доступных операций выбираем «
Количество» и нажимаем «ОК». Фрагмент нового отчета показан на рис. 6.
Теперь видно, что с контрагентом «
ЧП ″Креол″» в январе было заключено 7 сделок, с «ЧП ″Фин″» — 5 сделок и т. д. Таким образом, мы буквально одним движением мыши получили важнейший показатель деловой активности для каждого их трех контрагентов.Также просто мы могли бы показать в сводном отчете среднее, минимум, максимум и т. д. Всего Excel предлагает 11 вариантов. Для этого достаточно всего лишь изменить тип операции и обновить сводную таблицу. Но это еще не все.
Доля, динамика и приведенные отличия
Абсолютные значения показателей не всегда отражают реальную картину. Во многих случаях лучше анализировать процентные изменения, тенденции, приросты показателей и т. п. Такие расчеты широко представлены в сводных таблицах Excel. Получить к ним доступ можно через кнопку «
Дополнительно» в окне «Вычисление поля сводной таблицы». Посмотрим, как этим воспользоваться. За основу возьмем сводный отчет, изображенный на рис. 6. Вначале изменим его структуру. Мы добавим в отчет поле «Сумма» три раза. Затем для каждого экземпляра этого поля выберем разные типы операций и посмотрим, что у нас получится.Ставим активную ячейку в область сводной таблицы. Сейчас мы могли бы изменить состав полей в отчете через «
Мастер сводных таблиц». Например, так: щелкнуть правой кнопкой мыши внутри сводного отчета, выбрать из контекстного меню пункт «Мастер сводных таблиц», нажать кнопку «Макет», изменить поля в отчете. Но мы поступим иначе. Делаем так:1. Ставим активную ячейку внутрь сводного отчета. Появляется специальная панель работы со сводной таблицей (рис. 7). На этой панели видны все доступные поля.
2. Удерживая нажатой левую кнопку мыши, трижды переносим поле «
Сумма» в область данных сводной таблицы, как показано на рис. 7. Заметьте, что переносить поля нужно из панели сводных таблиц на рабочий лист. В результате мы должны получить отчет, как на рис. 7. В нем строка «Количество по полю Сумма» фигурирует в области данных четыре раза: одно поле «Сумма» находилось в сводном отчете до корректировки, еще три раза мы туда занесли. Теперь исправим операции для каждого из трех полей «Сумма».3. Ставим указатель активной ячейки на «
C4». Щелкаем правой кнопкой мыши. Появится контекстное меню управления сводной таблицей, как на рис. 5.4. Из контекстного меню выбираем «
Параметры поля...». Откроется окно «Вычисление поля сводной таблицы», как на рис. 3.5. В списке «
Операция:» выбираем «Сумма».6. В окошко «
Имя:» печатаем «Выручка, тыс. грн.».7. Нажимаем «
ОК».8. Ставим указатель активной ячейки на «
C5». Щелкаем правой кнопкой мыши.9. Из контекстного меню (рис. 5) выбираем «
Параметры поля...».10.
Щелкаем на кнопке «Дополнительно». В окне появятся параметры «Дополнительные вычисления:» (рис. 8).
11. В списке «
Дополнительные вычисления:» выбираем вариант «Доля от суммы по столбцу».12. В поле «
Имя:» печатаем текст «Доля, %». Так название показателя будет выглядеть компактнее.13. В списке «
Операция:» выбираем вариант «Сумма». Это означает, что мы посчитаем долю от суммы выручки (в гривнях).14. Нажимаем «
ОК». В сводном отчете появится удельный вес (в процентах) объема реализации за месяц в общем объеме реализации по каждому контрагенту (ячейки «C5», «C9», «C13» и т. д.).15. Становимся на ячейку «
C6». Щелкаем правой кнопкой мыши, из контекстного меню выбираем «Параметры поля...».16. В окне «
Вычисление поля сводной таблицы» (рис. 3) щелкаем на кнопке «Дополнительно».17.
В списке «Дополнительные вычисления:» указываем вариант «Отличие».18. В списке «
поле:» выбираем «Месяц», в списке «элемент» — вариант «(назад)». Это означает, что мы будем считать прирост (убыль) по полю «Сумма» в текущем месяце по сравнению с предыдущим периодом. Но мы еще не указали, как именно мы определим это изменение.19. В списке «
Операция:» выбираем вариант «Сумма». Вот теперь все понятно. В сводном отчете для каждого контрагента мы увидим изменение суммарного объема реализации в текущем месяце относительно предыдущего месяца. Причем информация эта будет представлена в гривнях. Выбрав в качестве «Операция:» вариант «Количество», мы могли бы получить сравнительную характеристику по количеству продаж.20. В поле «
Имя:» печатаем текст «Прирост, тыс. грн.».21. Нажимаем «
ОК». В сводном отчете появились сведения об изменении объемов реализации (в тыс. грн.) по контрагентам за каждый месяц. Иными словами, для каждого контрагента мы получили динамику продаж по месяцам в стоимостном выражении. Сравнение с предыдущим периодом происходит потому, что мы указали параметр «(назад)». Продолжим.22. Становимся на ячейку «
C7». Щелкаем правой кнопкой мыши.23. Из контекстного меню выбираем «
Параметры поля...».24. В окне «
Вычисление поля сводной таблицы» (рис. 3) щелкаем на кнопке «Дополнительно».25. В списке «
Дополнительные вычисления:» (рис. 8) указываем вариант «Приведенное отличие». В списке «поле:» выбираем «Месяц», в списке «элемент» — «(назад)».26. В поле «
Имя:» печатаем текст «Прирост, %».27. В списке «
Операция:» выбираем вариант «Сумма».28. Нажимаем «
ОК». В сводном отчете появятся данные о процентах роста (падения) продаж текущего месяца по сравнению с предыдущим. Окончательная форма отчета показана на рис. 9.
И последний штрих. Чтобы таблицу было легче читать, изменим формат отдельных ячеек сводного отчета. Например, процент падения выручки по контрагентам покажем красным цветом. Здесь есть два момента на заметку:
1) в процентном формате нельзя выделить цветом отрицательные значения;
2) применить условное форматирование к ячейкам всей таблицы мы не можем, так как хотим выделить красным только отрицательные значения в строке «
Прирост, %».Это значит, что способ форматирования нужно указать в самой сводной таблице. Делаем так:
1. Щелкаем правой кнопкой на ячейке «
C7». Из контекстного меню выбираем «Параметры поля...».2. В окне «
Вычисление поля сводной таблицы» (рис. 3) щелкаем на кнопке «Формат...». Откроется окно, как на рис. 10. Вначале в этом окне будет подсвечен вариант «Процентный» — это текущий формат ячеек в строке «Прирост, %». Сейчас мы этот формат изменим. Условное форматирование ячеек нам недоступно. Придется откорректировать маску формата.3. Из списка «
Числовые форматы:» выбираем «(все форматы)».4. В поле «
Тип:» печатаем такой текст: «0,00 %;[Красный]-0,00 %» (рис. 10). 
5. Нажимаем «
ОК». В окне «Вычисление поля сводной таблицы» нажимаем «ОК».Теперь отрицательные значения в строке «
Прирост, %» будут выделены красным. И еще одна тонкость. Названия в сводном отчете очень важны. Они должны быть понятны, лаконичны, отражать смысл показателя. Чтобы добиться этого, нужно заполнить соответствующее название в поле «Имя:» окна «Вычисление поля сводной таблицы» (рис. 8). И здесь может быть заминка. В качестве имени нельзя использовать название полей базы данных. Например, если в форме на рис. 2 вместо «Сумма по полю Сумма» ввести просто «Сумма», Excel выдаст ошибку. Как быть в такой ситуации?Совет
Чтобы использовать в заголовках сводного отчета имена полей базы данных, добавьте к их названию «пробел». Например, заголовок сводного отчета «Сумма» Excel не пропустит. А с вариантом «Сумма» проблем не будет.Пожалуй, на этом можно завершить тему вычислений в сводных таблицах. Для практической работы описанных вариантов более чем достаточно. А разобраться с остальными, думаю, при необходимости не составит труда.
Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua, nictomkar@rambler.ru/a> иили на форуме редакции www.bk.factor.ua/forum . Удачной работы!
