모든 시트에 대한 목차를 만들고 자동으로 업데이트하려면 어떻게 해야 하나요?

수백 개의 워크시트가 포함된 통합 문서가 있다고 가정해 보겠습니다. 여러 시트 중 특정 시트로 이동하는 것은 대부분의 사람들에게 어려울 수 있습니다. 이 경우, 이러한 워크시트에 대한 목차를 생성하면 특정 시트로 빠르고 쉽게 이동할 수 있습니다. 이 튜토리얼에서는 모든 시트에 대한 목차를 생성하고, 시트를 삽입하거나 삭제하거나 이름을 변경할 때 목차가 자동으로 업데이트되는 방법에 대해 설명합니다.

수식을 사용하여 모든 시트에 대한 목차를 만들고 자동으로 업데이트하기

Kutools for Excel을 사용하여 모든 시트에 대한 목차를 만들고 자동으로 업데이트하기

VBA 코드를 사용하여 모든 시트에 대한 목차를 만들고 자동으로 업데이트하기

수식을 사용하여 모든 시트에 대한 목차를 만들고 자동으로 업데이트하기

Excel에서 범위 이름을 정의하고 수식에서 해당 이름을 참조하여 목차를 만들 수 있습니다. 다음 단계를 따라 진행하세요:

1. 목차를 만들 첫 번째 시트 앞에 새 시트를 삽입하고 필요에 따라 이름을 바꿉니다.

2. 그런 다음 "수식" > "이름 정의"를 클릭하세요. 스크린샷을 참조하세요:

3. "새 이름" 대화 상자에서 "Sheetlist"라는 이름을 "이름" 상자에 지정하세요. 원하는 경우 다른 이름으로 변경할 수 있으며, "참조 대상" 텍스트 상자에 다음 수식을 입력합니다.

=GET.WORKBOOK(1)&T(NOW())

4. 그런 다음 "확인" 버튼을 클릭하여 대화 상자를 닫습니다.

5. 이제 목차를 만들 새 시트로 이동하여 빈 셀에 다음 수식을 입력하세요:

=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")

6. 그리고 채우기 핸들을 아래로 드래그하여 빈 셀이 표시될 때까지 드래그하면 현재 워크북의 모든 시트 이름(숨겨진 시트 포함)이 아래 스크린샷과 같이 나열됩니다:

7. 그런 다음 목차에 하이퍼링크를 생성해야 합니다. 다음 수식을 사용하세요:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

8. 이제 하이퍼링크 텍스트를 클릭하면 즉시 해당 시트로 이동합니다. 또한 새 시트를 삽입하거나 시트를 삭제하거나 시트 이름을 변경하면 목차가 자동으로 업데이트됩니다.

Kutools for Excel을 사용하여 모든 시트에 대한 목차를 만들고 자동으로 업데이트하기

"Kutools for Excel"이 있다면, "탐색 창" 기능을 통해 모든 시트 이름을 왼쪽 창에 세로로 나열하고 특정 시트로 빠르고 쉽게 이동할 수 있습니다.

Kutools for Excel 설치 후, 다음과 같이 진행하세요:

1. "Kutools" > "탐색"을 클릭하세요. 스크린샷을 참조하세요:

2. 확장된 "탐색" 창에서 "통합 문서 및 시트" 아이콘을 클릭하면, 열려 있는 모든 통합 문서가 상단 리스트 박스에 표시되고, 해당 통합 문서 내의 모든 표시된 시트는 하단 리스트 박스에 표시됩니다. 스크린샷을 참조하세요:

3. 이제 왼쪽 창에서 시트 이름을 클릭하기만 하면 해당 시트로 이동할 수 있습니다. 또한 시트를 삭제하거나 삽입하거나 이름을 바꾸면 창의 시트 목록이 동적으로 업데이트됩니다.

VBA 코드를 사용하여 모든 시트에 대한 목차를 만들고 자동으로 업데이트하기

때때로 숨겨진 시트를 목차에 표시할 필요가 없습니다. 이 문제를 해결하기 위해 다음 VBA 코드를 사용할 수 있습니다.

1. 목차를 만들 첫 번째 시트 앞에 새 시트를 삽입하고 필요에 따라 이름을 바꿉니다. 그런 다음 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 "코드 보기"를 선택하세요. 스크린샷을 참조하세요:

2. 열린 "Microsoft Visual Basic for Applications" 창에서 아래 코드를 복사하여 시트 코드 창에 붙여넣습니다:

VBA 코드: 모든 시트에 대한 목차를 만들고 자동으로 업데이트하기

Private Sub Worksheet_Activate()
'Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
    If xWsh.Name <> xStrWShName Then
        If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
            Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
            Me.Range(xStrTCHeader).Offset(xI).Value = xI
            xI = xI + 1
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

3. 그런 다음 "F5" 키를 눌러 이 코드를 실행하면 새 워크시트에 즉시 목차가 생성됩니다. 모든 숨겨진 시트는 목차에 표시되지 않습니다. 스크린샷을 참조하세요:

4. 이제부터 시트를 삭제하거나 삽입하거나 이름을 바꿀 때 목차가 동적으로 업데이트됩니다.