빵 받지 않은 학생 중 우유 3개 배분

10명 중 4~5명이 빵 선택. 나머지에게 우유 3개를 공평 배분. 우유를 받는 빈도는 되도록 동일.

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	날짜	빵					우유
3	6.4	1	2	3	5	4	7	9	10		5	4	3		번호	우유	빵	합계
4	6.5	5	6	7	8	9	1	3	4		4	3	2		1	5	7	12
5	6.6	1	2	4	6	10	5	3	8		2	1	1		2	4	11	15
6	6.7	6	5	3	2	10	1	4	7		0	0	0		3	6	8	14
7	6.8	4	5	6	2	1	9	10	8		3	2	1		4	5	7	12
8	6.9	7	8	9	10	1	2	6	5		3	2	1		5	5	11	16
9	6.10	8	7	5	2	3	1	4	6		1	1	1		6	4	11	15
10	6.1	8	6	4	5	9	3	10	2		4	3	2		7	4	7	11
11	6.12	5	6	3	2	1	7	9	8		3	2	1		8	5	8	13
12	6.1	5	4	3	10	8	2	1	6		2	1	1		9	5	5	10
13	6.1	3	4	5	6	7	2	8	10		4	3	2		10	5	5	10
14	6.2	1	2	7	5	8	6	9	3		4	3	2
15	6.2	6	7	8	2	9	3	5	4		2	2	1
16	6.2	6	7	8	2	9	1	3	5		1	1	1
17	6.2	3	4	5	6	2	7	8	10		4	3	2
18	6.2	10	2	3	6	1	5	9	4		4	3	2

 

H3 =SMALL(IF((COUNTIF(C3:G3,ROW($1:$10))=0)*((COUNTIF(H$2:J2,ROW($1:$10))<=(ROW()-ROW(H$3))*3/10)+(L3<3)),ROW($1:$10),11),INT(RAND()*L3+1))

I3 =SMALL(IF((COUNTIF($C3:H3,ROW($1:$10))=0)*((COUNTIF(H$2:J2,ROW($1:$10))<=(ROW()-ROW(H$3))*3/10)+(M3<2)),ROW($1:$10),11),INT(RAND()*M3+1))

J3 =SMALL(IF((COUNTIF($C3:I3,ROW($1:$10))=0)*((COUNTIF(H$2:J2,ROW($1:$10))<=(ROW()-ROW(H$3))*3/10)+(N3<1)),ROW($1:$10),11),INT(RAND()*N3+1))

 

L3 =SUM(IF((COUNTIF(C3:G3,ROW($1:$10))=0)*(COUNTIF(H$2:J2,ROW($1:$10))<=(ROW()-ROW(H$3))*3/10),1,0))

M3 =SUM(IF((COUNTIF($C3:H3,ROW($1:$10))=0)*(COUNTIF(H$2:J2,ROW($1:$10))<=(ROW()-ROW(H$3))*3/10),1,0))

N3 =SUM(IF((COUNTIF($C3:I3,ROW($1:$10))=0)*(COUNTIF(H$2:J2,ROW($1:$10))<=(ROW()-ROW(H$3))*3/10),1,0))

 

; 위 수식은 모두 배열수식이므로 수식창에 입력 후 CTRL+SHIFT+ENTER할 것. H3:N3 잡아 아래로 데이타 행 만큼 복사하면 작업 끝.

 

수식설명 : H3부터 오른쪽으로, RAND()함수를 써서 앞에서 나오지 않은 숫자 만큼을 발생시켜 그 순번의 번호를 출력함. RAND에 걸리는 구간을 (같은 행의 C:G열에서 나오지 않은 개수)*(앞선 행에서 빈도가 작은 숫자의 개수)를 L3:N3에서 SUM으로 구해 산출함. H3에서 RAND함수를 발생시켜 순번을 정할 때 L3:N3의 SUM결과를 참조, 대상이 적을 것 같으면 미리 대상을 넓히라고 지시함. 이럼으로써 앞에서 나오지 않은 숫자를 우선적으로 배정하며 M열에서 보듯 우유를 받은 횟수가 비슷비슷하게 만들 수 있음. 덧붙여 위 수식을 쓰면 빵 받은 이가 3명이든 5명이든 변화하더라도 비교적 공평하게 우유를 배분할 수 있음.