엑셀 OFFSET 범위 - egsel OFFSET beom-wi

엑셀에서 OFFSET 함수는 이름만 봐도 왠지 어려워 보이는 함수입니다. OFFSET이라는 단어가 주는 모호함 때문일 것입니다. OFFSET을 영어 사전에서 찾아보면 '상쇄하다', '만회하다', '오프셋인쇄'...? 무슨 뜻이지??? 볼수록 더 미궁에 빠지는 단어입니다.

컴퓨터와 관련된 분야에서는 '어떤 대상으로부터 벗어난 정도, 떨어진 정도'라는 뜻으로 사용됩니다. OFFSET 함수는 어떤 셀로부터 행과 열을 이동한 후 참조를 구해주는데 OFFSET의 뜻을 알고 나면 함수의 사용법이 조금은 쉽게 이해될 것 같습니다.

구문(Syntax)

OFFSET

OFFSET(reference, rows, cols, [height], [width])

셀에서 지정된 수 만큼 행과 열을 이동한 후의 참조를 구해준다.

인수 :

• reference : 행과 열을 이동하기 위한 기준점(셀 위치)
• rows : 행방향으로 이동할 거리, 양수는 아래로 이동, 음수는 위로 이동
• cols : 열방향으로 이동할 거리, 양수는 오른쪽으로 이동, 음수는 왼쪽으로 이동
• height : 반환되는 참조영역의 높이(생략시 기본값이 1로 지정됨)
• width : 반환되는 참조영역의 너비(생략시 기본값이 1로 지정됨)

사용 예

1) 행과 열 이동 후 값 가져오기

OFFSET 함수로 [B3] 셀에서 이동하여 이동한 위치에 있는 셀의 값을 가져오려면

[B9] 셀에 다음 수식을 입력합니다.

=OFFSET(B3,2,1)

위 그림과 같이 OFFSET 함수는 [B3] 셀로부터

• 아래로 2칸
• 오른쪽으로 1칸

이동하여 [C5] 셀에 있는 값 8을 가져옵니다.

2) 행과 열 이동 후 범위 가져오기

이번에는 셀로 이동 후 범위를 지정하여 출력해보겠습니다.

[B19] 셀에 다음 수식을 입력합니다.

=OFFSET(B13,2,1,3,2)

위와 같이 OFFSET 함수는 [B13] 셀로부터

• 아래로 2칸
• 오른쪽으로 1칸

이동하여 [C15:D17] 범위를 가져와서 [B19:C21] 범위에 출력해 줍니다. 수식에서 4, 5번째 인수인 높이와 너비를 지정하면 범위를 가져올 수 있습니다.

※ 위 수식은 결과를 동적배열로 가져오므로 Microsoft 365, 엑셀 2021 이상 버전 사용자가 아니라면 다음과 같이 배열수식으로 입력해야 합니다.

[B19:C21] 범위선택 → 수식 입력(=OFFSET(B13,2,1,3,2)) → [Ctrl+Shift+Enter]

3) 행과 열 이동 후 범위의 값 더하기

이번에는 범위를 가져와서 SUM 함수로 범위의 값을 더해 보겠습니다.

[B30] 셀에 다음 수식을 입력합니다.

=SUM(OFFSET(B24,2,1,3,2))

아래와 같이 OFFSET 함수는 [B24] 셀로부터

• 아래로 2칸
• 오른쪽으로 1칸

이동하여 [C26:D28] 범위를 가져와서 SUM함수로 값을 더해 줍니다.

4) 행과 열 이동하지 않고 범위의 값 더하기

이번에는 행과 열을 이동하지 않고 SUM 함수로 범위의 값을 더해 보겠습니다.

[B41] 셀에 다음 수식을 입력합니다.

=SUM(OFFSET(B35,0,0,3,2))

아래와 같이 OFFSET 함수는 [B35] 셀에서 이동하지 않고(2번째, 3번째 인수가 0이므로 이동하지 않음)

[B35:C37] 범위를 가져와서 SUM 함수로 값을 더해 줍니다.

관련 글