ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Универсальный макрос для создания PivotTable
18 сообщений из 18, страница 1 из 1
Универсальный макрос для создания PivotTable
    #37831407
rus_sun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rus_sun
Участник
Создаю 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:23
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37831473
TpaBka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TpaBka
Участник
Код: vbnet
1.
2.
SourceData:= _
        "InTotal!R1C1:R" & Worksheets("InWeek").UsedRange.Rows.Count & "C" & Worksheets("InWeek").UsedRange.Columns.Count
...
Рейтинг: 0 / 0
08.06.2012, 16:46
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37831577
rus_sun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rus_sun
Участник
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
08.06.2012, 17:23
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37832383
TpaBka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TpaBka
Участник
Код: 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
09.06.2012, 11:00
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37836164
rus_sun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rus_sun
Участник
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, 12:31
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37836479
TpaBka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TpaBka
Участник
Код: 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, 15:24
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37836572
rus_sun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rus_sun
Участник
TpaBka,

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

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

В excel (после 2007) вид-> …… (см. выше)
...
Рейтинг: 0 / 0
13.06.2012, 16:34
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37843966
rus_sun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rus_sun
Участник
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, 11:55
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37844053
TpaBka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TpaBka
Участник
Выложи свой файлик
Ща посмотрим
...
Рейтинг: 0 / 0
19.06.2012, 12:23
| Ответить | Цитировать | Написать  
Универсальный макрос для создания PivotTable
    #37844136
TpaBka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TpaBka
Участник
Ладно, попробуй в редакторе VBA на событие:
Код: vbnet
1.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

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

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

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

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

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

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

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


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