Независимая группировка сводных таблиц

Проблема

Если вы строите несколько сводных таблиц на основе одного источника, то, возможно, уже сталкивались с одним мелким, но неприятным моментом: Excel не позволяет независимо группировать данные в таких сводных. 

Допустим, мы построили две отдельных сводных таблицы для анализа выручки и объемов продаж:

Исходные сводные

Но выручку мы хотим видеть по месяцам, а объем продаж по кварталам, поэтому дальше логично применить группировку. Щелкаем правой кнопкой мыши по датам в первой сводной, выбираем Группировать - Месяцы - ОК и выручка действительно начинает отображаться по месяцам, как мы и хотели. Но и во второй таблице даты тоже сгруппировались по месяцам, хотя мы этого совершенно не просили:

Группировка первой сводной влияет на вторую

И наоборот, если попытаться аналогичным способом сгруппировать даты во второй сводной по кварталам, то перегруппируется и первая сводная, теряя текущую группировку по месяцам.

В чем дело?

Дело в том, что при построении любой сводной таблицы Microsoft Excel создает в файле так называемый кэш (Pivot Cache) - область памяти, куда загружаются исходные данные для анализа и вычислений. И когда вы делаете вторую, третью и т.д. сводные по той же исходной таблице Excel услужливо использует уже сформированный ранее для первой сводной кэш, а не создает новый.

С одной стороны, это правильно: данные не дублируются, файл меньше весит и быстрее считается. С другой, мы имеем ту самую проблему с группировкой, изменение которой в одной из сводных влияет на все остальные. Что же делать?

Способ 1. Строим сводную правильно

Чтобы построить сводную таблицу на новом кэше придется воспользоваться старым добрым Мастером сводных таблиц и диаграмм (Pivot Table and Chart Wizard) из версий Excel 97-2003. Как ни странно, но этот "устаревший" инструмент умеет при создании сводной выбирать - делать ли независимую сводную или сводную на основе уже имеющегося кэша. Кнопку для этого Мастера убрали из интерфейса еще в 2007 году, но он до сих пор доступен для совместимости и вполне работоспособен.

Щелкните правой кнпокой мыши по панели быстрого доступа в левом верхнем углу и выберите команду Настройка панели быстрого доступа (Customize Quick Access Toolbar). Затем в выпадающем списке в верхней части окна выберите Все команды (All Commands), найдите Мастер сводных таблиц и диаграмм и добавьте на панель:

Добавление кнопки мастера сводных таблиц

Теперь можно строить сводную. Ставим активную ячейку в таблицу с данными или выделяем ее, жмем на добавленную кнопку и проходим последовательно шаги Мастера, выбрав на первом создание именно независимого отчета на собственном кэше:

Первый шаг мастера сводных таблиц

Excel будет настойчиво предлагать сделать сводную на том же кэше - отказываемся и доходим до финала:

Предупреждение

Все, теперь группировка этой сводной не будет влиять на соседнюю и обе таблицы можно сгруппировать по-разному:

Независимая группировка сводных таблиц

Способ 2. Отвязываем уже созданную сводную от общего кэша

Если у вас уже есть готовая сводная, и создавать ее заново с помощью Мастера вы не хотите, то можно отвязать ее от общего кэша, используя тактическую хитрость:

  1. Скопируйте сводную в новый пустой файл
  2. Обновите сводную (правой кнопкой мыши по ней - Обновить)
  3. Скопируйте ее обратно на старое место

Такой бессмысленный, на первый взгляд, "финт ушами" заставляет 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:

    Количество кэшей

Ссылки по теме



27.02.2017 08:02:08
Спасибо! :D
27.02.2017 17:39:01
8)СУПЕР:D
28.02.2017 12:57:26
Николай, согласен с Вами!  что это «мелкий неприятный момент» при работе в Excel со сводными таблицами. Логично бы функцию управления кеша сводной вынести в «параметры сводной таблицы»!?

Кстати, если у сводной таблицы источник данных OLAP–куб, то такой проблемы не возникает.
Кому интересно как работают сводные таблицы на OLAP-кубах? то я как-то на эту тему писал статью в своем блоге: http://biweb.ru/excel-and-olap.html

Но самый «большой неприятный момент» для меня при работе в Excel со сводными таблицами – это ошибка: «Перекрытие отчетов сводных таблиц не допускается» когда одна сводная таблица на листе «залазит» на другую…

Вот бы Microsoft убрал этот глюк, как бы удобно стало строить Dashboard ы в Excel на сводных таблицах…
06.03.2017 05:16:02
Напишите разработчикам. Могут прислушаться.
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application
28.02.2017 17:18:17
Николай, спасибо!
Можете прояснить такой ещё момент: если я создаю копию листа со сводной и уже там настраиваю её по-другому, кэш ведь остаётся общий для обеих таблиц, верно?
01.03.2017 08:07:03
Можно ещё в таком случае (уже имеются несколько сводных на одном кэше) для открепляемой сводной в качестве источника (а источники у меня - ИД) указать ИД с иным именем, но с идентичным определением. Единственно, перед этим, надо в открепляемой сводной отключить все срезы, если были настроены.
Большое человеческое спасибо :D.
Добрый день! Подскажите, а как добиться обратного? Я хочу связать 10 сводных таблиц, чтобы при изменении данных в одной - они менялись во всех остальных (данные - это фамилии сотрудников. Нужно продублировать отчет на 10 разных отделов, и не хотелось бы вручную менять фамилии на каждой сводной)
20.01.2021 11:53:56
Огромное спасибо!
Наверх