Меню

При обновлении сводной таблицы слетает условное форматирование



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

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

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

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

4 ответа 4

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник

    Условное форматирование в сводных таблицах

    В версиях Excel, предшествующих Excel 2007, условное форматирование позволяло всего лишь динамически изменять цвета или текстовое форматирование значений в ячейках в зависимости от заранее заданных условий. [1] В Excel 2007 средства условного форматирования были значительно расширены возможностями визуализации, включая использование в ячейках гистограмм, цветовых шкал и наборов значков. Эти новые средства позволяют создавать окна в стиле управляющих консолей, позволяющих быстро найти информацию, выделенную самыми различными цветами и оттенками. И что более важно, теперь условное форматирование эффективно применяется в сводных таблицах. В частности, это означает применимость условного форматирования не только к данным, но и к структуре сводной таблицы в целом.

    Читайте также:  Заполните таблицу формулами оснований и названиями групп

    Прочитав эту заметку, вы научитесь применять средства условного форматирования в сводных таблицах, что позволит создавать красочные интерактивные презентации, не требующие применения сводных диаграмм. Начнем с простого примера сводной таблицы (рис. 1). Предположим, требуется в графическом виде получить отчет, который позволил бы менеджерам ознакомиться с объемами продаж, совершенными в каждом временном периоде. Для решения этой задачи можно создать сводную диаграмму либо воспользоваться условным форматированием. В рассматриваемом примере обратимся к упрощенному сценарию и выберем условное форматирование (гистограммы).

    Рис. 1. Исходная сводная таблица

    Рис. 1. Исходная сводная таблица

    Скачать заметку в формате Word или pdf, примеры в формате Excel

    Сначала выделите все ячейки, которые подлежат условному форматированию; в нашем примере С4:С17. Перейдите на вкладку ленты Главная в группу Стили и щелкните на кнопке Условное форматирование (рис. 2).

    Рис. 2. Для значений сводной таблицы выберите условное форматирование в виде гистограммы

    Рис. 2. Для значений сводной таблицы выберите условное форматирование в виде гистограммы

    В области сводной таблицы появятся гистограммы наряду со значениями в поле Сумма по полю Объем продаж2. Чтобы отобразить одну лишь гистограмму, выполните следующие действия.

    1. Щелкните на кнопке раскрывающегося меню Условное форматирование, и выберите пункт Управление правилами.
    2. В диалоговом окне Диспетчер правил условного форматирования выберите только что созданное правило Гистограмма и щелкните на кнопке Изменить правило.
    3. Установите флажок Показывать только столбец (рис. 3).

    Рис. 3. Установите флажок Показывать только столбец, чтобы отображать только гистограммы

    Рис. 3. Установите флажок Показывать только столбец, чтобы отображать только гистограммы

    В ячейки добавляется набор гистограмм, соответствующих хранящимся в них значениям (рис. 4). Немного напоминает горизонтальную гистограмму, не правда ли? Самое удивительное, что при фильтрации данных (например, рынков сбыта), осуществляемой в области ФИЛЬТРЫ, гистограммы динамически обновляются в соответствии с набором выбранных рынков сбыта. Вы можете переименовать заголовки столбцов сводной диаграммы, а также выбрать по своему усмотрению ширину столба Гистограмма.

    Рис. 4. Гистограммы условного форматирования

    Рис. 4. Гистограммы условного форматирования

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

    В следующем списке приведены готовые сценарии условного форматирования:

    • 10 первых элементов;
    • первые 10%;
    • 10 последних элементов;
    • последние 10%;
    • выше среднего;
    • ниже среднего.

    Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная, щелкните на кнопке Условное форматирование и выберите в раскрывающемся меню команду Удалить правилаУдалить правила из этой сводной таблицы (см. рис. 2).

    Создание пользовательских правил условного форматирования

    Следует отметить, что в применении условного форматирования вы не ограничены только заранее разработанными сценариями. Вы всегда можете создать собственные правила. Обратите внимание на исходную таблицу, представленную на рис. 5. В ней добавлено вычисляемое поле, определяющее значение выручки за час (подробнее см. Вычисляемые поля и вычисляемые элементы в Excel 2013).

    Рис. 5. Исходная сводная таблица

    Рис. 5. Исходная сводная таблица

    В этом сценарии мы попытаемся отследить связь между общим объемом продаж и выручкой за час. Основная идея заключается в применении условного форматирования, облегчающего поиск различий и совпадений. Сначала поместите курсор в столбец Объем продаж. Перейдите на вкладку Главная и щелкните на кнопке Условное форматирование. Выберите команду Создать правило. На экране появится диалоговое окно Создание правила форматирования (рис. 6).

    Читайте также:  Сон залог здоровья Учимся спать правильно

    Рис. 6. Диалоговое окно Создание правила форматирования

    Рис. 6. Диалоговое окно Создание правила форматирования; установки по умолчанию

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

    • к выделенным ячейкам;
    • ко всем ячейкам, содержащим значения «Объемпродаж». Условное форматирование применяется ко всем значениям в столбце Объем продаж, а также к промежуточным и общим итогам. Рекомендуется применять этот вариант в ходе анализа данных, для которых определяется среднее, процентное соотношение или другие величины, представляющие различные уровни одной и той же числовой величины.
    • ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта». Условное форматирование применяется ко всем значениям в столбце Объем продаж на уровне Рынок сбыта; при этом исключаются промежуточные и общие итоги. Его лучше всего использовать для анализа отдельных значений.

    Названия Объем продаж и Рынок сбыта диалогового окна Создание правила форматирования изменяются от одной таблицы к другой и отражают названия полей, содержащихся в области столбцов и активных элементов данных.

    В рассматриваемом примере третий вариант кажется наиболее удачным, поэтому установите переключатель ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта» (рис. 7). В разделе Выберите тип правила укажите правило, согласно которому будет применяться условное форматирование:

    • Форматировать все ячейки на основании их значений. Этот переключатель позволяет применять условное форматирование к ячейкам, содержимое которых соответствует заданному диапазону значений. Таким образом, значения в заданном диапазоне сравниваются с уже введенными в ячейках. Лучше всего применять его для определения отклонений в большом наборе данных.
    • Форматировать только ячейки, которые содержат. Данный переключатель применяется для условного форматирования ячеек, удовлетворяющих определенным условиям. Обратите внимание на то, что в этом случае значения в ячейках не сравниваются с другими значениями. Чаще всего используется при сравнении всего набора данных с заранее заданной характеристикой.
    • Форматировать только первые и последние значения. Играет важную роль при применении условного форматирования только к первым или последним нескольким значениям набора данных.
    • Форматировать только значения, которые находятся выше или ниже среднего. Этот переключатель позволяет применять условное форматирование к значениям, большим или меньшим среднего значения, рассчитанного для набора данных.
    • Использовать формулу для определения форматируемых ячеек. В случае выбора этого варианта идентификация значений, к которым применяется условное форматирование, осуществляется согласно пользовательской формуле. Если значение ячейки, подставленное в формулу, приводит к получению результата ИСТИНА, то к такой ячейке применяется условное форматирование. Если же возвращается результат ЛОЖЬ, то условное форматирование к ячейке не применяется.

    Рис. 7. Диалоговое окно Создание правила форматирования; установки пользователя

    Рис. 7. Диалоговое окно Создание правила форматирования; установки по умолчанию

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

    В выбранном нами сценарии будем обозначать проблемные области с помощью набора значков. Поэтому в качестве типа форматирования нужно выбрать параметр Форматировать все ячейки на основании их значений.

    Наконец, нужно определить детальные параметры условного форматирования, для чего используются настройки раздела Измените описание правила. Чтобы добавить в проблемные ячейки значки, выберите в раскрывающемся меню Стиль формата значение Наборы значков. В раскрывающемся списке Стиль значка выберите стиль, применяемый для выполнения анализа. Стиль, заданный на рис. 7, идеально подходит в случаях, когда сводную таблицу невозможно полностью разукрасить разными цветами. В заданной конфигурации настроек программа будет добавлять разные значки, распределяя значения в ячейках по трем следующим категориям: >=67, >=33 и Наталья 20.12.2018 в 20:31

    Скажите, пожалуйста, для сводной таблицы в кубе olap я настроила сводную диаграмму, применила к ней условное форматирование, добавила значения. Ежемесячно в куб добавляется новый месяц с данными и если фильтровать по месяцу, то все мое условное форматирование исчезает. Как настроить так, чтобы оно сохранялось? Excel 1016

    Читайте также:  Криптографические методы защиты данных

    Наталья, сам недавно столкнулся с этой проблемой: в сводной таблице устанавливаю фильтр, и на три столбца накладываю условное форматирование. У меня в поле Применяется к =$J$4:$L$23. Затем устанавливаю новый фильтр, отражаются иные данные, но в том же диапазоне $J$4:$L$23. Условное форматирование «слетает». Захожу повторно в окно условного форматирования, вижу что теперь отражается иной диапазон, и опять выбираю =$J$4:$L$23. Не нашел как побороть 🙁

    Источник

    При обновлении сводной таблицы слетает условное форматирование

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

    то на первый взгляд всё сработает:

    Правило будет выглядеть так

    Однако, если вы обновите сводную таблицу, то форматирование исчезнет! А правило неожиданно станет таким:

    В чём тут дело? Оказывается при создании правила УФ нельзя совместно выделять и обычные ячейки и ячейки сводной таблицы. Встаньте на любую ячейку сводной таблицы из области значений и начните создавать правило УФ:

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

    Обратите внимание как выглядит правило УФ — добавляется иконка сводной таблицы.

    Подобное УФ сохранит свою работоспособность даже, если вы поле Город перетащите из раздела СТРОКИ в раздел КОЛОННЫ сводной таблицы. Вот так:

    Это всё, что я имел сказать про условное форматирование сводных таблиц 🙂

    Источник

    Условное форматирование сводных таблиц

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

    В качестве примера, выбрав значения следующей сводной таблицы применяем к ней классическое цветовое УФ ( Главная -> Условное Форматирование -> Цветовые шкалы -> Цветовая шкала «Зеленый-Желтый-Красный»):

    Если присмотреться, то можно увидеть этот самый неброский элемент для настройки примененного правила форматирования:

    Несмотря на его маленький размер и невзрачность, этот элемент управления очень важен! Именно благодаря ему мы и можем сделать диапазон применения условного форматирования динамичным, то есть таким, чтобы он отслеживал изменения размера сводной таблицы и протягивал правило форматирования в соответствии с новым размещением значений. Это очень важно, так как если применить условное форматирование обычным образом (как я это сделал в скриншоте), условное форматирование применяется к статичному диапазону, который совсем никак не реагирует на изменения сводной таблицы.

    Поэтому, чтобы сделать диапазон динамичным, щелкаем по показанному элементу управления и выбираем одну из указанных ниже опций:

    Настройка «Ко всем ячейкам, содержащим значения «Выручка»» привязывает условное форматирования абсолютно ко всем значениям выручки, в том числе и к общему итогу:

    Последний же вариант применяет условное форматирование лишь к значениям отдельных городов ( что в нашем примера и имеет смысл):

    Теперь, даже если мы меняем размещение полей, Excel всегда знает, к какому именно диапазону следует применять условное форматирование. Вот, например всё та же сводная таблица, только теперь поле «Город» находится в разделе столбцов:

    Вот такая особенность работы УФ со сводными таблицами. При этом, есть еще пара особенностей применения УФ к сводным таблицам, которые имеют множество полей как в разделе строк, так и в разделе столбцов. О них (а также о всём рассказанном выше, но в наглядной форме) я рассказал вот в этом видео, предлагаю его посмотреть:

    Источник

    Adblock
    detector