Excel условное форматирование если ошибка

Обновлено: 07.07.2024

Средство проверки совместимости обнаружило одну или несколько проблем совместимости, связанных с условным форматированием.

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

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

В этой статье

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

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

Что это означает. Начиная с Excel 2007 г., правила условного форматирования могут ссылаться на значения на других таблицах. Эти правила не поддерживаются в более ранних версиях и будут потеряны при Excel 97–2003.

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

Что это означает. Новые условные форматы были Excel 2007, включая наборы значков. Формат Excel 97–2003 не поддерживает правила условного форматирования, в которые используются наборы значков. Если сохранить файл в этом формате, условное форматирование для ячеев, о которые идет речь, будет отменено в сохраненном файле.

Число условных форматов в некоторых ячейках превышает поддерживаемое выбранным форматом файла. В более ранних версиях Excel будут отображаться только первые три условия.

Что это означает. В Excel 2007 г. и более поздних гг. условное форматирование может содержать до 64 условий, но в Excel 97–2003 будут видеться только три первых условия.

Однако все правила условного форматирования остаются доступными в книге и применяются при повторном ее повторном Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти , чтобы найти ячейки, к которым применено условное форматирование с использованием более трех условий, и уменьшите число условий до трех.

Некоторые ячейки относятся сразу к нескольким диапазонам условного форматирования. В более ранних версиях Excel к таким ячейкам будут применены не все правила условного форматирования. Для таких ячеек будет использоваться разное условное форматирование.

Что это означает. В Excel 97–2003 перекрытие диапазонов условного форматирования не поддерживается, и условное форматирование не отображается должным образом.

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

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

Что это означает. В Excel 97–2003 для определения формата ячейки не используются такие типы условного форматирования, как гистограммы, цветовые шкалы, наборы значков, первые или последние значения, значения выше или ниже среднего, уникальные или повторяющиеся значения и сравнения столбцов таблицы.

Действия В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки с новыми типами условного форматирования в Excel 2007 и более поздних версиях, а затем внести необходимые изменения, чтобы использовать только те типы форматирования, которые поддерживаются в более ранних Excel.

Некоторые ячейки содержат условное форматирование со снятым параметром Остановить, если истина. Более ранним версиям Excel этот параметр не известен, поэтому выполнение будет остановлено после первого истинного условия.

Что это означает. В Excel 97–2003 условное форматирование без остановки при выполнении условия не является параметром. Условное форматирование больше не применяется после первого истинного условия.

Одна или несколько ячеек в этой книге содержат условное форматирование для несвязанного диапазона (например, первые/последние N, первые/последние N%, выше/ниже среднего или выше/ниже стандартного отклонения). Такое условное форматирование не поддерживается более ранними версиями Excel.

Что это означает. В Excel 97–2003 в несмежных ячейках условное форматирование не отображается.

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

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

Что это означает. Результаты условного форматирования, которые вы видите в отчетах Excel 97–2003, будут не одинаковыми, как в отчетах Excel 2007 и более поздних отчетов.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти поля отчета сводных таблиц, содержащие правила условного форматирования, и примените правила условного форматирования, доступные в предыдущих версиях Excel.

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

Что это означает. В Excel 97–2003 условное форматирование, ссылаясь на значения на других таблицах, не отображается.

Однако все правила условного форматирования остаются доступными в книге и применяются при повторном ее повторном Excel 2010 г. или более поздней, если они не были изменены в Excel 97–2007.

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

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

Что это означает. В Excel 97–2007 г. условное форматирование, в которое используются формулы для текста с правилами, не отображается на этом сайте.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки с условным форматированием, в котором используются формулы для текста с правилами, и примените условное форматирование, которое поддерживается в предыдущих версиях Excel.

Одна или несколько ячеек книги содержат правило, которое не будет поддерживаться в более ранних версиях Excel, так как в его диапазоне есть ошибка в формуле.

Что это означает. В Excel 97–2007 г. условное форматирование, в котором используются правила на основе диапазонов, не может правильно отображаться на экране, если правила на основе диапазона содержат ошибки в формулах.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие правила на основе диапазонов с ошибками в формулах, и устраните ошибки.

Одна или несколько ячеек книги содержат размещение набора значков условного форматирования, которое не поддерживается в более ранних версиях Excel.

Что это означает. В Excel 97–2007 условное форматирование с определенным набором значков не поддерживается, а набор значков не отображается на экране.

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

Одна или несколько ячеек книги содержат правило гистограммы, в котором используется параметр "Отрицательное значение". Такие гистограммы не поддерживаются в более ранних версиях Excel.

Что это означает. В Excel 97–2007 г. условное форматирование с правилом грабки данных, использующим отрицательное значение, не отображается на сайте.

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

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

Что это означает. В Excel 97–2007 г. условное форматирование, ссылаясь на более чем 8192 неотрывных области ячеек, не отображается на этом сайте.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки с условным форматированием, ссылающимся на более чем 8192 несмежных диапазона ячеек, и измените число несмежных диапазонов ячеек, на которые ссылается условное форматирование.

Проблемы, которые приводят к небольшой потере точности

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

Что это означает. В Excel 97–2007 г. условное форматирование с правилом грабки данных, которое использует сплошную цветную заливку или границу либо параметры направления г рубленых полос слева направо и справа налево для г. не отображаются на этом сайте.

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

Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный - зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких - серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя - зеленым. И так далее - насколько фантазии хватит.

Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат - Условное форматирование (Format - Conditional formatting) .

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

Кнопка А также>> (Add) позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях - бесконечно.

Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить (Delete) в нижней части окна.

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

Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать - другие.

