파트너스 활동의 일환으로 이에 따른 수수료를 제공 받습니다.
이번시간에는 OFFSET 함수에 대하여 알아 보겠습니다. 단일적으로 사용하는 함수는 아니지만 다른 함수와 조합하여 사용 할 경우 쓰임새가 있는 함수 입니다. OFFSET 이라는 단어는 사전에 상쇄하다, 벌충 하다 등 이해하기 어려운 단어로 되어 있습니다. 조금 더 의역된 단어로는 “차감계산하다” 가 있습니다. 엑셀 함수로서의 OFFSET 의뜻과 그나마 가장 비슷하게 표현 할 수 있어 보입니다. 컴퓨터 관련 용어 에서는 어떤 대상으로 부터 떨어진정도 라고도 표현 한다고도 합니다. 알고나면 쉬운 OFFSET 함수 알아 보도록 하겠습니다.
엑셀 OFFSET 함수 완벽 이해 행,열 이동 후 범위 추출
엑셀 OFFSET 예제 다운받기
이해를 돕기 위해 먼저 설명을 드리자면 보통 우리가 잘 알고 있는 함수는 범위에 대한 값을 구하거나
셀 값을 수식 + 인수 (셀 혹은 범위)가 입력 되도록 하였습니다. 이번 OFFSET 함수는 어떠한 값을 구할때 보다는
범위를 구하는 함수 로 주로 사용 됩니다. 물론 특정 하나의 셀값을 구하는 것도 가능합니다.
엑셀 OFFSET 함수 인수 설명
최대한 쉽게 설명 해보았지만 너무 복잡합니다. 실제로 사용 해보면 복잡 하지 않으니 바로
예제를 통해 알아보겠습니다.
다음과 같은 데이터에서 OFFSET 함수를 사용 해보겠습니다.
[A6] 셀에 =OFFSET(A1,3,6) 라고 입력 해보겠습니다.
=OFFSET(A1,3,6) 수식 풀이 : 3가지 인수만 입력이 되어있습니다.
- 첫번째 인수 : 기준이 될 셀주소
- 두번째 인수 : 기준으로 정한 셀 부터 아래로 3칸
- 세번째 인수 : 기준으로 정한 셀 부터 오른쪽으로 6칸
- 네번째 인수 : 생략(생략시 1로 지정됨)
- 다섯번째 인수 : 생략(생략시 1로 지정됨)
[A1]셀 에서 아래로 3칸 오른쪽으로 6칸 이동하여 37 이 입력되었습니다.
이렇게 단일 값으로 나오게 하려면 네번째, 다섯번째 인수를 생략하거나 각각 1을 입력하면 됩니다.
범위를 지정하는 네번째, 다섯번째 인수를 생략했기 때문에 1로 지정이 되어 범위가 위아래로 한칸씩
즉 범위가 아닌 하나의 셀만 표현이 되었습니다.
범위를 지정해서 인수를 입력 해보겠습니다.
엑셀 OFFSET 함수 범위 참조
[A7] 셀에 =OFFSET(A1,1,4,2,3) 을 입력해 보겠습니다.
엑셀 OFFSET 함수 응용 다른 함수와 함께 사용
합계 함수 SUM 활용
간단하게 수식앞에 SUM을 입력해준다면 범위 안에 수를 다합산 하여 나타낼 수 있습니다.
[A10] 셀에 =SUM(OFFSET(A1,1,4,2,3)) 입력 합니다.
이번에는 SUM 이 한번에 계산되어 범위가 아닌 단일 값이 나왔습니다.
하지만 실전에서는 이렇게 응용 하지 않고, 셀 값을 추적하여 수식을 자동화 하는데 많이 사용 합니다.
엑셀 OFFSET 함수 MATCH 함수와 응용
위와 같은 데이터에서 OFFSET 과 MATCH 함수를 이용하여 검색 기능을 만들 수 있습니다.
수식은 다음과 같습니다.
[B4]셀에 =OFFSET(A7,MATCH(B2,A8:A22,0),MATCH(B3,B7:D7,0),1,1)
수식 정리 해드리겠습니다.
표시 한것 처럼 5가지 인수이며 두번째 인수와 세번째 인수가 수식으로 들어간것을 볼수 있습니다.
- 첫번째 인수 A7 매장명을 기준으로
- 두번째 인수 MATCH 함수 범위중 입력한 값이 몇번째 인지 리턴 해줍니다 예제 에서 크림치킨은 3
- 세번째 인수 MATCH 함수 범위중입력한 값이 몇번째 인지 리턴 해줍니다 예제 에서 매출은 2
- 네번째 다섯 번째 각각 1,1 로 범위가 아닌 특정 셀 지정
- 결론적으로 검색할 값에 따라 계속 수식이 변경 됩니다.
이렇게 엑셀이 변환 해서 계산 했다고 볼 수 있습니다.
검색값에 따라 결과 값이 달라집니다.
인수로 함수가 포함되어 다소 어렵게 느껴질 수 있지만 MATCH 함수를 이해 하고 있다면 누구나 충분히
사용 할 수 있는 함수 입니다. OFFSET 함수를 이용한 응용 버전 으로 돌아오겠습니다.
