목차 만들기 자동화 함수 :: 엑셀 CreateTOC 사용법 및 전체 코드

시트를 지정한 기준에 따라 그룹화 및 정렬하여 목차를 생성하는 CreateTOC 함수의 사용법 및 전체 명령문 동작원리를 알아봅니다.

홈페이지 » 목차 만들기 자동화 함수 :: 엑셀 CreateTOC 사용법

엑셀 목차 만들기 자동화 명령문 :: CreateTOC 함수 사용법 총정리

구문

CreateTOC ( [분류기준], [정렬방향], [돌아가기링크생성], [링크위치] )

패치노트
  • 2020.01.09 : 숨겨진 시트는 목록에서 제외되도록 명령문을 수정하였습니다.
  • 2020.01.17 : 엑셀 2007 에서도 사용가능하도록 추가기능 파일을 수정하였습니다.
  • 2020.02.11 : 숫자형식의 시트명도 올바르게 표시되도록 명령문을 수정하였습니다.
설명

엑셀 사용자지정함수인 CreateTOC 함수는 통합문서의 시트를 사용자 기준에 맞춰 자동으로 분류 및 정렬하여 목차를 생성하는 함수입니다. VBA 사용이 어려우신 분들을 위해  '추가기능' 파일 제공해드립니다. 추가기능을 다운받아 설치하시면 '목차만들기' 기능을 바로 사용하실 수 있습니다. '추가기능' 사용법은 영상강의에서 자세히 설명드렸습니다.

CreateTOC 함수는 아래 3가지만 숙지하면 어떤 상황에서도 목차를 쉽게 만들 수 있습니다.

설정1. 오름차순/내림차순 정렬

목차를 오름차순과 내림차순으로 정렬하여 생성합니다. 필요에 따라, 정렬없이 목차를 분류만 하여 생성할 수도 있습니다. 쉬운 예제로, [과일-사과], [채소-배추], [과일-딸기], [채소-당근] 순서로 시트가 입력되어 있다고 가정하겠습니다.

  1. 시트를 오름차순으로 정렬한 뒤 2자리를 기준으로 분류할 경우, 아래 순서로 목차가 생성됩니다..

    1 시트 정렬 후 목차 생성
    시트를 정렬한 뒤 분류하여 목차를 생성합니다.
  2. 시트를 정렬하지 않고 기존 순서 그대로 분류할 경우, 아래와 같이 목차가 생성됩니다.

    2 시트 정렬없이 목차 만들기
    시트를 정렬하지 않고, 나열된 순서 그대로 분류하여 목차를 생성합니다.

설정2. 목차 그룹화 (시트 분류)

시트를 분류하는 기준은 숫자(자리수) 또는 특수기호( '-' 또는 ':' 등)의 2가지 형태로 입력합니다.

  1. 숫자로 입력
    시트 분류 기준을 숫자로 입력하면, 시트명의 왼쪽부터 고정된 자리수를 기준으로 시트를 분류합니다.

    3 자리수 기준 목차 만들기
    자리수를 기준으로 분류하여 목차를 생성합니다. (자리수 고정)
  2. 기호로 입력
    시트 분류 기준을 '특정 기호'로 입력할 수 있습니다. 그럴경우 각 시트의 왼쪽부터 '특정 기호'를 찾은 뒤, 특정 기호 바로 앞까지 입력된 값을 기준으로 시트를 분류합니다.

    4 기호 기준 목차만들기
    '기호'를 기준으로 분류합니다. 기호 위치에 따라 유동적으로 분류합니다.

설정3. '목차로 돌아가기' 링크 생성

시트가 많아 엑셀 하단에 위치한 '시트 이동 막대'로 시트 간에 이동이 어려울 경우에 대비하여, 각 시트에 특정 셀을 지정하여 '목차로 돌아가기' 링크를 생성할 수도 있습니다.

목차로 돌아가기 링크
필요시 목차로 돌아가기 링크를 각 시트에 추가할 수 있습니다.
예제파일 및 추가기능 다운로드
인수 설명
인수/변수명데이터형식설명기본값
SortByLengthVariant시트를 분류할 기준을 입력합니다. (숫자 또는 기호)""
SortOrderLong시트 정렬 방향입니다. (1: 오름차순, -1: 내림차순, 0: 정렬안함)0
returnBtnBoolean[목차로 돌아가기] 링크 생성여부입니다.FALSE
BtnCellString목차로 돌아가기 링크를 생성할 셀 주소입니다."A1"
명령문 사용예제
'1. 통합문서 시트 순서 그대로 목차를 생성
CreateTOC

