견적서

질문이 간단하면 해결이 어려운 것 같습니다. 장문의 질문을 보면 어느 정도 해결의
실마리를 질문자가 알고 계셔서 아이디어만 추가하면 되는데 간결한 질문의 경우는
처음부터 시작해야해서 시간이 많이 걸리고 변수들이 많습니다.

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)