엑셀 Offset 함수의 중요성
엑셀로 업무 자동화할 때 가장 중요한 게 함수를 적재적소에 활용한 수식을 만드는 것입니다. 이러한 점에서 동적참조범위를 반환해주는 엑셀 Offset 함수는 배워두면 유용하게 쓸 수 있습니다.
예를 들어, 일정 데이터 범위를 참조하는 Sumif 와 같은 함수 안에 offset 함수를 넣어서 참조되는 범위가 자동으로 업데이트되는 수식 등을 만들 수 있죠. 이런 수식을 만들 수 있다면, 딱 한 번만 작업해 놓으면 데이터가 입력되는 대로 자동으로/ 실시간으로 결과값을 확인할 수 있습니다. 그러면 같은 업무를 해도 효율성이 엄청 높아지는 것이죠.
엑셀 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 함수가 결과값으로 참조 범위를 돌려주기에 가능한 조합입니다.
OFFSET(reference, rows, cols, [height], [width]) 함수는 옆에 나열한 총 4 개의 인수를 채워야 하는데,
Reference 에서 시작할 셀을 지정하고,
Row, cols 부분에 숫자를 넣어주시면, 시작할 셀 – Reference – 에서 ± 행, ± 열 이동한 셀의 값을 돌려주게 됩니다.
다만 우리는 Offset 함수를 통해 범위를 돌려 받아야 하기 때문에.. rows, cols 인수 부분은 공백으로 두고…[height] 부분만 “COUNTA(B:B)-1” 로 채워주었습니다. 이 Counta 함수는 결과값으로 “B열에서 공백이 아닌 셀의 갯수 – 1” 을 반환하겠죠. – 1은 B2 에 있는 “제품” 때문에 수식에 넣은 것입니다. 결국 이 함수는 8을 돌려주며, Sumif 함수의 range 인수는 시작할 셀 -Reference- B3를 부터 아래로 7개의 행을 포함한 범위를 갖게 됩니다.
다시 정리하자면, OFFSET(B3,,,COUNTA(B:B)-1)은 B3 셀에서 아래로 7행/ 옆으로는 1행 의 범위를 참고하라는 명령을 내리는 셈입니다. 때문에 테이블에 새로운 값이 하나 추가 되면, Counta(B:B)-1 결과값이 업데이트되면서 Offset 함수로 반환되는 참조 범위가 한 행 늘어나는 원리입니다.
아래에 Offset 함수로 동적참조범위를 만든 예제 파일 첨부드리니, 한 번 다운로드 받아서 직접 연습해보세요!
마무리
한 번 시간이 걸리시더라도 위에 수식을 찬찬히 뜯어보시길 추천드립니다. 따지고 보면 sumif, offset, counta 이렇게 세 함수로 구성되어 있을 뿐입니다. 만약 이런 작업이 자동 계산이 되지 않았다면… 제품 입출고 대장에 새로운 값이 입력될 때마 일일이 수식을 수정해줘야 하니 굉장히 번거로운 일이 될 겁니다.
다들 Offset 함수를 익혀 업무 처리 속도를 높이고 다들 정.시.퇴.근 하시기 바랍니다!
다른 쉽고 유용한 엑셀 팁들을 보고 싶으다면 아래 링크 클릭해주세요!