엑셀 특정 비율만큼 난수 생성하기 Random number weighted probability

 

Random number weighted probability

각 항목별 정해진 비율로 난수를 생성하는 방법을 알아보자. 다음과 같이 랜덤하게 A에서  H까지 8개의 값을 생성하고자 한다. 다만, 각 값이 균등하게 생성되는 것이 아니라, 아래에서 값별로 정해놓은 비율만큼만 생성되도록 해야한다. 어떻게 구현할 수 있는지 살펴보자.

이를 다루기 전에 텍스트가 아닌 숫자의 경우 구현하는 방법부터 알아보자. 아래의 예이다. 

기본 수식은 다음과 같다. RAND 함수와 MATCH 함수에 기반을 둔 헬퍼 테이블을 사용하여 주어진 비율만큼 랜덤한 수를 생성할 수 있다.

=MATCH(RAND(),누적확률)

이 수식은 C3:E10 의 범위에서 볼 수 있는 헬퍼 테이블에 의존한다. C열에는 실행결과로 생성하고자 하는 8개의 수를 포함하고 있다. D열에는 퍼센트로 입력된 각각의 수에 할당된 비율을 포함하고 있다.

마지막으로 E열에는 누적확률을 담고 있다. 누적확률의 경우 한 칸씩 밑으로 내렸다는 점에 주목할 필요가 있다. E5의 경우 값이 43이 아니라 38이다. 

누적확률의 경우 한 칸씩 밑으로 내렸다는 점에 주목할 필요가 있다. E5의 경우 값이 43이 아니라 38이다. 이렇게 함으로써 아래에서 설명하겠지만, MATCH 함수가 RAND() 가 생성하는 0보다 크거나 같고 1보다 작은 모든 값에 대한 위치를 찾을 수 있게 해주는 것이다.

헬퍼테이블의 가중확률을 이용하여 랜덤값을 생성하기 위해서는 아래의 수식을 복사하여 A3 셀에 입력한다. 

=MATCH(RAND(),E$3:E$10)

위의 MATCH 함수를 보면, RAND 함수가 찾으려고 하는 값(lookup value)을 제공하고 있다. 그리고 RAND 함수는 그 정의대로 0보다 크거나 같고 1보다 작은 실수를 생성한다. 그리고 찾는 배열(lookup array)은 E3:E10 로 아래 행으로 수식을 복사할 때 그 범위가 변하지 않도록 E$3:E$10 와 같이 고정시킨다.

https://support.office.com/ko-kr/article/rand-%ED%95%A8%EC%88%98-4cbfa695-8869-4788-8d90-021ea9f5be73

RAND 함수

MATCH 함수이 3번째 인수인 match type 은 생략했다. match type 이 생략되면, MATCH는 lookup_value보다 작거나 같은 값 중에서 최대값을 찾습니다. 그리고 lookup_array 인수 값은 오름차순(...-2, -1, 0, 1, 2,...A-Z, FALSE, TRUE)으로 지정해야 합니다.

예를 들어, RAND()가 0.12121 를 반환했다고 하자. match type 이 생략되었으므로, 누적확률열에서 0.12121 보다 작거나 같은 값 중 최대값인 0% 을 찾게 되고, 그 값은 첫번째 값이므로 상대위치값 1을 반환한다.

실제적으로는 MATCH 함수는 E3:E10 을 lookup value 보다 큰 값을 찾기위해 돌게되고, 그 값을 만나게 되면 한 칸 뒤로 물러서게 되는 셈이다. 그리고 E3:E10 에 적혀있는 값보다 큰 값을 찾아야하게 된다면 가장 마지막 위치인 8을 반환하게 된다. 앞에서 이미 언급했듯이 E3:E10의 첫 번째 값은 0 으로, 0.2 보다 이하의 값들을 잡아내기 위해서이다. 만약 한 칸씩 밑으로 내리지 않았다면 방금 예로 들은 0.12121 과 같이 0.2 이하의 값은 갈 곳이 없게 된다. 

Random weighted text value

이제 텍스트 값을 랜덤하게 생성하는 경우를 보자. 기존 1~8 의 수가 들어가있던 열C 에 아래와 같이 텍스트를 입력하고, MATCH 함수에 의해 반환된 위치에 기반하여 해당 범위의 값을 반환해내기 위해 INDEX 함수를 적용시키자. 

INDEX 함수는 다음과 같은 구문을 가진다. 여기서 row_num 가 MATCH 함수에 의해 반환되는 위치값이다. 

INDEX(array, row_num, [column_num]) 

A3 셀에 다음의 수식을 입력하고 아래행으로 복사해나간다.

=INDEX($C$3:$C$10,MATCH(RAND(),E$3:E$10))

그리고 실행결과가 의도한대로 나왔는지 확인하지 위해 위의 식을 1,000개 행에 복사하여 테스트해보자. 아래 이미지에서 보는바와 같이 항목별 비율과 유사하게 나왔음을 알 수 있다.