Excel VBAで在庫管理を自動化！簡易入出庫システムをゼロから作る方法

Excel・VBA

はじめに：Excel VBAで在庫管理を効率化しよう

「日々の在庫管理を手作業で行っていて、時間ばかりかかってしまう…」「Excelで管理しているけれど、もっと効率的に入出庫を記録したい」

そんなお悩みをお持ちの方へ。本記事では、Excel VBAを使って誰でも簡単に、かつ現場で今すぐ使える「簡易在庫・入出庫管理システム」をゼロから構築する方法を解説します。複雑なプログラミング知識は不要です。基本的なExcel操作とVBAの概念だけで、業務効率を劇的に改善できるシステムを作り上げましょう。

システム概要：必要なシート構成と機能

今回作成する簡易システムは、以下の3つのシートとVBAマクロで構成されます。

  • 「操作」シート： 入出庫データの入力とマクロ実行ボタンを配置するインターフェース
  • 「在庫マスタ」シート： 商品情報（商品ID、商品名、初期在庫など）を管理
  • 「入出庫履歴」シート： すべての入庫・出庫履歴を時系列で記録
  • 「在庫一覧」シート： 現在の在庫状況を自動で集計・表示

主な機能は以下の通りです。

  • 「操作」シートからの入出庫データの簡単登録
  • 登録された履歴に基づくリアルタイムな在庫状況の表示
  • データの整合性チェック（例：出庫時の在庫不足防止）

ステップ1：シートの準備と基本レイアウト

まずは、必要なシートを作成し、各シートのヘッダーを設定します。

1.1. 「操作」シートの作成

入出庫データを入力し、マクロを実行するためのシートです。

以下の項目をA列に配置し、B列に入力セル、C列にボタンを配置します。

  • A1: 商品ID
  • A2: 数量
  • A3: 区分（入庫/出庫）
  • A4: 備考

B列の対応するセル（B1, B2, B3, B4）を入力用セルとします。

C列には後ほど「入庫実行」「出庫実行」「在庫一覧更新」のボタンを配置します。

1.2. 「在庫マスタ」シートの作成

商品の基本情報を管理します。

1行目に以下のヘッダーを入力してください。

A1: 商品ID
B1: 商品名
C1: 単位
D1: 初期在庫数

初期データとして、いくつか商品を登録しておきましょう。

1.3. 「入出庫履歴」シートの作成

すべての入庫・出庫の記録を時系列で保存します。

1行目に以下のヘッダーを入力してください。

A1: 記録日時
B1: 商品ID
C1: 区分
D1: 数量
E1: 備考

1.4. 「在庫一覧」シートの作成

現在の在庫状況を集計して表示します。このシートはVBAで内容を更新します。

1行目に以下のヘッダーを入力してください。

A1: 商品ID
B1: 商品名
C1: 現在庫数

ステップ2：VBAコードの記述

いよいよVBAコードを記述していきます。Excelの「開発」タブから「Visual Basic」を開き（Alt + F11）、左側のプロジェクトエクスプローラーで「VBAProject (ファイル名)」を右クリックし、「挿入」→「標準モジュール」を選択して、以下のコードを貼り付けてください。

2.1. 定数と共通処理の定義

シート名や入力セルのアドレスを定数として定義しておくと、後から変更しやすくなります。

Option Explicit

' 各シート名
Const SHEET_OPERATION As String = "操作"
Const SHEET_MASTER As String = "在庫マスタ"
Const SHEET_HISTORY As String = "入出庫履歴"
Const SHEET_INVENTORY As String = "在庫一覧"

' 操作シートの入力セルアドレス
Const CELL_PRODUCT_ID As String = "B1"
Const CELL_QUANTITY As String = "B2"
Const CELL_TYPE As String = "B3"
Const CELL_NOTE As String = "B4"

' 商品IDから商品名を取得する関数
Function GetProductName(ByVal productId As String) As String
    Dim wsMaster As Worksheet
    Dim foundRange As Range
    
    Set wsMaster = ThisWorkbook.Sheets(SHEET_MASTER)
    
    Set foundRange = wsMaster.Columns("A").Find(
        What:=productId,
        LookIn:=xlValues,
        LookAt:=xlWhole,
        SearchOrder:=xlByRows,
        SearchDirection:=xlNext,
        MatchCase:=False
    )
    
    If Not foundRange Is Nothing Then
        GetProductName = wsMaster.Cells(foundRange.Row, "B").Value ' B列が商品名
    Else
        GetProductName = ""
    End If
End Function

' 現在の在庫数を取得する関数
Function GetCurrentStock(ByVal productId As String) As Long
    Dim wsMaster As Worksheet
    Dim wsHistory As Worksheet
    Dim lastRowHistory As Long
    Dim initialStock As Long
    Dim stockIn As Long
    Dim stockOut As Long
    Dim i As Long
    
    Set wsMaster = ThisWorkbook.Sheets(SHEET_MASTER)
    Set wsHistory = ThisWorkbook.Sheets(SHEET_HISTORY)
    
    ' 在庫マスタから初期在庫を取得
    Dim foundRow As Variant
    On Error Resume Next
    foundRow = Application.Match(productId, wsMaster.Columns("A"), 0)
    On Error GoTo 0
    
    If IsError(foundRow) Then
        GetCurrentStock = -1 ' 商品が見つからない場合
        Exit Function
    End If
    
    initialStock = wsMaster.Cells(foundRow, "D").Value ' D列が初期在庫数
    stockIn = 0
    stockOut = 0
    
    ' 入出庫履歴から集計
    lastRowHistory = wsHistory.Cells(Rows.Count, "A").End(xlUp).Row
    If lastRowHistory > 1 Then ' ヘッダー行を除く
        For i = 2 To lastRowHistory
            If wsHistory.Cells(i, "B").Value = productId Then ' B列が商品ID
                If wsHistory.Cells(i, "C").Value = "入庫" Then ' C列が区分
                    stockIn = stockIn + wsHistory.Cells(i, "D").Value ' D列が数量
                ElseIf wsHistory.Cells(i, "C").Value = "出庫" Then
                    stockOut = stockOut + wsHistory.Cells(i, "D").Value
                End If
            End If
        Next i
    End If
    
    GetCurrentStock = initialStock + stockIn - stockOut
End Function

2.2. 入庫・出庫処理のマクロ

「操作」シートからデータを読み込み、「入出庫履歴」シートに記録します。

Sub RegisterInOutbound(ByVal transactionType As String)
    Dim wsOperation As Worksheet
    Dim wsHistory As Worksheet
    Dim productId As String
    Dim quantity As Long
    Dim note As String
    Dim lastRowHistory As Long
    Dim currentStock As Long
    
    Set wsOperation = ThisWorkbook.Sheets(SHEET_OPERATION)
    Set wsHistory = ThisWorkbook.Sheets(SHEET_HISTORY)
    
    ' 入力値の取得
    productId = Trim(wsOperation.Range(CELL_PRODUCT_ID).Value)
    quantity = wsOperation.Range(CELL_QUANTITY).Value
    note = Trim(wsOperation.Range(CELL_NOTE).Value)
    
    ' 入力チェック
    If productId = "" Then
        MsgBox "商品IDが入力されていません。", vbCritical
        Exit Sub
    End If
    If Not GetProductName(productId) <> "" Then
        MsgBox "入力された商品IDは存在しません。在庫マスタを確認してください。", vbCritical
        Exit Sub
    End If
    If quantity <= 0 Then
        MsgBox "数量は正の値を入力してください。", vbCritical
        Exit Sub
    End If
    
    ' 出庫時の在庫チェック
    If transactionType = "出庫" Then
        currentStock = GetCurrentStock(productId)
        If currentStock = -1 Then ' 商品IDが見つからない場合
            MsgBox "商品ID " & productId & " は存在しません。", vbCritical
            Exit Sub
        ElseIf currentStock < quantity Then
            MsgBox "現在の在庫数 (" & currentStock & ") が不足しています。出庫できません。", vbCritical
            Exit Sub
        End If
    End If
    
    ' 履歴シートの最終行を取得し、新しいデータを追加
    lastRowHistory = wsHistory.Cells(Rows.Count, "A").End(xlUp).Row + 1
    With wsHistory
        .Cells(lastRowHistory, "A").Value = Now ' 記録日時
        .Cells(lastRowHistory, "B").Value = productId
        .Cells(lastRowHistory, "C").Value = transactionType
        .Cells(lastRowHistory, "D").Value = quantity
        .Cells(lastRowHistory, "E").Value = note
    End With
    
    MsgBox transactionType & "処理が完了しました。", vbInformation
    
    ' 入力欄をクリア
    wsOperation.Range(CELL_PRODUCT_ID).ClearContents
    wsOperation.Range(CELL_QUANTITY).ClearContents
    wsOperation.Range(CELL_NOTE).ClearContents
    
    ' 在庫一覧を更新
    Call UpdateInventorySummary
End Sub

Sub InputStock()
    Call RegisterInOutbound("入庫")
End Sub

Sub OutputStock()
    Call RegisterInOutbound("出庫")
End Sub

2.3. 在庫一覧更新マクロ

「在庫マスタ」と「入出庫履歴」シートのデータに基づき、「在庫一覧」シートを再生成します。

