Google таблицы – расчет часов смены
Цель этой таблицы — подсчитать общее количество отработанных часов и на основе этого определить, сколько часов было в дневной смене (с 6:00 до 22:00) и сколько часов в ночной смене ( с 22:00 до 6:00).
Часы начала и окончания произвольны, продолжительность также произвольна, но не более 24 часов.
Теперь вопрос в том, есть ли лучший способ решить эту проблему. Я пошел и выполнил все условия, чтобы получить формулу IFS.
Формула работает, но я уверен, что есть способ получше.
Кроме того, я хочу поделиться этим с другими, кто оказался здесь.
Теперь основной столбец — это «Ночная смена» (E), поскольку в нем есть формула, которую я сейчас ставлю под сомнение.
Вся формула:
=IFS(NOT(ISNUMBER(G3)),"",AND(D3>C3,D3-TIME(0,1,0)<TIME(22,0,0),D3+TIME(0,1,0)>TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0),C3+TIME(0,1,0)>TIME(6,0,0)),0,AND(NOT(ISBLANK(D3)),NOT(ISBLANK(C3)),D3=C3),0,C3>D3,IF(D3>TIME(6,0,0),TIMETOINTH(TIME(6,0,0),IF(C3>TIME(22,0,0),C3,TIME(22,0,0))),TIMETOINTH(D3,IF(C3>TIME(22,0,0),C3,TIME(22,0,0)))),AND(D3<=TIME(6,0,0),D3-TIME(0,1,0)<TIME(22,0,0),C3+TIME(0,1,0)>TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(D3,TIME(22,0,0)),AND(D3>=TIME(22,0,0),D3+TIME(0,1,0)>TIME(6,0,0),C3+TIME(0,1,0)>TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(D3,TIME(22,0,0)),AND(C3<=TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0),D3+TIME(0,1,0)>TIME(6,0,0),D3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(TIME(6,0,0),C3),AND(C3>=TIME(22,0,0),C3>TIME(6,0,0),D3+TIME(0,1,0)>TIME(6,0,0),D3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(TIME(6,0,0),C3))
Та же формула с моими комментариями для удобства чтения:
=IFS(
// 1st condition to check if there is something to calculate
NOT(ISNUMBER(G3)),"",
// 2nd condition
AND(
D3>C3,
D3-TIME(0,1,0)<TIME(22,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0),
C3+TIME(0,1,0)>TIME(6,0,0)
),
0, // result for night shift hours
// 3rd condition
AND(
NOT(ISBLANK(D3)),NOT(ISBLANK(C3)),D3=C3
),
0, // result
// 4th condition
C3>D3,
IF(
D3>TIME(6,0,0),
TIMETOINTH(TIME(6,0,0),IF(C3>TIME(22,0,0),C3,TIME(22,0,0))), // result 4.1
TIMETOINTH(D3,IF(C3>TIME(22,0,0),C3,TIME(22,0,0)))), // result 4.2
// fifth condition, 4 subconditions
// 5.1
AND(
D3<=TIME(6,0,0),
D3-TIME(0,1,0)<TIME(22,0,0),
C3+TIME(0,1,0)>TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(D3,TIME(22,0,0)), // 5.1 result
AND(
D3>=TIME(22,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
C3+TIME(0,1,0)>TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(D3,TIME(22,0,0)), // 5.2 result
AND(
C3<=TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
D3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(TIME(6,0,0),C3), // 5.3 result
AND(
C3>=TIME(22,0,0),
C3>TIME(6,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
D3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(TIME(6,0,0),C3)) //5.4 result
TIMETOINTH — это именованная функция, которая преобразуется в =INT(TEXT(end_hour-start_hour,"h")), где обе переменные должны быть в формате TIME(0,0,0).
Входными данными являются месяц/год в столбце H1 (месяц/год) и часы в столбцах C и D (6:00 | 06:00 | 23:00).
Спасибо.
на этом демонстрационном листе есть две вкладки. один для дневной смены, другой для вечерней смены. каждая вкладка рассчитывает часть, а не сумму. вообще, в чем здесь твой вопрос? правильно ли работает ваша формула IFS (мне не удалось ее воспроизвести, поэтому было бы неплохо, если бы вы могли поделиться копией/образцом своего листа с примером желаемого результата) или вы ищете оптимизацию варианта формулы/массива?
Ну да, оптимизация и объяснение лучшего ответа, и даже отзыв, если таблица достаточно проста, чтобы люди могли ее использовать. Я поделился листом, но не в правильном формате, сейчас редактирую ссылку.
Ответы 1
на основе: https://stackoverflow.com/a/78536206/5632629
Ф3:
=INDEX(IF(D3:D33 = "",,LET(x, 86400, b, C3:C33, c, D3:D33,
bb, b*x, cc, c*x, cd, cc+((c<b)*x),
IF((bb>("22:00"*x))*(cd>cc)*(cc>("06:00"*x)),
MAP(cc, LAMBDA(cc, MIN(cc-("06:00"*x), (("22:00"-"06:00")*x)))),
MAP(BYROW(cd, LAMBDA(cd, MIN(cd, ("22:00"*x)))),
BYROW(bb, LAMBDA(bb, MAX(bb, ("06:00"*x)))),
LAMBDA(x, y, MAX(x-y, 0)))))/60^2))
Г3:
=INDEX(LET(s, 86400, c, C3:C33, d, D3:D33,
IF((c = "")+(d = ""),,((IF(d<c, 1)+d)*s-c*s)/60^2)))
Е3:
=INDEX(LET(g, G3:G33, f, F3:F33, IF(g<>"", g-f, )))
Большое спасибо, что нашли время исправить мою формулу. Оно простирается даже на минуты, и это была следующая версия моей формулы. Еще раз спасибо.
Да, я видел это и официальную демо-версию, но если присмотреться, она вычисляет продолжительность в целом, а не разделяет ее как общее количество=ночь+день. Или у меня просто мозг расплавился от моей формулы, что я не вижу ответа (править) -->> Да, расплавился, так как я даже не увидел "Вечернего" Листа в официальных документах... О боже.. .