Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
VBA: фильтр сводной таблицы
 
#1
04.12.2011 01:12:02
Уважаемые знатоки, помогите решить проблему, уже сломал себе мозг.  
Задача: отфильтровать в сводной таблице данные с датой менее 15.12.2011  
 
Использую код:  
 
Dim j As Integer  
Sheets(1).Select  
With ActiveSheet.PivotTables("summary").PivotFields("дата")  
       For j = 1 To .PivotItems.Count  
           If .PivotItems(j).Value < #12/15/2011# Then  
               .PivotItems(j).Visible = False  
               Else  
               .PivotItems(j).Visible = True  
           End If  
       Next j  
End With  
 
Вылетает с ошибкой 1004: "Нельзя установить свойство Visible для класса PivotItem"  
 
Причем этот же код нормально срабатывает на других сводных таблицах. Подскажите, в чем дело, и в какую сторону копать?  
 
Заранее спасибо!
 
 
 
#2
04.12.2011 01:14:23
Сорри, файл не прикрепился
 
 
 
#3
04.12.2011 13:30:13
Вот пример, где этот же самый код срабатывает на "ура".  
 
Почему же он не работает в предыдущем примере???
 
 
 
#4
04.12.2011 14:37:42
глюкнутая у Вас сводная, создайте новую
Спасибо
 
 
 
#5
04.12.2011 21:34:42
{quote}{login=R Dmitry}{date=04.12.2011 02:37}{thema=}{post}глюкнутая у Вас сводная, создайте новую{/post}{/quote}  
Для тестового примера это сработало, а вот для рабочего файла, почему-то нет. Полностью удалял лист со сводной таблицей и создавал заново, безрезультатно :-( Причем ошибка возникает исключительно с полем даты, на других полях работает без проблем.    
Опытным путем выяснил, что ошибка выдается на всех датах, кроме 12.12.2011. Есть у кого-нибудь идеи, что это может быть?
 
 
 
#6
04.12.2011 21:47:01
Я не практически не знаю VBA, но как-то решал подобную задачу, правда в 2010. Проблема в несовпадении формата дат, которые воспринимает VBA, и пишите Вы. Я могу поискать файлы, если хотите, но они для 2010 (должны работать и в 2003, и в 2007, но не все).
 
 
 
#7
04.12.2011 23:22:14
Здравствуйте! Михаил С. Вы мне когда то помогли с этим!  проблема решилась так: копируем единичку выделяем диапазон с датами нажимаем специальная вставка - умножить! Все!    
проверяйте!  
 
PS: я правда добавил строку On Error Resume Next, т.к. отсутствует одна дата в сводной, написано (пусто) и выдавало ошибку!
 
 
 
#8
04.12.2011 23:54:20
Ну если бы к items обращались бы по имени, я бы еще поверил что дело в формате, а так обращаемся по индексу, и сам цикл отрабатывает правильно, поэтому дело скорее всего не в дате.
Спасибо
 
 
 
#9
05.12.2011 00:14:20
{quote}{login=R Dmitry}{date=04.12.2011 11:54}{thema=}{post}Ну если бы к items обращались бы по имени, я бы еще поверил что дело в формате, а так обращаемся по индексу, и сам цикл отрабатывает правильно, поэтому дело скорее всего не в дате.{/post}{/quote}  
Согласен с Вами, условия он все отрабатывает верно, но почему-то просто не может присвоить свойство для определенного поля сводной таблицы. Что-то ему не нравится в данном конкретном поле, но вот что?
 
 
 
#10
05.12.2011 00:29:15
списываем на очередной баг мелкософта :))
Спасибо
 
 
 
#11
05.12.2011 00:45:15
{quote}{login=R Dmitry}{date=05.12.2011 12:29}{thema=}{post}списываем на очередной баг мелкософта :)){/post}{/quote}  
Списать я не против :) Вот только обойти это как-то нужно, проект стоит из-за этой, казалось бы, мелочи. Может есть еще какие-нибудь способы отфильтровать сводную таблицу с помощью VBA?
 
 
 
#12
05.12.2011 00:47:59
а мой вариант разве не работает?
 
 
 
#13
05.12.2011 00:52:23
Sub Макрос1()  
 ОбновлятьФильтрыСводнойТаблицы  '<-- Достаточно свыполнить один раз  
 Dim i As Long  
 With Sheets(1).PivotTables("summary").PivotFields("дата")  
   For i = 1 To .PivotItems.Count  
     With .PivotItems(i)  
       If IsDate(.Value) Then  
         .Visible = .Value >= #12/15/2011#  
       End If  
     End With  
   Next  
 End With  
End Sub  
 
 
' См. http://www.planetaexcel.ru/forum.php?thread_id=2748  
Sub ОбновлятьФильтрыСводнойТаблицы()  
 Dim x  
 For Each x In ActiveWorkbook.PivotCaches  
   x.MissingItemsLimit = xlMissingItemsNone  
   x.Refresh  
 Next  
End Sub
 
 
 
#14
05.12.2011 00:56:02
А чтобы не мигало:  
 
