OFFSET MATCH 함수 - OFFSET MATCH hamsu

반응형

오늘은 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 함수는 이름만 봐도 왠지 어려워 보이는 함수입니다. OFFSET이라는 단어가 주는 모호함 때문일 것입니다. OFFSET을 영어 사전에서 찾아보면 '상쇄하다', '만회하다', '오프셋인쇄'...? 무슨 뜻이지??? 볼수록 더 미궁에 빠지는 단어입니다.

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

구문(Syntax)

OFFSET

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

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

인수 :

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

실습용 엑셀파일 다운로드 : 엑셀-OFFSET-함수-행열이동후-참조구하기.xlsx

사용 예

1) 행과 열 이동 후 값 가져오기

OFFSET 함수로 [B3] 셀에서 이동하여 이동한 위치에 있는 셀의 값을 가져오려면

=OFFSET(B3,2,1)

위 그림과 같이 OFFSET 함수는 [B3] 셀로부터

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

이동하여 [C5] 셀에 있는 값 8을 가져옵니다.

2) 행과 열 이동 후 범위 가져오기

이번에는 셀로 이동 후 범위를 지정하여 출력해보겠습니다.

=OFFSET(B13,2,1,3,2)

위와 같이 OFFSET 함수는 [B13] 셀로부터

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

이동하여 [C15:D17] 범위를 가져와서 [B19:C21] 범위에 출력해 줍니다. 수식에서 4, 5번째 인수인 높이와 너비를 지정하면 범위를 가져올 수 있습니다.

※ 위 수식은 결과를 동적배열로 가져오므로 Microsoft 365, 엑셀 2021 이상 버전 사용자가 아니라면 다음과 같이 배열수식으로 입력해야 합니다.

* Microsoft 365, 엑셀 2021 이상 버전에서는 동적 배열 수식을 사용할 수 있습니다. 자세한 내용은 다음 링크를 참고하세요.
[엑셀의 새로운 기능, 동적 배열 수식 ]

* 배열수식에 대해서는 다음 링크를 참고하세요.
[엑셀 배열수식 제대로 이해하기]

3) 행과 열 이동 후 범위의 값 더하기

이번에는 범위를 가져와서 SUM 함수로 범위의 값을 더해 보겠습니다.

=SUM(OFFSET(B24,2,1,3,2))

아래와 같이 OFFSET 함수는 [B24] 셀로부터

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

이동하여 [C26:D28] 범위를 가져와서 SUM함수로 값을 더해 줍니다.

4) 행과 열 이동하지 않고 범위의 값 더하기

이번에는 행과 열을 이동하지 않고 SUM 함수로 범위의 값을 더해 보겠습니다.

=SUM(OFFSET(B35,0,0,3,2))

아래와 같이 OFFSET 함수는 [B35] 셀에서 이동하지 않고(2번째, 3번째 인수가 0이므로 이동하지 않음)

관련 글

• 인쇄
• 전자우편
• Tweet

• 

이것이 좋아요:

좋아하기 가져오는 중...