Note: The other languages of the website are Google-translated. Back to English

Как вывести список всех сводных таблиц из книги?

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

Список всех сводных таблиц из книги с кодом VBA

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу ...
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния: Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты: Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий ...
  • Более 300 мощных функций; Работает с Office 2007-2021 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

стрелка синий правый пузырь Список всех сводных таблиц из книги с кодом VBA

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

1. Откройте свою книгу, в которой вы хотите перечислить все сводные таблицы.

2. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

3. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: список всех сводных таблиц из книги

Sub ListPivotsInfor()
'Update 20141112
    Dim St As Worksheet
    Dim NewSt As Worksheet
    Dim pt As PivotTable
    Dim I, K As Long
    Application.ScreenUpdating = False
    Set NewSt = Worksheets.Add
    I = 1: K = 2
    With NewSt
        .Cells(I, 1) = "Name"
        .Cells(I, 2) = "Source"
        .Cells(I, 3) = "Refreshed by"
        .Cells(I, 4) = "Refreshed"
        .Cells(I, 5) = "Sheet"
        .Cells(I, 6) = "Location"
        For Each St In ActiveWorkbook.Worksheets
            For Each pt In St.PivotTables
                I = I + 1
                .Cells(I, 1).Value = pt.Name
                .Cells(I, 2).Value = pt.SourceData
                .Cells(I, 3).Value = pt.RefreshName
                .Cells(I, 4).Value = pt.RefreshDate
                .Cells(I, 5).Value = St.Name
                .Cells(I, 6).Value = pt.TableRange1.Address
            Next
        Next
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub

4, Затем нажмите F5 Ключ для запуска этого кода, все имена сводных таблиц, диапазон исходных данных, имя рабочего листа и другие атрибуты перечислены в новом рабочем листе, который помещается перед вашим активным листом, как показано на следующем снимке экрана:

список-документов-все-сводная-1


Статьи по теме:

Как проверить, существует ли в книге сводная таблица?

Как добавить несколько полей в сводную таблицу?


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (20)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Хорошая вещь. Можно включить этот фрагмент, чтобы сделать имя сводной таблицы гиперссылкой. .Cells(I, 1).Parent.Hyperlinks.Add Anchor:=.Cells(I, 1) _ , Address:="" _ , SubAddress:="'" + St.Name + "'!" + Split(pt.TableRange1.Address, ":")(0) _ , TextToDisplay:=pt.Name With .Cells(I, 7).Font .ColorIndex = xlAutomatic .Underline = xlUnderlineStyleNone End with With .Cells(I, 7). 1).Characters(Start:=4165632, Length:=Len(pt.Name)).Font.Underline = xlUnderlineStyleSingle.Color = -XNUMX End With
Этот комментарий был сведен к минимуму модератором на сайте
ЖАЛЬ, что не работает, возможно, из-за того, что я не могу нормально разбить на строку и, возможно, из-за любых заявлений, которые не объявляются. Не могли бы слелать код более читаемым (разбить на строки и переменные).
Этот комментарий был сведен к минимуму модератором на сайте
превосходно. благодарю вас.
Этот комментарий был сведен к минимуму модератором на сайте
Это была находка для меня! Жаль, что я не искал это решение три дня назад. Это сэкономило бы мне часы!! Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Мне нужно было исправить файл, в котором было более 60 сводных таблиц. Сначала щелкал каждый раз, чтобы исправить (как в случае, когда кто-то «отвязывает» их, но я не мог найти все неправильные. Он нашел последний в нескольких столбцах, которые были скрыты в оригинале). Хороший кусок кода!
Этот комментарий был сведен к минимуму модератором на сайте
Я попытался запустить это в Excel 2013, и я получаю ошибку времени выполнения «1004»: ошибка, определяемая приложением или объектом. Если закомментировать строку .Cells(I, 2).Value = pt.SourceData, она будет работать нормально, можете ли вы посоветовать мне, что мне может понадобиться сделать, чтобы часть SourceData заработала? Спасибо Росс
Этот комментарий был сведен к минимуму модератором на сайте
[quote]Я попытался запустить это в Excel 2013, и я получаю ошибку времени выполнения «1004»: ошибка, определяемая приложением или объектом. Если закомментировать строку .Cells(I, 2).Value = pt.SourceData, она будет работать нормально, можете ли вы посоветовать мне, что мне может понадобиться сделать, чтобы часть SourceData заработала? Спасибо РоссРосс[/quote] У меня такая же ошибка. Я думаю, что это бомба, когда источником для сводной таблицы является модель данных Excel, используемая PowerPivot.
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за это. Это отличный кусок кода, очень полезный. Как писали другие, я думаю, что это не работает для сводных таблиц, базовый источник данных которых основан на запросе данных только для подключения. У меня есть книга с одной таблицей данных. Впоследствии я создаю более точные запросы данных рабочей книги на основе данных этой одной таблицы. Запросы предназначены только для подключения (чтобы избежать ненужного увеличения размера книги). Затем я создал сводные таблицы, которые опираются на данные в запросах книги. Когда я запускаю код, он отлично работает со сводными таблицами из традиционных источников, но возникает ошибка времени выполнения, когда он обращается к сводным таблицам на основе данных в запросах рабочей книги. Конкретно; он дает ошибку времени выполнения «1004»: ошибка, определяемая приложением или объектом. Строка кода, в которой возникает ошибка: .cells(I, 2).value = pt.SourceData Спасибо за предоставленный код, и я надеюсь, что приведенное выше поможет вам его улучшить.
Этот комментарий был сведен к минимуму модератором на сайте
Очень могущественный. Огромное спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Для информации только о подключении информация о подключении к данным является свойством PivotCache.
У меня есть рабочая книга с более чем 40 сводными таблицами с сочетанием таблиц Excel и подключений к данным SQL-сервера. Я использую следующий код, чтобы отслеживать их


