중복 없는 난수 발생, 시간 구간 계산

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=pWK4Ei7KWiUfAkN/P2IipEUoqs2pnH+a

 

함수로 처리하는 방식입니다. 아래 표를 마우스로 긁어 엑셀시트에 붙이고 수식 역시 긁어 복사, 해당 주소창에 붙인 후 이해, 응용해 보세요.

 

	A	B	C	D	E	F	L	M	N
1		1	2	3	4	5			7:45
2		104	1	33	35	103			0:15
3		83	96	32	28	85
4		25	52	20	38	98		91	11:00
5								98	11:15
6		104	1	34	37	107		104	11:30
7		83	97	32	28	88		109	11:45
8		25	53	20	40	102		113	12:00
9								116	12:15
10		11:30-17:30	07:45-14:00	08:45-16:15	09:00-15:30	11:30-18:15		118	12:30
11		10:30-18:00	11:00-18:30	08:45-15:45	08:45-14:45	10:45-17:45
12		08:30-15:30	09:30-16:30	08:15-15:45	09:00-16:15	11:15-18:15

 

B2 =INT(RAND()*(119-COLUMN(N$1))) ; 오른쪽으로 가며 0~118, 0~117, 0~116...범위의 난수 발생
B6 =SMALL(IF(COUNTIF($N1:N1,ROW($1:$119)-1)=0,ROW($1:$119)-1),B2+1) ; 중복 처리
B10 =TEXT(IF(B6<91,$N$1+INT(B6/7)*$N$2,INDEX($N$4:$N$10,MATCH(B6,$M$4:$M$10,1))),"hh:mm")&"-"&TEXT(IF(B6<91,$N$1+INT(B6/7)*$N$2+MOD(B6,7)*$N$2,INDEX($N$4:$N$10,MATCH(B6,$M$4:$M$10,1))+(B6-INDEX($M$4:$M$10,MATCH(B6,$M$4:$M$10,1)))*$N$2)+TIME(6,,),"hh:mm") ; 시작-끝 시간표 생성

M5 =M4+8-ROW(N1) ; 91이상 세트번호 구분

 

수식 중 B6는 배열수식입니다. 입력 후 CTRL+SHIFT+ENTER 하십시오. 위 예시에는 3일치만 보였는데, 얼마든 늘일 수 있습니다. 예시의 색상이 들어간 셀 이외에는 모두 복사, 채워넣기입니다. 다만 N열의 시간데이타는 직접 입력해 주어야 합니다. 위에선 예시의 편의를 위해 5개만 보였는데, 오른쪽으로 채우기하시면 119개까지 중복 없는 난수 발생이 가능합니다.

 

수식 설명 : 제시하신 과제의 시간표 세트의 경우의 수는 모두 119종 나옵니다. (시작시간 11:00까지는 모두 13구간으로 각 구간 당 7개 끝시간이 존재, 13*7=91, 11:15 부터는 (18:30이 마지막이므로) 6, 5,...식으로 가능 끝시간 수가 줄어듬. 7+6+5...+2+1=28, 따라서 91+28=119개) 즉, 이 과제는 1~119 사이 난수를 중복 없이 10개씩 발생시키라는 문제와 같습니다.

 

B2, B6는 0~118 중 임의의 수를 전혀 중복 없이 발생시키는 로직입니다. 수식 입력 후 F9(재계산)를 여러번 눌러 확인해 보세요. 함수 수준에서는 0~118의 배열 중 이미 등장한 숫자를 제외하는 함수가 없으므로 배열수식을 쓸 수 밖에 없더군요. 데이타가 많아 속도가 느려진다면 VBA를 돌릴 수 밖에 없겠습니다.

 

B10은 복잡해 보이지만 로직은 별 것 아닙니다. 발생 번호를 시작시간-끝시간으로 바꾸고 시간서식으로 표현합니다. 11:15부터 끝시간 경우가 줄어드는 구간 처리를 함수로도 할 수 있겠지만 (n*(n+1)/2=x를 수식화), 무쟈게 수식이 복잡해질 터라 예시와 같이 M, N열의 표를 활용, 시간을 가져오도록 하는 것이 실전적일 겁니다.