엑셀 목차 만들기, 1분이면 만드는 정말 쉽고 빠른 방법!
초보자도 가능한, 엑셀 목차 만들기 - 목차
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 목차 만들기, 1분 완성!예제파일
GET.WORKBOOK 함수는 현재 실행중인 파일의 정보를 출력하는 엑셀 4.0 매크로 함수입니다. 엑셀 4.0 매크로 함수는 GET.WORKBOOK 함수를 포함하여 여러가지가 있지만, 공부하거나 암기하실 필요가 전혀 없습니다! 오늘 강의에서 사용한 "GET.WORKBOOK(1)를 이름범위에 사용하면 시트목록을 반환한다." 만 기억해주세요!
GET.WORKBOOK(1) 을 이름범위에 사용하면 시트목록을 반환한다!
GET.WORKBOOK(1) 을 이름범위에 사용하면 시트 목록을 반환합니다. 오빠두Tip : 엑셀 4.0 함수는 2007 이전 버전에서 사용하던 매크로 함수입니다. 엑셀 2010 버전부터는 상위버전인 VBA7이 도입되면서, 엑셀 4.0 함수는 보안설정을 변경하지 않을 경우 기본적으로 사용이 제한됩니다.또한 엑셀 2010 이후 버전부터는 ① 4.0 매크로 함수의 사용이 기본적으로 제한되며, 엑셀 4.0 함수를 사용할 경우 ② #BLOCKED! 오류를 반환합니다.
엑셀 2010 이후 버전에서는 GET.WORKBOOK 함수의 사용이 기본적으로 제한됩니다. 실무에서는 4.0 함수를 사용할 일이 없으므로, 설정을 변경하지 않는 것이 좋지만, GET.WORKBOOK 함수를 지속적으로 사용할 경우 보안 설정을 변경해서 4.0 매크로 함수를 실행할 수 있습니다.
- 목차를 추가할 엑셀 파일을 실행한 후, [수식] 탭 - [이름관리자]를 클릭하거나 단축키 Ctrl + F3 을 눌러 이름관리자를 실행합니다. 이름관리자 대화상자가 실행되면, [새로 만들기] 버튼을 클릭해서 새로운 이름범위를 등록합니다.
수식 - 이름관리자 - 새로만들기를 클릭해서 이름 범위를 등록합니다. - [새 이름] 대화상자가 실행되면, 아래 표와 같이 이름과 참조대상을 입력합니다. 모두 입력한 후, [확인] 버튼과 [닫기] 버튼을 차례대로 눌러서 이름 등록을 마무리합니다.
항목 값 이름 목차 참조 대상 =TRANSPOSE(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,50)) 
이름과 참조대상을 각각 입력합니다. 오빠두Tip : 위 공식에서 ① MID 함수와 FIND 함수는 "]" 기호를 기준으로 시트이름만 추출하도록 동작하고, ② TRANSPOSE 함수는 가로로 나열된 데이터를 세로로 변환합니다. MID 함수와 FIND 함수를 사용해 텍스트를 추출하는 방법은 아래 5분 기초 영상 강의를 참고해주세요!
- 목차를 만들 새로운 시트를 추가한 후, A1셀부터 세로방향으로 범위를 선택합니다. 이때 범위는 시트의 갯수와 동일하거나 조금 더 넉넉하게 선택합니다. 이후 "=목차"를 입력한 후, Ctrl + Shift + Enter로 배열수식을 입력하면 시트 목록이 한 번에 반환됩니다.
목차를 출력할 범위를 넉넉하게 선택한 후, =목차 를 배열수식으로 입력합니다. 오빠두Tip : 엑셀2021 이후 / M365 사용자는 A1셀에 "=목차"를 작성한 후, 엔터키로 수식을 입력하면 동적으로 배열이 반환됩니다. 동적배열에 대한 자세한 설명은 아래 5분 기초 영상강의를 참고해주세요!
- 이제 셀을 클릭하면 해당 시트로 이동하는 링크를 추가하겠습니다. 아래 수식을 복사한 후, B1셀에 입력하면 "특정시트로 이동>>" 이라는 하이퍼링크가 추가됩니다.
=HYPERLINK("#'"&A1&"'!A1",A1&"시트 이동 >>")
HYPERLINK 함수를 사용해서 클릭하면 해당 시트로 이동하는 링크를 추가합니다. - 이후 B1셀에 입력한 수식을 아래로 자동채우기하면, 목차 만들기가 완료되었습니다. 이제 "시트 이동>>" 버튼을 클릭하면 원하는 시트로 편리하게 이동할 수 있습니다.
수식을 자동채우기하면 엑셀 목차가 완성됩니다. - GET.WORKBOOK 함수는 엑셀 4.0 매크로 함수이므로, GET.WORKBOOK 함수를 사용한 파일은 ① '매크로 포함 문서'로만 저장할 수 있습니다. 또한 엑셀의 ② 보안 설정을 변경하지 않을 경우, "#BLOCKED" 오류를 반환하며 사용이 제한되는 문제가 있습니다.
GET.WORKBOOK 함수는 엑셀 2010 이후 버전에서는 기본적으로 사용이 제한됩니다. - 따라서 목차를 실시간으로 갱신해야하는 특별한 상황이 아니라면, 목차 만들기에 사용한 GET.WORKBOOK 를 제거한 후 파일을 저장하는 것이 좋습니다. 시트 목록을 출력한 A열 전체 범위를 선택한 후, Ctrl + C 로 복사합니다. 이후 범위를 선택한 상태로 Ctrl + Alt + V 를 눌러 선택하여 붙여넣기를 실행한 후, '값' 으로 붙여넣기 합니다.
목차를 출력한 범위를 선택한 후, 값 형태로 선택하여 붙여넣기 합니다. - 이후 [수식] 탭 - [이름관리자]로 이동한 후, 방금 전 추가했던 '목차' 이름 범위를 삭제하고 파일을 저장하면 오류메세지 없이 파일이 올바르게 저장됩니다.
이후 GET.WORKBOOK 함수를 사용한 이름 범위를 삭제한 후, 파일을 저장합니다.
만약 GET.WORKBOOK 함수를 사용해서 시트 목록을 실시간으로 갱신해야 할 경우, 매크로 보안 설정을 변경해서 4.0 함수를 항상 실행할 수 있습니다.
오빠두Tip : 단, 매크로 보안 설정을 변경하면, 이후 다른 파일을 열 때에도 4.0 함수가 항상 실행되므로 보안에 취약한 문제가 있습니다. 따라서 되도록이면 매크로 보안 설정을 변경하지 않는 것이 좋습니다.- 매크로 통합문서 저장 안내창이 나오면 [아니오]를 클릭한 후, 매크로 사용 통합문서 형태(*.xlsm)로 파일을 저장합니다.
안내창이 나오면 '아니오'를 클릭한 후, 매크로 사용 통합문서 형태로 파일을 저장합니다. - 이후 [파일] 탭 - [옵션] - [보안센터] - [보안센터 설정]으로 이동합니다.
파일 - 옵션 - 보안센터 - 보안센터 설정으로 이동합니다. - 보안센터 설정 대화상자에서 [매크로 설정] 탭을 클릭한 후, 중간에 있는 'VBA 매크로를 사용하도록 설정하면 Excel 4.0 매크로 사용'을 체크한 후, [확인] 버튼을 클릭해서 매크로 설정을 종료합니다.
이후 매크로 설정 탭에서 'Excel 4.0 매크로 사용' 확인란을 체크한 후 설정을 저장합니다.
- 목차를 추가할 엑셀 파일을 실행한 후, [수식] 탭 - [이름관리자]를 클릭하거나 단축키 Ctrl + F3 을 눌러 이름관리자를 실행합니다. 이름관리자 대화상자가 실행되면, [새로 만들기] 버튼을 클릭해서 새로운 이름범위를 등록합니다.
