두 정수 사이 중복 없는 랜덤 생성 공식
지원 버전 자세히 보기
지정한 범위에서 정수를 중복 없이 섞는 랜덤 공식입니다.
인수 설명
01레거시 배열 공식
{=LARGE ( ROW ( INDIRECT ( "1:"&(종료값-시작값+1) ) )*NOT ( COUNTIF ( 누적결과범위, ROW ( INDIRECT ( "1:"&(종료값-시작값+1) ) )+시작값-1 ) ), RANDBETWEEN ( 1, 종료값-시작값+2-ROWS ( 누적결과범위 ) ) )+시작값-1}
{=LARGE ( ROW ( INDIRECT ( "1:"&(종료값-시작값+1) ) )*NOT ( COUNTIF ( 누적결과범위, ROW ( INDIRECT ( "1:"&(종료값-시작값+1) ) )+시작값-1 ) ), RANDBETWEEN ( 1, 종료값-시작값+2-COLUMNS ( 누적결과범위 ) ) )+시작값-1}
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02동적 배열 공식 (Excel 2021 이상)
=SORTBY ( SEQUENCE ( 종료값-시작값+1, , 시작값 ), RANDARRAY ( 종료값-시작값+1 ) )인수 설명 자세히 보기
동작 원리
01레거시 배열 공식
ROW 함수와 INDIRECT 함수가 양의 순번 배열을 만들고, COUNTIF 함수와 NOT 함수가 이미 사용한 순번을 제외한 뒤 RANDBETWEEN 함수와 LARGE 함수가 남은 순번을 무작위로 고릅니다.
레거시 배열 공식 예제 시트에서 E7 셀이 -2를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
ROW 함수가 정수 후보를 만듭니다
INDIRECT 함수로 1행부터 6행까지의 참조를 만들면 ROW 함수가 1~6의 양의 순번을 반환합니다. 여기에 시작값 -2를 반영해 실제 후보 -2~3으로 바꿉니다.
=ROW ( INDIRECT ( "1:"&($C$2-$B$2+1) ) )+$B$2-1
시작값 -2와 종료값 3으로 여섯 후보를 만듭니다. = {-2;-1;0;1;2;3}
선택할 수 있는 실제 정수 후보입니다. COUNTIF 함수와 NOT 함수가 남은 후보를 표시합니다
E1:E6에는 머리글과 -1, 2, 0, 3, 1이 있습니다. COUNTIF 함수는 후보별 사용 횟수 {0;1;1;1;1;1}을 반환하고, NOT 함수는 아직 나오지 않은 -2의 위치만 TRUE로 바꿉니다.
=NOT ( COUNTIF ( $E$1:E6, {-2;-1;0;1;2;3} ) )
이미 나온 다섯 값은 FALSE가 됩니다. = {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
첫 번째 후보 -2만 남아 있습니다. 양의 순번에서 사용한 위치를 0으로 바꿉니다
실제 후보를 직접 0으로 바꾸면 음수와 0을 정확히 구분할 수 없습니다. 대신 1~6의 양의 순번을 논리 배열과 곱해 남은 첫 번째 순번 1만 유지합니다.
= {1;2;3;4;5;6}*{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
사용한 후보의 순번은 0으로 바뀝니다. = {1;0;0;0;0;0}
남은 후보에 대응하는 양의 순번입니다. LARGE 함수가 순번을 실제 정수로 되돌립니다
남은 후보가 하나이므로 RANDBETWEEN 함수는 1을 반환하고 LARGE 함수도 순번 1을 선택합니다. 여기에 시작값-1을 더하면 실제 후보 -2가 됩니다.
=LARGE ( {1;0;0;0;0;0}, RANDBETWEEN ( 1, 1 ) )+(-2)-1
RANDBETWEEN(1,1)은 항상 1입니다. = -2
E7 셀에 반환되는 마지막 정수입니다. 02동적 배열 공식 (Excel 2021 이상)
SEQUENCE 함수가 정수 후보를 만들고 SORTBY 함수가 RANDARRAY 함수의 난수 키로 순서를 바꿉니다. 예제 시트의 고정 모드에서는 재현 가능한 키 배열을 대신 사용합니다.
동적 배열 공식 예제 시트에서 G2:G7에 0, 3, -2, 2, 1, -1이 스필되는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
SEQUENCE 함수가 연속된 정수를 만듭니다
종료값 3에서 시작값 -2를 빼고 1을 더하면 후보 개수는 6입니다. SEQUENCE 함수는 시작값 -2부터 여섯 정수를 세로 배열로 반환합니다.
=SEQUENCE ( D2-C2+1, , C2 )
C2의 -2부터 D2의 3까지 생성합니다. = {-2;-1;0;1;2;3}
중복 없이 한 번씩 섞을 정수 후보입니다. IF 함수가 검산용 난수 키를 선택합니다
E2가 고정이면 재현 가능한 B2:B7의 키를 사용하고, 생성이면 RANDARRAY 함수가 0 이상 1 미만의 난수 키 여섯 개를 새로 만듭니다.
=IF ( E2="고정", B2:B7, RANDARRAY ( D2-C2+1 ) )
현재는 고정 모드이므로 B2:B7을 반환합니다. = {0.42;0.81;0.16;0.67;0.55;0.29}
검산에 사용하는 고정 난수 키 배열입니다. SORTBY 함수가 난수 키 순서로 후보를 섞습니다
SORTBY 함수는 각 후보와 짝을 이룬 키를 오름차순으로 정렬합니다. 고정 키가 작은 순서대로 후보를 배열하면 0, 3, -2, 2, 1, -1이 됩니다.
=SORTBY ( {-2;-1;0;1;2;3}, {0.42;0.81;0.16;0.67;0.55;0.29} )
B2:B7의 고정 난수 키로 정렬 순서를 추적합니다. = {0;3;-2;2;1;-1}
G2:G7에 스필되는 중복 없는 정수 순열입니다. 
일등되게 해주세요~.~헤헤
숫자 0이 나오는 경우는 어떻게 하나요?? 분명 시작값을 1로 했는데 숫자 0이 나옵니다. 칸의 갯수도 범위 값보다 작은데 어떻게 해야하나요?
적어주신 댓글만으로는 정확한 답변을 드리기가 어렵습니다.
작성하신 예제 파일과 함께 Q&A 커뮤니티로 올려주시겠어요? :)
감사합니다.
예제파일 이외에 따로 식 세워서 만들어보니까 자꾸 오류나서 시간 나면 무엇 때문인지 확인해봐야겠습니다.
본 공식은 배열수식이므로 MS365 버전 사용자가 아닐 경우 Ctrl + Shift + Enter 로 수식을 입력합니다.
이걸 지키지 않아서 오류 떴었습니다. 그냥 엔터로 입력하니까 배열수식 적용이 안되는 거였습니다.
오늘 사용하다가 오류 뜨길래 뭔가 싶었더만 이거 때문이었네요.
감사합니다.
공식이 잘못 작성되어 있었습니다. 죄송합니다.
수식을 아래로 다시 작성 후 사용해보시겠어요?
{ =LARGE(ROW(INDIRECT($시작값&":"&$종료값))*NOT(COUNTIF($시작범위좌측셀:시작범위좌측셀, ROW(INDIRECT($시작값&":"&$종료값)))), RANDBETWEEN(1,$종료값-$시작값+2-COLUMNS($시작범위좌측셀:시작범위좌측셀))) }
문제가 바로 해결되실겁니다..^^
질문이 하나 있습니다. 이게 계속 클릭할때마다 랜덤으로 숫자가 바뀌는데
제가 바꾸고 싶을때만 바꾸는 방법이 있을까요?
[파일] - [옵션] - [수식] 에서 계산 방식을 '수동'으로 바꾸신 다음,
바꾸고 싶을 때만 f9 키를 눌러보세요 :)
그런데 시작값과 종료값이 매우 큰 값(수천만~수억)인 경우에는 에러가 나는데 왜그런지 설명 부탁 드립니다.
수식이 엑셀의 행번호(최대 1,048,576)개 까지만 사용할 수 있어서 그렇습니다.^^
100만개 보다 큰 값의 무작위수는 RANDBETWEEN 함수를 사용해서 처리해보세요.
+) 지금 보니 그냥 데이터가 많아서 느리게 계산되는 것 같습니다... 좋은 자료 만들어주셔서 감사합니다.