Выделение цветом всей строки

Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе - он фиксирует столбец, оставляя незафиксированной ссылку на строку - проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:

Выделение максимальных и минимальных значений

Ну, здесь все достаточно очевидно - проверяем, равно ли значение ячейки максимальному или минимальному по диапазону - и заливаем соответствующим цветом:

В англоязычной версии это функции MIN и MAX, соответственно.

Выделение всех значений больше(меньше) среднего

Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:

Скрытие ячеек с ошибками

Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ (ISERROR) , которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:

Скрытие данных при печати

Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати - делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение ("да", "нет"):

Заливка недопустимых значений

Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF) , которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:

Проверка дат и сроков

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

Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования - заливку ячеек цветовыми градиентами, миниграфики и значки:

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

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

Преобразование ошибки в нулевое значение и использование формата для скрытия значения

Чтобы скрыть значения ошибок, можно преобразовать их, например, в число 0, а затем применить условный формат, позволяющий скрыть значение.

Создание примера ошибки

Откройте чистый лист или создайте новый.

Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

После знака равно (=) введите ЕСЛИERROR и открываю скобку.
ЕСЛИERROR(

Переместите курсор в конец формулы.

Введите ,0), то есть запятую и закрываюю скобки.
Формула =B1/C1 становится=ЕСЛИERROR(B1/C1;0).

Применение условного формата

Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.

Выберите команду Создать правило.

В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.

Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.

На вкладке Число в списке Категория выберите пункт (все форматы).

Скрытие значений ошибок путем изменения цвета текста на белый

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

Выделите диапазон ячеек, содержащих значение ошибки.

На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
Появится диалоговое окно Диспетчер правил условного форматирования.

Выберите команду Создать правило.
Откроется диалоговое окно Создание правила форматирования.

В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.

В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.

Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.

Описание функций

ЕСЛИERROR С помощью этой функции можно определить, содержит ли ячейка ошибку и возвращает ли ошибку формула.

Выберите отчет сводной таблицы.
Появится область "Инструменты для работы со pivottable".

Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

Изменение способа отображения ошибок. В поле Формат выберите значение ошибкиПоказывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.

Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

Ячейка с ошибкой в формуле

В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы.

In Excel 2007: Click the Microsoft Office button > Excel Options >Formulas.

В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.

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

Чтобы проще было понять, в чём, собственно, дело - давайте рассмотрим простой пример. Предположим, что мы работаем вот с такой несложной таблицей, где фиксируются продажи:

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

Первое правило делает синие гистограммы на столбце с суммами сделок. Создается через Главная - Условное форматирование - Гистограммы (Home - Conditional formatting - Data bars) .

Второе - подсвечивает желтым ячейки с именами менеджеров, которые не выполнили план, т.е. сумма их сделки меньше, чем зелёная ячейка H2.

Третье - делает нижнюю границу всей строки красной, если день меняется на следующий, т.е. дата в текущей строке не равна дате в следующей.

Второе и третье правила создаются через Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - Create rule - Use formula to determine which cells to format) с вводом соответствующей формулы (2) и настройкой формата ячеек (3):

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

Путь к катастрофе

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

Теперь представим, что Кирилл Краснов повторил свою сделку в Тольятти с магазином "Лента" (строка 25) и вам нужно внести эти данные в таблицу.

Как вы поступите?

Скорее всего, как любой нормальный человек, вы скопируете 25-ю строчку и вставите её в конец таблицы, верно?

Ага, и получите в наследство вот такой бардак в правилах условного форматирования:

Excel зачем-то продублировал те же правила для добавленной строки вместо того, чтобы просто растянуть диапазон в поле Применяется к (Applied to) .

Ну, и на десерт давайте попробуем ещё что-нибудь безобидное - например, вставить пустую строку в середину таблицы, между 4 и 5-й строчками:

В списке правил условного форматирования это приведёт к появлению еще одного дубликата и раздроблению диапазона уже существующего 5-го правила на кучу фрагментов:

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

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

На англоязычных Excel-форумах в интернете такую картину называют иногда "адом" или "кошмаром условного форматирования" ("Conditional Formatting Nightmare" или "Conditional Formatting Hell").

Причем весь этот быстро разрастающийся бардак очень скоро начнёт нещадно тормозить. Условное форматирование, само по себе, весьма ресурсоёмкая штука, т.к. Excel пересчитывает правила УФ гораздо чаще, чем те же формулы. А когда этих правил несколько десятков, то даже самый мощный ПК начнёт "тупить".

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

Способ 1. Вручную

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

Для этого делаем следующее:

Выделяем в нашей таблице все строки кроме первой.
Удаляем все правила условного форматирования с выделенных ячеек через Главная - Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек (Home - Conditional formatting - Clear rules - Clear rules from selected cells) .
Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home - Format Painter) и выделяем все остальные строки, копируя на них формат с первой.

Способ 2. Макросом

Если есть ощущение, что подобную процедуру вам придётся проделывать ещё не раз, то имеет смысл автоматизировать весь процесс с помощью макроса. Для этого:

Жмём сочетание клавиш Alt + F11 или на вкладке Разработчик кнопку Visual Basic (Developer - Visual Basic) .
В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert - Module.
Вставляем в созданный пустой модуль наш макрос:

Теперь можно будет просто выделить все строки в таблице (кроме шапки) и запустить макрос через Разрабочик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 .

И всё будет хорошо :)

И не забудьте сохранить файл в формате с поддержкой макросов (xlsm).

Если нужно применять этот макрос в других файлах, то имеет смысл поместить его в Личную Книгу Макросов (Personal Macro Workbook).

Немного улучшенная версия этого макроса уже встроена в последнюю версию моей надстройки PLEX ;)

Читайте также: