엑셀함수 OFFSET – 행열이동 후 참조구하기

공개됨 글쓴이 Admin댓글 남기기

엑셀에서 OFFSET함수는 이름만 봐도 뭔가 어려워 보이는 함수이다. OFFSET이라는 단어가 주는 모호함 때문일 것이다. OFFSET을 영어 사전에서 찾아보면 “상쇄하다”, “만회하다”, “오프셋인쇄”…? 무슨 뜻이지??? 볼수록 더 미궁에 빠지는 단어이다.

그러나 컴퓨터와 관련된 분야에서는 “어떤 대상으로부터 벗어난 정도, 떨어진 정도”라는 뜻으로 사용된다. OFFSET함수는 어떤 셀로부터 행과 열을 이동한 후 참조를 구해주는데 OFFSET의 뜻을 알고 나면 함수의 사용법이 조금은 쉽게 이해될 것이다.

구문(Syntax)
OFFSET(reference, rows, cols, [height], [width])

셀에서 지정된 수 만큼 행과 열을 이동한 후의 참조를 구해준다.

인수 :

– Reference : 행과 열을 이동하기 위한 기준점(셀 위치)
– rows : 행방향으로 이동할 거리, 양수는 아래로 이동, 음수는 위로 이동
– cols : 열방향으로 이동할 거리, 양수는 오른쪽으로 이동, 음수는 왼쪽으로 이동
– height : 반환되는 참조영역의 높이(생략시 기본값이 1로 지정됨)
– width : 반환되는 참조영역의 너비(생략시 기본값이 1로 지정됨)

 

OFFSET함수의 이해

OFFSET함수를 이해하기 위해 다음 예를 살펴보자.

다음 그림에서 셀 “B3″로부터

  • 아래로 2칸
  • 오른쪽 1칸

이동한 위치는 셀 “C5″이고 이 위치의 값은 8이다.

이것을 OFFSET함수로 다음과 같이 표현할 수 있다.

=OFFSET(B3,2,1) => 8

네번째/다섯번째 인수는 생략되었고 생략시 기본값이 1이므로 이 수식은 다음과 동일하다.

=OFFSET(B3,2,1,1,1) => 8

 

이번에는 네번째, 다섯번째 인수를 넣은 예를 보자.
셀 “B12″로부터

  • 아래로 2칸,
  • 오른쪽 1칸

이동한 위치는 셀 “C14″이고 이 위치부터

  • 높이3칸
  • 너비2칸인

범위는 “C14:D16″이다

이 범위를 더하면 69가 나오는데 이 것을 OFFSET함수로 다음과 같이 표현할 수 있다.

=SUM(OFFSET(B12,2,1,3,2)) => 69

 

OFFSET함수응용 – 누적판매실적 구하기

OFFSET함수를 좀 더 응용해보자. 아래와 같이 월별 판매실적 데이터가 있을 때 특정월까지의 누적판매실적을 구해야 할때 OFFSET함수를 사용할 수 있다.

위의 판매실적 자료에서 7월까지의 서울지역 판매실적 누계는 다음과 같이 구할 수 있다.

=SUM(OFFSET(C5,0,0,D20,1))  => 7,551,000

각각의 인수를 살펴보자

  • 첫번째 인수 C5 : 1월 실적의 위치
  • 두번째 인수 0 : 아래로 0칸(즉 현재 위치 그대로)
  • 세번째 인수 0 : 오른쪽으로 0칸(즉 현재 위치 그대로)

OFFSET(C5,0,0,D20,1) 에서 두번째,세번째가 0이므로 아래로, 오른쪽으로 이동하지 않는다.따라서 참조위치가 C5그대로이며,

이 위치로부터

  • 네번째 인수 D20 : 높이가 7칸(월을 입력받는데 7이면 7칸이므로 7월까지 참조된다)
  • 다섯번째 인수 1 : 너비가 1칸(서울지역만 포함)인

범위가 구해진다.

구해진 범위는 아래 그림과 같다.

이 범위를 SUM하면 7월까지의 누적판매실적 7,551,000원이 구해진다.

 

 

마지막으로 하나만 더 살펴보자.

판매실적 자료에서 7월까지의 전체지역 판매실적 누계는 다음과 같이 구할 수 있다.

=SUM(OFFSET(C5,0,0,D20,3))  => 17,356,000

서울지역 판매실적누적을 구하는 수식과 거의 동일한데 마지막 인수인 너비만 다르다.

서울,경기,충청지역 3군데를 포함해야 하므로 마지막 인수인 너비가 3칸이다.

 

간단히 OFFSET함수의 기능을 살펴보았는데 사실 이 함수가 가진 강력한 기능 중 극히 일부를 본 것이다. 다음에는 OFFSET함수를 이용해서 동적으로 범위를 참조하는 등 다양하게 응용하는 법을 다루어 보고자 한다.

 

[ 엑셀 찾기 및 참조영역 함수 목록 ]

 

댓글 남기기

이메일은 공개되지 않습니다.