생산 및 물류 솔루션을 사용하지 않는 매장이나 회사에서 생산 품목수가 점점 늘어나는 경우, 매번 수기로 제조 순서를 정하기에는 복잡해질 수 있습니다.
마감 시, 재고를 조사하고 표에 입력을 하면 내일 생산해야 할 품목이 어떤 것이고, 어떤 순서로 제조를 해야하는지 자동으로 계산되는 시트를 만들어보겠습니다.
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. 응용
다양한 방법으로 응용이 가능합니다.
재고가 안전수량과 동일해도 제조가 되게 수정하거나, 필요량에 따른 우선순위가 아닌 회사에서 정한 우선순위로 제조스케줄이 나오게끔 할 수도 있습니다.
여러 함수와 쿼리를 활용하여 다양하게 변형해보시고, 혹시나 어려운 부분이 있는 경우 댓글로 남겨주세요!
: )
'구글 스프레드 시트' 카테고리의 다른 글
구글 스프레드 시트 함수 - 요일 함수 WEEKDAY (0) | 2023.04.30 |
---|---|
구글 스프레드시트 출근부 만들기 (0) | 2023.04.30 |
구글 스프레드시트 엑셀 함수 - 날짜계산 date, datedif, days (0) | 2023.03.15 |
엑셀 함수 초보자를 위한 기본 개념 (함수 설명 이해하기) (0) | 2023.03.13 |
구글 스프레드 시트 공유 권한 / 범위보호 / 시트보호 (0) | 2023.02.22 |
댓글