VLOOKUP과 HLOOKUP의 기본기, INDEX와 MATCH의 유연성, 현대적인 XLOOKUP의 강력함을 배웠습니다.
이제 엑셀 조회 및 참조 함수의 영역을 더욱 확장하여, 셀 주소를 텍스트로 다루거나, 특정 위치로부터 상대적인 범위/셀을 참조하는 고급 기술을 알아볼 차례입니다.
INDIRECT와 OFFSET 함수들은 동적인 보고서, 가변적인 대시보드, 그리고 사용자 입력에 따라 내용이 바뀌는 유연한 수식을 만들 때 필수적인 역량을 제공합니다.
왜 INDIRECT와 OFFSET 함수를 마스터해야 할까요?
일반적인 엑셀 수식은 셀 주소(예: A1, B2:C5)를 직접 참조합니다. 하지만 때로는 '시트1'에 있는 A1셀이 아니라, 특정 셀에 입력된 텍스트 문자열("시트2!B3")을 실제 셀 주소로 변환하여 참조해야 할 때가 있습니다.
또는 '이 셀에서 3칸 아래, 2칸 오른쪽'과 같이 기준점에서 상대적으로 떨어진 범위를 동적으로 지정해야 할 때도 있습니다.
INDIRECT와 OFFSET 함수는 이러한 요구사항을 해결해 줍니다. 이들을 통해 우리는 엑셀이 고정된 위치만을 참조하는 것이 아니라, 사용자의 입력이나 특정 조건에 따라 참조 대상을 동적으로 변경하도록 지시할 수 있습니다. 이는 보고서의 유연성을 극대화하고, 복잡한 데이터 모델을 구축하는 데 필수적인 고급 기술입니다.
주요 함수들
다음 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- INDIRECT: 텍스트 문자열로 지정된 셀 참조를 반환 (동적 시트/셀 참조)
- OFFSET: 기준 셀로부터 지정된 행/열만큼 떨어진 셀 또는 범위 반환 (동적 범위 설정)
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 간접 참조 및 동적 범위 설정의 강력함을 경험해 봅시다.
1. INDIRECT: 텍스트 문자열을 실제 참조로 변환하는 마법
INDIRECT 함수는 텍스트 문자열을 실제 셀 참조로 변환하여 그 셀의 내용을 가져옵니다. 이는 사용자의 입력이나 다른 셀의 값을 기반으로 참조 대상을 동적으로 변경해야 할 때 유용합니다.
- 기본 형식: =INDIRECT(참조_텍스트, [A1_참조_스타일])
- 참조_텍스트: 셀 참조를 나타내는 텍스트 문자열 (예: "A1", "Sheet2!B3"). 따옴표로 묶거나, 셀 주소를 포함하는 셀을 참조합니다.
- A1_참조_스타일 (선택 사항): 참조 스타일 (TRUE/생략: A1 스타일, FALSE: R1C1 스타일). 대부분의 경우 TRUE를 사용합니다.
실무 예제 1 (시트 이름으로 동적 데이터 가져오기): 월별 판매 데이터를 각 월별 시트(1월, 2월, 3월 등)에 따로 관리하고 있습니다. 기준 시트에서 드롭다운 목록으로 월을 선택하면 해당 월 시트의 특정 셀에 있는 총 매출액을 자동으로 가져오고 싶습니다.
데이터 구조:
- 보고서 시트 (Sheet1):
| A | B |
| 선택월 | 1월 |
| 총 매출액 |
- 1월 시트 (1월)
- 2월 시트 (2월)
예제 목표: B2 셀의 월 선택에 따라 해당 월 시트의 B10 셀 (총 매출액이 있는 셀) 값을 Sheet1의 B3 셀에 자동으로 표시하세요.
해결 방법: Sheet1의 B3 셀에 다음 수식을 입력합니다. =INDIRECT(B2&"!B10")
- B2: '1월' 텍스트
- &"!B10": 텍스트 "!"와 "B10"을 연결하여 "1월!B10"이라는 문자열을 만듭니다.
- INDIRECT(...): 이 문자열을 실제 셀 주소 1월!B10으로 변환하여 해당 셀의 값을 가져옵니다.
결과: B2 셀에 '1월'을 선택하면 B10셀값 1,200이, '2월'을 선택하면 1,500이 표시됩니다.
최상급 실무 활용: INDIRECT 함수는 다수의 시트 또는 통합 문서를 동적으로 참조하여 데이터를 가져올 때 매우 강력합니다.
월별/부서별/제품별로 데이터가 분리된 상황에서 자동화된 대시보드, 요약 보고서, 또는 데이터 취합 시스템을 구축하는 데 필수적입니다. 데이터 유효성 검사로 드롭다운 목록을 만들고 INDIRECT를 연동하면 더욱 사용자 친화적인 보고서가 됩니다.
실무 예제 2 (행/열 번호로 동적 셀 참조): 테이블에서 특정 행과 열의 조합으로 데이터를 가져와야 하는데, 행/열 번호가 다른 셀에 입력되어 있습니다.
| A | B | C | |
| 1 | 1월 | 2월 | |
| 2 | 김 | 100 | 120 |
| 3 | 이 | 150 | 180 |
데이터 조회:
- 조회 행 번호: E2 셀에 2 (김)
- 조회 열 번호: F2 셀에 2 (1월)
예제 목표: G2 셀에 E2와 F2에 입력된 행과 열 번호에 해당하는 셀(B2)의 값을 가져오세요.
해결 방법: G2 셀에 다음 수식을 입력합니다. =INDIRECT(ADDRESS(E2, F2))
- ADDRESS(E2, F2): 행 번호(2)와 열 번호(2)를 기반으로 "B2"라는 텍스트 주소를 만듭니다.
- INDIRECT(...): "B2" 텍스트를 실제 셀 참조로 변환하여 그 값을 가져옵니다.
결과: 100
최상급 실무 활용: ADDRESS 함수와 INDIRECT를 결합하면 데이터 테이블의 구조가 바뀌더라도 유연하게 참조할 수 있는 동적인 수식을 만들 수 있습니다. 이는 복잡한 스프레드시트에서 유지보수성을 높이고, 특정 기준에 따라 데이터 포인트를 동적으로 찾아내야 할 때 매우 유용합니다.
2. OFFSET: 기준 셀로부터 상대적인 범위/셀 참조
OFFSET 함수는 기준 셀로부터 지정된 행과 열만큼 떨어져 있는 셀 또는 셀 범위를 반환합니다. 반환되는 범위의 높이와 너비도 지정할 수 있어 동적인 범위 설정에 매우 강력합니다.
- 기본 형식: =OFFSET(기준_셀, 행_이동, 열_이동, [높이], [너비])
- 기준_셀: 시작점으로 사용할 셀 (항상 절대 참조 $)
- 행_이동: 기준_셀에서 위/아래로 이동할 행의 수 (음수: 위로, 양수: 아래로).
- 열_이동: 기준_셀에서 좌/우로 이동할 열의 수 (음수: 왼쪽으로, 양수: 오른쪽으로).
- 높이 (선택 사항): 반환할 범위의 행 개수 (기본값: 1).
- 너비 (선택 사항): 반환할 범위의 열 개수 (기본값: 1).
실무 예제 1 (단일 셀 참조): 직원 급여 목록에서 '김철수'의 월급 바로 아래에 있는 '이영희'의 월급을 가져오세요.
| A | B |
| 직원명 | 월급 |
| 김철수 | 300 |
| 이영희 | 280 |
| 박지성 | 350 |
예제 목표: '김철수'의 월급 셀(B2)을 기준으로 한 칸 아래에 있는 월급을 가져오세요.
해결 방법: C2 셀에 다음 수식을 입력합니다. =OFFSET(B2, 1, 0)
- B2: 기준 셀
- 1: 1칸 아래로 이동
- 0: 열은 이동하지 않음
결과: 280
최상급 실무 활용: OFFSET은 동적으로 변하는 데이터의 시작점이나 끝점을 참조해야 할 때 유용합니다.
예를 들어, 매월 추가되는 데이터의 가장 마지막 행에 있는 값을 자동으로 가져오거나, 특정 기준값의 '다음' 또는 '이전' 값을 참조할 때 사용됩니다.
실무 예제 2 (동적 범위 설정): 최근 3개월간의 매출액 합계를 동적으로 계산해야 합니다.
| 월 | 매출액 |
| 1월 | 100 |
| 2월 | 120 |
| 3월 | 150 |
| 4월 | 130 |
| 5월 | 160 |
| 6월 | 180 |
예제 목표: 가장 최근 3개월(4월, 5월, 6월)의 매출액 합계를 계산하세요. (데이터가 추가될 때마다 자동으로 업데이트)
해결 방법: D2 셀에 다음 수식을 입력합니다. =SUM(OFFSET(B1, COUNT(B:B), 0, -3, 1))
- B1: 기준 셀 (매출액 열의 헤더)
- COUNT(B:B): B열의 숫자 데이터 개수 (6월까지 6개이므로 6을 반환) → B1에서 6칸 아래로 이동 (즉, B7 셀로 이동)
- 0: 열은 이동하지 않음
- -3: 이동한 셀(B7)을 기준으로 위로 3칸 범위 (B7, B6, B5)를 선택
- 1: 너비는 1열
결과: 470 (130+160+180)
최상급 실무 활용: OFFSET은 차트의 데이터 범위를 동적으로 설정하여 최신 데이터를 자동으로 반영하거나, 데이터 유효성 검사 목록을 동적으로 변경하는 데 사용됩니다. SUM, AVERAGE 등 집계 함수와 결합하여 가장 최근 N개의 데이터에 대한 요약 보고서를 만들 때 매우 강력합니다. COUNT 함수와 함께 사용하면 데이터의 마지막 행을 기준으로 유연하게 범위를 지정할 수 있습니다.
INDIRECT와 OFFSET 함수를 통해 텍스트 문자열 기반의 간접 참조와 기준 셀로부터의 상대적 동적 범위 설정 방법을 심층적으로 살펴보았습니다. 이 함수들은 엑셀 보고서의 유연성과 자동화를 극대화하며, 복잡한 데이터 모델을 구축하는 데 필수적인 고급 기술입니다.