엑셀 목차 만들기 자동화, 시트 관리를 효율적으로 하는 방법!

통합문서의 시트를 자동으로 정렬하여 목차를 생성하는 목차만들기 자동화 명령문의 동작원리와 실전 사용법을 단계별로 살펴봅니다.

홈페이지 » 엑셀 목차 만들기 자동화, 시트 관리를 효율적으로 하는 방법!

엑셀 목차 만들기 추가기능 단계별 사용방법 정리 :: 퀵 VBA 9강

목차 바로가기
패치노트
  • 2020.01.09 : 숨겨진 시트는 목록에서 제외하도록 명령문을 수정하였습니다.
  • 2021.06.22 : 영문 엑셀에서 일부 문자가 깨지는 문제가 발생하여, 영문 버전의 목차 만들기 추가기능을 업데이트 하였습니다.
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [퀵VBA] 엑셀 목차 만들기 CreateTOC 명령문
    완성파일

관련 기초함수
강의 설명

이번 강의에서는 엑셀 통합문서의 시트를 자동으로 정렬/분류하여 목차를 만들어주는 CreateTOC 명령문의 사용법을 알아봅니다. VBA에 익숙치 않은 분들을 위해, 간단하게 설치하여 사용할 수 있는 추가기능을 제공해드리고 있습니다.

엑셀 목차 만들기 추가기능
엑셀 목차 만들기 추가기능 다운로드

목차 만들기 명령문 CreatTOC 함수의 최신버전 및 자세한 설명이 필요하시다면 아래 함수설명 포스트를 확인하세요.

목차 만들기 프로그램을 사용하면 어떤점이 좋을까요?

  1. 시트를 오름차순 또는 내림차순으로 정렬하여 자동으로 목차를 생성합니다. 따라서 시트를 정렬하지 않아도 목차가 깔끔하게 만들어집니다.
  2. 입력한 기준에 따라 목차를 그룹화 할 수 있습니다. (자리수 또는 특정 기호로 그룹화할 수 있습니다.)
  3. 시트가 많아 시트 간 이동이 어려울 경우에 대비하여, [목차로 돌아가기] 버튼을 각 시트에 생성할 수 있습니다.

목차만들기 툴 사용방법

  1. 추가기능을 설치하면 엑셀 상단의 [삽입] - [DuTool] - [목차만들기] 버튼이 추가됩니다.
    1. 목차 만들기 리본버튼
    [삽입] 탭에 [목차생성] 기능이 추가됩니다.
  2. [목차생성] 버튼을 클릭하면 목차만들기 설정을 위한 창이 나타납니다.
    2 목차만들기 설정창
    [목차생성] 버튼을 클릭하면 설정창이 나타납니다.
  3. 설정창에는 [목차 그룹화] 및 [돌아가기 버튼] 체크박스가 있습니다.
    3 목차만들기 설정창 2가지 체크박스
    [목차정렬/분류] 및 [돌아가기버튼]을 설정할 수 있습니다.
  4. [목차 그룹화] 를 체크하면 목차를 오름차순 또는 내림차순으로 정렬할 수 있습니다.

    4 목차 오름차순 내림차순 정렬
    오름차순/내림차순으로 정렬합니다.

  5. 목차를 각 분류별로 묶어주기 위한 '묶을 기준'을 입력합니다. 묶을 기준은 '숫자' 또는 '특정 기호/문자'로 입력합니다.
    1. 숫자를 입력할 경우 : 입력한 자리수를 기준으로 목차를 분류합니다. (자리수 고정)
    5 시트를 2로 묶을 경우
    '숫자'를 입력할 경우 고정된 자리수로 목차가 분류됩니다.

    2. 기호를 입력할 경우: 시트 이름에서 특정 기호를 찾아, 해당 기호를 기준으로 목차를 분류합니다.

    5-1 기호로 묶을 경우
    특정 기호를 기준으로 목차를 분류합니다.

  6. 그룹화하지 않고 시트 정렬만 원할시, 묶을기준을 입력하지 않고 넘어갑니다.

    6 묶을기준 빈칸
    분류하지 않고 정렬만 할 수도 있습니다.

  7. [돌아가기 버튼]을 체크하면 '목차로 돌아가기' 버튼을 각 시트에 생성할 수 있습니다. 기본값은 A1셀 입니다.
    6 돌아가기 버튼 생성
    [목차로 돌아가기] 버튼을 생성합니다.
  8. [미리보기] 버튼을 클릭하면 생성 될 목차를 미리 볼 수 있습니다.

    8 미리보기 버튼 클릭
    설정이 완료되면 '미리보기' 버튼을 클릭합니다.

  9. 목차 목록에 이상이 없으면, [목차생성] 버튼을 클릭합니다. [목차] 시트가 맨 왼쪽 첫번째 시트로 생성됩니다.
    9 목차 생성 완료
    이상이 없으면 [목차생성] 버튼을 클릭합니다.
  10. 엑셀은 동일한 이름의 시트를 2개 이상 생성할 수 없습니다. 따라서 기존에 [목차]라는 이름을 가진 시트가 존재할 경우 현재시간이 추가된 이름으로 시트가 생성됩니다.

    10 목차 중복생성 방지
    현재시간을 추가하여 시트명의 중복을 방지합니다.

