[엑셀] 재고관리, 재고현황 자동으로 계산할 수 있는 수식 작성 방법.

안녕하세요.

행아아빠입니다.

​

이번 #엑셀 내용으로는

"재고현황"과 관련한 내용인데요.

​

엑셀로 많이 사용하는 유형중 하나가 재고현황을 파악하기 위한 내용이지 않을까 생각합니다.

​

재고현황을 파악하기 위한 데이터의 기본 양식은 자신에게 맞는 양식으로 많이들 만들어 사용하시기에

정해진 양식은 없는것 같습니다. 품목이나, 금액, 수량등의 요건들이 너무나도 다양할 수 있기 때문인것 같습니다.

​

오늘은 물품의 입, 출고를 입력하면 그 품목에 따라 자동으로 입고와, 출고가 계산되어 현재 재고가 계산되어

한눈에 볼 수 있는 재고현황판을 볼 수 있는 그런 내용일 것 같습니다.

​

오늘 내용도 부디 많은 분들께 도움이 되어 활용 될 수 있으면 좋겠습니다.

도움이 되셨다면

구독, 좋아요, 영상공유 부탁드리겠습니다. 감사합니다^^!

 

---

 

기초재고 + 입고량 - 출고량 = 기말재고라는 전제이며,

아이템, 호수, TYPE, 칼라가

4개다 일치해야지 합계로 값이 반환되어야 하는데...

 

▲ 기초재고와 입출고 내역에 데이터를 입력해 주시면

    아이템, 호수, TYPE, 칼라를 참조하여,

    입고수량에 작성된 수량은 기초재고 + 입고수량을 하여 기말재고에 표기하고,

    출고수량에 작성된 수량은 기초재고 + 입고수량 - 출고수량을 하여 기말재고에 표기하는 내용 입니다.

​

    이렇게 하면 기말재고만 보시면, 현 재고현황을 한눈에 보실 수 있을 것 같습니다.

​

    우선은 기초재고와 입출고 내역은 직접 데이터를 입력해 주셔야 할 부분인것 같습니다.

    그럼, 기말재고의 표안에 들어갈 수량을 함수를 이용해서, 기초재고와 입출고 내역을 참조하여 수량이 자동으로 나오도록 해주면 되겠네요.

▲ 우선은 기초재고의 수량을 기말재고로 가져와야 하는 수식은

    =INDEX($B$4:$F$9,MATCH($A23,$A$4:$A$9,0),MATCH(B$22,$B$3:$F$3,0))

    INDEX 함수와 MATCH 함수를 이용해서 작성해 주시면, 기초재고에 작성된 수량이 기말재고에 자동으로 수량이 입력 되겠습니다.

▲ 추가 작성된 수식

    +IFERROR(LOOKUP(1,1/(($C$13:$C$18=B$22)*($E$13:$E$18=$A23)*($D$13:$D$18=$D$21)*

    ($B$13:$B$18=$B$21)),$F$13:$F$18),0)

    이 수식이 의미하는 것은

    입출고 내역에 작성한 아이템, 호수, TYPE, 칼라를 참조하여, 입고수량을 기말재고에 입력된 기초재고에 더해주는 것 입니다.

​

    기초재고 + 입고수량의 수식을 연결하면,

    =INDEX($B$4:$F$9,MATCH($A23,$A$4:$A$9,0),MATCH(B$22,$B$3:$F$3,0))

    +IFERROR(LOOKUP(1,1/(($C$13:$C$18=B$22)*($E$13:$E$18=$A23)*($D$13:$D$18=$D$21)*

    ($B$13:$B$18=$B$21)),$F$13:$F$18),0)

 

    이제 기초재고 + 입고수량까지 수식을 작성해 주었으니,

    출고수량을 빼 주시면 되겠는데요.

▲ 출고수량에 대한 수식은

    -IFERROR(LOOKUP(1,1/(($C$13:$C$18=B$22)*($E$13:$E$18=$A23)*($D$13:$D$18=$D$21)*

    ($B$13:$B$18=$B$21)),$G$13:$G$18),0)

​

    출고수량에 대한 수식까지 작성이 되었으니,

    수식을 모두 연결하면 기초재고 + 입고재고 - 출고재고 = 현재재고(기말재고) 이렇게 나오게 되겠네요.

    =INDEX($B$4:$F$9,MATCH($A23,$A$4:$A$9,0),MATCH(B$22,$B$3:$F$3,0))

    +IFERROR(LOOKUP(1,1/(($C$13:$C$18=B$22)*($E$13:$E$18=$A23)*($D$13:$D$18=$D$21)*

    ($B$13:$B$18=$B$21)),$F$13:$F$18),0)

    -IFERROR(LOOKUP(1,1/(($C$13:$C$18=B$22)*($E$13:$E$18=$A23)*($D$13:$D$18=$D$21)*

    ($B$13:$B$18=$B$21)),$G$13:$G$18),0)

​

    출고수량에 대한 수식까지 연결한 하나의 수식이 완성 되었습니다.

    이제 이 수식을 나머지 셀에 수식복사를 하여 붙여 넣어주시면 되겠습니다.

▲ TYPE의 유형중 타입1의 기초재고 + 입고수량 - 출고수량의 기말재고는 18개로 수량이 잘 입력 됩니다.

▲ TYPE의 유형 타입2로 내용을 바꾸어 주었더니,

    입출고 내역의 입고내용과 출고수량에 따라 기말재고도 오류 없이 잘 반영 됩니다.

▲ TYPE의 유형 타입3에 적용된 수식 역시도 오류없이 수량이 잘 입력되네요!!

---

 

재고 관리현황도 엑셀에서 함수를 이용한 수식으로 쉽게 관리 된다는것!!

​

여기에 관리 파일을 조금 더 붙여서 양식을 만든다면

생산관리까지도 연결해서 원, 부자재의 소모와 입 출고, 그리고 완제품 재고까지 파악할 수 있는 파일도 만들수 있을 것 같습니다.

​

그럼 오늘 내용도 도움되시길 바라며,

기분 좋은 하루 보내세요~~~~^^!!

 

↓ 샘플 양식 다운로드

엑셀 입출고 재고관리 자동계산 수식 만들기 샘플 양식!..xlsx