엑셀 시트 목차 만들기 자세한 설명(feat. 오빠두엑셀)

엑셀 시트가 많아 목차로 만들어서 관리하고 싶다는 생각을 한 적  있으신가요? 이번 포스팅에서는 엑셀 시트 목차 만들기 방법을 정리했습니다. 오빠두엑셀에서 설명한 내용을 기반으로 한 것이지만, 누구나 이해할 수 있도록 자세한 설명을 추가했습니다.

오빠두엑셀 원본 강의는 아래 링크를 참조해주세요.

엑셀 목차 만들기, 1분이면 만드는 정말 쉽고 빠른 방법! - 오빠두엑셀

#1 엑셀 목차 만들기, 일단 따라 해보자

① 엑셀 상단의 수식 탭을 클릭한 후 리본 메뉴에 나타나는 ② 이름 관리자를 클릭합니다.

수식 - 이름 관리자 경로

새로 나타나는 창에서 ③ 새로 만들기를 클릭합니다.

이름 관리자 - 새로 만들기

새로 만들기를 클릭하면 아래와 같이 '새 이름' 창이 나타납니다.④ 이름에 적당한 단어를 입력합니다. 사진에서는 목차를 적어주었지만 'List', '엑셀_목차' 등과 같은 단어를 입력해도 괜찮습니다. 단, 띄어쓰기가 포함되어 있으면 오류가 표시됩니다. 띄어쓰기가 필요한 경우에는 '_'와 같은 특수문자를 활용해 표현하시기 바랍니다. ⑤ 참조 대상에 아래 수식을 복사하여 붙여 넣습니다. ⑥ 확인을 눌러줍니다.

=TRANSPOSE(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,50))

새 이름에서 이름과 참조 대상을 입력해 주었다.

⑦ '목차'라는 이름이 생성되었습니다. ④ 에서 입력한 이름대로 설정된 것입니다. 참조 대상에는 이전 단계에서 입력한 수식이 표시되어 있습니다. ⑧ 닫기를 클릭합니다.

이름 관리자 항목에 '목차'가 생성 되었다.

⑨ 셀을 세로로 여러 개 선택합니다. '=목차'를 입력하고 Ctrl+Shift+Enter 버튼을 한 번에 눌러줍니다. 엑셀 2021 이후 버전이나 M365 버전에서는 Enter키만 누르면 됩니다. ⑩ 엑셀 시트 목록이 나열된 것을 확인할 수 있습니다. 현재 엑셀 파일에 3개의 시트, Sheet1 ~ 3가 있기 때문에 목차에 Sheet1 ~ 3이 나타났습니다. 현재 만들어진 시트의 목록이 나타나는 것입니다. 새로운 시트를 추가 했다면 ⑨에서 '목차'를 입력한 셀을 클릭 후 F2를 누른 다음 Ctrl+Shift+Enter 혹은 Enter 키를 눌러주면 시트 목록이 최신으로 생신 됩니다. ⑨에서 '목차'를 입력한 것은 이름 관리자에서 '목차'로 이름을 설정했기 때문이며, 다른 단어로 입력했다면 그 단어를 입력해 주면 됩니다.

이름 관리자에서 생성한 목차를 '=목차'와 같이 셀에 입력해주었다. 그리고 Ctrl+Shift+Enter 버튼을 같이 눌러준다. 엑셀 2021 혹은 M365 버전이라면 Enter 키만 눌러주면 된다.

1-1 GET.WORKBOOK(1) 참고사항

GET.WORKBOOK 함수는 엑셀 4.0 매크로 함수입니다. VBA를 사용한 것은 아니지만 매크로 함수를 사용했기 때문에 GET.WORKBOOK 함수를 유지하기 위해서는 .xlsm 확장자로 저장을 해야 합니다. 엑셀 시트 목록을 실시간으로 최신화 할 필요가 없다면 GET.WORKBOOK 함수를 제거하고 .xlsx 확장자로 저장하는 것이 좋습니다. 다른 사람들에게 공유 시 보안 설정을 변경하지 않는다면 #Blocked이 나타나며 오류가 발생할 수 있기 때문입니다. GET.WORKBOOK을 제거하기 위해서는 아래의 순서대로 진행합니다.

GET.WORKBOOK() 함수를 제거하면 엑셀 시트 목록 값이 날라가므로, 수식이 아니라 값으로 변경해 주어야 합니다. ① 엑셀 시트 목록을 선택한 후 Ctrl+C 를 눌러 복사해줍니다. ② Ctrl+Alt+V 키를 한 번에 눌러주어 선택하여 붙여 넣기 창을 띄웁니다. ③ 값을 선택한 후에 ④ 확인을 눌러줍니다.

엑셀 시트 목록을 수식이 아니라 값으로 변경하는 방법이다.

수식 - 이름 관리자를 들어가서 ⑤ 삭제할 이름(목차)을 클릭하고 ⑥ 삭제를 클릭합니다. ⑦ 목차 이름을 삭제할 것인지 묻는 대화창이 나타나면 확인을 눌러 삭제를 진행합니다.

이름 관리자에서 '목차' 이름을 삭제하고 있다.

이름을 삭제한 후 F12를 눌러 다른 이름으로 저장 창이 나타나면, Excel 통합 문서(.xlsx) 확장자로 저장해줍니다.

