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

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

Условие, которое проверяет наличие пробелов, не требует изменения форматирования, но условие, которое проверяет нулевые значения, может. Это работает, потому что, если выполняется первое условие (ячейка пуста), второе условие никогда не проверяется. Сделайте следующее:

  1. Выберите диапазон, который нужно условно отформатировать. (В этом примере я предполагаю, что вы выбрали диапазон A2: A99.)

  2. Открыв вкладку «Главная» ленты, щелкните параметр «Условное форматирование» в группе «Стили». Excel отображает палитру параметров, связанных с условным форматированием.

  3. Щелкните «Управление правилами». Excel отображает диалоговое окно «Диспетчер правил условного форматирования».

  4. Щелкните Новое правило. Excel отображает диалоговое окно «Новое правило форматирования».

  5. В области «Выбор типа правила» в верхней части диалогового окна выберите «Форматировать только содержащиеся ячейки». (См. Рис. 1.)

  6. В первом раскрывающемся списке правила выберите Пробелы.

  7. Щелкните ОК. Excel закроет диалоговое окно «Новое правило форматирования» и снова отобразит диалоговое окно «Диспетчер правил условного форматирования», на этот раз с видимым новым правилом. (Обратите внимание, что вы не указали форматирование для этого правила; это нормально.)

  8. Убедитесь, что для правила установлен флажок Остановить, если истина.

  9. Щелкните Новое правило. Excel снова отображает диалоговое окно «Новое правило форматирования».

  10. В области «Выбор типа правила» в верхней части диалогового окна выберите «Форматировать только содержащиеся ячейки».

  11. Используя первый раскрывающийся список для правила, выберите Значение ячейки.

  12. Во втором раскрывающемся списке правила выберите «Равно».

  13. В поле значения для условия 2 введите 0.

  14. Нажмите кнопку «Форматировать». Excel отображает диалоговое окно «Формат ячеек».

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

  16. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Формат ячеек.

  17. Нажмите ОК, чтобы закрыть диалоговое окно Новое правило форматирования. Excel снова отображает Диспетчер правил условного форматирования, и правило, которое вы только что определили, будет первым в списке. (Его тоже нужно выбрать.)

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

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

Форматирование применяется к диапазону ячеек, выбранному на шаге 1.

Другое решение — объединить два ваших условия в одно условие. Выполните следующие действия:

  1. Выберите диапазон, который нужно условно отформатировать. (В этом примере я предполагаю, что вы выбрали диапазон A2: A99.)

  2. Открыв вкладку «Главная» ленты, щелкните параметр «Условное форматирование» в группе «Стили». Excel отображает палитру параметров, связанных с условным форматированием.

  3. Щелкните Новое правило. Excel отображает диалоговое окно «Новое правило форматирования».

  4. В области «Выбор типа правила» в верхней части диалогового окна выберите «Использовать формулу для определения ячеек для форматирования».

  5. В поле формулы введите формулу = И (A2 = 0, A2 <> «»).

  6. Нажмите кнопку «Форматировать». Excel отображает диалоговое окно «Формат ячеек».

(См. Рис. 2.)

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

  2. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Формат ячеек.

  3. Нажмите ОК, чтобы закрыть диалоговое окно Новое правило форматирования. Форматирование применяется к диапазону ячеек, выбранному на шаге 1.

Формула, используемая на шаге 5, проверяет, что значение равно 0 и ячейка не пуста. Функция AND гарантирует, что формула вернет True и будет применен формат только тогда, когда будут удовлетворены оба критерия.

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

  • = И (СЧЁТ (A2) = 1, A2 = 0)

  • = И (A2 = 0, НЕ (ISBLANK (A2)))

  • = И (A2 = 0, LEN (A2)> 0)

НЕ (ПУСТОЙ (A2)) (A2 = 0)

Если вам нужен еще более быстрый способ выделения нулевых значений при игнорировании пробелов, вы можете рассмотреть возможность использования макроса. Макрос будет быстрее, потому что вы можете просто импортировать и запустить его; вам не нужно выбирать диапазон ячеек и вводить формулу (или формулы) для условного форматирования. Следующий макрос является примером того, что вы можете использовать:

Sub FormatRed()

TotalRows = 5000     ColNum = 1

For i = 1 To Cells(TotalRows, ColNum).End(xlUp).Row         Cells(i, ColNum).Interior.ColorIndex = xlAutomatic         If IsNumeric(Cells(i, ColNum).Value) Then             If Cells(i, ColNum).Value = 0 Then                 Cells(i, ColNum).Interior.ColorIndex = 3             End If         End If     Next End Sub

Макрос проверяет ячейки в столбце A. (Он проверяет ячейки в строках с 1 по 5000; вы можете изменить это, если хотите.) Если ячейка содержит числовое значение и это значение равно нулю, то ячейка заполняется красным цветом.

Если ячейка содержит что-то еще, тогда ячейке возвращается свой нормальный цвет.

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (7131) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для более старого интерфейса меню Excel здесь:

link: / excel-Conditional_Formats_that_Distinguish_Blanks_and_Zeroes [Условные форматы, которые различают пробелы и нули].