회사에 낙하산?이 떨어지는 바람에 사무실 전체의 배치를 바꾸고 기존 책상을
새 책상으로 바꾸다 보니 서랍 구석에 숨겨져 있는 유물 RAM을 발견했습니다.
2012 Mac mini에서 적출한 PC3-10600S 2GB * 2EA의 노트북용 램이라서
아직은 쓸만하다고 생각되어 나눔을 하고자 합니다.
그냥 드릴려고 하다가 팁란에도 쓰고 혹시나 사무실에서 특정 시간을 정해 놓고
간식 내기 등에 유용하게 사용하시면 좋을 것 같아 제목과 같은 팁을 올려봅니다.
기존의 팁을 잘 읽으신 분들이라면 쉽게 적응하실 수 있을 것으로 보입니다.
일반적으로 난수를 발생시켜 정수(intger)값을 얻으면 중복된 값이 많이 나타나게
됩니다. 예로 =INT(RAND()*100)로 1~100까지 난수를 발생시키면 대부분의 경우
몇 개가 중복으로 나타납니다. 이것을 해결하기 위한 팁을 응용한 예제입니다.
우선 동적 이름 정의를 이용하여 지속적으로 변하는 신청자의 추가, 삭제를 자동으로
확장, 축소할 수 있도록 당첨자(순위)를 동적이름으로 정의합니다. 순위를 이용하여
벌금이나 혜택을 정할 수도 있지만 여기서는 난수를 발생시켜 그 난수와 일치하는
사람을 추첨하기로 합니다.
=OFFSET(Sheet1!$H$2,0,0,COUNTA(Sheet1!$H:$H)-1,1)
Timer를 이용하여 지속적으로 난수를 발생시킵니다. G2셀에 아래를 입력합니다.
그냥 난수를 발생시켜도 되지만 그냥 에러 처리 차원에서 이메일 주소가 유효한지
점검하는데 여러 방법이 있는데 아래와 같이 처리했습니다.
=IF(ISNUMBER(FIND(“@”,F2,1))=TRUE, RAND(), “”)
B7 셀에도 난수를 발생시켜 행운의 숫자를 만듭니다. (row_cnt는 신청자 수)
lucky_no = Int(Cells(7, 2).Value * row_cnt)
이 팁의 주요한 내용인 Rank함수를 이용하여 반복없는 난수를 얻어 순위를 정하고
동적이름정의를 활용해서 추가 삭제되는 난수를 자동으로 영역위치에 넣어줍니다.
=RANK(G2,중복없는난수)
물론 “중복없는난수”도 동적이름으로 정의되어 있습니다.
=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)
“참가자’도 동적이름으로 정의하여 나중에 특정영역을 복사할 수 있는 Row값을
얻을 수 있도록 합니다. 이것은 Start_Lucky_Break에서 이용할 것입니다.
=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F:$F)-1,1)
이제 Timer를 동작시켜 특정한 지정한 시간이 되면 당첨자를 알려줍니다.
첨부된 엑셀화일을 열어 매크로 사용가능하게 하시고 Specific Time에서 시각을
지정하면 1초 단위로 난수를 발생시켜 지정된 시간이 되면 당첨자를 보여줍니다.
Public dTime As Date
Sub SetReminder()
On Error Resume Next
Dim ws As Worksheet
Set ws = Sheets(“Sheet1”)
ws.Cells(5, 2) = Now
‘1초단위로 타이머동작, 여기서 타이머의 동작시간 변경
dTime = Now + TimeValue(“00:00:01”)
Application.OnTime dTime, “SetReminder”
‘지정된 시간에 Sub문 실행, 여기에 if나 switch문으로 여러가지 일을 시킬 수 있음
If Time = TimeSerial(ws.Cells(3, 2), ws.Cells(3, 3), ws.Cells(3, 4)) Then
Call Start_Lucky_Break
End If
End Sub
Sub StopSetReminder()
‘ 실행 중지
On Error Resume Next
Application.OnTime dTime, “SetReminder”, , False
End Sub
Sub Start_Lucky_Break()
Dim row_cnt As Integer, lucky_no As Integer
Dim cell As Range
Dim rng As Range
Dim msg As String
msg = “축하합니다.” & vbCrLf
msg = msg + ” 받으실 주소와 전화번호를 쪽지로 주세요” & vbCrLf
‘ 참가자의 수를 얻는 여러 방법들과 난수의 크기를 결정
‘ row_cnt = Application.CountA(Range(“F2”, Range(“F2”).End(xlDown)))
‘ row_cnt = Application.CountA(Range(“참가자”))
‘ row_cnt = Range(“참가자”).Rows.Count
‘ row_cnt = Sheet1.UsedRange.Rows.Count – 2
‘ row_cnt = Sheet1.Range(“F1”).CurrentRegion.Rows.Count – 1
row_cnt = Sheet1.Range(“F65536”).End(xlUp).Row – 1
‘ 수식이 있는 영역을 지원자만큼 선택하여 복사, 붙여넣기
Range(“G2:H2”).Select
Selection.AutoFill Destination:=Range(“G2:H” & row_cnt + 1)
Range(“F2”).Select
‘ 행운의 숫자 생성
lucky_no = Int(Cells(7, 2).Value * row_cnt)
Set rng = Range(“당첨자”)
‘ 행운의 숫자와 같은 사람의 E-mail주소 추출
For Each cell In rng
If cell.Value = lucky_no Then
MsgBox msg & lucky_no & ” : ” & cell.Offset(0, -2).Text
End If
Next
‘ 추첨이 끝났으니 타이머 중지
Call StopSetReminder
End Sub
첨부화일 : 20150512-Share_RAM
==========================================================
* 신청하실 분들은 쪽지나 댓글, 메일로 주시면 됩니다.
2015년 05월 15일 12시 00분 정오까지 신청한 분들을 대상으로 추첨해서
보내드리도록 하겠습니다. 잘 사용하다 업그레이드하면서 빼 놓은 것이라
정상작동하겠지만 혹시나 동작하지 않을 수도 있습니다.
5월 12 2015
나눔-엑셀(EXCEL) – 중복되지않는 난수,동적이름정의, Timer를 이용한 추첨 방법
회사에 낙하산?이 떨어지는 바람에 사무실 전체의 배치를 바꾸고 기존 책상을
새 책상으로 바꾸다 보니 서랍 구석에 숨겨져 있는 유물 RAM을 발견했습니다.
2012 Mac mini에서 적출한 PC3-10600S 2GB * 2EA의 노트북용 램이라서
아직은 쓸만하다고 생각되어 나눔을 하고자 합니다.
그냥 드릴려고 하다가 팁란에도 쓰고 혹시나 사무실에서 특정 시간을 정해 놓고
간식 내기 등에 유용하게 사용하시면 좋을 것 같아 제목과 같은 팁을 올려봅니다.
기존의 팁을 잘 읽으신 분들이라면 쉽게 적응하실 수 있을 것으로 보입니다.
일반적으로 난수를 발생시켜 정수(intger)값을 얻으면 중복된 값이 많이 나타나게
됩니다. 예로 =INT(RAND()*100)로 1~100까지 난수를 발생시키면 대부분의 경우
몇 개가 중복으로 나타납니다. 이것을 해결하기 위한 팁을 응용한 예제입니다.
우선 동적 이름 정의를 이용하여 지속적으로 변하는 신청자의 추가, 삭제를 자동으로
확장, 축소할 수 있도록 당첨자(순위)를 동적이름으로 정의합니다. 순위를 이용하여
벌금이나 혜택을 정할 수도 있지만 여기서는 난수를 발생시켜 그 난수와 일치하는
사람을 추첨하기로 합니다.
=OFFSET(Sheet1!$H$2,0,0,COUNTA(Sheet1!$H:$H)-1,1)
Timer를 이용하여 지속적으로 난수를 발생시킵니다. G2셀에 아래를 입력합니다.
그냥 난수를 발생시켜도 되지만 그냥 에러 처리 차원에서 이메일 주소가 유효한지
점검하는데 여러 방법이 있는데 아래와 같이 처리했습니다.
=IF(ISNUMBER(FIND(“@”,F2,1))=TRUE, RAND(), “”)
B7 셀에도 난수를 발생시켜 행운의 숫자를 만듭니다. (row_cnt는 신청자 수)
lucky_no = Int(Cells(7, 2).Value * row_cnt)
이 팁의 주요한 내용인 Rank함수를 이용하여 반복없는 난수를 얻어 순위를 정하고
동적이름정의를 활용해서 추가 삭제되는 난수를 자동으로 영역위치에 넣어줍니다.
=RANK(G2,중복없는난수)
물론 “중복없는난수”도 동적이름으로 정의되어 있습니다.
=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)
“참가자’도 동적이름으로 정의하여 나중에 특정영역을 복사할 수 있는 Row값을
얻을 수 있도록 합니다. 이것은 Start_Lucky_Break에서 이용할 것입니다.
=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F:$F)-1,1)
이제 Timer를 동작시켜 특정한 지정한 시간이 되면 당첨자를 알려줍니다.
첨부된 엑셀화일을 열어 매크로 사용가능하게 하시고 Specific Time에서 시각을
지정하면 1초 단위로 난수를 발생시켜 지정된 시간이 되면 당첨자를 보여줍니다.
Public dTime As Date
Sub SetReminder()
On Error Resume Next
Dim ws As Worksheet
Set ws = Sheets(“Sheet1”)
ws.Cells(5, 2) = Now
‘1초단위로 타이머동작, 여기서 타이머의 동작시간 변경
dTime = Now + TimeValue(“00:00:01”)
Application.OnTime dTime, “SetReminder”
‘지정된 시간에 Sub문 실행, 여기에 if나 switch문으로 여러가지 일을 시킬 수 있음
If Time = TimeSerial(ws.Cells(3, 2), ws.Cells(3, 3), ws.Cells(3, 4)) Then
Call Start_Lucky_Break
End If
End Sub
Sub StopSetReminder()
‘ 실행 중지
On Error Resume Next
Application.OnTime dTime, “SetReminder”, , False
End Sub
Sub Start_Lucky_Break()
Dim row_cnt As Integer, lucky_no As Integer
Dim cell As Range
Dim rng As Range
Dim msg As String
msg = “축하합니다.” & vbCrLf
msg = msg + ” 받으실 주소와 전화번호를 쪽지로 주세요” & vbCrLf
‘ 참가자의 수를 얻는 여러 방법들과 난수의 크기를 결정
‘ row_cnt = Application.CountA(Range(“F2”, Range(“F2”).End(xlDown)))
‘ row_cnt = Application.CountA(Range(“참가자”))
‘ row_cnt = Range(“참가자”).Rows.Count
‘ row_cnt = Sheet1.UsedRange.Rows.Count – 2
‘ row_cnt = Sheet1.Range(“F1”).CurrentRegion.Rows.Count – 1
row_cnt = Sheet1.Range(“F65536”).End(xlUp).Row – 1
‘ 수식이 있는 영역을 지원자만큼 선택하여 복사, 붙여넣기
Range(“G2:H2”).Select
Selection.AutoFill Destination:=Range(“G2:H” & row_cnt + 1)
Range(“F2”).Select
‘ 행운의 숫자 생성
lucky_no = Int(Cells(7, 2).Value * row_cnt)
Set rng = Range(“당첨자”)
‘ 행운의 숫자와 같은 사람의 E-mail주소 추출
For Each cell In rng
If cell.Value = lucky_no Then
MsgBox msg & lucky_no & ” : ” & cell.Offset(0, -2).Text
End If
Next
‘ 추첨이 끝났으니 타이머 중지
Call StopSetReminder
End Sub
첨부화일 : 20150512-Share_RAM
==========================================================
* 신청하실 분들은 쪽지나 댓글, 메일로 주시면 됩니다.
2015년 05월 15일 12시 00분 정오까지 신청한 분들을 대상으로 추첨해서
보내드리도록 하겠습니다. 잘 사용하다 업그레이드하면서 빼 놓은 것이라
정상작동하겠지만 혹시나 동작하지 않을 수도 있습니다.
By vinipapa • 무른모 • 0 • Tags: 나눔, 동적이름, 램, 엑셀, 중복되지않는 난수, 타이머