엑셀 재고관리의 모든것 2편 - 선입선출 관리

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

오빠두엑셀 LIVE 강의는 매주 토요일 저녁 9시에 진행합니다.

물류는 예측 불가능한 변수가 수시로 발생하는 분야입니다. 그러다 보니 선입선출 재고관리는 엑셀뿐 아니라 다른 전문 솔루션을 사용하더라도 100% 완벽하게 자동화하기가 현실적으로 어려운 영역입니다.

선입선출 재고관리를 제대로 적용하려면, 데이터를 기록하기 이전에 현실 운영에서 반드시 지켜져야 할 한 가지 규칙이 있습니다. 그것은 바로,

'박스단위 관리'

입니다. 동일한 제품이 여러 개의 박스로 나뉘어 입고되었다면, '스커트=200개'처럼 묶어서 입력하지 않고 '박스1=50개, 박스2=50개, 박스3=50개, 박스4=50개'와 같이 박스 단위로 데이터를 분리해 입력해야 합니다.

엑셀의 가장 큰 장점은 누구나 데이터를 손쉽게 가공할 수 있다는 점입니다. 따라서 선입선출의 계산 원리를 이해하고 박스 단위로 재고를 입력할 환경만 갖춰져 있다면, 별도의 프로그램 없이 함수만으로도 충분히 선입선출 재고관리를 구현할 수 있습니다.

다만 함수만 사용하는 경우 완벽한 자동화에는 한계가 있습니다. 따라서 선입선출 재고관리를 적용하기 전에 다음 두 가지 조건을 먼저 확인해야 합니다.

위 두 조건을 모두 만족한다면, 엑셀 함수만으로도 안정적인 선입선출 재고관리를 구축할 수 있습니다.

선입선출 재고관리의 계산 원리는 의외로 단순합니다. 한 가지 핵심 개념만 기억하면 됩니다. 그것은 바로,

'출고수량'

입니다. 선입선출 방식으로 계산하는 출고수량의 기본 공식은 다음과 같습니다.

공식의 동작 원리는 아래 표를 참고하면 직관적으로 이해할 수 있습니다. 예시는 1월 7일까지 총 450개의 재고가 입고되고, 그 중 101개가 판매된 상황입니다. 이 경우 출고수량은 다음과 같이 계산됩니다.

이어서 1월 7일에 50개가 추가로 입고되고, 누적 240개가 판매된 상황에서는 출고수량이 다음과 같이 계산됩니다.

선입선출 재고관리의 계산 원리는 이처럼 어렵지 않습니다. 이제 예제파일을 활용해 실제로 시트를 구축하는 단계를 차례대로 살펴보겠습니다.

예제파일의 [3]번 시트에는 한 개 제품을 선입선출로 관리하기 위한 데이터가 정리되어 있습니다. 이번 강의에서는 이해를 돕기 위해 제품이 한 개인 단순한 사례를 기준으로 설명합니다.

제품이 여러 개인 경우에는 수식의 SUMIF 함수를 SUMIFS 함수로 변경하고, 계산 조건에 '제품명'만 추가해주면 됩니다. 계산 원리만 정확히 이해하고 있다면 누구나 어렵지 않게 선입선출 재고관리 시트를 만들 수 있습니다.

제품명뿐 아니라 색상, 크기 등 다양한 속성도 SUMIFS 함수의 조건으로 추가하면, 동일한 방식으로 손쉽게 선입선출 재고관리를 확장할 수 있습니다.

1. 선입선출 출고수량 계산하기

1. 예제파일의 [3] 시트로 이동합니다. 시트는 '입고', '판매', '출고', '잔여(기말재고)' 총 4개의 영역으로 구분되어 있습니다.

   

2. 예제파일의 6행과 8행 사이에 얇은 선이 보입니다. 마우스로 두 행의 간격을 넓혀 7행이 표시되도록 조정해주세요. (시트 작업이 끝난 뒤에는 7행을 다시 숨겨도 무방합니다.)

   

3. K8셀(출고내역의 Box)을 선택한 뒤, 같은 위치의 구매내역 Box 정보를 그대로 불러오도록 수식을 입력합니다. 입력한 수식은 단가까지 오른쪽으로 자동채우기 한 다음, 아래 방향으로도 자동채우기 합니다.

   

