#Что происходит и к каким версиям относится
После сортировки таблицы правило условного форматирования может подсвечивать не те строки, если его диапазон разбился на участки, формула использует неверную относительную ссылку или действуют несколько перекрывающихся правил. В этой статье рассматривается именно область применения правила, а не сортировка как таковая.
#Как различить причины
| Возможная причина | Как узнать | Что делать |
|---|---|---|
| Неверная область «Применяется к»Правило привязано к старым адресам или отдельным разрывным диапазонам. | Часть строк не окрашивается, а соседние ячейки выделяются по прежнему условию. | Исправьте область действия через «Управление правилами». |
| Относительная ссылка в формуле смещенаФормула предполагает проверку одного столбца, но при распространении ссылки двигаются. | Подсвечивается строка выше или соседний столбец. | Проверьте закрепление столбца знаком $ и номер первой строки диапазона. |
| Есть дублирующие и перекрывающиеся правилаНесколько правил установлены на одни ячейки с разным приоритетом. | В диспетчере видно несколько аналогичных записей и разные диапазоны. | Сравните порядок и области правил, удаляйте только доказанные дубли. |
| Сортировка изменила ожидаемое соответствие записейФорматирование было рассчитано на неизменный порядок строк или использует номер строки. | После сортировки подсветка «закреплена» за номером, а не за объектом. | Привяжите проверку к данным ключевого столбца, а не старой позиции. |
#Подробные пошаговые решения
- Сохраните исходный порядок в проверочной копииПрежде чем сортировать снова, выберите «Файл» → «Сохранить как» и создайте копию. Если нужно восстановить порядок, добавьте в проверочную копию новый столбец «Исходный порядок», заполните последовательными числами до сортировки. Не пытайтесь «очистить сортировку» для возвращения старого порядка — такой команды, восстанавливающей исходную последовательность, нет.
- Найдите все действующие правила листаОткройте проблемный лист, выберите «Главная» → «Условное форматирование» → «Управление правилами». В выпадающем списке «Показать правила форматирования для» выберите «Этот лист», иначе часть правил может не отображаться. Запишите названия правил, их порядок и адреса из поля «Применяется к». Не нажимайте «Очистить правила»: это удалит настройки вместо диагностики.
- Сопоставьте «Применяется к» с фактической таблицейЩёлкните проблемное правило и посмотрите диапазон, например =$A$2:$F$100. Если таблица теперь заканчивается строкой 135, а форматирование обрывается на 100, в копии исправьте поле «Применяется к» на нужный единый диапазон. Убедитесь, что первая строка диапазона соответствует первой строке данных, а заголовок не включён по ошибке. Нажмите «Применить» и проверьте несколько строк.
- Проверьте формулу правила, если оно основано на формулеВыберите правило → «Изменить правило». Если выбран тип «Использовать формулу для определения форматируемых ячеек», проверьте, какой столбец нужно оценивать. Например, для диапазона A2:F100 условие =$D2="Просрочено" закрепляет столбец D, но меняет номер строки для каждого ряда. Если ошибочно написано =D$2="Просрочено", проверяться будет одна и та же строка. Исправляйте формулу только если понимаете смысл поля; перед сохранением запишите исходную.
- Разберите конфликты двух правилВ диспетчере правил для всего листа посмотрите, нет ли нескольких правил на пересекающиеся диапазоны. Более высокое правило имеет приоритет, а отметка «Остановить, если истинно» может мешать нижним правилам. В проверочной копии изменяйте порядок по одному правилу и сравнивайте результат, не удаляя все правила сразу. Если стало хуже, верните записанный порядок или закройте копию без сохранения.
- Проверьте подсветку после сортировки контролируемоОтсортируйте в копии всю таблицу целиком через «Данные» → «Сортировка», выбрав нужный столбец и убедившись, что выделены связанные строки. Найдите запись по уникальному номеру и проверьте подсветку до и после сортировки. Если форматирование должно следовать за значением, а не номером строки, условная формула должна ссылаться на данные этой записи.
- Убедитесь, что правило не продублировано массовой вставкойПосле вставки копированных ячеек правила могут раздробиться на многочисленные области. В «Управление правилами» сравните повторяющиеся условия и диапазоны. Если дубли действительно одинаковы, на проверочной копии сначала восстановите одно верное правило с полным диапазоном и оцените результат; только затем аккуратно удаляйте подтверждённые повторы. Откат: вернуться к копии до редактирования.
- Проверьте несколько граничных случаевВыберите записи, соответствующие условию, и записи, не соответствующие ему, в начале, середине и конце таблицы. Подсветка должна совпадать с фактическими значениями. После проверки сохраните отдельную исправленную копию, не заменяя оригинал без согласования.
#Как проверить, что проблема решена
После новой сортировки подсветка должна следовать за строками с подходящим значением, а не за прежними номерами. Сверьте хотя бы пять контрольных записей и проверьте поле «Применяется к» у каждого затронутого правила.
#Если проблема осталась
Если подсветка зависит от сложных формул, именованных диапазонов либо нескольких правил с разными приоритетами, составьте таблицу «условие — диапазон — ожидаемая подсветка» и передайте её владельцу книги. Не начинайте с полного удаления правил: так можно лишиться смысловой маркировки данных.