Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Настройка фильтров в сводной таблице через макрос
Artem9222 Дата: Среда, 29.03.2023, 09:35 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 40% ±

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

Автор - Artem9222
Дата добавления - 29.03.2023 в 09:35
kotlovan Дата: Среда, 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
 
Ответить
СообщениеДобрый день, я не профи в макросах, вот так я у себя обновлял фильтры, может подойдет?

[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:15
Artem9222 Дата: Среда, 29.03.2023, 15:25 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 40% ±

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


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

Автор - Artem9222
Дата добавления - 29.03.2023 в 15:25
_Boroda_ Дата: Среда, 29.03.2023, 15:49 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

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
 
Ответить
СообщениеЕсли одно условие, то можно использовать фильтр "Не содержит"
Запишите макрорекодером, получите вот так примерно
[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_
Дата добавления - 29.03.2023 в 15:49
Artem9222 Дата: Вторник, 04.04.2023, 11:37 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 40% ±

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

Автор - Artem9222
Дата добавления - 04.04.2023 в 11:37
Serge_007 Дата: Вторник, 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
 
Ответить
СообщениеArtem9222, замените строку[vba]
Код
If aaa.Name Like "*учеб*" Then
[/vba]на[vba]
Код
If aaa.Name Like "*учеб*" Or aaa.Name Like "*УЧЕБ*" Then
[/vba]

Автор - Serge_007
Дата добавления - 04.04.2023 в 12:15
RAN Дата: Вторник, 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]
:)


Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеSerge_007, не учи плохому
[vba]
Код
If Lcase(aaa.Name) Like "*учеб*" Then
[/vba]
или
[vba]
Код
If Ucase(aaa.Name ) Like "*УЧЕБ*" Then
[/vba]
:)

Автор - RAN
Дата добавления - 04.04.2023 в 13:53
msi2102 Дата: Вторник, 04.04.2023, 13:56 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 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
 
Ответить
СообщениеМожно ещё так попробовать
[vba]
Код
If LCase(aaa.Name) Like "*учеб*" Then
[/vba]
Пока писал уже ответили :)
Тогда ещё так
[vba]
Код
If StrConv(aaa.Name, 2) Like "*учеб*" Then
[/vba]

Автор - msi2102
Дата добавления - 04.04.2023 в 13:56
_Boroda_ Дата: Вторник, 04.04.2023, 14:02 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

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
 
Ответить
СообщениеНу и сразу на весь модуль игнорирование регистра
[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_
Дата добавления - 04.04.2023 в 14:02
Serge_007 Дата: Вторник, 04.04.2023, 14:20 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

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


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
не учи плохому
Андрей, для меня это как в Excel с функцией ЕСЛИ()
Если одно-два условия - можно её использовать :)

Автор - Serge_007
Дата добавления - 04.04.2023 в 14:20
RAN Дата: Вторник, 04.04.2023, 15:13 | Сообщение № 11
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
[vba]
Код
If Instr(1, aaa.Name, "*учеб*", vbTextCompare)>0 Then
[/vba]
:)


Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение[vba]
Код
If Instr(1, aaa.Name, "*учеб*", vbTextCompare)>0 Then
[/vba]
:)

Автор - RAN
Дата добавления - 04.04.2023 в 15:13
Artem9222 Дата: Вторник, 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
 
Ответить
СообщениеВсем спасибо!
Пока разбирался с этим, вылез еще вопрос - в другом фильтре нужно включить все, что попадает в 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
Artem9222 Дата: Вторник, 04.04.2023, 16:59 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 40% ±

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

Автор - Artem9222
Дата добавления - 04.04.2023 в 16:59
Serge_007 Дата: Вторник, 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
 
Ответить
СообщениеНа макросе _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]

Автор - Serge_007
Дата добавления - 04.04.2023 в 17:22
Artem9222 Дата: Среда, 05.04.2023, 16:51 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 40% ±

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

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


Сообщение отредактировал Artem9222 - Среда, 05.04.2023, 17:13
 
Ответить
Сообщение
If aaa.Name = "(blank)" Or aaa.Name <= 1500 Or aaa.Name = " " Then

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

Автор - Artem9222
Дата добавления - 05.04.2023 в 16:51
Serge_007 Дата: Среда, 05.04.2023, 17:10 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
если вместо 1500 поставить, например, 12000
Поставил
К сообщению приложен файл: 20230405_artem9222.xls (39.0 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
если вместо 1500 поставить, например, 12000
Поставил

Автор - Serge_007
Дата добавления - 05.04.2023 в 17:10
Artem9222 Дата: Среда, 05.04.2023, 17:16 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 40% ±

Serge_007,
да, в маленькой табличке все ок - работает, а вот если в фильтре куча значений, то это не срабатывает..
таблицу, к сожалению приложить не могу. слишком много конфиденциальных данных
К сообщению приложен файл: 8208627.png (6.8 Kb)
 
Ответить
СообщениеSerge_007,
да, в маленькой табличке все ок - работает, а вот если в фильтре куча значений, то это не срабатывает..
таблицу, к сожалению приложить не могу. слишком много конфиденциальных данных

Автор - Artem9222
Дата добавления - 05.04.2023 в 17:16
Serge_007 Дата: Среда, 05.04.2023, 17:29 | Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
слишком много конфиденциальных данных
Так замените их на яблоки и помидоры


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
слишком много конфиденциальных данных
Так замените их на яблоки и помидоры

Автор - Serge_007
Дата добавления - 05.04.2023 в 17:29
Artem9222 Дата: Среда, 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
 
Ответить
Сообщение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
Дата добавления - 05.04.2023 в 17:49
  • Страница 1 из 1
  • 1
Поиск:

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