Skip to content
На чтение 4 мин. Просмотров 2.4k.
Что делает макрос: Когда рабочая книга содержит несколько сводных таблиц, часто нужно иметь список инвентаризации, в котором изложены основные сведения о сводных таблицах. При этом в списке, вы можете быстро просмотреть важную информацию, такую как местоположение каждой сводной таблицы, расположение исходных данных каждой сводной таблицы, индекс кэша и т.д.
Следующий макрос выводит такой список.
Содержание
- Как макрос работает
- Код макроса
- Как это код работает
- Как использовать
Как макрос работает
Когда вы создаете переменную объекта PivotTable, вы открываете все свойства сводной таблицы — такие как имя, местоположение, индекс кэша и т. д. В этом макросе цикл проходит по каждой сводной таблице, извлекает определенные свойства на новый лист.
Код макроса
Sub SpisokSvodnihTablicKnigi()
'Шаг 1: Объявляем переменные
Dim ws As Worksheet
Dim pt As PivotTable
Dim MyCell As Range
'Шаг 2: Добавляем новый лист с заголовками столбцов
Worksheets.Add
Range("A1:F1") = Array("Pivot Name", "Worksheet", _
"Location", "Cache Index", _
"Source Data Location", _
"Row Count")
'Шаг 3: Устанавливаем якорь курсора в ячейке А2
Set MyCell = ActiveSheet.Range("A2")
'Шаг 4: Запускаем цикл по листам книги
For Each ws In Worksheets
'Шаг 5: Запускаем цикл по всем сводным таблицам
For Each pt In ws.PivotTables
MyCell.Offset(0, 0) = pt.Name
MyCell.Offset(0, 1) = pt.Parent.Name
MyRange.Offset(0, 2) = pt.TableRange2.Address
MyRange.Offset(0, 3) = pt.CacheIndex
MyRange.Offset(0, 4) = Application.ConvertFormula _
(pt.PivotCache.SourceData, xlR1C1, xlA1)
MyRange.Offset(0, 5) = pt.PivotCache.RecordCount
'Шаг 6: Переместить курсор строкой вниз и установить якорь
Set MyRange = MyRange.Offset(1, 0)
'Шаг 7: Работа через все сводные таблицы и листы
Next pt
Next ws
'Шаг 8: Соответствие размеров столбцов
ActiveSheet.Cells.EntireColumn.AutoFit
End Sub
Как это код работает
- Шаг 1 объявляет объект под названием WS. Это создает контейнер памяти для каждого рабочего листа. Затем мы объявляем объект под названием PT, который содержит
каждый цикл PivotTable . Наконец, мы создаем переменную диапазона под названием MyCell. Эта переменная действует как курсор, когда заполняет список инвентаризации. - Шаг 2 создает новый рабочий лист и добавляет заголовки столбцов, которые варьируются от А1 до F1. Обратите внимание, что мы можем добавить заголовки столбцов, используя простой массив, который содержит наши метки заголовка. Этот новый рабочий лист остается активным.
- Так же, как вы бы вручную помещали курсор в ячейку, если вы должны были начать вводить данные, шаг 3 помещает MyCell курсор в ячейку A2 активного листа. Это наша точка привязки, что позволяет нам перейти отсюда. На протяжении всего макроса, вы используете свойство offset. Свойство offset позволяет передвигать курсор Offset х количество строк и х количество столбцов от точки привязки. Например, Range (A2) .offset (0,1) будет перемещать курсор на один столбец. Если мы хотим, переместить курсор на одну строку вниз, мы вводим Range(A2). Offset(1, 0). В макросе, мы перемещаемся с помощью
Offset MyCell. Offset (0,4) будет перемещать курсор на четыре столбца справа от анкерной ячейки. После того, как курсор будет на месте, мы можем ввести данные. - Шаг 4 начинает зацикливание, говоря Excel, что мы хотим оценить все рабочие листы в этой книге.
- Шаг 5 перебирает все сводные таблицы в каждом листе. Для каждого PivotTable он находит, извлекает соответствующее свойство и заполняет таблицу на основании положения
курсора (шаг 3). Мы используем шесть свойств: Name, Parent.Range, TableRange2, CacheIndex, PivotCache.SourceData и PivotCache. RecordCount.
Свойство Name возвращает имя сводной таблице.Свойство Parent.Range дает нам лист, где постоянно находится PivotTable. Свойство TableRange2.Address возвращает диапазон, где находится PivotTable. Свойство CacheIndex возвращает порядковый номер кэша сводной для сводной таблицы.
Кэш-память поворота представляет собой контейнер памяти, которая хранит все данные для сводной таблицы. При создании новой сводной таблицы, Excel делает снимок исходных данных и
создает кэш возврата. Каждый раз при обновлении сводной таблицы, Excel приходит к исходным данным и делает еще один снимок, тем самым обновляя кэш возврата. Каждый кэш имеет свойство SourceData, который идентифицирует местоположение данных, используемых для создания кэш возврата. PivotCache. SourceData свойство сообщает нам, какой диапазон будет призван, когда мы обновить сводную таблицу. Вы также можете вытащить количество записей из исходных данных, используя PivotCache.Recordcount. - Каждый раз, когда макрос встречает новую сводную таблицу, он перемещает курсор вниз MyCell строку, начиная новую строку для каждого сводной таблицы.
- Шаг 7 говорит Excel повторить цикл для всех листов. После того, как все листы были оценены, макрос переходит к последнему шагу.
- Шаг 8 завершает с небольшим количеством форматирования размеров столбцов, чтобы соответствовало данным.
Как использовать
Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:
- Активируйте редактор Visual Basic, нажав ALT + F11.
- Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
- Выберите Insert➜Module.
- Введите или вставьте код.
Как вывести список всех сводных таблиц из книги?
Предположим, у вас есть большая книга, содержащая несколько сводных таблиц, теперь вы хотите перечислить все сводные таблицы в этой книге, возможно ли это? Конечно, следующий код VBA в этой статье окажет вам услугу. Чтобы узнать больше, прочтите статью ниже.
Список всех сводных таблиц из книги с кодом VBA
Список всех сводных таблиц из книги с кодом VBA
Список всех сводных таблиц из книги с кодом 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 Ключ для запуска этого кода, все имена сводных таблиц, диапазон исходных данных, имя рабочего листа и другие атрибуты перечислены в новом рабочем листе, который помещается перед вашим активным листом, как показано на следующем снимке экрана:
Статьи по теме:
Как проверить, существует ли в книге сводная таблица?
Как добавить несколько полей в сводную таблицу?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (20)
Оценок пока нет. Оцените первым!
Пользовательское соглашение
Политика конфиденциальности
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
Техническая поддержка сайта
|
ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Can someone tell me how I can find all the pivot tables in a workbook (or sheet)? In some cases a pivot table might be hidden or hard to find in a very large excel sheet. If i could at least get the cell address or range of where the pivot table(s) are/is, that would be great.
Thanks.
asked Dec 14, 2011 at 20:13
This should work for you. It prints out the results to the Immediate window:
Sub FindPivotTables()
Dim wst As Worksheet
Dim pvt As PivotTable
' loop through all sheets and print name & address of all pivot tables
For Each wst In ActiveWorkbook.Worksheets
For Each pvt In wst.PivotTables
Debug.Print wst.Name, pvt.TableRange2.Address, pvt.Name
Next pvt
Next wst
End Sub
answered Dec 15, 2011 at 4:59
Rachel HettingerRachel Hettinger
7,8522 gold badges21 silver badges31 bronze badges
0
Each worksheet should expose a PivotTables collection; you can loop through each worksheet looking for sheets that have a .PivotTables.Count > 0 and then loop through the PivotTables on that sheeet to find the one you are looking for:
Sub Test()
Dim pTable As pivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim pivotTableCount
pivotTableCount = ws.PivotTables.Count
If pivotTableCount > 0 Then
For Each pTable In ws.PivotTables
Dim pivotTableName As String
pivotTableName = pTable.Name
Dim pivotTableTopLeftCell As String
pivotTableTopLeftCell = pTable.Location
Next pTable
End If
Next ws
End Sub
NorthCat
9,55316 gold badges47 silver badges50 bronze badges
answered Dec 14, 2011 at 20:29
dashdash
89.2k4 gold badges51 silver badges71 bronze badges
2
Жизнь человека из мира технологий — это невероятная комбинация цифр, показателей, которые периодически требуют систематизации. Чтобы осуществить последнее, приходится использовать особые технические инструменты. Наша статья докажет, что сводные таблицы в Excel доступны даже для чайников.
Немного теории
Сводные таблицы Excel (для чайника) — это разновидность реестра, которая содержит конкретную часть данных из источника для анализа и изображена так, чтобы можно было проследить между ними логические связи. Основа для ее проектирования — определенный список значений.
Прежде чем начать работу, стоит подготовить для неё необходимые материалы, которые можно свести для анализа. Формируя подготовительный вариант, следите за тем, чтобы данные были классифицированы, например, цифры не путались с буквенным обозначением, а все столбцы имели заголовки.
Сводные таблицы в Excel незаменимы для тех, кто по роду занятости имеет дело с большим количеством цифр, которые периодически необходимо систематизировать и формировать отчетность. Программа Excel поможет подсчитать и проанализировать большое количество значений, сэкономив время.
Плюсы использования такого вида группировки данных:
- во время работы не нужны особые познания из сферы программирования, метод подойдет и для чайников;
- возможность комбинировать информацию из других первоисточников;
- можно пополнять базовый экземпляр новой информацией, несколько подкорректировав параметры.
Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.
Пример создания сводной таблицы Excel – алгоритм для чайников
Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.
Запустив его, перед вами откроется обширное поле, разделенное на большое количество ячеек. Более детально о том, как делать сводные таблицы в Excel, вам подскажет видеоурок выше.
С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel.
На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».
Далее на экране открывается диалоговое окошко, где требуется указать соответствующие параметры. На этом этапе создания сводных таблиц в Excel есть несколько важных моментов. Если перед тем, как начать формирование реестра, вы установили значок курсора на листе, то заполнение пустых строчек окна произойдёт автоматически. Иначе адрес диапазона данных нужно обозначить самостоятельно.
Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.
Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.
Заранее озаглавьте каждый столбик
Выберите место, где будет располагаться будущая рамка с ячейками. Это может быть новое окно или же этот лист, рекомендуем использовать другой лист.
Закрепив все настройки, получаем готовую основу. По левую сторону располагается область, где размещена основа будущей рамки. В правой части есть окно с настройками, которое помогает управлять реестром.
Теперь необходимо разобраться, как строится вся эта конструкция. В окне настроек «Поля свободной таблицы», вы обозначаете данные, которые будут присутствовать.
Вся структура строится таким образом, чтобы текстовые данные играли роль объединяющих элементов, а числовые показывали консолидированные значения. Например, объединим все поступления по отделам и узнаем на какую сумму получил товары каждый. Для этого поставим галочку у двух заголовков: отдел и стоимость товара в партии.
Обратите внимание, как расположились эти данные в нижней области панели настройки.
Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец.
При этом происходит суммирование. Сейчас мы можем узнать из нашего отчета, сколько товаров поступило в каждый отдел и их общую стоимость.
Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel.
Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов — просто наведите мышь, нажмите левую кнопку и тяните.
Что касается подсчета результатов, то сумма – далеко не единственная функция. Чтобы посмотреть, какие Excel предлагает еще, нажмите на название в области значений и выберите последнюю команду.
В параметрах полей значений вы найдете множество вариантов для анализа.
Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой.
Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.
Сейчас сводная таблица в Excel выглядит неудобно, если нам необходимо провести анализ по дате. Поэтому переместим дату из строк в фильтры — просто перетяните, как было указано выше.
Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все».
Теперь нам доступен выбор конкретного дня, чтобы открыть список, щелкайте по треугольнику в правом углу.
Также можно выбрать и значения для отдела.
Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию.
Во время работы вы можете столкнуться с подобным сообщением «недопустимое имя сводной таблицы Excel». Это означает, что первая строка диапазона, откуда пытаются извлечь информацию, осталась с незаполненными ячейками. Чтобы решить эту проблему, вы должны заполнить пустоты колонки.
Обновление данных в сводной таблице в Excel
Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».
Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.
Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.
Как в сводную таблицу Excel добавить столбец или таблицу
Чтобы добавить дополнительный столбец, вам необходимо добавить его в исходные данные и расширить диапазон для нашего реестра.
Перейдите на вкладку «Анализ» и откройте источник данных.
Excel сам все предложит.
Обновите и вы получите новый перечень полей в области настройки.
Добавить таблицу вы сможете только если «склеите» ее с исходной. Можно заменить диапазон в уже существующей, но добавить «на лету» другой диапазон нельзя. Зато можно создать новую сводную таблицу на основе нескольких исходных, даже расположенных на разных листах.
Как сделать сводную таблицу в Excel из нескольких листов
Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».
Выберите все команды.
И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.
Значок появится сверху.
У вас должны быть две идентичные по полям таблицы на разных листах. У нас это данные о поступлениях в отделы за май и июнь. Нажимайте на ярлык мастера сводных таблиц и выбирайте консолидацию диапазонов.
Нам нужно несколько полей, а не одно.
На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист ( щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.
Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел».
Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем.
Щелкайте «Далее» и создавайте на новом листе.
После нажатия на «Готово» получим результат. Это многомерная таблица, так что управлять ей довольно сложно. Поэтому мы и выбрали диапазон меньше, чтобы не запутаться в измерениях.
Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.
Снимая или устанавливая галочки, вы регулируете значения, которые вам необходимо увидеть. Неудобно и то, что расчет проводится для всех значений одинаковый.
Как видите, у нас одно значение в соответствующей области.
Изменение структуры отчёта
Мы поэтапно разобрали пример, как создать сводную таблицу Exce, а как получить данные другого вида расскажем далее. Для этого мы изменим макет отчета. Установив курсор на любой ячейке, переходим во вкладку «Конструктор», а следом «Макет отчета».
Вам откроются на выбор три типа для структуризации информации:
- Сжатая форма
Такой тип программа применяется автоматически. Данные не растягиваются, поэтому прокручивать изображения практически не нужно. Можно сэкономить место на подписях и оставить его для чисел.
- Структурированная форма
Все показатели подаются иерархично: от малого к большому.
- Табличная форма
Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.
Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.
Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.
Отличного Вам дня!