Sub Макрос1()  
 Application.ScreenUpdating = False  
 ОбновлятьФильтрыСводнойТаблицы  '<-- Достаточно выполнить один раз  
 Dim i As Long  
 With Sheets(1).PivotTables("summary").PivotFields("дата")  
   For i = 1 To .PivotItems.Count  
     With .PivotItems(i)  
       If IsDate(.Value) Then  
         .Visible = .Value >= #12/15/2011#  
       End If  
     End With  
   Next  
 End With  
 Application.ScreenUpdating = True  
End Sub
 
 
 
#15
05.12.2011 01:14:37
Уж, поскольку нашел, покажу и свое решение.    
Вот тема,  
http://www.excelworld.ru/forum/2-793-1    
где решается подобный вопрос, но только чисто для 2010. Мне кажется, что самый первый файл подойдет и к более ранним версиям - проверить не могу, т.к. все их (и 2007, и 2003) снес, надоели.  
На всякий случай перевел этот файл в формат 2003.
 
 
 
#16
05.12.2011 01:15:19
{quote}{login=ZVI}{date=05.12.2011 12:56}{thema=}{post}А чтобы не мигало:  
 
Sub Макрос1()  
 Application.ScreenUpdating = False  
 ОбновлятьФильтрыСводнойТаблицы  '<-- Достаточно выполнить один раз  
 Dim i As Long  
 With Sheets(1).PivotTables("summary").PivotFields("дата")  
   For i = 1 To .PivotItems.Count  
     With .PivotItems(i)  
       If IsDate(.Value) Then  
         .Visible = .Value >= #12/15/2011#  
       End If  
     End With  
   Next  
 End With  
 Application.ScreenUpdating = True  
End Sub{/post}{/quote}  
 
