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(그림참조)-주문서
8월 5 2015
엑셀(EXCEL) – VBA, OptionButton을 이용한 책갈피 기능 사용
내일이 휴가인데 요사이 경기가 좋지않아 현장직 사원분들은 강제로 오늘부터 휴무에 들어가서
사무직 직원들만 출근했는데 협력 업체 대부분도 휴가 중이고 공장은 돌아가지 않으니 사무실이
적막강산이고 월급루팡 하는 중에 아질게에 아래 질문이 올라와 그냥 뚝딱뚝딱 만들어 봤습니다.
http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3499667
그냥 질문의 내용을 간단히 해석하면 책갈피 기능을 원한다는 말씀 같아서 그렇게 만들었습니다.
내용은 아주 간단합니다.
엑셀 > 보기에 첫행 고정으로 틀고정 하시고 책갈피를 원하는 수만큼 OptionButton을 만듭니다.
(개발도구, 디자인모드로 선택하시고 옆의 삽입에서 AcitveX Control의 Optionbutton을 선택합니다)
개발도구에서 Visual Basic을 선택하시고 들어가 Sheet1을 더블 클릭하시고 아래를 복사합니다.
Option Explicit
Private Sub Move_Active_Cell()
Worksheets(“Sheet1”).Activate
Select Case True
Case OptionButton1.Value
Worksheets(“Sheet1”).Range(“A20”).Select
ActiveCell.Interior.ColorIndex = 1
Case OptionButton2.Value
Worksheets(“Sheet1”).Range(“A50”).Select
ActiveCell.Interior.ColorIndex = 3
Case OptionButton3.Value
Worksheets(“Sheet1”).Range(“A70”).Select
ActiveCell.Interior.ColorIndex = 4
Case OptionButton4.Value
Worksheets(“Sheet1”).Range(“A90”).Select
ActiveCell.Interior.ColorIndex = 5
Case OptionButton5.Value
Worksheets(“Sheet1”).Range(“A110”).Select
ActiveCell.Interior.ColorIndex = 5
End Select
End Sub
Private Sub OptionButton1_Click()
Call Move_Active_Cell
End Sub
Private Sub OptionButton2_Click()
Call Move_Active_Cell
End Sub
Private Sub OptionButton3_Click()
Call Move_Active_Cell
End Sub
Private Sub OptionButton4_Click()
Call Move_Active_Cell
End Sub
Private Sub OptionButton5_Click()
Call Move_Active_Cell
End Sub
소스는 그냥 간단하게 버튼이 눌러질 때마다 함수를 호출에서 참 값인 OptionButton의 명령을 실행합니다.
이렇게 하면 버튼이 늘어도 함수만 수정해 주면 되기 때문에 확장과 수정이 편리한 면이 있습니다. 추가로
함수에 위치에 따라 색상이 달라지도록 할 수도 있지요. 책갈피의 기능이기때문에 그 위치는 이동만 하고
나머지 셀에서 데이터를 운용하도록 합니다. 우리가 흔히 책갈피의 색을 다르게 끼우는 것처럼 말입니다.
도움이 되었으면 하네요.
엑셀 자료 : 20150805-선택적 이동(책갈피)
By vinipapa • 무른모 • 0 • Tags: 심심해, 엑셀, 책갈피, 팁