Цей підручник покаже, як використовувати фільтр зведеної таблиці у VBA.
Зведені таблиці - це надзвичайно потужний інструмент даних Excel. Зведені таблиці дозволяють нам аналізувати та інтерпретувати великі обсяги даних шляхом групування та узагальнення полів та рядків. Ми можемо застосувати до наших зведених таблиць фільтри, щоб ми могли швидко бачити дані, які є для нас актуальними.
По -перше, нам потрібно створити зведену таблицю для наших даних. (Натисніть тут, щоб переглянути наш посібник зі зведеної таблиці VBA).
Створення фільтра на основі значення комірки
Ви можете фільтрувати у зведеній таблиці за допомогою VBA на основі даних, що містяться у значенні комірки - ми можемо або фільтрувати в полі Сторінка або в полі Рядок (наприклад, у полі Постачальник вище або в полі Операція, що у стовпці Мітки рядків ).
У порожній клітинці праворуч від зведеної таблиці створіть клітинку для утримання фільтра, а потім введіть дані у клітинку, за якою потрібно відфільтрувати зведену таблицю.
Створіть такий макрос VBA:
| 1234567 | ПідфільтрPageValue ()Затемнити pvFld як зведене полеDim strFilter As StringВстановити pvFld = ActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Постачальник")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.CurrentPage = strFilterEnd Sub |
Запустіть макрос, щоб застосувати фільтр.
Щоб очистити фільтр, створіть такий макрос:
| 12345 | Sub ClearFilter ()Затемнити pTbl як зведену таблицюВстановити pTbl = ActiveSheet.PivotTables ("Зведена таблиця1")pTbl.ClearAllFiltersEnd Sub |
Після цього фільтр буде видалено.
Потім ми можемо змінити критерії фільтрації, щоб фільтрувати рядок у зведеній таблиці, а не поточну сторінку.
Після цього введення наступного макросу дозволить нам відфільтрувати рядок (зверніть увагу, що зведене поле для фільтрації тепер є оператором, а не постачальником).
| 1234567 | ПідфільтрRowValue ()Затемнити pvFld як зведене полеDim strFilter As StringВстановити pvFld = ActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Oper")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.PivotFilters.Add2 xlCaptionEquals,, strFilterEnd Sub |
Запустіть макрос, щоб застосувати фільтр.
Використання кількох критеріїв у зведеному фільтрі
Ми можемо додати до фільтра значення рядка вище, додавши додаткові критерії.
Однак, оскільки стандартний фільтр приховує рядки, які не є обов’язковими, нам потрібно перебирати критерії та показувати ті, які повторюються, приховуючи ті, які не є обов’язковими. Це робиться шляхом створення змінної Array та використання пари циклів у коді.
| 1234567891011121314151617181920212223 | ПідфільтрMultipleRowItems ()Dim vArray як варіантDim i Як ціле число, j як ціле числоЗатемнити pvFld як зведене полеВстановити pvFld = ActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Oper")vArray = Діапазон ("M4: M5")pvFld.ClearAllFiltersЗ pvFldДля i = 1 До pvFld.PivotItems.Countj = 1Do While j <= UBound (vArray, 1) - LBound (vArray, 1) + 1Якщо pvFld.PivotItems (i) .Name = vArray (j, 1) ТодіpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = TrueВихід ДоІнакшеpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = FalseЗакінчити Якщоj = j + 1ПетляДалі iЗакінчити зEnd Sub |
Створення фільтра на основі змінної
Ми можемо використовувати ті ж концепції для створення фільтрів на основі змінних у нашому коді, а не значення у комірці. Цього разу змінна фільтра (strFilter) заповнюється в самому коді (наприклад: жорстко кодується в макрос).
| 1234567 | ПідфільтрTextValue ()Затемнити pvFld як зведене полеDim strFilter As StringВстановити pvFld = ActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Постачальник")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterEnd Sub |
