엑셀을 이용하는 목적이 대부분은 숫자를 다루는 일에서 출발을 합니다. 그래서 초급자분들은
아주 넓고 편한 계산기에서 출발해서 나중에는 표다루기가 무지 편한 워드로 발전을 합니다. ㅋㅋㅋ
엑셀은 원래 목적인 숫자를 다루기도 수월하지만 사실을 데이터를 다룬다고 보는 것이 좋을 듯 합니다.
그래서 그 데이터를 다룰 목적으로 단가나 기타 제품 이력 관리를 하기도 하지요. 그런데 이 제품들의
가격들이 환율이나 기타 원가 변동 등으로 수시로 업데이트가 되고 제품들이 첨삭되기도 하면 사용자가
일일이 원가를 DB에서 계산해서 Excel로 내려 받으면 아래로 긴 리스트로 받게 되는 경우가 많죠.
몇 가지 제품들을 다루고 계신다면 그냥 수작업으로 긴 리스트 형식에서 정형화된 양식에 입력하면
되지만 저희 회사같이 수천종을 생산하면 얘기가 달라집니다. 누가 농담처럼 LG생활건강의 제품을
다 외우면 임원시켜 준다고 하는데 저희 회사도 마찬가지입니다?. ㅠㅠ 특히 단가 승인 관련 보고시
자료에 아래로 죽~ 긴 리스트보다 정형화된 폼에 만들어 올리는 것도 보기가 좋고 종이도 절약!
즉 Vlookup등이나 찾기 함수를 넣어 리스트와 양식을 분리해서 리스트에만 입력하면 자동으로
찿기를 할 수도 있지만 제품이 추가되거나 삭제되면 보기싫은 #N/A 등이 나타날 수 있으므로
물론 코드 처리를 해서 삭제된 제품은 처리 가능하지만 추가된 부분은 또 수작업이 필요함으로
특히 제품리스트를 정렬해서 바로바로 볼 수 있게 하려면 찾기 함수로는 어려운 부분이 많음.
서두가 길어졌습니다.
요약하면 동적이름을 정의하여 제품의 첨삭이 일어나더라도 자동으로 제품의 리스트 영역을 선택하고
UserForm, Multi Column Listbox를 사용 동적이름으로 정의된 리스트를 가져오고 그것을 지정된
(양식) 시트에 뿌려서 종이도 절약하고 깔끔하게 만들어 프린터하고 가지고 다니면서 볼 수 있습니다.
단가표나 다른 형태의 반복적인 작업을 할 때 아주 유용한 방법이 될 수 있을 것으로 보입니다.
1. 동적 이름 정의
=OFFSET(대상 셀, 행 방향 이동 수, 열 방향 이동 수, 행 방향 취할 값, 열 방향 취할 값)
설명을 드리자면 $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
동적이름정의- 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
팁과 강좌 게시판에 글을 올리다 보니 재미있어서 실생활에 활용될 수 있는 자료들을 찿아
예전에 만들어 두었던 자료를 수정해서 올리고 있는데 회사생활에 도움이 되었으면 합니다.
By vinipapa • 무른모 • 0 • Tags: 리스트정리, 멀티컬럼리스트, 양식, 엑셀