엑셀 OFFSET 함수로 지정한 열 또는 행 참조하기

이전 글에서 VLOOKUP 함수 및 HLOOKUP 함수의 단점인 기준값이 가장 왼쪽 열 또는 첫 행에 위치해야 하는 단점을 극복할 수 있는 INDEX 함수와 MATCH 함수를 이용하여 데이터를 참조하는 방법에 대해 알아보았습니다. 이번에는 기준 셀로부터 일정하게 떨어져 있는 열 또는 행을 참조할 때 사용할 수 있는 OFFSET 함수에 대해 알아보겠습니다.

 

엑셀 INDEX 함수와 MATCH 함수로 VLOOKUP 함수 기능 발휘하기

엑셀 INDEX 함수와 MATCH 함수로 VLOOKUP 함수 기능 발휘하기

 

 

 

 

1. OFFSET 함수

  OFFSET 함수는 기준 셀을 지정하고 그 셀로 부터 떨어진 거리를 주어 데이터를 참조할 위치를 지정할 때 사용하는 함수로 다음과 같이 사용할 수 있습니다.

 

    OFFSET( Reference, Rows, Columns, Height, Width )

 

Reference : 참조의 기준이 되는 셀 ( 또는 범위)

 

Rows : 데이터를 참조할 영역의 첫 행과 Reference에 입력한 기준의 첫 행 사이의 간격

 

Columns : 데이터를 참조할 영역의 첫 열과 Reference에 입력한 기준의 첫 열 사이의 간격

 

Height : 데이터를 참조할 영역의 행의 개수

 

Width : 데이터를 참조할 영역의 열의 개수

 

예를 들어 다음과 같이 셀에 수식을 입력했을 때를 확인해 보겠습니다.

 

   = OFFSET(A1,1,1,2,2)

: 기준인 A1셀에서 행 방향으로 1행, 열 방향으로 1열을 이동한 지점인 B2 셀에서 행 방향으로 2행, 열 방향으로 2열을 참조하라는 것으로 결과는 B2:C3의 데이터를 참조하게 됩니다.

 

위의 함수를 실행하실 때는 수식을 입력하시고 Ctrl + Shift + Enter 키를 누르셔야 에러 없이 실행이 됩니다. 또한, Height와 Width 크기에 맞게 셀을 선택하시고 Ctrl + Shift + Enter 키를 누르시면 참조값들이 제대로 화면에 나타납니다. (예에서는 2행 2열의 범위를 선택한 상태에서 Ctrl + Shift + Enter 키를 누름)

 

 

 

 

2. 동적 참조

  OFFSET 함수와 COUNTA 함수를 이용하면 참조 영역을 동적으로 가져올 수 있게 됩니다. 참조 영역을 동적으로 불러오려면 다음과 같이 사용하면 됩니다.

 

   = OFFSET ( Reference, 0, 0, COUNTA(열 범위))

: Reference로 지정한 기준 셀의 데이터가 입력되어 있는 열 전체를 참조하게 되며 열에 데이터가 추가되면 자동으로 이를 인식하여 참조하게 됩니다.

 

   = OFFSET ( Reference, 0, 0, 1, COUNTA(행 범위))

: Reference로 지정한 기준 셀의 데이터가 입력되어 있는 행 전체를 참조하게 되며 행에 데이터가 추가되면 자동으로 이를 인식하여 참조하게 됩니다.

 

   = OFFSET ( Reference, 0, 0, COUNTA(열 범위), COUNTA(행 범위))

: Reference로 지정한 기준 셀의 데이터가 입력되어 있는 행과 열 전체를 참조하게 되며 행에 데이터가 추가되면 자동으로 이를 인식하여 참조하게 됩니다.

 

참조하는 열과 행의 데이터는 공백 없이 연속적으로 입력되어 있어야 합니다.

 

 

 

 

3. 예제로 확인

  [그림 1]과 같은 가계부를 엑셀 시트로 작성하였을 때를 예제로 OFFSET 함수를 이용하여 동적으로 데이터를 참조하는 방법을 확인해보겠습니다.

 

[그림 1] 가계부 예제

수입과 지출에 보이는 빈 셀은 화면에 표시되지 않지만 0이 입력되어 있습니다.

이제 OFFSET 함수를 이용하여 수입 합계와 지출 합계를 구하는 식을 작성하겠습니다. 임의의 셀에 다음과 같이 수식을 입력합니다.

 

수입 합계를 구하는 수식 :

 

  =SUM(OFFSET(D3,0,0,COUNTA(D:D)))

: OFFSET 함수를 이용하여 기준 셀인 D3셀에서 D열에 데이터가 입력된 전체 셀의 값을 참조하도록 하였으며 참조한 값의 합을 구하도록 수식을 작성하였습니다.

 

지출 합계를 구하는 수식 :

 

  =SUM(OFFSET(E3,0,0,COUNTA(E:E)))

: OFFSET 함수를 이용하여 기준 셀인 E3셀에서 E열에 데이터가 입력된 전체 셀의 값을 참조하도록 하였으며 참조한 값의 합을 구하도록 수식을 작성하였습니다.

 

수식을 입력하시고 엔터키를 누르시면 각 수입과 지출의 합계가 출력되는 것을 보실 수 있습니다.

 

그리고, 19행에 내용을 입력하면 자동으로 그 값이 적용되어 합계가 구해지는 것을 알 수 있습니다. 이와 같이 OFFSET 함수와 COUNTA 함수를 이용하여 동적으로 데이터를 참조하게 되면 데이터가 추가되었을 때 일일이 적용시켜야 하는 수고를 덜 수 있습니다.