Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Настройка форматов данных в сводной таблице
 
#1
09.12.2011 12:18:22
Добрый день! Не затруднит ли кого-нибудь из вас объяснить как работает макрос предложенный в приемах: http://planetaexcel.ru/tip.php?aid=226  
т.к. возникли трудности уже с самого начала - "Осталось установить активную ячейку в любое место вашей сводной таблицы и запустить макрос через диалоговое окно" (что за активная ячейка? откуда ее взять и как установить в сводную?) Да и макрос, я так понимаю - универсальный, нужно же менять что то под свою сводную, так? Поскольку прочесть, а тем более понять макрос я могу с таким же успехом, если бы пытался читать русско-народные сказки на санскрите, пожалуйста, если вас не затруднит, объясните как этот макрос прописать под свою таблицу :)  
Буду очень благодарен. не судите строго.
 
 
 
#2
09.12.2011 12:28:47
{quote}{login=Алексей_kuf}{date=09.12.2011 12:18}{thema=Настройка форматов данных в сводной таблице}{post}что за активная ячейка? откуда ее взять{/post}{/quote}:-) Здравствуйте! Активной будет ТА ячейка, в которую Вы ткнёте мыхой :-)
 
 
 
#3
09.12.2011 12:37:22
{quote}{login=Юрий М}{date=09.12.2011 12:28}{thema=Re: Настройка форматов данных в сводной таблице}{post}{quote}{login=Алексей_kuf}{date=09.12.2011 12:18}{thema=Настройка форматов данных в сводной таблице}{post}что за активная ячейка? откуда ее взять{/post}{/quote}:-) Здравствуйте! Активной будет ТА ячейка, в которую Вы ткнёте мыхой :-){/post}{/quote}  
 
Ок, тыкаю на любую ячейку в сводной - затем запускаю макрос - в итоге ошибка "Сначала установите активную ячейку в сводную таблицу."
 
 
 
#4
09.12.2011 12:41:33
А сводная у вас действительно СВОДНАЯ?
 
 
 
#5
09.12.2011 12:48:46
{quote}{login=}{date=09.12.2011 12:41}{thema=}{post}А сводная у вас действительно СВОДНАЯ?{/post}{/quote}  
 
Да :) создавал с помощью подключения данных (данные из другой книги), но не используется только области "фильтр отчета" и "названия строк", т.к. нужно только подгружать фиксированные данные без каких либо математических действий. Т.е. по сути сводная выполняет роль фильтра огромной базы.
 
 
 
#6
09.12.2011 12:49:41
{quote}{login=Алексей_kuf}{date=09.12.2011 12:48}{thema=Re: }{post}{quote}{login=}{date=09.12.2011 12:41}{thema=}{post}А сводная у вас действительно СВОДНАЯ?{/post}{/quote}  
 
Да :) создавал с помощью подключения данных (данные из другой книги), но *ИСПОЛЬЗУЮТСЯ* только области "фильтр отчета" и "названия строк", т.к. нужно только подгружать фиксированные данные без каких либо математических действий. Т.е. по сути сводная выполняет роль фильтра огромной базы.{/post}{/quote}
 
 
 
#7
09.12.2011 12:50:58
Да :) создавал с помощью подключения данных (данные из другой книги), но *ИСПОЛЬЗУЮТСЯ* только области "фильтр отчета" и "названия строк", т.к. нужно только подгружать фиксированные данные без каких либо математических действий. Т.е. по сути сводная выполняет роль фильтра огромной базы.
 
 
 
#8
12.12.2011 10:35:03
Подскажите, пожалуйста!! Как правильно расписать этот макрос под свою сводную?
 
 
 
#9
12.12.2011 10:50:11
{quote}{login=Александр_kuf}{date=12.12.2011 10:35}{thema=}{post}Подскажите, пожалуйста!! Как правильно расписать этот макрос под свою сводную?{/post}{/quote}  
Файл со сводной (по правилам) -  в студию!
 
 
 
