Если кто-нибудь может оказать мне какую-либо помощь в объяснении, почему у меня вместо 40 отрицательные числа, что должно быть равно, помогите. Я был бы так благодарен. Благодарю вас!
4 ответа
4
Для книги правильно выбран "Использовать систему данных 1904" в разделе "Файл"> "Дополнительно"> "При вычислении этой книги" (прокрутите вниз до конца).
Это необходимо, чтобы время рассчитывалось как отрицательное. (Попробуйте выключить его, и вы увидите, что штыри превращаются во все #)
Но, например, в B16 и C16 время просто вводится в 18:00 и 01:00 без указания даты. 01:00 - 18:00 действительно отрицательное время. Примерно -0,71, если отображается как число.
Решение состоит в том, чтобы изменить каждый отдельный расчет времени начала и окончания, чтобы он проходил после полуночи. Замените «C16-B16» на «IF (C16-B16 <0, C16-B16+1, C16-B16)».
Это нужно делать для каждого дня недели, что делает формулу довольно длинной.
Попробуйте добавить дополнительный столбец между днями, чтобы отобразить количество часов, отработанных для этого дня. Тогда всего нужно только сложить эти ячейки.
Редактировать: избили это!
Отредактируйте снова: вы должны использовать +1, как в моем примере, а не +24, как в предыдущем посте, потому что единица измерения - дни, а не часы.
Окончательное редактирование: гораздо более короткое решение - заменить «C16-B16» на «MOD (C16-B16,1)». Это работает, сохраняя только дробную часть времени. Временами десятичный 1 составляет 24 часа.
1
Обычная формула для прохождения 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 по вашим номерам
0
У меня была похожая проблема, вычисляющая время, которое иногда проходило после полуночи без информации о дне или дате. Мои начальные времена были вечером и иногда переходили на следующий день, поэтому я использовал несколько формул вместе, чтобы сделать это подходящим для меня:
=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))
(обратите внимание, что я был вынужден добавить пробел после символа «меньше» по какой-то причине здесь - я не использую пробел в моей формуле)
Это просто, если, где в моем случае D4 - время окончания, а C4 - время начала. Если время окончания меньше времени начала, оно должно быть завершено после полуночи. Средний оператор дает общее время работы, корректирующееся после полуночи (+24). Конец показывает время работы, если регулярное вычитание - все, что вам нужно. Я обнаружил, что без "ТЕКСТА" и формата времени математика +24 будет отображаться как десятичное значение.
0
Твоя проблема в том, когда ты выходишь за полночь. Возьмите, например, первый ряд в пятницу, субботу и воскресенье. Каждый день время окончания 2:00 утра. Например, в пятницу, когда вы вычитаете время, требуется 18:00 пятницы минус 2:00 утра пятницы, то есть до 18: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, предоставленный вместо уравнения, которое вы использовали для расчета часов для каждого дня.