.xlsx 확장자로 저장 했다.

#2 함수 설명

목차 만들기 수식에 4가지 함수를 사용 했습니다. 아래 번호 순서대로 설명을 해보겠습니다.

목차 만들기 수식에 4가지 함수를 표시 했다.

2-1 GET.WORKBOOK(1) 함수 설명

GET.WORKBOOK(1)은 엑셀 파일에 있는 모든 시트를 일렬로 나타내주는 함수입니다. 하지만 GET.WORKBOOK은 엑셀 4.0 매크로 함수로 다른 함수와 사용 방법이 다릅니다. 단순히 셀에 '=GET.WORKBOOK(1)'을 입력하여 사용하는 것이 아니라는 것이지요. 앞에서 했던 것처럼 이름 관리자에서 '이름'으로 등록 후 사용합니다.

GET.WORKBOOK(1)은 가로로 시트 목록을 나타냅니다. 셀 3개를 가로로 선택한 후 이름 관리자에서 등록한 '목차'를 '=목차'와 같이 적은 후 Ctrl+Shift+Enter를 눌러주면 시트 리스트가 가로로 나타나는 것을 확인할 수 있습니다. 세로로 셀을 선택하면 첫 번째 시트가 여러 개 표시됩니다.

여기서 주목해서 봐야 할 것은 시트를 표시는 하는 양식입니다. [엑셀 시트 목차 만들기 study.xlsm]Sheet2와 같이 표시되는 것이 보이시나요? 여기서 '엑셀 시트 목차 만들기 study.xlsm'은 파일 이름과 확장자를 나타낸 것입니다. ']'바로 왼쪽의 Sheet2는 시트의 이름입니다. GET.WORKBOOK(1) 함수가 이런 형태로 시트 리스트를 나타낸다는 것을 기억하며 다음 함수 설명으로 넘어가 보겠습니다.

셀 3개를 가로로 선택하여 '=목차'를 입력하고 Ctrl+Shift+Enter를 눌러 시트 목차를 나타내었다.

2-2 FIND 함수 설명 : FIND("]",GET.WORKBOOK(1))

FIND 함수는 특정 문자의 위치를 주어진 텍스트 내에서 찾아 냅니다. 왼쪽에서부터 오른쪽 순서로 몇 번째에 위치해 있는지 찾아내고 숫자로 표시합니다. 목차를 만들기 위한 수식 중 FIND 함수를 GET.WORKBOOK(1)의 결과 텍스트에서 ] 문자 위치를 찾기 위해 활용한 것입니다.

FIND 함수 구성 : =FIND(find_text, within_text)

이해를 돕기 위해 아래 사진에서 ]가 '[엑셀 시트 목차 만들기 study.xlsm]Sheet2'와 같이 표현되는 텍스트에서 몇 번째에 위치하는지 찾아내기 위한 수식을 나타내었습니다. 아래의 조건에서는 25번째로 결과가 나타났습니다. 이제 이 값을 MID 함수에서 어떻게 활용하는지 보겠습니다.

FIND 함수를 활용해 "]" 위치를 확인 했다.

2-3 MID 함수 설명 : =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,50)

MID 구성 : =MID(text, start_num, num_chars)

MID 함수는 문자열에서 특정 텍스트를 추출하기 위한 함수입니다. 각 시트의 이름은 ]바로 다음에 이어지므로 수식에서 FIND 함수 결과에 +1을 해준 것입니다. num_chars에 50을 입력했는데 50이 의미하는 것은 추출할 문자의 길이입니다. 50은 넉넉하게 잡은 값이며 시트 이름이 길더라도 전부 가져올 수 있도록 설정한 것으로 이해하시면 되겠습니다.

아래 사진을 예시로 한 번 더 설명해 보겠습니다. ']'위치가 25이므로 시트 이름만 추출하기 위해서 +1을 해주었습니다. 시트 이름 전체를 가져올 수 있도록 num_chars에 넉넉한 값인 50을 입력해 주었습니다.

MID 함수로 시트 이름을 추출하는 과정을 설명하고 있다. ]위치 +1을 해주어 시트 이름만 추출하고 있다.

2-4 TRANSPOSE

=TRANSPOSE(array)

TRANSPOSE는 행과 열을 변환 시키는 함수입니다. 아래 사진으로 설명해 보겠습니다. ① TRANSPOSE 함수를 사용하여 V36:X36 범위를 설정했습니다. ② 가로로 나열된 배열이 ③과 같이 세로로 전환 되었습니다. GET.WORKBOOK(1) 결과가 가로로 나타나기 때문에 TRANSPOSE 함수를 사용하여 세로로 나열되게 합니다. 세로로 보는 것이 한 눈에 더 잘 들어오기 때문입니다.

TRANSPOSE 활용 예시이다.

마치며

엑셀 시트 목차 만들기와 수식을 자세하게 설명해 보았습니다. 기능 구현을 위해 그냥 가져다가 사용해도 무방하지만, 이왕이면 이해하고 사용하는 것이 좋지 않겠습니까? 이번 포스팅 내용 참고하셔서 엑셀 목차 만들기를 어떤 순서와 원리로 하는 것인지 이해하셨으면 좋겠습니다. 감사합니다.