[VBA] 재고 관리 엑셀 자동화 - 양식 공유

지인이 사업을 하고 있는데 매입과 매출, 재고를 각각의 엑셀 파일로 관리하고 있는 것을 보았다.

매입과 매출이 발생했을 때 수행하는 단계가 다음과 같았다.

매입 : 매입 발생 > 매입 엑셀 작성 > 재고 엑셀 작성

매출 : 매출 발생 > 매출 엑셀 작성 > 재고 엑셀 작성

계산서 발행 : 매출 엑셀에 발행일 표시 > 계산서 시트에 복사 > 출력

 

재고 엑셀 파일을 수작업으로 업데이트하다보니 누락되거나 오기입되는 경우도 종종 있었고,
계산서 발행도 수작업으로 복사를 하다보니 시간과 노력이 많이 필요하고 무엇보다 불편해보였다.

반복되는 일은 최대한 자동화를 하고 있는 개발자 입장에서 좀 더 수고를 줄이면서 편리하고 체계적으로 관리할 수 있도록 해주고 싶어서 작업에 착수했다.

처음에는 파이썬으로 tkinter gui를 사용하여 만들었다. 원하는 기능 구현은 완료하였지만 불편함이 있었다.

역시 상황에 맞는 언어를 써야한다는 것을 느끼고 VBA를 공부하면서 다시 만들었다.

자동화를 하면서 공부했던 내용들을 정리해보려한다.

글 하단 링크에서 해당 파일을 받을 수 있다. 

유사한 환경에서 재고를 관리한다면 그대로 사용을 해도 좋고, 정리하는 과정을 참조에서 자신의 환경에 맞게 수정해서 사용하는 것도 좋을 것이라 생각한다.

 

목표

• 매입, 매출, 재고 관리를 하나의 엑셀로 관리
• 매입, 매출에 입력시 재고에 자동 업데이트
• 재고 항목별 수량, 평균 매입가, 수익 자동 계산
• 거래명세표 작성 개선
• 월별 매입, 매출, 미수금 확인
• 매입, 매출 검색 기능 

위 목표를 위해 시트를 만들고 각 시트별 기능을 정의하였다

시트별 기능

• 표지
  • 월별 매입, 매출, 미수금 출력 : 매입과 매출 시트의 데이터를 취합하여 표시
• 매입
  • 구매일, 결제일 월별 필터
  • 재고 관리를 위해 품명, 단위, 매입처를 목록에서 선택하도록 구현
  • 외화로 매입하는 경우 대응을 위해 외화와 환율이 입력되는 경우 원단가 자동 계산
  • 공급가액, 부가세, 총액은 자동 계산
• 매출
  • 발생일, 계산서 발행일, 수금일 월별 필터
  • 재고 관리를 위해 품명, 단위, 매입처를 목록에서 선택하도록 구현
  • 거래업체명 목록에서 선택하도록 구현
  • 외화로 매입하는 경우 대응을 위해 외화와 환율이 입력되는 경우 원단가 자동 계산
  • 총액 자동 계산
  • 품명, 단위, 매입처를 확인하여 매입단가, 매입금액, 차익 자동 계산
• 재고
  • 품명, 단위, 매입처를 기반으로 매입, 매출 시트의 데이터 취합
  • 재고 수량, 평균매입단가, 재고 평가액 자동 계산
  • 금년 매입량/액, 매출량/액, 수익 자동 계산
  • 누적 매입량/액, 매출량/액, 수익 자동 계산
  • 이월 매입량/액, 매출량/액 입력 가능
• 거래처
  • 상호, 사업자명, 대표자, 주소 정보 관리
• 거래명세표
  • 공급받는자 보관용에 데이터 입력시, 공급자 보관용에 데이터 자동 입력
  • 거래처 정보 자동입력
  • 법인 정보 자동 입력
  • 선택 아이템 수에 따라 다른 양식 적용
• 법인
  • 세금계산서 출력 정보 보관

 

다음 글 부터 각 시트별 구현 방법에 대해 정리하도록 하겠다.

재고 관리 엑셀 공유

작업 완료해 놓은 재고 관리 엑셀 파일은 링크에서 받을 수 있다.