Форматирование диаграммы Excel потеряно при обновлении всех или отдельных данных. Щелкните правой кнопкой мыши на Data> Refresh.

Question

У меня есть 4 сводные диаграммы, которые основаны на данных, которые обновляются из соединения.

Когда я нажимаю обновить все, я теряю все настройки, которые я установил (цвета / границы / линия и полоса и выбор 2-й оси)

• Я уже снял флажок Properties Follow Chart Data Point for Current Workbook .
• Я также попытался щелкнуть правой кнопкой мыши Данные> Обновить для каждой таблицы данных, но у меня возникла та же проблема.
• Preserve cell formatting on update отмечен для всех диаграмм.
• Invert if negative option отмеченный / не отмеченный отрицательный параметр не имеет значения
• Preserve cell formatting on update Я попытался убрать пометку, затем все в порядке, затем щелкнуть правой кнопкой мыши параметры и снова поставить галочку, все еще не работает ..
• Я сохранил формат диаграммы в качестве шаблона, а затем после обновления применяется, но форматирование все еще теряется.

Версия:

Excel 2016 MSO (16.0.4738.1000) 32-bit

Answer 1

Чтобы сохранить форматирование при обновлении сводной таблицы, выполните следующие действия:

1. Выберите любую ячейку в сводной таблице и щелкните правой кнопкой мыши.

2. Затем выберите « Параметры сводной таблицы» в контекстном меню.

3. В диалоговом окне « Параметры сводной таблицы» перейдите на вкладку « Макет и формат».
4. Затем установите флажок Сохранить форматирование ячейки на элементе обновления в разделе « Формат ».
5. Закончите с OK, чтобы закрыть.

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

Отредактировано 1:

Вы можете попробовать это:

Инвертировать, если отрицательная опция должна быть проверена для опций сводной диаграммы.

Или вы можете написать этот код VBA в Immediate Window.

Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection(1).InvertIfNegative = True

Примечание: лист, диаграмма и номер серии доступны для редактирования.

Отредактировано 2

Другая возможность

1. Выберите область печати, щелкните правой кнопкой мыши и выберите команду « Сохранить как шаблон».

Всякий раз, когда вы теряете формат диаграммы, доходите до Excel, выберите файл Выберите график.

2. Щелкните правой кнопкой мыши и выберите « Изменить тип диаграммы».

3. Выберите шаблон из всплывающего меню типа диаграммы.

Вы найдете все эти потерянные форматы на выбранной карте, примененной ранее.

NB

Вышеуказанный процесс может быть реализован через VBA (Macro) на графике или на всех графиках.

Answer 2

@Matt - так же, как и вы, ни одно из приведенных выше решений не помогло мне. Что еще более расстраивало, так это то, что у меня было две сводные диаграммы / таблицы в одном файле, оба связаны с одной и той же моделью данных Power Pivot, одна сохраняла свое пользовательское форматирование, а другая - нет. Таким образом, я знал, что это вряд ли связано с моей версией Excel или конкретной ошибкой.

Статья, на которую ссылается harrymc, содержит ключ к решению этой проблемы, но без использования обходного пути - это XML. Я собирался сравнить XML для двух сводных диаграмм, но потом подумал, что здесь может работать иерархия форматирования.

Мое решение:

1. Удалите все зависимые сводные диаграммы (вы начинаете с нуля)
2. Удалите ВСЕ слайсеры и удалите ВСЕ фильтры из сводной таблицы.
3. Убедитесь, что установлен флажок «Сохранить форматирование ячейки при обновлении» (это не решит проблему напрямую, но кажется важным)
4. Добавьте новую сводную диаграмму, но НЕ фильтруйте и не разрезайте данные каким-либо образом, независимо от того, насколько плохо диаграмма может выглядеть на этом этапе.
5. Примените пользовательское форматирование.
6. Сохраните файл (пользователь на другом форуме предложил выйти и перезапустить Excel - что я и сделал в отчаянии!)
7. Теперь добавьте фильтры / слайсеры, чтобы создать нужный график.

Затем я смог обновить / нарезать / отфильтровать данные, и все форматы пользовательских диаграмм были сохранены.

Answer 3

В статье Изменения форматирования сводной диаграммы при фильтрации подробно рассматривается предмет.

Это объясняет, что Excel фактически сохраняет данные форматирования в кеше со всеми другими свойствами диаграммы. Это означает, что он запоминает точное форматирование. Когда данные обновляются, Excel лишает законной силы этот кэш, так что применяется форматирование по умолчанию для диаграммы.

Он предлагает решение, в котором создается новая область на рабочем листе, содержащая реплику сводной таблицы, содержащую формулы, ссылающиеся на сводную таблицу, с использованием либо прямых ссылок на ячейки, таких как (= C9), либо функции GETPIVOTDATA (), указывающей на сводную таблицу. , Функция GETPIVOTDATA предпочтительна для отображения подмножества данных сводной таблицы на диаграмме.

Это делается в два этапа.

Шаг 1

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

Шаг 2

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

Заключение

Статья заканчивается:

Добавление слайсеров в ваши сводные диаграммы и сводные таблицы - отличный способ сделать вашу презентацию интерактивной. Сводные диаграммы позволяют вам связать вашу диаграмму с источником данных, чтобы они могли обновляться динамически с минимальным обслуживанием. Однако PivotCharts отображает странное поведение при фильтрации диаграмм с пользовательским форматированием. Понимание этого поведения и планирование его во время разработки сэкономит ваше время и разочарование.

Пример презентации можно скачать с
Изменения форматирования сводной диаграммы в фильтре Slicer.xlsx.

Автор также рекомендует статью Dynamic Chart с использованием Pivot Table и VBA с подходом использования VBA для создания динамических диаграмм с использованием более продвинутого подхода (слишком длинный, чтобы включать его здесь).

Answer 4

Лучшее решение этой повторяющейся ошибки: выберите всю сводную таблицу -> перейдите к параметру «Анализ» на ленточной панели -> раздел «Действия» -> нажмите «Выбрать» -> всю сводную таблицу. Теперь выполните любое необходимое форматирование и сохраните лист, когда закончите, используйте обновление. Это сработало абсолютно правильно для меня.

Надеюсь, поможет.