【VBA×ChatGPT】ピッキングリストをデータ分析に最適化！①
こんにちは。今日は、製造業の在庫管理に欠かせない「ピッキングリスト」の効率化についてお話しします。
製造業の在庫管理の仕事では、資材を選び出すためのピッキングリストが頻繁に使われますよね。
その一方で、来年や来季の在庫量を正確に予測するために、過去のデータを集計・分析する必要も出てきます。
たとえば、1年間で「どの製品が、どの型番の資材を、どの工程でどれだけ使ったか」を把握しなければなりません。
ただ、ピッキングリストの情報を一回一回持ってきて分析用の表に書き写したり、
フォーマットが複雑だと、データ分析が非常に手間に感じることもありますよね。
そこで今回は、VBAでピッキングリストを効率化して在庫管理業務を大幅に楽にする事例をご紹介します。
これを活用すれば、煩雑なデータの書き換え作業が自動化され、分析が驚くほど簡単になります。
どんな仕組みか、実際の例を使ってご説明していきますね。
まずは、何がどうなるのか、下記に図解します。
左側のピッキングリストのシート（エクセルブック①）のデータを読み込んで、
右側の集計シート（エクセルブック②）に分析しやすいデータとして取り込むVBAマクロを作成しました
コードは下記になります。
Sub 別ブックからデータを取得し集計()
Dim inputWb As Workbook ' データ取得元のワークブック
Dim inputWs As Worksheet ' データ取得元のシート
Dim outputWs As Worksheet ' 集計結果を出力するシート
Dim lastRow As Long ' 入力シートの最終行
Dim outputLastRow As Long ' 出力シートの最終行
Dim currentRow As Long ' 現在の行
Dim outputDict As Object ' データ集計用辞書
Dim typeNum As String ' 型番
Dim processName As String ' 工程（AAA, BBB, CCC）
Dim i As Integer ' 工程カラム用ループ
Dim todayDate As String ' 本日の日付
' 本日の日付
todayDate = Format(Date, "yyyy/mm/dd")
' 入力元ブックを取得（集計シート以外の開いているブックを対象）
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name <> ThisWorkbook.Name Then ' 集計シートのブック以外
Set inputWb = wb
Exit For
End If
Next wb
' 入力元ブックが見つからない場合
If inputWb Is Nothing Then
MsgBox "集計シート以外のブックを開いてください。", vbExclamation
Exit Sub
End If
' 入力元シートの設定（最初のシート）
Set inputWs = inputWb.Worksheets(1)
' 出力先シートの設定（集計シート）
Set outputWs = ThisWorkbook.Worksheets("集計")
' 入力シートの最終行取得
lastRow = inputWs.Cells(inputWs.Rows.Count, 3).End(xlUp).Row
' 出力シートの最終行取得
outputLastRow = outputWs.Cells(outputWs.Rows.Count, 1).End(xlUp).Row + 1
' 集計用辞書を初期化
Set outputDict = CreateObject("Scripting.Dictionary")
' データ処理
For currentRow = 2 To lastRow
' 型番を取得
If inputWs.Cells(currentRow, 2).value <> "" Then
typeNum = Trim(inputWs.Cells(currentRow, 2).value)
End If
' 各工程のデータを処理
For i = 1 To 3 ' AAA, BBB, CCC（4列目から6列目）
processName = inputWs.Cells(1, 3 + i).value ' ヘッダー行から工程名取得
If processName <> "" Then
Dim key As String
key = typeNum & "_" & processName
' 辞書にデータが存在する場合、合計を加算
If Not outputDict.exists(key) Then
outputDict.Add key, 0
End If
outputDict(key) = outputDict(key) + Nz(inputWs.Cells(currentRow, 3 + i).value, 0)
End If
Next i
Next currentRow
' 集計シートに出力
Dim dictKey As Variant
For Each dictKey In outputDict.Keys
Dim splitKey() As String
splitKey = Split(dictKey, "_") ' 型番と工程名を分離
' データを出力
outputWs.Cells(outputLastRow, 1).value = splitKey(0) ' 型番
outputWs.Cells(outputLastRow, 2).value = outputDict(dictKey) ' 払出数合計
outputWs.Cells(outputLastRow, 3).value = splitKey(1) ' 工程
outputWs.Cells(outputLastRow, 5).value = todayDate ' 本日の日付
outputLastRow = outputLastRow + 1
Next dictKey
MsgBox "データ集計が完了しました！", vbInformation
End Sub
' 空の値を 0 に置き換える関数
Function Nz(value As Variant, Optional defaultValue As Variant = 0) As Variant
If IsEmpty(value) Or IsNull(value) Or value = "" Then
Nz = defaultValue
Else
Nz = value
End If
End Function
ではChatGPTのプロンプトはどのように書いたらそれができるコードができたか下記で紹介します
少々荒っぽい書き方ですが、書いてきてくれました
既に開いている入力元ブック（集計シート以外の開いているブックを対象）
製品名 型番 製品Lot.No AAA BBB CCC
製品A 12345 00001 1 1
00002 2
製品B 67890 00003 1
00004 1 1
00005 1 1
製品C 987654 00006 5 1
00007 4 2
00008 3
00009 1 1
集計シート
型番 払出数合計 工程 備考 日付
12345 1 AAA 2024/12/30
12345 2 BBB 2024/12/30
12345 1 CCC 2024/12/30
67890 3 AAA 2024/12/30
67890 1 BBB 2024/12/30
67890 1 CCC 2024/12/30
987654 13 AAA 2024/12/30
987654 3 BBB 2024/12/30
987654 1 CCC 2024/12/30
既に開いている入力元ブックの表を解析して
集計シートのように表形式にシンプルな表に直して、
表に追記する形でVBAを作って
表項目としては、
型番
払出数合計（型番と工程（AAA,BBB、CCC)に対する合計値
工程：AAA,BBB、CCCなどが入る
備考の項目には何も書かなくていいです
日付には本日の日付を書いてください
例えば、型番:12345の1セルに対して2つの00001、00002のセルがあると思いますが
合計値をAAA、BBB、CCCごとに出すような表にしたい
既に開いている入力元ブックの表は集計シートとは別のワークブックです。
別ワークブックからデータを取得するという風にして
例えば、型番:987654の1セルに対して４つの00006、00007、00008、00009のセルが
あると思いますが合計値をAAA、BBB、CCCごとに出すような表にしたい
最終行の取得は製品Lot.Noの行にしてください
まとめると
別の開いているブック（現在のブック以外）からデータを取得し、
辞書（Scripting.Dictionary）を使用して型番と工程ごとにデータを集計してください。
型番と工程名をキーとして集計し、結果を「集計」という名前のシートに追記してください。
空白セルは 0 として扱うようにし、Nz関数を作成して使用してください。
集計結果には型番、払出数合計、工程、日付を含め、本日の日付を自動挿入してください。
処理完了時には完了メッセージを表示してください。
上記すべてをかなえられるVBAを書いて
以上です。
私自身も日々勉強しており、
ご質問などあれば、お気軽にコメントください！