DB의 임의 행을 중복 없이 지정 수 만큼 계속 나열 (5*3)

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=kZ9tfO7fdboPQs7T+4Sw2K+Woa1WWzMC

 

1. 함수 방식

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	5				3		5	3	25	16	27		0	4	4
3	4	5	3	9	12		15	5	7	6	32		1	2	2
4	15	5	7	6	32		9	27	14	18	23		2	3	5
5	13	11	25	49	33								3	2	0
6	5	3	25	16	27		15	5	7	6	32		0	2	2
7	9	27	14	18	23		5	3	25	16	27		1	3	4
8							13	11	25	49	33		2	2	3
9													3	2	0
10							4	5	3	9	12		0	1	1
11							9	27	14	18	23		1	4	5
12							13	11	25	49	33		2	2	3
13													3	1	0
14							13	11	25	49	33		0	3	3
15							5	3	25	16	27		1	3	4
16							4	5	3	9	12		2	1	1

 

A2 =COUNT(A3:A7)
G2 =IF($M2<$E$2,OFFSET($A$2,$O2,COLUMN(A$2)-1),"")
M2 =MOD(ROW(P1)-1,$E$2+1)
N2 =INT(RAND()*($A$2-M2)+1)
O2 =IF(M2=E$2,0,SMALL(IF(COUNTIF(INDIRECT(ADDRESS(ROW(M1)-M2,COLUMN(O$1))):O1,ROW(INDIRECT("$1:"&$A$2)))=0,ROW(INDIRECT("$1:"&$A$2))),N2))

 

수식 중 O2는 입력 후 CTRL+SHIFT+ENTER (배열수식)해야 합니다. G2를 오른쪽으로 K2까지 드래그 복사, G2:O2 잡아 아래로 원하는 만큼 드래그 복사하면 됩니다. 위와 같이 하면 A2에 추출대상 행의 전체 수가 찍히고 E2에 입력한 추출을 원하는 행 수(예시에서는 3) 만큼 임의추출된 결과가 G:K열에 표시됩니다.

 

위 표와 수식은 마우스로 드래그 복사가 됩니다. 엑셀시트에 붙인 후 F9 (재계산) 키를 여러 번 눌러보세요. 그리고 E2를 1, 2, 3, 4, 5...식으로 바꾸면 제대로 임의추출이 진행되는지 확인할 수 있습니다. O2(이미 나온 숫자를 제외하고 임의행번호 구하기)가 난제여서 할 수 없이 배열수식(속도가 느려짐 --+)을 썼는데요, 배열수식 쓰지 않고 해결하라면 일반함수로는 최고 난이도에 속해서, 아래의 VBA방식을 써야 할 겁니다.

 

2. VBA 방식

	A	B	C	D	E	F	G	H	I	J	K
1				3	4		9	27	14	18	23
2	4	5	3	9	12		5	3	25	16	27
3	15	5	7	6	32		13	11	25	49	33
4	13	11	25	49	33
5	5	3	25	16	27		15	5	7	6	32
6	9	27	14	18	23		5	3	25	16	27
7	17	5	2	3	7		13	11	25	49	33
8
9							9	27	14	18	23
10							15	5	7	6	32
11							4	5	3	9	12
12
13							9	27	14	18	23
14							4	5	3	9	12
15							15	5	7	6	32

 

D1은 한번에 추출할 행 수(전체 행수 (예시는 2~7의 6개) 이하), E1은 반복 추출할 무더기 수. 아래 매크로를 실행하면 G:K에 결과 표가 찍힙니다.

----------------------------------------

Sub 행_임의_추출()

 

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim t As Long
Dim A() As Integer
Dim P(1) As Integer                                 '원본 시작점
Dim N(1) As Integer                                 '가로 세로 크기
Dim NDump As Integer                                '한 번에 뿌릴 행 수
Dim NTime As Long                                   '되풀이할 회수
Dim R() As Integer                                  '추출된 난수 행번호

 

ActiveCell.CurrentRegion.Select                     '초기화
    P(0) = Selection.Rows(1).Row + 1
    P(1) = Selection.Columns(1).Column
    N(0) = Selection.Rows.Count - 2
    N(1) = Selection.Columns.Count - 1
        
        NDump = Cells(P(0) - 1, P(1) + N(1) - 1) - 1
        NTime = Cells(P(0) - 1, P(1) + N(1)) - 1
           
            ReDim R(N(0), 1)
            ReDim A(N(0), N(1))
               
                For i = 0 To N(0)                   '원본 표 불러 오기
                    For j = 0 To N(1)
                        A(i, j) = Cells(P(0) + i, P(1) + j)
                    Next j
                Next i
                
    For t = 0 To NTime
        For i = 0 To NDump                          '난수 발생
           R(i, 0) = Int(Rnd * (N(0) - i) + 1)
        Next i

        For i = 0 To N(0):  R(i, 1) = 0: Next i     '처리 공간 청소

        For i = 0 To NDump                          '발생된 난수 순서대로
            j = 0: k = 0
            Do While j < R(i, 0)                    '자리값 찾아 가기
                If R(k, 1) = 0 Then
                    j = j + 1
                End If
                k = k + 1
            Loop
        R(k - 1, 1) = 1
        R(i, 0) = k                                 '보정된 난수 값
            For j = 0 To N(1)                       '보정된 행 출력
                Cells((NDump + 2) * t + i + 1, P(1) + N(1) + 2 + j) = A(k - 1, j)
            Next j
        Next i
    Next t

End Sub
----------------------------------------

 

(2008.04.08)