엑셀 offset 함수 이용하여 동적 영역 설정하는 방법

[보명(普明吉永)]

설명

셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환합니다. 반환되는 참조는 단일 셀 또는 셀 범위일 수 있습니다. 반환할 행 및 열 수를 지정할 수 있습니다.

구문

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

OFFSET 함수 구문에는 다음과 같은 인수 (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다.

• reference    필수 요소입니다. 오프셋의 기반으로 삼을 참조입니다. reference는 셀 또는 인접한 셀 범위를 참조해야 합니다. 그렇지 않으면 #VALUE! 오류 값이 반환됩니다.
• rows    필수 요소입니다. 왼쪽 위에 있는 셀이 참조할 위 또는 아래에 있는 행의 수입니다. rows 인수로 5를 사용하면 참조의 왼쪽 위 셀이 참조보다 5행 아래로 지정됩니다. rows는 양수(시작 참조보다 아래) 또는 음수(시작 참조보다 위)일 수 있습니다.
• cols    필수 요소입니다. 결과의 왼쪽 위 셀이 참조할 왼쪽 또는 오른쪽에 있는 열의 수입니다. cols 인수로 5를 사용하면 참조의 왼쪽 위 셀이 참조에서 5열 오른쪽으로 지정됩니다. cols는 양수(시작 참조에서 오른쪽) 또는 음수(시작 참조에서 왼쪽)일 수 있습니다.
• height    선택 요소입니다. 반환되는 참조의 높이(단위: 행 수)입니다. height는 양수여야 합니다.
• width    선택 요소입니다. 반환되는 참조의 너비(단위: 열 수)입니다. width는 양수여야 합니다.

1

2

3

4

A	B
수식	설명(결과)
=OFFSET(C3,2,3,1,1)	셀 F5의 값을 표시합니다(0).
=SUM(OFFSET(C3:E5,-1,0,3,3))	범위 C2:E4의 합계를 계산합니다(0).
=OFFSET(C3:E5,0,-3,3,3)	참조가 워크시트를 벗어나므로 오류가 반환됩니다(#REF!).

데이타의 영역이 자주 변경되는 경우에 많이 사용 합니다.

1. 피벗 테이블의 영역

2. 인쇄 영역 등에서 이름을 정의하고 (이름을 "데이타영역")

3. 이름 데이타영역을 수식으로 정의 합니다. =offset(c3,0,0,counta(c:c)-2, counta(c3:f3))

4. c3; 기준셀

5. 0,0,; 기준셀 기준으로 행과 열의 위치

6. counta(c:c)-2,; c열의 문자를 모두 세어서 2개를 뺀 개수 - 행의 긑 셀 위치를 나타냄

7. counta(c3:f3); c3:f3 3행의 열을 셉니다. - 열의 끝 셀 위치를 나타냄