엑셀 선입선출 재고관리, 함수만으로 손쉽게 관리하는 방법

엑셀 함수만 사용하는 재고 선입선출 관리방법에 대해 선입선출 계산 원리부터 실전 응용예제까지 단계별로 알아봅니다.

홈페이지 » 엑셀 선입선출 재고관리, 함수만으로 손쉽게 관리하기

작성자 :
오빠두엑셀
최종 수정일 : 2021. 01. 22. 19:49
URL 복사
메모 남기기 : (14)

엑셀 선입선출 재고관리, 함수만으로 손쉽게 관리하는 방법

엑셀 선입선출 재고관리 목차 바로가기
영상 강의

큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 모든 강의 예제파일은 회원 여러분에게 무료로 제공됩니다.

  • [엑셀고급] 엑셀 재고관리의 모든것 - 선입선출 관리
    완성파일
  • [엑셀고급] 엑셀 재고관리의 모든것 - 선입선출 관리
    예제파일

.

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

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


엑셀 선입선출 재고관리를 위한 2가지 전제조건

물류는 예측 불가능한 상황이 수시로 발생하는 분야입니다. 그러다보니. 선입선출 재고관리는 엑셀뿐만 아니라, 다른 프로그램에서도 100% 완벽하게 자동화하여 구현하는 것이 현실적으로 어려운 관리방법 인데요.

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

'박스단위 관리'

입니다. 선입선출 재고관리는 동일한 제품이 여러개의 박스로 나누어 입고된 경우, '스커트=200개' 가 아닌 '박스1=50개, 박스2=50개, 박스3=50개, 박스4=50개' 로 데이터를 나누어 입력해야 합니다.

재고 선입선출 박스단위 관리
선입선출 재고관리는 박스당 관리를 기본 원칙으로 합니다.

엑셀은 모든 사용자가 손쉽게 데이터를 가공할 수 있다는 큰 장점이 있습니다. 따라서 선입선출 재고관리의 계산 원리를 이해하고 각각의 박스단위로 재고를 관리할 수 있는 환경만 갖추어져 있다면 누구나 손쉽게 엑셀 함수만으로 손쉽게 선입선출 관리를 할 수 있습니다.

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

  1. 출고원가를 건by건으로 확인하지 않고 특정 기간으로 나누어서 관리해도 괜찮은 경우
  2. 예외상황이 많이 발생하지 않으며, 예외상황이 발생할 경우 별도의 다른 시트에서 관리해도 괜찮은 경우

위 두가지 조건을 모두 만족한다면, 엑셀 함수만으로 선입선출 재고관리를 할 수 있습니다.

선입선출 계산공식 동작원리

선입선출 재고관리의 계산원리는 간단한데요. 한가지만 기억하시면 됩니다. 그것은 바로,

'출고수량'

인데요. 선입선출로 계산 된 출고수량을 구하는 엑셀 공식은 아래와 같습니다.

= MIN ( 현재 박스수량, (총 판매수량 - 이전 입고수량) )

위 공식의 계산 원리는 아래 표를 보시면 쉽게 이해할 수 있습니다. 아래 예제를 보시면, 1월 7일까지 총 450개의 재고가 입고되었고, 이 중 101개의 재고가 판매되었다고 가정하겠습니다. 그럴 경우 출고수량은 아래와 같이 계산됩니다.

입고일 입고(현재박스)
[A]
총판매-이전입고
[B]
출고수량
[A,B 중 작은값]
1월 1일 50 101-0 = 101 50
1월 2일 100 101-50 = 51 51
1월 3일 50 101-150 = -49 (0) -
1월 4일 100 ... (0) -
1월 5일 50 ... (0) -
1월 6일 100 ... (0) -
합계 450 - 101

그리고 1월 7일에 50개가 입고되고, 240개가 판매 될 경우 출고수량은 아래와 같이 계산됩니다.

입고일 입고(현재박스)
[A]
총판매-이전입고
[B]
출고수량
[A,B 중 작은값]
1월 1일 0 - -
1월 2일 49 240-0 = 240 49
1월 3일 50 240-49 = 191 50
1월 4일 100 240-99 = 141 100
1월 5일 50 240-199 = 41 41
1월 6일 100 240-249 = -9 (0) -
1월 7일 50 ... (0) -
합계 399 - 240

선입선출 재고관리 계산 원리, 알고보니 매우 간단하죠? 🙂 이제 예제파일을 통해서 선입선출 재고관리를 실제로 구현하는 방법에 대해서 단계별로 알아보겠습니다.

엑셀 선입선출 재고관리 시트 만들기

예제파일의 [3]번 시트로 이동하면 1개 제품을 선입선출로 관리하기 위한 데이터가 있습니다. 이번 강의에서는, 보다 쉬운 설명을 위해 제품이 1개인 경우를 살펴볼 예정입니다.

