엑셀 파일 내 시트 이름 한 번에 취합한 후 시트별 내용 가져오기 (feat. 이름 관리자 & INDEX & INDIRECT)

엑셀 파일을 사용하다보면 동일한 구성의 여러 시트에 있는 내용을 수식으로 한 번에 끌어올 방법이 없나 고민될 때가 있습니다.

아무래도 보고서나 각종 일지같이 비슷한 포맷을 날짜별, 혹은 건수별로 정리하는 경우가 해당될 겁니다.

 

이번 포스팅에서는 동일 양식의 시트들을 리스트화 한 후 각 시트별 특정 위치의 값들을 가져와 정리하는 과정을 살펴보겠습니다.

 

예시로 사용하기 위해 이전의 뒤로 보내기 단축키 포스팅에서 사용했던 샘플 화면을 다시 가져왔습니다.

 

아래 이미지와 같은 포맷의 시트가 여러개 있다고 가정하겠습니다.

 

[비슷한 포맷의 시트 예시 화면, 각 시트별로 이름과 사이즈를 가져올 예정]

 

 

[각 시트별 이름 및 사이즈 값]

 

 

 

여기서 '요약'시트에 Sp1부터 sp7까지 각 시트별 C3셀의 이름과, D3셀의 사이즈를 각각 가져와서 리스트업해보겠습니다. 

 

이번에는 과정에 앞서 결과화면 부터 보여드리겠습니다.

GIF이미지 상의 타겟으로 삼았던 각 시트별 3번 셀 Name, Size값이 아래 이미지와 같이 정리된 모습입니다.

[각 시트별로 이름과 사이즈 값을 가져온 결과 화면]

 

 

 

제가 사용하는 방식은 제목에서 언급한 것처럼

이름 관리자 + Index + Indirect 함수 조합입니다.

 

그럼 진행 과정을 보겠습니다.

 

 

 

 

 이름관리자 등록 후 엑셀 시트별 시트 이름 가져오기

 

 

먼저 데이터를 불러오기 위해 다음과 같은 빈 양식을 만들어두었습니다.

 

시트 넘버는 엑셀 파일 상의 각 시트별 인덱싱 넘버라고 보시면 됩니다.

맨 왼쪽부터 1번입니다.

 

[시트 이름 호출 전]

 

 

 

 

리본 메뉴의 수식 - 이름관리자 를 직접 눌러주거나 단축키로 Ctrl + F3을 눌러 이름 관리자를 실행합니다.

 

[수식 - 이름 관리자 실행]

 

 

 

 

이름 관리자 메뉴가 호출되면 새로 만들기를 눌러줍니다.

 

[이름 관리자 - 새로 만들기 클릭]

 

 

 

 

 

이름은 편하게 작성해주시고, 참조 대상에 아래와 같은 수식을 적어줍니다.

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

 

 

[이름과 참조 수식 작성]

 

 

 

 

정상적으로 진행되었다면 이름 관리자 화면에 아래와 같은 형태의 이름 요소가 생성됩니다.

 

[이름 관리자 설정 후]

 

 

 

 

이제 아래의 수식들을 사용하여 데이터를 호출하면 됩니다.

시트 이름 호출 =@INDEX(시트List,B4)
각 시트별 특정 셀값 호출 =@INDIRECT($C4&"!C3")

 

[Index + Indirect 함수로 해당 셀값 호출하기]

 

 

 

 

각 함수의 역할은 다음과 같습니다.

 

• INDEX : 시트 넘버에 해당하는 시트 이름 호출
• INDIRECT : 시트 이름을 직접 호출 하여 해당 시트의 지정 셀 값 호출

 

[Index함수로 시트 이름 호출하기]

 

 

[Indirect 함수로 각 시트별 셀값 호출하기]

 

 

 

 

이런 방식으로 각 시트의 이름과 시트별 특정 셀 값을 호출하여 한 화면에 정리하시면 됩니다.

 

 

여기서 중요!!

