Марисса работает в лаборатории, где она использует Excel для подготовки отчетов для клиентов, показывающих результаты отбора проб спор плесени. Первый показанный образец всегда является базовым. Все последующие образцы в отчете должны быть выделены, если они в 10 раз превышают базовый уровень. Марисса выяснила, если базовая линия больше 0, но если базовая линия равна 0, образцы должны быть выделены, только если их 10 или больше. (Другими словами, базовые значения 0 и 1 обрабатываются одинаково для целей сравнения и выделения.)

Предположим на мгновение, что базовое значение находится в ячейке A2.

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

=A3 >= ($A$2 * 10)

Это возвращает True, если значение в A3 больше или равно базовому значению в ячейке A2. Единственное, что необходимо сделать с этой формулой, — это убедиться, что используемое базовое значение никогда не меньше 1. В формулу можно внести любое количество корректировок. Например, в следующем примере оператор IF используется для оценки базового значения. Если он равен 0, он возвращает модифицированное значение 10.

=A3 >= (IF($A$2 = 0, 10, $A$2 * 10))

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

=A3 >= (MAX($A$2,1) * 10)

Обратите внимание, что MAX вернет либо базовое значение в A2, либо значение 1, в зависимости от того, что больше. Другими словами, базовое значение никогда не будет меньше 1.

Наконец, вы могли бы вообще избежать функций рабочего листа в своей формуле, если бы просто полагались на то, как Excel обрабатывает логическую логику. Рассмотрим эту формулу:

=A3 >= ($A$2 - ($A$2 = 0)) * 10

Это работает по очень простому принципу: если базовое значение (ячейка A2) содержит 0, то ($ A $ 2 = 0) возвращает значение -1 (значение, которое Excel использует для True); если базовое значение отличается от 0, тогда тест ($ A $ 2 = 0) вернет 0 (значение, которое Excel использует для False).

Затем это значение (-1 или 0) будет вычтено из базового значения и умножено на 10. Другими словами, если A2 содержит ноль, формула будет проверяться на 0-1, что равно 1, и поэтому тест будет выполняется так, как если бы A1 содержал 1.

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

Этот совет (13447) применим к Microsoft Excel 2007, 2010, 2013 и 2016.