☰

Планирование в Excel — попытка рассчитать часы после полуночи, чтобы они не были отрицательными

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
Планирование в Excel — попытка рассчитать часы после полуночи, чтобы они не были отрицательными

Это скриншот проблемы, с которой я столкнулся, и кода, который я использую.

Если кто-нибудь может мне помочь, почему у меня отрицательные числа вместо 40, которым это должно быть равно, пожалуйста, помогите. Я был бы очень благодарен. Спасибо!

решение1

Вы правильно выбрали «Использовать систему данных 1904» для рабочей книги в разделе Файл > Дополнительно > «При расчете этой рабочей книги» (прокрутите вниз до конца).

Это необходимо для того, чтобы время можно было рассчитывать как отрицательное число. (Попробуйте отключить его, и вы увидите, что отрицательные числа превратятся во все #).

Но, например, в B16 и C16 время просто вводится как 18:00 и 01:00 без даты. 01:00 - 18:00 - это действительно отрицательное время. Примерно -0,71, если отображается как число.

Решение состоит в том, чтобы изменить каждый отдельный расчет времени End-Start, чтобы учесть переход за полночь. Поэтому замените "C16-B16" на "IF(C16-B16<0,C16-B16+1,C16-B16)"

Вам придется сделать это для каждого дня недели, что сделает формулу довольно длинной.

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

Редактировать: опередили!

Еще раз отредактируйте: вам следует использовать +1, как в моем примере, а не +24, как в предыдущем посте, потому что единицей измерения являются дни, а не часы.

Последняя правка: гораздо более короткое решение — заменить «C16-B16» на «MOD(C16-B16,1)». Это работает, сохраняя только дробную часть времени. В случае со временем десятичная 1 равна 24 часам.

решение2

Обычная формула для выхода за пределы 24:

 =EndTime - StartTime +(EndTime < StartTime)

Объединить все это в одну формулу для суммирования всех дней недели — неловко, но выполнимо. Для этого требуется формула, вводимая в массив, поскольку вам нужно проверять каждую пару по отдельности.

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

Обратите внимание, что два построенных массива отличаются одним столбцом. В версиях Excel 2007+ вы можете напрямую проверить ODD/EVEN с помощью функции ISODD/ISEVEN.

Эта формула должна бытьвведенный массив:

=SUM(
MOD(COLUMN($C16:$O16),2)*$C16:$O16-
(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16+
((MOD(COLUMN($C16:$O16),2)*$C16:$O16-(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16)<0))
*24

или, используя ISODD и ISEVEN:

=SUM(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16+
((ISODD(COLUMN($C16:$O16))*$C16:$O16-ISEVEN(COLUMN($B16:$N16))*$B16:$N16)<0))
*24

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

=SUM(
     MOD(
         ISODD(COLUMN($C16:$O16))*$C16:$O16-
         ISEVEN(COLUMN($B16:$N16))*$B16:$N16,
     1))
*24

Кмассив-вводформула, после ввода формулы в ячейку или строку формул, удерживайте клавишу ctrl-shiftво время ударавходить. Если вы сделали это правильно, Excel поместит фигурные скобки {...} вокруг формулы.

Я сделал формулу для линии, которую вы показываете на скриншоте. Результат 37,9833, используя ваши числа

решение3

Ваша проблема в том, что вы переходите через полночь. Возьмем, к примеру, первую строку в пятницу, субботу и воскресенье. Каждый день конечное время — 2:00 утра. Например, в пятницу, когда вы вычитаете время, получается 6:00 вечера пятницы минус 2:00 утра пятницы, что до 6:00 вечера.

Чтобы избежать негатива, но при этом сохранить приятный визуальный вид вашей диаграммы, вам следует использовать оператор IF. Для расчета на пятницу используйте:

=IF(K3 < J3, K3 - J3 + 24, K3 - J3)

В выражении IF есть три раздела.

(1) состояние

K3 < J3

Это проверяет, меньше ли K3 (время окончания), чем J3 (время начала). Если это так, то будет возвращено отрицательное число (время 2:00 утра «меньше» 6:00 вечера).

(2) если условие истинно

K3 - J3 + 24

Если условие истинно, Excel использует это уравнение. Я написал его, чтобы добавить 24, чтобы противодействовать любым отрицательным значениям, которые могут возникнуть.

(3) если условие ложно

K3 - J3

Если условие истинно, Excel использует это уравнение.

Краткое содержание

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

решение4

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

=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))

(обратите внимание, что мне пришлось добавить пробел после символа «меньше» по какой-то причине форматирования — я не использую пробел в своей формуле)

Это просто if, где в моем случае D4 — это время окончания, а C4 — время начала. Если время окончания меньше времени начала, то, должно быть, оно перешло за полночь. Средний оператор дает общее время работы с поправкой на то, что оно перешло за полночь (+24). Конечный оператор показывает время работы, если вам нужно только обычное вычитание. Я обнаружил, что без «TEXT» и формата времени математика +24 отображалась бы как десятичное значение.

Связанный контент