1일1함수 (18) INDIRECT 함수 : 셀주소 지정을 간접적으로

지난번에 만들었던 이원목적분류표에서 가장 수식이 긴 것은 다음과 같은 수식이었습니다.

=COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))

이것은 평가목표와 난이도를 표시한 것의 개수를 세는 수식인데, 그냥 간단히

=COUNTA($G$8:$G$42)

라고 표시하면 안되냐고 생각하시는 분이 있을것입니다. 예, 그렇게해도 해당셀에 숫자나 문자가 입력되어있는 셀의 개수를 세는 것이니까 같은 역할을 합니다. 하지만 제 생각에는 그렇게하면 8행부터 42행까지의 개수는 셀 수 있지만, 행을 몇 개 추가하거나 삭제하여 행의 번호가 바뀌면 수식을 일일이 다시 고쳐야한다고 생각했었습니다.
하지만 이것은 완전히 잘못된 생각으로, 일단 수식으로 정해놓은 범위는 행번호를 오른쪽 클릭하여 행전체를 추가하거나 삭제하면 수식이 자동으로 수정된다는 사실을 깜빡한 것이었습니다.

즉, 다음과 같이 평가항목중 "지식"에 해당하는 문항이 몇 개인지 합산하여 보여주는 G43셀의 수식을 다음과 같이 입력하고,
COUNTA($G$8:$G$42)

주관식 문항의 수가 5개가 아니라 8개이므로, 3개의 행을 더 추가하고 싶습니다. 이때 여기서보듯이 39,40,41이라고 되어있는 행숫자를 드래그하여 세 개의 행전체를 선택하고, 오른쪽 클릭하면

보시는 것과 같은 메뉴창이 뜹니다. 여기서 "삽입"을 선택하면

39,40,41행자리에 새로운 행이 만들어지고 기존의 행들은 뒤로 밀립니다. 여기서 아까 수식을 입력했던 G43셀은 G46셀로 밀려갔네요. 여기의 수식을 살펴보면

=COUNTA($G$8:$G$45)

로 수식이 자동으로 바뀌어 있음을 알 수 있습니다. 따라서, 처음에 제가 썼던 것처럼 INDIRECT함수를 써서 복잡하게 생각할 필요가 전혀 없었던 것이지요.

하지만 이런 기회로 INDIRECT 함수를 설명할 기회를 갖게 되었다는 것을 다행으로 생각하면 되겠죠? ^^;

이제 설명 들어갑니다.

INDIRECT(ref_text,a1)

ref_text  A1 스타일 참조, R1C1 스타일 참조, 참조로 정의된 이름이 들어 있는 셀에 대한 참조이거나 셀에 대한 텍스트 문자열 참조입니다. ref_text가 유효한 셀 참조가 아닌 경우에는 #REF! 오류 값이 반환됩니다.

• ref_text가 다른 통합 문서를 참조하는 경우(외부 참조)에는 그 통합 문서가 반드시 열려 있어야 합니다. 원본 통합 문서가 열려 있지 않으면 #REF! 오류 값이 반환됩니다.
• ref_text가 행 제한 1,048,576개나 열 제한 16,384개(XFD)를 초과하는 셀 범위를 참조하는 경우 INDIRECT는 #REF! 오류를 반환합니다.

   참고    그러나 Microsoft Office Excel 2007 이전 버전의 Excel에서는 이와 달리 제한 초과가 무시되고 값이 반환됩니다.

a1  ref_text 셀에 들어 있는 참조 유형을 지정하는 논리값입니다.

• a1이 TRUE이거나 이를 생략하면 ref_text는 A1 스타일의 참조로 해석됩니다.

• a1이 FALSE이면 ref_text는 R1C1 스타일의 참조로 해석됩니다.

그러니까, INDIRECT함수는 셀주소를 간접적으로 지정하는 함수가 되겠습니다. 예를 들어, A2라는 셀주소를 써야하는데, A2라고 직접적으로 써도 되지만,

INDIRECT("A"&1+1)

이라고 하면
A라는 글자 뒤에 1+1이라는 계산의 결과(2)를 붙여서 결국 A2가 되는 식입니다. 단, 여기서 주의할 점이 있습니다. 위에 수식의 도움말중에 제가 밑줄을 친 부분이 있습니다.

a1이 FALSE이면 ref_text는 R1C1 스타일의 참조로 해석됩니다.

이 말을 이해하시면 맨 위의 수식을 이해하실 수 있습니다.