이번 강의에서 사용된 보조 명령문 (SortArray 명령문)

이번 강의에서는 배열을 정렬하는 SortArray 명령문이 보조 명령문으로 사용되었습니다. SortArray 함수에 대한 자세한 설명은 관련 포스트를 확인하세요.

'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'■ SortArray 명령문
'■ 배열을 오름차순/내림차순으로 정렬합니다.
'■ 인수 설명
'_____________vaArr       : 정렬할 배열입니다.
'_____________SortOrder     : 정렬방향입니다. (오름차순,내림차순)
'_____________NumericSort   : 숫자크기로 정렬할 여부를 결정합니다.(기본값 False, 텍스트형식 정렬)
'■ 반환값
'_____________정렬된 배열을 반환합니다.
'###############################################################
 
Function SortArray(vaArr As Variant, Optional SortOrder As XlSortOrder = xlAscending, Optional NumericSort As Boolean = True)
 
Dim i As Long: Dim j As Long
Dim vaVal As Variant
Dim Temp
 
If NumericSort = True Then
    For Each vaVal In vaArr
        If IsNumeric(vaVal) = False Then NumericSort = False: Exit For
    Next
End If
 
If NumericSort = False Then
    If SortOrder = xlAscending Then
        For i = LBound(vaArr) To UBound(vaArr) - 1
            For j = i + 1 To UBound(vaArr)
                If UCase(vaArr(i)) > UCase(vaArr(j)) Then
                    Temp = vaArr(j)
                    vaArr(j) = vaArr(i)
                    vaArr(i) = Temp
                End If
            Next j
        Next i
    Else
        For i = LBound(vaArr) To UBound(vaArr) - 1
            For j = i + 1 To UBound(vaArr)
                If UCase(vaArr(i)) < UCase(vaArr(j)) Then Temp = vaArr(j) vaArr(j) = vaArr(i) vaArr(i) = Temp End If Next j Next i End If Else If SortOrder = xlAscending Then For i = LBound(vaArr) To UBound(vaArr) - 1 For j = i + 1 To UBound(vaArr) If vaArr(i) > vaArr(j) Then
                    Temp = vaArr(j)
                    vaArr(j) = vaArr(i)
                    vaArr(i) = Temp
                End If
            Next j
        Next i
    Else
        For i = LBound(vaArr) To UBound(vaArr) - 1
            For j = i + 1 To UBound(vaArr)
                If vaArr(i) < vaArr(j) Then
                    Temp = vaArr(j)
                    vaArr(j) = vaArr(i)
                    vaArr(i) = Temp
                End If
            Next j
        Next i
    End If
End If
 
SortArray = vaArr
 
End Function

목차 만들기 매크로 전체 명령문

목차만들기 매크로의 각 단계별 동작원리는 영상강의에서 자세히 설명해드렸습니다.

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
    vaArr(i - 2) = WB.Worksheets(i).Name
Next
 
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 = "_~@"
        .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
 
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

목차 만들기 매크로, CreateTOC 명령문 사용법

엑셀 목차만들기 매크로인 CreateTOC 명령문에 사용되는 인수는 아래와 같습니다.

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

CreateTOC 명령문의 간단 사용법은 아래와 같습니다. 명령문 사용법에 대한 자세한 설명은 영상강의를 참고하세요.

'1. 통합문서 시트 순서 그대로 목차를 생성
CreateTOC
'2. 시트를 오름차순으로 정렬한 뒤 목차 생성
CreateTOC SortOrder:=1
'3. 시트를 오름차순으로 정렬한 뒤, "-" 기호를 기준으로 분류
CreateTOC "-", 1
'4. 각 시트 B1 셀에 '목차로 돌아가기' 링크 생성
CreateTOC "-", 1, True, B1
5 8 투표
게시글평점
guest
16 댓글
Inline Feedbacks
모든 댓글 보기
윤달수
윤달수
2020년 2월 3일 10:44 오후
게시글평점 :
     

추가기능에서 DuTool이 생성되게 하려면 어떻게 하는지요.

사랑의신
사랑의신
2020년 10월 28일 2:05 오전
답글 남기기  오빠두엑셀

설치된 윈도우가 한글 윈도우가 아니거나 언어 기본설정이 한글로 안되어 있고 다른 언어로 되어 있으면 설치할 때 에러가 자주 발생하는 것 같아요. 여러번 해보았는데 다른 툴들도 비슷한것 같습니다. 설치할 때 파일을… 더보기 »

솔
2020년 2월 11일 1:15 오후
게시글평점 :
     

목차를 만들 때 sheet명이 001, 002 등으로 되어있는 경우에는 1, 2 로 표시되어 링크에는 문제가 없으나 함수에 사용시 인식되지 않는 문제가 발생됩니다.
수정을 어떻게 해야 하는지요

솔
2020년 2월 12일 10:53 오전
답글 남기기  오빠두엑셀

고맙습니다 정말 많은 도움이 되었고, 되고있습니다.
매일 좋은하루 보내세요

김수정
김수정
2020년 3월 1일 4:45 오후
게시글평점 :
     

안녕하세요. 좋은 영상으로 도움주셔서 감사해요. 목차 작업후 모바일에서 볼때는 바로가기 기능이 안되는데, 방법이 있을까요?

닥코드
닥코드
2020년 4월 8일 10:53 오전
게시글평점 :
     

좋은 내용 감사합니다.

심성만
심성만
2020년 5월 28일 1:38 오후
게시글평점 :
     

엘셀 2010 에서는 안되나요?
클릭해두 아무것도 실행이 안되는데요

등대지기
등대지기
2020년 8월 8일 10:28 오후
게시글평점 :
     

좋은 내용 감사드립니다.

창진
창진
2021년 6월 17일 4:17 오후
게시글평점 :
     

안녕하세요 너무 편리한 기능이네요..사용하다보니 시트명을 변경하거나 추가하게되면 목차에 반영이 안되던데 변경된시트가 있을시 목차만들기를 새로 만들어야 하나요? 다른 방법이 있는지 궁금해요

oth11021
oth11021
2021년 6월 17일 10:55 오후
게시글평점 :
     

안녕하세요 현재 영어 버젼 엑셀을 쓰고 있는데 글씨가 깨져서 나오네요 . 영어로 나오게 할수있나요 ?

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