INDIRECT 함수는 엑셀에 있는 함수들 중에 난이도가 있는 함수며 초보자 입장에서는 그렇게 많이 사용하지는 않습니다만, 정말 중요한 곳에 사용이 되기도 하죠. 바로 드롭다운 목록을 정할 때 대분류와 이에 따른 소분류가 있다면 소분류에서 INDIRECT함수를 사용해야 합니다. 정말 중요한 함수죠. 그런데 사실 이 함수의 기능은 단순한데 생소해서 이해하기가 꽤 까다롭습니다. 단순히 소분류를 정할 때 INDIRECT함수를 쓴다고 외워되 되겠지만 여기에서는 몇가지 예를 들어서 이 함수를 이해해 보도록 하겠습니다.
우선 엑셀에서 설명한 이 함수의 정의를 살펴보겠습니다.
- INDIRECT 함수의 정의
텍스트 문자열로 지정한 셀 주소를 돌려줍니다.
설명 : Ref_test 은(는) 셀의 주소로서, 주소 또는 텍스트 문자열 형태로 셀 주소로 정의된 이름인 A1 또는 R1C1 스타일의 셀 주소를 포함합니다.
(사실 이 설명은 잘못된 설명은 아닌데 이해하기 어렵군요. 천천히 설명해 보겠습니다.)
INDIRECT 함수의 형식은 다음과 같습니다.
INDIRECT(Ref_text, A1) |
(A1은 기본값이 True로 생략 가능합니다.)
Ref_text
Ref_text 는 텍스트이어야 합니다. 이 값은 셀의 주소형식의 텍스트("A1") 일 수도 있고 정의된 이름일 수도 있습니다. 예를 들어 설명해 보겠습니다.
위와 같이 입력해 보세요. 그리고 셀 주소 A5에 있는 값이 어떻게 되는지 보세요.
Hello가 됩니다. 그림에서도 알 수 있듯이 INDIRECT라는 함수의 이름이 괜히 INDIRECT가 아닙니다. 즉 간접적으로 셀 주소 E6를 참조하여 그 값을 돌려주었네요. 그래도 자세한 설명은 필요하겠네요.
기본적으로 알고 있어야 할 사항은 수식에서 사용되는 셀 주소는 그 자체로 사용되지 않는다는 것입니다. 예를 들면 위 그림에서 C1이 수식에 사용되면 C1에 있는 값을 참조하라고 해석되어 "E6"로 대체됩니다. 그러면 INDIRECT(C1)이라고 하면 C1이 해석되어 INDIRECT("E6")가 되겠죠. 이 기본적인 사항을 기억하고 설명을 읽어나가길 바랍니다.
다시 INDIRECT 함수의 정의를 읽어보세요.
"텍스트 문자열로 지정된 셀 주소를 돌려준다"
이해하기 쉽게 말하면
"전달된 텍스트 문자열을 셀주소로 반환해 준다"
셀 주소를 반환하는 군요. 결국 반환된 셀 주소 또한 셀 주소에 있는 값으로 대체되겠죠?
앞에서 C1이 수식에서 사용될 때 C1의 내용이 반환되어 대체된다고 했던 것 기억 나실 겁니다.
결국 이런 메커니즘이죠.
1. 셀 주소를 INDIRECT 함수에 전달
2. 전달된 셀 주소는 수식에 있으므로 자동적으로 주소에 있는 값으로 변환되어 결국 INDIRECT에 "E6"라는 텍스트가 전달됨
3. INDIRECT 함수는 전달된 텍스트를 주소값으로 변환
4. 변환된 주소값(E6) 역시 수식에서는 주소에 있는 값으로 변환되므로, 즉 셀 주소 E6에 값 "Hello"로 바뀜
이 때 INDIRECT 함수에 전달되는 ref_text는 텍스트여야 합니다. 텍스트이기 때문에 주소값의 조작
따라서 그냥 INDIRECT("E6")라고 쓰면 E6라고 쓰는 것과 동일한 의미입니다. 굳이 INDIRECT 함수에 직접 셀주소를 의미하는 텍스트로 넣을 필요가 없는 것이죠. 보통 앞의 예 처럼 셀 주소를 전달하되 전달된 셀 주소는 텍스트로 또다른 셀 주소를 가지고 있습니다. 그래서 이 함수 이름이 INDIRECT인 듯 합니다.
A1
이번에는 두번 째 전달되는 인수 A1에 대해서 설명 드리겠습니다.
일반적으로 그렇게 많이 사용될 것 같지는 않으니 간단히 개념만 알아두세요.
A1은 주소 패턴입니다. 우리가 앞에서 사용한 주소 E6처럼 알파벳+숫자 형식의 주소입니다. INDIRECT의 두 번째 인자는 패턴을 정하는데 사용합니다. 기본적으로 A1 패턴으로 사용하지만 R1C2스타일로 사용할 수도 있습니다. 우선 앞의 예를 R1C1 스타일로 바꿔 보겠습니다.
즉 INDIRECT함수로 전달되는 두 번째 인자를 FALSE로 해놓고 텍스트로 전달될 값을 R6C5로 받은 겁니다.
R은 행(ROW)를 의미하고 C는 열(COLUMN)을 의미합니다. 즉 6행 5열을 뜻합니다.
엑셀 기본 주소형식을 안쓰고 왜 이렇게 사용하나요? 라고 물으실 수있는데 이렇게 사용을 하게 되면 R 6 C 5에서 행과 열을 의미하는 숫자를 동적으로 계산해서 사용할 수 있기 때문이죠. 예를 들어 보면
위와 같은 표가 있다고 합시다. 한번 위 표를 이동해 보세요.
포인터는 주소텍스트를 지니고 있는데 이 값은 텍스트니까 상대적으로 변하지 않습니다. 이럴 땐 다음과 같이 하면 해결됩니다.
"R"&ROW()&"C"&COLUMN()+1
ROW()함수는 현재 셀의 행번호를 COLUMN()함수는 현재 셀의 열 번호를 숫자로 반환합니다 그리고 &연산으로 텍스트에 붙여줬죠.
위 값은 테이블이 이동해도 상대적으로 변합니다. 텍스트 형식으로 주소를 표현하기에는 딱 좋죠. 그래서 R1C1 형식으로 사용하는 겁니다. 그냥 그렇구나 하고 넘어가면 될 듯합니다.
대분류 소분류에서 사용하기
INDIRECT에 드롭다운 대분류에 해당되는 셀이 참조될 수 있습니다. 사용자가 대분류를 선택하면 소분류에서 선택된 대분류에 따라서 INDIRECT로 대분류에 선택된 이름정의가 전달되는 것이죠. 이름정의는 소분류 목록들을 지정한 범위주소인 것이구요. 이 것은 드롭다운 소분류에 해당하는 내용이므로 다음을 참고하시면 될 것 같습니다.