Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Универсальный макрос для создания PivotTable / 18 сообщений из 18, страница 1 из 1
08.06.2012, 16:23
    #37831407
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
Создаю PivotTable, который на основе таблицы в Листе "InWeek", записывается в новом листе.
Но в таблице в Листе "InWeek" может быть переменное количество строк. Как мне избавиться от R1C1:R767209C22, и сделать так, чтобы просто бралась вся таблица целиком из Листа "InWeek".

Код: vbnet
1.
2.
3.
4.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "InTotal!R1C1:R767209C22", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=ActiveSheet.Cells(32, 1), TableName:="PivotTable5", DefaultVersion _
        :=xlPivotTableVersion12
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2012, 16:46
    #37831473
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
Код: vbnet
1.
2.
SourceData:= _
        "InTotal!R1C1:R" & Worksheets("InWeek").UsedRange.Rows.Count & "C" & Worksheets("InWeek").UsedRange.Columns.Count
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2012, 17:23
    #37831577
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka,

Спасибо огромное,
пользуясь случаем, раз уж напал на специалиста, хочу спросить.

Мне надо в готовом PivotTable подкрасить values, если значение > 20 - зеленым. Как получить значение этих values. Возможно к ним можно как-то подобраться через RowLabels.
Пока просто сделал селект и закрасил:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
ActiveSheet.PivotTables("PivotTable5").PivotSelect _
        "'город по таблице 1'[unknown,Башкортостан,Екатеринбург,'Краснодарский край',Москва:'Нижегородская обл.',Новосибирск,'Санкт-Петербург',Татарстан,'Челябинская обл.']" _
        , xlDataAndLabel + xlFirstRow, True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 6750054
        .TintAndShade = 0
        .PatternTintAndShade = 0



В PivotTable это выглядит вот так:

Москва 165 76
Санкт-Петербург 96 54
Московская обл. 54 30
Краснодарский край 35 14
Нижегородская обл. 34 19

Цифры больше 20 закрашены зеленым
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2012, 11:00
    #37832383
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable5") 'правильно не ActiveSheet а Worksheets("имя листа или индекс").PivotTables("имя табл. или индекс") 

pt.DataBodyRange.FormatConditions.Delete
pt.DataBodyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="20"
pt.DataBodyRange.FormatConditions(1).Interior.ColorIndex = 4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2012, 12:31
    #37836164
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka,

огромное спасибо за помощь.

Единственно, подкрасилось не все, что мне надо.

Вот моя Pivot-таблица из трех столбцов

Код: vbnet
1.
2.
3.
4.
5.
6.
"город" "звонков" "заказов"
Москва 165 76
Санкт-Петербург 96 54
Московская обл. 54 30
Краснодарский край 35 14
Нижегородская обл. 34 19



Мне надо, если в столбце "звонков" значение ячейки больше 20, чтобы подкрашивалась вся соответствующая строка, т.е. все три колонки. А не только ячейка, в которой значение больше 20.

Очень жду ответа, заранее спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2012, 15:24
    #37836479
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable5") 'правильно не ActiveSheet а Worksheets("имя листа или индекс").PivotTables("имя табл. или индекс")


For i = 3 To pt.TableRange1.Rows.Count
   'i=3 -первые 2 строки в сводной таб. это заголовки
    
    pt.TableRange1.Rows(i).FormatConditions.Delete
    pt.TableRange1.Rows(i).FormatConditions.Add Type:=xlExpression, Formula1:="=" & pt.TableRange1(i, 2).Address & ">20"
    pt.TableRange1.Rows(i).FormatConditions(1).Interior.ColorIndex = 4

    'pt.TableRange1(i, 2).Address & ">20" - "2" это номер колонки сводной таб. в которой выполняется условие

Next

З.Ы. ты хоть понял что такое FormatConditions?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2012, 16:17
    #37836572
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka,

спасибо огромное, благодаря Вам у меня теперь полностью автоматизированный отчет.
Что такое FormatConditions, ну полагаю здесь название говорящее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2012, 16:21
    #37836582
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka,

еще маленький вопрос, если ColorIndex = 4 это зеленый, то какой ColorIndex у желтого и красного. Я так понимаю, это какие-то встроенные цвета. Потому что зеленный как раз того оттенка, который мне нужен
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2012, 16:34
    #37836611
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
В excel (до 2007) заходишь -> сервис -> макросы -> начать запись
Берешь, заливаешь ячейки разными цветами, останавливаешь запись
заходишь -> сервис -> макросы -> макрос1-> изменить

В excel (после 2007) вид-> …… (см. выше)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2012, 11:55
    #37843966
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka, добрый день,