4. 아래 수식을 복사해 예제파일의 N8셀에 붙여넣기 합니다. 이후 수식을 아래 방향으로 자동채우기 합니다. 공식의 동작 원리는 영상 강의에서 자세히 설명해드렸습니다.
   =MIN(E8,IF($I$6-SUMIF(C:C,"<"&L8,E:E)-SUMIF($L$7:L7,L8,$N$7:N7)<0,0,$I$6-SUMIF(C:C,"<"&L8,E:E)-SUMIF($L$7:L7,L8,$N$7:N7)))

   

5. O8셀(금액)은 단가 × 수량으로 계산합니다. O8셀에 아래 수식을 입력한 후 아래 방향으로 자동채우기 합니다.
   =M8*N8

   

6. 여기까지 진행하면 선입선출 출고수량 계산이 완료됩니다.

   

2. 기말재고 계산하기

1. Q8셀(기말재고의 Box)을 선택한 뒤, 같은 위치의 구매내역 Box 정보를 불러오도록 수식을 입력합니다. 출고내역과 동일한 방식으로 수식을 자동채우기 합니다.

   

2. 기말재고 수량은 [구매수량 − 출고수량]으로 산출됩니다. 예제파일 T8셀에 아래 수식을 입력한 뒤 아래 방향으로 자동채우기 합니다.
   =E8-N8

   

3. 금액은 단가 × 수량으로 계산합니다. U8셀에 아래 수식을 입력한 다음 아래 방향으로 자동채우기 하여 기말재고 계산을 마무리합니다.
   =T8*S8

   

3. 현재 보관중인 기말재고만 출력하기

'구매수량 − 출고수량'으로 기말재고를 계산하면 수량이 0인 항목까지 함께 표시됩니다. 따라서 실제로 보관 중인 수량만 출력하도록 기말재고_필터 범위를 별도로 구성합니다. MS365 버전을 사용 중이라면 FILTER 함수로 간단히 처리할 수 있고, 365 이전 버전에서는 VLOOKUP 여러 개 값 불러오기 공식을 활용합니다. 두 방법에 대한 자세한 설명은 아래 링크를 참고해주세요.

FILTER 함수를 사용하는 방법 (365 버전)

1. 아래 수식을 복사해 예제파일의 W8셀에 붙여넣으면 기말재고 중 수량이 0보다 큰 항목, 즉 현재 보관 중인 재고만 자동으로 필터링됩니다.
   =FILTER(Q8:U23,T8:T23>0)

   

VLOOKUP 여러개 값 불러오기 공식을 사용하는 방법 (365 이전 버전)