#10
12.12.2011 11:38:53
Пожалуйста - файл. Макрос вставлен - формат не меняется, но ошибок не выдает.
 
 
 
#11
12.12.2011 12:30:32
Здравствуйте  
Данный безусловно замечательный макрос не работает у Вас потому что его работа предполагает форматирование данных в сводной таблице только в области данных. У Вас в примере все данные находятся в области строк и столбцов.  
Немного преобразовал Ваш файл, чтобы можно было увидеть работу макроса - запускайте.
 
 
 
#12
12.12.2011 12:31:41
{quote}{login=Александр_kuf}{date=12.12.2011 11:38}{thema=}{post}Макрос вставлен - формат не меняется, но ошибок не выдает.{/post}{/quote}Макрос работает корректно.  
А что Вы от него ждёте, собственно? В области значений у Вас пусто, менять форматы негде...
 
 
 
#13
12.12.2011 12:47:48
{quote}{login=Serge 007}{date=12.12.2011 12:31}{thema=Re: }{post}{quote}{login=Александр_kuf}{date=12.12.2011 11:38}{thema=}{post}Макрос вставлен - формат не меняется, но ошибок не выдает.{/post}{/quote}Макрос работает корректно.  
А что Вы от него ждёте, собственно? В области значений у Вас пусто, менять форматы негде...{/post}{/quote}  
 
Так в том то и дело, что у меня в сводной используются только фильтр отчета и область строк. Можно ли как-нибудь преобразовать, чтобы формат менялся в область строк?
 
 
 
#14
12.12.2011 13:18:45
{quote}{login=VDM}{date=12.12.2011 12:30}{thema=}{post}Здравствуйте  
Данный безусловно замечательный макрос не работает у Вас потому что его работа предполагает форматирование данных в сводной таблице только в области данных. У Вас в примере все данные находятся в области строк и столбцов.  
Немного преобразовал Ваш файл, чтобы можно было увидеть работу макроса - запускайте.{/post}{/quote}  
 
А тоже самое, но для области строк он может делать? У меня сводная играет роль своеобразного фильтра огромной базы. Данные просто подгужаются, не производя ни каких математических действий (как это предполагает область значений)
 
 
 
#15
12.12.2011 16:06:23
Вряд ли стоит отпираться на данный макрос, он на то и универсальный, что решает КОНКРЕТНУЮ задачу, для любых сводных, но именно для области значений.  
У Вас судя по всему другие потребности.  
И кстати говоря из Вашего примера так и не понятно, в соответствии с чем, или каким образом должен поменяться формат в области строк?  
 
Покажите какие данные, и как должны быть отформатированы (что было и что надо)
 
 
 
#16
12.12.2011 16:49:13
{quote}{login=VDM}{date=12.12.2011 04:06}{thema=}{post}Вряд ли стоит отпираться на данный макрос, он на то и универсальный, что решает КОНКРЕТНУЮ задачу, для любых сводных, но именно для области значений.  
У Вас судя по всему другие потребности.  
И кстати говоря из Вашего примера так и не понятно, в соответствии с чем, или каким образом должен поменяться формат в области строк?  
 
Покажите какие данные, и как должны быть отформатированы (что было и что надо){/post}{/quote}  
 
Проблема состоит в том, что при обновлении (в настоящем файле) каждый раз меняется формат в тех ячейках (области строк), где подгружаются числа, при чем каждый раз по-разному (где-то ставится 2 знака после запятой, где-то 5). Нужно, чтобы был один стандартный формат (2 знака после запятой), иногда, если ячека содержат даты - в сводной отбражаются просто числы. Если не затруднит, подскажите, как в коде этого макроса идентифицировать область строк,а так же те строки, которые требуют форматирования (язык VBA совершенно не знаю)  
 
