아이엑셀러 닷컴

질문 하나 & 완성 예

아이엑셀러 닷컴에서 열심히 공부하는 어느 분의 질문입니다. 아마도 테니스 동호회에서 총무 역할을 담당하시나 봅니다. 질문 내용을 요약하면 이렇습니다.

각 회원 별로 10게임씩 경기를 하며 경기마다 승/패를 기록한 시트가 있습니다. N열에는 회원 별로 몇 연승했는지 기록하고 싶다는 겁니다. 지금까지는 매번 일일이 카운팅해서 입력하고 있는데 이걸 간단히 해결할 방법이 없을까 하는 질문입니다.

질문 내용

수식으로도 가능은 해 보입니다만 아주 길고 복잡한 수식이 될 것 같습니다. 그런 수식을 만들 노력과 정성이라면 VBA로 코딩하는 것이 한결 편리하고 효율적이리라 생각합니다. '연승기록' 시트에서 '연승 기록 체크' 버튼을 클릭하면 원하는 대로 결과가 나옵니다.

완성 예

뿐만 아니라 승리 내역(O)에는 빨간 글자색으로, 연승 기록에는 빨간 배경색에 흰 글씨로 하이라이팅 표시되어 있습니다. 이런 것은 함수로는 도저히 할 수 없는 일이지요.

코드 작성

워크시트에서 ALT + F11 키를 눌러서 Visual Basic Editor 창을 엽니다. VB Editor에서 [삽입] - [모듈] 메뉴를 선택하여 모듈^Module을 삽입하고 코드를 작성합니다. 코드에 대한 자세한 설명은 이어서 나옵니다. 길고 복잡한 코드라도 나누고 쪼개어(같은 말이네...) 접근하면 이해하기 쉬워집니다.

Sub successiveWin()
    ''' ---------- (1단계) ----------
    Dim shtX As Worksheet
    Dim rTbl As Range
    Dim rAreas As Range
    Dim rArea As Range
    Dim rA As Range
    Dim rWin As Range
    Dim rRow As Range
    Dim rX As Range
    
    On Error Resume Next
    Set shtX = Worksheets("연승기록")
    Set rTbl = shtX.Range("myTable").CurrentRegion
    Set rTbl = rTbl.Offset(1, 1).Resize(rTbl.Rows.Count - 1, rTbl.Columns.Count - 1)
    
    ''' ---------- (2단계) ----------
    With rTbl
        .Interior.ColorIndex = xlNone
        .Font.ColorIndex = 1
        With .Cells(.Columns.Count)
            .Offset(, 2).CurrentRegion.Clear
            .Offset(, 2).EntireColumn.HorizontalAlignment = xlCenter
            .Offset(-1, 2) = "연승 기록"
        End With
    End With

    ''' ---------- (3단계) ----------
    For Each rRow In rTbl.Rows
        For Each rX In rRow.Cells
            If rX = "O" Then
                If rArea Is Nothing Then
                    Set rArea = rX
                Else
                    Set rArea = rArea.Resize(, rArea.Cells.Count + 1)
                End If
            Else
KKK:
                If rAreas Is Nothing Then
                    Set rAreas = rArea
                Else
                    Set rAreas = Union(rAreas, rArea)
                End If
                Set rArea = Nothing
            End If
        Next
        If Not rArea Is Nothing Then GoTo KKK
        
    ''' ---------- (4단계) ----------
        For Each rA In rAreas.Areas
            rA.Font.ColorIndex = 3
            rA.Font.Bold = True
            If rWin Is Nothing Then
                Set rWin = rA
            Else
                If rWin.Cells.Count <= rA.Cells.Count Then Set rWin = rA
            End If
        Next
        
    ''' ---------- (5단계) ----------
        rWin.Interior.ColorIndex = 3
        rWin.Font.ColorIndex = 2
        rRow.Cells(rRow.Cells.Count).Offset(, 2) = rWin.Cells.Count
        Set rWin = Nothing
        Set rAreas = Nothing
    Next
End Sub

CODE

코드 해설

1. 첫 번째 단계는 변수를 선언하고 필요한 값이나 영역을 지정하는 작업입니다.

Dim shtX As Worksheet        ''' 워크시트 개체에 접근할 변수
Dim rTbl As Range            ''' 대진 결과표에 접근할 변수
Dim rAreas As Range          ''' 승리(O)한 영역 전체에 접근할 변수
Dim rArea As Range           ''' 승리한 영역에 개별적으로 접근할 변수
Dim rA As Range              ''' 승리 영역에 접근할 순환 변수
Dim rWin As Range            ''' 최장 연속 승리 영역에 접근할 변수
Dim rRow As Range            ''' 대진 결과표의 행 단위로 접근할 변수
Dim rX As Range              ''' 개별 셀에 접근할 순환 변수

On Error Resume Next         ''' 실행 도중 오류가 발생하더라도 다음 명령 수행
Set shtX = Worksheets("연승기록")
Set rTbl = shtX.Range("myTable").CurrentRegion        ''' myTable의 인접 영역을 rTbl 변수에 할당
Set rTbl = rTbl.Offset(1, 1).Resize(rTbl.Rows.Count - 1, rTbl.Columns.Count - 1)
                                    ''' 대진 결과표의 본문 영역을 rTbl 변수에 할당

