#excel #excel-formula
#excel #excel-формула
Вопрос:
Я новичок в Excel. Я пытаюсь составлять отчеты о посещаемости.Теперь отчеты будут рассчитываться в соответствии со следующими условиями:
Min Range as 'C': 09:30
Max Range as 'D': 19:30
Давайте рассмотрим, A как вход, а B как выход
a)If A>C and B<D, then B-A For Eg: 10:00>09:30 and 19:00<19:30 then O/P is 09:00
b)If A<C and B<D ,then B-C For Eg: 09:00<09:30 and 19:00<19:30 then O/P is 09:30
c)If A>C and B>D, then D-A For Eg: 10:30>09:30 and 20:00>19:30 then O/P is 08:00
d)If A<C and B>D, then D-C For Eg: 09:00<09:30 and 20:00>19:30 then O/P is 10:00
Формула Excel:
=IF(AND(B>=C,A<=D),B-A,
IF(AND(A>=C,B>=D),H55-A,
IF(AND(A<=C,B<=D),B-H54,
IF(AND(A<=C,B>=D),"10:00","Wrong"))))
Теперь формула Excel работает для некоторых условий, но для некоторых условий не работает. Пожалуйста, предложите мне.Заранее спасибо.
Ответ №1:
Проверьте это.
=IF(AND(A>=C,B<=D),B-A,IF(AND(A<=C,B<=D),B-C,IF(AND(A>=C,B>=D),D-A,IF(AND(A<=C,B>=D),D-C,"Wrong"))))
Комментарии:
1. Условие C), которое вы указали в вопросе, неверно. 19:30 — 10:30 — это 9:00, а не 8:00. Моя формула дает правильный ответ. Проверьте еще раз.
Ответ №2:
Описанные критерии можно резюмировать следующим образом:
In Time не может быть раньше 09:30 и Out Time не может быть позже 19:30 .
Поэтому In Time = IF(A<=C,C,A) и Out Time =IF(B>=D,D,B)
Окончательная формула должна быть O/P = Out Time - In Time затем попробуйте эту формулу:
=SUM(IF(B>=D,D,B),-IF(A<=C,C,A))
Комментарии:
1. Спасибо за ваш ответ, но частично работает. Для ранее 09:30 и позже 19:30 он должен показывать 10:00, а не разницу.
2. Раньше 09:30 и позже 19:30 приводит к 10:00, поскольку разница между последним вычисляемым часом минус самым ранним вычисляемым часом (т.Е. 19:30 и 09:30 соответственно) и разницей между этими двумя значениями составляет 10:00. Как уже упоминалось, это применяемые критерии.