Условное форматирование Excel 2010 - ВРЕМЯ
Мне нужно сделать электронную таблицу с условным форматированием. Я не знаю, как это сделать. То, что я пытаюсь сделать, это Регистрация в раз. Таким образом, Боб покинет наш магазин в 8:00, и он проверит посылку, когда доберется до места прибытия. Затем он проверит еще раз, когда он прибудет на сайт.
Мне нужна регистрация на полпути и регистрация по прибытии, чтобы изменить цвет. Если Боб зарегистрировался за 15 минут рано или на полпути или по прибытии, мне нужна эта клетка, чтобы ЖЕЛТЫЙ.. Если Боб зарегистрировался вовремя, мне нужна эта клетка, чтобы перейти ОРАНЖЕВАЯ, и если Боб зарегистрировался за 15 минут позже, мне нужна эта клетка, чтобы КРАСНЫЙ,
Однако время отправления и время прибытия будут разными на каждой линии, потому что не все отправляются в одно и то же время и не все едут в одно и то же место.
В значительной степени мне нужен формат времени, чтобы, когда я говорю, что отправление происходит (в любое время) 8:00, а время регистрации должно быть 10:00, но Боб приехал туда в 10:30, мне нужна эта ячейка, чтобы покраснеть, потому что он поздно..
2 ответа
(Это моя первая публикация ответов на этом сайте, поэтому совет о том, как улучшить мой ответ, приветствуется)
Я не знаю, как настроен ваш лист, но самый простой способ заставить его работать - это настроить столбцы следующим образом: A: Сотрудник B: Время отъезда C: Ожидаемое время в пути D: Фактическое время в пути E: Ожидаемое время прибытия F: фактическое время прибытия
То, как вы заполняете эти поля, зависит от вас. Однако, как только они будут введены, вы можете использовать условное форматирование столбцов D и F, используя такие формулы: (умножение на 1440 превращает число даты / времени в минуты)
= ((C2-D2) * 1440)> 15 (Желтый, Стоп, если Истина)
= ((D2-C2) * 1440)> 15 (Красный, Стоп, если Истина)
= D2> 0 (оранжевый)
Эта настройка превращает фактическое половинное время в желтый, если это за 15 минут до ожидаемого времени, красный, если это через 15 минут после, и оранжевый, если это любое другое ненулевое значение (т. Е. Когда время установлено, а не на 15 минут раньше) или поздно, так как это было бы поймано в предыдущих формулах)
Обратите внимание, что вам нужно ввести 3 условия условного формата, и вам нужно убедиться, что оранжевый находится внизу, чтобы он проверялся только после проверки красного и желтого.
Изучив исходную версию моего ответа, я понял, что объединение всех необходимых формул форматирования было немного проще, чем я ожидал. Это, и я использовал неправильные цвета. Вот как вы можете делать все, что вам нужно, полностью в рамках условного форматирования.
Все поля ниже введены вручную. Убедитесь, что столбцы C, E и F отформатированы в формате даты / времени.
Вот скриншот правил условного форматирования. Здесь нет необходимости использовать "Stop If True", так как формулы "рано" и "поздно" не пересекаются. Приоритетное упорядочение "ранних" и "поздних" правил не имеет значения, но вам нужно убедиться, что "вовремя" - последняя обработанная.
Формулы:
Ранний полпути (применить к колонке E):
=E1<=C1+TIME(0,D1/2-15,0)
Поздно на полпути (применить к колонке E):
=E1>=C1+TIME(0,D1/2+15,0)
Раннее прибытие (применить к колонке F):
=F1<=C1+TIME(0,D1-15,0)
Позднее прибытие (применить к колонке F):
=F1>=C1+TIME(0,D1+15,0)
Вовремя (Применить к E:F; Убедитесь, что это последнее обработанное правило.):
=AND(ISERROR(SEARCH("Actual",E1)),NOT(ISBLANK(E1)))
По сути, правило "Ранний полпути" означает:
Возьмите время в пути, разделите его на два, вычтите пятнадцать минут и прибавьте к времени отправления. Если фактическое время полпути меньше или равно этому значению, выделите ячейку.
Вы можете, вероятно, выяснить остальные правила раннего / позднего оттуда. Примечание. Ранние правила приведут к ложному срабатыванию в пустой ячейке, если в той же строке указано время отправления. Учитывая правило "Вовремя", которое я использовал (см. Ниже), вы, вероятно, сможете выяснить, как предотвратить это, если хотите.
Правило "Вовремя" - это почти все ловушка по умолчанию, предназначенная для запуска на чем угодно, кроме заголовков или пустой ячейки. Это позволяет вашей электронной таблице выглядеть аккуратно, и в то же время быть относительно простым (читай: не больше / меньше или математика) правилом типа "все, что не соответствует другим". По сути, это означает:
Если при поиске строки "Actual" возникает ошибка (т. Е. "Actual" не существует в ячейке), а ячейка НЕ пуста, выделите ячейку.
Конечно, как уже говорилось, правило "в срок" работает правильно, только если это последнее обработанное правило.