Sub UpdateInventorySummary()
    Dim wsMaster As Worksheet
    Dim wsInventory As Worksheet
    Dim lastRowMaster As Long
    Dim i As Long
    Dim productId As String
    Dim productName As String
    Dim currentStock As Long
    Dim inventoryRow As Long
    
    Set wsMaster = ThisWorkbook.Sheets(SHEET_MASTER)
    Set wsInventory = ThisWorkbook.Sheets(SHEET_INVENTORY)
    
    ' 在庫一覧シートをクリア（ヘッダーを除く）
    With wsInventory
        .UsedRange.Offset(1, 0).ClearContents
        .Range("A:C").ClearContents ' セル書式が残らないように完全にクリア
        .Cells(1, "A").Value = "商品ID"
        .Cells(1, "B").Value = "商品名"
        .Cells(1, "C").Value = "現在庫数"
    End With
    inventoryRow = 2 ' 在庫一覧の書き込み開始行
    
    ' 在庫マスタから各商品の在庫数を計算し、在庫一覧シートに書き込む
    lastRowMaster = wsMaster.Cells(Rows.Count, "A").End(xlUp).Row
    If lastRowMaster > 1 Then
        For i = 2 To lastRowMaster
            productId = wsMaster.Cells(i, "A").Value
            productName = wsMaster.Cells(i, "B").Value
            currentStock = GetCurrentStock(productId)
            
            wsInventory.Cells(inventoryRow, "A").Value = productId
            wsInventory.Cells(inventoryRow, "B").Value = productName
            wsInventory.Cells(inventoryRow, "C").Value = currentStock
            inventoryRow = inventoryRow + 1
        Next i
    End If
    
    MsgBox "在庫一覧が更新されました。", vbInformation
End Sub

ステップ3：ボタンの配置とマクロの割り当て

「操作」シートにボタンを配置し、先ほど作成したマクロを割り当てます。

  1. 「開発」タブ → 「挿入」 → 「フォームコントロール」の「ボタン」を選択します。
  2. 「操作」シートのC1セル付近にボタンをドラッグして作成します。
  3. ボタンを配置すると「マクロの登録」ダイアログが表示されるので、1つ目のボタンには「InputStock」を選択し、「OK」をクリックします。
  4. 作成したボタンを右クリックし、「テキストの編集」でボタン名を「入庫実行」に変更します。
  5. 同様に、C2セル付近に「出庫実行」ボタンを作成し、「OutputStock」マクロを割り当てます。
  6. C3セル付近に「在庫一覧更新」ボタンを作成し、「UpdateInventorySummary」マクロを割り当てます。

システムの利用方法

作成した簡易在庫管理システムの基本的な使い方は以下の通りです。

  1. 商品マスタの登録： 「在庫マスタ」シートに商品ID、商品名、初期在庫数を入力します。
  2. 入出庫の記録：
    1. 「操作」シートの「商品ID」セル（B1）に入出庫する商品のIDを入力します。
    2. 「数量」セル（B2）に入出庫する数量を入力します。
    3. 「備考」セル（B4）に必要に応じてメモを入力します。
    4. 「入庫実行」または「出庫実行」ボタンをクリックします。
  3. 在庫状況の確認： 「在庫一覧」シートを開くと、現在の在庫数が表示されます。もし最新情報ではない場合は、「操作」シートの「在庫一覧更新」ボタンをクリックして更新してください。

拡張性と今後の改善点

この簡易システムはあくまで基本形です。必要に応じて以下の機能を追加することで、より実用的なシステムに拡張できます。

  • ユーザーフォームの導入： より使いやすい入力インターフェースを提供できます。
  • データ入力規則の強化： ドロップダウンリストの活用や入力値の自動補完など。
  • 在庫不足アラート： 特定の商品が閾値を下回った際に警告を表示する機能。
  • 棚卸機能： 実際の在庫数とシステムの在庫数を比較し、調整する機能。
  • レポート機能： 月次・年次の入出庫レポートや在庫推移グラフなど。
  • 排他制御： 複数人で同時に操作する際のデータ競合防止（大規模システム向け）。

まとめ

本記事では、Excel VBAを使って簡易的ながらも実用的な在庫・入出庫管理システムを構築する方法を解説しました。

今回作成したシステムは、日々の業務で発生する在庫管理の手間を大幅に削減し、ミスの軽減にも繋がります。ぜひこのコードをベースに、ご自身の業務に合わせてカスタマイズしてみてください。Excel VBAは、アイデア次第で様々な業務を自動化できる強力なツールです。一歩ずつ学習を進め、業務効率化の達人を目指しましょう！

コメント

タイトルとURLをコピーしました