Настройка фильтров в сводной таблице через макрос - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Настройка фильтров в сводной таблице через макрос - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Настройка фильтров в сводной таблице через макрос (Макросы/Sub)

Настройка фильтров в сводной таблице через макрос

Дата: Среда, 29.03.2023, 09:35 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

Добрый день уважаемые форумчане!
Столкнулся с такой проблемой - имеется несколько больших сводных таблиц, исходные данные которых ежемесячно обновляются. Суть вопроса в том, что при обновление исходных данных, во все фильтры сводных таблиц так же подгружается новая информация и ее приходится выбирать руками, т.е. это крайне неудобно и затрачивает достаточно много времени. Возможно ли при помощи макроса автоматизировать сей процесс? Т.е. чтобы в фильтрах выбиралось все, кроме условных 2-3 значений, которые из месяца в месяц не изменяются?
Заранее благодарен.

Ответить

Дата: Среда, 29.03.2023, 10:15 | Сообщение № 2

Группа: Пользователи

Ранг: Новичок

Сообщений: 30

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Добрый день, я не профи в макросах, вот так я у себя обновлял фильтры, может подойдет?

[vba]

Код

'назначаем переменную pvtTable для работы с сводной таблицей
Set pvtTable = Worksheets("СвднКнг").PivotTables("ОтчетныйГодСводнаяТаб")
pvtTable.PivotFields("Месяц").ClearAllFilters 'сбрасываем фильтр "Месяц" выбранной сводной таблицы
pvtTable.PivotFields("Месяц").EnableMultiplePageItems = True 'разрешаем менять отдельные позиции фильтра "Месяц"

'ставим все галочки на фильтр (возможно эта часть не нужна)
For lngCountSvodBook = 1 To pvtTable.PivotFields("Месяц").PivotItems.Count Step 1
pvtTable.PivotFields("Месяц").PivotItems(lngCountSvodBook).Visible = True
Next

[/vba]

Сообщение отредактировал kotlovan - Среда, 29.03.2023, 10:18

Ответить

Дата: Среда, 29.03.2023, 15:25 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

kotlovan,
спасибо. А как убрать из фильтров значения, которые содержат в себе, например "учеб.". Т.е. в фильтре огромный список значений и только несколько из них имеют такой префикс, но он может быть как в начале, так и в конце - Учеб. Авто № 343 или же Авто 3... учеб.
пытаюсь править PivotItems и делаю так - PivotItems(*учеб.*), но выходит ошибка

Сообщение отредактировал Artem9222 - Среда, 29.03.2023, 15:29

Ответить

Дата: Среда, 29.03.2023, 15:49 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16714

Репутация: 6503 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Если одно условие, то можно использовать фильтр "Не содержит"
Запишите макрорекодером, получите вот так примерно
[vba]

Код

ActiveSheet.PivotTables("Сводная таблица2").PivotFields("й").PivotFilters.Add2 Type:=xlCaptionDoesNotContain, Value1:="учеб"

[/vba]

Если много всяких разных, то перебор
[vba]

Код

    Application.ScreenUpdating = 0
    With ActiveSheet.PivotTables(1).PivotFields("й")
        .ClearAllFilters
        For Each aaa In .PivotItems
            If aaa.Name Like "*учеб*" Then
                aaa.Visible = False
            Else
                aaa.Visible = True
            End If
        Next aaa
    End With
    Application.ScreenUpdating = 1

[/vba]

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Вторник, 04.04.2023, 11:37 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

_Boroda_,
спасибо. Частично помогло, но столкнулся со следующей проблемой - где-то "учеб." написано строчными буквами, а где-то заглавными - как исключить и то и другое?

Ответить

Дата: Вторник, 04.04.2023, 12:15 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Artem9222, замените строку[vba]

Код

If aaa.Name Like "*учеб*" Then

[/vba]на[vba]

Код

If aaa.Name Like "*учеб*" Or aaa.Name Like "*УЧЕБ*" Then

[/vba]

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Вторник, 04.04.2023, 13:53 | Сообщение № 7

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Serge_007, не учи плохому
[vba]

Код

If Lcase(aaa.Name) Like "*учеб*" Then

[/vba]
или
[vba]

Код

If Ucase(aaa.Name ) Like "*УЧЕБ*" Then

[/vba]

Быть или не быть, вот в чем загвоздка!

Ответить

Дата: Вторник, 04.04.2023, 13:56 | Сообщение № 8

Группа: Проверенные

Ранг: Обитатель

Сообщений: 415

Репутация: 129 ±

Замечаний: 0% ±

Excel 2007

Можно ещё так попробовать
[vba]

Код

If LCase(aaa.Name) Like "*учеб*" Then

[/vba]
Пока писал уже ответили

Тогда ещё так
[vba]

Код

If StrConv(aaa.Name, 2) Like "*учеб*" Then

[/vba]

Сообщение отредактировал msi2102 - Вторник, 04.04.2023, 13:59

Ответить

Дата: Вторник, 04.04.2023, 14:02 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 16714

Репутация: 6503 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Ну и сразу на весь модуль игнорирование регистра
[vba]

