7월 17 2015
엑셀(EXCEL) – 동적이름정의-VLOOKUP(그림참조하는 법)-주문서 작성
http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3480070
팁란에 글을 올릴 재료가 없어서 심심하던 차에 아질게에 위의 질문이 올라왔습니다.
“아 제품 주문서를 엑셀로 만들려고 하는데 Asheet가 통합 주문서면 그 주문서에서 품목별 사진을
Bsheet ,Csheet 이런식으로 장당 그림을 넣어놧거든요 근데 보기도 안좋고 해서 그림넣어둔 sheet를
숨기거나 하고싶어서요”
어떻게 풀어야할지 고민을 좀 해봤습니다. 그리고 구걸신에에 또 구걸을 좀 해 봅니다.
사실 아이디어를 찾으면 금방 해결될 문제이고 그 방법을 현실에 맞게 바꾸어 주면 됩니다.
주문을 하다 보면 가끔 각 제품의 주문을 잘못 내는 경우가 생깁니다. 그래서 그 제품을 선택하면
제품 사진을 보여주어 명확히 하면 제품 주문의 오류를 막아줄 수 있습니다. 위의 첨부 그림과 같이
제품을 선택하면 제일 오른쪽 칼럼에 사진을 보여 주도록 VBA를 작성할 예정입니다.
첫번째로 제가 올리는 팁에서 자주 사용하는 동적이름 정의를 이용할 예정입니다.
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,4)
위의 내용을 설명해 보면 Sheet2의 A2에서 아래로 셀의 내용이 있는 부분과 오른쪽으로 4칼럼을
자동적으로 취하여 PicTable라는 이름의 내용으로 변경합니다.
두번째로 VLOOKUP 함수를 사용, 동적이름정의로 정의된 부분에서 A열의 이름과 같은 그림의
이름을 취해서 그 그림의 이름과 같은 그림을 VBA함수를 이용하여 나머지 그림은 숨기고 같은
이름의 그림만 보여줍니다.
=VLOOKUP(A2, PicTable, 2, FALSE)
세번째 VBA함수 입니다. 위의 설명대로 과일그림을 과일 이름과 비교하여 같은 것만 보여줍니다.
Private Sub Worksheet_Calculate()
Dim ObjPic As Picture
‘시트의 모든 그림을 숨깁니다.
Me.Pictures.Visible = False
‘현재 셀에서 오른쪽으로 6째 칼럼의 이름과 같은 그림이름을 비교하여 보여줍니다.
With ActiveCell.Offset(0, 6)
For Each ObjPic In Me.Pictures
If ObjPic.Name = .Text Then
ObjPic.Visible = True
ObjPic.Top = .Top
ObjPic.Left = .Left
ObjPic.ShapeRange.LockAspectRatio = msoFalse
ObjPic.Placement = xlMoveAndSize
ObjPic.ShapeRange.Width = .Width
ObjPic.ShapeRange.Height = .Height * 8
‘같은 그림이름을 발견하면 루프를 빠져나갑니다.
Exit For
End If
Next ObjPic
End With
End Sub
그냥 예제로 간단하게 오리는 것이므로 첨부된 화일에서 양식 및 에러처리는 하지 않았습니다.
첨부화일을 보시면서 조금씩 공부하고 해석하시면 멋진 주문서를 만들어 사용하실 수 있을 것으로
판단되어(사실 귀차니즘) 그냥 올려 드립니다.
첨부 : 20150717-동적이름정의-VLOOKUP(그림참조)-주문서
3월 18 2016
엑셀(EXCEL) – 동적이름정의, VLOOKUP을 이용한 그림 참조 및 공과잡비 자동계산 견적서
질문이 간단하면 해결이 어려운 것 같습니다. 장문의 질문을 보면 어느 정도 해결의
실마리를 질문자가 알고 계셔서 아이디어만 추가하면 되는데 간결한 질문의 경우는
처음부터 시작해야해서 시간이 많이 걸리고 변수들이 많습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3679593
자동으로 단가란에 바로 전 셀까지의 합계 * 0.1의 값이 표시되게 하고 싶습니다.
처음에는 간단하게 접근하고 그냥 기본 데이터 양식처럼 처리해서 어느 정도 해결책을
찾았는데 기존 어떤 양식에도 바로 처리할 수 있게 하다보니 정말 문제점이 많더군요.
제가 기존에 올린 여기 아이디어를 이용해 견적 오류를 줄이는 것으로 시작했습니다.
(엑셀(EXCEL) – 동적이름정의-VLOOKUP(그림참조하는 법)-주문서 작성)
우선 사용자 정의 함수를 이용해서 처리하기로 합니다. 기존 리스트 형식의 데이터로
처리하면 간단한데 견적서 양식에 바로 처리하려니 군더더기가 많은 코드가 만들어
졌습니다. 리스트 양식으로 처리하고 셀 링크를 만들어 사용하는 것을 권합니다.
일단 모듈 하나 삽입하시고 사용자 정의 함수를 만듭니다. 내용은 코멘트로 처리해
두었으니 천천히 살펴 보시고 이해하시면 되겠습니다.
Option Explicit
Function EtcCost() As Double
Dim rcnt As Integer
Dim rngSum As Double
Dim sht As Worksheet
Dim cell As Range, rngcell As Range
rngSum = 0
Set sht = Sheets(“견적서”)
‘ 더해야할 위치값 찾기
rcnt = 10 + Application.CountA(sht.Range(“F11”, sht.Range(“F26”).End(xlUp))) – 1
‘ 위치값이 현재값과 같거나 적으면 처리 안 함
If rcnt < 10 Then Exit Function
‘ 더할 위치 지정
Set rngcell = sht.Range(“I11”, sht.Range(“I” & rcnt))
‘ 순환하면서 합산
For Each cell In rngcell
rngSum = rngSum + cell.Value
Next cell
‘ 결과값 리턴
EtcCost = rngSum * 0.1
Set rngcell = Nothing
End Function
그리고 견적서 시트의 처리 코드를 입력합니다. 하나는 그냥 숨어있는 그림을 숨기고,
하나는 다 보여서 그림의 이름 정의 등이 필요할 때 사용합니다. 그리고 워크시트가
변할 때마다 VLOOKUP 함수를 사용해서 특정 셀의 이름과 그림의 이름이 같으면
보여주게 하는 것입니다.
Option Explicit
Private Sub Show_Pic_All()
Me.Pictures.Visible = True
End Sub
Private Sub Hide_Pic_All()
Me.Pictures.Visible = False
End Sub
Private Sub Worksheet_Calculate()
Dim ObjPic As Picture
Me.Pictures.Visible = False
With ActiveCell.Offset(0, 17)
For Each ObjPic In Me.Pictures
If ObjPic.Name = .Text Then
ObjPic.Visible = True
ObjPic.Top = .Top
ObjPic.Left = .Left + 5
ObjPic.ShapeRange.LockAspectRatio = msoFalse
ObjPic.Placement = xlMoveAndSize
ObjPic.ShapeRange.Width = .Width – 5
ObjPic.ShapeRange.Height = .Height * 5
Exit For
End If
Next ObjPic
End With
End Sub
그리고 단가 부분에 아래의 함수를 사용합니다.
=IF(ISBLANK(F14), “”,IF(F14=”공과잡비”,EtcCost(),VLOOKUP(F14, PicTable, 3, FALSE)))
유효성 검사를 사용한 목록에서 ‘공과잡비’란 항목이 선택되면 사용자 정의 함수를 불러와서
계산하고 아니면 VLOOKUP함수를 처리하는 것입니다. 그리고 나머지 셀에도 VLOOKUP을
처리해서 자동으로 단가, 기타 내용을 추가하시면 됩니다. 첨부 화일 참조하세요.
최대한 오류를 줄일려고 했는데 오류가 있으시면 연락? 코멘트 달아 주세요!
첨부 : 20160316-그림 참조 동적 주문서(Form)
By vinipapa • 무른모 • 0 • Tags: VLOOKUP, 견적서, 그림 참조, 동적이름정의, 부과세 자동, 엑셀