'2. 시트를 오름차순으로 정렬한 뒤 목차 생성
CreateTOC
  SortOrder:=1 
'3. 시트를 오름차순으로 정렬한 뒤, "-" 기호를 기준으로 분류
CreateTOC "-", 1
'4. 각 시트 B1 셀에 '목차로 돌아가기' 링크 생성
CreateTOC "-", 1, True, B1   
ImageLookup 함수 전체 코드
'###############################################################
'오빠두엑셀 VBA 사용자 지정함수 (https://www.oppadu.com)
'■ CreateTOC 명령문
'■ 통합문서의 각 시트를 기준에따라 정렬 및 분류하여 목차를 생성합니다.
'■ 인수 설명
'_____________SortByLength      : 시트를 분류할 기준입니다. 숫자 또는 기호를 받아옵니다.
'_____________SortOrder         : 시트 정렬 방향입니다. (1: 오름차순, -1: 내림차순, 0: 정렬안함)
'_____________resultBtn         : 목차로 돌아가기 링크 생성 여부입니다.
'_____________BtnCell           : 목차로 돌아가기 링크를 생성할 셀 주소입니다.
'■ 사용된 기타 사용자지정함수
'_____________SortArray 함수
'■ 그외 참고사항
'###############################################################
 
Sub CreateTOC(Optional SortByLength As Variant = "", Optional SortOrder As Long = 0, Optional returnBtn As Boolean = False, Optional BtnCell As String = "A1")
 
Dim WB As Workbook: Dim WS As Worksheet: Dim tocWS As Worksheet
Dim vaArr As Variant
Dim i As Long: Dim x As Long: Dim j As Long: Dim jj As Long: x = 0: j = 0: jj = 1
Dim groupLen As Variant: Dim prefix As String: groupLen = 0
 
Application.ScreenUpdating = False
 
Set WB = ActiveWorkbook
Set tocWS = WB.Worksheets.Add(Before:=WB.Worksheets(1))
 
'<--! 임시 배열 생성 -->;
ReDim vaArr(0 To WB.Worksheets.Count - 2)
For i = 2 To WB.Worksheets.Count
    Set WS = WB.Worksheets(i)
    '// ======= 2020.01.09 숨김시트 목록에서 제외 ========
    If WS.Visible = xlSheetVisible Then vaArr(j) = WB.Worksheets(i).Name: j = j + 1
Next
 
'// ======= 2020.01.09 배열 Redim 및 변수 초기화 ========
ReDim Preserve vaArr(0 To j - 1)
j = 0
 
'<--! 임시 배열 정렬 -->;
If SortOrder = 1 Then vaArr = SortArray(vaArr, xlAscending)
If SortOrder = -1 Then vaArr = SortArray(vaArr, xlDescending)
 
tocWS.Activate
ActiveWindow.DisplayGridlines = False
 
'<--! 목차시트 꾸미기 -->;
With tocWS
    On Error GoTo shtexist
    .Name = "목차"
resume_1:
    .Tab.Color = RGB(47, 47, 47)
    .Range("A:B").EntireColumn.ColumnWidth = 4
    .Range("4:500").EntireRow.RowHeight = 20
    .Range("2:2").Interior.Color = RGB(47, 47, 47)
    .Range("1:1").EntireRow.RowHeight = 11
 
    With .Range("B2")
        .Value = "목차"
        .Font.Bold = True
        .Font.Size = 18
        .Font.Color = RGB(255, 255, 255)
    End With
 
    .Range("B4").Value = "#"
    .Range("C4").Value = "시트명"
    With .Range("B4").EntireRow
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(89, 89, 89)
    End With
 
