MyTetra Share
Делитесь знаниями!
Преобразование сводной таблицы в формулы SUMIFS — бесплатный макрос VBA Автор Дмитрий Якушев На чтение 14 мин
Ссылка на оригинал
Время создания: 12.10.2019 20:12
Раздел: Разные закладки - VBA - Excel - Сводные
Запись: xintrea/mytetra_db_adgaver_new/master/base/1570876789dgbqxjcsf7/text.html на raw.githubusercontent.com

Отображать только используемые поля в сводной таблице

Автор Дмитрий Якушев На чтение5 мин. Просмотров74

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

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

Содержание

  1. В листе «Показать подробности» обычно отображаются все поля
  2. Макрос — Показать детали используемых полей
  3. Как работает макрос?
  4. Скачать файл Excel
  5. Как запустить макрос

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

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

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

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

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

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

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

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

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

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

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

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

Скачать файл Excel

Загрузите файл, содержащий макрос.

Show Details On Active Pivot Table Columns.xlsm (117.0 KB)

Вот макрос, который вы можете скопировать и вставить в свою личную книгу макросов или в любой проект 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

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

Загрузите файл, содержащий макрос.

Show Details On Active Pivot Table Columns.xlsm (117.0 KB)
Так же в этом разделе:
 
MyTetra Share v.0.65
Яндекс индекс цитирования