일일 근무편성, 랜덤/공평

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=T9yA1cwoUHOVFZ/GeMjcm6K7ObxvyPBH

 

	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	날짜		명단		11						휴일목록			명단
3	2008-02-14	목	근08		가	1	0	3	7		2008-03-01	삼일절		근01
4	2008-02-15	금	근06			1	1	3	5		2008-05-01	창립기념일		근02
5	2008-02-16	토				0	1	3			2008-05-05	어린이날		근03
6	2008-02-17	일				0	1	3			2008-05-12	석가탄신일		근04
7	2008-02-18	월	근04			1	2	3	3		2008-06-06	현충일		근05
8	2008-02-19	화	근09			1	3	3	8		2008-08-15	광복절		근06
9	2008-02-20	수	근10			1	4	3	9		2008-09-13	추석		근07
10	2008-02-21	목	근07			1	5	3	6		2008-09-14	추석		근08
11	2008-02-22	금	근05			1	6	3	4		2008-09-15	추석		근09
12	2008-02-23	토				0	6	3			2008-10-03	개천절		근10
13	2008-02-24	일				0	6	3			2008-12-25	기독탄신일		근11
14	2008-02-25	월	근11			1	7	3	10
15	2008-02-26	화	근03			1	8	3	2
16	2008-02-27	수	근02			1	9	3	1
17	2008-02-28	목	근01			1	10	3	0
18	2008-02-29	금	근11			1	0	18	10
19	2008-03-01	토				0	0	18
20	2008-03-02	일				0	0	18
21	2008-03-03	월	근04			1	1	18	3
22	2008-03-04	화	근02			1	2	18	1

 

F2 =COUNTA(O3:O13) ; 근무자 수, 숫자로 입력해도 무방

G3 =IF(AND(WEEKDAY(B3,2)<6,COUNTIF(L$3:L$13,B3)=0),1,0) ; 근무일이면 1, 휴일/토/일이면 0

H3 =MOD(IF(F3<>"",0,H2+G3),F$2) ; F열에 입력이 있으면 0, 그렇지 않으면 0~10 순차 증가

I3 =IF(G3&H3="10",ROW(),I2) ; 최근 근무일이면서 H열이 0이었던 행번호

J3 =IF(G3=0,"",IF(H3=0,INT(RAND()*F$2),SMALL(IF(COUNTIF(J2:INDIRECT("J"&I3),ROW(A$1:INDIRECT("a"&F$2))-1)=0,ROW(A$1:INDIRECT("a"&F$2))-1),INT(RAND()*(F$2-H3)+1)))) ; 랜덤 순차 계산

C3 =TEXT(B3,"aaa") ; 요일 표기

D3 =IF(G3>0,OFFSET(O$3,J3,0),"") ; 휴일이면 빈칸, 근무일이면 J3로 근무자 명단 표시

 

J3는 수식 입력 후 CTRL+SHIFT+ENTER (배열수식) 해야 합니다. C3:J3 잡아 아래로 날짜 만큼 드래그 복사하면 근무표가 완성됩니다. 이 표는 근무일 11일 동안 빠짐 없이 랜덤으로 근무자를 표시하고, 그 다음 11일 근무일 동안 다른 순차로 근무자를 표시합니다. (예시의 F3와 같이) F열의 어느 칸이든 아무 문자를 입력하면 그 칸 부터 랜덤 배열이 새로 시작됩니다. 날짜/요일 칸에 조건부 서식을 쓰면 예시표와 같이 색이 달라집니다. B열은 날짜 형식이어야 합니다.

 

J3 수식을 간단히 설명하면, G3=0 이면 빈칸, H3=0 (처음)이면 그냥 랜덤, H3=1~10이면 0~10 숫자 중 위에서 나오지 않은 숫자를 배열해서 SMALL함수로 랜덤 숫자를 가져옵니다. 위에 나왔는지 검사하기 위해 COUNTIF를 쓰고 최근 0이 나온 행번호를 써서 유동참조 범위를 만듭니다. 근무자 수가 증감되면 F2만 바꾸어도 표가 유효하도록 ROW(A1:An) 식으로 유동참조를 사용했습니다.