'<--! 임시 배열의 값을 하나씩 돌아가며 목차 생성 -->;
    For i = LBound(vaArr) To UBound(vaArr)
 
        If IsNumeric(SortByLength) = True Then
            groupLen = SortByLength
        Else
            If InStr(1, vaArr(i), SortByLength) > 0 Then groupLen = InStr(1, vaArr(i), SortByLength) - 1
        End If
        On Error Resume Next
        If Left(vaArr(i - 1), groupLen) <> Left(vaArr(i), groupLen) Then
 
            If (i = LBound(vaArr) And SortOrder = 0) Then
                j = j + 1: jj = 1
            Else
                j = j + 1: jj = 1
                .Cells(x + 5, 2).Value = j
                .Cells(x + 5, 3).Value = Left(vaArr(i), groupLen)
                If .Cells(x + 5, 3).Value = "" Then .Cells(x + 5, 3).Value = "기타항목"
                .Cells(x + 5, 2).NumberFormat = "0*."
                .Cells(x + 5, 1).EntireRow.Font.Bold = True
                .Cells(x + 5, 1).EntireRow.Interior.Color = RGB(245, 245, 245)
                x = x + 1
            End If
            On Error GoTo 0
        Else
            jj = jj + 1
        End If
 
        If blnSort = True Then prefix = "'" & j & "-"
 
        .Cells(x + 5, 2).Value = prefix & jj
        .Cells(x + 5, 2).NumberFormat = "[email protected]"
        .Hyperlinks.Add anchor:=.Cells(x + 5, 3), Address:="", SubAddress:="'" & CStr(vaArr(i)) & "'!A1", TextToDisplay:="'" & vaArr(i) '// 2020.02.11 수정 : 숫자형식 시트명 텍스트형식으로 변경
        .Cells(x + 5, 3).InsertIndent 1
 
        x = x + 1
    Next
 
    .Range("C:C").EntireColumn.AutoFit
 
End With
 
'<--! 각 시트에 '목차로 돌아가기' 링크 생성 -->;
If returnBtn = True Then
    For i = 2 To WB.Worksheets.Count
        WB.Worksheets(i).Hyperlinks.Add anchor:=WB.Worksheets(i).Range(BtnCell), Address:="", SubAddress:=tocWS.Name & "!A1", TextToDisplay:="목차로 돌아가기"
        WB.Worksheets(i).Range(BtnCell).Font.Bold = True
    Next
End If
 
 
Application.ScreenUpdating = True
 
Exit Sub
 
shtexist:
    tocWS.Name = "목차" & Format(Now, "yyyymmddhhmmss")
    On Error GoTo 0
    Resume resume_1
 
End Sub