Прикрепленный файл не отображает все то, что есть в исходном файле. (где то нужно 2 знака после запятой, где то 4, где то процентный формат с 4 - мя знаками после запятой)  
 
Заранее благодарю, если кто нибудь объяснит, что значит каждая строчка в коде и что в ней нужно изменять под свою сводную (название таблицы, названия строк и т.д.)
 
 
 
#17
13.12.2011 00:33:06
Попробуйте, не подойдёт?  
Сводную нужно обновить.
 
 
 
#18
13.12.2011 00:37:51
В первом примере с разрядностью напутал, вот так:
 
 
 
#19
13.12.2011 10:22:08
{quote}{login=VDM}{date=13.12.2011 12:37}{thema=}{post}В первом примере с разрядностью напутал, вот так:{/post}{/quote}  
 
Супер! Спасибо большое! Все так, как надо!
 
 
 
#20
13.12.2011 10:26:40
{quote}{login=VDM}{date=13.12.2011 12:37}{thema=}{post}В первом примере с разрядностью напутал, вот так:{/post}{/quote}  
 
Черт... Возник глюк, на файле стоит автоматическое обновление, т.е. при открытии идет запрос на подключение данных, происходит обновление и в макросе возникает ошибка: "Невозможно получить свойство PivotTable класса Range" :(  
Но если, потом вручную опять обновить - все впорядке, форматы выставляются согласно макросу. Как можно исправить этот глюк?
 
 
 
#21
13.12.2011 11:02:55
Так же иногда возникает этот же глюк при работе со сводной. Выбираю, выставляю к примеру нужный месяц, число - идет вормирование таблицы, затем ошибка. При повторном действии - все впорядке, потом опять..
 
 
 
#22
13.12.2011 12:12:15
Код по форматированию я вставил в событие рабочего листа "обновление данных сводной таблицы".  
Я так понимаю ошибка возникает из-за того, что сама сводная в момент обновления не всегда активна.  
 
В модуле листа со сводной таблицей, в самом коде  
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)  
 
ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).PivotSelect "цена[All]", xlLabelOnly, True
Selection.NumberFormat = "#,##0.00"  
End Sub  
 
Попробуйте заменить здесь  
(ActiveCell.PivotTable.Name)  
на  
("НАЗВАНИЕ_СВОДНОЙ_ТАБЛИЦЫ"), в Вашем текущем примере это ("СводнаяТаблица4")
 
 
 
#23
13.12.2011 14:35:05
{quote}{login=VDM}{date=13.12.2011 12:12}{thema=}{post}Код по форматированию я вставил в событие рабочего листа "обновление данных сводной таблицы".  
Я так понимаю ошибка возникает из-за того, что сама сводная в момент обновления не всегда активна.  
 
В модуле листа со сводной таблицей, в самом коде  
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)  
 
ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).PivotSelect "цена[All]", xlLabelOnly, True
Selection.NumberFormat = "#,##0.00"  
End Sub  
 
Попробуйте заменить здесь  
(ActiveCell.PivotTable.Name)  
на  
("НАЗВАНИЕ_СВОДНОЙ_ТАБЛИЦЫ"), в Вашем текущем примере это ("СводнаяТаблица4"){/post}{/quote}  
 
Прописал, но все равно ошибка. Проблема, я думаю в том, что в файле есть три сводных на трех разных листах. Написал макрос для каждой. Я так понимаю, что обновляет (и выставляет формат) только на активном листе. А чтобы макрос работал и для не активных листов? Извиняюсь за надоедливость :) просто совсем чуть-чуть осталось для идеала :)
 
 
 
#24
13.12.2011 15:57:47
Точно! Обновляет формат только на активном листе. Я так понимаю, что нужно изменить "ActiveSheet.PivotTables" на что то, что могло походить по всем листам, включая неактивные! Но вот что это за команда?
 
 
 
