엑셀 자동 서식으로 입고, 출고에 따라 재고를 파악 할 수 있는 재고관리 서식을 만들어서 사용할 수 있습니다.
재고관리 자동서식을 만들어서 현재 재고현황을 파악하고 물품의 입고와 출고의 흐름을 파악할 수 있기 때문에 업무 효율을 증대 시킬 수 있겠습니다.
이번 글에서는 물품의 입고, 출고 수량에 따라 재고 수량이 자동으로 더해지거나 차감되는 샘플 서식을 제공하고 함수와 수식을 작성해 보면서 사용자의 환경에 맞는 자동 서식을 만들어 보면 좋겠습니다.
예제 서식.
재고 관리 서식은 사용자의 환경에 따라 서식 자체를 작성하는 방법은 다양하게 구현할 수 있기 때문에 모든 환경에서 적용하기에는 어렵습니다.
따라서 본문에서 준비한 예제 서식은 2가지로 준비를 해 봤고, 서식의 형태에 따라 함수와 수식을 어떻게 사용해야 하는지 확인함으로써 사용자의 서식에 맞게 수식을 수정하거나 새롭게 작성하여 활용할 수 있는지가 중요할것입니다.
예제 서식1.
예제1에서는 현재 재고에서 입고/출고수량을 더하거나 차감해서 현재 재고를 파악할 수 있도록 제품별로 나열한 형태의 서식.
제품 카테고리별로 전월재고(시작재고)를 작성하고 입고, 출고 수량을 이슈가 발생할때마다 작성하면 제품 카테고리별 현재 재고를 파악할 수 있습니다.
예제 서식2.
예제2에서는 현재재고에서 제품 카테고리별로 입고/출고 수량을 더하거나 차감해서 제품 카테고리별 현재 재고 수량을 파악 할 수 있는 재고 현황판 형태의 서식.
예제1 서식과 마찬가지로 제품 카테고리별로 입고, 출고에 대한 기록을 하면 전월재고(시작재고)를 기준으로 현재 재고를 파악할 수 있습니다.
다만 예제1 서식과 차이점은 제품 카테고리별로 재고 현황판 서식으로 작성하여 제품 카테고리별로 현재 재고를 파악할 수 있다는것이 장점인 서식이 될 수 있겠습니다.
입고/출고 자동 계산 수식.
엑셀 재고 관리 서식은 원/부자재의 투입량에 대한 재고 관리가 아닌 제품 재고의 경우에는 단순하게 시작재고에서 입고수량은 더해주고 출고수량은 차감하는것으로 현재 재고를 계산할 수 있습니다.
예제1서식과 예제2서식의 수식을 아래에서 확인해보고 샘플 서식을 그대로 활용 가능한 경우가 아니라면 사용자의 환경에 맞도록 새롭게 서식을 작성하고 입고,출고 계산 수식을 활용할 수 있습니다.
예제1서식 입출고 계산 수식.
A열~E열은 전월재고, M열~S열은 입고,출고 수량에 대한 데이터를 기록하는것으로 직접 입력해줘야 합니다.
G열부터K열은 현재 재고를 전월재고+(+입고수량or -출고수량)으로 계산한 수량을 나타내는데 G열~J열은 직접 작성해줘야할것으로 제품명, 제품 카테고리별로 구분을 해서 작성을 해 주면 됩니다.
| K열 현재 재고 수식.
=SUM(SUMIFS($E$3:$E$13,$A$3:$A$13,$G3,$B$3:$B$13,$H3,$C$3:$C$13,$I3,$D$3:$D$13,$J3), ▲ 전월재고(시작재고)+입고수량-출고수량 계산 수식.
T열 제품 카테고리별 현재 재고 수식. =SUMIFS($K$3:$K$13,$G$3:$G$13,$N3,$H$3:$H$13,$O3,$I$3:$I$13,$P3,$J$3:$J$13,$Q3) ▲ 입출고 데이터를 기록한 제품 카테고리별 현재 재고수량을 입력해 주는 수식. |
예제서식1의 경우에는 입출고 데이터와 제품 카테고리별 현재 재고를 행방향으로 나열한 서식으로 합계및 다중조건 합계 수식으로 작성하여 현재 재고 수량을 계산하여 결과값으로 표기할 수 있습니다.
예제2 서식 입출고 계산 수식.
예제서식2에서는 제품별로 제품 카테고리별로 현황판 서식처럼 작성하여 확인하고자 하는 제품 재고를 한눈에 볼 수 있다는 장점이 있습니다.
그러나 행방향으로 수량을 계산하는것이 아니고 제품 카테고리별로 가로,세로를 참조하여 수량을 계산해야 하기 때문에 단순히 합계(SUM함수)와 다중조건합계(SUMIFS함수)만으로는 계산 수식을 작성할 수 없습니다.
A열부터 Q열까지는 전월재고(시작재고)와 입출고 데이터 기록으로 직접 작성해줘야 하고 S열부터 AA열까지는 제품 카테고리별로 계산 수식을 작성해 주면 다음과 같이 수식을 작성할 수 있습니다.
| 제품1/Type1/ 현재 재고(범위-T4:V5)
=INDEX($B$4:$D$5,MATCH($S4,$A$4:$A$5,0),MATCH(T$3,$B$3:$D$3,0))+ ▲ 위 수식을 T4셀에 작성 후 V5셀까지 수식복사. 나머지 제품/카테고리별로 참조 범위를 수정하여 수식복사하여 현재 재고 자동 계산 수식으로 활용 할 수 있습니다. |
예제2 서식은 현황판 형태의 서식으로 작성해서 제품별, 카테고리별로 재고 수량을 한눈에 파악하는것이 용이하지만 수식의 참조 범위를 제품별, 카테고리별로 수정해줘야 하는 번거로움이 생기게 됩니다.
이때 참조범위를 동적 참조범위로 작성할 수 있는 Offset함수를 사용해서 작성해 줄수는 있겠지만 offset함수 수식으로 참조범위를 동적범위로 간편하게 작성하려면 서식의 형태에 약간의 변화를 줄 필요가 있고, 수식이 길어지는 문제로 수식을 작성하는데 어려움이 따른다면 위 수식만으로도 충분히 벅찰 수 있습니다.
글을 마치며…[행아아빠]
엑셀 서식으로 간단하게 제품 재고관리가 가능한 샘플 서식 2종과 함께 현재 재고가 계산되는 수식을 작성해 보았습니다.
서식의 형태에따라 계산해야할 참조범위, 조건을 찾을값의 위치나 형태가 달라지기 때문에 함수와 수식의 작성방법이 달라질 수 있습니다.
그러나 “시작재고+입고수량-출고수량=현재재고”의 연산이 달라지는것은 아니기 때문에 기본적인 연산방법을 생각하고 서식의 형태에 따라 사용해야할 함수와 수식을 작성하는것으로 사용자의 환경에 맞는 재고관리 자동 서식을 작성할 수 있다고 하겠습니다.
이번 글에서는 단순히 제품, 제품의 카테고리별로 재고관리가 가능한 수식을 작성해 보았는데 원/부자재의 투입,소모등으로 현재 재고 수량을 계산하는 경우도 있을 수 있습니다.
특히 제조업 분야에서는 완제품 재고관리 뿐만 아니라 원/부자재의 BOM관리가 가능한 엑셀 서식이 필요한 경우도 있을텐데, BOM관리가 가능한 엑셀 샘플 서식도 준비해서 글을 작성해 보도록 하겠습니다.
샘플 서식 2종 다움로드.
| 재고 관리 샘플 서식 2종 |
