엑셀 INDIRECT 함수 활용법: 이름 범위부터 여러 시트 참조까지

엑셀에서 INDIRECT 함수 사용법만 익히면 다른 시트에서 데이터를 가져오고, 이름 범위를 참조하고, 다른 함수와 결합하여 진정한 만능 도구를 만들 수 있습니다. 익숙해지기까지 약간의 연습이 필요하지만, INDIRECT 함수를 활용하면 생각보다 훨씬 더 많은 작업을 손쉽게 처리할 수 있습니다.

이 글의 내용은 Microsoft 365용 Excel, Excel 2019, Excel 2016에 적용됩니다.

INDIRECT 함수란 무엇인가?

INDIRECT 함수는 텍스트 문자열을 셀 참조로 변환하는 함수입니다. 즉, 다른 셀이나 범위에 대한 참조로부터 정보를 가져옵니다. 이 함수는 텍스트를 기반으로 참조를 생성하기 때문에, 참조 대상 범위에서 셀·행·열이 수정, 추가, 삭제되더라도 참조 자체는 변경되지 않습니다. 또한 생성된 참조는 실시간으로 평가되므로, 언제나 원본 데이터를 정확하게 반영합니다.

설명이 조금 어렵게 느껴진다면 걱정하지 마세요. INDIRECT 함수는 실제 예제와 실습을 통해 접하면 훨씬 쉽게 이해할 수 있습니다. 아래 단계를 직접 따라 해보면 금방 감을 잡으실 수 있을 것입니다.

이름 범위와 함께 INDIRECT 함수 사용하기

엑셀의 이름 범위는 여러 데이터를 하나의 참조명으로 묶어 관리할 수 있는 매우 유용한 기능이며, INDIRECT 함수를 사용하면 해당 정보를 더욱 손쉽게 불러올 수 있습니다. 방법은 다음과 같습니다.

1. 이름 범위가 이미 적용된 엑셀 문서를 엽니다. 이 예제에서는 다양한 음식과 음료의 판매 정보가 들어 있으며, 요일별 수익이 제품 이름을 딴 이름 범위로 정리되어 있습니다.

   

2. 이름 범위를 입력할 셀을 선택하고 그중 하나를 입력합니다. 예제에서는 Burgers(버거)를 사용했습니다. 원한다면 다른 구분 제목과 색상을 추가해도 좋습니다.

   

3. INDIRECT 결과를 표시할 다른 셀을 선택합니다. 특정 음식(여기서는 버거)의 주간 총 판매량을 구하려는 것이므로, 셀에 다음 수식을 입력합니다.

   =SUM(INDIRECT(G5))

4. 이 수식은 SUM 함수를 지정하며, INDIRECT 함수를 통해 G5 셀에 있는 이름 범위(여기서는 Burgers)의 정보를 가져옵니다. 결과값은 3781로, 버거의 주간 총 판매액입니다.

   이 예제에서는 G5 셀의 Burgers를 나머지 두 이름 범위인 Lemonade(레모네이드) 또는 Desserts(디저트)로 바꾸기만 하면, 결과값이 해당 제품의 SUM 합계로 자동으로 변경됩니다.

   

여러 시트에서 INDIRECT 함수 사용하기

INDIRECT 함수는 다른 시트에서 정보를 가져올 때 더욱 강력한 힘을 발휘합니다. 이때 이름 범위를 사용할 필요조차 없습니다.

1. 여러 시트가 있는 엑셀 문서를 열거나, 필요한 정보를 모두 담은 시트들을 새로 만듭니다.

2. INDIRECT 결과를 표시할 시트에서, 정보를 가져올 시트의 이름이 들어간 셀을 만듭니다. 예제에서는 FoodSales입니다.

3. FoodSales 시트에서 판매된 Burgers의 총 개수를 구하려는 것이므로, 다음과 같이 입력했습니다(셀 범위와 시트 이름은 각자 상황에 맞게 교체하세요).

   =SUM(INDIRECT(B4&"!B4:B10"))

   

4. 합계를 구하는 것이 목적이므로 SUM 함수로 시작합니다. 그다음 B4 셀을 INDIRECT 함수의 참조 텍스트로 지정합니다. &(앰퍼샌드)가 함수의 요소들을 연결하며, 뒤따르는 따옴표와 느낌표(!), 그리고 데이터를 가져올 셀 범위인 B4부터 B10까지가 이어집니다.

5. 결과값은 해당 주의 버거 총 판매량입니다. 새로운 한 주의 다른 숫자로 FoodSales2 시트를 만들었다면, B4 셀만 FoodSales2로 수정하면 해당 주의 버거 판매 데이터를 바로 확인할 수 있습니다.

R1C1 스타일 참조와 함께 INDIRECT 함수 사용하기

데이터가 계속 확장되는 시트, 즉 참조하려는 위치가 항상 같은 셀에 있지 않은 경우에는 R1C1 스타일 참조를 INDIRECT 수식과 함께 사용하면 필요한 정보를 얻을 수 있습니다. 여기서도 음식 판매 예제를 이어가지만, 전체 주간 판매 총계를 살펴보는 상위 수준의 워크시트라고 가정해 보겠습니다.

1. 필요한 데이터가 모두 담긴 엑셀 문서를 열고 INDIRECT 함수의 결과를 표시할 셀을 선택합니다. 예제에서는 월간 음식 판매 총계를 확인 중이며, 가장 최근 월의 판매 총계를 알고 싶습니다.

2. 예제의 수식은 다음과 같습니다.

   =INDIRECT("R12C"&COUNTA(12:12),FALSE)

   

3. INDIRECT 함수는 R12(12행) 뒤에 열을 의미하는 C를 붙여 따옴표로 묶어 사용합니다. &가 함수의 두 부분을 연결하며, COUNTA 함수로 12행에서 비어 있지 않은 모든 셀의 개수를 셉니다(행을 선택하거나 12:12를 입력). 그 뒤의 FALSE는 이 참조가 R1C1 방식임을 지정합니다.

4. 결과값은 표의 마지막 항목, 즉 이 예제에서는 8102($8,102)입니다. 이후 4월 판매 데이터를 추가하면 최신 판매 숫자가 실시간으로 자동 업데이트됩니다.