#25
13.12.2011 17:07:36
Решить проблему захотел следующим способом. Решил отключить автоматическое обновление при открытии файла. А затем на каждый лист книги повесил кнопку "Обновить" с кодом:    
Sub Refresh()  
   ActiveSheet.PivotTables("СводнаяТаблица1").PivotCache.Refresh  
End Sub  
 
Надеясь, что при взаимодействии с вашим кодом, сейчас он имеет такой вид:  
 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)  
ActiveSheet.PivotTables("СводнаяТаблица1").PivotSelect "price[All]", xlLabelOnly, True
Selection.NumberFormat = "#,##0.0000"  
ActiveSheet.PivotTables("СводнаяТаблица1").PivotSelect "fees[All]", xlLabelOnly, True
Selection.NumberFormat = "#,##0.0000"  
ActiveSheet.PivotTables("СводнаяТаблица1").PivotSelect "Cost[All]", xlLabelOnly, True
Selection.NumberFormat = "#,##0.00"  
ActiveSheet.PivotTables("СводнаяТаблица1").PivotSelect "Cost_[All]", xlLabelOnly, True
Selection.NumberFormat = "#,##0.0000"  
End Sub  
 
Все будет супер! НО коварный Ексель опять строит козни :) Выдается ошибка: "Application-defined or object-defined error". Нажимаю END и (О,чудо!) таблица обновляется с выставлением нужных форматов. Но КАК теперь убрать эту ошибку. Или можно как-нибудь совместить ваш код и мой код и повесить его на кнопку?  
 
P.S. Все это делается для пользователей, которые хотят открыть файл и увидеть то, что они хотят видеть, а я вот тут гемороюсь :(. Под конец рабочего дня попросили, чтобы сводная обновлялась на текущую дату!!! О,Боже!
 
 
 
#26
13.12.2011 17:09:28
Напишите мне макрос, чтобы я мог повеситься в Excel'е :)
 
 
 
#27
13.12.2011 17:33:24
Ошибку выдает с выделением строки    
ActiveSheet.PivotTables("СводнаяТаблица1").PivotCache.Refresh
 
 
 
#28
13.12.2011 17:52:07
Спасибо огромное за столь масштабную помощь! Но все же хочется понять, как избежать этих конфликтов...  
Для точности скажу конкретно что у меня за файлы.  
Два файла - файл с базой (.xls) и файл со сводными (.xls). В файле со сводными - три листа, на каждом из которых сводная таблица, данные подгружаются путем подключения к базе. Объединить в один лист не позволяет поставленное задание от начальства. Ваш код работает, я так понял, для активного листа. Мой код почему то выдает ошибку...
 
 
 
#29
13.12.2011 18:15:22
Ну Вы тут понаписали ! :)  
Попробуйте указывать так:  
Обратите внимание, что для листа "БЫЛО" имеем:  
Sheets("БЫЛО").PivotTables("СводнаяТаблица4").PivotSelect "цена[All]", xlLabelOnly, True
А для лита "НАДО"  
Sheets("НАДО").PivotTables("СводнаяТаблица5").PivotSelect "цена[All]", xlLabelOnly, True
 
В вашем примере от 13.12.2011, 17:07 , как раз ошибка - Вы обращаетесь 4 раза к одной и той же таблице  
 
И давайте без уважаемых, неловко как то, я к слову сказать сам только только учусь и очевидно что далеко не оптимальные решения предлагаю.
 
 
 
#30
13.12.2011 19:44:45
"Под конец рабочего дня попросили, чтобы сводная обновлялась на текущую дату!!! О,Боже!"  
Это как, при открытии книги? Если так, о можно использовать событие рабочей книги:  
"Workbook_Open()"  
 
Кстати, Вы ко всем сводным данные из одной книги тяните, может тогда в ней поработать, добавить столбец, где числовые данные с разной разрядностью будут округляться до нужной разрядности, или преобразовываться в текст с нужным количеством знаков после запятой и по нему уже строить все сводные?
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх