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

Обновлено: 07.07.2024

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Симптомы

Рассмотрим следующий сценарий.

Вы выбираете ячейку в таблице Microsoft Excel 2000 или в более поздней версии Excel.
Вы используете microsoft Visual Basic для приложений (VBA) для создания программы условного форматирования на основе формулы.
В программе условного форматирования VBA используются относительные ссылки на ячейки.
Условное форматирование применяется к ячейке, помимо выбранной ячейки.

При применении условного форматирования вы заметите, что условное форматирование не установлено правильно.

Например, при использовании программы с кодом VBA в Excel, аналогичной следующему коду, возникает проблема.

Этот код автоматически применяет условное форматирование к ячейке B1 при вводе "1" в ячейке A1. При вводе "1" в ячейке A1 ожидается, что цвет ячейки B1 изменится на красный. Однако цвет ячейки не меняется. Цвет ячейки B1 меняется на красный только при вводе "1" в ячейке B1.

Кроме того, в диалоговом окне Условное форматирование отображается формула =B1=1 вместо =A1=1.

Обходной путь

Для решения проблемы используйте один из указанных ниже способов.

Метод 1. Использование абсолютных ссылок на ячейки

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

Например, можно изменить текстовую запись Formula1:=="=A1=1" в коде VBA, который описан в разделе "Симптомы" как Formula1:=="=$A$1=1", чтобы заставить код использовать абсолютные ссылки на ячейки. Эта измененная версия кода VBA является следующим образом:

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

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

Для этого выполните указанные действия, если это необходимо для версии Excel запущенной версии.

Если вы используете в своих таблицах 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 ;)

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

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

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

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

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

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

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

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

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

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

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

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

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

Формат ячеек в Excel не меняется? Копируйте любую секцию, в которой форматирование прошло без проблем, выделите «непослушные» участки, жмите на них правой кнопкой мышки (ПКМ), а далее «Специальная вставка» и «Форматы». Существует и ряд других способов, позволяющих решить возникшую проблему. Ниже рассмотрим, почему происходит такой сбой и разберем методы его самостоятельного решения.

Причины, почему не меняется форматирование в Excel

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

Одна из причин, почему не меняется формат ячейки в Excel — появление конфликта в этом секторе, из-за чего стиль блокируется. В большинстве случаев проблема актуальна для документов в Эксель 2007 и более. Зачастую это обусловлено тем, что в новых форматах документов данные о форматировании ячеек находятся в схеме XML, а иногда при изменении происходит конфликт стилей. Excel, в свою очередь, не может установить и, как следствие, он не меняется.

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

Что делать

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

Вариант №1

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

Проверьте, в каком формате сохранена книга. Если в XLS, жмите на «Сохранить как» и «Книга Excel (*.xlsx). Иногда ничего не меняется из-за неправильного расширения.

Откройте архив любой специальной программой.
Найдите в нем следующие папки: _rels, docProps и xl.
Войдите в xl и деинсталлируйте Styles.xml.
Закройте архив.
Измените разрешение на первичное .xlsx.
Откройте книгу и согласитесь с восстановлением.
Получите информацию об удаленных стилях, которые не получилось восстановить.

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

Этот вариант наиболее эффективный, когда в Экселе не меняется формат ячеек, но из-за своего «массового» применения в дальнейшем могут возникнуть трудности с настройкой. С другой стороны, риск появления такой же ошибки в будущем сводится к минимуму.

Вариант №2

Если формат ячеек вдруг не меняется в Excel, можно воспользоваться другим методом:

Копируйте любую ячейку, с которой не возникало трудностей в этом вопросе.
Выбелите проблемный участок, где возникла проблем, с помощью ПКМ.
Выберите «Специальная вставка» и «Форматы».

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

Вариант №3

Следующее решение, когда не меняется форматирование в Excel — попытка правильного выполнения работы. Сделайте следующее:

Выделите ячейки, требующие форматирования.
Жмите на Ctrl+1.
Войдите в «Формат ячеек» и откройте раздел «Число».
Перейдите в раздел «Категория», а на следующем шаге — «Дата».
В группе «Тип» выберите подходящий формат информации. Здесь будет возможность предварительного просмотра формата с 1-й датой в данных. Его можно найти в поле «Образец».

Вариант №4

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

Жмите на любую секцию с цифрой и кликните комбинацию Ctrl+C.
Выделите диапазон, требующий конвертации.
На вкладке «Вставка» жмите «Специальная вставка».

После этого попробуйте, меняется вариант форматирования с учетом вашего запроса, или неисправность сохраняется в прежнем режиме.

Вариант №5

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

Для решения вопроса сделайте следующее:

Выберите весь столбец.
Кликните на «Данные», а далее «Текст по столбцам».
Жмите на клавишу «Готово».
Применяйте форматирование, которое вас интересует.

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

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