엑셀 랜덤함수 문의드립니다.

엑셀 고수님들 답변 부탁드립니다. 5명의 데이터를 순차적이 아닌 무작위로 순번을 매기고 싶습니다.그래서 데이터 옆에 " =RANDBETWEEN(1,5) " 함수를 넣었습니다.그랬더니 중복으로 숫자가 나오게 되는데(예를 들어 5개 2개, 3이 2개) 이런식으로 말고 중복숫자 없이 1부터 5가 무작위로 나오는 방법 좀 알려주시기 바랍니다. 잘못된 예<table width="144" style="width: 108pt; border-collapse: collapse;" border="0" cellspacing="0" cellpadding="0"> <colgroup><col width="72" style="width: 54pt;" span="2"> <tbody><tr height="22" style="height: 16.5pt;"> <td width="72" height="22" style="border: 0.5pt solid windowtext; width: 54pt; height: 16.5pt; background-color: transparent;">uuu</td> <td width="72" align="right" style="border-width: 0.5pt 0.5pt 0.5pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext black; width: 54pt; background-color: yellow;">3</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">rrr</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: red;">5</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">qq</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">1</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">hhs</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: red;">5</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">mud</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: yellow;">3</td> </tr> </tbody></table> 잘된 예 <table width="144" style="width: 108pt; border-collapse: collapse;" border="0" cellspacing="0" cellpadding="0"> <colgroup><col width="72" style="width: 54pt;" span="2"> <tbody><tr height="22" style="height: 16.5pt;"> <td width="72" height="22" style="border: 0.5pt solid windowtext; width: 54pt; height: 16.5pt; background-color: transparent;">uuu</td> <td width="72" align="right" style="border-width: 0.5pt 0.5pt 0.5pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext black; width: 54pt; background-color: transparent;">3</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">rrr</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">1</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">qq</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">5</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">hhs</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">2</td> </tr> <tr height="22" style="height: 16.5pt;"> <td height="22" style="border-width: 0px 0.5pt 0.5pt; border-style: none solid solid; border-color: black windowtext windowtext; height: 16.5pt; background-color: transparent;">mud</td> <td align="right" style="border-width: 0px 0.5pt 0.5pt 0px; border-style: none solid solid none; border-color: black windowtext windowtext black; background-color: transparent;">4</td> </tr> </tbody></table>

카페정보

엑셀 사용 전문가 (엑사전)

실버 (공개)
카페지기 부재중
회원수 16,476
방문수0
카페앱수13

카페 전체 메뉴

▲

친구 카페

이전 다음

ㆍ 엑셀 2007

카페 게시글

질의 & 답변 엑셀 랜덤함수 문의드립니다.

안웃겨 추천 0 조회 495 17.01.10 15:44 댓글 2

게시글 본문내용

다음검색

저작자 표시 컨텐츠변경 비영리

캡이다
17.01.11 14:16

첫댓글 안녕하세요?
RANDBETWEEN 의 범위가 너무 작아서 발생하는 문제로 보이네요
rand() 함수를 이용해 값을 구하고 rank를 구해 보세요
VBA를 이용하는 방법입니다.
[위클리] 25회_중복없이 난수 구하기
http://cafe.daum.net/ex4mo/LSsy/29

그럼, 잘 해결되시길...
안웃겨
작성자 17.01.14 19:17

감사합니다. (^o^)b 찾아보겠습니다