INDIRECT 함수는 조회/참조(Lookup & Reference) 범주에 속하는 함수로, 텍스트 문자열로 지정된 참조를 반환합니다. 이 함수를 활용하면 특정 셀이나 셀 범위의 참조에서 값을 손쉽게 가져올 수 있습니다. 이 글에서는 엑셀 INDIRECT 함수로 범위를 활용하는 8가지 방법을 단계별로 자세히 설명해 드리겠습니다.
설명을 이해하기 쉽게 하기 위해 4개 분기의 판매 정보가 담긴 데이터 세트를 사용하겠습니다. 이 데이터 세트는 영업 사원(Sales Person), 1분기(Quarter-1), 2분기(Quarter-2), 3분기(Quarter-3), 4분기(Quarter-4)의 5개 열로 구성되어 있습니다.
연습용 파일 다운로드
엑셀 INDIRECT 범위 활용법 8가지
1. 셀 참조를 통해 값 가져오기
INDIRECT 함수를 사용하면 셀이나 셀 범위의 참조를 통해 값을 불러올 수 있습니다.
여기서는 1분기 데이터가 있는 셀 범위 C4:C8의 값을 가져오겠습니다.
방법은 다음과 같습니다.
⏩ 셀 H4에 아래 수식을 입력합니다.
=INDIRECT("C4:C8")
INDIRECT 함수의 ref_text 인수에 셀 범위 C4:C8을 문자열 형태("C4:C8")로 입력했습니다.
이제 ENTER 키를 누르면 지정한 셀 참조의 모든 값이 표시됩니다.
2. 셀에 저장된 참조를 통해 값 가져오기
참조할 셀이나 셀 범위를 직접 수식에 입력하지 않고, 다른 셀에 저장해 둔 후 그 값을 불러오고 싶다면 역시 INDIRECT 함수를 활용할 수 있습니다.
여기서는 2분기 데이터가 있는 셀 범위 D4:D8의 값을 가져오겠습니다. 셀 참조를 수식에 직접 쓰는 대신 별도의 셀에 저장하는 방식입니다. 아래 데이터 세트에서는 값을 가져올 셀 범위를 미리 셀에 입력해 두었습니다.
⏩ 셀 I4에 아래 수식을 입력합니다.
=INDIRECT(H4)
INDIRECT 함수의 ref_text 인수로 셀 H4를 선택했습니다. 셀 H4에는 셀 범위 D4:D8이 저장되어 있으므로, 해당 범위의 값들을 불러오게 됩니다.
ENTER 키를 누르면 지정된 셀의 모든 값이 화면에 나타납니다.
3. 셀의 값을 조합해 참조 만들기
INDIRECT 함수는 셀에 입력된 값을 활용하여 새로운 셀 참조를 동적으로 생성하는 기능도 제공합니다.
참조를 만드는 과정을 살펴보겠습니다.
⏩ 셀 I4에 아래 수식을 입력합니다.
=INDIRECT("E"&H4)
INDIRECT 함수의 ref_text 인수에 "E"&H4를 사용했습니다. 여기서 E는 열 이름이고, 셀 H4에는 값 5가 저장되어 있습니다. 함수 내에서 열 E와 H4 셀의 값을 연결(&)하여 E5라는 참조를 만들고, 최종적으로 INDIRECT 함수가 E5 셀의 값을 반환합니다.
ENTER 키를 누르면 참조로 지정한 셀의 값이 표시됩니다.
4. 이름 정의 범위로 셀 범위의 합계 구하기
INDIRECT 함수에는 이름 정의 범위(named range)를 사용할 수 있으며, 다른 함수와 결합하면 해당 범위의 값을 계산할 수 있습니다.
범위에 이름을 지정하는 과정을 살펴보겠습니다.
먼저 셀 범위를 선택합니다.
➤ 여기서는 셀 범위 C4:C8을 선택했습니다.
그런 다음 이름 상자(Address Bar)로 이동하여 원하는 이름을 입력합니다.
➤ 여기서는 선택한 범위를 Quarter_1이라고 지정했습니다.
이제 이름 정의 범위를 사용해 Quarter_1의 총 판매액을 계산하기 위해 INDIRECT 함수와 SUM 함수를 함께 사용하겠습니다.
⏩ 셀 I4에 아래 수식을 입력합니다.
=SUM(INDIRECT(I3))
SUM 함수 안에 INDIRECT(I3)를 number1 인수로 사용했습니다.
INDIRECT 함수의 ref_text 인수로는 셀 I3를 선택했으며, 이 셀에는 셀 범위 C4:C8의 이름이 저장되어 있습니다.
따라서 INDIRECT 함수가 해당 셀들의 값을 가져오면, SUM 함수가 그 합계를 계산합니다.
마지막으로 ENTER 키를 누르면 Quarter_1의 총 판매액을 확인할 수 있습니다.
5. INDIRECT 이름 정의 범위로 평균 구하기
AVERAGE 함수와 INDIRECT 함수를 함께 사용하면 이름 정의된 셀 범위의 평균도 계산할 수 있습니다.
⏩ 셀 I4에 아래 수식을 입력합니다.
=AVERAGE(INDIRECT(I3))
AVERAGE 함수 안에 INDIRECT(I3)를 number1 인수로 사용했습니다.
INDIRECT 함수의 ref_text 인수로는 셀 I3를 선택했으며, 이 셀에는 셀 범위 C4:C8의 이름이 들어 있습니다.
이렇게 하면 INDIRECT 함수가 해당 셀들의 값을 가져오고, AVERAGE 함수가 그 평균값을 반환합니다.
ENTER 키를 누르면 Quarter_1의 평균 판매액을 얻을 수 있습니다.
6. 셀 범위의 최댓값 구하기
이름 정의된 범위의 최댓값을 찾고 싶다면 MAX 함수와 INDIRECT 함수를 함께 사용하면 됩니다.
⏩ 셀 I4에 아래 수식을 입력합니다.
=MAX(INDIRECT(I3))
MAX 함수 안에 INDIRECT(I3)를 number1 인수로 사용했습니다.
INDIRECT 함수의 ref_text 인수로는 셀 I3를 선택했으며, 이 셀에는 셀 범위 C4:C8의 이름이 저장되어 있습니다.
이제 INDIRECT 함수가 해당 셀들의 값을 가져오면, MAX 함수가 그중 최댓값을 반환합니다.
ENTER 키를 누르면 Quarter_1의 최대 판매액을 확인할 수 있습니다.
7. 셀 범위의 최솟값 구하기
마찬가지로 MIN 함수와 INDIRECT 함수를 함께 사용하면 이름 정의된 범위의 최솟값도 구할 수 있습니다.
⏩ 셀 I4에 아래 수식을 입력합니다.
=MIN(INDIRECT(I3))
MIN 함수 안에 INDIRECT(I3)를 number1 인수로 사용했습니다.
INDIRECT 함수의 ref_text 인수로는 셀 I3를 선택했으며, 이 셀에는 셀 범위 C4:C8의 이름이 저장되어 있습니다.
이제 INDIRECT 함수가 해당 셀들의 값을 가져오면, MIN 함수가 그중 최솟값을 반환합니다.
ENTER 키를 누르면 Quarter_1의 최소 판매액을 얻을 수 있습니다.
8. INDIRECT 범위로 가장 작은 값들의 평균 구하기
AVERAGE, SMALL, ROW, INDIRECT 함수를 함께 사용하면 셀 범위에서 가장 작은 3개 값의 평균도 계산할 수 있습니다.
⏩ 셀 I4에 아래 수식을 입력합니다.
=AVERAGE(SMALL(F4:F8,ROW(INDIRECT("1:3"))))
AVERAGE 함수 안에 SMALL(F4:F8,ROW(INDIRECT("1:3")))을 number1 인수로 사용했습니다.
SMALL 함수에는 F4:F8을 array(배열)로, ROW(INDIRECT("1:3"))를 위치를 나타내는 k 값으로 사용했습니다.
그리고 ROW 함수 안에는 INDIRECT("1:3")을 reference(참조)로 사용했습니다.
INDIRECT 함수에는 1:3을 ref_text로 지정했는데, 이는 3개의 값을 의미합니다. INDIRECT 함수를 ROW 함수 안에 사용하면 행을 삽입하거나 삭제하더라도 수식이 올바르게 유지되는 장점이 있습니다.
이제 SMALL 함수가 가장 작은 3개 숫자를 반환하고, AVERAGE 함수가 그 숫자들의 평균을 계산합니다.
ENTER 키를 누르면 Quarter_4에서 가장 작은 3개 판매액의 평균을 확인할 수 있습니다.
기억해야 할 주의사항
🔺 ref_text가 유효한 셀 참조가 아니거나 범위 제한을 초과하면 INDIRECT 함수는 #REF 오류를 표시합니다.
🔺 함수 이름을 잘못 입력하면 INDIRECT 함수는 #NAME 오류를 표시합니다.
연습 문제
결론
이 글에서는 엑셀 INDIRECT 함수로 범위를 활용하는 8가지 방법을 살펴보았습니다. 또한 INDIRECT 함수가 오류를 자주 표시하는 경우와 그 원인에 대해서도 함께 다루었습니다. 마지막으로, 제안이나 의견, 피드백이 있다면 언제든지 아래 댓글로 남겨 주세요.
관련 글
- 엑셀에서 INDIRECT ADDRESS 활용법 (12가지 예시)
- 엑셀에서 시트 이름과 함께 INDIRECT 함수 사용하기 (4가지 조건)
- 엑셀 INDIRECT 함수: 다른 시트에서 값 가져오기
- 엑셀 INDIRECT 함수로 드롭다운 목록 만들기