В своем офисе Уолли отвечает за то, чтобы самолеты обслуживались по расписанию, основанному на количестве часов, затраченных на полет. Он использует Excel, чтобы отслеживать время полета для каждого самолета. В ячейке F1 у него есть «порог», когда должно произойти техническое обслуживание, как количество часов. Уолли нужен способ сделать так, чтобы ячейка, содержащая общее время полета каждого самолета, стала красной, как только это общее время окажется в пределах 5 часов от порога в F1.

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

Вы можете проверить, выполняется ли это условие, просто изменив форматирование одной из ячеек, содержащих накопленные летные часы. Например, предположим, что в ячейке D2 указано 3,5 накопленных летных часа. Выберите эту ячейку и измените ее формат на Общий. Если в результате на дисплее все еще отображается 3,5, значит, все в порядке. Если он показывает что-то другое, велика вероятность, что в ячейке нет числового количества летных часов. В этом случае вам нужно будет выполнить преобразование часов, чтобы получить то, что вам нужно:

=ROUND(MOD(D2,1)*24,2)

Эта формула удаляет часть значения перед десятичной точкой (эта часть представляет дату, и нас интересует только время), а затем округляет ее до двух десятичных знаков.

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

  1. Выделите все ячейки, содержащие накопленные летные часы.

  2. Убедитесь, что на ленте отображается вкладка «Главная».

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

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

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

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

  7. В области «Редактировать описание правила» измените первый раскрывающийся список на «Значение ячейки». (Это может быть по умолчанию.)

  8. Измените второй раскрывающийся список на Больше или Равно.

  9. В области справа от раскрывающихся списков введите формулу «= $ F $ 1-5» (без кавычек). Обратите внимание, что вы должны включить знак равенства, иначе Excel не сможет определить, что вы вводите формулу.

  10. Щелкните Форматировать, чтобы открыть диалоговое окно Формат ячеек.

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

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

  13. Щелкните ОК. Диалоговое окно «Новое правило форматирования» исчезнет, ​​и Excel снова отобразит диалоговое окно «Диспетчер правил условного форматирования». Только что определенное вами правило отображается в диалоговом окне.

  14. Щелкните ОК. Excel применяет условное форматирование ко всем ячейкам, выбранным на шаге 1.

Если вы предпочитаете не использовать правило условного форматирования (возможно, у вас уже есть другие такие правила), вы также можете полагаться на настраиваемый формат для выполнения форматирования. Однако в этом подходе вы не полагаетесь на то, что находится в ячейке F1. Вместо этого вам нужно «жестко привязать» значение триггера к формату. Например, если ваш порог в F1 составляет 500 часов, то ваше значение срабатывания будет 495 часов. Выполните следующие действия:

  1. Выделите все ячейки, содержащие накопленные летные часы.

  2. Щелкните диапазон правой кнопкой мыши, чтобы отобразить контекстное меню, в котором следует выбрать «Формат ячеек». Excel отображает диалоговое окно «Формат ячеек».

  3. Убедитесь, что отображается вкладка Число.

  4. В списке категорий выберите Custom. (См. Рис. 1.)

  5. В поле Тип введите «[Красный] [> = 495] #, 0; [<495] #, 0 ;;» (без кавычек).

  6. Щелкните ОК.

Теперь любые накопленные часы, которые меньше 495, будут отображаться нормально, а все, что больше, будет отображаться красным. Этот подход имеет некоторые преимущества (пользовательские форматы не сбиваются с толку, как иногда бывает условное форматирование), но это означает, что если значение вашего триггера изменится в какой-то момент в будущем, вам нужно будет изменить пользовательский формат напрямую.

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

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