[엑셀] (INDEX 함수) 품번별 전월 대비 당월 주문량 증가/감소 비율 나타내기

연도별 해당품번 감소 및 증가량 구하기.xls

0.16MB

품번별로 전월 또는 전년도 대비 당월 또는 당해 주문량의 증가/감소 비율표를 만들어 해당 품번에 대한 주문량의 증가량 감소량을 알아보기 위해 INDEX 함수를 이용해 보았어요. 품번이 많을 경우 한눈에 보기 편한것 같아요.

 

저는 60개 품번의 3개년도(2017년~2019년) 주문량를 평균치 내서, 당해년도 (2020년) 현재 시점 주문량과 비교 했을시 감소한 품목과 비율, 증가한 품목과 비율를 볼 수 있도록 할 수 있어요.

 

먼저 각 년도에 해당하는 품번별 주문량을 불러와서 직전 3개년도 평균주문수량을 나타내고, 당해년도인 2020년 현 시점까지의 주문량도 불러와서 증감율(%)로 나타냈어요.

품번이 많은 경우, 어느 품번이 급감했는지 급증했는지 한눈에 알 수 있도록 이제 감소한 품번들과 증가한 품번들만 따로 표로 나타낼 수 있게 INDEX 함수를 사용해 봅니다

 

1. DATA 준비

 

2. 직전대비 감소한 품번들만 볼 수 있도록 새로운 시트 (Q'ty reduction item)를 만들고, 1행~4행까지의 행양식을 그대로 복사해서 붙여옵니다.

 

3. INDEX 함수 사용 (감소품번 <0 조건)

5행부터 감소 또는 증가한 데이터를 불러올 수 있도록, 5행 (B5~H5) 에 아래와 같이 INDEX 함수식을 넣어줍니다.

이렇게 5행을 작성하고 나서 전체행을 드래그 해서 그대로 64행까지 (1~4행 서식양식, 5~64행 자료) 복사해주면 아래와 같이 지난 3년대비 감소한 품번들만 불러와 집니다.

또한, 증가한 품목들은 보여지지 않고 빈칸으로 표시되어지도록 IFERROR를 사용합니다. 

이렇게하여 65행의 합계도 감소한 품번별 합계만 표시되어지도록 합니다.

(ROW($B1)-4는 불러올 자료는 B1에서 4번째 아래행에 표시하겠다는 의미입니다)

 

B5 : =IFERROR(INDEX(DATA!A$5:A$64,1/LARGE(INDEX((DATA!$G$5:$G$64<0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

C5 : =IFERROR(INDEX(DATA!B$5:B$64,1/LARGE(INDEX((DATA!$G$5:$G$64<0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

D5 : =IFERROR(INDEX(DATA!C$5:C$64,1/LARGE(INDEX((DATA!$G$5:$G$64<0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

.

.

.

H5 : =IFERROR(INDEX(DATA!G$5:G$64,1/LARGE(INDEX((DATA!$G$5:$G$64<0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

 

 

 

4. INDEX 함수 사용 (증가품번 >=0 조건)

직전3개년도대비 평균주문수량보다 당해년도 주문수량이 증가한 품번들만 볼 수 있도록 시트(Q'ty increase item)를 하나 추가합니다.

감소와 동일하게 5행만 (B5~H5) 식을 입력하여, 드래그 하여 64행까지 내려 복사하고, IFERROR로 증가한 품번이 아닌 행은 빈칸으로 처리하여 합계에 표시되어지지 않게 해줍니다.

 

B5 : =IFERROR(INDEX(DATA!A$5:A$64,1/LARGE(INDEX((DATA!$G$5:$G$64>=0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

C5 : =IFERROR(INDEX(DATA!B$5:B$64,1/LARGE(INDEX((DATA!$G$5:$G$64>=0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

D5 : =IFERROR(INDEX(DATA!C$5:C$64,1/LARGE(INDEX((DATA!$G$5:$G$64>=0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

.

.

.

H5 : =IFERROR(INDEX(DATA!G$5:G$64,1/LARGE(INDEX((DATA!$G$5:$G$64>=0)/ROW(DATA!$A$5:$A$64),),ROW($B1))-4),"")

 

 

이렇게 품번이 많은 경우 해당 범위내에서 직전월(연도) 대비 당월(연도) 증가/감소 품번만 별도로 만들어 한눈에 자료를 보고 싶을때 이용하면 좋은 INDEX 함수 입니다.

 

최종 표의 모습은 이렇게 나옵니다.

첨부파일로 해당 파일 공유하여 놓았으니 상세한 식 참조하시기 바랍니다.