실전에서 바로 쓰는 엑셀 기능과 단축키 - 여러 시트 취합을 위한 INDIRECT 함수

미국 마이크로소프트에서 만든 MS Office 프로그램의 하나인 엑셀(Excel)은 전세계의 수많은 학생들과 직장인들이 이용하고 있는 MS의 킬러 컨텐츠입니다.

 

 

우리나라에서도 대학생이나 직장인, 사업자 분들에게는 데이터를 관리할 때 거의 필수적으로 이용하는 소프트웨어라고 할 수 있는데요,

 

저는 엑셀을 처음 익힐 때 엑셀을 알려주는 시중 서적은 양이 너무 방대하고 인터넷에 올라와있는 정보들은 이미 알고있는 기초적인 내용만 되풀이하는 경향이 있어 답답했던 경험이 있습니다.

 

 

이에 엑셀을 쉽고 빠르게 만들어주는 여러 단축키와 기능들, 특히 실전에서 당장 써먹고 과제시간을 줄여주거나 퇴근시간을 앞당겨줄 수 있는 꿀팁들을 알려 드리려고 합니다.

 

 

오늘은 똑같은 양식으로 되어 있는 여러 시트를 한 시트로 취합할 때 유용한 INDIRECT 함수에 대해 소개해 드리겠습니다.

 

 

 

 

[INDIRECT 함수]

 

Indirect 함수는 특정 시트의 특정 셀 값을 반환하는 함수이며 기본적인 입력 방식은 다음과 같습니다.

 

=INDIRECT(시트이름!셀주소)

 

 

예시로 다음과 같이 똑같은 양식으로 되어 있는 4 개의 시트 중 "거래량" 부분을 하나의 취합 시트로 정리해 보겠습니다.

 

자료출처 : KRX

자료출처 : KRX

자료출처 : KRX

자료출처 : KRX

 

우선 각 시트 이름을 열 이름으로 하는 취합용 시트를 하나 준비합니다.

 

삼성전자의 2020/06에 해당하는 셀(C5)에 INDIRECT 수식을 입력해 줄 것입니다.

 

시트 이름들을 이미 4행의 C~F 열에 기재해 두었으니 시트 이름 부분은 C4를 입력하면 됩니다.

 

채우기 기능으로 바로 채울 때 시트 이름 부분이 상대참조로 움직이는 것을 방지하기 위해 C$4로 입력하여 참조 행 부분을 절대참조로 고정시켜 둡니다.

 

다음과 같이 입력하는 부분까지 왔습니다.

 

=INDIRECT(C$4,

 

 

이제 셀 주소를 입력할 차례입니다.

 

2020/06의 "거래량" 셀은 각 시트의 D2 셀에 입력되어 있습니다. 여기서 느낌표 부분과 셀 주소 부분은 텍스트로 인식되기 때문에 큰 따옴표(") 안에 넣어 입력해줘야 합니다. 또한 수식 안에서 텍스트 부분은 앰퍼샌드 기호(&)로 연결해 주어야 오류가 나지 않습니다.

 

큰 따옴표 부분과 앰퍼샌드 기호를 포함하여 셀 주소를 다음과 같이 입력합니다.

 

=INDIRECT(C$4&"!D2")

 

위와 같이 삼성전자 시트의 D2 셀 값인 413,868,628 값이 제대로 출력되었습니다.

 

여기서 오른쪽 LG화학 부분까지 채우기를 해 주면,

 

위와 같이 각 시트의 D2 셀에 해당하는 값들이 출력됩니다.

 

 

 

 

 

[ROW 함수와의 연계]

 

그러나 위의 수식을 밑으로 채우기하여 2020/01 부분까지 채우게 되면 다음과 같이 2020/06의 거래량으로만 복사되는 현상이 발생합니다.

 

이는 INDIRECT 함수의 셀 주소 부분이 "D2"라는 텍스트로 고정되어버렸기 때문인데요,

 

D2의 2 부분이 상대참조로 바뀌어 채우기 했을 때 자동으로 D3, D4, D5...로 변환되도록 하기 위해 ROW 함수와 결합하여 수식을 입력해줘야 합니다.

 

 

(참고)

 

ROW 함수는 선택한 셀의 "행" 값을 결과값으로 반환하는 함수입니다. 즉 ROW(C2)의 값은 2 입니다. 또한 ROW() 처럼 괄호 안을 빈 값으로 두면 ROW 함수가 입력된 셀의 행 값을 결과값으로 반환합니다.

 

 

다시, 삼성전자의 2020/06 거래량 부분을 입력하는 부분으로 돌아와서,

 

이번에는 아래와 같이 입력해 줍니다.

 

=INDIRECT(C$4&"!D"&ROW(D2))

 

 

본래 "!D2" 라고 입력했던 부분을 "!D"&ROW(D2) 로 바꿔 준 수식입니다.

(ROW 안의 셀은 D2가 아닌 A2, B2 등 2행에 있는 어떠한 셀이든 무관합니다)

 

!D 부분까지는 기존 수식대로 텍스트로 입력해야 하지만, ROW(D2) 부분은 텍스트가 아닌 함수의 결과값이 수식에 들어가야 하므로 따옴표 밖으로 빼낸 후 & 기호로 연결해줍니다.

 

이와 같이 입력하는 경우 ROW 안의 D2가 상대참조로 인식되어 종방향으로 채우기를 하면 다음과 같이 각 시트의 월별 거래량을 가져올 수 있습니다.

 

이처럼 INDIRECT 함수와 ROW 함수의 결합을 통해 여러 시트로 나뉘어져 있는 데이터를 하나의 표로 집계할 수 있습니다.

 

각 지점별 매출, 창고별 재고 관리, 금고 별 현금 시재 등 특히 실무에서 수많은 쓰임새가 있는 함수이므로 꼭 이용법을 익혀두시길 추천드립니다.

 

 

이상으로 INDIRECT 함수에 대한 실전 팁 포스팅을 마치겠습니다.

 

긴 글 읽어주셔서 감사드리고, 독자 여러분들의 엑셀 능률 향상에 조금이나마 도움이 되었길 진심으로 기원합니다.