http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=266028&page=2&sca=&sfl=&stx=&spt=0&page=2&comment_page=last&cwin=#c_266501
데이터에서 일정한 간격의 행과 또 다른 일정 간격의 열을 추출하는 팁을 올렸는데 댓글에서 초코코님이
일정한 간격의 행에서 랜덤한 열들을 추출할 수 없는지가 문의가 있었습니다. 쉽게 생각해서 접근했는데
Listbox의 열의 한계가 10개 임으로 Offset함수를 사용할 경우 List중간 중간이 비어서 보기도 싫고
내용도 많이 들어가지 못해서 이 랜덤한 열을 어떻게 처리할 지 고민하다가 10년은 늙은 듯 해요… ㅠㅠ
자! 아래 내용 설명 들어갑니다. 코드는 짧지만 코드 처리 부분이 너무 힘들었어요.
우선 개발도구> Visual Basic 선택하시고 UserForm1을 하나 만듭니다.
Listbox1, Listbox2, Textbox1, Commandbutton1, Commandbutton2, Label1을 UserForm1에 만듭니다.
각각의 내용에 아래 함수 붙여 넣으시고 실행하시면 됩니다. 자료는 INT(RAND()*10000)으로 생성함.
Listbox1의 속성창에서 MultiSelect 는 fmMultiSelectMulti로 선택합니다. 다중 선택을 위해서입니다.
Listbox2의 ColumnCount는 10으로 최대한 주고, ColumnWidths는 60pt로 전체를 다 조정합니다.
Option Explicit
‘ 아래 함수에서 참조할 내용을 public으로 정의해서 사용합니다.
Public row_cnt As Integer, col_cnt As Integer, sel_cnt As Integer
‘ 사용자 정의폼을 초기화 합니다.
Private Sub UserForm_Initialize()
Dim i As Integer
Dim rng As Range
Sheets(“Data”).Select
‘ 데이터가 들어간 행의 갯수를 구함
row_cnt = Application.CountA(Range(“A1”, Range(“A1”).End(xlDown)))
‘ 데이터가 들어간 열의 갯수를 구함
col_cnt = Application.CountA(Range(“A1”, Range(“A1”).End(xlToRight)))
‘ Listbox1에 추출할 데이터 내용 추가
For i = 0 To col_cnt – 1
‘ A1셀에서 오른쪽으로 전체 열의 수만큼 이동하면서 추가
‘ 추출할 내용을 Listbox1에 추가하는 루틴임.
For Each rng In Range(“A1”).Offset(0, i)
UserForm1.ListBox1.AddItem rng
Next rng
Next i
‘ Time은 항상 선택되므로 선택을 기본으로 함
UserForm1.ListBox1.Selected(0) = True
End Sub
‘ Data 추출 루틴입니다.
Private Sub CommandButton1_Click()
Dim i As Integer, j As Integer, k As Integer, ccnt As Integer, runit As Integer
runit = UserForm1.TextBox1.Value
i = 0
j = 0
k = 0
ccnt = 0 ‘ 리스트 박스에 나타날 위치를 정해주는 변수, 중간에 값들이 비어서 offset함수만 사용하면
‘ 데이터가 리스트 박스 중간중간이 비어서 보기 싫고 Listbox가 10열밖에 Data를 넣을 수 없어
‘ 효율이 떨어짐
sel_cnt = 0 ‘ Listbox1에 선택되어진 리스트의 갯수
‘속도를 빠르게 하기 위해서 업데이트를 하지 않고 추출 진행
Application.ScreenUpdating = False
For i = 0 To UserForm1.ListBox1.ListCount – 1
If UserForm1.ListBox1.Selected(i) = True Then
sel_cnt = sel_cnt + 1 ‘ 선택된 리스트의 갯수를 확인, Listbox2의 위치 선정을 위해 계산
End If
Next i
‘ 선택한 데이터가 10개가 넘으면 경고
If sel_cnt > 10 Then
MsgBox “선택한 데이터가 10개가 넘어요”
Exit Sub
End If
UserForm1.ListBox2.Clear ‘ 기존 추출된 Data 삭제
UserForm1.ListBox2.AddItem “” ‘ 추출할 데이터 제목 최상단에 추가
‘ 제일 힘든 루틴이었어요. Listbox 열의 크기가 10개 밖에 넣질 못해서 그냥 offset로 데이터를 추출할 수는 있지만
‘ 선택되지 않은 데이터의 열들이 비고 10이 넘어가는 데이터는 추출을 하지 못해서 로직 짜느라 10년은 늙은 듯 …
‘ 더욱이 추출하는 데이터가 랜덤이라 수열을 만들 수도 없고 해서 ㅠㅠ
For j = 0 To UserForm1.ListBox1.ListCount – 1
If UserForm1.ListBox1.Selected(j) = True Then
ccnt = (ccnt Mod sel_cnt) + 1 ‘ 핵심 루틴, 잘 해석해서 보세요
UserForm1.ListBox2.List(0, ccnt – 1) = Range(“A1”).Offset(0, j)
End If
Next j
‘ 데이터를 추출하는 루틴입니다. 위는 제목 행을 추출하는 루틴이구요.
‘ 첫 행을 임의로 추가 추출하고 싶으시면 runit – 1 부분을 0으로 대치하시면 됩니다.
For i = runit – 1 To row_cnt Step runit
UserForm1.ListBox2.AddItem “”
For j = 0 To UserForm1.ListBox1.ListCount – 1
If UserForm1.ListBox1.Selected(j) = True Then
ccnt = (ccnt Mod sel_cnt) + 1 ‘ 핵심 루틴, 잘 해석해서 보세요
UserForm1.ListBox2.List(k + 1, ccnt – 1) = Range(“A2”).Offset(i, j)
End If
Next j
k = k + 1
Next i
End Sub
‘ 추출된 데이터를 시트에 뿌려줍니다.
Private Sub CommandButton2_Click()
Dim i As Integer, j As Integer
i = 0
j = 0
‘ 속도를 빠르게 하기 위해서 업데이트 제한, 아래를 주석 처리하면 데이터가 넣어지는 것이 보임
Application.ScreenUpdating = False
‘ 추출할 데이트를 담을 시트 선택
Sheets(“ExtItem”).Select
‘ 기존 추출된 데이터 삭제
Range(“A1”, Range(“A1”).End(xlToRight)).End(xlDown).Clear
‘ 추출한 데이터 시트에 넣기, 자주 사용하는 루틴이므로 기억해 두면 좋음
For i = 0 To UserForm1.ListBox2.ListCount – 1
For j = 0 To UserForm1.ListBox2.ColumnCount – 1
Cells(i + 1, j + 1) = UserForm1.ListBox2.List(i, j)
Next j
Next i
‘ 짠~하고 업데이트된 데이터 보이기
Application.ScreenUpdating = True
Unload UserForm1
End Sub
첨부자료) 20150406-일정행-임의열 데이터 추출
전체 소스는 위 내용이므로 직접 실습하시면서 만들어 보시는 것도 실력향상에 도움이 될 것 같네요.
4월 6 2015
엑셀(EXCEL) – 일정한 간격의 행과 임의의 열들을 추출하는 법
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=266028&page=2&sca=&sfl=&stx=&spt=0&page=2&comment_page=last&cwin=#c_266501
데이터에서 일정한 간격의 행과 또 다른 일정 간격의 열을 추출하는 팁을 올렸는데 댓글에서 초코코님이
일정한 간격의 행에서 랜덤한 열들을 추출할 수 없는지가 문의가 있었습니다. 쉽게 생각해서 접근했는데
Listbox의 열의 한계가 10개 임으로 Offset함수를 사용할 경우 List중간 중간이 비어서 보기도 싫고
내용도 많이 들어가지 못해서 이 랜덤한 열을 어떻게 처리할 지 고민하다가 10년은 늙은 듯 해요… ㅠㅠ
자! 아래 내용 설명 들어갑니다. 코드는 짧지만 코드 처리 부분이 너무 힘들었어요.
우선 개발도구> Visual Basic 선택하시고 UserForm1을 하나 만듭니다.
Listbox1, Listbox2, Textbox1, Commandbutton1, Commandbutton2, Label1을 UserForm1에 만듭니다.
각각의 내용에 아래 함수 붙여 넣으시고 실행하시면 됩니다. 자료는 INT(RAND()*10000)으로 생성함.
Listbox1의 속성창에서 MultiSelect 는 fmMultiSelectMulti로 선택합니다. 다중 선택을 위해서입니다.
Listbox2의 ColumnCount는 10으로 최대한 주고, ColumnWidths는 60pt로 전체를 다 조정합니다.
Option Explicit
‘ 아래 함수에서 참조할 내용을 public으로 정의해서 사용합니다.
Public row_cnt As Integer, col_cnt As Integer, sel_cnt As Integer
‘ 사용자 정의폼을 초기화 합니다.
Private Sub UserForm_Initialize()
Dim i As Integer
Dim rng As Range
Sheets(“Data”).Select
‘ 데이터가 들어간 행의 갯수를 구함
row_cnt = Application.CountA(Range(“A1”, Range(“A1”).End(xlDown)))
‘ 데이터가 들어간 열의 갯수를 구함
col_cnt = Application.CountA(Range(“A1”, Range(“A1”).End(xlToRight)))
‘ Listbox1에 추출할 데이터 내용 추가
For i = 0 To col_cnt – 1
‘ A1셀에서 오른쪽으로 전체 열의 수만큼 이동하면서 추가
‘ 추출할 내용을 Listbox1에 추가하는 루틴임.
For Each rng In Range(“A1”).Offset(0, i)
UserForm1.ListBox1.AddItem rng
Next rng
Next i
‘ Time은 항상 선택되므로 선택을 기본으로 함
UserForm1.ListBox1.Selected(0) = True
End Sub
‘ Data 추출 루틴입니다.
Private Sub CommandButton1_Click()
Dim i As Integer, j As Integer, k As Integer, ccnt As Integer, runit As Integer
runit = UserForm1.TextBox1.Value
i = 0
j = 0
k = 0
ccnt = 0 ‘ 리스트 박스에 나타날 위치를 정해주는 변수, 중간에 값들이 비어서 offset함수만 사용하면
‘ 데이터가 리스트 박스 중간중간이 비어서 보기 싫고 Listbox가 10열밖에 Data를 넣을 수 없어
‘ 효율이 떨어짐
sel_cnt = 0 ‘ Listbox1에 선택되어진 리스트의 갯수
‘속도를 빠르게 하기 위해서 업데이트를 하지 않고 추출 진행
Application.ScreenUpdating = False
For i = 0 To UserForm1.ListBox1.ListCount – 1
If UserForm1.ListBox1.Selected(i) = True Then
sel_cnt = sel_cnt + 1 ‘ 선택된 리스트의 갯수를 확인, Listbox2의 위치 선정을 위해 계산
End If
Next i
‘ 선택한 데이터가 10개가 넘으면 경고
If sel_cnt > 10 Then
MsgBox “선택한 데이터가 10개가 넘어요”
Exit Sub
End If
UserForm1.ListBox2.Clear ‘ 기존 추출된 Data 삭제
UserForm1.ListBox2.AddItem “” ‘ 추출할 데이터 제목 최상단에 추가
‘ 제일 힘든 루틴이었어요. Listbox 열의 크기가 10개 밖에 넣질 못해서 그냥 offset로 데이터를 추출할 수는 있지만
‘ 선택되지 않은 데이터의 열들이 비고 10이 넘어가는 데이터는 추출을 하지 못해서 로직 짜느라 10년은 늙은 듯 …
‘ 더욱이 추출하는 데이터가 랜덤이라 수열을 만들 수도 없고 해서 ㅠㅠ
For j = 0 To UserForm1.ListBox1.ListCount – 1
If UserForm1.ListBox1.Selected(j) = True Then
ccnt = (ccnt Mod sel_cnt) + 1 ‘ 핵심 루틴, 잘 해석해서 보세요
UserForm1.ListBox2.List(0, ccnt – 1) = Range(“A1”).Offset(0, j)
End If
Next j
‘ 데이터를 추출하는 루틴입니다. 위는 제목 행을 추출하는 루틴이구요.
‘ 첫 행을 임의로 추가 추출하고 싶으시면 runit – 1 부분을 0으로 대치하시면 됩니다.
For i = runit – 1 To row_cnt Step runit
UserForm1.ListBox2.AddItem “”
For j = 0 To UserForm1.ListBox1.ListCount – 1
If UserForm1.ListBox1.Selected(j) = True Then
ccnt = (ccnt Mod sel_cnt) + 1 ‘ 핵심 루틴, 잘 해석해서 보세요
UserForm1.ListBox2.List(k + 1, ccnt – 1) = Range(“A2”).Offset(i, j)
End If
Next j
k = k + 1
Next i
End Sub
‘ 추출된 데이터를 시트에 뿌려줍니다.
Private Sub CommandButton2_Click()
Dim i As Integer, j As Integer
i = 0
j = 0
‘ 속도를 빠르게 하기 위해서 업데이트 제한, 아래를 주석 처리하면 데이터가 넣어지는 것이 보임
Application.ScreenUpdating = False
‘ 추출할 데이트를 담을 시트 선택
Sheets(“ExtItem”).Select
‘ 기존 추출된 데이터 삭제
Range(“A1”, Range(“A1”).End(xlToRight)).End(xlDown).Clear
‘ 추출한 데이터 시트에 넣기, 자주 사용하는 루틴이므로 기억해 두면 좋음
For i = 0 To UserForm1.ListBox2.ListCount – 1
For j = 0 To UserForm1.ListBox2.ColumnCount – 1
Cells(i + 1, j + 1) = UserForm1.ListBox2.List(i, j)
Next j
Next i
‘ 짠~하고 업데이트된 데이터 보이기
Application.ScreenUpdating = True
Unload UserForm1
End Sub
첨부자료) 20150406-일정행-임의열 데이터 추출
전체 소스는 위 내용이므로 직접 실습하시면서 만들어 보시는 것도 실력향상에 도움이 될 것 같네요.
By vinipapa • 무른모 • 0 • Tags: Excel, VBA, 데이터 추출, 엑셀, 일정행, 임의열