OFFSET 함수 활용하기

오늘은 조금 어려운, 그렇지만 아주 흥미로운 함수 한 가지를 소개해 드립니다.

혹 Offset()함수라고 들어 보셨는지요?

Offset()함수는 "어떤 셀의 위치를 기준으로 행이나 열의 수만큼 떨어진 곳의 참조 영역을 구해주는 함수"입니다. 어째 좀 복잡하지요? ^^

사용 형식

Offset(특정위치, 이동행 수,이동열 수,구하려는 행개수, 구하려는 열개수)

사용 형식을 보니… 더 복잡하군요. ^^ 하지만 지레 겁먹지 말고 아래를 보세요.

111	=OFFSET(B42,3,2,1,1)

즉 B41 셀(아래의 파란색 셀)에서 행 방향으로 3행, 열 방향으로 2열 떨어진 셀, 그러니까 D45 셀이 되지요. 여기서 행 방향으로 1, 열 방향으로 1에 해당되는 영역이니까 D45 셀 그 자체가 되는 것입니다.

특정 위치

111

222

333

그런데 만약 위의 표에서 노란 색으로 표시된 두 개의 셀 값을 가져오고자 한다면 어떻게 하면 될까요. 사용 형식에 나와 있는 함수의 여러 인수값을 조금 바꾸어 주면 되지 않겠느냐구요? 글쎄요… 한번 해 볼까요?

#VALUE!

=OFFSET(B42,6,2,2,1)

어럽쇼? 에러가 나는군요. 아무리 수식을 쳐다봐도 논리적으로는 아무런 이상이 없어 보이는데 말입니다!

이런 경우에는, 즉 하나 이상의 셀 참조 영역을 구하고자 할 경우에는 배열의 형태로 입력해야 합니다. 아래를 보세요. 이번에는 제대로 되었지요?

222	{=OFFSET(B42,6,2,2,1)}
333	{=OFFSET(B42,6,2,2,1)}

이런 경우에는 아래 그림과 같이 구하고자 하는 영역과 같은 크기의 범위를 먼저 지정한 다음 수식을 입력하고… 배열 형태로 입력하는 것이므로 Ctrl + Shift + Enter키를 함께 눌러서 마무리 합니다. 그러면 수식 좌우에 {} 괄호가 저절로 생깁니다.

명심하세요! {} 괄호는 타이핑 해서 넣은 것이 아니고 Ctrl + Shift + Enter 키를 눌러야 한다는 것을…

이번에는 위의 표에서 노란색 셀 영역의 합계를 구해 볼까요?

555	=SUM(OFFSET(B42,6,2,2,1))

이번에는 조금더 응용을 해 볼까요?

Sum 함수와 Offset 함수 그리고 Counta 함수를 함께 조합하면 아주 재미있는 것을 만들 수 있습니다. 아래 그림을 보세요.

합계

100

=SUM(OFFSET(F91,0,0,COUNTA(F:F)))

이 부분에 계속 데이터를 입력해 보세요. 리얼 타임으로 입력된 값까지 합산에 반영이 될 것입니다.

10

7

12

5

8

17

41

재미있지요?

이상에서 설명드린 것 이외에도 평소에 얼마나 문제 의식을 갖고 있고 창의적인 마인드를 가지고 있느냐에 따라 활용 방법은 무궁무진 할 것입니다. 다음 시간에는 이러한 개념을 차트에 접목시키면 어떤 일이 일어나는지 소개해 드리겠습니다.

기대하셔도 좋습니다!

오늘은 여기까지…

정리 — OFFSET 함수 활용하기

구분	내용
함수	OFFSET(기준 셀, 이동 행 수, 이동 열 수, 구하려는 행 개수, 구하려는 열 개수)
역할	기준 셀에서 지정한 행, 열만큼 떨어진 곳의 참조 영역을 구함
단일 셀	=OFFSET(B42,3,2,1,1) 은 기준 셀에서 3행, 2열 떨어진 D45 셀
여러 셀	{=OFFSET(B42,6,2,2,1)} 처럼 배열수식(Ctrl + Shift + Enter)으로 입력
합계	=SUM(OFFSET(B42,6,2,2,1)) 의 결과는 555
동적 합계	=SUM(OFFSET(F91,0,0,COUNTA(F:F))) 는 데이터를 계속 입력해도 합계에 반영됨

자주 묻는 질문 (FAQ)

마치며

OFFSET 함수는 다음 시간의 동적 차트로 이어지는 기초이므로 충분히 연습해 두시기 바랍니다. VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.