학습(공부)하는 블로그

오늘은 offset 함수를 배우겠습니다.

offset 함수는 주로 범위를 지정할 때 자주 사용하는 함수입니다.

특히 범위를 이름으로 지정하는 경우에 자주 쓰입니다.

보통 사원명부와 같은 자료에서 내가 원하는 자료를 찾아내기 위해서는

앞에서 배운 vlookup 함수나 index match 함수를 활용하면 찾기는 쉽죠.

그런데 이 명부가 변동이 없다면 참 좋겠습니다만

대부분이 그렇지가 않죠.

사원명부에는 신입사원이 새로 생길수도 있고, 퇴직하는 사원이 있을 수도 있습니다.

즉 추가, 삭제가 자주 이루어지면 원하는 자료를 찾기 위해 범위를 새로 지정해야되죠.

한번 두번은 그럴 수 있지만 자주 이러한 일이 반복되면 짜증이 많이 나겠죠.

이럴 때 사용하는 것이 자료의 범위를 이름으로 지정하는 방법입니다.

대신 이름으로 지정한 범위도 변동사항에 따라 자동으로 변하게 하면 되겠죠.

서설이 길어졌네요.

기본적인 문법부터 알려드리겠습니다.

OFFSET(기준셀, 행 이동 칸수, 열 이동 칸수, 행 포함 칸수, 열 포함 칸수)

기준셀 : 기준이 되는 셀입니다.(시작점으로 생각하시면 됩니다.)

행 이동 칸수 : 아래로 이동할 칸수를 말합니다.

열 이동 칸수 : 오른쪽으로 이동할 칸수를 말합니다.

행 포함 칸수 : 현재 위치에서 아래로 포함할 행수를 말합니다.(자료의 높이라고 생각하시면 됩니다.)

열 포함 칸수 : 현재 위치에서 오른쪽으로 포함할 행수를 말합니다.(자료의 폭으로 보시면 됩니다.)

예제를 보겠습니다.

위 예제는 이가을 학생의 총점을 구하는 문제입니다.

눈으로 보면 A3셀을 기준으로 아래로 5칸, 오른쪽으로 6칸을 가면 원하는 자료가 있죠.

위 그림을 보시면 D18셀에 들어있는 공식을 이해할 수 있겠죠.

D18셀에 들어있는 수식은 다음과 같습니다.

OFFSET($A$3,5,6)

그런데 처음에 배운 문법에서 두 가지가 빠졌죠.

바로 포함할 행수와 열수가 빠졌습니다.

위와 같이 한 셀만 가지고 값을 가져올 때는 포함할 행수와 열수가 각각 1, 1이 됩니다.

이럴 경우에는 생략이 가능합니다.

위의 예제를 보시면 match 함수와 같이 사용해서 5행 6열을 이동하는 것은 간단하게 이해되죠.

OFFSET($A$3,MATCH("이가을",$A$4:$A$12,0),MATCH("총점",$B$3:$I$3,0))

A열에서 이가을 이름을 찾고 3행에서 총점을 찾는 방법이죠.

문자열을 직접 입력할 때는 겹따옴표("")로 싸야되는 것 꼭 기억하세요.

이제 제대로 된 offset 함수를 알아보도록 하겠습니다.

위 그림은 이가을, 박겨울, 장여름 학생의 수학과 사회 점수를 모두 더한 값을 구하는 문제입니다.

A3 셀을 기준으로 하면 아래로 5칸, 오른쪽으로 2칸 가면 이가을 학생의 수학 점수가 나옵니다.

그 지점에서 아래로 3칸 오른쪽으로 2칸에 있는 모든 자료를 더하면 되죠.

그래서 D18셀에 들어있는 수식은 다음과 같습니다.

SUM(OFFSET($A$3,5,2,3,2))

이해가 되셨습니까?

offset 함수를 이용하여 범위를 이름으로 정의하여 

자료의 추가 삭제가 되어도 자동으로 범위를 잡아주는 방법은 다음 시간에 배우겠습니다.

아래의 파일은 유정파란 분이 만든 메크로인데, 참고하시면 되겠습니다. ^^

offset이해하기.xlsx