OFFSET 함수

엑셀에서 offset 함수는 다음과 같이 정의됩니다.

 

= OFFSET(reference, rows, cols, [height], [width])

 

• reference 는 기준이 되는 셀입니다. (필수 인풋)
• rows 는reference 셀에서 떨어져 있는 행의 수 입니다. (필수 인풋)
  • rows = 0 이면 reference 셀과 행이 똑같은 곳의 셀이나 범위를 의미합니다.
  • rows = 1 이면 reference 셀에서 한 행 아래에 위치한 셀이나 범위를 의미합니다.
  • rows = -1 이면 reference 셀에서 한 행 위에 위치난 셀이나 범위를 의미합니다.
• cols 는 reference 셀에서 떨어져 있는 열의 수 입니다. (필수 인풋)
  • cols =0 이면 reference 셀과 열이 똑같은 곳의 셀이나 범위를 의미합니다.
  • cols = 1 이면 reference 셀에서 한 열 오른쪽에 위치한 셀이나 범위를 의미합니다.
  • cols= -1 이면 reference 셀에서 한 열 왼쪽에 위치난 셀이나 범위를 의미합니다.

위의 필수인풋 reference, rows, cols로 지정된 새로운 셀에서 [height]와 [width]를 사용하여 범위(배열)을 지정할 수 있습니다. 이 두 input 은 선택 인풋입니다. (생략하면, height =1 , width =1을 의미)

• height는 새로운 셀에서 아래로 height 만큼의 행을 가진 범위를 의미합니다.
• width는 새로운 셀에서 오른쪽으로 width만큼의 열을 가진 범위를 의미합니다.

 

예제 1

 

다음의 엑셀표에서 refrerence 셀은 A1입니다. 이 셀을 기준으로 B4셀에 있는 1을 얻어내는 함수를 만들고 싶습니다.

A1에서 B4까지 떨어져 있는 행은 3, 열은 1이므로 

다음과 같이 수식을 걸어주면, B4셀에 있는 값을 얻을 수 있습니다. 그렇다면 B4셀을 첫셀로 한 4*3 배열(위 그림에서 굵은 선으로 네모한 부분) 의 합계를 구하고 싶을 때는 어떻게 하면 될까요? 바로 아래 그림처럼 하면 됩니다.

offset(A1,3,1)에 height =4, width =3 의 input을 넣어주면 offset(A1,3,1)인 B4 셀에서 시작한 높이 4, 폭 3의 직사각형 배열을 선택하게 되고, 여기에 SUM() 함수를 걸어주면 네모 부분 숫자의 합계가 되겠죠.

만일, 네모 부분의 숫자 중 최댓값을 구하고 싶을 때는 다음과 같이 합니다.

 

이번에는 SUM() 함수대신, MAX() 함수를 썼습니다. 선택된 영역중 가장 큰 값을 반환해 주는 함수입니다.

 

 

예제 2	KOSPI 지수의 수익률 구하기

지수나 종목의 수익률(또는 등락률)은 다음과 같이 정의됩니다.

 

$$지수의 수익률 = \frac{(오늘의 종가 - 어제의 종가)}{어제의 종가} $$

KOSPI 지수의 일별 종가 데이터에서 수익률은 아래와 같이 구할 수 있습니다.

이를 offset 함수를 써서도 구할 수 있는데, reference를 오늘의 종가 즉, B2 셀로 지정하고 rows= 1, cols =0으로 지정하면 바로 아래(B3 셀)의 값을 얻게 됩니다. 즉, 

어제의 종가 = OFFSET(B2, 1, 0)

이 된다. 따라서 다음의 식도 가능합니다.

 

수식은 다소 어려워 보이나 다음처럼 수익률의 개념을 확장할 때 쓸 수 있습니다.

만일, 일일수익률이 아니고 2일간 수익률, 3일간 수익률을 구할 때, rows=1 대신 rows=2 또는 3.. 이렇게만 바꾸어주면 되기 때문에 계산이 용이한 장점이 있습니다.

 

위와 같이 $N$일간 수익률 (위 그림에서는 $N=3$ 인 상황)을 구하고 싶을 때, B1셀에 $N$을 입력하고, 일일수익률 구하는 것처럼 수익률을 구하면 위와 같은 수식이 됩니다.