셀주소를 표시하는 방식에는 두 가지가 있습니다. 첫번쨰는 A1 스타일로서, 우리가 흔히 사용하는 방식입니다. 열의 이름(ABCD....)를 먼저 쓰고, 행의 번호(1,2,3,4...)를 뒤에 씁니다.
두번째는 R1C1스타일로서, R뒤에 행의번호를, C뒤에 열의번호를 씁니다. 열의이름(ABCD....)을 사용하지 않고 숫자로만 표시하며, A1스타일과는 행열의 나열순서가 반대라는 점을 주의하세요.

A1스타일로 표시한 셀의 주소와 R1C1스타일로 표시한 셀의 주소를 몇 개만 연습해 봅시다.
A1 = R1C1
A3 = R3C1
B1 = R1C2
B5 = R5C2
F4 = R4C6

맨위에 썼던 수식이 다음과 같은 것이었는데,

=COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))

INDIRECT를 빼고는 이전시간에 다 배운 것들입니다. COUNTA는 빼고 그 안의 것들만 해석해 봅시다.
(이 수식이 들어있는 셀주소가 G43셀이며, A6셀에 "유형"이라는 글자가 들어있습니다.)

함수	해석	결과
INDIRECT("R"	R 뒤에	R
&MATCH("유형",$A:$A,0)+2	A열전체에서 "유형"이라는 글자가 몇번째 위치에서 나오는지 구하여 2를 더한 결과값과	8
&"C"&COLUMN()	C뒤에 현 위치의 열의 번호를 붙이고	C7
&":R"	콜론과 R을 붙이고	:R
&ROW()-1	현위치의 행의 번호에서 1을 빼고	42
&"C"	C를 붙이고	C
&COLUMN()	현 위치의 열의 번호를 붙인다	7
,FALSE))	R1C1방식의 주소이다	=R8C7:R42C7

 
즉, 다 계산하고보니 결국은
COUNTA(R8C7:R42C7)의 수식입니다. A1 스타일로 수식을 바꾸면

COUNTA(G8:G42) 와 같습니다.

처음에는 괜히 어렵게 보이지만 해석해 놓고 보니까 간단하지요? ^^*

INDIRECT함수는 셀주소를 일정 규칙에 의해 변화시킬때 많이 사용합니다. 예를 들어, 학생들의 명단이 있는데, 홀수번호의 학생만을 선발한다고 합시다. (C열에 점수가 있지만 무시합니다.)
E열에 선발된 학생들의 이름을 적는 난을 만들고, E2셀에는
=B2
E3셀에는
=B4
의 수식을 각각 입력하고, E2:E3셀을 선택하여 아래로 끌기하면 어떻게 될까요? B2,B4의 규칙이니 그 아래로는 B6,B8,B10의 수식이 자동으로 입력될까요?

결과를 보시면 생각과는 딴판이라는 것을 알 수 있습니다.
B2,B4,B4,B6,B6,B8,B8,B10 과 같이 입력되어있습니다. 우리가 의도한대로 나오지 않네요.

그럼 다음과 같이 수식을 입력해 봅시다.

=INDIRECT("B"&2*ROW())

그럼 결과는? 아래에 보시다시피, 홀수번호의 학생들이 나열되어있네요.

E2셀의 수식을 분석해 봅시다.

함수	해석	결과
=INDIRECT(
"B"&	B라는 글자뒤에	B
2*ROW())	행번호에 2를곱한 숫자를 붙인다	4

결국, B4의 셀에 있는 값을 가져오게 되며, 그 아래 셀은 짝수행인 B6,B8,B10... 에 있는 학생의 이름을 가져오게 됩니다.

P.S. 여기서 연습문제를 드립니다. 위에서보면 학생1이 나와있지 않고 학생3부터 나와있는데, 학생1부터 학생3,학생5,학생7의 순으로 명단에 나오게 하려면 수식을 어떻게 수정해야할까요?

2009/04/15 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적분류표
2009/04/20 - [컴퓨터/엑셀] - 1일 1함수 (17) MATCH 함수 : 찾는 데이타가 있는 위치를 알려준다.
2009/04/13 - [컴퓨터/엑셀] - 1일1함수 (14) COUNTA 함수 : 비어있지 않은 셀의 개수 구하기
2009/04/04 - [컴퓨터/엑셀] - 1일1함수 (10) COLUMN함수 : VLOOKUP함수를 좀 더 편하게 쓰자.