OFFSET 함수 사용 방법 정리

Offset 함수는 "어떤 셀의 위치를 기준으로 행이나 열의 수만큼 떨어진 곳의 참조 영역을 구해주는 함수"입니다. 알 듯 모를 듯한 설명이죠?

기본 사용 형식

Offset 함수는 5개의 인수를 가집니다. 얼핏 복잡해 보입니다만 겁먹을 건 없습니다.

아래와 같은 표가 있다고 할 경우, B2 셀을 기준으로 하여 Offset 함수의 인수를 다음과 같이 지정하면 결과값은 '19'가 됩니다.

I8 셀에 입력된 수식은 다음과 같습니다.

=OFFSET(INDIRECT(I2),I3,I4,I5,I6)

B2 셀에서 행 방향으로 3행, 열 방향으로 3열 떨어진 셀, 즉 E5 셀이 되겠지요? 여기서 참조 높이가 1, 참조 너비가 1이니까 E5 셀에 있는 값을 가져오게 됩니다.

만약 아래와 같이 회색으로 표시된 두 셀 값을 가지고 오려면 어떻게 하면 될까요? 다른 조건은 똑같고 가져올 행 수만 2행이니까 '참조 높이' 인수를 2로 바꿔주면... 오류가 발생합니다. 아무리 들여다봐도 논리적으로는 이상이 없어 보이는데 말이지요. 이런 경우에는 배열 형태로 입력해야 합니다.

가져올 행 수가 2이므로 같은 크기를 범위로 지정하고(위의 경우에는 I8:I9), 아래 수식을 입력한 후, <Ctrl> + <Shift> + <Enter> 키를 함께 눌러줍니다. 수식 좌우의 중괄호 { }는 손으로 타이핑 한 게 아니라 <Ctrl> + <Shift> + <Enter> 키를 누르면 자동으로 생기는 것임에 유의하세요!

[I8:I9] {=OFFSET(INDIRECT(I2),I3,I4,I5,I6)}

위 표에서 회색 영역의 합계를 구해 볼까요?

I11 셀에는 이런 수식이 들어 있습니다.

=SUM(OFFSET(INDIRECT(I2),I3,I4,I5,I6))

자동 합계 구하기

Sum, Offset, Counta 함수를 함께 조합하면 재미있는 것을 만들 수 있습니다. 아래와 같은 자료가 있다고 할 때, 회색 부분에 숫자를 입력해 보세요. 실시간으로 결과에 반영됨을 알 수 있습니다.

O2 셀에 입력된 수식은 이렇습니다. Offset 함수에 대해 이해가 되었다면 어려운 부분은 없으리라 생각합니다. 곰곰이 생각해 보시고 혹시라도 이해 안되는 부분이 있다면 질문주세요.

=SUM(OFFSET(L2,0,0,COUNTA(L:L)))

응용 예제: 스크롤 막대로 영역을 선택하면 합계 자동 계산

예제 파일의 'Sheet2'에서 스크롤 막대로 행 수와 열 수를 선택하면, 지정한 범위만큼 테이블에 색깔이 칠해지고 합계도 구해지도록 해보겠습니다.

(1) 나중에 사용할 이름을 미리 정의해 둡니다. '수식' 탭의 '이름 관리자'를 클릭하여 어디에 무슨 이름으로 정의되어 있는지 확인해 보세요.

(2) '개발 도구 - 삽입'을 클릭합니다. '양식 컨트롤'에 있는 '스크롤 막대'를 선택하고 J2:K2 셀에 삽입합니다. 스크롤 막대를 마우스 오른쪽 버튼으로 클릭하고 '컨트롤 서식' 메뉴를 선택합니다.

(3) '컨트롤 서식' 대화상자에서 '최대값', '최소값', '셀 연결' 항목을 지정합니다. 최대값을 12로 지정한 이유는 테이블의 가로 방향 크기, 즉 행 수가 12개 밖에 없기 때문입니다.

(4) 같은 방법으로 J3:K3 셀에 스크롤 막대를 삽입하고 '컨트롤 서식'을 지정합니다.

(5) 스크롤 막대에 연결할 코드를 작성합니다. <Alt> + <F11> 키를 눌러 Visual Basic Editor를 표시한 다음 아래 코드를 입력합니다. VBA 강좌가 아니므로 코드는 최소화했습니다.

Sub SelectRange()
    Dim rStart As Range
    Set rStart = Range("Start")
    
    With rStart.CurrentRegion
        .Interior.ColorIndex = xlNone
        .Font.ColorIndex = 1
    End With
    
    With Range(rStart, rStart.Offset(Range("MyRow") - 1, Range("MyCol") - 1))
        .Interior.ColorIndex = 8
    End With
End Sub

(6) '선택 행 수' 오른쪽에 있는 스크롤 막대를 마우스 오른쪽 버튼으로 클릭하고 [매크로 지정]을 선택합니다. [매크로 지정] 대화상자에서 'SelectRange' 매크로를 선택하고 [확인] 버튼을 클릭합니다.

(7) '선택 열 수' 옆의 스크롤 막대에 대해서도 (6) 과정을 반복합니다.

(8) '선택된 범위 합계'를 구할 K5 셀에 수식을 입력합니다.

=SUM(OFFSET(B2,0,0,L2,L3))

이제 스크롤 막대를 클릭하면 지정한 영역만큼 색상이 지정되고 K5 셀에 합계도 구해집니다.

정리 — OFFSET 함수 활용 단계

단계	내용
기본	OFFSET(reference,rows,cols,[height],[width])로 단일 셀 참조
여러 셀	height/width > 1이면 배열 수식(Ctrl+Shift+Enter)
합계	SUM(OFFSET(...))으로 배열 수식 없이 합계
자동 확장	COUNTA를 height에 사용해 데이터만큼 범위 확장
대화형 응용	이름 정의 + 스크롤 막대 + VBA로 범위 선택 도구

자주 묻는 질문 (FAQ)

마치며

VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.