제품이 여러개인 경우에도, 수식의 SUMIF 함수만 SUMIFS 함수로 바꾼 다음 계산 조건으로 '제품명'만 추가해주면 되는데요. 계산 원리만 이해한다면 누구나 어렵지 않게 선입선출 재고관리 시트를 만들 수 있습니다.

제품명 뿐만 아니라 각 제품에 추가되는 속성(예: '제품색상', '제품크기') 등도 SUMIFS 함수에 조건만 추가하면 손쉽게 선입선출 재고관리를 할 수 있습니다.

1. 선입선출 출고수량 계산하기
  1. 예제파일의 [3] 시트로 이동합니다. [3] 시트에는 각 '입고', '판매', '출고', '잔여(기말재고)', 총 4개로 구분되어 있습니다.

    선입선출 재고관리 파일
    예제파일의 재고관리 시트는 총 4개 섹션으로 구분되어 있습니다.
  2. 예제파일의 6행과 8행사이를 보면 얇은 선이 있습니다. 마우스로 두 행의 간격을 넓혀서 7행이 보이도록 만들어주세요. (시트가 완성된 이후 7행을 숨겨줘도 괜찮습니다.)

    7행 높이 조절
    6행과 8행 사이의 행 높이를 넓혀서 7행이 보이도록 합니다.
  3. K8셀(출고내역의 Box)을 선택한 뒤, 동일한 위치의 구매내역 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를 불러오도록 수식을 입력합니다. 출고내역과 동일하게 수식을 자동채우기 합니다.

    기말재고 값 입력
    구매내역의 BOX~단가 데이터를 기말재고 범위에 동일하게 불러옵니다.
  2. 기말재고의 수량은 [구매수량-출고수량] 입니다. 예제파일 T8셀에 아래 수식을 입력한 뒤, 아래로 자동채우기 합니다.
    =E8-N8

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

    기말재고 금액 계산
    기말재고 금액을 계산하면 기말재고 정리가 완료됩니다.
3. 현재 보관중인 기말재고만 출력하기

'구매수량 - 출고수량'으로 기말재고를 계산하면 수량이 0인 재고도 같이 표시됩니다. 따라서 현재 보관중인 수량만 표시하도록 기말재고_필터 범위를 입력합니다. MS365 버전을 사용중이실 경우 FILTER 함수로 쉽게 해결할 수 있으며, 365 이전버전 사용자는 VLOOKUP 여러개 값 불러오기 공식을 사용합니다. 각 FILTER 함수와 VLOOKUP 여러개 값 불러오기 공식에 대한 자세한 설명은 아래 링크를 참고하세요.

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

  1. 아래 수식을 복사한 뒤, 예제파일의 W8셀에 아래 수식을 입력하면 기말재고 중 수량이 0보다 큰 재고(현재 보관중인 재고)만 필터링됩니다.
    =FILTER(Q8:U23,T8:T23>0)

    기말재고 보유중 재고 필터링
    365 버전 사용자는 FILTER 함수로 수량이 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)),"")

    기말재고 보유중인 재고 365 이전
    VLOOKUP 여러개 값 불러오기 수식을 붙여넣기 한 뒤, 오른쪽/아래방향으로 자동채우기 합니다.
  2. 붙여넣기 한 수식을 넓이에 맞춰 오른쪽, 아래방향으로 자동채우기하면 현재 보관중인 수량만 필터링됩니다. 단, 해당 수식은 배열수식이므로, 넓은 범위에 공식을 사용할 경우 파일의 처리속도가 느려질 수 있습니다.

    엑셀 현재 보유중 재고 필터링 완료
    현재 보유중인 기말재고 수량만 출력됩니다.

VBA를 활용한 선입선출 재고관리

엑셀 매크로를 활용하면 재고관리를 더욱 편리하게 자동화 할 수 있습니다. 이번 강의 예제파일에 포함된 매크로는 아래 과정으로 확인할 수 있습니다.

  1. 예제파일을 실행한 뒤, [개발도구] - [Visual Basic] 버튼을 클릭하거나, 단축키 ALT + F11 키로 매크로 편집기를 실행합니다. 만약 개발도구가 안보이실 경우, 개발도구 활성화 방법 관련 포스트를 확인하세요.

    개발도구 visual basic 이동
    개발도구 - 매크로 편집기를 실행합니다.
  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강으로 나누어 자세히 소개해드렸습니다. 관련 라이브강의는 아래 링크를 확인해주세요.
5 9 투표
게시글평점
14 댓글
Inline Feedbacks
모든 댓글 보기
14
0
여러분의 생각을 댓글로 남겨주세요.x