огромное спасибо за помощь. Написал вот такой код и все подкрасилось верно:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("PivotTable5") 


    For i = 3 To pt.TableRange1.Rows.Count - 1   
    
    pt.TableRange1.Rows(i).FormatConditions.Delete
    pt.TableRange1.Rows(i).FormatConditions.Add Type:=xlExpression, Formula1:="=" & pt.TableRange1(i, 2).Address & ">20"
    pt.TableRange1.Rows(i).FormatConditions(1).Interior.ColorIndex = 4
    
    pt.TableRange1.Rows(i).FormatConditions.Add Type:=xlExpression, Formula1:="=" & pt.TableRange1(i, 2).Address & ">=10"
    pt.TableRange1.Rows(i).FormatConditions(2).Interior.Color = 10092543
    
    pt.TableRange1.Rows(i).FormatConditions.Add Type:=xlExpression, Formula1:="=" & pt.TableRange1(i, 2).Address & "<10"
    pt.TableRange1.Rows(i).FormatConditions(3).Interior.Color = 10079487    
    

    Next



Строки больше 20 зеленым, больше равно 10 желтым, остальные красным.
Но когда, используя фильтр, начинаешь работать с таблицей. Правильно подкрашивается только первый столбец, остальные два становятся без подкраски. Плюс, остаются подкрашеными строки нижу Пивота, на том месте где он раньше был.
Не подскажите, как от этого избавится?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2012, 12:23
    #37844053
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
Выложи свой файлик
Ща посмотрим
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2012, 13:03
    #37844136
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
Ладно, попробуй в редакторе VBA на событие:
Код: vbnet
1.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

повесить процедуру покраски
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2012, 10:34
    #37845803
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka,

Файл с кодом приложил
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2012, 10:38
    #37845814
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka,

авторповесить процедуру покраски

Worksheet_PivotTableUpdate(ByVal Target As PivotTable) - это стандартная процедура или ее надо написать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2012, 10:54
    #37845857
QValD
QValD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBkaЛадно, попробуй в редакторе VBA на событие :

rus_sunэто стандартная процедура или ее надо написать?
Это стандартное событие , которое нужно обработать. Бегом в магазин за книжкой по ООП
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2012, 12:11
    #37846078
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
rus_sunTpaBka,

Файл с кодом приложилФайла екселевского нету
Мне код не нужен, мне надо твоя таблица чтобы было на чем потестить
rus_sunWorksheet_PivotTableUpdate(ByVal Target As PivotTable) - это стандартная процедура или ее надо написать?
В экселе нажимаешь ALT+F11
Слева в верхнем окне кликнеш по тому листу, где находится твоя таблица
Затем в большом правом окне где пишутся процедуры сверху есть 2 поля сосписком
Нажимаешь на первое и вместо GENERAL выбираешь Worksheet
Затем во втором выбираешь в списке PivotTableUpdate
У тебя появляется процедура Worksheet_PivotTableUpdate(ByVal Target As PivotTable) вот сюда и вставляешь вот это:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
   Dim pt As PivotTable
    Set pt = Target


    For i = 3 To pt.TableRange1.Rows.Count - 1
   
    
    pt.TableRange1.Rows(i).FormatConditions.Delete
    pt.TableRange1.Rows(i).FormatConditions.Add Type:=xlExpression, Formula1:="=" & pt.TableRange1(i, 2).Address & ">20"
    pt.TableRange1.Rows(i).FormatConditions(1).Interior.ColorIndex = 4
    
    pt.TableRange1.Rows(i).FormatConditions.Add Type:=xlExpression, Formula1:="=" & pt.TableRange1(i, 2).Address & ">=10"
    pt.TableRange1.Rows(i).FormatConditions(2).Interior.Color = 10092543
    
    pt.TableRange1.Rows(i).FormatConditions.Add Type:=xlExpression, Formula1:="=" & pt.TableRange1(i, 2).Address & "<10"
    pt.TableRange1.Rows(i).FormatConditions(3).Interior.Color = 10079487
    
    
    Next
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2012, 14:21
    #37846427
rus_sun
rus_sun
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBka,

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

Вот мой excel
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2012, 15:40
    #37846664
TpaBka
TpaBka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Универсальный макрос для создания PivotTable
TpaBkaВ экселе нажимаешь ALT+F11
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Универсальный макрос для создания PivotTable / 18 сообщений из 18, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 499ms
Закрыть
start [/forum/topic.php?fid=60&mobile=1&tid=2157663]:
 0ms
get settings:
 10ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 64ms
get tp. blocked users:
 2ms
others: 345ms
total:  499ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]