안녕하세요. 행아아빠 입니다.
오늘 엑셀 내용으로는 재고 관리 엑셀 서식 두 번째 내용으로 제품명 - 제품명과 같이 하위 제품명으로 구분되는 재고 관리 엑셀 서식을 만들고 자동으로 재고 계산을 할 수 있는 함수와 수식을 작성해 보도록 하겠습니다.
예를 들면 "제품명 : 티셔츠 - 색상 : 블랙"과 같은 제품의 재고 관리 방법이라고 볼 수 있겠습니다.
[엑셀] - 재고관리, 재고현황 자동으로 계산할 수 있는 수식 작성 방법.
[엑셀] - 엑셀 재고 관리 서식 만들기와 재고 자동 계산 함수 수식 작성 방법.
오늘 엑셀 내용에 작성할 함수는 주로 Sumifs함수를 이용해서 자동 계산이 될 수 있도록 작성할 것입니다.
그럼 오늘 내용도 많은 분들께 도움이 될 수 있기를 바라겠습니다.
YouTuBe : https://www.youtube.com/channel/UCQlocK7yFlXLZFt-VTxVaAg
네이버 Tv : https://tv.naver.com/hangadaddy
■ 재고 관리 엑셀 서식.
재고 관리 엑셀 서식의 시트는 1. 입출고 시트, 2. 월별 제품 입출고 현황, 3. 거래처 리스트 이렇게 3개의 시트로 이루어져 있습니다.
1. "입출고" 시트.
입출고 시트는 제품의 입고, 출고 데이터를 직접 입력해 주어 재고 현황 또는 매입, 매출액을 자동으로 계산할 수 있도록 참조하는 기본 데이터를 입력해 주는 시트라고 할 수 있겠습니다.
또한 오른쪽에는 재고현황을 제품군별로 총 입고 수량과 총 출고 수량 그리고 현재 재고 수량이 얼마나 남았는지 확인할 수 있는 표 서식이 작성하여 현재 해당 제품군 재고 수량이 얼마나 남았는지 데이터를 입력하면서 확인 할 수 있습니다.
2. "월별제품입출고현황" 시트.
월별 제품 입출고 현황 시트에서는 제품의 입고와 출고에 대해서 월 단위마다 각각의 수량을 확인할 수 있는 표 형태의 서식을 작성하여 각 월마다 어떠한 제품이 몇 개가 입고 되었고, 몇개가 출고되었는지 확인해 볼 수 있는 서식입니다.
3. "거래처리스트" 시트.
마지막으로 "거래처 리스트" 시트 에서는 거래처의 기본 정보를 직접 입력해 주시면 되겠습니다.
거래처 정보등록은 사실 사용해도 되고 안해도 되겠지만, 해당 샘플 서식에서는 구현하지 않았지만 거래처마다의 거래 품목과 단가등 이러한 부분을 작성해 놓는다면 재고 관리 뿐만 아니라 재고를 입고 하기 위해서 발주 해야 하는 거래처 리스트를 한눈에 볼 수 있는 서식도 만들 수 있을 것 같습니다.
■ 재고 관리 엑셀 서식 자동 계산을 위한 수식 작성 방법.
1. "입출고" 시트 수식 작성.
재고 현황을 자동으로 계산하기 위한 수식을 "입출고" 시트에 작성된 내용으로 먼저 확인을 해 보면 "입출고 상세 내역"의 표 서식 부분은 대부분 직접 입고와 출고에 대한 정보를 입력해 주셔야 하는 부분 입니다.
다만, 여기서 재고 현황과 월별 입고, 출고 부분을 자동으로 계산 하기 위한 부분 또는 직접 계산하여 입력하지 않아도 되는 부분은 간단한 수식을 이용해서 자동 계산 될 부분으로 만들어 주시면 되겠습니다.
우선 A열과 B열은 수식을 작성하고 나서 추 후 열 숨기기를 해 줘야 할 부분으로 재고 현황 수식에 참조할 영역으로 활용할 부분 입니다.
A열과 B열에 각각 작성할 수식은 C열에 입력해준 날짜의 년도와 월을 별도로 각각 A열과 B열에 입력 해주기 위해서 다음과 같이 작성 할 수 있겠습니다.
A6셀 연도 수식 : =IF(C6="","",YEAR(C6))
B6셀 월 수식 : =IF(C6="","",MONTH(C6))
연도와 월을 표기 하는 함수는 각각 Year, Month함수인데 IF함수를 이용해서 조건문으로 작성해 준 이유는 C열의 날짜 부분이 입력되지 않았을 경우에는 엉뚱한 날짜 값이 A,B열에 표기 되기 때문에 IF조건문을 이용해서 C열의 날짜 값이 입력되지 않았을 때에는 공란으로 표기 하기 위해 조건문으로 입력 한 것 입니다.
이제 해당 수식을 작성했다면 필요한 셀까지 수식을 복사해 주고, A,B열은 숨기기 하여 서식에서 보이지 않도록 깔끔하게 정리 해 주시면 되겠습니다.
다음으로 입고액 K열과 매출액 L열에 대해서 수식을 작성해 주시면 되겠는데요.
수식은 간단하게 다음과 같이 작성해 주시고, 필요한 셀까지 수식 복사 해서 사용해 주시면 되겠습니다.
K6셀 입고액 수식 : =SUM(G6*H6) / 또는 =G6*H6
L6셀 매출액 수식 : =SUM(I6*J6) / 또는 =I6*J6
수식을 작성하지 않은 날짜, 제품명1(티셔츠), 제품명2(블랙색상), 거래처, 입고수량, 입고단가, 출고수량, 출고단가 부분은 직접 입력해 줘야 하는 데이터 부분으로 보시면 되겠습니다.
직접 입력한 입고 출고 데이터를 참조 하여 매입금액과 매출액을 간단하게 확인 할 수 있도록 표기 할 수 있는 셀은 매입금현황 D2셀과 매출현황 F2셀에 수식을 다음과 같이 작성해 주시면 되겠습니다.
매입현황 D2셀 수식 : =SUMIFS($K$6:$K$1001,$A$6:$A$1001,$C$1,$B$6:$B$1001,$E$1)
매출현황 F2셀 수식 : =SUMIFS($L$6:$L$1001,$A$6:$A$1001,$C$1,$B$6:$B$1001,$E$1)
다음으로 "입출고" 시트의 오른쪽에 작성되어 있는 재고 현황 서식에서 입출고 상세 내역을 참조 하여 총 입고 수량과 총 출고 수량 그리고 현 재고 수량을 자동으로 계산하여 표기 할 수 있는 수식을 작성해 보도록 하겠습니다.
우선 제품명1 N열과 제품명2 O열은 직접 입력해 주시면 되는 부분이고, 총 입고수량 P열과, 총 출고수량 Q열, 현 재고 수량 R열에 대해서 수식을 작성하면 다음과 같이 작성 할 수 있겠습니다.
총 입고 수량 P6셀 수식 : =SUMIFS($G$6:$G$1000,$D$6:$D$1000,$N6,$E$6:$E$1000,$O6)
총 출고 수량 Q6셀 수식 : =SUMIFS($I$6:$I$1000,$D$6:$D$1000,$N6,$E$6:$E$1000,$O6)
현 재고 수량 R6셀 수식 : =SUM(P6-Q6)
위와 같이 수식을 작성 했다면 필요한 셀까지 또는 자동으로 값을 표기할 부분까지 수식 복사해서 사용해 주시면 되겠습니다.
2. "월별 제품 입출고 현황" 시트 수식 작성.
"월별 제품 입출고 현황" 시트에서는 제품 월별로 입고 수량을 확인 할 수 있는 표 서식과, 제품 월별로 출고 수량을 확인 할 수 있는 표 서식 2가지로 분리해 볼 수 있겠습니다. 두 서식 모두 자동으로 표기 하기 위한 함수는 Sumifs함수를 주된 함수로 수식을 작성하면 됩니다만, 참조하는 범위 영역이 다르다고 할 수 있겠습니다.
제품 입고 현황에 직접 입력해 주셔야 할 부분은 A열과 B열로 제품명과 하위 제품명을 각각 직접 입력해 주시면 되고, 나머지는 모두 함수로 자동 계산되어 표기 될 부분으로 보시면 되겠습니다.
간단하게 합계 부분은 3행과 O열로 3행은 각 월별 합계, O열은 전체 1월부터 12월까지의 합계로 보시면 되겠습니다. 수식은 Sum 합계 함수를 이용해서 다음과 같이 작성 할 수 있겠습니다.
C3셀 각 월별 합계 수식 : =SUM(C4:C1000)
O3셀 전체 월 합계 수식 : =SUM(C3:N3)
위와 같이 수식을 작성 하고, C3셀 수식은 N3셀까지 복사하고, O3셀 수식은 필요하거나 사용할 부분까지 아래로 수식 복사해서 사용해 주시면 되겠습니다.
다음으로 표 안에 제품명과 하위 제품명 그리고 해당월에 입고된 수량을 자동 계산하기 표기 하기 위한 수식은 Sumifs함수를 이용해서 다음과 같이 작성해 주시면 되겠습니다.
C4셀 수식 : =SUMIFS(입출고!$G$6:$G$1001,입출고!$D$6:$D$1001,월별제품입출고현황!$A4,입출고!$B$6:$B$1001,월별제품입출고현황!C$2,입출고!$E$6:$E$1001,월별제품입출고현황!$B4)
위 수식을 작성했다면 필요하거나 사용하고자 하는 범위까지 수식 복사해서 사용해 주시면 되겠습니다. 샘플 양식에서는 36행인 N36까지 수식을 복사해서 입력해 놓았습니다.
제품 출고 현황 표 서식도 마찬가지로 Q열과 R열은 직접 입력해 주셔야 할 제품명과 하위 제품명입니다.
그리고 3행과 AE열은 단순 합계 수식으로 작성한 행과 열이기 때문에 Sum함수를 이용해서 S3셀에는 =SUM(S4:S1000) 수식을 입력하고 AD열까지 수식을 복사해 시면 되고, AE3셀에는 =SUM(S3:AD3) 수식을 작성한 다음 사용하시는 행까지 수식을 복사해서 사용해 주시면 되겠습니다. 해당 샘플 양식에서는 36행까지 수식을 복사해서 1월부터 12월까지의 합계를 표기 할 수 있도록 했습니다.
이제 각 월별의 제품명과 하위제품명의 출고 현황의 값들을 표기 하기 위한 수식을 작성해야 하는데 입고 현황에서 작성했던 것처럼 Sumifs함수를 이용해서 다음의 수식을 작성해 주시면 값을 표기 할 수 있겠습니다.
A4셀에 입력될 수식 : =SUMIFS(입출고!$I$6:$I$1001,입출고!$B$6:$B$1001,월별제품입출고현황!S$2,입출고!$D$6:$D$1001,월별제품입출고현황!$Q4,입출고!$E$6:$E$1001,월별제품입출고현황!$R4)
위 수식을 작성해 주시고, AD36셀까지 수식 복사해 주시면 출고 현황에 대한 값이 자동으로 표기 될 수 있는 수식은 작성이 완료 되었습니다.
사용하실 제품명이 36행을 넘어 가신다면 해당 수식을 사용하실 영역만큼 수식을 복사해 주시면 되겠습니다.
이렇게 Sumifs함수를 이용해서 제품 입고와 출고에 대한 간단한 재고 관리 엑셀 서식을 작성해 보았습니다.
재고 관리 서식은 사용자에 따라서 너무나도 다양한 형식으로 데이터가 작성 될 수 있고, 재고 현황을 어떠한 형식으로 보고 싶으냐에 따라 달라질 수 있기 때문에 앞으로도 다양한 재고 관리 엑셀 서식을 샘플 형식으로 작성해서 내용 올려 보도록 하겠습니다.
그럼 오늘 하루도 좋은 하루 되시길 바라겠습니다.^^!
'엑셀' 카테고리의 다른 글
| 텍스트로 입력된 날짜 0000-00-00 형식으로 표기 하는 엑셀 수식 작성 방법. (0) | 2021.06.23 |
|---|---|
| 세후 급여 (실 지급 급여) 금액으로 세전 급여 금액 추정 하는 엑셀 서식과 수식 작성 방법. (6) | 2021.06.22 |
| 엑셀 재고 관리 서식 만들기와 재고 자동 계산 함수 수식 작성 방법. (1) | 2021.06.06 |
| 엑셀 함수 정리-직장인을 포함하여 엑셀을 사용한다면 엑셀 자동 계산을 하기 위해서 꼭 알아야 할 실무 엑셀 함수. (0) | 2021.05.25 |
| 2021년 급여 대장 엑셀 자동 서식(양식) 만들기와 엑셀 자동 계산 수식 작성 방법. (0) | 2021.05.16 |
댓글