엑셀 OFFSET 함수 사용법 및 실전예제 총정리 :: 참조 함수

시작지점으로부터 지정한 만큼 이동한 곳의 값을 반환하는 OFFSET 함수의 사용법과 주의사항을 알아봅니다.

홈페이지 » 엑셀 OFFSET 함수 사용법 및 실전예제 :: 참조 함수

엑셀 OFFSET 함수 사용법 및 공식 총정리

엑셀 OFFSET 함수 목차 바로가기
함수 요약

엑셀 OFFSET 함수는 시작점에서 지정한 거리만큼 이동한 위치의 셀 또는 범위를 반환하는 함수입니다.

함수 구문
=OFFSET (시작지점, 행이동, 열이동, [높이], [너비])
인수 알아보기
인수 설명
시작지점 OFFSET 함수로 참조 할 시작지점입니다.
행이동 시작셀 기준, 아래로 몇 칸을 이동할지 결정합니다. (음수로 입력할 경우 위로 이동합니다.)
열이동 시작셀 기준, 오른쪽으로 몇 칸을 이동할지 결정합니다. (음수로 입력할 경우 좌측으로 이동합니다.)
높이
[선택인수]
행/열로 이동한 위치에서, 출력할 범위를 아래로 확장합니다. (음수일 경우 위쪽으로 확장합니다.)
너비
[선택인수]
행/열로 이동한 위치에서, 출력할 범위를 오른쪽으로 확장합니다. (음수일 경우 왼쪽으로 확장합니다.)
OFFSET 함수 상세설명

엑셀 OFFSET 함수는 시작 지점에서 입력된 수 만큼 행과 열로 이동한 뒤, 높이/너비만큼 확장된 범위를 반환하는 함수입니다.

OFFSET 함수는 VBA나 매크로 없이 엑셀 자동화를 구현하려면 반드시 알아야 할 엑셀 필수 함수입니다. 보통 단독으로 사용되는 VLOOKUP 함수나 SUMIF 함수와 달리, OFFSET 함수는 다른 함수와 함께 사용됩니다.

특히, 매일 누적되는 데이터를 자동으로 인식할 수 있는 '동적범위'를 활용하면 다양한 엑셀 서식을 손쉽게 자동화 할 수 있습니다. 동적범위에 대한 자세한 설명은 아래 영상강이를 참고하세요.

OFFSET 함수 사용법 간단예제
  1. A1셀에서 아래로 3칸 이동한 위치의 셀 참조하기
    =OFFSET(A1,3)
    'A1셀에서 아래로 3칸 이동한 위치(=A4셀)를 참조합니다.
  2. A1셀에서 아래로 3칸 이동한 뒤, 아래로 1칸, 오른쪽으로 3칸 확장한 범위를 참조하기
    =OFFSET(A1,3,,1,3)
    'A1셀에서 아래로 3칸 이동한 뒤(=A4), 아래로 1칸, 오른쪽으로 3칸 확장한 범위(A4:C5)를 참조합니다.
호환성
운영체제 호환성
Windows 버전 모든 엑셀 버전에서 사용 가능합니다.
Mac 버전 모든 엑셀 버전에서 사용 가능합니다.

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀함수] 엑셀 OFFSET 함수 사용법
    예제파일

관련 기초함수

그 외 참고사항

  • 만약 OFFSET 함수를 통한 결과값이 시트를 벗어날 경우 (예: A1셀 가장자리 위), #REF 오류를 반환합니다.
  • 높이나 너비를 생략할 경우, OFFSET 함수는 셀 1칸을 참조합니다.
  • OFFSET 함수는 셀을 실제로 이동하거나 변경하지 않습니다. 행/열로 이동하거나 높이/너비만큼 확장된 범위의 참조만 구합니다.
  • OFFSET 함수를 통한 결과값은 SUM 함수 COUNT 함수와 같은 다른 함수의 참조인수로 사용할 수 있습니다.
    SUM(OFFSET(A1,2,1,1,3))
    'A1에서 아래로 2칸 우측으로 1칸 이동한 셀인 B3에서 높이1 x 넓이3 인 범위의 합계를 구합니다.
    = COUNT(OFFSET(A1,,,10000,0))
    'A1로부터  높이가 10000인 범위, A1:A10000 중 숫자가 입력된 셀의 개수를 출력합니다.

자주묻는질문

1. 수식을 잘 입력했는데 #VALUE 오류를 출력합니다.

OFFSET 함수의 결과값으로 #VALUE 오류가 출력될 수 있습니다. OFFSET의 첫번째 인수인 시작위치는 '기준 셀'로 하나의 셀이 입력되어야 합니다. 만약 범위로(예: A1:A3) 첫번째 인수를 입력할 경우 #VALUE 오류를 출력합니다.

=OFFSET(A1:A3,2,1)
'첫번째 인수로 A1:A3 (범위)를 입력하였으므로 #VALUE 오류를 출력합니다.
2. 높이와 넓이를 입력했는데 범위로 출력이 안 됩니다.

OFFSET 함수로 [높이]와 [넓이] 인수를 사용할 경우, 함수를 넓은 범위에 배열 수식으로 입력해야 합니다. (365 버전에서는 OFFSET 함수의 결과로 범위가 올바르게 반환됩니다.)

=OFFSET(A1,1,0,3,0)
'범위를 넓게 선택한 뒤, 아래 수식을 입력한 후 CTRL + SHIFT + ENTER로 배열수식을 입력합니다.
'365 버전 사용자는 범위를 선택하지 않고, 일반 수식과 동일하게 셀 하나를 선택한 뒤 함수를 입력하면 범위가 배열로 반환됩니다.

관련 링크 : MS 홈페이지 OFFSET 함수 상세설명

5 2 투표
게시글평점
guest
5 댓글
Inline Feedbacks
모든 댓글 보기
cjau****
cjau****
2021년 4월 24일 5:42 오후
게시글평점 :
     

죄송하지만 예제 파일이 잘 이해가 안됩니다
offset(시작지점,행이동,열이동,[높이],[너비])가 평균점수에서 어떻게 나오는건지 잘 모르겠습니다
부연설명 부탁드려도 될까요?

cjau****
cjau****
2021년 5월 1일 11:53 오전
답글 남기기  오빠두엑셀

감사합니다.

제로다
제로다
2021년 10월 5일 3:59 오후
게시글평점 :
     

에제에서 이해가 되지 않는 부분이 있습니다. OFFSET 구문이 아래와 같은데 =OFFSET (시작지점, 행이동, 열이동, [높이], [너비]) 예제에서는 =OFFSET(B7,H7,1,1,3)) 참조가 B7, H7 이렇게 두개가 걸려 있어서 어느 부분이 시작지점인지, 그리고 셀 두개가… 더보기 »

5
0
여러분의 생각을 댓글로 남겨주세요.x