해설

2. 대진 결과표의 서식을 초기화합니다.

With rTbl
    .Interior.ColorIndex = xlNone        ''' 셀 배경색 초기화
    .Font.ColorIndex = 1                 ''' 글자색 검정색으로 지정
    With .Cells(.Columns.Count)
        .Offset(, 2).CurrentRegion.Clear ''' 연승 내역을 기록할 주변 영역 지우기
        .Offset(, 2).EntireColumn.HorizontalAlignment = xlCenter
        .Offset(-1, 2) = "연승 기록"
    End With
End With

해설

3. For Each ~ In ~ Next 문을 두 번 중첩합니다. 바깥쪽 순환문은 테이블의 행 단위로, 안쪽 순환문은 각 행의 셀 단위로 접근합니다. 이곳이 전체 코드의 핵심입니다. rArea와 rAreas 변수에 어떤 영역이 이미 할당되어 있는지 아닌지 여부에 따라 새로운 영역을 업데이트 해주는 부분을 눈여겨 보시기 바랍니다.

For Each rRow In rTbl.Rows        ''' 테이블의 개별 행 단위로 접근
  For Each rX In rRow.Cells       ''' 개별 행의 각 셀 단위로 접근
      If rX = "O" Then            ''' 셀 값이 O(승리)라면...

        ''' rArea 변수에 아무 것도 할당되어 있지 않다면 rX를,
        ''' 이미 어떤 영역이 할당되어 있다면 다음 셀까지로 범위 확장
        If rArea Is Nothing Then
              Set rArea = rX
          Else
              Set rArea = rArea.Resize(, rArea.Cells.Count + 1)
          End If
      Else
KKK:
          ''' rAreas 변수에 아무 것도 할당되어 있지 않다면 rArea 변수 값을,
          ''' 이미 어떤 영역이 할당되어 있다면 기존 rAreas 영역과 rArea 영역 통합
          If rAreas Is Nothing Then
              Set rAreas = rArea
          Else
              Set rAreas = Union(rAreas, rArea)
          End If
          Set rArea = Nothing               ''' rArea 변수 초기화
      End If
    Next
    If Not rArea Is Nothing Then GoTo KKK    ''' 마지막 rArea 영역을 처리하기 위해 KKK 레이블로 이동

해설

4. 승리한 셀이 담긴 영역에 접근하여 서식을 지정합니다. 또한 rWin과 rA 영역을 비교하여 가장 긴 연승 기록에 해당하는 영역을 rWin 변수에 담아둡니다.

    For Each rA In rAreas.Areas         ''' rAreas의 각 영역(Area)에 접근
      rA.Font.ColorIndex = 3            ''' rA, 즉 값이 O인 셀의 글자색을 빨간색으로 지정
      rA.Font.Bold = True               ''' 굵은 글꼴로 지정

      ''' rWin 변수에 아무 것도 할당되어 있지 않다면 rA 변수 값을,
      ''' 이미 어떤 영역이 할당되어 있다면 rWin과 rA 영역의 크기를 비교하여 큰 영역을 rWin 변수에 할당
      If rWin Is Nothing Then        
          Set rWin = rA
      Else
          If rWin.Cells.Count <= rA.Cells.Count Then Set rWin = rA
      End If
    Next

해설

여기까지 진행되었다면 테이블의 첫 번째 행에 대한 처리가 끝납니다. 그 결과는 아래 그림과 같습니다. 셀 값이 O라면 굵은 빨간색으로 표기해 줍니다. 빨간 점선으로 표기한 영역이 각각의 rA에 해당합니다.

5. 마지막으로, 가장 긴 연승 기록 영역에 서식을 지정합니다.

    rWin.Interior.ColorIndex = 3      ''' 셀 배경색을 빨간색으로 지정
    rWin.Font.ColorIndex = 2          ''' 글자색을 흰색으로 지정
    rRow.Cells(rRow.Cells.Count).Offset(, 2) = rWin.Cells.Count    
                      ''' 각 행의 가장 오른쪽 셀에서 2열 더 오른쪽으로 이동하여 연승 기록 삽입
    Set rWin = Nothing                ''' rWin 변수 초기화
    Set rAreas = Nothing              ''' rAreas 변수 초기화
Next

해설

중첩 For ~ Next 문이 사용되어 처음 보면 좀 복잡해 보일 수 있습니다만 VBA 디버깅 툴을 이용하여 진행 과정을 몇 차례 추적해 보면 이해하시리라 생각합니다. 이와 관련해서는 다음 강의를 참고하세요.

버그를 줄이는 5가지 방법 및 엑셀의 디버깅 툴

#엑셀_대진표 #Resize #Area #Union

들어가기 전에 ㅡ 그 지긋지긋하던 '경우의 수' 징크스를 깨다

질문 하나 & 완성 예

코드 작성

코드 해설