Автоматическое обновление сводной таблицы.
На текущем занятии мы ознакомимся с различными способами обновления сводной таблицы автоматически. Сразу оговоримся, что существует разница между обновлением данных в исходном диапазоне для сводной таблицы и обновлением области источника данных для сводной таблицы. В первом случае достаточно обработать изменения внутри исходного диапазона, а во втором потребуется создать именованный диапазон, который автоматически будет отслеживать изменения структуры исходной области (добавление или удаление строк, добавление колонок и так далее). На занятии наглядно рассматривается разница в применении первого и второго метода и особенности каждого из них.
Итака, приступаем к работе. В нашем файле на листе AZS имеется таблица с информацией о реализации различных видов топлива. Задача – настроить на основе этой таблицы (назовем ее таблицей АЗС) несколько сводных таблиц и настроить их автоматическое обновление.
Рисунок
Приступим к работе. На первом шаге мы зададим на основе нашей таблицы два именованных диапазона – АЗС_Данные и АЗС_Дата. Два диапазона потребуются в дальнейшем, чтобы рассмотреть особенности автоматического обновления сводных таблиц, основанных на динамических диапазонах.
Для создания динамического диапазона применяются разные способы. В частности, можно преобразовать исходный диапазон в умную таблицу, а затем использовать ее название. Сегодня же воспользуемся двумя вариантами создания динамического именованного диапазона формулами. Переходим на вкладку «Формулы» , нажимаем кнопку «Диспетчер имен». Кстати, открыть окно диспетчера имен можно, нажав сочетание Ctrl+F3. Для добавления имени нажимаем кнопку «Создать». В верхней строке вводим имя – АЗС_Данные, а в нижней – формулу, описывающую наш диапазон.
В первом случае мы применим для задания адреса диапазона функцию СМЕЩ, а так же тот факт, что по умолчанию в первой строке и первой колонке исходного диапазона для создания сводной таблицы заполнены все ячейки. В результате имеем формулу:
=СМЕЩ(AZS!$A$1;;;СЧЁТЗ(AZS!$A:$A);СЧЁТЗ(AZS!$1:$1))
Рисунок
Нажимаем кнопку ОК, затем снова нажимаем кнопку «Создать». Создаем новый диапазон с именем АЗС_Дата и формулой
=AZS!$A$1:СМЕЩ(AZS!$A$1;СЧЕТЗ(AZS!$A:$A)-1;СЧЕТЗ(AZS!$1:$1)-1)
Нажимаем кнопку ОК, возвращаемся в диспетчер имен и нажимаем кнопку «Закрыть». Два динамических диапазона с различными именами и формулами созданы.
Рисунок
На следующем шаге создадим два листа, назовем их «Свод АЗС 1» и «Свод АЗС 2». Разместим на них несколько сводных таблиц. На листе «Свод АЗС 1» поместим 2 сводные таблицы на основе диапазона «АЗС_Данные», одну таблицу на основе имени «АЗС_Дата» и одну таблицу на основе исходной таблица АЗС. В каждой из них выведем наименования номенклатурной группы и реализацию в литрах. На листе «Свод АЗС 2» разместим три таблицы – одну на основе диапазона «АЗС_Дата», вторую – на основе диапазона «АЗС_Данные» и третью – на основе таблицы АЗС. Как и на первом листе, выведем в них реализацию в литрах по наименованиям номенклатуры. Кроме этого, на каждом листе скопируем в виде значений исходные отчеты, а также присвоим соответствующие имена таблицам. На первом листе таблицы получат названия «АЗС Данные 1», «АЗС Данные 2», «АЗС Дата 1» и «Таблица АЗС 1» соответственно. Сводные таблицы на втором листе получат аналогичные названия, но без нумерации. На изображении ниже размещение таблиц на листе «Свод АЗС 1» показано наглядно.
Рисунок
Данный шаг намерено был усложнен, чтобы иметь возможность отследить нюансы автоматического обновления. На самом деле можно было бы добавить всего три таблицы, две на одном и одну – на втором. Более подробно о создании, размещении и первоначальном форматировании сводных таблиц читайте в наших предыдущих материалах. Теперь, когда необходимые таблицы созданы, переходим к рассмотренную вариантов автоматического обновления.
Перейдем непосредственно к автоматическому обновлению сводных таблиц. Сразу стоит отметить, что обновление каждой таблицы через определенный период помимо явной бессмысленности приведет к постоянным подвисаниям при работе с файлом. Это же относится и к настройке автоматического обновления при открытии и закрытии книги со сводными таблицами. Поэтому мы начнем отслеживать события листа. Но не события изменения выделения на листе с исходной таблицей или выбор определенных ячеек на листе с сводной таблицей. Эти способы не то чтобы бесполезны, но достаточно неудобны. Ведь при обновлении исходной таблицы в любом случае придется открывать лист с сводной таблицей или таблицами заново. В результате выберем процедуру, отслеживающую активацию, другими словами – открытие, листа с сводными таблицами.
Проверим первый вариант настройки обновления сводных таблиц при активации листа с ними. Настроим обновление всех сводных таблиц на выбранном листе и в качестве объекта эксперимента выберем лист «Свод АЗС 1». Нажимаем на его корешке правой кнопкой и выбираем команду «Просмотреть код».
Рисунок
Запустится редактор VBA и откроется модуль текущего листа. В выпадающем списке слева выбираем объект WorkSheet, а справа – событие Activate.
Рисунок
Вставляем в процедуру Worksheet_Activate() следующий код
Dim Pt As PivotTable
For Each Pt In ActiveSheet.PivotTables
Pt.PivotCache.Refresh
Next Pt
Разберем его по порядку. Вначале объявляется переменная Pt с типом PivotTable (сводная таблица). Затем перебираются все элементы коллекции PivotTables с помощью подставления каждой из них в созданную переменную и у каждой таблицы обновляется кеш.
Рисунок
Возвратимся в Excel и сделаем небольшие изменения в таблице АЗС. Увеличим в 100 раз (для наглядности) объем реализации в литрах в первой строке и добавим колонку «Средний объем», в которую выведем отношении реализации в литрах к количеству трансакций. Затем вернемся на лист «Свод АЗС 1».
Рисунок
Как видим, данные обновились во всех таблицах, при этом в сводных таблицах, основанных на именах, в списке полей появилось поле «Средний объем», в таблице же, основанной непосредственно на исходном диапазоне, этого не произошло.
Рисунок
Теперь перейдем на лист «Свод АЗС 2». Что мы видим? Все таблицы тоже обновились .
Рисунок
Перейдём ко второму варианту. На листе «Свод АЗС 2» настроим обновление только для выбранной таблицы при активации листа и для начала выберем таблицу, основанную на исходном диапазоне. Напомним, она называется «Таблица АЗС». Снова переходим в код листа, выбираем событие Activate, но вставляем теперь только одну строку.
ActiveSheet.PivotTables(“Таблица АЗС”).PivotCache.Refresh
Вернемся в Excel и уменьшим объем реализации в первой строке в 10 раз. Перейдем на лист «Свод АЗС 2»
Рисунок
Обратите внимание, что теперь обновилась информация по реализации в литрах бензина (92,80) только для указанной в коде таблицы. Перейдем в код листа «Свод АЗС 1» и настроим обновление только таблицы «АЗС Дата 1», основанной на соответствующем именованном диапазоне.
Рисунок
Теперь вернемся на лист AZS, после чего увеличим в 10 раз объем реализации во второй строке (номенклатурная группа «Высокий октан») и добавим колонку «средний чек».
Откроем после этого листы «Свод АЗС 1» и «Свод АЗС 2»:
«Свод АЗС 1»
Рисунок
«Свод АЗС 2»
Рисунок
Как видим произошло обновление всех сводных таблиц, основанных на именованном диапазоне «АЗС_Дата».
Наконец переходим к третьему варианту и настроим обновление всех сводных таблиц, которое не будет зависеть от открытия конкретного листаю для этого в отдельном модуле, желательно в личной книге макросов, вставим соответствующую процедуру, которая будет перебирать каждый лист и обновлять в цикле все имеющиеся на нем модули:
Sub AllPivotRefresh()
Dim Ws As Worksheet
Dim Pt As PivotTable
For Each Ws In Worksheets
For Each Pt In Ws.PivotTables
Pt.PivotCache.Refresh
Next Pt
Next Ws
End Sub
Данный код можно запускать или комбинацией Alt+F8 либо в качестве варианта добавив кнопку на панель быстрого доступа. Напомним вкратце как это сделать.
На вкладке файл открываем параметры Excel и переходим к пункту «Панель быстрого Доступа»
Рисунок
Теперь справа в выпадающем списке «Выбрать команды из» выбираем «Макросы». Находим наш макрос, нажимаем кнопку «Добавить». После этого, выбрав его в правом списке, щелкаем кнопку «Изменить», после чего корректируем название, меняем значок. Закрываем параметры нажатием кнопки ОК и вверху в Excel появляется кнопка, вызывающая наш макрос по обновлению всех сводных таблиц.
Рисунок
Конечно, данный вариант нельзя назвать полностью автоматическим – придется нажимать кнопку или комбинацию клавиш, но это лучше, чем закрывать и открывать книгу при необходимости такого обновления. Ну и естественно, что для того, чтобы все эти коды, в том числе коды страниц, в книге работали, файл необходимо сохранить как файл с поддержкой макросов. Не забываем про это, иначе при сохранении Excel удалит весь вставленный код.
Подведем итоги.
- Можно настроить автоматическое обновление при открытии листа как всех расположенных на нем сводных таблиц, так и конкретно указанной из них.
- При обновлении любой таблицы на листе, основанной на динамическом диапазоне, обновляются все таблицы на каждом листе, основанные на этом же диапазоне. Таблицы, основанное на другом динамическом диапазоне, описывающем те же исходные данные, не обновляется.
- Если задано обновление только для таблицы, основанной на исходных данных, а не на динамическом диапазоне, то таблица обновится только на листе, где обновление указано в коде.
- Можно настроить обновление сводных таблиц не только при активации листа, но и при выборе на нем определенных ячеек, либо при внесении дополнительных изменений. Предлагаем попробовать этот способ самостоятельно. При возникновении проблем напишите нам, разберемся вместе.
- Если требуется выполнить обновление всех сводных таблиц в книге, лучше это сделать в виде отдельного макроса.
На этом занятие подошло к концу. Как обычно, вы можете скачать исходный файл для самостоятельной тренировки. При возникновении вопросов можете связаться с нами или задать свой вопрос, отправив сообщение.
Делаем всем удачного дня и успешной работы! До встречи на наших занятиях!
