[엑셀] OFFSET 함수의 기초 사용 방법

 엑셀을 사용할 때 OFFSET 함수는 그 자체로는 사용 빈도가 그렇게 높지 않지만 다른 함수와 결합해서 사용될 때 활용성이 매우 높은 함수입니다. 특히 매크로를 만들 때는 아주 빈번하게 사용되기 때문에 워크시트에서 어떻게 사용되는지 기본 원리만 이해한다면 다양하게 사용할 수 있습니다.

 

 OFFSET 함수는 기본적으로 특정 위치를 기준으로 하여 그 위치로부터 지정한 행과 열만큼 떨어진 위치를 참조하는 함수입니다. OFFSET 함수의 구조는 아래와 같습니다.

 

=OFFSET(Reference, Rows, Cols, (Height), (Width))

 

 첫 번째 인수 'Reference'는 기본 참조 영역으로서 기준이 되는 셀입니다.

 

 두 번째 인수 'Rows'는 첫 번째 인수로부터 몇 행만큼 떨어진 위치를 참조할 것인지 정하는 인수입니다.

 세 번째 인수 'Cols'는 첫 번째 인수로부터 몇 열만큼 떨어진 위치를 참조할 것인지 정하는 인수입니다.

 

 네 번째 인수 'Height' 참조하려는 영역의 행 방향 높이를 정하는 인수로 생략 가능합니다.

 

 다섯 번째 인수 '

Width

' 참조하려는 영역의 옆 방향 넓이를 정하는 인수로 생략 가능합니다.

 

 

 이런 데이터가 있을 때 OFFSET 함수의 인수의 입력에 따른 결과값들을 몇 가지 구해보겠습니다.

 

 

 J4셀에 '=OFFSET(B4,3,2)'을 입력하자 D7셀의 값인 '53'이 나타납니다. 여기서 첫 번째 인수 'B4'는 참조할 기준이 되는 셀로 출발점이라고 생각하시면 됩니다. 두 번째 인수 '3'은 첫 번째 인수 B4셀에서 행 방향으로 3칸 이동하라는 뜻입니다. 세 번째 인수 '2'는 열 방향으로 2칸 이동하라는 뜻으로 최종적으로 D7셀이 참조됩니다.

 

 

 J5셀도 같습니다. C5셀에서 행 방향으로 4칸, 열 방향으로 4칸 이동한 G9셀의 값을 참조합니다.

 

 OFFSET 함수의 두 번째 인수와 세 번째 인수는 0이나 음수도 입력이 가능합니다. 예시를 보겠습니다.

 

 

 J6셀에서는 두 번째 인수를 '2'로 입력하고 세 번째 인수를 '0'으로 입력했습니다. 이렇게 세 번째 인수를 0으로 입력하면 열 방향으로는 움직이지 않고 행방향으로만 두 번째 인수만큼 이동한 셀을 참조합니다.

 

 J7셀의 경우 세 번째 인수를 '4'로 입력하고 두 번째 인수를 '0'으로 입력했습니다. 이렇게 두 번째 인수를 0으로 입력하면 행 방향으로는 움직이지 않고 열 방향으로만 세 번째 인수만큼 이동한 셀을 참조합니다.

 

 

 J8셀은 두 번째 인수와 세 번째 인수를 모두 음수로 입력했습니다. 이렇게 음수로 입력하면 역방향으로 이동하게 됩니다. E8셀에서 위로 3칸, 왼쪽으로 2칸 이동한 셀을 참조합니다.

 

 그렇다면 OFFSET 함수의 네 번째 인수와 다섯 번째 인수를 언제 사용할까요? 만약 참조할 셀이 1개라면 위와 같이 세 번째 인수까지만 입력하고 네 번째 인수와 다섯 번째 인수를 입력할 필요가 없습니다. 하지만 참조할 셀이 일정한 범위라면은 네 번째 인수와 다섯 번째 인수를 입력하는데요.

 

 즉 위와 같이 첫 번째 인수, 두 번째 인수, 세 번째 인수로 참조된 셀을 기준으로 하여 네 번째 인수의 행 높이만큼, 다섯 번째 인수의 열 넓이만큼 참조합니다.

 

 

 J9셀에 '=OFFSET(D5,3,2)'을 입력했습니다. 여기까지는 똑같습니다. D5셀로부터 행방향 3칸, 열 방향 2칸 떨어진 F8셀을 참조합니다. 여기에서 네 번째 인수와 다섯 번째 인수를 입력해 보겠습니다.

 

 

 네 번째 인수와 다섯 번째 인수를 입력하니 '#VALUE!' 오류가 발생하였습니다. OFFSET 함수에서 네 번째 인수와 다섯 번째 인수를 입력하면은 하나의 셀이 아닌 특정 범위를 참조하게 되는데 이런 범위를 하나의 셀에 나타낼 수 없기 때문인데요. 그러면 J9셀에 OFFSET 함수로 참조한 범위를 SUM 함수로 합계를 구해보겠습니다.

 

 

 J9셀의 수식을 '=SUM(OFFSET(D5,3,2,2,2))'으로 수정하였더니 '176'의 값이 구해집니다. 이는 F8:G9셀의 합계인데요.

 

 즉 OFFSET 함수의 네 번째 인수와 다섯 번째 인수는 두 번째 인수와 세 번째 인수를 통해 이동한 셀에서 각각 행방향과 열 방향으로 어떤 크기의 범위를 참조할지 정하는 인수입니다. 

 

 첫 번째 인수 D5에서 두 번째 인수 '3'만큼 행방향으로 이동했고, 세 번째 인수 '2'만큼 열 방향으로 이동한 셀이 F8셀입니다. 이 F8셀을 기준으로 하여 네 번째 인수인 '2'만큼의 높이를 가지고 다섯 번째 인수 '2'만큼의 넓이를 가지는 범위를 참조하는 것입니다.

 

 그래서 결국 2 x 2의 범위를 참조하게 되었고 이 범위에 속한 셀들의 합계값인 176이 나타나는 것입니다.

 

 그렇다면 OFFSET 함수를 통해 참조하는 범위를 그대로 화면에 표시하려면 어떻게 해야 할까요? '=OFFSET(D5,3,2,2,2)'이라는 수식을 통해 참조할 수 있는 범위를 그대로 화면에 표시해 보겠습니다.

 

 

 먼저 참조할 범위가 2 x 2의 크기이기 때문에 이와 같은 크기의 셀 범위를 선택합니다.

 

 

 범위가 선택된 상태에서 수식을 입력하고 그냥 Enter 키를 누르는 것이 아니라 'Ctrl + Shift Enter'키를 누릅니다. 이렇게 배열을 반환할 때는 'Ctrl + Shift Enter'키를 눌러야 합니다.

 

 

 참조하고자 했던 F8:G9셀이 나타납니다. 이렇게 각종 연산의 대상으로 배열을 입력하거나 함수의 인수로 배열이 입력되는 등의 배열수식은 중괄호({})로 묶여 나타나게 됩니다.