완성 예
품목별 월별 실적 데이터가 있습니다. 제목과 본문으로 되어 있고 하나의 열에는 같은 성격의 정보가 들어 있는 엑셀 표준 테이블이라고 할 수 있습니다. 표준 포맷을 따르고 있으므로 엑셀의 분석 도구를 이용하여 다양한 작업을 할 수 있습니다.
기초 데이터
하지만 아래와 같은 형태로 만들기는 쉽지 않습니다. 이번 시간에는 [완성 예]와 같이 품목이 같은 셀은 셀 병합을 하고 각 품목마다 부분합을 자동으로 삽입하는 코드를 작성해 보겠습니다.
완성 예
전체 코드 슬쩍 봐두기
워크시트 상태에서 ALT + F11 키를 눌러서 Visual Basic Editor 창을 엽니다. [삽입] - [모듈] 메뉴를 이용하여 모듈Module을 하나 삽입하고 코드를 작성합니다. 코드에 대한 설명은 이어서 자세히 소개하므로 이 단계에서는 전체적인 구조와 흐름을 개략적으로 파악하고 넘어가시면 됩니다. 너무 스트레스 받지 마시고...
코드가 평소보다 조금 길다고 너무 흥분(?)하지 말고 차근차근 접근하시기 바랍니다. 몇 가지 기능을 수행하다 보니 약간 길어졌습니다만 단계를 나눠서 분해하여 접근하면 크게 까다로울 것은 없으리라 생각합니다. 여기서는 4단계로 나눠서 접근해 보겠습니다.
Sub mergeItem()
''' ---------- (1단계) ----------
Dim shtX As Worksheet
Dim rTbl As Range
Dim rTblItem As Range
Dim rMerge As Range
Dim rX As Range
Dim iX As Integer
Dim lTotal As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Test").Delete
Worksheets("예제").Copy before:=Worksheets("예제")
Set shtX = ActiveSheet
shtX.Name = "Test"
Set rTbl = shtX.Range("A1").CurrentRegion
lTotal = Application.WorksheetFunction.Sum(rTbl.Columns(3))
rTbl.Sort key1:=rTbl.Cells(1), order1:=xlAscending, Header:=xlYes
Set rTblItem = rTbl.Columns(1).Offset(1).Resize(rTbl.Columns(1).Rows.Count - 1)
''' ---------- (2단계) ----------
For Each rX In rTblItem.Cells
If rMerge Is Nothing Then
Set rMerge = rX
Else
If rX = rMerge(rMerge.Cells.Count) Then
Set rMerge = rMerge.Resize(rMerge.Cells.Count + 1)
Else
rMerge.Merge
Set rMerge = rX
End If
End If
Next
rMerge.Merge
''' ---------- (3단계) ----------
For iX = rTblItem.Cells.Count To 1 Step -1
If iX = rTblItem.Cells.Count Then
Set rMerge = rTblItem.Cells(rTblItem.Cells.Count).MergeArea
GoTo K
End If
If rTblItem.Cells(iX) <> "" Then
If iX > 1 Then
rTblItem.Cells(iX).EntireRow.Insert
Set rMerge = rTblItem.Cells(iX).Offset(-1).MergeArea
K:
Set rMerge = rMerge.Resize(rMerge.Cells.Count + 1)
rMerge.Merge
With rMerge.Cells(rMerge.Cells.Count)
.Offset(, 1) = "소계"
.Offset(, 2) = Application.WorksheetFunction.Sum(rMerge.Cells(1).Offset(, 2).Resize(rMerge.Cells.Count))
.Offset(, 1).Resize(, 2).Interior.ColorIndex = 6
End With
End If
End If
Next
''' ---------- (4단계) ----------
rTblItem.CurrentRegion.Borders.LineStyle = xlSolid
With rTbl.Columns(3).Cells(rTbl.Rows.Count + 2)
.Value = lTotal
.BorderAround xlSolid
.Font.Bold = True
End With
With shtX.UsedRange
.Columns(1).VerticalAlignment = xlCenter
.Columns(3).NumberFormat = "#,###"
End With
Application.DisplayAlerts = True
End SubCODE
코드 해설 (1) ㅡ 등장 인물과 배역 설정
늘 그렇듯, 첫 번째로 해 줄 일은 등장 인물과 배역을 지정하는 일입니다. 변수를 선언하고 역할을 맡긴다는 뜻이지요.
Dim shtX As Worksheet ''' 새로운 시트에 접근하기 위한 워크시트 개체 변수
Dim rTbl As Range ''' 원본 테이블 영역에 접근하기 위한 레인지 개체 변수
Dim rTblItem As Range ''' 원본 테이블 중 '품목' 영역에 접근하기 위한 변수
Dim rMerge As Range ''' 같은 값을 가진 품목에 접근하기 위한 변수
Dim rX As Range ''' For Each ~ In ~ Next 순환문에서 사용될 flag 변수
Dim iX As Integer ''' For ~ Next순환문에서 사용될 flat 변수
Dim lTotal As Long ''' 실적 총계를 담아둘 변수
''' Test라는 이름의 시트가 이미 있다면 삭제
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Test").Delete
Application.DisplayAlerts = True
Worksheets("예제").Copy before:=Worksheets("예제") ''' 예제 시트를 해당 시트 앞에 복사
Set shtX = ActiveSheet ''' 예제 시트를 shtX 변수에 할당(assign)
shtX.Name = "Test" ''' 예제 시트 이름을 Test로 변경
Set rTbl = shtX.Range("A1").CurrentRegion ''' 데이터가 들어 있는 영역을 rTbl 변수에 할당
lTotal = Application.WorksheetFunction.Sum(rTbl.Columns(3)) ''' 총합계를 lTotal 변수에 저장
rTbl.Sort key1:=rTbl.Cells(1), order1:=xlAscending, Header:=xlYes ''' 데이터 정렬
Set rTblItem = rTbl.Columns(1).Offset(1).Resize(rTbl.Columns(1).Rows.Count - 1) ''' 제목을 제외한 품목 영역을 rTblItem 변수에 저장
해설
VB Editor에서 F8 키를 눌러서 단계별로 실행해 보면서 '직접 실행 창'을 통해 변수 값이나 범위를 확인해 보시면 이해가 빠릅니다.
코드 해설 (2) ㅡ 동일 품목 셀 병합
For Each ~ In ~ Next 문을 이용하여 현재 셀(rX)과 바로 윗 셀 값을 비교합니다. 두 값이 같으면 Resize 메서드를 이용하여 범위를 계속 넓혀 나가고 두 값이 달라지는 순간 셀 병합 처리를 하는 원리입니다. Resize 메서드의 성질을 이용하는 것이 핵심입니다.
For Each rX In rTblItem.Cells ''' 각 품목 셀에 차례로 접근
If rMerge Is Nothing Then ''' rMerge 변수에 아무 것도 들어 있지 않다면
Set rMerge = rX ''' rX 변수 값을 rMerge 변수에 할당
Else
If rX = rMerge(rMerge.Cells.Count) Then ''' rX 값과 그 윗 셀 값이 같다면
Set rMerge = rMerge.Resize(rMerge.Cells.Count + 1) ''' rMerge 변수 크기를 한 셀 확장
Else ''' 그렇지 않다면, 즉 rX 값과 그 윗 셀 값이 다르다면
rMerge.Merge ''' rMerge 영역을 병합 처리하고
Set rMerge = rX ''' rMerge 변수 초기화(다음 병합 작업을 위해)
End If
End If
Next
rMerge.Merge ''' 맨 마지막 항목을 병합하기 위한 과정
해설
여기까지 실행하면 그림과 같은 작업 상태가 됩니다.
품목명이 같은 셀 병합 완료
코드 해설 (3) ㅡ 품목별 소계 삽입
다음은 병합 된 셀 사이에 빈 행과 소계를 삽입하는 과정입니다. 여기서의 포인트는 For ~ Next 문을 이용하여 순환하되 셀의 맨 아래쪽에서부터 위로 진행한다는 점입니다. 만약 이것을 반대로 하면 작업 범위가 수시로 변하게 되어 상황이 아주 복잡해집니다.
For iX = rTblItem.Cells.Count To 1 Step -1 ''' 영역의 맨 아래 쪽에서 위로 접근
If iX = rTblItem.Cells.Count Then ''' iX 값이 영역 맨 아래 셀 위치와 같다면
Set rMerge = rTblItem.Cells(rTblItem.Cells.Count).MergeArea ''' 병합된 영역을 rMerge 변수에 할당하고
GoTo K ''' K라는 이름의 레이블(Label)로 이동
End If
If rTblItem.Cells(iX) <> "" Then ''' 셀 값이 공란이 아니라면
If iX > 1 Then ''' 표 제목은 작업 대상에서 제외하기 위한 조건 추가
rTblItem.Cells(iX).EntireRow.Insert ''' 병합된 영역 중 품목 값이 있는 셀 위치에 행 삽입
Set rMerge = rTblItem.Cells(iX).Offset(-1).MergeArea ''' 삽입한 행 바로 위의 병합된 영역 값을 rMerge 변수에 할당
K:
Set rMerge = rMerge.Resize(rMerge.Cells.Count + 1) ''' rMerge의 크기 확대(삽입한 행 영역 + 기존 rMerge)
rMerge.Merge ''' 셀 병합
''' rMerge 영역 아래 행에서 1열 오른쪽에 있는 셀에 '소계', 2열 오른쪽 셀에는 Sum 함수로 계산한 값 표시
With rMerge.Cells(rMerge.Cells.Count)
.Offset(, 1) = "소계"
.Offset(, 2) = Application.WorksheetFunction.Sum(rMerge.Cells(1).Offset(, 2).Resize(rMerge.Cells.Count))
.Offset(, 1).Resize(, 2).Interior.ColorIndex = 6
End With
End If
End If
Next
해설
하고자 하는 작업이 거의 완료되어 갑니다. 품목명이 같을 경우 하나만 남기고 병합처리 되었고 품목별로 소계도 삽입되었습니다.
코드 해설 (4) ㅡ 마무리
일부 영역에 서식을 지정하고 총합계를 삽입하여 코드를 완성합니다.
rTblItem.CurrentRegion.Borders.LineStyle = xlSolid ''' 품목 열에 괘선 그리기
With rTbl.Columns(3).Cells(rTbl.Rows.Count + 2) ''' 총합계를 삽입할 셀에 접근
.Value = lTotal ''' Sum 함수로 계산한 총합계 표시
.BorderAround xlSolid ''' 괘선 작성
.Font.Bold = True ''' 굵은 글꼴 지정
End With
With shtX.UsedRange ''' 작업 대상 시트에서 사용된 영역에 접근
.Columns(1).VerticalAlignment = xlCenter ''' 1열 글자를 수직 가운데 방향 정렬
.Columns(3).NumberFormat = "#,###" ''' 3열 숫자에 쉼표 형식 지정
End With
Application.DisplayAlerts = True ''' 경고 모드 초기화
해설
Resize, Offset, CurrentRegion 같은 몇 가지 메서드나 속성을 이해하였다면 나머지는 부단한 반복입니다. 여기에서 기본 초식을 익힌 다음 여러분 자신의 업무에 적용하려는 자세가 필수적입니다. 많이 활용해 보세요.