У Дэвида есть много сводных таблиц, которые фильтруются по годам. В его данных есть отдельное поле для года (значения 2016, 2017, 2018 и т.д., полученные через функцию Year). Он исключает пожилые годы в большинстве случаев и сообщает о более поздних годах. Когда наступает новый год, Дэвиду утомительно редактировать каждую сводную таблицу и выбирать последний год. Он задается вопросом, есть ли способ указать значения выбора для фильтра сводной таблицы, чтобы ему не приходилось выполнять утомительное редактирование.

Возможно, самый простой способ сделать это — добавить один столбец к исходным данным для вашей сводной таблицы. Столбец может содержать простую формулу, определяющую, находится ли строка в желаемом диапазоне для включения в сводную таблицу. Например, если столбец A содержит дату транзакции для строки, вы можете включить в добавленный столбец следующее:

=YEAR(A2)>YEAR(NOW())-3

Результат формулы — Истина или Ложь, в зависимости от того, совершена ли транзакция в течение предыдущих трех лет или нет. Таким образом, если эта формула оценивается в 2018 году, то любые транзакции в течение 2016, 2017 и 2018 вернут True; все остальные будут ложными. Затем в определении сводной таблицы вы можете выполнить фильтрацию на основе содержимого этого конкретного столбца, тем самым гарантируя, что в сводную таблицу включены только эти строки True.

Если вы предпочитаете решение на основе макросов, вы можете легко разработать такое решение, которое проверяло бы каждую из сводных таблиц и изменяло сводное поле с именем «Год»

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

Sub ChangePivotYear()

Dim sht As Worksheet     Dim pvt As PivotTable     Dim iYear as Integer

iYear = 2018     ' Change to desired year     For Each sht In Worksheets         For Each pvt In sht.PivotTables             pvt.PivotFields("Year").ClearAllFilters             pvt.PivotFields("Year").CurrentPage = iYear        Next pvt     Next sht End Sub

Макрос устанавливает в поле значение 2018; если вы хотите использовать другой год, просто измените значение, присвоенное переменной Year. Также обратите внимание, что макрос влияет на все сводные таблицы во всей книге.

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12571) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.