Итог: узнаете, как отфильтровать сводную таблицу, сводную диаграмму или установить срез для самой последней даты или периода в наборе данных.
Уровень мастерства: Средний
Pip имеет набор отчетов на основе сводных таблиц, которые она часто обновляет (ежедневно, еженедельно, ежемесячно). Она хочет автоматически фильтровать отчеты по самой последней дате в столбце в наборе данных. Этот фильтр выберет элемент в слайсере, чтобы отфильтровать сводные таблицы и диаграммы.
Как и все в Excel, есть несколько способов решить эту проблему. Мы можем использовать макрос или добавить вычисляемый столбец в набор данных …
Настройка: данные конвейера продаж CRM
В этом примере мы собираемся использовать данные о продажах. Таблица данных содержит еженедельные снимки или экспорт данных из системы CRM (Salesforces.com, Dynamics CRM, HubSpot и т.д.).
Для обновления данных мы экспортируем конвейерный отчет каждую неделю и вставляем данные в конец существующей таблицы. Есть и другие способы автоматизации этого процесса, но мы не будем вдаваться в подробности.
В наборе данных есть столбец «Дата отчета», в котором содержится дата запуска отчета для каждой строки. Вы можете видеть на изображении, что есть 4 набора данных, добавленных (сложенных) вместе, чтобы создать одну большую таблицу.
Сводная таблица показывает сводку доходов по этапам конвейера, а поле «Дата отчета» находится в области «Фильтры». Это позволяет нам фильтровать по любой дате отчета, чтобы увидеть сводную информацию о конвейере за эту неделю.
Когда новые данные добавляются в таблицу данных, мы хотим автоматически фильтровать все связанные сводные таблицы, диаграммы и срезы на последнюю дату отчета.
Решение № 1: Макрос VBA для фильтрации сводной таблицы по определенной дате или периоду
Мы можем использовать простой макрос, чтобы установить фильтр в сводной таблице для самой последней даты в таблице исходных данных. Фильтрация поля «Дата отчета» в сводной таблице также выберет отфильтрованный элемент в срезе и отфильтрует все связанные сводные диаграммы.
Приведенный ниже макрос может выглядеть как много кода, но на самом деле он очень прост. Чтобы его использовать, вам просто нужно будет указать все переменные для имени рабочего листа, имени сводной таблицы, имени сводного поля (Дата отчета) и критериев фильтрации (последняя дата). Эти переменные будут представлять имена объектов в вашей собственной книге.
Sub Filter_PivotField()
' Описание: фильтрация сводной таблицы или среза по определенной дате или периоду.
Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotItem
' Установите переменные
sSheetName = "Pivot"
sPivotName = "PivotTable1"
sFieldName = "Report Date"
'sFilterCrit = "5/2/2016"
sFilterCrit = ThisWorkbook.Worksheets("Data").Range("G2").Value
With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
' Очистить все фильтры основного поля
.ClearAllFilters
' Проходить по элементам сводки поля сводки
' Скрыть или отфильтровать элементы, которые не соответствуют критериям
For Each pi In .PivotItems
If pi.Name <> sFilterCrit Then
pi.Visible = False
End If
Next pi
End With
End Sub
Макрос в настоящее время настроен на использование значения из ячейки G2 в Таблице данных для критериев фильтра (самая поздняя дата). Ячейка G2 содержит формулу, которая возвращает самую последнюю дату из столбца с помощью функции MAX
Конечно, это можно изменить, чтобы вычислить самую последнюю дату в коде макроса. Хотя приятно видеть это на листе.
Как работает макрос?
Макрос сначала очищает все фильтры для поля сводки фильтра отчетов с помощью метода ClearAllFilters.
Затем он использует цикл For Next для циклического прохождения всех элементов сводки в поле сводки, чтобы применить фильтр. Каждый уникальный элемент в поле является основным элементом. Макрос проверяет, не соответствует ли имя элемента сводки (<>) критериям. Если нет, то он скрывает элемент или фильтрует его. В результате мы видим только критерии фильтра.
У меня есть статья, которая объясняет For Next Loops более подробно.
Как насчет фильтрации двух сводных таблиц за разные периоды времени?
Вот еще один пример с настройкой переменных в качестве параметров макроса. Это позволяет нам вызывать макрос из другого макроса.
Sub Filter_PivotField_Args( _
sSheetName As String, _
sPivotName As String, _
sFieldName As String, _
sFilterCrit As String)
' Фильтрация сводной таблицы или среза по определенной дате или периоду
Dim pi As PivotItem
With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
' Очистить все фильтры основного поля
.ClearAllFilters
' Проходить по элементам сводки поля сводки
' Скрыть или отфильтровать элементы, которые не соответствуют критериям
For Each pi In .PivotItems
If pi.Name <> sFilterCrit Then
pi.Visible = False
End If
Next pi
End With
End Sub
Sub Filter_Multiple_Pivots()
' Вызвать макрос Filter Pivot для нескольких пивотов
Dim sFilter1 As String
Dim sFilter2 As String
' Установите критерии фильтра
sFilter1 = ThisWorkbook.Worksheets("Data").Range("G2").Value
sFilter2 = ThisWorkbook.Worksheets("Data").Range("G3").Value
' Вызовите макрос сводных фильтров, чтобы отфильтровать оба сводных
Call Filter_PivotField_Args("2 Pivots", "PivotTable1", "Report Date", sFilter1)
Call Filter_PivotField_Args("2 Pivots", "PivotTable2", "Report Date", sFilter2)
End Sub
В этом примере у нас есть лист с двумя сводными таблицами для сравнения неделя за неделей. Таким образом, нам нужно отфильтровать одну сводную таблицу для самой последней даты и одну для даты предыдущей недели.
Макрос Filter_Multiple_Pivots вызывает макрос Filter_PivotField_Args дважды. Обратите внимание, что имя сводной таблицы и значения критериев фильтрации различны для каждого вызова. Это простой способ многократно использовать макрос сводного поля фильтра без необходимости повторения большого количества кода.
Ознакомьтесь с моей бесплатной серией видео о том, как начать работу с макросами и VBA, чтобы узнать больше о написании макросов.
Решение № 2. Добавление вычисляемого столбца в набор данных
Если вы не можете использовать макросы, вы можете решить эту проблему с помощью вычисляемого столбца. Это решение НЕ так гибко, как макрос. Оно НЕ очень хорошо работает, если вы хотите иметь срезы, где пользователь может выбирать другие даты отчета.
Мы можем добавить столбец к исходным данным, чтобы проверить, совпадает ли дата отчета в каждой строке с самой последней датой. Мы назовем этот столбец «Current Wk».
Если это так, то формула вернет ИСТИНА, если нет — ЛОЖЬ.
Это очень простая формула. Мы могли бы использовать функцию If, но в этом нет необходимости. Знак равенства оценивает совпадение и возвращает значение ИСТИНА или ЛОЖЬ. Ознакомьтесь с моей статьей If в формулах Excel для объяснения этого.
Теперь мы можем добавить этот новый столбец в область «Фильтры» сводной таблицы и отфильтровать его для ИСТИНА. Это означает, что каждый раз, когда исходные данные обновляются новыми данными, формулы пересчитывают новые строки текущей недели. Обновление сводной таблицы автоматически применит фильтр для строк текущей недели и отобразит их в сводной таблице.
Недостатком здесь является то, что пользователь не может использовать слайсер для фильтрации других дат. Технически они могут, но нам придется повторно применить фильтр Current Wk при следующем обновлении данных. Мы могли бы использовать макрос для автоматизации этого, но тогда мы действительно вернулись к решению № 1.
Хотя он не такой динамичный, как решение № 1, рассчитанный столбец может быть всем необходимым для отображения данных за последний период в статическом отчете.
Два способа автоматизации фильтрации сводных таблиц
Фильтрация сводных таблиц по самой последней дате или периоду — это, безусловно, хороший процесс для автоматизации, если вы часто выполняете эту задачу. Это может сэкономить нам много времени и предотвратить ошибки, которые обычно возникают при этих скучных повторяющихся задачах. 🙂
Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо!