Сводная таблица. Ошибка при скрытии поля.

Пользователь

Сообщений: 2 Регистрация: 01.01.1970

26.04.2010 14:44:08

MS Office 2003 SP3

Есть таблица с данными по нескольким фирмам.
Столбцы: Фирма, Период, Выручка, Маржа
Добавляю вычисляемое поле "Рентабельность маржи" как =Маржа/Выручка
Перемещаю поле "Рентабельность" в область данных, потом скрываю его. Все работает. Записываю макрос. Потом прогоняю макрос, поле добавляется, но при выполнении операции скрытия
ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _
"Сумма по полю Рентабельность").Orientation = xlHidden
выдается ошибка 1004: Нельзя установить свойство Orientation класса PivotField

При этом любое поле, исходно присутствуеющее в данных, легко показывается и скрывается макросом без проблем. Может, есть какая-то особенность при работе с вычисляемыми полями? вариант пририсовать колонку к исходным данным не катит.

Второй день гуглю интернет, но решения не нашел. Помогите, пожалуйста.
Файл с примером прилагаю.

Заранее благодарен. С уважением, Павел.

Прикрепленные файлы

post_120142.xls (25 КБ)

Ёксель-моксель

Пользователь

Сообщений: 1807 Регистрация: 16.01.2013

27.04.2010 10:22:36

А если ".Orientation" убрать?

ActiveSheet.PivotTables("СводнаяТаблица1").AddDataField ActiveSheet.PivotTables _
("СводнаяТаблица1").PivotFields("Рентабельность"), _
"Сумма по полю Рентабельность", xlSum
Range("A14").Select
ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _
"Сумма по полю Рентабельность") = xlHidden

Удивление есть начало познания © Surprise me!
И да пребудет с нами сила ВПР.

Ёксель-моксель

Гость

27.04.2010 16:07:30

{quote}{login=Ёксель-моксель}{date=27.04.2010 10:22}{thema=}{post}А если ".Orientation" убрать?

ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _
"Сумма по полю Рентабельность") = xlHidden{/post}{/quote}

.Orientation - это одно из свойств (и не является при этом свойством по умолчанию). Если его убрать, а написать так, как Вы привели, то данные из сводной таблицы никуда не денутся, только вместо названия поля будет стоять "0".

А вообще, есть мысли, почему с полями данных скрипт работает, а с вычисляемыми полями - нет? В чем их отличие? Ведь я привел макрос, который Excel сам сгенерировал при записи макроса.

Ведь как-то же это вручную происходит! Я же поле drag-and-drop-ом убираю из таблицы, и оно скрывается. Так же как и людое поле данных. :(

pache

Пользователь

Сообщений: 2 Регистрация: 01.01.1970

28.04.2010 17:15:11

После двух дней ковыряния нашел решение. Правда, оно в стиле Microsoft :)
Я записал еще один макрос, но скрывал поле в таблице не drag-and-drop-ом, а через контекстное меню с помощью команды "Скрыть". И вместо текста

ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _
"Сумма по полю Рентабельность").Orientation = xlHidden

обнаружил в записанном макросе текст

Range("A14").Select
Selection.Delete

то есть производится операция удаления в ячейке, содержащей заголовок поля.

Чтобы было более универсально, я подкорректировал текст так:
ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _
"Сумма по полю Рентабельность").LabelRange.Cells(1, 1).Delete

Ссылка на .Cells(1, 1) необходима, так как при наличии в сводной таблице более одного поля данных, .LabelRange возвращает массив ячеек, в которых присутствует название поля, а несколько ячеек таким образом Excel удалять не позволяет. Если же выбрать первую из них, то все будет Ок.

Вывод: видимо, есть некий глюк в реализации сводных таблиц, связанный с различными механизмами обработки полей данных и вычисляемых полей. И через GUI работа с вычисляемыми полями организована корректно, а через Properties - нет. В Help-е ничего по этому поводу этой особенности не сказано.

Файл с результатом прилагаю.

Прикрепленные файлы

post_120797.xls (31.5 КБ)

aloha_I

Пользователь

Сообщений: 1 Регистрация: 01.01.1970

13.07.2012 13:02:07

Добрый день.

Решение pache у меня не сработало..
Зато нашелся такой простой и изящный, а главное, универсальный способ очистить все поля сводной таблицы, в том числе вычисляемые:

Код

  Dim p As PivotTable   
    Dim f As PivotField   
       
    Set p = ActiveSheet.PivotTables("Pivot1")   
       
    For Each f In p.PivotFields   
        If f.Orientation <> xlHidden Then   
            f.Orientation = xlHidden   
        End If   
    Next f   
       
    For Each f In p.DataFields   
       
        If f.Orientation <> xlHidden Then   
            f.Orientation = xlHidden   
        End If   
    Next f

может быть, кому-нибудь пригодится)

Читают тему