[엑셀] (SUMPRODUCT 함수) 품번별 출하일의 월별/년도별 범위내 합계표

회사에서 자료 정리를 위해 요즘 엑셀함수 자주 이용하고 있어요

오늘은 sumprodut 함수 활용해서 품번별로 각 년도별 및 월별의 자료들을 하나로 일정범위 지정하여 하나의 표로 정리하는걸 해보았어요

출고할때마다 출고날짜를 기록해 놓고 같은 월에 일자가 다른  출고가 여러번이더라도 SUMPRODUCT 함수로 월별로 자동으로 범위지정해서 수작업으로 일일이 하는 수고를 덜 수 있었어요.

 

최종 Summary Table 모습이고, 해당 각 셀에 들어가는 식이예요.

1. "2018~2020년" "1월"에 출고되어진 Item # "ABC001"를 "18년출고량~20년출고량" 시트에서 가져오는 식이예요

2018년도 G4 : SUMPRODUCT((MONTH('18년출고량'!$B$11:$L$11)='Summary Table'!G$3)*1,'18년출고량'!$B$4:$L$4)
2019년도 G5 : SUMPRODUCT((MONTH('19년출고량'!$B$11:$L$11)='Summary Table'!G$3)*1,'19년출고량'!$B$4:$L$4)

2020년도 G6 : SUMPRODUCT((MONTH('20년출고량'!$B$11:$L$11)='Summary Table'!G$3)*1,'20년출고량'!$B$4:$L$4)

 

그럼, 2월에 출고되어진 자료를 각 해당 년도에서 가져오는 건 다른식은 다 동일하고 해당 월만 변경해주었어요

2. "2018~2020년" "2월"에 선적되어진 Item # "ABC001"를 "18년출고량~20년출고량" 시트에서 가져오는 식이예요

2018년도 H4 : SUMPRODUCT((MONTH('18년출고량'!$B$11:$L$11)='Summary Table'!H$3)*1,'18년출고량'!$B$4:$L$4)
2019년도 H5 : SUMPRODUCT((MONTH('19년출고량'!$B$11:$L$11)='Summary Table'!H$3)*1,'19년출고량'!$B$4:$L$4)

2020년도 H6 : SUMPRODUCT((MONTH('20년출고량'!$B$11:$L$11)='Summary Table'!H$3)*1,'20년출고량'!$B$4:$L$4)

 

출고일인 MONTH의 범위에서 행과 열에 $를 사용, 해당월에서 행부분에 $ 사용, 출고량 범위에서 행과 열에 $를 사용하여, 각 품번별로 해당년도의 1월의 첫칸 함수식을 기재하여 12월까지 드래그 해서 복사하면 자동으로 합계가 산출되어집니다.

 

* 위 표의 월 부분은 각 월의 표기는 숫자만 입력해야 합니다.

  저는 숫자가 월이라는 걸 알게 하기 위해 1월~12월 (G3~R3) 부분에 숫자만 입력하고, 마우스 오른쪽 클릭하여 셀서식 > 표시형식 >일반 > 사용자지정으로 해당월의 영문약자를 사용해서 입력했어요.

 

* 위의 표는 첨부파일로 올려두었으니 참조해서 보시기 바랍니다.

일정범위내 연별.월별.아이템별 출고량 합계.xls

0.10MB