구간이 많은 시간 합산 구하기

	B	C	D	E	F	G	H	I
2	출근	퇴근	작업		시작	끝	차이	효과
3	7:00	18:00	9.667		7:00	9:50	2:50	1
4	7:10	18:00	9.5		9:50	10:00	0:10	0
5	9:55	10:30	0.5		10:00	12:00	2:00	1
6	11:30	13:30	1		12:00	13:00	1:00	0
7	13:00	15:00	1.833		13:00	14:50	1:50	1
8	14:20	15:30	1		14:50	15:00	0:10	0
9	15:00	17:00	2		15:00	18:00	3:00	1
10
11			1	1	0	1
12	6:50	6:55	0	0	0	0	0
13	6:50	7:05	0	1	0	0	1
14	6:50	10:00	0	1	0	1	3
15	7:05	8:00	1	1	0	0	2
16	7:05	10:00	1	1	0	1	4
17	9:55	10:30	1	1	1	1	5
18
19	2.833		0	1	2	3	4	5
20	2.833		0	11:00	11:00	2:50	2:50	0

 

작업표 (B2:D9)

D3 =SUM(IF((B3<>"")*(C3<>""),VALUE(CHOOSE(N(B3>=F$3:F$9)+N(C3>=F$3:F$9)+N(B3>=G$3:G$9)+N(C3>=G$3:G$9)*2+1,0,C3-F$3:F$9,C3-B3,H$3:H$9,G$3:G$9-B3,0))*24*I$3:I$9,0)) ; 수식 입력 후 CTRL+SHIFT+ENTER. 작업이 필요한 만큼 아래로 복사하면 작업 끝.

 

조건표 (F2:I9) - 미리 만들어 둠.

H3 =G3-F3 ; 아래로 H9까지 복사.

 

D3 수식이 나온 경로 (B11:I20) - 프로그래밍에 관심 있다면 참조. 없어도 B2:D9의 작업은 가능.

D11 =N(B3>=F3)

E11 =N(C3>=F3)

F11 =N(B3>=G3)

G11 =N(C3>=G3)

H12 =SUM(D12:F12)+G12*2

E20 =C3-F3

F20 =C3-B3

G20 =G3-F3

H20 =G3-B3

B19 =VALUE(CHOOSE(D11+E11+F11+G11*2+1,D20,E20,F20,G20,H20,I20))*24*I3

B20 =VALUE(CHOOSE(N(B3>=F3)+N(C3>=F3)+N(B3>=G3)+N(C3>=G3)*2+1,0,C3-F3,C3-B3,G3-F3,G3-B3,0))*24*I3

 

출근시간, 퇴근시간이 입력되면 그 시간이 각 구간의 어디에 해당하는지 D11:G11로 위치를 구함. 경우의 수는 6가지이며 각 경우에 따른 처리방법을 CHOOSE함수로 지정함. 그 골격이 B19이며 D11:H20까지를 B19에 밀어넣으면 B20이 됨. B20을 기초로 출퇴근시간이 입력되지 않았으면 0, 입력되었으면 각 구간별 계산을 합산하도록 배열수식을 만든 것이 D3임. 이것을 활용하여 구간이 많은 시간합산을 할 수 있음. (구간이 많을수록 IF함수의 한계가 문제 됨.)