Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Группировка в PivotTable (сводная таблица) на VBA
 
#1
04.02.2009 12:08:48
В данный момент задача однозначно решается с помощью СТ (база данных на листе valbal), однако на листе TOTAL показан вид таблицы, которая используется для отчетов. Отсюда появляет несколько вопросов:  
1. Если работать дальше с СТ, как наглядно представить данные, т.е. сделать группировку "Валюта в рублях" макросом (сейчас он строит только сводную)  
2. Если работать с привычной таблицой (TOTAL), как заполнить её значениями из СТ (с учетом все той же группировки, т.е. суммы) ?  
 
Пожааалуйста, присоединитесь к моей проблеме =)
 
 
 
#2
04.02.2009 12:54:41
{quote}{login=Branch}{date=04.02.2009 12:08}{thema=Группировка в PivotTable (сводная таблица) на VBA}{post}2. Если работать с привычной таблицой (TOTAL), как заполнить её значениями из СТ (с учетом все той же группировки, т.е. суммы) ?{/post}{/quote}  
функция ВПР
 
 
 
#3
04.02.2009 14:19:48
{quote}{login=Марчук}{date=04.02.2009 12:54}{thema=Re: Группировка в PivotTable (сводная таблица) на VBA}{post}{quote}функция ВПР{/post}{/quote}  
 
спасибо, но не вариант, т.к. в файле достаточно много производиться выборок по формулам до построения сводной;  
 
какой аналог ВПР будет на VBA?
 
 
 
#4
04.02.2009 14:22:14
ВПР в VBA выглядит так: VLookUp  
Application.WorksheetFunction.VLookUp
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#5
04.02.2009 14:28:38
The_Prist, спасибо, попробую покрутить с ней.  
 
Как на VBA выглядит группировка в сводной (PivotTable)?
 
 
 
#6
04.02.2009 14:31:16
По-моему за это отвечает элемент PivotItem, если я правильно Вас понял.  
Воспользуйтесь записью макроса и посмотрите как и при помощи каких методов будут меняться элементы в зависимости от группировки.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#7
04.02.2009 14:41:14
Макрорекодер выдает такое чудо:  
 
Range("B5:C5,E5:J5").Select  
Range("E5").Activate  
Selection.Group  
 
Range("B5").Select  
ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Код вал.2").PivotItems( _  
"Группа1").Caption = "Валюта в рублях"  
Range("B5").Select  
ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Код вал.2").PivotItems( _  
"Валюта в рублях").ShowDetail = False  
 
Т.е., первые три строчки показывают, что я выделяю ячейки, а не объект сводной, поэтому если я пытаюсь вставить также в основной код выплывает ошибка Application-defined or object-defined error !  
 
Как указать что группируются не ячейки, а часть конкретные данные в сводной?
 
 
 
#8
04.02.2009 14:42:23
пардон, слово ЧАСТЬ вычеркнуть из вопроса =)
 
 
 
#9
04.02.2009 14:53:31
Set rngDiap = Range(Cells(1, 1), Cells(lLastRow, 5)) 'Задаем диапазон (обязательно через Set)  
 
   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _  
       rngDiap).CreatePivotTable TableDestination:="", TableName:= _  
       "Моя таблица", DefaultVersion:=xlPivotTableVersion10 'Указываем место размещения и тип сводной таблицы  
   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)  
   ActiveSheet.Cells(3, 1).Select  
   With ActiveSheet.PivotTables("Моя таблица").PivotFields("Договор")  
       .Orientation = xlRowField 'Ориентация поля - по строкам  
       .Position = 1 ' номер позиции - 1  
   End With  
   With ActiveSheet.PivotTables("Моя таблица").PivotFields("Дата")  
       .Orientation = xlRowField 'Ориентация поля - по строкам  
       .Position = 2 ' номер позиции - 2  
   End With  
   With ActiveSheet.PivotTables("Моя таблица").PivotFields("Счет")  
       .Orientation = xlColumnField 'Ориентация поля - по столбцам  
       .Position = 1 ' номер позиции - 1  
   End With  
   ActiveSheet.PivotTables("Моя таблица").AddDataField ActiveSheet.PivotTables _  
       ("Моя таблица").PivotFields("Кредит"), "Сумма по полю Кредит", xlSum ' Задаем вывод информации в поле - Сумма
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#10
04.02.2009 15:00:43
The_Prist,    
из Вашего кода я вижу, что строите сводную таблицу, а где в нем группировка?
 
 
 
#11
04.02.2009 15:05:49
Я выложил просто пример, а не конкретный ответ на вопрос.  
Выложите свой последний вариант файла с кодом, пожалуйста. Посмотрим
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#12
04.02.2009 15:10:36
{quote}{login=The_Prist}{date=04.02.2009 03:05}{thema=}{post}Выложите свой последний вариант файла с кодом, пожалуйста. Посмотрим{/post}{/quote}  
 
рабочий код, который строит нужную мне сводную лежит в файле с 1-м сообщением;там же показано, что должно группироваться (сгруппировано руками)  
 
кода для группировки у меня нет - тот, что дает макрос я уже показала..
 
 
 
#13
04.02.2009 15:36:34
Вот ваш файлик. Исправил.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#14
04.02.2009 16:32:42
The_Prist,  
безмерно благодарю за проявленный альтруизм;  
а сталкивались ли Вы с тем, чтобы итоги по строкам сводной отражались не в конце таблицы, а после каждого соответствующего столбца?  
на эту тему мне приходит в голову мысль только еще раз воспользоваться группировкой и обозвать Итог по ... =)  
в свое время приходилось наталкиваться на перенос итога по столбцам снизу вверх, по-моему на ozgrid.com, но сейчас что-то ничего там не найду
 
 
 
