엑셀] 입력에 따라 범위를 변경해서 합계 구하기 - offset, counta함수

엑셀에서 동적 범위를 구해서 합계 계산하기

엑셀에서 계산할 때 입력되는 값에 따라 범위를 정해서 합계를 구하는 방법

아래와 같은 표가 있는 경우, 

2020년의 각 달에 입력되는 값에 따라 2019년의 합계를 구해서 비교하고 싶은 경우에 사용하는 수식이다. 

가령 2020년이 1월~3월까지 값이 입력되면 2019년의 합계도 1월~3월까지만 계산하고 

2020년 5월까지 입력되면 2019년의 합계도 1월~5월까지로 범위를 변경해서 

합계를 구하는 범위가 동적으로 계속 변경되는 수식을 만들어야 한다.

수식은 offset함수를 이용해서 계산 범위를 구하면되고

여기에 counta함수가 이용된다. 

offset은 다음처럼 이동해서 범위를 반환한다.

OFFSET(기준셀, 행방향 이동 수, 열방향 이동 수, 참조 높이, 참조 너비)

아래그림에서 3월까지 합계를 구해야 한다면 수식은 다음과 같다. 

=SUM(offset(C3, 0, 0, 1, 3))

C3에서 시작해서 행방향 이동 0, 열방향 이동 0(그냥 C1에서부터 시작하는 범위이기 때문에)

참조높이 1, 참조 너비 3으로 계산하면 된다

6월까지의 합계를 구해야 한다면??

=SUM(offset(C3, 0, 0, 1, 6))

이렇게 된다. 

그러면 결국 offset함수의 마지막 인수인 참조 너비를 변경해가면서 계산해야 하는데 

여기서는 counta 함수를 사용한다. 

counta함수는 범위 내에서 데이터가 입력된 셀의 갯수를 반환한다. 

 - 엑셀] 갯수 세기 - COUNT, COUNTA, COUNTIF, COUNTIFS

2020년 자료가 3월까지 입력되면 counta함수는 3을 반환하고 6월까지 입력하면 6을 반환한다.

이제 마지막으로 offset과 counta 함수를 조합해서 

=SUM(OFFSET(C3,,,,COUNTA($C$4:$N$4))) 

이렇게 입력해주면 된다

(offset함수에서 이동수 0이나 참조 높이 1인 경우 빈 칸으로 남겨도 된다)