[VBA] 재고 관리 엑셀 자동화 - 재고

재고 관리 엑셀을 만들면서 가장 많은 시간을 투자했고, 핵심이 되는 부분이다.

엑셀의 목표 자체가 재고를 관리하는 부분이니 당연하다.

재고 품목은 품명+단위+매입처를 조합하여 하나의 아이템을 정의한다.

품명이 같더라도 단위나 매입처가 다르다면 서로 다른 품목으로 관리할 수 있도록 구현하였다.

아래 항목은 각각 다른 품목으로 인식된다.

품명	단위	매입처
A	A	A
A	A	B
A	B	A
A	B	B

기능

신규등록

• 입력폼을 초기화하고, 데이터를 입력할 수 있도록 한다.

수정모드

• 기존 데이터를 더블 클릭했을 때, 입력 폼에 해당 데이터가 입력된다. 
  기존 데이터를 수정하고자 할 때를 위한 기능이다.

저장

• ID가 신규일 경우에는 신규 항목으로 등록한다.
• 기존 데이터를 더블클릭해서 수정모드로 진입했다면 해당 ID를 가지는 값을 수정한다.
• 품목, 단위, 매입처, Date Code, 생산, 비고 정보등을 입력하고 저장을 누르면 저장이 된다.
• 올해의 매입(량/액), 매출(량/액), 수입액은 자동으로 계산된다.
• 매입탭에서 품명, 단위, 매입처가 일치하는 항목들의 값을 취합해서 매입정보를 가져온다.
• 매출탭에서 품명, 단위, 매입처가 일치하는 항목들의 값을 취합해서 매입정보를 가져온다. 
• 가져온 매입, 매출 정보를 기반으로 하여 수익을 계산한다.
• 엑셀을 1년 단위로 사용하는 것을 전제로 만들었기에 매입(량/액), 매출(량/액), 수입액 항목이 있다.
• 작년 까지의 데이터를 입력하면 올해의 데이터와 계산하여 누적 데이터로 취합하고 이를 기반으로 평균 매입단가와 총 수익액이 계산이 된다.
• 여기서 계산된 평균 매입단가가 매출탭에서 입력될 때 매입단가로 계산되어 매출시 해당 항목의 수익액을 보여준다.

삭제

• 수정모드(기존 데이터를 더블클릭해서 입력폼에 데이터가 입력된 상태)에서 누를 경구 해당 데이터를 삭제한다.

검색

• 품명, 단위, 매입처, Date Code, 생산, 재고수량 등을 검색할 수 있도록 기능을 제공한다.

구현방법

신규

신규 값임을 알 수 있도록 ID를 "신규"로 설정하고, 계산식이 들어가지 않은 셀들의 값을 초기화 한다.

Sub 신규등록모드()
    [A5] = "신규"
    [B5:F5, J5, U5:Y5] = ""
End Sub

수정모드

기존 데이터를 더블클릭한 경우 입력창에 데이터를 가져온다.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row < 17 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Cancel = True
    Call 수정모드
End Sub

Sub 수정모드()
    ridx = Selection.Row
    [A5] = Range("A" & ridx)
    [B5] = Range("B" & ridx)
    [C5] = Range("C" & ridx)
    [D5] = Range("D" & ridx)
    [E5] = Range("E" & ridx)
    [F5] = Range("F" & ridx)
    [J5] = Range("J" & ridx)
    [U5] = Range("U" & ridx)
    [V5] = Range("V" & ridx)
    [W5] = Range("W" & ridx)
    [X5] = Range("X" & ridx)
    [Y5] = Range("Y" & ridx)
End Sub

저장

품명이나 매입처가 없는 경우에는 동작하지 않도록 한다.

ID값이 신규일 경우에는 제일 마지막줄에 입력된 값을 저장한다.

ID값이 있는 경우 해당 ID가 있는 줄에 입력된 값을 저장한다.

G~I, K~T는 수식이 들어가야하고, 상대참조 주소로 수식이 입력되어야 하기에 FormulaR1C1로 값을 설정해준다.

저장 후에는 입력창을 초기화하고, 값이 입력된 부분에 테두리를 그려준다.

Sub 저장()
    If [B5] = "" Or [D5] = "" Then Exit Sub
    If [A5] = "신규" Then
        ridx = [A1048576].End(xlUp).Row + 1
        Range("A" & ridx) = WorksheetFunction.Max([A12:A1048576]) + 1
    Else
        ridx = Range("A11:A1048576").Find([A5]).Row
    End If
    Range("B" & ridx) = [B5]
    Range("C" & ridx) = [C5]
    Range("D" & ridx) = [D5]
    Range("E" & ridx) = [E5]
    Range("F" & ridx) = [F5]
    Range("G" & ridx & ":I" & ridx).FormulaR1C1 = [G5:I5].FormulaR1C1
    Range("J" & ridx) = [J5]
    Range("K" & ridx & ":T" & ridx).FormulaR1C1 = [K5:T5].FormulaR1C1
    Range("U" & ridx) = [U5]
    Range("V" & ridx) = [V5]
    Range("W" & ridx) = [W5]
    Range("X" & ridx) = [X5]
    Range("Y" & ridx) = [Y5]
    Call 신규등록모드
    Call 테두리
End Sub

삭제

수정모드에서 삭제를 누를 경우 해당 ID를 가지는 줄의 데이터를 삭제한다.

Sub 삭제()
    If IsNumeric([A5]) Then
        ridx = Range("A11:A1048576").Find([A5]).Row
        Rows(ridx).Delete
        Call 신규등록모드
    End If
End Sub

검색

AdvancedFilter 필터를 통해 검색할 수 있도록 기능을 제공한다.

• 검색 범위 : Range("A16").CurrentRegion
• 검색 조건 : Range("A13").CurrentRegion

Sub 재고검색()
    Range("A16").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A13").CurrentRegion, Unique:=False
End Sub

올해 매입(량/액), 매출(량/액) 계산

매입, 매출탭에서 품명, 단위, 매입처가 동일한 아이템들의 합을 입력하도록 한다.

엑셀에서 제공해주는 SUMIFS 함수를 사용해 품명, 단위, 매입처가 일치하는 수량의 합계를 표시한다.

// 매입량
=SUMIFS(매입!$G$12:$G$3000, 매입!$D$12:$D$3000,재고!$B5,매입!$E$12:$E$3000,재고!$C5,매입!$F$12:$F$3000,재고!$D5)
// 매입액
=SUMIFS(매입!$M$12:$M$3000, 매입!$D$12:$D$3000,재고!$B5,매입!$E$12:$E$3000,재고!$C5,매입!$F$12:$F$3000,재고!$D5)
// 매출량
=SUMIFS(매출!$I$12:$I$3000, 매출!$F$12:$F$3000,재고!$B5,매출!$G$12:$G$3000,재고!$C5,매출!$H$12:$H$3000,재고!$D5)
// 매출액
=SUMIFS(매출!$M$12:$M$3000, 매출!$F$12:$F$3000,재고!$B5,매출!$G$12:$G$3000,재고!$C5,매출!$H$12:$H$3000,재고!$D5)

결과물

항목이 많다.