Как отображать используемые поля

Итог: узнаете, как создать развернутый лист «Показать детали» из сводной таблицы, которая содержит только поля (столбцы), используемые в сводной таблице.

Уровень мастерства: Средний

В листе «Показать подробности» обычно отображаются все поля

Если дважды щелкнуть ячейку в области значений сводной таблицы (или щелкнуть правой кнопкой мыши> Показать подробности), в рабочую книгу добавится новый лист. Новый лист содержит все строки исходных данных для ячейки, которую мы дважды щелкнули.

Это полезно, когда вы связываете числа и хотите увидеть все строки, которые составляют конкретное число.

Лист сведений также содержит ВСЕ столбцы из диапазона исходных данных.

Арис, член сообщества Excel Campus, задал отличный вопрос, можем ли мы создать информационный лист, включающий ТОЛЬКО поля, используемые в сводной таблице?

Это не возможно напрямую в Excel, но мы можем использовать макрос для решения этой проблемы. Давайте посмотрим, как мы можем использовать VBA, чтобы сохранить кучу времени! 🙂

Макрос — Показать детали используемых полей

Следующий макрос группирует и скрывает или удаляет столбцы на листе «Показать детали», которые НЕ используются в сводной таблице. Это означает, что у нас останется лист подробностей показа, который содержит только поля (столбцы), которые используются в сводной таблице. Это дает нам гораздо более тонкую таблицу, которую легче читать.

Преимущество группировки и скрытия столбцов заключается в том, что их можно легко сделать видимыми, нажав клавишу 2 в верхнем левом углу, чтобы развернуть группы.

Как работает макрос?

Вот что происходит при запуске макроса:

1. Макрос создает лист ShowDetails для активной ячейки в сводной таблице.
2. Затем он просматривает каждый столбец в таблице (объект списка) нового листа.
3. Он проверяет, используется ли столбец (поле) в какой-либо области в сводной таблице.
4. Если столбец НЕ используется, он группирует столбец (столбцы также могут быть скрыты или удалены).
5. Шаги 3 и 4 повторяются для каждого столбца.
6. Контур столбца свернут, поэтому остаются видимыми только использованные столбцы.
7. Ширина столбцов таблицы автоматически подбирается, что позволяет сохранить еще один шаг с отображением подробных листов.

Вот макрос, который вы можете скопировать и вставить в свою личную книгу макросов или в любой проект VBA.

Sub Show_Details_Used_Fields_Only()
'Создает лист данных для сводной таблицы
'на основе активной ячейки и удаляет или скрывает
'столбцы, которые не используются в сводной таблице.
'Макрос может быть добавлен в ваш личный макрос
'Книга и запуск на любой открытый файл.

Dim pt As PivotTable
Dim pf As PivotField
Dim pfData As PivotField
Dim lo As ListObject
Dim loCol As ListColumn
Dim bVisible As Boolean

    
  'Проверьте, что активная ячейка находится в сводной таблице
  On Error Resume Next
  Set pt = ActiveCell.PivotTable
  On Error GoTo 0
  
  If pt Is Nothing Then
    MsgBox "Please select a cell inside a pivot table"
    Exit Sub
  End If
  
  'Убедитесь, что активная ячейка находится в области значений сводной таблицы
  If Not Intersect(ActiveCell, pt.DataBodyRange) Is Nothing Then
  
    'Создайте лист деталей шоу
    ActiveCell.ShowDetail = True
    
    'Set ListObject (Table) на листе подробностей показа
    Set lo = ActiveSheet.ListObjects(1)
    
    'Удалить неиспользуемые столбцы из листа данных
    For Each loCol In lo.ListColumns
      
      bVisible = False
      
      'Проверьте, что поле не используется в фильтрах, строках или
      'Области столбцов
      For Each pf In pt.PivotFields
        If pf.Name <> "Values" Then
          If pf.SourceName = loCol.Name Then
            If pf.Orientation = xlHidden Then
              'Проверьте, что поле не используется в области значений
              'Поля данных в области значений имеют скрытую ориентацию
              For Each pfData In pt.DataFields
                If pfData.SourceName = loCol.Name Then
                  bVisible = True
                End If
              Next pfData
            Else
              'Поле используется в строках, столбцах или фильтрах
              bVisible = True
            End If
            
           'Сгруппируйте и сверните столбцы в листе данных
            If bVisible = False Then
              'Раскомментируйте любую из строк ниже, чтобы удалить или скрыть
              'столбцы вместо группировки
              loCol.Range.EntireColumn.Group
              'loCol.Delete
              'loCol.Range.EntireColumn.Hidden = True
            End If
          End If
        End If
      Next pf
      
    Next loCol
    
    'Свернуть группы
    ActiveSheet.Outline.ShowLevels ColumnLevels:=1
    
    'Колонки Autofit
    lo.Range.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
    
  Else
    MsgBox "Please select a cell in the values area of the pivot table."
    Exit Sub
    
  End If

End Sub

Как запустить макрос

Макрос можно добавить в вашу личную книгу макросов и запустить в любом файле Excel, который вы открыли. Вы можете добавить кнопку на ленту, которая будет запускать макрос в любое время. Ознакомьтесь с моей бесплатной серией видео в Персональной книге макросов, чтобы узнать, как начать работу и создавать кнопки на ленте.

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

Другой вариант — добавить код в событие приложения для события BeforeDoubleClick. Тогда макрос может автоматически запускаться при двойном щелчке ячейки в сводной таблице.

Есть много способов реализовать этот макрос, в зависимости от того, как часто вы хотите его использовать. Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями о том, как его улучшить. Спасибо! 🙂