구간별 시간 계산

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	시작	17	시	10	분		새벽	0	분		새벽	0	6		3	3	시작	끝	2	2	0
3	끝	18	시	30	분		오전	0	분		오전	6	12		1	1	0	0	2	2
4							오후	50	분		오후	12	18		1	2	x	b	3	1
5							야간	30	분		야간	18	24		1	3	x	y	3	3
6															2	1	a	y	1	3
7															2	2	a	b	1	1
8															2	3	a	y	1	3
9															3	1	x	y	3	3
10															3	2	x	b	3	1
11															3	3	0	0	2	2

 

L2:N5에 시간표를 만들어 둔 후 C,E열에 데이타 입력함.

I2 =CHOOSE(IF(N(C$3>=M2)+N(C$3>=N2)+1=2,1,IF(N(C$3>=M2)+N(C$3>=N2)+1=N(C$2>=M2)+N(C$2>=N2)+1,2,3)),C$3*60+E$3,0,N2*60)-CHOOSE(IF(N(C$2>=M2)+N(C$2>=N2)+1=2,1,IF(N(C$2>=M2)+N(C$2>=N2)+1=N(C$3>=M2)+N(C$3>=N2)+1,2,3)),C$2*60+E$2,0,M2*60)  ; I2 잡아 아래로 I5까지 복사하면 작업 끝.

 

I2 수식 구조 설명 : a, b는 C,E열의 데이타, x, y는 M,N열의 데이타일 때 데이타 구간에 입력시간이 어디 위치하는지 계산하여 각 경우의 시작시간, 마감시간을 CHOOSE로 구하여 그 차이를 구함. (아래는 프로그래밍에 관심있을 경우 참고용임. P:S의 노란 셀은 9가지 경우의 수를 압축하는 경과를 가시화한 것으로, 없어도 관계 없음.)

 

P2 =N(C$2>=M2)+N(C$2>=N2)+1

Q2 =N(C$3>=M2)+N(C$3>=N2)+1

T2 =IF(P2=2,1,IF(P2=Q2,2,3))

U2 =IF(Q2=2,1,IF(Q2=P2,2,3))

V2 =CHOOSE(U2,C$3*60+E$3,0,N2*60)-CHOOSE(T2,C$2*60+E$2,0,M2*60)

; V2에 P2:U2를 밀어 넣고 정리하면 I2가 됨.