명령문 동작원리 단계별 알아보기

  1. 명령문에 사용될 변수를 설정합니다. 각 변수의 사용목적은 영상강의에서 자세히 설명드렸습니다.
    Dim WB As Workbook: Dim WS As Worksheet: Dim tocWS As Worksheet
    Dim vaArr As Variant
    Dim i As Long: Dim x As Long: Dim j As Long: Dim jj As Long: x = 0: j = 0: jj = 1
    Dim groupLen As Variant: Dim prefix As String: groupLen = 0
     
    Application.ScreenUpdating = False
     
    Set WB = ActiveWorkbook
    Set tocWS = WB.Worksheets.Add(Before:=WB.Worksheets(1))
  2. 통합문서 시트를 정렬하여 보관할 임시 배열을 생성합니다.
    '<--! 임시 배열 생성 -->
    ReDim vaArr(0 To WB.Worksheets.Count - 2)
    For i = 2 To WB.Worksheets.Count
        vaArr(i - 2) = WB.Worksheets(i).Name
    Next
  3. 임시배열을 입력한 설정에따라 오름차순 또는 내림차순으로 정렬합니다. 배열 정렬에 사용된  SortArray 함수 설명은 관련포스트를 참고하세요.
    '<--! 임시 배열 정렬 -->
    If SortOrder = 1 Then vaArr = SortArray(vaArr, xlAscending)
    If SortOrder = -1 Then vaArr = SortArray(vaArr, xlDescending)
  4. 목차가 입력될 시트의 이름 및 탭색상과 목차의 글꼴, 배경색, 글꼴크기 등을 수정하여 시트를 꾸며줍니다.
    tocWS.Activate
    ActiveWindow.DisplayGridlines = False
     
    '<--! 목차시트 꾸미기 -->
    With tocWS
        On Error GoTo shtexist
        .Name = "목차"
    resume_1:
        .Tab.Color = RGB(47, 47, 47)
        .Range("A:B").EntireColumn.ColumnWidth = 4
        .Range("4:500").EntireRow.RowHeight = 20
        .Range("2:2").Interior.Color = RGB(47, 47, 47)
        .Range("1:1").EntireRow.RowHeight = 11
     
        With .Range("B2")
            .value = "목차"
            .Font.Bold = True
            .Font.Size = 18
            .Font.Color = RGB(255, 255, 255)
        End With
     
        .Range("B4").value = "#"
        .Range("C4").value = "시트명"
        With .Range("B4").EntireRow
            .Font.Bold = True
            .Font.Color = RGB(255, 255, 255)
            .Interior.Color = RGB(89, 89, 89)
        End With
  5. 임시배열의 값을 하나씩 돌아가며 기준에 따라 분류한 뒤, 목차시트에 값을 추가합니다.
    '<--! 임시 배열의 값을 하나씩 돌아가며 목차 생성 -->
        For i = LBound(vaArr) To UBound(vaArr)
     
            If IsNumeric(SortByLength) = True Then
                groupLen = SortByLength
            Else
                If InStr(1, vaArr(i), SortByLength) > 0 Then groupLen = InStr(1, vaArr(i), SortByLength) - 1
            End If
            On Error Resume Next
            If Left(vaArr(i - 1), groupLen) <> Left(vaArr(i), groupLen) Then
     
                If (i = LBound(vaArr) And SortOrder = 0) Then
                    j = j + 1: jj = 1
                Else
                    j = j + 1: jj = 1
                    .Cells(x + 5, 2).value = j
                    .Cells(x + 5, 3).value = Left(vaArr(i), groupLen)
                    If .Cells(x + 5, 3).value = "" Then .Cells(x + 5, 3).value = "기타항목"
                    .Cells(x + 5, 2).NumberFormat = "0*."
                    .Cells(x + 5, 1).EntireRow.Font.Bold = True
                    .Cells(x + 5, 1).EntireRow.Interior.Color = RGB(245, 245, 245)
                    x = x + 1
                End If
                On Error GoTo 0
            Else
                jj = jj + 1
            End If
     
            If blnSort = True Then prefix = "'" & j & "-"
     
            .Cells(x + 5, 2).value = prefix & jj
            .Cells(x + 5, 2).NumberFormat = "[email protected]"
            .Hyperlinks.Add anchor:=.Cells(x + 5, 3), Address:="", SubAddress:="'" & CStr(vaArr(i)) & "'!A1", TextToDisplay:=vaArr(i)
            .Cells(x + 5, 3).InsertIndent 1
     
            x = x + 1
        Next
     
        .Range("C:C").EntireColumn.AutoFit
     
    End With
  6. '목차로 돌아가기' 링크삽입 여부가 True 일 경우, 모든 시트를 돌아가며 '목차로 돌아가기' 링크를 추가합니다.
    '<--! 각 시트에 '목차로 돌아가기' 링크 생성 -->
    If returnBtn = True Then
        For i = 2 To WB.Worksheets.Count
            WB.Worksheets(i).Hyperlinks.Add anchor:=WB.Worksheets(i).Range(BtnCell), Address:="", SubAddress:=tocWS.Name & "!A1", TextToDisplay:="목차로 돌아가기"
            WB.Worksheets(i).Range(BtnCell).Font.Bold = True
        Next
    End If
     
     
    Application.ScreenUpdating = True
     
    Exit Sub

링크] MS홈페이지 Application.DisplayGridlines 속성 설명 바로가기

5 2 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
3 Comments
Inline Feedbacks
View all comments
솔
2020년 1월 22일 4:20 오후
게시글평점 :
     

열심히 공부하겠습니다.
질문 : 만약 시트명이 "002" 등으로 적용되었을 경우 이를 일반 서식으로 인식하여 숫자 2로 표시되는데 이를 개선할 수는 없나요?

림수
림수
2020년 4월 10일 8:16 오후
게시글평점 :
     

정말 잘써먹고? 있습니다. 목차를 만드니 데이터 취합하기도 쉽네요

3
0
여러분의 생각을 댓글로 남겨주세요.x