엑셀 목차 만들기, 초보자도 1분이면 만드는 쉬운 방법!

실습 가이드

GET.WORKBOOK 함수는 현재 실행 중인 파일의 정보를 반환하는 엑셀 4.0 매크로 함수입니다. 엑셀 4.0 매크로 함수에는 GET.WORKBOOK 외에도 다양한 함수가 있지만, 별도로 공부하거나 암기할 필요는 없습니다. 이번 강의에서는 "GET.WORKBOOK(1)을 이름 범위에 사용하면 시트 목록을 반환한다"는 한 가지 핵심만 기억해 두시면 됩니다.

GET.WORKBOOK(1)을 이름 범위에 사용하면 시트 목록을 반환한다!

또한 엑셀 2010 이후 버전에서는 ① 4.0 매크로 함수의 사용이 기본적으로 제한되며, 4.0 함수를 그대로 실행하면 ② #BLOCKED! 오류를 반환합니다.

실무에서는 4.0 함수를 사용할 일이 거의 없으므로 보안 설정을 그대로 두는 편이 안전합니다. 다만 GET.WORKBOOK 함수를 지속적으로 사용해야 한다면 보안 설정을 변경해 4.0 매크로 함수를 실행할 수 있습니다.

1. 목차를 추가할 엑셀 파일을 실행한 후, [수식] 탭 - [이름 관리자]를 클릭하거나 단축키 Ctrl + F3을 눌러 이름 관리자를 실행합니다. 이름 관리자 대화상자가 열리면 [새로 만들기] 버튼을 눌러 새로운 이름 범위를 등록합니다.

   

2. [새 이름] 대화상자가 실행되면 아래 표와 같이 이름과 참조 대상을 입력합니다. 모두 입력한 뒤 [확인] 버튼과 [닫기] 버튼을 차례대로 눌러 이름 등록을 마무리합니다.
   

   항목	값
   이름	목차
   참조 대상	=TRANSPOSE(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,50))

   

   오빠두Tip : 위 공식에서 ① MID 함수와 FIND 함수는 "]" 기호를 기준으로 시트 이름만 추출하도록 동작하고, ② TRANSPOSE 함수는 가로로 나열된 데이터를 세로로 변환합니다. MID 함수와 FIND 함수로 텍스트를 추출하는 방법은 아래 5분 기초 영상 강의를 참고해주세요.
   
3. 목차를 만들 새로운 시트를 추가한 후, A1셀부터 세로 방향으로 범위를 선택합니다. 이때 범위는 시트 개수와 동일하거나 조금 더 넉넉하게 잡습니다. 이후 "=목차"를 입력한 뒤 Ctrl + Shift + Enter로 배열 수식을 입력하면 시트 목록이 한 번에 반환됩니다.

   

   오빠두Tip : 엑셀 2021 이후 버전 또는 M365 사용자는 A1셀에 "=목차"를 입력한 후 엔터키만 눌러도 동적 배열 형태로 시트 목록이 반환됩니다. 동적 배열에 대한 자세한 설명은 아래 5분 기초 영상 강의를 참고해주세요.
   
4. 이제 셀을 클릭하면 해당 시트로 이동하는 링크를 추가합니다. 아래 수식을 복사한 뒤 B1셀에 입력하면 "특정 시트로 이동>>" 형태의 하이퍼링크가 추가됩니다.
   =HYPERLINK("#'"&A1&"'!A1",A1&"시트 이동 >>")

   

5. B1셀에 입력한 수식을 아래 방향으로 자동 채우기하면 목차 만들기가 완료됩니다. 이제 "시트 이동>>" 버튼을 클릭하면 원하는 시트로 빠르게 이동할 수 있습니다.

   

6. GET.WORKBOOK 함수는 엑셀 4.0 매크로 함수이므로, 이 함수를 사용한 파일은 ① '매크로 포함 문서' 형식으로만 저장할 수 있습니다. 또한 엑셀의 ② 보안 설정을 변경하지 않으면 "#BLOCKED" 오류가 반환되어 함수 사용이 제한된다는 점에 주의해야 합니다.

   

7. 따라서 목차를 실시간으로 갱신해야 하는 특별한 상황이 아니라면, 목차 만들기에 사용한 GET.WORKBOOK 함수를 제거한 뒤 파일을 저장하는 방법을 권장합니다. 시트 목록을 출력한 A열 전체 범위를 선택한 후 Ctrl + C로 복사합니다. 같은 범위가 선택된 상태에서 Ctrl + Alt + V를 눌러 [선택하여 붙여넣기]를 실행한 뒤, '값'으로 붙여넣습니다.

   

8. 이후 [수식] 탭 - [이름 관리자]로 이동한 다음, 방금 추가했던 '목차' 이름 범위를 삭제하고 파일을 저장하면 오류 메시지 없이 파일이 정상적으로 저장됩니다.

   

GET.WORKBOOK 함수로 시트 목록을 실시간 갱신해야 한다면, 매크로 보안 설정을 변경해 4.0 함수가 항상 실행되도록 허용할 수 있습니다.

1. 매크로 통합 문서 저장 안내창이 나오면 [아니오]를 클릭한 후, 매크로 사용 통합 문서 형식(*.xlsm)으로 파일을 저장합니다.

   

2. 이후 [파일] 탭 - [옵션] - [보안 센터] - [보안 센터 설정]으로 이동합니다.

   

3. 보안 센터 설정 대화상자에서 [매크로 설정] 탭을 클릭한 후, 중간에 있는 'VBA 매크로를 사용하도록 설정하면 Excel 4.0 매크로 사용'을 체크합니다. 마지막으로 [확인] 버튼을 눌러 매크로 설정을 마무리합니다.