엑셀 배우기, INDIERCT 함수의 드롭다운 활용!

INDIRECT 함수는 이전 포스팅, 엑셀배우기, 이름정의와 INDIRECT/VLOOKUP 함수 활용, 목록과 드롭다운 표시! 에서 이미 다룬적이 있는 참조 합수중 하나입니다. VLOOKUP 함수는 INDEX 함수 및 MATCH 함수와 달리 특정 셀이 아닌 특정 범위를 참조할 수 있기 때문에, 이 INDIRECT 함수는 다른 함수와 함께 활용되는 경우가 대부분입니다. 간단한 예를 들자면, SUM 함수 내에 INDIRECT 함수를 중첩시면 특정 범위의 합을 구할 수 있게 됩니다. 이러한 측면에서 또다른 특정 범위를 참조하는 함수, OFFSET 함수와 비슷합니다.

OFFSET 함수에 대한 내용은 아래의 포스팅을 참고해 주시면 되겠습니다.

엑셀배우기, OFFSET 함수로 특정일부터의 매출액 합산하기!

엑셀배우기, OFFSET/COUNTA함수를 활용한 동적 이름 정의와 드롭다운 목록 생성!

오늘은 엑셀 INDIRECT 함수를 드롭다운 기능에서 활용하는 방법에 대한 내용입니다. 일단, 아래에서는 B2:D17 범위에 재무상태표의 자산 계정 과목을 정리했습니다. 그리고 F2 셀에서 유동자산 또는 비유동자산을 드롭다운으로 선택할 수 있도록 하였습니다.

드롭다운을 만드는 방법은, 상단 메뉴의 '데이터'와 '데이터 유효성 검사'를 차례로 클릭하여 '데이터 유효성' 창을 열어줍니다. 이어서 '제한 대상'을 '목록'으로 선택해주고, '드롭다운 표시'에 체크, 그리고 '원본'에 유동자산, 비유동자산을 입력해주면 끝입니다. 아래처럼 목록의 내용을 직접 입력해 줄 수 도 있고, 목록이 입력된 범위를 지정해 줄 수 도 있습니다. 즉, '원본'에 '=$C$2:$C$17'와 같이 특정 범위를 넣을 수도 있습니다.

F2 셀에서, 유동자산 또는 비유동자산을 선택하면 관련 계정과목을 G2 셀에서 선택할 수 있도록 위와 마찬가지로 드롭다운을 만들고 싶습니다. 간단히 위와 같이 G2 셀에서 '데이터 유효성' 창을 열고, '원본'에 '=$D$2:$D$17'의 범위를 입력해도 되지만, 이렇게 되면 자산관련 모든 계정 과목이 목록으로 생성됩니다. 따라서 INDIRECT 함수를 사용해서, F2 셀에 유동자산이 선택되면 G2 셀에는 유동자산 관련 계정 과목만 선택할 수 있도록, 또한 F2 셀에 비유동자산이 선택되면 G2 셀에는 비유동자산 관련 계정 과목만 선택할 수 있도록 드롭다운을 만들어 보겠습니다.

마치 INDEX 함수와 MATCT 함수의 조합처럼,  INDIRECT 함수는 거의 대부분의 경우에 '이름 정의'가 필수입니다. 앞에서 말씀드린대로 INDIRECT 함수는 특정 범위를 참조하는 함수이고, 이 범위에 이름을 부여하는 것이 '이름 정의' 입니다. 

아래에서는 D2:D9의 범위가 '유동자산'이라는 이름으로 정의되어 있습니다. '이름 정의' 방법은 수식 메뉴와 이름관리자 메뉴를 차례로 클릭하여, '이름 관리자' 창을 열고 '새로 만들기' 버튼을 눌러서 특정 범위에 이름을 부여할 수도 있고, 특정 범위를 선택한 후, '이름 상자'에 곧바로 원하는 이름을 입력해 줄 수 도 있습니다. 당연히 후자의 방법이 훨씬 간편합니다. 상단 메뉴 아래, 함수 입력창 왼쪽에 작은 입력창을 '이름 상자'라고 합니다.

위와 같이, D2:D9 범위에 '유동자산', D10:D17 범위에, '비유동자산'으로 '이름 정의'를 마쳤다면, 이제 G2 셀에서 다시 '데이터 유효성' 창을 열어줍니다. 그리고 이제 '원본' 입력창에 '=INDIRECT($F$2)'와 같이 함수 수식을 구성해줍니다. INDIRECT 함수 구조는 '=INDIRECT(참조할 텍스트)'이기 때문에, F2셀이 유동자산이라면, G2셀은 결국 '=INDIRECT(유동자산)'이 되어, D2:D9의 범위를 목록으로 생성합니다. 마찬가지로, F2셀이 비유동자산이라면, G2셀은 결국 '=INDIRECT(비유동자산)'이 되어, D10:D17의 범위를 목록으로 생성합니다.

범위를 참조한다라는 개념이 엑셀 초급자분들한테는 약간 이해하기 어려운 부분이 있습니다. 단 일단 이해가 되면, 이 범위를 참조하는 함수인 INDIRECT 함수와 또다른 함수인 OFFSET 함수가 얼마나 유용한 함수인지 곧바로 깨닫게 되실 겁니다. 앞서 설명이 부족한 부분이 있었다면, 아래에 댓글 남겨주시기 바랍니다!