Подпрограмма GetPivotTableInfo()

Dim wb как рабочая книга
Dim pvt как сводная таблица
Dim wsheet как рабочий лист
Тусклый компьютер как PivotCache

Установите wb = ActiveWorkbook
Для каждого листа в wb.Worksheets
Для каждого pvt в wsheet.PivotTables
Debug.Print wsheet.Name & ": " & pvt.Name
Установите pc = wb.PivotCaches(pvt.CacheIndex)
Если pc.SourceType = xlDatabase Тогда
Debug.Print pc.SourceData
Еще
Если pc.QueryType = xlOLEDBQuery Тогда
Debug.Print pc.Connection
Debug.Print pc.SourceConnectionFile
Debug.Print pc.WorkbookConnection.Name
Debug.Print pc.CommandText
End If
End If


Следующий пвт
Следующий лист
Этот комментарий был сведен к минимуму модератором на сайте
Спас меня от большой головной боли!
Не удалось найти, какая сводная таблица вызывала «Ошибку обновления всех»
Boom Перечислено с местами, большое спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Удивительно!!! спасибо!!!!
Этот комментарий был сведен к минимуму модератором на сайте
Это круто! Я искал такой код, но с добавлением отображения всех активных полей в таблице. Мне нужно очистить исходные таблицы от ненужных полей (слишком тяжелые), а они питают книгу из более чем 300 пивотов. Я бы предпочел не просматривать одну сводную таблицу за раз, чтобы выяснить, какие поля я могу удалить... Если бы вы могли показать мне, как это сделать, это было бы невероятно... Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за сообщение, я немного подчистил код, определив I и Long и удалив ссылку на K, так как она не использовалась. Наконец, я добавил строку чуть ниже вашего .Activate, чтобы показать .Columns.AutoFit.
Этот комментарий был сведен к минимуму модератором на сайте
ОЧЕНЬ удобно иметь на всякий случай! Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Это потрясающе.
Этот комментарий был сведен к минимуму модератором на сайте
Огромная помощь, сэкономила мне кучу времени, отслеживая одну из множества сводных таблиц с одинаковыми именами в рабочей книге!
Этот комментарий был сведен к минимуму модератором на сайте
ошибка времени выполнения 1004
Этот комментарий был сведен к минимуму модератором на сайте
Подходит идеально!! Большое спасибо
Этот комментарий был сведен к минимуму модератором на сайте
к сожалению, я получаю несколько ошибок, это было бы абсолютно здорово для моих рабочих книг с несколькими вкладками и несколькими сводами.
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL