Условное форматирование на основе другой ячейки

1. Два вида правил и какое из них вам нужно

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

Таким образом, «окрашивать доход в красный цвет, когда он меньше 500» — это встроенное правило, а «окрашивать всю строку заказа, когда его доход меньше 500» — нет, потому что окрашиваемые ячейки — это номер заказа, дата, представитель и все остальное, ни одна из которых ничего не знает о доходе.

Единственная запись, которая снимает ограничение, - это Используйте формулу, чтобы определить, какие ячейки нужно форматировать.. Это позволяет тестовой и целевой ячейкам быть разными ячейками, и это вся тема этой страницы. Все остальное в меню — это ярлык для формулы, которую вы могли бы написать.

2. Откуда написана формула

Перед пояснением: выбираете A2:H17 и вводите правило. Скажите, о какой ячейке, по вашему мнению, написана формула?

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

Это единственное предложение объясняет каждый странный результат, который получают люди. Формула в правиле не является описанием диапазона; это одна формула, написанная один раз и пронизывающая все.

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

Одна практическая ловушка при наборе текста в диалоговом окне: клавиши со стрелками вставляют ссылки на ячейки вместо перемещения курсора. Нажмите F2, чтобы переключиться в режим редактирования, или щелкните там, где вы хотите находиться.

3. Знак доллара, превращающий ячейку в строку

Вот одно и то же правило дважды, примененное к одному и тому же диапазону A2:H17, оба выполняются в Excel с последующим считыванием цветных ячеек.

Без знака доллара:

=H2>800

cells coloured:  A2, A3, A11, A16

Четыре одиночные ячейки, все в столбце A. Правило смещается в сторону вместе с диапазоном, поэтому копия, находящаяся в столбце B, проверяет I2, копия в столбце C проверяет J2 и так далее до пустого места. Только копия в столбце A всегда смотрит на столбец H.

Со знаком доллара:

=$H2>800

rows coloured:  1001, 1002, 1010, 1015     (four whole rows)

тот $ перед H столбец замораживается, поэтому каждая копия правила в каждом столбце по-прежнему проверяет столбец H. Номер строки не имеет знака доллара, поэтому он перемещается вниз по мере заполнения правила, а это именно то, что вам нужно: строка 3 проверяет H3, строка 4 проверяет H4.

Вот и все правило выделения всей строки, и его стоит произнести в виде предложения, которое можно повторять: заблокировать столбец, оставить строку свободной. $H2. Четыре найденных заказа: 1001, 1002, 1010 и 1015 — это четыре с доходом более 800, которые вы можете проверить по данным: 880, 850, 1100 и 880.

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

Ввод текста 800 в правило спрятано самое важное число во всем упражнении внутри диалогового окна, которое никто не откроет. Вместо этого поместите его в ячейку, полностью заблокируйте и наведите на него правило.

J1:  800                      a labelled cell somebody can type into

rule:  =$H2 > $J$1

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

J1 = 800     4 rows highlighted
J1 = 500    10 rows highlighted

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

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

5. Применимо к и почему эта настройка неверна

Каждое правило имеет диапазон, показанный как Применяется к в разделе «Управление правилами». Он устанавливается из того, что вы выбрали при создании правила, и это наиболее распространенная причина, по которой правильно выглядящее правило окрашивает неправильные вещи.

Три привычки, которые предотвращают почти все это.

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

Установите флажок Применить к последующему. Главная, Условное форматирование, Управление правилами. Это поле доступно для редактирования, поэтому неправильный диапазон — это пятнадцатисекундное исправление, а не перестройка.

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

6. Порядок правил и остановка, если верно

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

Оба применяются, если они не противоречат друг другу. Excel просматривает список правил сверху и применяет каждое подходящее правило, поэтому красный и жирный шрифт удачно сочетаются. Там, где два правила устанавливают такой же свойство, побеждает тот, который находится выше в списке, и этот порядок — это то, что вы контролируете с помощью стрелок в «Управлении правилами», а не то, что решает Эксель.

Это дает стандартный образец для полосатости. Напишите правила в том порядке, в котором сначала самые крайние:

1.  =$H2>=1000     dark
2.  =$H2>=800      medium
3.  =$H2>=500      light

Ордер на сумму 1100 соответствует всем трем, и поскольку правило темного цвета стоит на первом месте, темное значение выигрывает при заполнении. Если бы вы написали их наоборот, все, что выше 500, было бы легким, а два других правила никогда бы не проявились.

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

7. Чего не делает условное форматирование

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

Это не так. Условное форматирование окрашивает отображение и оставляет ячейку в покое. Проверено в Excel в выделенной ячейке, у которой нет собственной заливки:

the cell's own fill        none
what is on the screen      the rule's colour

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

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

=COUNTIF(H2:H17, ">800")      4

Эта цифра 4 соответствует четырем строкам, окрашенным правилом, потому что это тот же вопрос, который задается в отношении данных, а не краски. Все, что вы хотите посчитать, суммировать или отфильтровать, должно где-то существовать в виде формулы. Цвет только для глаз.

Это не меняет ни одного числа. Столбец доходов по-прежнему составляет 9890, что бы ни делало правило, что является скорее облегчением, чем ограничением: форматирование не может повредить данные. Это также причина, по которой выделение не является находкой. Раскрашивание четырех крупных ордеров в красный цвет не приводит к тому, что читатель может процитировать «четыре ордера выше 800», а информационной панели требуется не только краска, но и предложение.

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

8. Правила, которые порождают

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

Лист был настроен ровно с одним правилом, примененным к A1:A10. Были выполнены две обычные операции копирования и вставки отдельных ячеек в новые места. После этого Excel сообщил:

rules before   1
rules after    3

rule 1  applies to $E$6         =E6>0
rule 2  applies to $C$3         =C3>0
rule 3  applies to $A$1:$A$10   =A1>0

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

Два исправления. Чтобы предотвратить это, при перемещении данных внутри отформатированного листа вставляйте только значения: Ctrl+Альт+В, затем V. Чтобы исправить это, откройте «Управление правилами», установите раскрывающийся список «Этот рабочий лист», чтобы вы могли видеть их все, удалите фрагменты и перестройте одно правило в правильном диапазоне. Это пятиминутная работа, которую люди откладывают годами.

Крайние случаи, которые незаметно нарушают правила

Шесть, прошедшие первую сборку.

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

Пустые ячейки считаются за ноль. Правило вроде =$H2<500 подсвечивается каждая пустая строка под вашими данными, поскольку пустое значение рассматривается как 0. Берегите его: =AND($H2<>"", $H2<500).

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

Вставка строки разбивает диапазон. Вставка внутри форматированного диапазона обычно расширяет его, а вставка по краям часто этого не делает, и новая строка просто оказывается снаружи. В разделе «Управление правилами» отображается правда в поле «Применяется к».

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

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