Условное форматирование Excel для пустых ячеек

Все, что вам нужно знать об условном формате для пустых ячеек в Excel

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

Почему условное форматирование выделяет пустые ячейки?

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

Во внутренней системе Excel пустая ячейка соответствует нулевому значению. Итак, когда вы создаете условный формат для ячеек меньше определенного числа, скажем, 20, пустые ячейки также выделяются (поскольку 0 меньше 20, для пустых ячеек условие ИСТИНА).

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

Решение: Создайте отдельное правило для остановить условное форматирование, если ячейка пуста или используйте формула для игнорирования пустых ячеек.

Почему пустые ячейки не выделяются при условном форматировании?

Могут быть разные причины того, что пробелы не форматируются, например:

• Существует правило приоритета первого в очереди, которое останавливает условное форматирование для пустых ячеек.
• Ваша формула неверна.
• Ваши ячейки не совсем пусты.

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

Например, если ячейка содержит строку нулевой длины (“”), возвращаемую какой-либо другой формулой, эта ячейка не считается пустой:

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

Как выделить пустые ячейки в Excel

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

1. Выберите диапазон, в котором вы хотите выделить пустые ячейки.
2. На вкладке Главная в группе Стили щелкните Условное форматирование > Новое правило.
   
3. В открывшемся диалоговом окне «Новое правило форматирования» выберите «Форматировать только ячейки, содержащие тип правила», а затем выберите «Пробелы» в раскрывающемся списке «Форматировать только ячейки»:
   
4. Нажмите кнопку Формат….
5. В диалоговом окне «Формат ячеек» перейдите на вкладку «Заливка», выберите нужный цвет заливки и нажмите «ОК».
   
6. Нажмите OK еще раз, чтобы закрыть предыдущее диалоговое окно.

Все пустые ячейки в выбранном диапазоне будут выделены:

Кончик. Чтобы выделить непустые ячейки, выберите Форматировать только те ячейки, которые содержат > Без пробелов.

Примечание. Встроенное условное форматирование для пробелов также выделяет ячейки со строками нулевой длины (“”). Если вы хотите выделить только абсолютно пустые ячейки, создайте собственное правило с формулой ЕПУСТО, как показано в следующем примере.

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

Чтобы иметь больше гибкости при выделении пробелов, вы можете настроить собственное правило на основе формулы. Подробные шаги по созданию такого правила приведены здесь: Как создать условное форматирование с помощью формулы. Ниже мы обсудим сами формулы

Чтобы выделить только действительно пустые ячейки, которые абсолютно ничего не содержат, используйте функцию IПУСТО.

Для приведенного ниже набора данных формула:

=ЕПУСТО(B3)=ИСТИНА

Или просто:

=ПУСТО(B3)

Где B3 — верхняя левая ячейка выбранного диапазона.

Имейте в виду, что ЕПУСТО вернет ЛОЖЬ для ячеек, содержащих пустые строки (“”), следовательно, такие ячейки не будут выделены. Если такое поведение вам не нужно, то либо:

Проверьте наличие пустых ячеек, включая строки нулевой длины:

=B3=””

Или проверьте, равна ли длина строки нулю:

=ДЛСТР(B3)=0

Остановить условное форматирование, если ячейка пуста

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

Предположим, вы использовали встроенное правило для выделения ячеек от 0 до 99,99. Проблема в том, что подсвечиваются и пустые ячейки (как вы помните, в условном форматировании Excel пустая ячейка равняется нулевому значению):

Чтобы предотвратить форматирование пустых ячеек, сделайте следующее:

1. Создайте новое правило условного форматирования для целевых ячеек, щелкнув Условное форматирование > Новое правило > Форматировать только те ячейки, которые содержат > Пробелы.
2. Нажмите OK, не устанавливая формат.
   
3. Откройте Диспетчер правил («Условное форматирование» > «Управление правилами»), убедитесь, что правило «Пробелы» находится вверху списка, и установите рядом с ним флажок «Остановить, если истинно».
4. Нажмите OK, чтобы сохранить изменения и закрыть диалоговое окно.
   

Результат именно такой, как вы ожидаете:

Формула условного форматирования для игнорирования пустых ячеек

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

• Игнорировать абсолютно пустые ячейки, которые ничего не содержат:

  НЕ(ПУСТО(A1))

• Игнорировать визуально пустые ячейки, включая пустые строки:

  А1<>“”

Где A1 — самая левая ячейка выбранного диапазона.

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

=$B2<99,99

Чтобы выделить значения меньше 99,99, игнорируя пустые ячейки, вы можете использовать функцию И с двумя логическими тестами:

=И($B2<>“”, $B2<99,99)

=И(НЕ(ПУСТО($B2)), $B2<99,99)

В данном конкретном случае обе формулы игнорируют ячейки с пустыми строками, так как второе условие (<99,99) для таких ячеек ЛОЖЬ.

Если ячейка пуста, выделить строку

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

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

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

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

1. Выберите свой набор данных (A3:E15 в этом примере).
2. На вкладке «Главная» щелкните «Условное форматирование» > «Новое правило» > «Использовать формулу для определения форматируемых ячеек».
3. В поле Формат значений, где эта формула верна, введите одну из следующих формул:

   Чтобы выделить абсолютно пустые ячейки:

   =ЕПУСТО($E3)

   Чтобы выделить пустые ячейки, включая пустые строки:

   =$E3=””

   Где $E3 — это верхняя ячейка ключевого столбца, которую вы хотите проверить на наличие пробелов. Обратите внимание, что в обеих формулах мы блокируем столбец знаком $.

4. Нажмите кнопку «Формат» и выберите нужный цвет заливки.
5. Нажмите OK дважды, чтобы закрыть оба окна.

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

Выделите строку, если ячейка не пуста

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

1. Выберите свой набор данных.
2. На вкладке «Главная» щелкните «Условное форматирование» > «Новое правило» > «Использовать формулу для определения форматируемых ячеек».
3. В поле Формат значений, где эта формула верна, введите одну из следующих формул:

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

   = НЕ (ПУСТО ($ E3))

   Чтобы выделить непустые ячейки, исключая ячейки с пустыми строками:

   =$E3<>“”

   Где $E3 — самая верхняя ячейка в ключевом столбце, которая проверяется на наличие непустых значений. Опять же, для корректной работы условного форматирования блокируем столбец знаком $.

4. Нажмите кнопку «Формат», выберите любимый цвет заливки и нажмите «ОК».

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

Условное форматирование Excel для нулей, но не пробелов

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

• Создайте 2 правила: одно для пробелов, а другое для нулевых значений.
• Создайте 1 правило, которое проверяет оба условия в одной формуле.

Создайте отдельные правила для пробелов и нулей

1. Сначала создайте правило для выделения нулевых значений. Для этого нажмите «Условное форматирование» > «Новое правило» > «Форматировать только те ячейки, которые содержат», а затем установите значение ячейки равным 0, как показано на снимке экрана ниже. Нажмите кнопку Формат и выберите нужный цвет.

   Это условное форматирование применяется, если ячейка пуста или равна нулю:
   

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

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

Создайте одно правило, чтобы проверить, является ли ячейка нулевой, а не пустой

Другой способ условно отформатировать 0, но не пробелы, — создать правило с формулой, которая проверяет оба условия:

=И(B3=0, B3<>“”)

=И(B3=0, ДЛСТР(B3)>0)

Где B3 — верхняя левая ячейка выбранного диапазона.

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

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

Практическая рабочая тетрадь для скачивания

Условное форматирование Excel для пустых ячеек — примеры (файл .xlsx)

Вас также могут заинтересовать