엑셀 배우기, OFFSET 함수로 특정일부터의 매출액 합산하기!

지난 엑셀 참조 함수 관련 포스팅, 엑셀배우기, 참조함수 VLOOKUP/INDEX/MATCH 함수 정리. 에서 가장 대표적인 함수인 VLOOKUP 함수와 INDEX 및 MATCH 함수를 정리했습니다. 또한 포스팅 마지막에 특정 셀이 아닌 범위를 참조할 수 있는 INDIRECT 함수와 OFFSET 함수에 대한 관련 포스팅을 함께 링크했었는데, 오늘은 이중 OFFSET 함수의 실무 예제를 하나 더 다뤄볼까 합니다. 먼저 이전 OFFEST 함수 관련 포스팅은 아래의 링크를 참고하시면 되겠습니다. 

월별 판매 수량 또는 품목별 매출액을 구하는 것은 하나의 조건을 만족하는 셀의 합계를 구할 수 있는 SUMIF 함수를 활용하면 됩니다. 또한 월별/품목별, 이 두가지 조건을 모두 만족하는 판매 수량 또는 매출액을 구하려면, 다중 조건을 만족하는 셀의 합계를 구할 수 있는 SUMIFS 함수 또는 SUMPRODUCT 함수를 활용하면 됩니다.

엑셀배우기, SUM/SUMIF/SUMIFS/SUMPRODUCT 함수 정리.

그런데, 특정일 기준으로 이후의 모든 판매량 또는 매출액을 구하려면 어떻게 함수를 구성해야 할까요? 바로 OFFEST 함수를 활용하면 됩니다! 위에서 말씀드린대로, OFFSET 함수는 특정 범위를 참조할 수 있습니다. 즉, VLOOKUP 함수나 INDEX 함수와  MATCH 함수의 조합처럼 특정 셀 값을 반환하는 것이 아닌, 특정 범위를 반환한다는 의미입니다. 따라서 이 OFFEST 함수로 합계를 구하고 싶은 범위를 반환하고, 이 범위를 SUM 함수로 합계를 구해주면, 특정일 기준으로 이후의 모든 판매량 또는 매출액을 구할 수 있는 것입니다.

아래에서 I2 셀에 특정일이 입력되면, J2 셀에서 해당일 이후의 매출액이 자동으로 합산되고 있습니다. 9월 13일 이후의 판매 금액이 각각, 10만원, 10만원, 5만원으로 총액 25만원이 됩니다. 그리고 이 값을 구하기 위해 J2 셀에 아래와 같이 함수 수식을 구성하였습니다.

중첩된 함수 하나하나 살펴보겠습니다. 먼저 MATCH 함수의 수식부터 풀어보겠습니다. 'MATCH(I3, $C:$C, 0)' 수식은 C열 전체에서 I3 셀의 값의 행번호를 찾습니다. 비어있는 1행과 머리글인 2행을 포함한 C열 전체 범위에서 I3 셀의 값, '9월 13일'을 찾기 때문에, 행 번호 14를 반환합니다.

 다음으로 OFFSET 함수입니다. 일단 OFSET 함수의 구조는 '=OFFSET(기준셀, 행 이동 수, 열 이동 수,[길이], [너비])' 입니다. 특정 셀을 참조하기 위해서는 옵션 값인 [길이]와 [너비] 값을 생략하면 됩니다만, 우리는 특정 범위, 즉 G14:G16를 참조해야 하기 때문에, [길이] 옵션이 필요합니다.

앞서의 MATCH 함수 수식의 값이 14이기 때문에 OFFSET 함수 수식은 'OFFSET(C1, 14-1, 4, COUNTA($C:$C)-14+2)'와 같이 정리됩니다. 기준 셀이 C1입니다. 기준 셀에서 최초의 '9월 13일' 값인 C14 셀까지이동 하려면, 13번의 행 이동이 필요하고, 그래서 앞서의 MATCH 함수 수식에서 1을 빼준 것입니다. 또한 최종적으로 참조할 범위가 G열이니 이제 C14 셀에서 4번의 열 이동이 필요하고, 이제 OFFEST 함수가 참조하는 셀은 G14 셀입니다.

마지막으로 G14셀부터 G16 셀까지의 범위를 참조하게 만들어야하기 때문에, [길이] 옵션을 넣어주어야합니다. 여기에서 약간의 고민이 필요한데, 이 [길이] 값이 자동으로 계산되도록 만들기 위해서는 COUNTA 함수가 필요합니다.

COUNTA 함수의 구조는  '=COUNTA(범위)'로, 지정한 범위에서 비어 있지 않은 셀의 개수를 셉니다. 따라서 'COUNTA($C:$C)' 수식의 값은 머리글 셀인 C2셀의 포함해서 15가 됩니다. 이 값에서 앞서의 'MATCH(I3, $C:$C, 0)' 수식의 값인 14를 빼면 1이 되고, 여기에 다시 2를 더해주면 3이 되어, 최종적으로 OFFSET 함수 수식은 'OFFSET(C1, 13, 4, 3)'이 되어, G14:G16의 범위를 참조하게 되는 것입니다. 그리고 마지막으로 이 범위를 다시 SUM 함수로 합계를 구한 것입니다.

참고로, 위에서 MATCH 함수 값에서 1을 빼주고, COUNTA 함수 값과 MATCH 함수 값의 차이에 다시 2를 더해준 것은 스스로 고민해 보시기 바랍니다. 단, OFFEST 함수의 구조가 여타 함수에 비해 다소 복잡한 측면이 있으므로, 혹시 위의 설명이 부족한 부분이 있었다면 아래에 댓글 남겨 주시기 바랍니다!