INDEX로 불러온 시트 이름은 수식 상태가 아닌 값 상태로 바꿔주는 것을 추천합니다.

 

 

이름 관리자에 들어간 수식은 매크로로 분류됩니다. 

위 수식으로 데이터를 꾸미고, 저장할 때 아래와 같은 경고 팝업이 뜰 겁니다.

 

[이름 관리자 지정 후 파일 저장시 경고 문구]

 

 

 

 

다음 기능은 매크로 제외 통합 문서에 저장할 수 없습니다.

정의된 이름으로 저장된 Excel 4.0 함수입니다.

 

위 내용처럼 매크로 파일 저장할 때와 유사한 것을 볼 수 있습니다.

 

그대로 기능을 온전히 유지하려면 매크로 파일 형식으로 저장하면 됩니다.

다만 매크로 파일은 각종 보안 이슈를 대비하기 위해 제약이 많은 편입니다.

 

VBA를 직접 사용하는 것도 아닌 문서를 굳이 매크로 문서로 저장할 필요는 없습니다.

예를 눌러 일반 문서로 저장하는 것을 권합니다.

 

 

제가 조금 전 시트 이름 호출하는 수식을 값 상태로 바꾸는걸 권했는데 이유는 다음과 같습니다.

일반 문서로 저장한 후 파일을 재실행하게 되면 아래와 같은 모습을 볼 수 있습니다.

 

[일반 문서로 저장한 후 재실행 화면]

 

 

 

 

엑셀에서 "NAME?"로 뜬다는 것은 문자 그대로 이름 관련 문제를 의미합니다.

 

Ctrl+F3을 눌러 이름 관리자를 호출해보면 아래 이미지처럼 기존에 만들었던 이름 요소가 증발한 것을 볼 수 있습니다.

 

[이름 관리자가 초기화된 상태]

 

 

 

 

수식은 그대로 있는데 참조해야할 이름 요소가 없다보니 오류가 발생한 상태입니다.

이런 경우, 수식이 깨지거나 한 것은 아니므로 이름을 다시 지정해주면 됩니다.

 

제가 만든 예시 화면에선 INDEX함수는 "시트List"라는 이름을 참조하고 있습니다.

아래 예시처럼 시트List라는 이름 요소를 다시 생성해주면 모든 수식이 정상화되는 것을 볼 수 있습니다.

 

[이름 관리자 재지정]

 

 

 

 

매크로로 저장하거나, 값으로 바꾸거나, 필요할 때만 이름을 재지정하는 식으로 편의에 맞게 사용하시면 됩니다.

 

 

내용이 다소 길어보일 수는 있으나 막상 해보면 간단한 편입니다.

 

이름 관리자, INDEX, INDIRECT

 

위 3가지 요소를 조합하면 VBA나 Quary가 없더라도 시트 목록 작성과 값 호출이 가능하니 활용해보시길 바랍니다.

 

 

 

---

 

엑셀 사용시 참고할만한 내용들 링크 추가합니다.

이 외에도 다양한 팁을 다루고 있으니 둘러보시길 바랍니다.

 

 

맨 뒤로 보내기, 맨 앞으로 가져오 단축키 설정 및 다중 정렬 팁

 

https://sswh.tistory.com/103

엑셀 이미지 (도형) 맨 뒤로 보내기 맨 앞으로 가져오기 단축키 설정과 응용

 

 

필터 및 틀고정 단축키

https://sswh.tistory.com/26

엑셀 팁 - 작업 속도를 올려주는 단축키 Part.4 (필터 기능, 틀 고정)

 

 

개체 선택 방법

https://sswh.tistory.com/24

엑셀 팁 - 이미지 및 개체 전체 선택, 부분 선택하기

 

 

하이퍼 링크와 이름 정의를 이용한 이동 버튼 만들기

https://sswh.tistory.com/96

엑셀 하이퍼링크와 이름 정의를 이용한 시트 내 이동 버튼 만들기