자동합계를 만드는 참조범위 변경 함수 OFFSET

함수를 사용할 때 범위 지정을 위해 절대참조를 자주 사용합니다. 하지만 범위가 변경될 때마다 다시 지정해야 하는 불편함이 있습니다. 이럴 때 사용할 수 있는 참조범위 변경 함수 OFFSET에 대해서 포스팅하겠습니다. 이 함수는 보통 단독으로는 사용하지 않고 다른 함수와 함께 사용합니다.

 

 

OFFSET 함수 기본 사용법

• 함수의 구성
  =OFFSET(기준 셀, 이동할 행 방향 셀 개수, 이동할 열 방향 셀 개수, 출력할 행 개수, 출력할 열 개수) - 출력할 행 개수, 출력할 열 개수는 옵션입니다.
  - 이동 행과 열 개수에 양수를 입력하면 아래쪽, 오른쪽으로 이동하고, 음수를 입력하면 위쪽, 왼쪽으로 이동합니다.
  
  
• 김종국 차장의 4월 월급을 구한다고 할 경우 K2셀에 '=OFFSET(C2,3,3,1,1)'을 입력합니다.
  - C2셀을 기준으로 3번째 행, 3번째 열의 1개 행, 1개 열을 출력하기에 5,300,000이 출력되었습니다.

=OFFSET(C2,3,3,1,1)을 입력하면 김종국 차장의 월급을 구할 수 있습니다

 

 

SUM/AVERAGE에 OFFSET 함수 사용하기

이 함수는 일반적으로 단독으로 사용하지 않기 때문에 SUM/AVERAGE 함수와 함께 사용합니다.

• '하하, 김종국, 크러쉬'의 2~5월까지의 합계를 구한다고 할 경우 K3셀에 '=SUM(OFFSET(C2,2,1,3,4)'을 입력합니다.
  - C2셀을 기준으로 2번째 행, 1번째 열에서 3개의 행과 4개의 열의 합계를 출력하기에 54,820,000이 출력되었습니다.

=SUM(OFFSET(C2,2,1,3,4)를 입력하면 데이터의 중간 값의 합계를 구할 수 있습니다

 

 

자동합계 범위 만들기

• F1셀에 김종국 차장의 12월까지 월급여 합계를 구할 경우 '=SUM(C7:N7)'을 입력하면 자동으로 합계가 구해집니다.

=SUM(C7:N7)을 하면 김종국 차장의 12월까지 합계를 구할 수 있습니다만..

 

만약 8월까지의 김종국 차장의 월급여 합계를 구한다고 한다면... 이럴 경우에는 OFFSET함수를 사용해야 합니다.

• G1셀에 김종국 차장의 8월까지 월급여 합계를 구할 경우에는
  '=SUM(OFFSET(C8,0,0,1,B2))'를 입력하면 8월까지 월급여 합계를 구할 수 있습니다.
  - C8셀을 기준으로 이동 없이(0, 0) 해당 행(8행)의 B2까지 값을 합하라는 의미입니다.
  - 여기서 B2는 숫자 셀로 숫자의 값을 변경할 때 마다 자동합계가 구해집니다. 예를 들어 10을 입력하면 10열(10월)까지 합계를 구할 수 있습니다.

=SUM(OFFSET(C8,0,0,1,B2)를 입력하면 B2셀의 값(1~12)에 따라 해당 월까지의 합계를 구할 수 있습니다

 

이상 OFFSET함수의 사용법과 자동합계를 만드는 참조범위 변경 방법에 대해 설명드렸습니다.

감사합니다.