Код

Option Compare Text

Sub tt()
Application.ScreenUpdating = 0
    With ActiveSheet.PivotTables(1).PivotFields("й")
        .ClearAllFilters
        For Each aaa In .PivotItems
            If aaa.Name Like "*учеб*" Then
                aaa.Visible = False
            Else
                aaa.Visible = True
            End If
        Next aaa
    End With
    Application.ScreenUpdating = 1
End Sub

[/vba]

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Вторник, 04.04.2023, 14:20 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата RAN, 04.04.2023 в 13:53, в сообщении № 7 (

)

не учи плохому

Андрей, для меня это как в Excel с функцией ЕСЛИ()
Если одно-два условия - можно её использовать

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Вторник, 04.04.2023, 15:13 | Сообщение № 11

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

[vba]

Код

If Instr(1, aaa.Name, "*учеб*", vbTextCompare)>0 Then

[/vba]

Быть или не быть, вот в чем загвоздка!

Ответить

Дата: Вторник, 04.04.2023, 16:29 | Сообщение № 12

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

Всем спасибо!
Пока разбирался с этим, вылез еще вопрос - в другом фильтре нужно включить все, что попадает в 3 условия:
1. все до 1500
2. все пустое
3. все без названия

2 условия вроде объединить смог, а вот с 3-мя либо ошибка, либо работает через раз:
[vba]

Код

If aaa.Name <= 1500 Then

                aaa.Visible = 1


                ElseIf aaa.Name = "" Then
                aaa.Visible = 1

                ElseIf aaa.Name = "(blank)" Then
                aaa.Visible = 1

[/vba]

Сообщение отредактировал Artem9222 - Вторник, 04.04.2023, 16:29

Ответить

Дата: Вторник, 04.04.2023, 16:59 | Сообщение № 13

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

Serge_007,
да, это я понимаю. Но как тогда выбрать несколько критериев в фильре?
вариант меньше 1500 работает. Если выбрать исключительно пустые - тоже.
Но соединить 3 в 1 не получается

Ответить

Дата: Вторник, 04.04.2023, 17:22 | Сообщение № 14

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

На макросе _Boroda_:
[vba]

Код

Sub tt()
Application.ScreenUpdating = 0
    With ActiveSheet.PivotTables(1).PivotFields("й")
        .ClearAllFilters
        For Each aaa In .PivotItems
            If aaa.Name = "(blank)" Or aaa.Name <= 1500 Or aaa.Name = " " Then
                aaa.Visible = 1
            Else
                aaa.Visible = 0
            End If
        Next aaa
    End With
    Application.ScreenUpdating = 1
End Sub

[/vba]

К сообщению приложен файл: 20230404_artem9222.xls (40.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Среда, 05.04.2023, 16:51 | Сообщение № 15

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

Цитата Serge_007, 04.04.2023 в 17:22, в сообщении № 14 (

)

If aaa.Name = "(blank)" Or aaa.Name <= 1500 Or aaa.Name = " " Then

Спасибо. На маленьких значениях работает, а вот если вместо 1500 поставить, например, 12000, то выходит ошибка 1004
куда тут можно копать?
ps - уточнение: если в фильтре мало значений и указать например отбор до 12000 то все ок, но если в фильтре куча различных значений, то выскакивает ошибка

Сообщение отредактировал Artem9222 - Среда, 05.04.2023, 17:13

Ответить

Дата: Среда, 05.04.2023, 17:10 | Сообщение № 16

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата Artem9222, 05.04.2023 в 16:51, в сообщении № 15 (

)

если вместо 1500 поставить, например, 12000

Поставил

К сообщению приложен файл: 20230405_artem9222.xls (39.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Среда, 05.04.2023, 17:16 | Сообщение № 17

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

Serge_007,
да, в маленькой табличке все ок - работает, а вот если в фильтре куча значений, то это не срабатывает..
таблицу, к сожалению приложить не могу. слишком много конфиденциальных данных

К сообщению приложен файл: 8208627.png (6.8 Kb)

Ответить

Дата: Среда, 05.04.2023, 17:29 | Сообщение № 18

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата Artem9222, 05.04.2023 в 17:16, в сообщении № 17 (

)

слишком много конфиденциальных данных

Так замените их на яблоки и помидоры

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Среда, 05.04.2023, 17:49 | Сообщение № 19

Группа: Пользователи

Ранг: Новичок

Сообщений: 15

Репутация: 0 ±

Замечаний: 40% ±

Serge_007,
очень странно. создал самую простую таблицу с тем же самым фильтром - на ней все работает. Но на основной, где куча других фильтров - ничего не работает.. выдает ошибку 1004

удалось решить таким путем:
[vba]

Код

If aaa.Name = "(blank)" Or aaa.Name < 12000 Or aaa.Name = "" Then
aaa.Visible = 1
On Error Resume Next

[/vba]
не знаю насколько это корректно, но результат есть

Сообщение отредактировал Artem9222 - Четверг, 06.04.2023, 15:04

Ответить

Настройка фильтров в сводной таблице через макрос (Макросы/Sub)

Страница 1 из 1
1

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!