구글 스프레드시트 함수 - 재고표, 생산 스케줄표 만들기 (Query)

생산 및 물류 솔루션을 사용하지 않는 매장이나 회사에서 생산 품목수가 점점 늘어나는 경우, 매번 수기로 제조 순서를 정하기에는 복잡해질 수 있습니다.

 

마감 시, 재고를 조사하고 표에 입력을 하면 내일 생산해야 할 품목이 어떤 것이고, 어떤 순서로 제조를 해야하는지 자동으로 계산되는 시트를 만들어보겠습니다.

 

1. 재고표 만들기 (안전수량 정하기)

 

매장 별로 매일 마감 혹은 오픈 시에 판매할 제품의 재고를 체크 할 것입니다.

대부분은 마감 때 하게 될텐데, 그 갯수를 시트에 입력하므로써 생산 필요여부와 얼마나 필요한지, 또 어떤 순서로 제조가 들어가야 하는지에 대한 결과값이 산출됩니다.

제품명	제조 제품명
	제품명을 직접 입력합니다.
현재 재고 수량	마감 혹은 오픈 시 체크한 사용가능 재고 수량
	체크한 제고 수량을 입력합니다.
안전 재고 수량	판매를 하기 위한 최소 재고 수량 (항상 이 갯수보다는 많이 있어야 함)
	안전한 최소 보유 재고 수량을 설정합니다. 모든 계산은 이 값이 기준입니다.
생산 필요 여부	안전 재고 이하인 경우 제조가 필요하므로 표시
	=if(B4="","",if(B4-C4>-1,"","필요"))
제조 기준량(1팬)	제조 시 1팬에 몇개의 제품이 생산되는지 기준 설정
	1팬 생산 시 몇 개의 제품이 생산되는지 숫자로 입력합니다.
필요 생산량(팬)	필요량과 제조 기준량으로 몇 팬을 제조 해야 하는지 계산
	=if(D4="","",roundup(((B4-C4)*-1)/E4,0))
필요량	실질적으로 안전재고수량보다 얼마나 부족한지를 계산
	=if(D4="","",(B4-C4)*-1)

-함수 설명

1) 생산 필요 여부  =if(B4="","",if(B4-C4>-1,"","필요"))

 

현재 재고수량(B4)이 빈칸인 경우, 이 셀도 빈칸으로 둔다. 

현재 재고수량(B4)이 빈칸이 아닌 경우 다음과 같이 계산한다.

만약 현재 재고수량(B4) 빼기 안전 재고수량(C4)이 -1보다 큰 경우는 결과를 빈칸으로 둔다.

 

*-1로 해야 현재 재고가 안전 재고와 동일한 경우 생상리스트에 넣지 않는다.

안전 재고와 동일한 경우에도 생산리스트에 넣고자 하는 경우, 기준값을 0으로 한다.

 

만약 현재 재고수량(B4) 빼기 안전 재고수량(C4)이 -1보다 작은 경우 "필요" 라는 텍스트를 노출한다.

2) 필요량 =if(D4="","",(B4-C4)*-1)

 

생산필요여부(D4)가 빈칸이면 (생산할 필요가 없으면) 빈칸으로 두고, 빈칸이 아닌 경우 다음과 같이 계산한다.

현재 재고수량(B4) - 안전 재고수량(C4) 을 계산하면 그 값이 마이너스 값이므로 -1을 곱해준다.

 

이 필요량은 필요 생산량과 스케줄표 우선순위에 관여한다.

 

3) 필요 생산량(팬)  =if(D4="","",roundup(((B4-C4)*-1)/E4,0))

 

생산필요여부(D4)가 빈칸이면, 빈칸으로 두고, 빈칸이 아닌 경우 다음과 같이 계산한다.

제조기준량(E4) 나누기 필요량((B4-C4)*-1)을 1자리에서(0) 반올림(Roundup) 한다.

 

반응형

2. 제조 스케줄표 만들기 (우선순위 적용)

 

재고표를 통해 현재 어떤 제품이 얼마나 생산되어야 하는지가 계산되었다면, 제조가 필요한 제품 중 우선순위가 높은 제품부터 생산을 하게끔 자동으로 생산 스케줄을 반영해서 노출해주는 표를 만들어 보겠습니다.

우선 순위	계산된 제품이 몇 번째 순위인지 숫자로 표시 (순서대로 표시 되지만 보기 쉽게 하기 위해 표시)
	=if(J4="","",row(A1))
제품명/필요량	재고표에 계산된 제품명과 필요량 리스트를 우선순위대로 불러옴
	=query(A4:G,"select A,F where D<>'' order by G desc")

-함수 설명

1) 우선수위 =if(J4="","",row(A1))

 

제조 필요 리스트가 없다면(=제품명이 비어있다면) 빈칸으로 두고, 리스트가 작성된 경우 1번부터 순서를 매긴다.

 

2) 제품명/필요량  =query(A4:G,"select A,F where D<>'' order by G desc")

 

쿼리를 활용하여 재고 리스트의 일부분(A4부터 G)을 불러옵니다.

그 중 제품명(A)열과 필요량(F)열을 불러온다. 그 중 D열 값이 빈칸인 제품은 제외하고 가져오되, 필요량이 높은 순으로 정렬한다.

 

3. 응용

 

다양한 방법으로 응용이 가능합니다. 

재고가 안전수량과 동일해도 제조가 되게 수정하거나, 필요량에 따른 우선순위가 아닌 회사에서 정한 우선순위로 제조스케줄이 나오게끔 할 수도 있습니다.

여러 함수와 쿼리를 활용하여 다양하게 변형해보시고, 혹시나 어려운 부분이 있는 경우 댓글로 남겨주세요!

 

: )