4월 2 2015
동적이름정의- UserForm-Multi Column Listbox활용하여 리스트를 양식에 맞춰 넣기
요새 제가 많이 바쁜가 봅니다? 항상 바쁘면 더 엉뚱한 일을 더 하고 싶거든요.
첨부 화일
20150402-동적이름정의- UserForm-Multi Column Listbox활용하여 리스트를 양식에 맞춰 넣기
엑셀을 이용하는 목적이 대부분은 숫자를 다루는 일에서 출발을 합니다. 그래서 초급자분들은
아주 넓고 편한 계산기에서 출발해서 나중에는 표다루기가 무지 편한 워드로 발전을 합니다. ㅋㅋㅋ
엑셀은 원래 목적인 숫자를 다루기도 수월하지만 사실을 데이터를 다룬다고 보는 것이 좋을 듯 합니다.
그래서 그 데이터를 다룰 목적으로 단가나 기타 제품 이력 관리를 하기도 하지요. 그런데 이 제품들의
가격들이 환율이나 기타 원가 변동 등으로 수시로 업데이트가 되고 제품들이 첨삭되기도 하면 사용자가
일일이 원가를 DB에서 계산해서 Excel로 내려 받으면 아래로 긴 리스트로 받게 되는 경우가 많죠.
몇 가지 제품들을 다루고 계신다면 그냥 수작업으로 긴 리스트 형식에서 정형화된 양식에 입력하면
되지만 저희 회사같이 수천종을 생산하면 얘기가 달라집니다. 누가 농담처럼 LG생활건강의 제품을
다 외우면 임원시켜 준다고 하는데 저희 회사도 마찬가지입니다?. ㅠㅠ 특히 단가 승인 관련 보고시
자료에 아래로 죽~ 긴 리스트보다 정형화된 폼에 만들어 올리는 것도 보기가 좋고 종이도 절약!
즉 Vlookup등이나 찾기 함수를 넣어 리스트와 양식을 분리해서 리스트에만 입력하면 자동으로
찿기를 할 수도 있지만 제품이 추가되거나 삭제되면 보기싫은 #N/A 등이 나타날 수 있으므로
물론 코드 처리를 해서 삭제된 제품은 처리 가능하지만 추가된 부분은 또 수작업이 필요함으로
특히 제품리스트를 정렬해서 바로바로 볼 수 있게 하려면 찾기 함수로는 어려운 부분이 많음.
서두가 길어졌습니다.
요약하면 동적이름을 정의하여 제품의 첨삭이 일어나더라도 자동으로 제품의 리스트 영역을 선택하고
UserForm, Multi Column Listbox를 사용 동적이름으로 정의된 리스트를 가져오고 그것을 지정된
(양식) 시트에 뿌려서 종이도 절약하고 깔끔하게 만들어 프린터하고 가지고 다니면서 볼 수 있습니다.
단가표나 다른 형태의 반복적인 작업을 할 때 아주 유용한 방법이 될 수 있을 것으로 보입니다.
1. 동적 이름 정의
=OFFSET(대상 셀, 행 방향 이동 수, 열 방향 이동 수, 행 방향 취할 값, 열 방향 취할 값)
응용법 (아래 Data!딸라A딸라2,입니다. 이상하게 보이는군요)
=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),3)
설명을 드리자면 $A$2에서 0행 0열을 움직여서 A열을 데이터가 들어있는 부분만 선택
즉 그 행까지 취하고 $A$2에서 3열의 영역을 취해서 동적 이름으로 정의하는 것입니다.
데이터가 아래로 추가되면 카운터가 올라가고, 삭제되면 줄어 들어 자동으로 영역 선택
엑셀 2007기준
수식 > “이름 관리자” 나 “이름 정의”를 선택해서 이름을 정의하고 참조 대상에 저 함수를
넣어 단가나 기타 데이터를 선택합니다. 그러면 저 영역 A열에 데이터가 추가되면 자동으로
Count가 늘어나서 영역이 넓어지고 삭제되면 줄어드는 원리입니다.
2. VBA 활용
개발도구 > Visual Basic을 선택하고 삽입 > 사용자 정의폼 을 선택해서 UserForm 추가
도구 상자가 보이지 않으면 보기 > 도구 상자를 선택
목록 상자를 선택해서 UserForm에 하나를 만들고 선택, 속성창에서 RowSource에
아까 동적 이름으로 정의된 이름을 입력함, Column Count 에 3을 입력
명령 단추를 하나 만들고 아래 코드를 삽입
Private Sub CommandButton1_Click()
Dim i, j, k, row, unit As Integer
i = 0 ‘ list순서
j = 0 ‘ 행위치
k = 0 ‘ 단위 조직 위치
row = 0 ‘ 단위표의 데이터 위치
unit = 50 ‘ 단위표당 데이터 갯수
‘ 빨리 재배치를 할 경우, 채워지는 애니메이션을 보고 싶은 경우 주석 처리
Application.ScreenUpdating = False
‘ 데이터 숫자 카운터, 제목이 있어서 A2부터 선택
Sheets(“Data”).Select
row_cnt = Application.CountA(Range(“A2”, Range(“A2”).End(xlDown)))
‘ 출력된 양식 시트 선택
Sheets(“PrtForm”).Select
‘ 기존 데이터 지우기, 양식은 보존
Range(“A1:HH55”).ClearContents
‘ 핵심 코드, 돌머리 굴리느라 힘들었어요 엉엉엉
With ListBox1
For i = 0 To .ListCount – 1
For row = 0 To row_cnt Step unit
Select Case i
Case Is < unit * (i + 1)
‘ 단위표의 크기에 따라 몫을 구해서 표의 위치 결정
k = i \ unit
‘ 단위표의 몫을 구해 한 단위표의 위치를 정함
j = i – (unit * (i \ unit))
End Select
Next row
‘ 제목행 반복 삽입
‘ 기존 데이터 지우는 코드를 A1을 A2로 바꾸면 되는데 돌머리 또 엉엉엉
‘ 데이터 열이 늘어나면 지속적으로 추가
‘ 물론 k * 4의 4도 변경해야 함. 양식에 빈 열이 하나 넣어 보기 좋게 함
Cells(1, 1 + k * 4) = “코 드 ”
Cells(1, 2 + k * 4) = “제품명”
Cells(1, 3 + k * 4) = “단 가”
‘ Cells(1, 4 + k * 4) = “비 고”
‘ 데이터 채우기
‘ 제목행이 있으므로 2행 부터 추가됨
Cells(2 + j, 1 + k * 4) = .List(i)
Cells(2 + j, 2 + k * 4) = .List(i, 1)
Cells(2 + j, 3 + k * 4) = .List(i, 2)
‘ Cells(2 + j, 3 + k * 4) = .List(i, 3)
Next i
End With
‘ 이 앞에 까지는 눈에 보이지 않음. 완료된 시트 업데이트해서 보기
Application.ScreenUpdating = True
Unload UserForm1
End Sub
3. Data 시트 선택, 개발도구에서 삽입, ActiveX 컨트롤-> 명령 단추 선택해서 삽입
디자인 모드 선택 > 명령 단추 더블 클릭해서 아래 코드 삽입
Private Sub CommandButton1_Click()
Load UserForm1
UserForm1.Show
End Sub
팁과 강좌 게시판에 글을 올리다 보니 재미있어서 실생활에 활용될 수 있는 자료들을 찿아
예전에 만들어 두었던 자료를 수정해서 올리고 있는데 회사생활에 도움이 되었으면 합니다.
4월 2 2015
엑셀(EXCEL) – 가로 세로로 구성된 데이터에서 일정 간격의 행, 열 데이터 추출하기
20150402-일정 행열마다 데이터 추출 <— 첨부 화일
엑셀을 다루고 계시지만 초보자분들께서는 아직도 계산기능으로만 사용하시고
고급기능을 잘 모르시니 많은 데이터를 다루실 때는 많은 시간을 들이시는가 봅니다.
대부분이 표에다가 산술식으로 자동계산기를 이용하시고 조금 더 발전하시면
데이터 검색 함수를 응용해서 잘 다루시는 것 같군요.
아래에다 실무에 필요한 팁을 올렸는데 처음 데이터를 구성(엑셀에 입력)하실 때
잘못 입력해 놓으면 데이터의 활용이 상당히 어렵게 됩니다. 보통 전임자가 생각없이
표나 시트를 만들어 계속 사용하다 데이터가 많이 쌓이면 고칠 엄두가 나질 않아서
그대로 사용하시는 것 같더군요.
아래 팁의 댓글에서 일정하게 반복되는 행열에서 데이터를 추출하시는 분이 계시는데
추출 방법을 잘 몰라서 업무에 애로가 많으신가 봅니다. 별로 어려운게 아니라서 짬을 내어
VBA로 만들었어요. 참고가 되기를 바랍니다.
1. 이번에는 동적 이름을 이용하지 않고 엑셀의 기본 기능을 이용하여 검색할 영역을 지정
떨어지지 않은 특정영역의 데이터 숫자를 세는 방법으로 A2에서 아래, 오른쪽 끝까지의
데이터 숫자를 세어 줍니다.
row_cnt = Application.CountA(Range(“A2”, Range(“A2”).End(xlDown)))
col_cnt = Application.CountA(Range(“A2”, Range(“A2”).End(xlToRight)))
2. 서브루틴을 사용하지 않고 바로 한 프로시져에 넣어도 되는데 나중을 고려해서
서브루틴으로 사용했어요. 보시면 아시겠지만 간단합니다. 행열의 데이터 크기를 알려주고
행열의 단위를 알려주면 루프를 돌면서 데이터를 추출해서 Listbox에 넣어 줍니다.
속성창에서 Listbox의 Column Count는 2로 해 줍니다.
k = 0
‘ 반복 검색이 필요하므로 리스트박스를 클리어 합니다.
UserForm1.ListBox1.Clear
For i = runit To rcnt Step runit
For j = cunit To ccnt Step cunit
UserForm1.ListBox1.AddItem “(” & i & “x” & j & “)”
UserForm1.ListBox1.List(k, 1) = Cells(i, j).Value
‘ 행을 추가해야 하므로
k = k + 1
Next j
Next i
3. Listbox에 추출된 데이터를 엑셀 시트로 보내는 서브루틴입니다.
Sheets(“Extract”).Select
‘ 기존 데이터를 지워 줍니다.
Range(“A1”, Range(“B1”).End(xlDown)).Clear
Application.ScreenUpdating = False
With UserForm1.ListBox1
For i = 0 To .ListCount – 1
Cells(j + 1, 1) = .List(i, 0)
Cells(j + 1, 2) = .List(i, 1)
j = j + 1
Next i
End With
간단하게 추출할 행과 열을 넣고 검색, 추출하면 됩니다.
엑셀 자료는 제 블로그에 올립니다.
By vinipapa • 무른모 • 0 • Tags: VBA, 데이터 추출, 엑셀, 일정 행열