Excel+Access로 생산관리시스템 구축하기 (1)

한줄 요약

부서마다 따로 놀던 Excel 파일들을 하나의 흐름으로 연결하고, VBA 755줄짜리 자동화시스템을 구축해봤습니다.

이런 분들께 도움돼요

중소 제조업체에서 ERP 없이 Excel로 생산관리 하시는 분
부서 간 데이터 연결이 안 돼서 매번 수작업 집계하시는 분
AI로 사내 시스템을 만들고 싶은데 어디서 시작해야 할지 모르는 분
"AI가 코드를 짜준다고? 나도 할 수 있을까?" 반신반의하시는 분

소개: 시도하고자 했던 것과 그 이유

저희 회사는 의료기기를 제조하는 곳입니다. 자재부, 생산관리부, 가공팀(CNC/MCT), 출고팀, 제품출하까지 5개 부서가 하나의 생산 흐름으로 연결되어 있는데, 각 부서가 쓰는 파일은 제각각이었습니다.

실제 겪었던 문제들:

자재부에서 생산의뢰를 입력하면, 생산관리부는 그걸 다시 자기 파일에 옮겨 적습니다. 가공팀은 또 다른 파일에 작업 내용을 기록하고, 출고팀은 또 따로 일지를 씁니다. 같은 제품 정보가 부서마다 5번 입력되는 구조였죠.

월말이 되면 더 심각했습니다. 각 부서 파일을 다 모아서 VLOOKUP으로 짜맞추는 집계 작업에 하루 이상이 걸렸고, 데이터가 서로 달라 회의에서 혼선이 생기는 일도 있었습니다. 담당자가 바뀌면 인수인계가 제대로 안 되는 문제도 반복됐고요.

진행 방법: 어떻게 AI와 협업했나요?

도구: Claude (Excel 애드인), Microsoft Excel, Access DB

먼저 기존에 쓰던 Excel 파일(Database기획.xlsx)을 Claude에게 보여주며 업무 흐름을 설명했습니다. 8개 문서 양식, 5개 부서, 각 양식의 컬럼 구조를 분석하게 했죠.

"오, 이거 진짜 되네!" 했던 순간

8개 부서 양식을 설계해달라고 했더니, Claude가 부서 간 업무 흐름을 스스로 파악해서 이전 부서에서 넘어온 컬럼은 회색 read-only로, 현재 부서 입력 컬럼은 흰색으로 자동 구분했습니다. 제가 별도로 지시하지 않은 부분이었는데, 업무 맥락을 이해하고 알아서 챙겨준 게 인상적이었습니다.

Claude가 생성한 VBA 코드 구조는 이렇습니다. modCommon이라는 공통 모듈에 DB 연결을 한곳에 모아두고, 각 시트 모듈에서 호출하는 방식입니다:

 modCommon — ADO DB 연결 공통 모듈
Function GetConnection() As Object
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    Dim dbPath As String
    dbPath = "C:\Dropbox\생산관리\생산관리DB.accdb"  ' Dropbox 공유 경로
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"
    Set GetConnection = conn
End Function

 각 시트 모듈 (예: 가공팀-납품내역서)
Sub 검색_납품내역()
    Dim conn As Object
    Set conn = GetConnection()
    Dim sql As String
    sql = "SELECT * FROM tbl_작업지시 WHERE 가공팀='" & Range("D4").Value & "'" & _
          " AND 진행완료 BETWEEN #" & Range("F4").Value & "# AND #" & Range("H4").Value & "#"
    Call FillSheet(conn, sql, ActiveSheet, 9)
    conn.Close
End Sub

진행하면서 곤란했던 부분

버튼 구성을 결정할 때 어려움이 있었습니다. 어떤 시트는 검색/등록/수정/삭제 4개가 다 있어야 하고, 어떤 시트는 수정만 가능해야 하는 구조였는데, 이걸 Claude에게 한 번에 설명하기가 쉽지 않았습니다. 세부 UI를 제가 직접 판단해서 다시 지시해야 하는 상황이 반복됐습니다.

여기서 중요한 걸 배웠습니다. AI와 협업 설계를 잘 하려면 약간의 고유 지식이 있어야 합니다. 단순히 "만들어줘"로는 한계가 있습니다. 내 업무를 어느 정도 구조화해서 설명할 수 있어야, Claude도 제대로 된 결과를 만들어줍니다.

설계 문서를 먼저 만든 것이 결정적이었습니다

추가 기능 5종을 추가할 때는 먼저 Claude와 함께 추가기능설계 시트를 만들었습니다. 특히 월별 소요자재 집계 기능의 경우, BOM 전개 로직을 설계 문서에 먼저 정리했습니다:

[설계 문서에서 Claude와 정리한 BOM 전개 흐름]

월별 소요자재 집계 — VBA 처리 순서:

① tbl_생산의뢰에서 해당 월 레코드 GROUP BY 대표품번
   → 제품별 의뢰수량 합계 산출

② 각 제품에 대해 ExpandBOM(대표품번) 호출
   → tbl_BOM 재귀 전개 (제품 → 반제품 → 원자재)
   → 각 단계 소요량 곱셈 누적

③ 최하위 원자재별 소요량 합산
   유효소요량 = 소요량 × (1 + 손실율/100)
   총소요량  = 단위소요량 × 총의뢰수량

예시:
  IMPLANT Ø4.0 100EA 의뢰
  → Ti Bar Ø4.0 : 1.2EA × 1.05(손실) × 100 = 126EA 필요

컬럼 구조, 데이터 소스, VBA 로직을 모두 정리한 뒤에 구현에 들어갔더니, 그 이전보다 훨씬 빠르고 오류도 적었습니다.

결과와 배운 점

결과물:

8개 업무 양식 시트 + 6개 집계/현황/출력 시트 = 총 14개 시트
VBA 코드 755줄, 16개 모듈 (부서별 CRUD + 집계 리포트 + 출하확인서 출력)
Access DB + Dropbox 공유로 6명 동시 사용 가능한 구조 설계 완료

배운 점:

업무 흐름을 먼저 설명해야 합니다. "양식 만들어줘"가 아니라 "자재부가 생산의뢰를 하면, 생산관리부가 이걸 받아서..." 식으로 맥락을 먼저 전달해야 제대로 된 결과가 나옵니다.
설계 → 검토 → 구현 순서가 핵심입니다. Claude와 함께 설계 문서를 먼저 만들고 확인하는 단계가 결국 더 빠릅니다.
딸깍 만으론 안 됩니다. 어느 정도의 고유 지식과 판단이 있어야 Claude를 제대로 활용할 수 있습니다.

AI의 파도, 함께하면 이뤄낼 수 있어요! 22기 스터디 사전판매 시작 🚀

Excel+Access로 생산관리시스템 구축하기 (1) - Excel VBA