Независимая группировка сводных таблиц
Проблема
Если вы строите несколько сводных таблиц на основе одного источника, то, возможно, уже сталкивались с одним мелким, но неприятным моментом: Excel не позволяет независимо группировать данные в таких сводных.
Допустим, мы построили две отдельных сводных таблицы для анализа выручки и объемов продаж:
Но выручку мы хотим видеть по месяцам, а объем продаж по кварталам, поэтому дальше логично применить группировку. Щелкаем правой кнопкой мыши по датам в первой сводной, выбираем Группировать - Месяцы - ОК и выручка действительно начинает отображаться по месяцам, как мы и хотели. Но и во второй таблице даты тоже сгруппировались по месяцам, хотя мы этого совершенно не просили:
И наоборот, если попытаться аналогичным способом сгруппировать даты во второй сводной по кварталам, то перегруппируется и первая сводная, теряя текущую группировку по месяцам.
В чем дело?
Дело в том, что при построении любой сводной таблицы Microsoft Excel создает в файле так называемый кэш (Pivot Cache) - область памяти, куда загружаются исходные данные для анализа и вычислений. И когда вы делаете вторую, третью и т.д. сводные по той же исходной таблице Excel услужливо использует уже сформированный ранее для первой сводной кэш, а не создает новый.
С одной стороны, это правильно: данные не дублируются, файл меньше весит и быстрее считается. С другой, мы имеем ту самую проблему с группировкой, изменение которой в одной из сводных влияет на все остальные. Что же делать?
Способ 1. Строим сводную правильно
Чтобы построить сводную таблицу на новом кэше придется воспользоваться старым добрым Мастером сводных таблиц и диаграмм (Pivot Table and Chart Wizard) из версий Excel 97-2003. Как ни странно, но этот "устаревший" инструмент умеет при создании сводной выбирать - делать ли независимую сводную или сводную на основе уже имеющегося кэша. Кнопку для этого Мастера убрали из интерфейса еще в 2007 году, но он до сих пор доступен для совместимости и вполне работоспособен.
Щелкните правой кнпокой мыши по панели быстрого доступа в левом верхнем углу и выберите команду Настройка панели быстрого доступа (Customize Quick Access Toolbar). Затем в выпадающем списке в верхней части окна выберите Все команды (All Commands), найдите Мастер сводных таблиц и диаграмм и добавьте на панель:
Теперь можно строить сводную. Ставим активную ячейку в таблицу с данными или выделяем ее, жмем на добавленную кнопку и проходим последовательно шаги Мастера, выбрав на первом создание именно независимого отчета на собственном кэше:
Excel будет настойчиво предлагать сделать сводную на том же кэше - отказываемся и доходим до финала:
Все, теперь группировка этой сводной не будет влиять на соседнюю и обе таблицы можно сгруппировать по-разному:
Способ 2. Отвязываем уже созданную сводную от общего кэша
Если у вас уже есть готовая сводная, и создавать ее заново с помощью Мастера вы не хотите, то можно отвязать ее от общего кэша, используя тактическую хитрость:
- Скопируйте сводную в новый пустой файл
- Обновите сводную (правой кнопкой мыши по ней - Обновить)
- Скопируйте ее обратно на старое место
Такой бессмысленный, на первый взгляд, "финт ушами" заставляет Excel создать для сводной отдельную копию кэша, т.е. делает ее независимой.
Если подобную операцию придется проделывать часто, то имеет смысл автоматизировать это с помощью небольшого макроса. Нажмите кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt+F11, чтобы открыть редактор Visual Basic, затем вставьте новый пустой модуль через меню Insert - Module и скопируйте туда этот код:
Sub Unlink_Pivot_From_Shared_Cache()
Dim pvtTable As PivotTable
Dim startcell As Range
Dim wbCur As Workbook, wbTemp As Workbook
On Error Resume Next
Set pvtTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
On Error GoTo 0
If Not pvtTable Is Nothing Then
Set wbCur = ActiveWorkbook
'копируем сводную и вставляем в новую книгу
Set startcell = pvtTable.TableRange2.Cells(1)
pvtTable.TableRange2.Copy
Set wbTemp = Workbooks.Add
ActiveSheet.Paste
'обновляем сводную, копируем назад и закрываем временный файл
ActiveSheet.PivotTables(1).PivotCache.Refresh
ActiveSheet.PivotTables(1).TableRange2.Copy Destination:=startcell
wbTemp.Close SaveChanges:=False
Else
MsgBox "Сначала поставьте активную ячейку в сводную таблицу!", vbExclamation, "Подсказка"
End If
End Sub
Если поставить активную ячейку в сводную и запустить этот макрос с помощью кнопки Макросы на вкладке Разработчик или сочетания клавиш Alt+F8, то текущая сводная таблица будет отвязана от общего кэша, и вы сможете группировать ее независимо.
Нюансы
- Если у сводных таблиц не общий кэш, то их не получится одновременно фильтровать срезами и шкалой.
- Посмотреть количество кэшей в книге можно, если в редакторе Visual Basic (Alt+F11) открыть консоль через меню View - Immediate и ввести туда команду ? Activeworkbook.PivotCaches.Count и нажать на Enter:
Ссылки по теме
- Тонкая настройка вычислений в сводных таблицах
- Фильтр исходных данных при двойном щелчке по ячейке в сводной таблице
- Группировка данных в сводных таблицах
Кстати, если у сводной таблицы источник данных OLAP–куб, то такой проблемы не возникает.
Кому интересно как работают сводные таблицы на OLAP-кубах? то я как-то на эту тему писал статью в своем блоге:
Но самый «большой неприятный момент» для меня при работе в Excel со сводными таблицами – это ошибка: «Перекрытие отчетов сводных таблиц не допускается» когда одна сводная таблица на листе «залазит» на другую…
Вот бы Microsoft убрал этот глюк, как бы удобно стало строить Dashboard ы в Excel на сводных таблицах…
Можете прояснить такой ещё момент: если я создаю копию листа со сводной и уже там настраиваю её по-другому, кэш ведь остаётся общий для обеих таблиц, верно?