Учебное пособие по Excel: как проверить условное форматирование с помощью фиктивной формулы

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

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

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

Когда формула в наложении возвращает значение ИСТИНА для данной ячейки, применяется форматирование.

Формулы, которые не возвращают ИСТИНА (или эквивалент), ничего не делают.

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

Хороший способ ускорить процесс - использовать то, что я называю «фиктивными формулами».

Формулы-пустышки позволяют визуализировать поведение формул до создания правила.

Позвольте мне проиллюстрировать это очень простым примером. Допустим, мы хотим выделить в этом наборе данных значения более 100.

Для начала я выберу область сбоку, выровненную по рядам.

Затем я напишу первую формулу относительно левой верхней ячейки данных.

В данном случае это B4, поэтому формула

= B4> 100

Теперь скопирую формулу поперек и вниз.

Обратите внимание, мы получаем ИСТИННЫЙ или ЛОЖНЫЙ результат в каждой ячейке. Если мы проверим несколько ссылок, вы увидите, что каждая формула оценивает ячейку в данных относительно B4.

А теперь представьте, что эти результаты накладываются прямо на данные. Если вы видите значение ИСТИНА, будет применено форматирование.

Если вы видите FALSE, ничего не происходит.

Эта фиктивная формула выглядит хорошо, поэтому давайте опробуем ее в правиле условного форматирования.

Сначала я копирую первую фиктивную формулу. Затем я выбираю данные и создаю новое правило.

В области формулы я просто вставляю формулу. Затем я устанавливаю формат и сохраняю правило.

Теперь все значения, превышающие 100, подсвечиваются точно так, как предсказано фиктивными формулами.

Давайте попробуем ту же идею с более сложной формулой. Выделим строки в этой таблице с приоритетом «А».

Как и прежде, первым делом нужно выяснить, куда ставить фиктивные формулы. Справа у нас много места, поэтому я начну с камеры G5.

Поскольку мы хотим выделить задачи с приоритетом «А», попробуем

= B5 = "А"

Когда я копирую формулы, вы видите, что это не сработает.

Результаты TRUE показывают, что будут выделены только значения в столбце B. Мы хотим выделить целые строки, поэтому мне нужно настроить формулу, чтобы заблокировать ссылку на столбец, добавив знак доллара:

= $ B5 = "А"

Теперь фиктивные формулы работают. Мы получаем полную строку ИСТИНА, когда приоритет равен «А».

Давайте попробуем формулу в новом правиле, следуя тому же процессу, что и раньше.

Когда я устанавливаю формат и сохраняю, новое правило работает отлично с первого раза.

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

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