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

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

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

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

  2. Выберите «Условное форматирование» в меню «Формат». Excel отображает диалоговое окно «Условное форматирование».

  3. Используя первый раскрывающийся список для условия 1, выберите Formula Is.

  4. В поле формулы для условия 1 введите формулу = ISBLANK (A2).

(См. Рис. 1.)

  1. Щелкните Добавить. Excel добавляет в диалоговое окно второе условие.

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

  3. Во втором раскрывающемся списке для условия 2 выберите «Равно». В поле значения для условия 2 введите 0. (см. Рисунок 2.)

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

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

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

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

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

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

  2. Выберите «Условное форматирование» в меню «Формат». Excel отображает диалоговое окно «Условное форматирование».

  3. Используя первый раскрывающийся список для условия 1, выберите Formula Is.

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

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

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

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

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

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

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

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

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

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

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

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

Sub FormatRed()

TotalRows = 65000     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 по 65 000; вы можете изменить это, если хотите.) Если ячейка содержит числовое значение и это значение равно нулю, то ячейка заполняется красным цветом.

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

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

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

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

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

Этот совет (2980) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

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