1. 아래 수식을 복사해 예제파일의 AC8셀에 붙여넣기 합니다. 해당 수식은 배열수식이므로 반드시 CTRL + SHIFT + ENTER로 입력해야 한다는 점에 유의합니다.
   =IFERROR(INDEX($Q$8:$U$16, SMALL(IF(($T$8:$T$16>0),MATCH(ROW($Q$8:$U$16), ROW($Q$8:$U$16)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")

   

2. 붙여넣은 수식을 너비에 맞춰 오른쪽과 아래 방향으로 자동채우기 하면, 현재 보관 중인 수량만 필터링되어 표시됩니다. 다만 해당 수식은 배열수식이므로 넓은 범위에 적용할 경우 파일의 처리 속도가 느려질 수 있습니다.

   

엑셀 매크로를 활용하면 재고관리를 한층 더 편리하게 자동화할 수 있습니다. 이번 강의 예제파일에 포함된 매크로는 다음 절차로 확인할 수 있습니다.

1. 예제파일을 실행한 뒤, [개발도구] - [Visual Basic] 버튼을 클릭하거나 단축키 ALT + F11로 매크로 편집기를 실행합니다. 만약 [개발도구] 탭이 보이지 않는다면, 아래 포스트를 참고해 활성화 방법을 확인해주세요.
   엑셀 개발도구 (매크로) 활성화 방법 :: 엑셀 위키

   

2. 매크로 편집기에서 [폼] - [UserForm1]을 선택합니다. 유저폼을 더블클릭하면 해당 폼에 작성된 매크로 코드를 확인할 수 있습니다.

   

3. 유저폼에 사용된 전체 명령문은 다음과 같습니다.

   Option Explicit
    
   Private Sub btnAdd_Click()
    
   Dim i As Long: Dim j As Long
   Dim db As Variant: Dim x As Long
    
   If Me.lst3.ListIndex <> -1 Then
       Me.lst2.AddItem lst3.List(Me.lst3.ListIndex, 0)
       For i = 1 To 4
           With Me.lst2
               .List(.ListCount - 1, i) = Me.lst3.List(Me.lst3.ListIndex, i)
           End With
       Next
    
       ReDim db(0 To Me.lst1.ListCount - 1, 0 To 4)
       For i = 0 To Me.lst1.ListCount - 1
           For j = 0 To 4
               db(i, j) = Me.lst1.List(i, j)
           Next
       Next
    
       x = Get_ListRecord(db, 0, Me.lst3.List(Me.lst3.ListIndex, 0))
       Me.lst1.RemoveItem (x)
       Me.lst3.RemoveItem (Me.lst3.ListIndex)
   End If
    
   End Sub
    
   Private Sub btnRemove_Click()
    
   Dim i As Long
    
   If Me.lst2.ListIndex <> -1 Then
       Me.lst1.AddItem lst2.List(Me.lst2.ListIndex, 0)
       Me.lst3.AddItem lst2.List(Me.lst2.ListIndex, 0)
       For i = 1 To 4
           With Me.lst1
               .List(.ListCount - 1, i) = Me.lst2.List(Me.lst2.ListIndex, i)
           End With
           With Me.lst3
               .List(.ListCount - 1, i) = Me.lst2.List(Me.lst2.ListIndex, i)
           End With
       Next
    
       Me.lst2.RemoveItem (lst2.ListIndex)
   End If
    
   End Sub
    
   Private Sub btnSubmit_Click()
    
   Dim i As Long: Dim j As Long: Dim x As Long
   Dim vbyn As VbMsgBoxResult
    
   vbyn = MsgBox("출고 등록하시겠습니까", vbYesNo)
   If vbyn = vbYes Then
       With Sheet6
           .Range("I4:M15").ClearContents
       End With
    
       x = 4
       For i = 0 To Me.lst2.ListCount - 1
           For j = 0 To 4
               With Sheet6
                   .Cells(x, j + 9) = lst2.List(i, j)
               End With
           Next
           x = x + 1
       Next
       Unload Me
   End If
    
   End Sub
    
   Private Sub lst3_Click()
    
   If Me.lst2.ListIndex <> -1 Then Me.lst2.Selected(Me.lst2.ListIndex) = False
    
   End Sub
    
   Private Sub lst2_Click()
    
   If Me.lst3.ListIndex <> -1 Then Me.lst3.Selected(Me.lst3.ListIndex) = False
    
   End Sub
    
   Private Sub txtFilter_Change()
    
   Dim db As Variant
   Dim i As Long: Dim j As Long
   ReDim db(0 To lst1.ListCount - 1, 0 To 4)
    
   For i = 0 To Me.lst1.ListCount - 1
       For j = 0 To 4
           db(i, j) = Me.lst1.List(i, j)
       Next
   Next
    
   db = Filtered_DB(db, Me.txtFilter.Value)
    
   Me.lst3.Clear
   If Not IsEmpty(db) Then Me.lst3.List = db
    
   End Sub
    
   Private Sub UserForm_Initialize()
    
   Dim rng As Range
   Dim db As Variant
    
   With Sheet6
       Set rng = .Range(.Cells(4, 3), .Cells(.Cells(.Rows.Count, 3).End(xlUp).Row, 8))
   End With
    
   db = rng
    
   With Me.lst1
       .ColumnCount = 5
       .ColumnWidths = "0pt;70pt;60pt;60pt;50pt"
       .List = db
   End With
    
   With Me.lst3
       .ColumnCount = 5
       .ColumnWidths = "0pt;70pt;60pt;60pt;50pt"
       .List = db
   End With
    
   With Me.lst2
       .ColumnCount = 5
       .ColumnWidths = "0pt;70pt;60pt;60pt;50pt"
   End With
    
   End Sub
    
   Function Get_ListRecord(db, ListIndex, Value) As Long
    
   Dim i As Long
    
   For i = LBound(db, 1) To UBound(db, 1)
       If db(i, ListIndex) = Value Then Get_ListRecord = i: Exit Function
   Next
    
   End Function
   Function Filtered_DB(db, Value, Optional ExactMatch As Boolean = False) As Variant
    
   Dim cRow As Long
   Dim cCol As Long
   Dim vArr As Variant: Dim s As String
   Dim vReturn As Variant: Dim vResult As Variant
   Dim Dict As Dictionary: Dim dictKey As Variant
   Dim i As Long: Dim j As Long: Dim x As Long
    
   Set Dict = New Dictionary
    
   If Value <> "" Then
       cRow = UBound(db, 1)
       cCol = UBound(db, 2)
       ReDim vArr(LBound(db, 1) To cRow)
       For i = LBound(db, 1) To cRow
           s = ""
           For j = LBound(db, 2) To cCol
               s = s & db(i, j) & "|^"
           Next
           vArr(i) = s
       Next
    
       If ExactMatch = False Then
           For i = LBound(db, 1) To cRow
               If InStr(1, vArr(i), Value) > 0 Then
                   vArr(i) = Left(vArr(i), Len(vArr(i)) - 2)
                   vReturn = Split(vArr(i), "|^")
                   Dict.Add i, vReturn
               End If
           Next
       Else
           For i = LBound(db, 1) To cRow
               If vArr(i) = Value & "|^" Then
                   vArr(i) = Left(vArr(i), Len(vArr(i)) - 2)
                   vReturn = Split(vArr(i), "|^")
                   Dict.Add i, vReturn
               End If
           Next
       End If
    
       If Dict.Count > 0 Then
           ReDim vResult(LBound(db, 1) To Dict.Count, LBound(db, 2) To cCol)
           i = LBound(db, 1)
           For Each dictKey In Dict.Keys
               x = 0
               For j = LBound(db, 2) To cCol
                   vResult(i, j) = Dict(dictKey)(x)
                   x = x + 1
               Next
               i = i + 1
           Next
       End If
    
       Filtered_DB = vResult
   Else
       Filtered_DB = db
   End If
    
   End Function
    
   '커서 이동시 버튼 색깔을 변경하는 보조명령문을 유저폼에 추가합니다.
   Private Sub OnHover_Css(lbl As Control): With lbl:   .BackColor = RGB(211, 240, 224):    .BorderColor = RGB(134, 191, 160): End With: End Sub
    
   Private Sub OutHover_Css(lbl As Control): With lbl:   .BackColor = &H8000000E:    .BorderColor = &H8000000A: End With: End Sub
    
   '유저폼에 추가한 버튼에 개수만큼 아래 명령문을 유저폼에 추가한 뒤, btnAdd를 버튼 이름으로 변경합니다.
   Private Sub btnAdd_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   OutHover_Css Me.btnAdd
   End Sub
    
   Private Sub btnAdd_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
   OnHover_Css Me.btnAdd
   End Sub
    
   '유저폼에 추가한 버튼에 개수만큼 아래 명령문을 유저폼에 추가한 뒤, btnRemove를 버튼 이름으로 변경합니다.
   Private Sub btnRemove_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   OutHover_Css Me.btnRemove
   End Sub
    
   Private Sub btnRemove_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
   OnHover_Css Me.btnRemove
   End Sub
    
   '유저폼에 추가한 버튼에 개수만큼 아래 명령문을 유저폼에 추가한 뒤, btnSubmit를 버튼 이름으로 변경합니다.
   Private Sub btnSubmit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   OutHover_Css Me.btnSubmit
   End Sub
    
   Private Sub btnSubmit_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
   OnHover_Css Me.btnSubmit
   End Sub
    
   '아래 코드를 유저폼에 추가한 뒤, "btnXXX, btnYYY"를 버튼이름을 쉼표로 구분한 값으로 변경합니다.
   Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
   Dim ctl As Control
   Dim btnList As String: btnList = "btnAdd, btnRemove, btnSubmit" ' 버튼 이름을 쉼표로 구분하여 입력하세요.
   Dim vLists As Variant: Dim vList As Variant
   If InStr(1, btnList, ",") > 0 Then vLists = Split(btnList, ",") Else vLists = Array(btnList)
   For Each ctl In Me.Controls
       For Each vList In vLists
           If InStr(1, ctl.Name, Trim(vList)) > 0 Then OutHover_Css ctl
       Next
   Next
   End Sub

4. 엑셀 매크로를 활용한 재고관리 프로그램 만들기는 라이브 강의에서 총 5강으로 나누어 자세히 다뤘습니다. 관련 라이브 강의는 아래 링크에서 확인해주세요.