В этом руководстве будет показано, как тестировать формулы перед применением их к условному форматированию в Excel.
Тестирование пользовательских формул - Excel
При применении настраиваемой формулы к условному форматированию в Excel важно убедиться, что формула действительно возвращает правильное значение ИСТИНА или ЛОЖЬ на вашем листе, чтобы условное форматирование работало правильно.
Перед созданием правила условного форматирования мы можем создать формулу в «свободном месте» в нашей книге Excel и убедиться, что формула работает правильно.
Тестирование формулы для выделения ячейки рабочего листа
В приведенном выше примере мы хотим написать формулу, чтобы увидеть, больше ли каждая ячейка на листе 5, и если она больше 5, мы хотим раскрасить фон ячейки.
Мы можем начать с первой ячейки в диапазоне - в данном случае B3, а затем мы можем протестировать каждую строку и столбец диапазона - вплоть до E11.
Начав с простой формулы, мы видим, что B3 имеет значение 5 и, следовательно, НЕ больше 5.
Затем мы можем использовать дескриптор, чтобы перетащить эту формулу вниз в строку 11, отметив, что часть строки адреса ячейки (например, 3) изменится на следующее число, когда мы перетаскиваем формулу вниз по строкам, так что B3 станет B4, затем B5 и так далее.
Затем мы можем перетащить выделенный диапазон ячеек через 4 столбца, чтобы проверить, имеют ли ячейки в столбцах от C до E значение больше 5. По мере того, как мы перетаскиваем формулу, часть столбца адреса ячейки будет соответственно изменяться - с столбцы от C до D до E до F.
Как мы теперь видим, мы получаем правильное значение ИСТИНА или ЛОЖЬ в зависимости от значения в соответствующей ячейке.
Теперь мы знаем, что наша формула верна, и можем использовать эту формулу для создания правила условного форматирования. Как и в нашей тестовой формуле, мы используем первую ячейку в диапазоне, B3.
= B3> 5
Когда у нас есть типы в формуле, мы можем установить Формат для цвета фона и нажать OK.
Как видите, формула = B3> 5 применяется к диапазону B3: E11. Ячейка формулы всегда должна соответствовать первой ячейке в форматируемом диапазоне.
Нажмите Подать заявление , чтобы применить форматирование к вашему листу.
Тестирование формулы для выделения строки на листе
Применение формулы для изменения цвета фона строки вместо столбца немного отличается и немного сложнее.
Рассмотрим следующий рабочий лист.
На этом листе мы хотим выделить всю строку, если проект просрочен, поэтому, если в столбце E есть ячейка, которая возвращает значение «Просроченный" вместо того "Вовремя«, Затем мы хотим выделить всю строку, в которой находится эта ячейка.
Формула для этого проста:
= ЕСЛИ (E4 = «Просрочено»; ИСТИНА; ЛОЖЬ)
Однако, если мы применим это к нашему условному форматированию, это то, что будет возвращено.
Будет отформатирован только первый столбец диапазона.
Давайте попробуем эту формулу на нашем листе Excel.
Мы получаем желаемый результат, ИСТИНА, когда проект просрочен в столбце E. Однако, если бы мы скопировали эту формулу для 5 столбцов в диапазоне (например: столбец B в столбец E), возвращенный результат был бы ЛОЖЬ .
Формула изменится - столбец E изменится на столбец F - и, конечно же, в столбце F ничего нет, поэтому формула будет возвращать ЛОЖЬ каждый раз.
Нам нужно убедиться, что формула смотрит ТОЛЬКО на столбец E, но это смотрит на правильную строку, поэтому, когда мы копируем формулу, столбец E остается прежним. Для этого нам нужно использовать смешанную ссылку, которая фиксирует столбец на месте.
= $ E4 = "Просрочено"
Теперь, когда мы копируем его на 4 столбца, столбец в формуле останется прежним, но строка изменится.
Теперь, когда наша формула работает на листе, мы можем создать собственную формулу в условном форматировании.
Когда мы нажимаем Ok, а также Подать заявление, наши строки, в которых столбец E говорит о просрочке, будут выделены.
