Как сохранить форматирование в сводной диаграмме Excel 2010?

Question

У меня есть некоторый код VBA, который запускает SQL-запрос, выгружает его в электронную таблицу Excel 2010 и обновляет 2 сводные таблицы на основе данных. У меня также есть 2 сводные диаграммы, основанные на сводных таблицах, и каждый раз, когда таблицы обновляются, форматирование на диаграммах теряется.

Я следовал инструкциям в Разделе 12.3 этой книги, в которых предлагалось создать собственный тип диаграммы на основе моих предпочтений форматирования и применить его к диаграммам, которые я хочу сохранить таким образом. Эти инструкции прекрасно работают, если я вручную изменяю данные, а затем вручную обновляю сводную таблицу - диаграммы остаются правильно отформатированными. Тем не менее, когда я выполняю этот код:

For Each pt In NewSheet.PivotTables
  pt.RefreshTable
Next

форматирование сразу теряется.

Вот как выглядят данные раньше:

и мои тщательно составленные круговые диаграммы:

Как только я обновлю данные и обновлю таблицы:

Вот что я получаю:

Опять же, применение стиля пользовательской диаграммы сохранит форматирование, если оно выполнено вручную, но выполнение кода VBA каким-то образом сбрасывает тип пользовательской диаграммы.

Answer 1

Чтобы достичь этого без использования сводных диаграмм, все, что вам нужно, - это динамический именованный диапазон и простая (если грубо отформатированная) диаграмма.

1) Создайте определенное имя, используя эту формулу: Series_A=OFFSET(Sheet!B1,0,0,COUNT(A:A),1) , где - Sheet1!B1 относится к вашей первой ячейке подсчета в сводной таблице (или любой другой ссылке, если вам удобнее использовать OFFSET) - Count (A: A) - это ваш столбец с метками строк

2) Создайте свои диаграммы с сериями = Series_A (и любыми другими, которые вам нужны).

При обновлении сводных таблиц определенные имена будут ссылаться на значения расширения / сжатия в столбце меток строк и возвращать только значения в столбце счетчика. Нет необходимости в 0, # н / д или вспомогательных столбцах.