[Excel]OFFSET()과 COUNTA()를 사용한 동적 범위 설정

개요

수식을 사용하여 동적 범위를 만드는 한 가지 방법은 COUNTA 함수와 함께 OFFSET 함수를 사용하는 것입니다.

동적 범위는 범위 확장이라고도 하며, 데이터 추가 또는 삭제를 수용하기 위해 범위가 자동으로 확장 및 축소됩니다.

참고: OFFSET은 휘발성 함수이므로 워크시트를 변경할 때마다 다시 계산됩니다.

최신 시스템과 더 작은 데이터 세트를 사용하면 문제가 발생하지 않지만 큰 데이터 세트에서 성능이 저하될 수 있습니다. 이 경우 대신 INDEX 함수를 사용하여 동적 명명 범위를 구축하는 것이 좋습니다.

https://power-of-optimism.tistory.com/822

[Excel]Offset() 함수 - 행과 열을 이동하여 값 가져오기

https://power-of-optimism.tistory.com/656

[엑셀(Excel)]셀 개수 세기(COUNT,COUNTA,COUNTBLANK,COUNTIF)

사용되는 공식

 OFFSET(시작 셀, 0, 0, COUNTA(범위), COUNTA범위)

이 공식은 OFFSET 함수를 사용하여 비어 있지 않은 셀 수를 기준으로 높이와 너비를 조정하여 확장 및 축소되는 범위를 생성합니다.

OFFSET(B3,0,0, COUNTA($B$3:$B$100), COUNTA($B$2:$Z$2))

OFFSET의 첫 번째 인수는 원하는 데이터의 첫 번째 셀을 나타내며, 이 경우 셀 B3입니다. 다음 두 인수는 행과 열에 대한 오프셋이며 0으로 제공됩니다.

마지막 두 인수는 높이와 너비를 나타냅니다. 높이와 너비는 COUNTA를 사용하여 즉시 생성되며, 이는 결과 기준을 동적으로 만든다.

높이의 경우 COUNTA 함수를 사용하여 B3:B100 범위에서 비어 있지 않은 값을 카운트합니다. 이 값은 데이터에 공백 값이 없고 B100을 초과하는 값이 없다고 가정합니다. COUNTA는 6으로 반환합니다.

너비의 경우 COUNTA 함수를 사용하여 B2:Z2 범위에서 비어 있지 않은 값을 카운트합니다. 헤더 셀이 없고 Z2를 초과하는 헤더가 없다고 가정합니다. COUNTA는 3을 반환합니다.

이때 공식은 다음과 같습니다.

OFFSET(B3,0,0,6,3)

이 정보를 사용하여 OFFSET은 B3:D8에 대한 참조를 반환합니다.

이것은 6 행 높이 x 3 열 너비의 범위에 해당합니다.

참고: 높이와 너비에 사용되는 범위는 워크시트 레이아웃과 일치하도록 조정해야 합니다.

https://power-of-optimism.tistory.com/665

[엑셀(Excel)]상대참조, 절대 참조 $ (수식 셀 고정), 혼합 참조 사용법

전체 열/행 기준 동적 범위 지정

다음과 같이 높이 및 너비에 전체 열 및 행 참조를 사용할 수도 있습니다.

 OFFSET($B$3,0,0, COUNTA($B:$B)-1, COUNTA($2:$2))

B2의 헤더 값을 고려하여 높이가 -1로 조정됩니다.

이 접근법의 장점은 COUNTA 내부의 범위가 간단하다는 것이다.

단점은 전체 열과 행의 크기가 크다는 것입니다. 

예제) 동적 범위를 이용한 자동 계산

총액의 합을 SUM으로 계산하니 20,000이 나왔습니다.

이 값을 SUM과 OFFSET, COUNTA를 사용하여 추가 입력하면 자동으로 변경되게 하겠습니다.

감귤과 고구마를 입력하면 합계가 자동으로 변경됩니다.

=SUM(OFFSET(D3,0,0, COUNTA(D3:D100))) 에서 D100까지만 COUNTA를 사용하였음으로 

만일 D100을 넘어간다면 데이터만큼 변경해 주어야 합니다.

D3:D1000 또는 D3:D5000 이렇게 변경해야 정상적으로 계산이 됩니다.

 

즐거운 엑셀 시간 되시기 바랍니다.