제조에서 꼭 필요한 엑셀 소요량 계산기 만들기

엑셀

제조에서 꼭 필요한 엑셀 소요량 계산기 만들기

컴퓨터쟁이 2025. 1. 19. 01:56

엑셀로 제품bom별 소요량을 넣어서 자동 계산하는 기능을 만들었습니다.

그냥 제가 쓰려고 만든 것이라 다른 분들도 도움이 되면 좋을 듯 하여 공유 드립니다.

상업적으로 사용하시면 신고 예정입니다.

정말 필요하신 분들이 사용하시면 좋을 것 같아서 공유 함을 다시 한번 강조합니다.

궁금한 점이 있다면 카톡으로 문의 주시면 시간이 되면 알려드릴게요.

엑셀 알려드려요

open.kakao.com

엑셀 UserForm으로 다단계 BOM 계산 프로그램 만들기

목적:
- 제품을 조회하고, 선택한 제품의 수량을 입력하면 공정별 소요량을 계산하여 표시.
- 데이터를 Sheet "BOM"에 입력하고 결과를 Sheet "Result"에 출력.

단계별 가이드

1. 데이터 준비

Sheet "BOM"

역할: 제품과 공정, 소요량 비율을 정의.

2. UserForm 디자인

UserForm 구성 요소

텍스트박스:
- txtProductName (제품 이름 입력/선택)
- txtQuantity (수량 입력)
ListBox:
- lstProducts (제품 목록 표시)
버튼:
- btnLoadProducts (제품 목록 조회)
- btnSelectProduct (제품 선택 및 입력)
- btnCalculate (소요량 계산 실행)
- btnClose (UserForm 닫기)

UserForm 디자인 예시

레이블:
- 제품 이름, 수량 입력 안내.
텍스트박스:
- 제품 이름과 수량 입력용.
ListBox:
- 제품 목록 조회용.
버튼:
- "제품 조회", "선택", "start(계산 실행)", "end(닫기)".

3. VBA 코드 작성

3.1. UserForm 초기화

UserForm이 열릴 때 제품 목록을 자동으로 로드합니다.

Private Sub UserForm_Initialize()
Call btnLoadProducts_Click
End Sub

3.2. 제품 목록 조회

Sheet "BOM"에서 제품 목록을 ListBox에 표시합니다.

Private Sub btnLoadProducts_Click()
Dim wsBOM As Worksheet
Dim lastRow As Long
Dim i As Long

Set wsBOM = ThisWorkbook.Sheets("BOM")
Me.lstProducts.Clear

lastRow = wsBOM.Cells(wsBOM.Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow
If wsBOM.Cells(i, 1).Value <> "" Then
Me.lstProducts.AddItem wsBOM.Cells(i, 1).Value
End If
Next i
End Sub

3.3. 제품 선택

ListBox에서 선택한 제품 이름을 txtProductName에, 기본 수량을 txtQuantity에 입력합니다.

Private Sub btnSelectProduct_Click()
If Me.lstProducts.ListIndex = -1 Then
MsgBox "제품을 선택하세요.", vbExclamation
Exit Sub
End If

Me.txtProductName.Value = Me.lstProducts.Value
Me.txtQuantity.Value = 1 ' 기본값
End Sub

3.4. BOM 소요량 계산

입력된 제품과 수량을 기반으로 다단계 BOM을 계산하고 결과를 Sheet "Result"에 출력합니다.

Private Sub btnCalculate_Click()
Dim wsBOM As Worksheet, wsResult As Worksheet
Dim productName As String
Dim inputQuantity As Double
Dim lastRowBOM As Long
Dim resultRow As Long

Set wsBOM = ThisWorkbook.Sheets("BOM")
Set wsResult = ThisWorkbook.Sheets("Result")

productName = Me.txtProductName.Value
If IsNumeric(Me.txtQuantity.Value) Then
inputQuantity = CDbl(Me.txtQuantity.Value)
Else
MsgBox "수량은 숫자만 입력하세요.", vbExclamation
Exit Sub
End If

If productName = "" Or inputQuantity <= 0 Then
MsgBox "제품 이름과 유효한 수량을 입력하세요.", vbExclamation
Exit Sub
End If

wsResult.Cells(4, 1).Resize(wsResult.Rows.Count - 3, 2).ClearContents
wsResult.Cells(4, 1).Value = "공정"
wsResult.Cells(4, 2).Value = "필요 수량"
resultRow = 5

lastRowBOM = wsBOM.Cells(wsBOM.Rows.Count, 1).End(xlUp).Row

Call CalculateBOMRecursive(wsBOM, productName, inputQuantity, lastRowBOM, wsResult, resultRow)

MsgBox "소요량 계산이 완료되었습니다.", vbInformation
End Sub

3.5. 재귀 함수

하위 공정을 재귀적으로 계산합니다.

Private Sub CalculateBOMRecursive(wsBOM As Worksheet, currentProduct As String, requiredQuantity As Double, lastRowBOM As Long, wsResult As Worksheet, ByRef resultRow As Long)
Dim i As Long
Dim subProduct As String
Dim unitQuantity As Double
Dim subQuantity As Double

For i = 2 To lastRowBOM
If wsBOM.Cells(i, 1).Value = currentProduct Then
subProduct = wsBOM.Cells(i, 2).Value
unitQuantity = wsBOM.Cells(i, 3).Value
subQuantity = requiredQuantity * unitQuantity

wsResult.Cells(resultRow, 1).Value = subProduct
wsResult.Cells(resultRow, 2).Value = subQuantity
resultRow = resultRow + 1

Call CalculateBOMRecursive(wsBOM, subProduct, subQuantity, lastRowBOM, wsResult, resultRow)
End If
Next i
End Sub

3.6. UserForm 닫기

Private Sub btnClose_Click()
Unload Me
End Sub

4. UserForm 실행 매크로

UserForm을 실행하는 매크로를 작성합니다.

Sub ShowBOMForm()
BOMForm.Show
End Sub

실행 절차

Sheet "BOM"에 데이터를 입력합니다.
Alt + F8 → ShowBOMForm 실행.
UserForm에서:
- "조회" 버튼 클릭 → 제품 목록 로드.
- 제품 선택 → "선택" 버튼 클릭.
- 수량 입력 → "계산 실행" 버튼 클릭.
결과는 Sheet "Result"에 표시됩니다.