항목수, 항목별 점수 갯수가 늘어나면 날노가다가 된다는 단점은 있습니다만...(변수량이 커지면 VBA가 훨씬 편할 겁니다.)
|
|
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
P |
Q |
R |
S |
T |
U |
V |
W |
|
2 |
창 |
4 |
30 |
20 |
10 |
5 |
|
|
|
|
72 |
36 |
18 |
9 |
4 |
2 |
1 |
1 |
1 |
1 |
1 |
1 |
|
3 |
언 |
3 |
20 |
10 |
5 |
|
|
|
|
|
72 |
36 |
54 |
45 |
41 |
43 |
★ |
★ |
★ |
★ |
★ |
★ |
|
4 |
융 |
4 |
30 |
20 |
10 |
5 |
|
|
|
|
10 |
5 |
9 |
7 |
5 |
7 |
## |
## |
## |
## |
## |
## |
|
5 |
타 |
3 |
20 |
15 |
10 |
|
|
|
|
|
0 |
1 |
0 |
0 |
1 |
0 |
## |
## |
## |
## |
## |
## |
|
6 |
|
|
144 |
|
|
|
|
|
|
|
-1 |
1 |
-1 |
-1 |
1 |
-1 |
## |
## |
## |
## |
## |
## |
|
7 |
★ |
창 |
언 |
융 |
타 |
합 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
8 |
갑 |
20 |
20 |
10 |
20 |
70 |
14 |
7 |
43 |
|
72 |
36 |
54 |
45 |
41 |
43 |
★ |
★ |
★ |
★ |
★ |
★ |
|
9 |
을 |
30 |
5 |
30 |
15 |
80 |
9 |
5 |
26 |
|
72 |
36 |
18 |
27 |
23 |
25 |
26 |
★ |
★ |
★ |
★ |
★ |
|
10 |
병 |
10 |
10 |
30 |
15 |
65 |
16 |
12 |
86 |
|
72 |
108 |
90 |
81 |
85 |
87 |
86 |
★ |
★ |
★ |
★ |
★ |
|
11 |
정 |
30 |
20 |
30 |
20 |
100 |
2 |
2 |
145 |
|
72 |
108 |
126 |
135 |
139 |
141 |
142 |
143 |
144 |
145 |
★ |
★ |
|
12 |
무 |
5 |
5 |
5 |
10 |
25 |
1 |
1 |
144 |
|
72 |
108 |
126 |
135 |
139 |
141 |
142 |
143 |
144 |
★ |
★ |
★ |
|
13 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
14 |
36 |
|
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
30 |
|
15 |
12 |
|
20 |
20 |
20 |
20 |
20 |
20 |
20 |
20 |
20 |
20 |
20 |
20 |
10 |
10 |
10 |
10 |
10 |
10 |
10 |
10 |
|
16 |
3 |
|
30 |
30 |
30 |
20 |
20 |
20 |
10 |
10 |
10 |
5 |
5 |
5 |
30 |
30 |
30 |
20 |
20 |
20 |
10 |
10 |
|
17 |
3 |
|
20 |
15 |
10 |
20 |
15 |
10 |
20 |
15 |
10 |
20 |
15 |
10 |
20 |
15 |
10 |
20 |
15 |
10 |
20 |
15 |
|
18 |
|
|
100 |
95 |
90 |
90 |
85 |
80 |
80 |
75 |
70 |
75 |
70 |
65 |
90 |
85 |
80 |
80 |
75 |
70 |
70 |
65 |
항목 수 4, 항목 당 최대 변수 수 4로 테이블이 B2:G5에 주어졌을 때, 아래와 같이 작업합니다. (위 표를 긁어 복사, 엑셀에 붙이고 각 셀에 해당 작업을 해주면 이해하기 손쉽습니다)
간략한 설명 : 과제는 주어진 점수가 나오는 모든 조합의 수를 구하고(N) 난수를 발생시켜 1부터 N까지의 값(M)을 구한 후 전체 경우의 수 중 M번째 열을 구하면 되는 문제입니다. 뭔가 쌈박한 알고리즘이 있을 것 같은데 떠오르지 않아서리 이 예시에는 단순작업으로 찾았습니다. (이 알고리즘만 찾는다면 아래 날노가다 작업을 안 해도 변수량에 상관 없이 간략한 작업이 가능한 바, 숙제로 남김)
항목별 점수가 주어지면 모든 경우의 수를 D14:ER17에 나열합니다. (이 작업이 날노가다. 전체갯수 만큼 펼쳐지도록 마우스 조작을 해야 함.) 점수 셀인 D18:ER18 범위 줘서 M번째 열을 찾아라...하면 끝나는 작업인데 안타깝게도 엑셀에선 그런 함수가 없습니다. 그래서 고전적인 sort로직(전체 경우의 수를 반씩 쪼개어 왼쪽 오른쪽 왔다갔다 해서 해당 위치를 탐색)을 써서 M의 위치를 찾습니다 (L2:W6). M 위치(D18:ER18 중 주어진 점수가 M번째 등장하는 열의 위치)만 찾으면 나머지는 INDEX로 항목별 데이타를 가져와서 작업 끝 (C8:F8)
<전처리>
C2 =COUNT(D2:G2) ; C2잡아 C5까지 복사. 항목당 점수 갯수임. 일일이 세기 귀찮아서...
D6 =PRODUCT(C2:C5) ; 점수 조합의 가지 수. C6는 비워 둠.
<조합 표>
B14 =PRODUCT(INDIRECT("C"&(ROW()-ROW(B14)+ROW(B3))):C$5) ; 잡아 B17까지 복사.
D14 =INDEX($D2:$G2,1,MOD(INT((COLUMN()-COLUMN($D14))/$B14),$C2)+1) ; 잡아 D16까지 복사
D17 =INDEX($D5:$G5,1,MOD(COLUMN()-COLUMN($D17),$B17)+1)
D18 = SUM(D14:D17)
D14:D18 잡아 ER14:ER18까지 복사 (4*3*4*3=총 144열 ; 모든 조합의 수)
<구할 위치>
G8 ; 배분할 합계 점수
H8 =COUNTIF(D$18:ER$18,G8) ; 주어진 점수(G8)가 되는 조합 수 (N)
I8 =MOD(INT(RAND()*100),H8)+1 ; 난수 발생 (M=1에서 N까지)
<M 찾는 로직>
L2 =INT(D6/2)
M2 =IF(INT(L2/2)=0,1,INT(L2/2)) ; 잡아 W2까지 복사
L3 =L2
L4 =COUNTIF($D$18:INDIRECT(ADDRESS(18,L$3+3,4)),$G8)
L5 =N(L4<$I8)
L6 =(L5*2-1) ; L4:L6 잡아 M4:M6에 복사
M3=IF(L3="★","★", IF(AND(L4=$I8,INDIRECT(ADDRESS(18,L3+3,4))=$G8),"★",L3+L6*M2)) ; M3:M6 잡아 W3:W6에 복사
<종합>
J8 =INDEX(L8:V8,1,MATCH("★",L8:W8,0)-1) ; M번째 G8이 나오는 열 위치
L8 =L$2
M8 =IF(L8="★","★", IF(AND(COUNTIF($D$18:INDIRECT(ADDRESS(18,L8+3,4)),$G8)=$I8,INDIRECT(ADDRESS(18,L8+3,4))=$G8),"★",L8+(N(COUNTIF($D$18:INDIRECT(ADDRESS(18,L8+3,4)),$G8)<$I8)*2-1)*M$2)) ; L2:L6, M3를 한 셀로 모은 것임. M8 잡아 W8까지 복사
C8 =INDEX($D$14:$ER$17,COLUMN()-COLUMN($B7),$J8) ; 인원별 배부 결과. 잡아 F8까지 복사
C8:W8 잡아 원하는 만큼(인원 수) 아래로 복사하면 원하는 결과표를 얻습니다. 다만 주의할 것은 난수가 들어 있는 탓에 시트 상태가 조금만 변해도 결과표가 확확 달라진다는 점입니다. 작업한 결과표를 복사-셀서식-선택하여 붙여넣기-값 지정해 고정시키는 것이 바람직하다는 뜻입니다.
'논리 > 엑셀 프로그래밍' 카테고리의 다른 글
| 다중조건 SUMPRODUCT (0) | 2008.11.13 |
|---|---|
| 안내문 대량 인쇄 (0) | 2008.11.13 |
| m~n 중 3개 고르는 조합 (0) | 2008.11.13 |
| 같은 양식 여러 개 데이타 추출 (0) | 2008.11.13 |
| 주소의 동만 표시하기 (0) | 2008.11.13 |