Выдает ту же ошибку :(((((((((((((((
 
 
 
#17
05.12.2011 01:16:31
{quote}{login=AKSENOV048}{date=05.12.2011 12:47}{thema=}{post}а мой вариант разве не работает?{/post}{/quote}  
 
К сожалению, нет :(    
Попробуйте, файл постом выше.
 
 
 
#18
05.12.2011 01:21:24
Для того, чтобы программно установить фильтр поля сводной таблицы, необходимо сначала установить параметр сортировки поля = "вручную (разрешается перетаскивание)", а в конце восстановить сортировку поля. Этот факт малоизвестен.  
При тестировании я исправил этот параметр вручную, поэтому приведенный выше код сработал корректно.  
 
Чтобы все устанавливалось автоматически, нужен такой вариант кода:  
 
Sub Макрос1()  
 Dim i As Long, SortOrder, SortField  
 Application.ScreenUpdating = False  
 ОбновлятьФильтрыСводнойТаблицы  '<-- Достаточно выполнить один раз  
 With Sheets(1).PivotTables("summary")  
   .ManualUpdate = True  
   With .PivotFields("дата")  
     SortOrder = .AutoSortOrder  
     SortField = .AutoSortField  
     If SortOrder <> xlManual Then .AutoSort xlManual, SortField  
     For i = 1 To .PivotItems.Count  
       With .PivotItems(i)  
         If IsDate(.Value) Then  
           .Visible = .Value >= #12/15/2011#  
         End If  
       End With  
     Next  
     If SortOrder <> xlManual Then .AutoSort SortOrder, SortField  
   End With  
   .ManualUpdate = False  
 End With  
 Application.ScreenUpdating = True  
End Sub  
 
' См. http://www.planetaexcel.ru/forum.php?thread_id=2748  
Sub ОбновлятьФильтрыСводнойТаблицы()  
 Dim x  
 For Each x In ActiveWorkbook.PivotCaches  
   x.MissingItemsLimit = xlMissingItemsNone  
   x.Refresh  
 Next  
End Sub
 
 
 
#19
05.12.2011 01:33:06
{quote}{login=ZVI}{date=05.12.2011 01:21}{thema=}{post}Для того, чтобы программно установить фильтр поля сводной таблицы, необходимо сначала установить параметр сортировки поля = "вручную (разрешается перетаскивание)", а в конце восстановить сортировку поля. Этот факт малоизвестен.  
При тестировании я исправил этот параметр вручную, поэтому приведенный выше код сработал корректно.  
 
Чтобы все устанавливалось автоматически, нужен такой вариант кода:  
 
Sub Макрос1()  
 Dim i As Long, SortOrder, SortField  
 Application.ScreenUpdating = False  
 ОбновлятьФильтрыСводнойТаблицы  '<-- Достаточно выполнить один раз  
 With Sheets(1).PivotTables("summary")  
   .ManualUpdate = True  
   With .PivotFields("дата")  
     SortOrder = .AutoSortOrder  
     SortField = .AutoSortField  
     If SortOrder <> xlManual Then .AutoSort xlManual, SortField  
     For i = 1 To .PivotItems.Count  
       With .PivotItems(i)  
         If IsDate(.Value) Then  
           .Visible = .Value >= #12/15/2011#  
         End If  
       End With  
     Next  
     If SortOrder <> xlManual Then .AutoSort SortOrder, SortField  
   End With  
   .ManualUpdate = False  
 End With  
 Application.ScreenUpdating = True  
End Sub  
 
' См. http://www.planetaexcel.ru/forum.php?thread_id=2748  
Sub ОбновлятьФильтрыСводнойТаблицы()  
 Dim x  
 For Each x In ActiveWorkbook.PivotCaches  
   x.MissingItemsLimit = xlMissingItemsNone  
   x.Refresh  
 Next  
End Sub{/post}{/quote}  
 
Та же ошибка, что я делаю не так?  
P.S. На всякий случай: Excel 2010 SP1
 
 
 
#20
05.12.2011 01:34:25
{quote}{login=Darkman}{date=05.12.2011 01:16}{thema=Re: }{post}{quote}{login=AKSENOV048}{date=05.12.2011 12:47}{thema=}{post}а мой вариант разве не работает?{/post}{/quote}  
 
К сожалению, нет :(    
Попробуйте, файл постом выше.{/post}{/quote}  
 
пробуйте!
 
 
 
#21
05.12.2011 02:00:29
{quote}{login=AKSENOV048}{date=05.12.2011 01:34}{thema=Re: Re: }{post}  
 
пробуйте!{/post}{/quote}  
Сработало,  а у меня почему-то не получилось, хотя через спецвставку и умножал на 1. Опишите алгоритм Ваших действий. Хотя смысл данного преобразования мне, в данном конкретном случае, и не совсем понятен.
 
 
 
#22
05.12.2011 02:05:41
{quote}{login=Михаил С.}{date=05.12.2011 01:14}{thema=}{post}Уж, поскольку нашел, покажу и свое решение.    
Вот тема,  
http://www.excelworld.ru/forum/2-793-1    
где решается подобный вопрос, но только чисто для 2010. Мне кажется, что самый первый файл подойдет и к более ранним версиям - проверить не могу, т.к. все их (и 2007, и 2003) снес, надоели.  
На всякий случай перевел этот файл в формат 2003.{/post}{/quote}  
Спасибо, интересное решение, возьму на вооружение.
 
 
 
#23
05.12.2011 07:19:46
{quote}{login=Darkman}{date=05.12.2011 01:33}{thema=Re: }{post}P.S. На всякий случай: Excel 2010 SP1{/post}{/quote}  
Файл и сводная таблица были в формате Excel 2003, в этой версии все работает.  
Начиная с Excel 2007 сводные таблицы претерпели значительные изменения.  
Но при работе с полями даты, содержащими пустые ячейки, появились проблемы с программным скрытием/отображением элементов.  
 
Для исключения проблем лучше всего указывать динамический диапазон данных с исключением пустых ячеек в поле даты.  
 
Приложил исправленный таким образом файл из post_284932.zip  
Код немного обновлен.
 
 
 
#24
06.12.2011 08:13:59
{quote}{login=ZVI}{date=05.12.2011 07:19}{thema=}{post}{quote}{login=Darkman}{date=05.12.2011 01:33}{thema=Re: }{post}P.S. На всякий случай: Excel 2010 SP1{/post}{/quote}  
Файл и сводная таблица были в формате Excel 2003, в этой версии все работает.  
Начиная с Excel 2007 сводные таблицы претерпели значительные изменения.  
Но при работе с полями даты, содержащими пустые ячейки, появились проблемы с программным скрытием/отображением элементов.  
 
Для исключения проблем лучше всего указывать динамический диапазон данных с исключением пустых ячеек в поле даты.  
 
Приложил исправленный таким образом файл из post_284932.zip  
Код немного обновлен.{/post}{/quote}  
Действительно, в Excel 2003 работает без проблем, проблемы начинаются только с 2007. Спасибо Вам!
 
 
 
#25
06.12.2011 09:27:35
{quote}{login=Darkman}{date=06.12.2011 08:13}{thema=Re: }{post}Действительно, в Excel 2003 работает без проблем, проблемы начинаются только с 2007. Спасибо Вам!{/post}{/quote}  
Рад был помочь :-)  
 
Файл в post_284949.zip работает в Excel 2003, 2007, 2010.  
 
Признаком того, что с полем даты все в порядке служит наличие кнопки "Число" для Excel версии 2003, или "Числовой формат" для 2007/2010 при нажатии правой кнопки на поле даты сводной таблицы и выборе "Параметры поля".  
 
Обычно рекомендуют в данных, на основе которых строится сводная таблица, вместо формата даты устанавливать общий формат или формат чисел. Но это неудобно ни в данных, ни в фильтре сводной, где вместо дат будут числа.  
 
Еще одна популярная рекомендация - в качестве данных использовать списки (2003) или таблицы (2007/2010). Но это тоже не удобно, потому что таблицы, созданные в 2007/2010 не работают в 2003.  
 
А причина, как я уже указал, в пустых ячейках в столбце дат, если диапазон указан "с запасом". Достаточно ограничить диапазон динамически, чтобы решить проблему. В post_284949.zip это динамический диапазон с именем БД, на основе которого построена сводная таблица. При вводе новых данных диапазон автоматически расширится.
 
 
 
Страницы: 1
Читают тему
Loading...