오늘은 엑셀의 Indirect함수를 활용해서, 여러시트의 값을 아주 쉽게 참조한 수식을 만들어 보겠습니다.
여러 시트에 있는 값들을 참조해야할 경우
아래 밑에 있는 예제 파일을 열어 보시면, 각 지역 이름별로 구분된 탭에 해당 지역의 데이터가 있습니다. 여기에 보고용으로 Summary 요약 탭을 하나 만들어서 각 지역별 데이터를 한 테이블에 정리하려는데, 함수 하나로 손쉽게 여러 시트에 있는 데이터들을 동시에 참조할 수 있을까요?
물론입니다. Indirect함수를 사용하면 가능합니다.
Indirect함수 설명
엑셀에서 Indirect함수는 문자로 직접 입력한 셀 주소를 통해 특정 셀이나 범위의 값을 반환해주는 함수입니다. 함수 구문은 아래와 같이 이루어져 있습니다.
= Indirect (참조범위, [참조방식])
참조범위에는 참조할 셀 주소 또는 범위 주소를 입력합니다. 만약, 텍스트로 “A5” 을 입력하면 동일한 시트의 A5셀을 참조하죠. 다른 시트 참조할 때 셀 주소가 어떻게 입려되는지 눈여겨 보신 분들은, 이를 통해 다른 시트의 셀주소/ 범위를 참조하게 할 수 있습니다. 바로 ‘시트명’!A5 형태로 입력하시면 되죠. (천천히 아래 예제 파일 통해서 이해하시면 되니 조급하실 필요는 없습니다)
참조방식 인수는 사실 쓸 일이 없기 때문에 크게 신경 쓰지 않으셔도 됩니다. 그래도 빠뜨리면 서운한 분들을 위해 설명 드리면, 참조방식은 셀 주소를 참조할 방식입니다. 기본값은 TRUE(=A1방식, 보통 셀 주소가 이렇게 알파벳숫자 형식으로 이루어져 있잖아요) 입니다. 만약 셀 주소가 R1C1(=R1C1, R1C2, R1C3, …) 방식으로 입력될 경우, 참조방식을 FALSE로 입력하시면 됩니다.
예제파일에 사용된 Indirect함수 설명
Summary tab C2 에 입력된 수식을 한 번 같이 뜯어 (?) 보시죠.
=IFERROR(HLOOKUP(C$1,INDIRECT($B2&”!$C$1:$T$37″),37,FALSE),””)
Iferror 함수는 로직 테스트에서 오류가 나왔을 때 특정 값을 반환하라는 함수이고, Hlookup 은 각 지역별 탭 37 행에 있는 count 갯수 값을 가지고 오기 위한 함수이니 따로 설명해드리지는 않겠습니다. (혹시 설명이 필요하신 분들은 댓글 달아주시면 감사드리겠습니다.)
오늘 꼭 이해하고 넘어가실 부분은 Hlookup 함수에서 Table Array 로서 작동하는 “Indirect 함수를 통한 참조 범위” 입니다. 일단 예제 파일의 모든 탭들이 지역 이름 (Tigray, Afar, Amhara) 을 가지고 있으므로
- Summary table 의 B 열 (region) 에 같은 지역 이름들을 입력하였습니다.
- Indirect 함수 참조범위 인수에 $B2 를 입력해 B열에 있는 지역 이름을 차례 차례 참조하도록 하였습니다.
- 셀 주소 다음에 문자값 ! 을 넣으려 하므로 와일드 카드 & 로 구분해 주었습니다.
- 각 지역별 탭에서 가지고 올 값들이 있는 참조 범위가 C$1:$T$37 로 같으므로 (각 탭 데이터 테이블 디자인 할 때부터 이 부분을 고려해 주는 것이 좋습니다) “!$C$1:$T$37″을 입력해 주었습니다. 그러면 첫 번째 수식 완성!
- 완성된 수식을 복사하여, Summary table 의 C2:G4 범위에 붙여 넣기 하였습니다.
예제파일을 열어 보면 아시겠지만, C2에 있는 수식은 결국 indirect(Tigray!$C$1:$T$37)로 C3의 수식은 indirect(Afar!$C$1:$T$37) 으로 인식되니, 결국 각각 시트의 해당 범위들을 hlookup함수의 table array 로 가지고 오게 되는 것입니다.
예제파일 다운로드
이렇게 수식을 뜯어서 설명하니 살짝 어렵게 느끼실 수도 있을 것 같습니다. 하지만 수식의 가장 안쪽에 있는 함수부터 차근 차근 로직을 이해하고, 직접 만들어 보시면 금방 업무에 적용하실 수 있을 거예요! 아래 예제 파일 링크 드리니, 다운로드 받으셔서 한 번 연습해보시기를 바랍니다! 그럼 오늘도 칼퇴와 저녁이 있는 삶을 위해 모두 화이팅!
엑셀 사용하면서 다른 쉽고 유용한 엑셀 팁들을 보고 싶으다면 아래 링크 클릭해주세요!