1

У меня есть столбец времени, который охватывает некоторый интервал в двадцать четыре часа (не обязательно начиная с полуночи или полудня). Первый заполняется вручную, а затем каждый последующий заполняется автоматически по формуле « [prevcell]+TIME(0,30,0) », где предыдущая ячейка - это ячейка, расположенная непосредственно над текущей. Первая ячейка всегда будет на часовом знаке, поэтому она будет иметь форму "n:00:00" для некоторого целого числа n. Таким образом, все последующие времена будут либо на новой часовой отметке, либо на тридцатиминутной отметке, что означает, что они будут иметь форму "n:m0:00", где n - целое число, а m - либо 0, либо 3.

Затем я попытался использовать условное форматирование ячеек, чтобы любая ячейка, которая находится на тридцатиминутной отметке (то есть имеет форму «n: 30: 00» для целого числа n), имела светло-серый цвет фона (тогда как все остальные ячейки белые / бесцветные). Так, например, ячейка с 10:00:00 будет белой, а ячейка с 10:30:00 будет светло-серой.

Я сделал это:

  1. Тип правила: «Форматировать только те ячейки, которые содержат».

  2. Отформатируйте ячейки следующим образом: "значение ячейки", "равно", « =TIME(HOUR([curcell]), 30, 0) ». (Где текущая ячейка является конкретной ячейкой, которая сама форматируется.)

  3. Выбран соответствующий цвет фона форматирования.

Форматирование работало для большинства ячеек. Возможно, я бы понял, если по какой-то причине это не сработало для первых или последних ячеек, но обычно это работает. То, что не работает, это именно те ячейки со следующими значениями: 11:30:00, 18:30:00 (18:30:00 PM), 21:30:00 (9:30:00 PM), 22: 30:00 (22:30:00); эти клетки не окрашены (белые), когда они должны быть окрашены. Он работает для всех остальных ячеек, включая 23:30:00 (11:30:00 вечера), даже если он не работал для ячейки в 11:30:00 (утра). Там нет клеток, которые окрашены, когда они не должны быть; единственные клетки, которые не окрашены, когда они должны быть, являются вышеупомянутыми.

Когда я изменяю начальное значение / время в моей верхней ячейке, возникают разные ошибки одного и того же вида (часто, но не всегда для одних и тех же значений, а иногда и для дополнительных).

Кто-нибудь знает что не так или как это исправить?

Редактировать! Вот некоторые наблюдения, которые у меня были:

  • Он не работает в течение любого времени после следующей полуночи, независимо от того, сколько времени в моей первой камере (кроме 00:00:00). Зависимость от значения первой ячейки выглядит так:
  • 11:30 (утра) работает в определенное время в моей первой камере.
    1. Он не работает для 00:00:00, 00:30:00, 1:00:00, 1:30:00 и 2:00:00.
    2. Работает на 2:30, 3:00, 3:30.
    3. Он не работает в течение 4:00, 4:30, 5:00, 5:30, 6:00, 6:30.
    4. Он работает в любое время после 7:00 с шагом в 30 минут и до 11:30 включительно. (Рассмотрение больше не применяется после этого начального значения).
  • Я не проверял подробно, но с первого взгляда: 18:30:00, 21:30:00 и 22:30:00 никогда не работают.
  • В зависимости от значения моей первой ячейки, некоторые другие n: 30: 00 раз не работают (для целого числа n), но я не могу обнаружить образец. Насколько я могу сказать, ошибок нет ни для одного n: 00: 00 раз (для целого числа n).

Обновление № 1

Я изменил условие форматирования на: Форматировать ячейки с "значением ячейки", "не равно", « =TIME(HOUR([curcell]), 0, 0) ». (Где текущая ячейка является конкретной ячейкой, которая сама форматируется.)

Это, похоже, улучшило ситуацию, но не идеально. Он форматирует все правильно, за исключением того факта, что он неправильно окрашивает ячейки с этим временем: 20:30:00 (8:00:00 вечера), 23:00:00 (11:30 вечера) и (если присутствует) все время после и включая следующую полночь (24:00:00; иначе: следующие 12:00 утра).

(Правка: время, когда оно неправильно окрашивается, кажется, зависит от значения моей первой ячейки и, возможно, других факторов. Но всегда есть две отдельные ошибки в конце дня и проблема после полуночи.)

Обновление № 2

Основная проблема была решена. Однако, если кто-нибудь может сказать мне, почему это возникло в первую очередь, я был бы очень благодарен. Любопытные умы хотят знать!

|источник

2 ответа2

2

Я предполагаю, что эта проблема вызвана тем, как Excel рассчитывает, см. Эту ссылку

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

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

Использование функции MINUTE будет работать, когда значение немного больше, но не меньше. Вы можете проверить мою теорию, используя SECOND функцию для ваших данных, чтобы увидеть, есть ли у вас значения 59

Для исправления, если вы имеете дело с целыми минутами, вы можете округлить до ближайшей минуты, а затем проверить значение минуты, например, для значения времени в A2

=MINUTE(MROUND(A2,"0:01"))=30

Обновить:

Я проверил это, поместив значения времени в один столбец, а затем использовал вашу формулу в следующем столбце, поэтому там, где мои значения времени начинались с A2, я использовал эту формулу в B2, скопированную

=A2=TIME(HOUR(A2),30,0)

Для 18:30 я получаю FALSE и когда я проверяю значения, значение в А2 это

+0,770833333333334

но значение из TIME(HOUR(A2),30,0) таково:

0,77083333333333 3

Так что нет совпадения, если вы не округлите данные перед сравнением, как предложено

|источник
1

Посмотрите на снимок экрана ниже, показывает успешное цветное форматирование части MINUTE серым цветом в ячейках, если они равны 30, как вы и требовали.

Следуй этим шагам.

  1. Выберите диапазон данных, где вы должны ввести время.
  2. Примените формат ячейки как ЧЧ: ММ: СС, затем введите значения ВРЕМЕНИ.
  3. Выберите ДАННЫЙ ДИАПАЗОН, чтобы применить условный формат.
  4. В домашней вкладке нажмите «Условное форматирование», «Новые правила» и, наконец, используйте формулу, чтобы определить ячейку для форматирования.
  5. Напишите эту формулу =MINUTE(A2)=30 затем выберите Цветовое окончание с помощью OK, где A2 является частью диапазона данных, который вы в данный момент выбрали.

NB: Помните, что для лучшего понимания только в столбце B я упомянул периоды времени, так как вы написали, что условное форматирование не работает после MID NIGHT и других.

Кроме того, в столбце C я нашел МИНУТЫ из Даты в столбце А, чтобы показать и сопоставить значения в соседней ячейке, чтобы убедиться, что мое Решение использует только форматирование ячейки серого цвета, если значение Минута составляет только 30.

Надеюсь, что работа для вас.

Примечание: в качестве альтернативы, следуйте вышеуказанным письменным шагам, и вместо формулы =MINUTE(A2)=30 вы также можете использовать эту формулу: =TIME(0,MINUTE(A2),0)=TIME(0,30,0) .

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .