7월 8 2015
엑셀(EXCEL) – 일정한 간격의 행을 번갈아서 칠하기
몇 달전 엑셀 자료를 편집하면서 흰색인 기타 색으로 아래로 쭉 리스트 된 자료를 편하게
편집하고 볼 수 있도록 아래의 팁을 올린 계기로 업무에 도움이 될만한 팁들을 지속적으로
올리고 있습니다. 그런데 저는 불편함이 없이 여러 업무에 필요할 때마다 VBA를 사용하여
자료를 정리하고 있어 팁을 올릴 계기가 없었습니다.
엑셀(EXCEL) – 자료보기 편하게 번갈아 가면서 행 배경색 자동으로 넣기
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=265108
오늘 클리앙 메모로 어떤 분이 위 팁을 사용하고 계시는데 조금 더 응용할 방법을 찾으시다가
본인이 해결하기 힘드시다고 방법을 요청해서 조금 변형해서 올립니다.
“안녕하세요. 엑셀팁 유용하게 쓰고 있습니다. 감사해요.
혹시 5개 단위로 색을 달리하는 조건부서식용 수식이 있을까요?
1-5행은 흰색, 6-10행은 녹색, 다시 11-15행은 흰색… 이런식으로요
많이 고민해 봤는데 좋은 방법을 모르겠기에 도움을 청합니다.”
=IF(MOD(ROW(A1),2)=0,1,0)
위 팁의 간단한 조건부 서식의 내용입니다. 현재 행을 2로 나누어 0과 같으면 조건부 서식대로
색상, 글꼴 등을 변형시키고 1이면 그냥 셀을 흰색으로 보여줍니다.
이제 조금 변형을 해 볼까요?
=QUOTIENT(ROW(A2)) 현재 행의 몫의 정수 부분을 보여줍니다. D2행에서 보이는 것처럼
아래로 수식을 복사하면 각 행을 5로 나눈 몫의 정수 부분을 보여줍니다. 첨부의 그림처럼
규칙이 일정하지를 않습니다.
조금 더 변형을 해 볼까요?
=QUOTIENT(ROW(E2)-2,5)
현재 행에서 2를 빼고 5로 나눈 몫의 정수 부분을 보여줍니다. 이제 일정한 규칙이 보입니다.
=MOD(QUOTIENT(ROW(A2)-2,5),2)
제일 최초의 팁에서 응용한 수식을 입력하고 필요한 부분에 수식 복사를 합니다.
간단하게 그냥 =MOD(QUOTIENT(ROW(A2),5),2)를 하면 번갈아서 색을 조건부 수식에 따라
보여주게 되는데 대부분의 엑셀을 사용하는 사용자라면 제목을 사용하고 데이터를 아래로 쭉
나열해서 자료를 정리하므로 제목행에 대해 예외 처리를 하기 위해서 입니다.
잘 사용하셨으면 합니다. 언제나 소스?는 여기 올리고 엑셀 자료는 블로그에 첨부해 둡니다.
첨부 : 20150708-일정한 간격의 행을 번갈아서 칠하기
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(그림참조)-주문서
By vinipapa • 무른모 • 0 • Tags: VLOOKUP, 그림 참조하는 법, 동적이름정의