|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
P |
|
1 |
날짜 |
요일 |
출근 |
퇴근 |
실근 |
연장 |
|
시작 |
끝 |
평실 |
휴실 |
평연 |
휴연 |
|
2008-01-01 |
신정 |
|
2 |
05월 02일 |
금 |
9:00 |
10:00 |
1 |
0 |
|
0:00 |
12:30 |
1 |
1 |
0 |
0 |
|
2008-02-06 |
설 |
|
3 |
05월 03일 |
토 |
12:30 |
21:00 |
4 |
4 |
|
13:30 |
17:30 |
1 |
1 |
0 |
0 |
|
2008-02-07 |
설 |
|
4 |
05월 04일 |
일 |
9:00 |
23:00 |
7.5 |
7 |
|
18:00 |
22:00 |
1 |
0 |
0 |
1.3 |
|
2008-02-08 |
설 |
|
5 |
05월 05일 |
월 |
9:00 |
23:00 |
7.5 |
7 |
|
22:00 |
0:00 |
0 |
0 |
1.3 |
1.7 |
|
2008-03-01 |
삼일절 |
|
6 |
05월 06일 |
화 |
9:00 |
23:00 |
11.5 |
1.33 |
|
0:00 |
9:00 |
0 |
0 |
1.3 |
1.7 |
|
2008-05-05 |
어린이날 |
|
7 |
05월 07일 |
수 |
7:00 |
20:00 |
11.5 |
0 |
|
|
|
|
|
|
|
|
2008-05-12 |
석가탄신일 |
|
8 |
05월 08일 |
목 |
8:00 |
1:00 |
12.5 |
4 |
|
|
a |
|
b |
|
fr |
to |
2008-06-06 |
현충일 |
|
9 |
05월 09일 |
금 |
8:00 |
1:00 |
12.5 |
4 |
|
xy |
|
|
|
|
a |
a |
2008-08-15 |
광복절 |
|
10 |
05월 10일 |
토 |
8:00 |
1:00 |
8.5 |
10.3 |
|
x |
|
y |
|
|
y |
a |
2008-09-13 |
추석 |
|
11 |
05월 11일 |
일 |
8:00 |
1:00 |
8.5 |
10.3 |
|
x |
|
|
|
y |
b |
a |
2008-09-14 |
추석 |
|
12 |
05월 12일 |
월 |
8:00 |
1:00 |
8.5 |
10.3 |
|
|
|
xy |
|
|
y |
x |
2008-09-15 |
추석 |
|
13 |
05월 13일 |
화 |
8:00 |
1:00 |
12.5 |
4 |
|
|
|
x |
|
y |
b |
x |
2008-10-03 |
개천절 |
|
14 |
05월 14일 |
수 |
8:00 |
1:00 |
12.5 |
4 |
|
|
|
|
|
xy |
b |
b |
2008-12-25 |
기독탄신일 |
B2 =TEXT(A2,"aaa")
E2 =SUMPRODUCT((N($I$2:$I$6<$D2)*$I$2:$I$6+($I$2:$I$6>=$D2)*($H$2:$H$6<$D2)*$D2+($I$2:$I$6>=$D2)*($H$2:$H$6>=$D2)*$H$2:$H$6-(N($H$2:$H$6>$C2)*$H$2:$H$6+($H$2:$H$6<=$C2)*($I$2:$I$6>$C2)*$C2+($H$2:$H$6<=$C2)*($I$2:$I$6<=$C2)*$I$2:$I$6))*24*((COUNTIF($O$1:$O$14,$A2)=0)*(WEEKDAY($A2,2)<6)*J$2:J$6+((COUNTIF($O$1:$O$14,$A2)>0)+(WEEKDAY($A2,2)>5))*K$2:K$6))
F2 =SUMPRODUCT((N($I$2:$I$6<$D2)*$I$2:$I$6+($I$2:$I$6>=$D2)*($H$2:$H$6<$D2)*$D2+($I$2:$I$6>=$D2)*($H$2:$H$6>=$D2)*$H$2:$H$6-(N($H$2:$H$6>$C2)*$H$2:$H$6+($H$2:$H$6<=$C2)*($I$2:$I$6>$C2)*$C2+($H$2:$H$6<=$C2)*($I$2:$I$6<=$C2)*$I$2:$I$6))*24*((COUNTIF($O$1:$O$14,$A2)=0)*(WEEKDAY($A2,2)<6)*L$2:L$6+((COUNTIF($O$1:$O$14,$A2)>0)+(WEEKDAY($A2,2)>5))*M$2:M$6))
M4 =2/1.5
M5 =2.5/1.5
먼저 H2:M6에 시간 구간별 수치를, O열과 같이 공휴일 목록을 만들어 둡니다. A열, O열은 날짜 서식(2008년 5월6일은 8-5-6)으로 입력합니다. 자정(24:00)을 넘는 시간은 25:00, 34:00 식으로 입력합니다. (그러지 않고 01:00, 09:00 식으로 퇴근시간을 입력해도 수식이 유효하도록 하려면 위 양식 외 보조열을 쓰면 되지만 생략함) E2:F2 잡아 아래로 드래그 다운하면 표가 완성됩니다.
E2 수식의 구조는 (끝시간(초록색)-시작시간(파랑색))*24*(적용열) 이며 F2도 마찬가지입니다. H8:N14의 표는 구간의 시간을 출퇴근 시간과 비교, 끝시간/시작시간을 무엇으로 지정해야 하는지 로직을 시각화한 것으로 프로그래밍에 관심 있을 때 참고용입니다 (실전에선 없어도 무방함). 위 표와 수식은 마우스로 드래그 복사가 되는 바 엑셀시트에 붙여 넣고 (I5, H6은 24:00, I6은 34:00로 입력해 주어야 함) 찬찬히 뜯어보세요.
'논리 > 엑셀 프로그래밍' 카테고리의 다른 글
| 총액을 항목별 분할 (기본, 수당, 성과급) (0) | 2008.11.14 |
|---|---|
| 유동참조 SUM (OFFSET, INDIRECT) (0) | 2008.11.14 |
| 같은 날짜 기준에 맞는 수량 카운트 (COUNTIF 유동참조) (0) | 2008.11.14 |
| 1~9 중 7개 숫자 조합 (9C7) (0) | 2008.11.14 |
| 영업시간 계산 (휴일 제외, 09:00 시작, 끝은 실제로) (0) | 2008.11.14 |