텍스트 주소 참조 함수, INDIRECT
이전글에서 VLOOKUP 함수를 대체할 수 있는 INDEX, MATCH 함수를 배웠습니다.
두 가지 방식 모두 찾을 값이 포함된 참조 범위를 절대참조 방법으로 지정해야 한다고 했습니다.
예제에서 상대참조 방식을 사용한 경우도 있었는데, 이는 아주 특이한 경우에만 그렇습니다.
대부분의 경우는 절대참조 방식을 사용해야 합니다.
이번 글에서는 참조 범위를 지정할 때 절대참조 방식에 비해 좀 더 유연하게 지정할 수 있는 텍스트 주소 참조 함수 INDEIRCT 함수에 대해 배워보겠습니다.
엑셀에서 값을 참조하기 위해 가장 많이 사용하는 함수는 바로 VLOOKUP 입니다.
보통 =VLOOKUP(E3,$B$3:$C$8,2,FALSE) 형태로 함수 수식을 작성합니다.
여기서 주목할 부분은 두 번째 인수인 table_array $B$3:$C$8 입니다.
절대참조 방식으로 참조 범위가 지정된 것을 보실 수 있습니다.
절대참조로 지정된 참조 범위는 수식을 복사할 경우에도 그 값이 자동으로 변하지 않습니다.
VLOOKUP 함수를 사용하는 대부분의 경우는, 변하지 않는 참조 범위에서 찾는 값을 검색하여 그 행의 다른 열에 있는 값을 참조하는 것 입니다.
학생의 이름을 찾는 값으로 하여 생년월일 값을 참조하는 예에서 이를 확인했습니다.
만약 VLOOKUP 함수의 참조 범위가 유동적이 되어야 한다면, 기존에 작성된 함수 수식에 어떤 변화가 나타날까요?
$B$3:$C$8 절대참조 참조 범위를 수정하고 적용된 전체 셀에 복사해서 붙여넣기 해야합니다.
데이터 건수가 적은 경우에는 간단하지만, 건수가 늘어나면 쉽지 않습니다.
이때 활용할 수 있는 것이 텍스트 주소 참조 함수 INDIRECT 입니다.
간단한 예제를 통해 확인해 보겠습니다.
VLOOKUP 함수 상대참조 방식의 문제
이전 글에서 VLOOKUP 함수의 참조 범위 지정시 절대참조 방식을 사용하는 것을 권장한다고 했습니다.
상대참조 방식을 사용하면 어떤 문제가 발생하기 때문인지에 대한 설명은 자세히 하지 않았습니다.
위의 그림을 보고 확인해 보겠습니다.
F3 셀에 입력된 VLOOKUP 함수 수식의 두 번째 인수 table_array 는 상대참조 방식 B3:C8 으로 작성되어 있습니다.
다른 학생의 이름으로 생년월일 찾는 부분을 하나 더 만들기 위해 E3:F3 범위를 복사하여 E4:F4 범위에 붙여넣기 했습니다.
그러자 F4 셀에 “#N/A” 오류가 발생했습니다.
오류의 원인을 확인하기 위해 F4 셀에서 펑션키F2 를 눌렀더니, 참조 범위가 이전과 달라진 것이 보입니다.
상대참조 방식으로 작성했기 때문에 복사 붙여넣기 작업시 수식이 자동으로 변경 적용되었기 때문입니다.
그 결과 김민준 학생이 포함되지 않은 참조 범위가 VLOOKUP 함수에 입력 되었고, 오류가 발생했습니다.
이 문제를 해결하기 위한 방법은 바로 참조 범위를 절대참조 방식으로 지정하는 것 입니다.
F3 셀의 함수 수식을 =VLOOKUP(E3,$B$3:$C$8,2,FALSE) 으로 변경합니다.
두 번째 인수를 상대참조 방식 에서 절대참조 방식으로 변경한 것 입니다.
이후 F3 셀을 복사하여 F4 셀에 붙여넣기 합니다.
이제 결과가 정상적으로 출력되고 있습니다.
F4 셀의 함수 수식을 확인해보면 참조 범위가 학생 전체 범위에 대해 올바르게 복사되어 지정되었음을 알 수 있습니다.
이러한 이유로 VLOOKUP 함수의 참조 범위는 절대참조를 사용해야 하는 것 입니다.
간접 주소 참조 함수, INDIRECT
절대참조 방식으로 참조 범위를 지정하여 수식 복사하여 붙여넣기 시에도 참조 범위가 변경되지 않도록 하는 것을 봤습니다.
하지만 이 방식은 참조 범위가 고정되어 있기 때문에 유연한 방식이라고 할 수 없습니다.
만약 참조 범위가 변경된다면, 해당 절대참조 범위가 적용된 모든 함수 수식에 변경이 필요하기 때문입니다.
이러한 문제에 도움을 줄 수 있는 함수가 바로 INDIRECT 함수 입니다.
함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(INDIRECT)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.
위의 그림에서 =INDIRECT( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (ref_text, [a1]) 의 의미는 아래와 같습니다.
ref_text : 참조 범위를 나타내는 텍스트 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
[a1] : TRUE 또는 생략시 기본적으로 사용되는 A1 형태, FALSE 의 경우 R1C1 형태의 주소체계를 사용합니다. 이 값을 두 번째 인수로 입력해야 합니다. (선택 입력)
F3 셀에 =VLOOKUP(E3,INDIRECT(“B3:C8”),2,FALSE) 함수 수식을 입력합니다.
F3 셀에 김민준 학생의 생년월일 값이 올바르게 출력됩니다.
F3 셀을 복사하여 F4 셀에 붙여넣기 합니다.
F4 셀에 김민준 학생의 생년월일 값이 올바르게 출력됩니다.
절대참조 방식이 아닌 INDIRECT 함수를 사용해서 동일한 결과를 얻었습니다.
INDIRECT 함수의 인수인 “B3:C8” 은 참조 범위를 텍스트로 입력한 것 이므로, 복사 붙여넣기 작업을 하더라도 자동으로 변경되지 않았기 때문입니다.
INDIRECT 함수를 사용했지만 절대참조를 사용한 경우와 달라진 것이 별로 없습니다.
여전히 참조 범위가 변경되면 INDIRECT 함수의 인수 값을 변경하고 다른 셀에 복사하여 붙여넣기를 해야하기 때문 입니다.
이런 상태라면 굳이 INDIRECT 함수를 사용할 필요가 없습니다.
간접 주소 참조 함수, INDIRECT 활용 방법
INIDRECT 함수에는 텍스트로 작성된 참조 범위 인수가 입력됩니다.
그 인수에 텍스트를 직접 입력하는 것이 아니라 텍스트가 입력된 셀의 참조를 입력해도 된다면 상황이 많이 달라집니다.
바로 이것이 가능하기 때문에 INDIRECT 함수가 빛을 발하게 됩니다.
F6 셀에 INDIRECT 함수의 첫 번째 인수 ref_text 에 입력될 참조 범위를 텍스트로 입력합니다.
F3 셀의 수식도 =VLOOKUP(E3,INDIRECT($F$6),2,FALSE) 형태로 변경합니다.
INDIRECT 함수의 첫 번째 인수로 F6 셀을 절대참조 방식으로 입력했습니다.
F3 셀의 값이 정상적으로 출력되고 있음을 확인할 수 있습니다.
F3 셀을 복사하여 F4 셀에 붙여넣기 한 후 값을 확인해 보겠습니다.
F4 셀의 값이 정상적으로 출력되고 있음을 확인할 수 있습니다.
이 상태에서 만약 참조 범위가 변경된다면 어떻게 하면 될까요?
이제 딱 하나만 바꿔주면 됩니다.
바로 F6 셀에 입력된 참조 범위의 텍스트 값만 변경된 참조 범위에 맞게 수정하면 됩니다.
기존에 입력된 VLOOKUP 수식은 변경할 필요가 없어졌습니다.
INDIRECT 함수를 사용해서 유연하게 대응할 수 있도록 VLOOKUP 함수 수식이 변경되었습니다.
INDIRECT 함수는 다양하게 활용 가능합니다.
이후의 글에서 여러가지 활용방법을 확인해 보겠습니다.
오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.
각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.
엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.