#15
04.02.2009 16:47:55
На самом деле не очень понял как можно Итог по строкам отобразить после каждого столбца...  
Может Вы имеете ввиду Промежуточные Итоги?  
В любом случае поэксперементируйте с Параметрами сводной таблицы -> Итоги и фильтры. Или попробуйте объяснить более подробно, специально для меня тугодума. :-)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#16
04.02.2009 17:08:04
сейчас в файле стоят итоги по строкам в столбцах F:I соответственно  
Итог Входящий сальдо  
Итог Оборот по Д-ту    
Итог Оборот К-ту  
Итог Исходящее сальдо    
 
интересует перемещение этих итогов так:  
 
Входящий сальдо | Итог Входящий сальдо | Оборот по Д-ту | Итог Оборот по Д-ту
 
 
 
#17
05.02.2009 11:48:11
вот незадача, вариант с группировкой не прокатывает, но точно помню, что был код для сводной таблицы в книге Excel Hacks (кажется такая обложка http://www.piter.com/book.phtml?978546900384) - этот код перемещал итоги сводной под "шапку"..  
вдвойне обиднее, что на работе нет возможности эту книгу скачать, а на сайте    
ozgrid.com выложены не все листинги из нее;  
[ч т о д е л а т ь?]
 
 
 
#18
12.02.2009 12:51:38
ответ с перемещением промежуточных итогов в сводной не найден, поэтому пошла другим путем - заполняю на другом листе таблицу значениями, перестраивая порядок столбцов сводной, НО в приложенном коде со строкой    
Set toCopyData = Union(.Cells(i, 1), .Cells(i, 3), .Cells(i, 2), .Cells(i, 10), .Cells(i, 4), .Cells(i, 5), .Cells(i, 11), .Cells(i, 6), .Cells(i, 7), .Cells(i, 12), .Cells(i, 8), .Cells(i, 9), .Cells(i, 13))  
            toCopyData.Copy _  
           Destination:=Sheets("GrandTotal").Cells(Sheets("GrandTotal").Cells(Rows.Count, 2).End(xlUp).Row + 1, 2)  
 
у меня не получается:  
1. начать заполнение с 1-го столбца (А4), т.к. в шапке присутствует объединение ячеек;  
2. указывая весь порядок столбцов, переноситься порядок сводной, а не указанный, при этом кусек .Cells(i, 3), .Cells(i, 2), .Cells(i, 10) работает    
3. заполнение идет с первой пустой строчки, а как указать предварительную очистку не знаю.  
 
Окажите посильную помощью, пожалуйста =)
 
 
 
#19
12.02.2009 12:52:43
oops, что-то файл не приклеилсо...
 
 
 
#20
12.02.2009 12:53:54
а вот и ОН, видимо все дело в размере..
 
 
 
#21
12.02.2009 14:06:06
дико извиняюсь, что-то я погорячилась с архивированием -    
спешу исправиться и приклеиваю 114Кбайт моих мучений
 
 
 
#22
12.02.2009 19:55:18
Выкладываю, что получилось - интересует мнение по оптимизации заполнения и как всё-таки вставить очищение заполненного диапазона с А4, т.к. сейчас данные вставляются на лист TOTAL с первой пустой строки.
 
 
 
#23
12.02.2009 22:03:28
А Вы не пробовали очищать лист от данных перед копированием?  
Можно так с расчетом границ.  
 
           With bazaWb.Sheets("TOTAL")  
               .Range(.Cells(4, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, Columns.Count).End(xlToLeft).Column)).Cells.Clear  
           End With  
А если у Вас таблица стандартная то проще  
 
bazaWb.Sheets("TOTAL").range("A4:P14").Clear
 
 
 
#24
12.02.2009 23:18:29
{quote}{login=Igor67}{date=12.02.2009 10:03}{thema=}{post}А Вы не пробовали очищать лист от данных перед копированием?  
Можно так с расчетом границ.  
 
           With bazaWb.Sheets("TOTAL")  
               .Range(.Cells(4, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, Columns.Count).End(xlToLeft).Column)).Cells.Clear  
           End With  
А если у Вас таблица стандартная то проще  
 
bazaWb.Sheets("TOTAL").range("A4:P14").Clear{/post}{/quote}  
 
о как,  
а я чистила руками через Правку, и еще последную строчку начинала с pvtSht3, а не bazaWb =)[краснею]
судя повсему пока таблица должна быть стандартная, но как видите, Ваш код сводной начал кочевать в разные файлы, чувствую, что и вторая строчка скоро пригодится..
 
 
 
#25
13.02.2009 02:47:58
Может быть не надо каждый раз строить сводную таблицу заново. Проще назначать существующей таблице новый диапазон данных. Лист со сводной таблицей лучше скрыть.  
В приложении именно так и сделано. При переходе на лист GrandTotal обновляется диапазон данных сводной таблицы, затем обновляется сама сводная таблица, очищается от данных лист GrandTotal и на него   копируются данные из сводной таблицы. Код содержится в модуле листа GrandTotal и модуле Module2.
 
 
 
#26
23.02.2009 23:26:33
LAD, лист сводной должен отображаться из-за производственной необходимости, тем не менее Ваш код элегантен, а комментарии доходчивы - спасибо, что не прошли мимо =)
 
 
 
#27
24.02.2009 02:33:54
Спасибо за комплементы, но все-таки, пожалуйста, объясните свою задачу подробнее. Непонятно что нужно выбирать и по какому принципу.
 
 
 
Страницы: 1
Читают тему
Наверх