엑셀 OFFSET 함수는 시작 지점에서 일정 거리만큼 떨어진 셀이나 범위를 참조하는 함수입니다.
=OFFSET(시작지점, 행이동, 열이동, [높이], [너비])
- 단일 셀 또는 범위를 지정합니다. 만약 범위를 지정할 경우, 왼쪽 상단 첫번째 셀이 기준점이 됩니다.
- 양수는 아래로, 음수는 위로 이동합니다. 0을 입력하면 이동하지 않습니다.
- 양수는 오른쪽으로, 음수는 왼쪽으로 이동합니다. 0을 입력하면 이동하지 않습니다.
- 0을 입력하면 #REF 오류가 반환됩니다.
- 양수는 아래, 음수는 위로 확장된 범위를 반환합니다.
- 음수를 입력하면 왼쪽으로 확장된 범위를 반환합니다.
엑셀 OFFSET 함수는 시작 지점으로부터 지정한 행과 열 거리를 이동한 후, 셀 또는 범위를 반환하는 함수입니다. 단순히 셀을 반환하는 것뿐만 아니라, 데이터가 추가되거나 바뀌었을 때 범위를 동적으로 참조하는 동적 범위를 만들 때 사용할 수 있습니다.
=OFFSET(A1,5,5)/ / A1셀에서 오른쪽으로 5칸, 아래로 5칸을 이동한 F6셀을 참조합니다. =OFFSET(A1,0,0,3,3)/ / A1셀에서 가로로 3칸, 아래로 3칸 확장한 A1:C3 범위를 참조합니다.
OFFSET 함수는 주로 INDEX 함수와 INDIRECT 함수 등 다른 함수와 조합하여 사용되며, 특히 복잡한 범위를 참조해야 하는 데이터 분석이나 보고서를 작성할 때 효과적입니다. 예를 들어, 매일 누적되는 데이터를 관리해야 할 때 동적 범위를 활용하면 다양한 보고서를 손쉽게 자동화 할 수 있습니다. 이 동적범위에 대한 자세한 설명은 아래 영상강의를 참고하세요!👇
=OFFSET(A1,3) / / A1셀에서 3칸 아래로 이동한 A4셀을 참조합니다.
A열에 일별 매출 데이터가 입력되어 있을 경우, 다음과 같이 수식을 작성하면 최근 3개 항목의 합계를 구할 수 있습니다.
=SUM(OFFSET(A1, COUNTA(A:A)-3, 0, 3, 1)) / / OFFSET 함수는 A1셀에서 "A열 데이터 개수-3" 만큼 아래로 이동한 후, 3칸 확장된 범위를 반환합니다. / / 이후 SUM 함수로 해당 범위의 합계를 구합니다.
만약 1월부터 12월까지 시트가 구분되어 있고, 각 시트의 A1:A10 범위의 합계를 동적으로 구하는 상황을 가정하겠습니다. B1셀에 시트 이름이 작성된 경우, 다음과 같이 수식을 작성합니다.
=SUM(OFFSET(INDIRECT("'"&B1&"'!A1"),0,0,10,1)) / / B1셀에 입력된 시트의 A1:A10 범위의 합계를 구합니다.
엑셀 2019 이전 버전에서는 수식의 배열 결과를 동적으로 출력하는 분산 범위 기능이 제공되지 않습니다. 따라서, 높이와 너비를 지정한 경우 범위를 넓게 선택한 상태에서 함수를 입력하고 Ctrl + Shift + Enter로 수식을 입력합니다.
=OFFSET(A1,0,0,5,5) / / 먼저 범위를 넓게 선택한 후, F2키를 누르거나 수식입력줄을 선택하고 OFFSET 함수를 Ctrl + Shift + Enter로 입력합니다. / / 함수로 반환되는 크기보다 넓은 범위를 선택한 경우, 나머지 비어있는 범위에는 #N/A 가 출력됩니다.
만약 OFFSET 함수의 결과 범위가 시트를 벗어날 경우 #REF! 오류를 반환합니다.
예) =OFFSET(A1,-1,-1) → A1셀의 위쪽으로 범위가 벗어나 #REF! 오류를 반환합니다.
높이나 너비를 생략할 경우, OFFSET 함수는 기본값으로 셀 1칸을 참조합니다.
OFFSET 함수는 셀을 실제로 이동하거나 변경하지 않고, 기준 셀에서 이동한 범위만 반환합니다.
