Offset 함수를 이용한 동적 참조 (데이터 입력에 따라 참조 범위 자동 업데이트)표

 

자동계산표_자동테두리표.xlsx

0.02MB

엑셀을 사용하면 가장 많이 하는 작업이 수식 활용한 계산인데,  데이터가 업데이트 되면 보통 입력된 수식을 일일이 수정하는데… 번거롭기만 하다.  엑셀을 사용하는 이유는 반복 작업을 자동화해 내 일신이 편하고자 함인데도 말이다.

일정 데이터 범위를 참조하는 계산식에 offset  함수를 활용해 참조 범위를 자동으로 변하게 하면 어떨까? 수식을 한 번만 입력해 놓으면… 데이터가 입력되는 대로 자동으로 계산해 결과값을 도출해 주니 꽤 편하다..

 

첨부 파일 재고 수량에 입력된 수식은 다음과 같다… 일견 복잡해 보이지만 뜯어보면 그렇지 않으니 미리 겁먹지 마시길…

=SUMIF(OFFSET(B3,,,COUNTA(B:B)-1),F3,OFFSET(C3,,,COUNTA(B:B)-1))-SUMIF(OFFSET(B3,,,COUNTA(B:B)-1),F3,OFFSET(D3,,,COUNTA(B:B)-1))

 

그냥 보통  sumif  함수에  range, sum range  자리에  offset 함수를 넣어준 것 뿐이다.

OFFSET(reference, rows, cols, [height], [width]) 함수는 보는 것 같이 4 개의 인수를 채워야 하는데,

Reference 는 시작 셀,

Row, cols 부분에 숫자를 넣어주면, 시작할 셀에서 ± 행, ± 열 이동한 셀의 값을 돌려준다.

다만 우리는 범위를 참조할 것이기 때문에..   rows, cols 부분은 공백으로 두고…

[height] 부분만 COUNTA(B:B)-1 로 채워준 것. 곧, B 열에 값이 입력된 셀 숫자 – 1, 여기서는8이된다.

즉, OFFSET(B3,,,COUNTA(B:B)-1)은 B3 셀에서 아래로 8행/ 옆으로는 1행 의 범위를 참고하라는 명령인 셈.

때문에…  표가 채워지는 대로 참조 범위가 업데이트 되는 것이다.

 

주의할 점이 있다면, 현재 표의 구매수량, 사용 수량에 공백이 있기 때문에  sum range 부분은 reference (시작점) 을 각각 C3, D3 로 놓았지만, [height] 는 COUNTA(B:B)-1 를 그대로 써야 한다는 점?

 

표 테두리도 자동으로 그려지게 해 놓았는데... 이 설명은 다른 포스트에 올려야 겠다...