Форум программистов «Весельчак У»
  *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

  • Рекомендуем проверить настройки временной зоны в вашем профиле (страница "Внешний вид форума", пункт "Часовой пояс:").
  • У нас больше нет рассылок. Если вам приходят письма от наших бывших рассылок mail.ru и subscribe.ru, то знайте, что это не мы рассылаем.
   Начало  
Наши сайты
Помощь Поиск Календарь Почта Войти Регистрация  
 
Страниц: [1]   Вниз
  Печать  
Автор Тема: макрос для сводной таблицы в excel  (Прочитано 30269 раз)
0 Пользователей и 1 Гость смотрят эту тему.
Olegspb
Гость
« : 12-12-2005 11:32 » 

Пытаюсь написать макрос, который создавал бы сводную таблицу на основе таблицы данных. Проблема в том, что границы таблицы данных  в макросе получаются фиксированными, а ведь они будут меняться. Как сделать, чтобы границы получались автоматически? На данный момент получается следующее:
Sub СводнаяТаблица()
       ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Лист1 '!R1C1:R1680C5").CreatePivotTable TableDestination:="", TableName:= _
        "СводнаяТаблица1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("СводнаяТаблица1").SmallGrid = False
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Кредитор")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Имя поставщика")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Сумма")
        .Orientation = xlDataField
        .Position = 1
    End With
End Sub
« Последнее редактирование: 19-12-2007 19:22 от Алексей1153++ » Записан
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #1 : 14-12-2005 14:33 » 

как автоматически не знаю, но можно менять источник руками
примерно так
Код:
Public Sub change_source()
    Dim pv As PivotTable
   
    Set pv = ActiveSheet.PivotTables("СводнаяТаблица1")
   
    pv.PivotCache.SourceData = "Лист1!R1C1:R10C5"
    pv.PivotCache.Refresh
End Sub
Записан

I Have Nine Lives You Have One Only
THINK!
Olegspb
Гость
« Ответ #2 : 16-12-2005 06:42 » 

Спасибо за совет, но, как я понимаю, данный макрос обновляет значения в сводной таблице, а не границы исходной. Меня же интересует обновление именно границ исходной таблицы. Например, если добавится количество строк в таблице,макрос должен это увеличение откорректировать. Подскажите возможно ли это в принципе.
Записан
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #3 : 16-12-2005 06:56 » 

а что вы понимаете под термином "граница"?
Записан

I Have Nine Lives You Have One Only
THINK!
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #4 : 16-12-2005 08:57 » 

Olegspb если я правильно понял, то пусть диапозон данных имел значения
Код:
Лист1!R1C1:R10C5
, потом в данные добавили еще 15 строк, тогда получаем
Код:
     Dim pv As PivotTable
   
    Set pv = ActiveSheet.PivotTables("СводнаяТаблица1")
   
    pv.PivotCache.SourceData = "Лист1!R1C1:R25C5"
    pv.PivotCache.Refresh

попробуйте
Записан

I Have Nine Lives You Have One Only
THINK!
Olegspb
Гость
« Ответ #5 : 16-12-2005 14:37 » 

Вы правильно поняли Отлично, но для ясности обрисую ситуацию подробнее. По работе приходится из корпоративной информационной системы выкачивать в Excel определенную информацию и затем обрабатывать с помощью сводных таблиц. Процедура в основном всегда одинаковая, единственное что всегда меняется, так это объем данных, т.е. количество строк в таблице. Я понимаю, что в макросе можно просто изменить диапазон в ручную, но как раз этого и хотелось бы избежать. :?
Записан
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #6 : 16-12-2005 15:41 » 

Вот после изменения объема данных и вызывайте данную процедуру
только останеться одна проблема определить диапозон, но и это можно разрешить

Как часто и почему(отчего) меняются данные
Записан

I Have Nine Lives You Have One Only
THINK!
RomCom
Опытный

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #7 : 16-12-2005 15:56 » 

Наверно примерно так:
Код:
pv.PivotCache.SourceData = "Лист1!A1:" & ActiveCell.SpecialCells(xlLastCell).Address
« Последнее редактирование: 16-12-2005 15:58 от RomCom » Записан

R.O.M.C.O.M.: Robotic Operational Mathematics and Ceaseless Observation Machine
Olegspb
Гость
« Ответ #8 : 19-12-2005 14:31 » 

Стою на освальте в лыжи обутый. То ли лыжи не едут, то ли я ...ый. Не получается ничего.
To HandKot
Данные в информационной системе обновляются постоянно. Я периодически делаю отчеты для своей службы, поэтому объем данных всегда разный. Из-за этого придется всегда корректировать адрес диапазона. Молчу Что есть Элементарное КЮ.
(С)Кин-дза-дза
Записан
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #9 : 19-12-2005 15:02 » 

Цитата
Что есть Элементарное КЮ.
в том то и дело, что элементарное Улыбаюсь

я так и не понял как у вас происходит работа с листом Excell, но можно написать примерно так
исходные данные:
1) есть некий лист
2) есть запрос или некая программа, которые формируют данные для сводной таблицы

варианты решения:
1) после того, как обновили запрос ( сам-то он никогда не заработает, надо пинать его), запустить макрос, который и изменяет диапозон данных для сводной таблицы
2) либо в событиях листа обрабатывать изменение дитапозона данных для сводной таблицы (может притормаживать, если изменения в листе происходят часто)

Цитата
Я периодически делаю отчеты для своей службы, поэтому объем данных всегда разный
вот коли вы делаете отчет для своей службы, то после (или до) точно не скажу и меняйте макросом источник данных (очень неплохую идею RomCom предложил)
Цитата
Цитата
Записан

I Have Nine Lives You Have One Only
THINK!
Olegspb
Гость
« Ответ #10 : 20-12-2005 08:16 » 

to HandKot
Идею RomCom понимаю и вроде бы все правильно, но выдается ошибка:"Недопустимое имя поля сводной таблицы ...". Хотя все заданно правильно.
Записан
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #11 : 20-12-2005 10:59 » 

Попробуйте в окне дебагера выполнить
Код:
? "Лист1!A1:" & ActiveCell.SpecialCells(xlLastCell).Address
и посмотрите что получиться
а по ошибке похоже что в диапозон данным попал столбец без названия, хотя не уверен
Записан

I Have Nine Lives You Have One Only
THINK!
RomCom
Опытный

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #12 : 21-12-2005 15:18 » 

Попробуй так:
Код:
"Лист1!A1:" & Worksheets("Лист1").Cells.SpecialCells(xlLastCell).Address
Записан

R.O.M.C.O.M.: Robotic Operational Mathematics and Ceaseless Observation Machine
Olegspb
Гость
« Ответ #13 : 26-12-2005 07:25 » 

To HandKot and RomCom
Большое спасибо за помощь. Макрос работает. Отлично
Записан
Olegspb
Гость
« Ответ #14 : 28-12-2005 08:51 » 

Нашел еще один способ:

Worksheets("Лист1").Activate
selection.CurrentRegion.Select
numRows = selection.Rows.Count
numColumns = selection.Columns.Count
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Лист1!A1:" & Cells(numRows, numColumns).Address).CreatePivotTable TableDestination:="", TableName:= _
        "СводнаяТаблица1"
и т.д.

Может кому-нибудь понадобится. :razz:
Записан
Страниц: [1]   Вверх
  Печать  
 

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines