엑셀로 작업하다 보면 시트가 점점 많아져, 원하는 시트를 찾아 하나씩 탭을 클릭해가며 이동해야 할 때가 생깁니다. 시트 수가 10개, 20개를 넘어가면 탭을 스크롤하며 찾아야 해서 작업 흐름이 끊기고 불편해지는 상황, 한 번쯤 겪어보셨을 텐데요.
그럴 때는 이름 관리자와 HYPERLINK 함수를 활용한 자동 목차를 만들어 보세요! 클릭 한 번으로 원하는 시트로 바로 이동하는 목차 버튼을 1분 만에 완성할 수 있습니다.
- 단축키 Ctrl + F3을 눌러 이름 관리자를 실행합니다. 이름 관리자 창이 열리면 상단의 [새로 만들기] 버튼을 클릭해 새 이름을 추가할 준비를 합니다.
- 새로 만들기 창이 열리면 이름 입력란에 '목차'를 입력합니다. 이어서 참조 대상 입력란에는 아래 공식을 그대로 복사해서 붙여 넣고 [확인]을 클릭합니다.
=TRANSPOSE(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255)&T(NOW()))
- 이름 관리자 설정이 끝났으면 시트 탭으로 이동합니다. 첫 번째 시트 탭을 클릭한 후, Shift 키를 누른 채 마지막 시트 탭을 클릭하면 중간의 모든 시트 탭이 동시에 선택됩니다.
[ipsan]선택된 탭은 흰색으로 강조 표시됩니다. 이 상태에서 함수나 값을 입력하면, 선택한 모든 시트에 일괄 입력됩니다.[/ispan]
- 모든 시트가 선택된 상태에서, 목차를 표시할 위치의 비어있는 셀에 =목차를 입력합니다. 그러면 통합 문서의 모든 시트 이름이 한 번에 세로 목록으로 출력됩니다. 이 상태에서는 아직 수식이 연결되어 있으므로 다음 단계에서 값으로 변환합니다.
오빠두Tip : 파란색 테두리의 동적배열 반환은 엑셀2021 이후 버전에서 제공됩니다. 엑셀 2019 이전 버전 사용자는 아래 이전 버전 사용자 안내를 확인하세요!👇
- 출력된 시트 이름 범위 전체를 선택하고 복사합니다. 이후 같은 위치에서 우클릭 → [선택하여 붙여넣기] → [값]을 선택합니다.
- 시트 이름 목록 바로 옆 열에 HYPERLINK 함수를 입력합니다. 첫 번째 인수(이동할 주소)는 큰따옴표 안에
# 기호와 작은따옴표를 입력한 뒤, 시트 이름이 입력된 셀을 참조하고, 다시 작은따옴표와 느낌표, 이동할 셀 주소를 순서대로 연결해 구성합니다. 완성된 수식은 다음과 같습니다.
=HYPERLINK("#'"&시트&"'!E2",시트)
- 첫 번째 셀의 HYPERLINK 함수가 정상 작동하는 것을 확인했으면, 자동 채우기로 나머지 시트에 해당하는 셀 모두에 함수를 채웁니다. 이후 글꼴 색상을 적절히 변경하면 시트 이동 목차 버튼이 완성됩니다.
- 마지막으로 중요한 주의 사항입니다. 이름 관리자에 추가한 GET.WORKBOOK은 구형 매크로 함수이기 때문에, 삭제하지 않고 파일을 저장하면 파일 형식이 매크로 파일(.xlsm)로 자동 변경됩니다.
- 반드시 Ctrl + F3으로 이름 관리자를 열어 '목차' 항목을 선택하고 [삭제]를 클릭한 뒤 파일을 저장하세요. 시트 이름 목록은 이미 값으로 변환되어 있어 삭제해도 목차 버튼 동작에는 영향이 없습니다.
- 엑셀 2019 이전 버전 사용자는 '시트 순번'을 미리 만든 후, "=목차" 범위를 다음과 같이 입력합니다. 먼저 1씩 증가하는 순번을 빈 공간에 만들어주세요.
- 이후 아래와 같이 INDEX 함수를 입력하면 시트 목차를 한 번에 만들 수 있습니다. 시트 개수보다 목차 순번이 큰 경우 #REF오류가 반환되지만, 정산이니 오류는 무시하셔도 괜찮습니다.
=INDEX(목차,순번)
