사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 동적 범위 참조하기

 

 

 

 

 

특정 범위를 참조해 작업할 때, 데이터가 추가되거나 삭제된다면 변경된 범위를 다시 참조하기 위해 참조 위치를 수정해야 한다. 이때 표를 엑셀 표로 변환해 구조적 참조를 사용하는 방법이 있기는 하지만 엑셀 표를 변환할 수 없거나 변환시 하위 버전과의 호환성에 문제가 있다면 동적 범위 참조 수식을 사용한다. OFFSET, COUNT 계열 함수를 사용하여 동적 범위 참조 수식을 작성하는 방법에 대해 알아보자.

 

OFFSET 함수는 시작 셀에서 원하는 행, 열 방향 개수만큼 위치를 이동한 후 원하는 행, 열 방향 개수 만큼의 범위를 참조한다. 함수의 사용법은 다음과 같다.

 

= OFFSET(시작 셀, 행 방향 이동 셀 개수, 열 방향 이동 셀 개수, 행 방향 포함 셀 개수, 열 방향 포함 셀 개수)

 

① 시작 셀 : 참조할 대상 범위의 왼쪽 위 첫 번째 셀 주소이다.

② 행 방향 이동 셀 개수 : 시작 셀부터 참조할 셀까지의 행 방향 셀 개수이다.

③ 열 방향 이동 셀 개수 : 시작 셀부터 참조할 셀까지의 열 방향 셀 개수이다.

④ 행 방향 포함 셀 개수 : 행 방향으로 참조할 셀 개수이다. 생략가능.

⑤ 열 방향 포함 셀 개수 : 열 방향으로 참조할 셀 개수이다. 생략가능.

 

동적 범위를 참조하는 경우 주로 다음과 같이 구성된다.

 

=OFFSET(시작 셀,0,0,COUNTA(행 방향 참조할 열 주소 전체), COUNTA(열방향 참조할 행 주소 전체))

 

범위참조.xlsx

0.01MB

 

위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다.

 

[A3:E12] 범위에 작성된 금전출납 내역을 참고해 [B1]셀과 [E1]셀에 각각 수입과 지출총액을 각각 계산한다. 이때 금전출납부의 데이터가 추가되어도 별도의 수정 없이 추가된 금액이 바로 집계되도록 데이터 범위를 동적으로 참조하는 수식을 작성한다.

 

 

[C4:C12] 범위를 동적으로 참조하는 이름을 정의하기 위해, [수식] - [정의된 이름] - [이름 정의]를 클릭한다.

 

새 이름 대화상자가 나타나면 다음과 같이 설정한 후 [확인] 단추를 클릭한다.

 

참조대상 : = OFFSET($C$4,0,0,COUNTA($A$4:$A$1000))

 

위의 수식은 [C4] 셀에서부터 행 방향으로 COUNTA($A$4:$A$1000))의 개수만큼 세를 포함하는 범위를 참조한다. COUNTA함수 값이 입력된 셀의 개수를 세어 주는 함수로, [A4:A12] 범위의 값이 입력되어 있으므로 9라는 값이 반환된다. 그러므로 이 수식은 [C4]셀을 포함해 아래쪽으로 9개의 셀을 참조하라는 의미가 되어 [C4:C12]범위를 참조하게 된다. 참조할 범위를 COUNTA 함수를 사용해 참조하므로 데이터가 추가되거나 삭제될 때마다 참조 대상범위가 조금씩 달라지는 동적 범위를 참조하게 된다. 참조로 이번 수식에서 [C4:C1000]범위를 참조하지 않고 [A4:A1000] 범위를 참조한 것은 [C]열에는 갑을 입력하지 않을 수도 있지만 [A]열에는 항상 값을 입력해야 하기 때문이다. 값이 입력된 데이터 범위를 십게 알 수 있다.

 

 

새 이름 대화상자가 나타나면 [이름(N):]을 '지출'이라고 입력하고 참조대상을 다음과 같이 설정한 후 [확인] 단추를 클릭한다.

 

참조대상 : = OFFSET($D$4,0,0,COUNTA($A$4:$A$1000))

 

 

수입총액과 지출총액을 다음과 같이 수식을 입력한다.

 

[B1] : =SUM(수입)

 

[E1] : =SUM(지출)