블로그 이미지
주로 인재개발원 등의 사이버학습을 정리, 요약하는 상시학습 블로그입니다. 깨비형
« 2017/10 »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

Archive»

체험 블로그 마케팅 서비스 OLPOST

Category»

Notice»

Statistics Graph

 

'엑셀 offset 함수'에 해당되는 글 1

  1. 2014.12.15 offset 함수 사용법
 

offset 함수 사용법

오피스 활용/엑셀 찾기/참조함수 | 2014.12.15 16:25 | Posted by 깨비형


오늘은 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




저작자 표시 동일 조건 변경 허락
신고