아이엑셀러 닷컴

엑셀 도움말에서는 Offset 함수에 대해 이렇게 소개하고 있습니다.

"셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환합니다. 반환되는 참조는 단일 셀 또는 셀 범위일 수 있습니다. 반환할 행 및 열 수를 지정할 수 있습니다."

뭔가 알 듯 모를 듯한 설명이죠? 이렇게 표현하면 어떨까요?

기준셀에서 지정한 위치(행, 열)만큼 이동한 다음, 지정한 크기(행, 열)만큼 참조 범위를 가져오는 함수

(도긴개긴인가?) 뭐, 하여튼 이 정도로 이해하고 계속 강의를 들으시기... 아니 보시기 바랍니다.

Offset 함수 기본 사용 형식

Offset 함수는 아래와 같이 5개의 인수를 가집니다. 얼핏 복잡해 보입니다만 나중에는 이해하기 싫어도 이해하게 될 예정(?)이므로 전혀 겁먹을 것 없습니다.

아래와 같은 표가 있습니다. B2 셀(빨간색 셀)을 기준으로 하여 Offset 함수 인수를 다음과 같이 지정하면 결과값(I8 셀)은 '19'가 됩니다.

I8 셀(검정색 셀)에 입력된 수식은 이렇습니다.

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

첫 번째 인수(Reference)로는 특정한 셀에 입력된 문자열 형태의 정보를 셀 참조 형식으로 변환하기 위해 Indirect(I2)와 같은 형태로 해 주었습니다만, 그냥 B2 셀이라고 이해하고 넘어가세요.

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 함수를 이용하여 합계를 구하는 것이기에 셀을 하나만 이용하였고 배열 수식도 아니기에 그냥 수식 입력 후에 그냥 ENTER 키를 누르면 됩니다.

자동 합계 구하기

엑셀의 기본 함수들인 Sum, Offset, Counta를 조합하면 재미있는 것을 만들 수 있습니다. 아래와 같은 자료가 있습니다. 회색 부분에 숫자를 입력하고 ENTER 키를 누르자 마자 O2 셀 값이 실시간으로 업데이트 됩니다.

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 강의가 아니므로 코드는 최소화했습니다. 지금 단계에서는 아래 코드를 굳이 이해하려 하지 마시고 그냥 코드 내용을 복사해서 붙여넣기 해도 됩니다.

참고

아래 코드가 이해되지 않는 분들은 나중에 VBA를 공부하신 다음 다시 보세요. 하지만 예제 파일을 열어서 컨트롤(스크롤 막대)과 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

CODE

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

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

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

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

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

이 시점에서 저기 어디선가 이런 궁시렁거림(?)이 들리는 듯 합니다.

"지금껏 이런 강의는 없었다. 이것은 Excel 강의인가 VBA 강의인가..."
ㅡ 영화 '극한직업' 류승룡 버전으로

강의 진행 편의 상 Excel과 VBA를 나누었을 뿐이지 VBA는 언젠가는 넘어야 할 산입니다. 그러니 너무 고민하지 말고 잘 따라오시기 바랍니다.

오늘은 여기까지...

들어가기 전에

Offset 함수 기본 사용 형식

자동 합계 구하기

[응용 예제] 스크롤 막대를 클릭하면 자동으로 합계 계산하기