Y&S 두 여자의 말랑말랑 생활공감

엑셀함수강좌 20강

찾기/참조 함수

OFFSET

OFFSET 함수에 대해 설명한 강의입니다.

▶ OFFSET(기준, 행수, 열수, [높이], [폭])

기준으로부터 행 또는 열 수만큼 떨어진 곳에 있는 특정 높이와 너비의 참조 영역을 표시

행/열 - 행은 아래로, 열은 오른쪽으로 지정된 숫자(행/열)만큼 떨어진 위치의 참조 영역을 돌려주게 된다.

높이/너비 - 참조 영역의 높이(행 수)와 너비(열 수)를 지정할 수 있다.

아래의 연습파일을 다운로드해서 연습하세요.

offset 함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

이전 엑셀 강좌에서 동적인 셀을 참조하기 위한 INDIRECT 함수에 대해서 알아보았었다.

관련글 ☞ 엑셀 함수 22 - INDIRECT 함수 (동적인 셀 참조하기) by Y

이번에는 INDIRECT 함수처럼 동적인 셀을 참조할 수 있는 또다른 참조 함수 OFFSET 함수에 대해 알아보자.

OFFSET(참조, 행, 열, [높이], [너비])

참조 영역에서 행과 열만큼 떨어진 위치의 참조 영역을 돌려주는 함수다.

참조는 셀 주소이고, 행/열/높이/너비는 숫자이거나 숫자가 입력된 셀이다.

행/열 - 행은 아래로, 열은 오른쪽으로 지정된 숫자(행/열)만큼 떨어진 위치의 참조 영역을 돌려주게 된다.

높이/너비 - 참조 영역의 높이(행 수)와 너비(열 수)를 지정할 수 있다.

OFFSET 함수의 행/열이란??

아래와 같이 데이터가 입력되어 있을 때

'=OFFSET(B2,2,0)' 식을 입력하면

B2 셀에서 행이 2번 이동된 셀인 B4셀이 식의 결과가 된다. 

'=OFFSET(B2,0,3)' 식은 B2 셀에서 열이 3번 이동된 셀인

E2 셀이 결과로 출력된다. 

'=OFFSET(B2,4,2)' 식은 B2 셀에서 행이 4, 열이 2만큼 떨어진 위치인

D6 셀이 결과로 출력된다.

OFFSET 함수의 높이/너비란?

'=OFFSET(C2,0,0,3,1)' 식은 C2 셀에서부터

높이(행)이 3칸, 너비(열)이 1칸인 범위를 의미하므로

'C2:C4' 참조 범위를 반환한다.

그러므로 '=SUM(OFFSET(C2,0,0,3,1))' 식은

'=SUM(C2:C4)' 식이 되어 답은 6이 된다. 

OFFSET 함수 활용

아래의 표에서 놀이공원의 섹션별로 평균방문자수를 구하고자한다.

이때 지정된 일수만큼만 구하는 식을 OFFSET 함수를 이용하여 두 가지 방식으로 구해보겠다.

예를 들어, 어드벤처는 8월 1일부터 4일까지의 방문자수의 평균만 구해져야한다.

1. =AVERAGE(C2:OFFSET(C3,C12-1,0))

'OFFSET(C3,C12-1,0)' 식은 'OFFSET(C3,4-1,0)' 식과 같다.

즉, C3 셀에서 3(4-1)만큼 아래로 이동한 셀 C6셀이 구해진다.

그럼 결과적으로 '=AVERAGE(C2:C6)'과 같으므로 4개 숫자의 평균이 구해진다.

2. =AVERAGE(OFFSET(C3,0,0,C12,1))

'OFFSET(C3,0,0,C12,1)' 식은 'OFFSET(C3,0,0,4,1)' 식과 같다.

즉, C3 셀에서부터 높이(행)가 4, 너비(열)가 1인 'C3:C6' 범위가 구해진다.

결국 1번의 경우와 마찬가지로 '=AVERAGE(C2:C6)'의 식이 되어 평균이 구해진다.

그리고 식을 오른쪽으로 수식 복사하면 일수(C12:E12) 값이 변함에 따라

각기 다른 일수만큼의 방문자수평균을 구하는 동적인 식이 구해진다.

이처럼 OFFSET 함수를 이용하면 다른 함수와 함께 사용하여 동적인 참조가 가능해진다.