ミーネクスト
読み込み中...

エクセルのマクロ作成をAIで|Copilotが在庫管理表の更新と発注の印付けまで

1週間前00.0☆☆☆☆☆所要時間 10分

エクセルのマクロ作成をAIで、在庫管理表をMicrosoft 365 Copilotに渡してVBAを作ってもらう方法です。 入出庫の記録から毎月手で在庫を計算しなくても、発注が要る品に印と色が付きます。

材料(必要なもの)

Microsoft 365 Copilot今回は会社のアカウントの Microsoft 365 Copilot Premium で確認。無料のCopilotで同じことができるかは確かめていません
デスクトップ版のExcelWindows
見本架空の設備資材の会社の在庫一覧24品・11月の入出庫82件。リンクを知っている人は閲覧可):
在庫管理のExcel・マクロ入りのExcel・頼む文・Copilotが出したマクロのフォルダhttps://drive.google.com/drive/folders/1wLUxEfY7j5ENQjQMO-qWK_N_xLGdDJjb

手順

1

Copilotアプリで在庫のExcelを添付する

設備資材を扱う会社の在庫担当者が、毎月の在庫の計算と発注の洗い出しを手作業でやめるための手順です。 WindowsのMicrosoft 365 Copilotアプリ(ブラウザなら https://m365.cloud.microsoft/chat )で新しいチャットを開き、入力欄の左の「+」→「画像とファイルのアップロード」で、在庫一覧と入出庫記録のシートが入ったExcelを添付します。

💡
テクニック

https://m365.cloud.microsoft/chat /見本の「在庫管理_11月.xlsx」(在庫一覧24品・入出庫記録82件)は準備物のリンクから取れます。パソコンから添付すると、ファイルは会社のOneDriveにコピーされると画面に出ます。

2

Copilotにエクセルのマクロ作成を頼む

下の頼む文を貼り付けて送ります。シートの名前(「在庫一覧」「入出庫記録」)と列の名前は、ご自身のExcelに合わせて書き換えてください。

プロンプト例
添付のExcelの在庫を、実行するだけで更新するマクロ(VBA)を作ってください。 ・「入出庫記録」シートを品番ごとに集計し、「在庫一覧」の「現在の在庫」に「10月末の在庫+入庫-出庫」を書く ・現在の在庫が発注点以下の品は、「状態」に「発注」と書き、その行をうすい赤にする ・「在庫一覧」に無い品番が入出庫記録にあれば、新しいシート「要確認」に書き出す ・何度実行しても同じ結果になるようにする(前の色と「要確認」は消してから作り直す) 標準モジュールにそのまま貼れるコードを1つだけ出してください。
💡
テクニック

「何度実行しても同じ結果になるように」を入れておくと、2回目に実行した時に色や「要確認」シートが二重にならないコードが出ます。撮影した回は、送ってから約7秒でコードが出ました。

3

出てきたコードを「コピー」ボタンでコピーする

答えのコードの枠の右上にある「コピー」ボタンを押すと、コード全体がコピーされます。画面を手でなぞって選ぶより確実です。

💡
テクニック

初めてコピーする時は「読み取りを許可しますか?」と聞かれることがあります。その時は「はい」を押します(撮影した回は、前に許可していたので出ませんでした)。コード枠の上に「vba は完全にはサポートされていません」と出ますが、表示の色分けの話なので気にしなくて大丈夫です。

4

ExcelのVBAの画面で標準モジュールに貼り付ける

在庫のExcelを開いて Alt+F11 を押すと、VBAの画面が開きます。上のメニューの「挿入」→「標準モジュール」を選び、出てきた白い画面に Ctrl+V で貼り付けて、VBAの画面を閉じます。 下は、撮影した回にCopilotが出したコードの全文です(見本のExcel用)。

プロンプト例
Option Explicit Sub 在庫更新() Dim wb As Workbook Dim wsStock As Worksheet Dim wsLog As Worksheet Dim wsCheck As Worksheet Dim dicIn As Object Dim dicOut As Object Dim dicStock As Object Dim lastStockRow As Long Dim lastLogRow As Long Dim i As Long Dim checkRow As Long Dim itemCode As String Dim category As String Dim quantity As Double Dim currentStock As Double Dim orderPoint As Double Dim key As Variant On Error GoTo ErrorHandler Application.ScreenUpdating = False Application.EnableEvents = False Set wb = ThisWorkbook Set wsStock = wb.Worksheets("在庫一覧") Set wsLog = wb.Worksheets("入出庫記録") Set dicIn = CreateObject("Scripting.Dictionary") Set dicOut = CreateObject("Scripting.Dictionary") Set dicStock = CreateObject("Scripting.Dictionary") dicIn.CompareMode = vbTextCompare dicOut.CompareMode = vbTextCompare dicStock.CompareMode = vbTextCompare lastStockRow = wsStock.Cells(wsStock.Rows.Count, "A").End(xlUp).Row lastLogRow = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row '前回の計算結果、状態、色を消去 If lastStockRow >= 2 Then wsStock.Range("F2:G" & lastStockRow).ClearContents wsStock.Range("A2:G" & lastStockRow).Interior.Pattern = xlNone End If '前回の「要確認」シートを削除 Application.DisplayAlerts = False On Error Resume Next wb.Worksheets("要確認").Delete On Error GoTo ErrorHandler Application.DisplayAlerts = True '在庫一覧の品番を辞書へ登録 For i = 2 To lastStockRow itemCode = Trim$(CStr(wsStock.Cells(i, "A").Value)) If itemCode <> "" Then If Not dicStock.Exists(itemCode) Then dicStock.Add itemCode, i End If End If Next i '入出庫記録を品番ごとに集計 For i = 2 To lastLogRow itemCode = Trim$(CStr(wsLog.Cells(i, "B").Value)) category = Trim$(CStr(wsLog.Cells(i, "D").Value)) If IsNumeric(wsLog.Cells(i, "E").Value) Then quantity = CDbl(wsLog.Cells(i, "E").Value) Else quantity = 0 End If If itemCode <> "" Then If category = "入庫" Then If dicIn.Exists(itemCode) Then dicIn(itemCode) = dicIn(itemCode) + quantity Else dicIn.Add itemCode, quantity End If ElseIf category = "出庫" Then If dicOut.Exists(itemCode) Then dicOut(itemCode) = dicOut(itemCode) + quantity Else dicOut.Add itemCode, quantity End If End If End If Next i '現在の在庫と状態を更新 For Each key In dicStock.Keys i = CLng(dicStock(key)) currentStock = 0 orderPoint = 0 If IsNumeric(wsStock.Cells(i, "D").Value) Then currentStock = CDbl(wsStock.Cells(i, "D").Value) End If If dicIn.Exists(CStr(key)) Then currentStock = currentStock + CDbl(dicIn(CStr(key))) End If If dicOut.Exists(CStr(key)) Then currentStock = currentStock - CDbl(dicOut(CStr(key))) End If wsStock.Cells(i, "F").Value = currentStock If IsNumeric(wsStock.Cells(i, "E").Value) Then orderPoint = CDbl(wsStock.Cells(i, "E").Value) If currentStock <= orderPoint Then wsStock.Cells(i, "G").Value = "発注" wsStock.Range("A" & i & ":G" & i).Interior.Color = RGB(255, 199, 206) End If End If Next key '新しい「要確認」シートを作成 Set wsCheck = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)) wsCheck.Name = "要確認" wsCheck.Range("A1:G1").Value = Array( _ "入出庫記録の行番号", "日付", "品番", "品名", "区分", "数量", "担当") With wsCheck.Range("A1:G1") .Font.Bold = True .Font.Color = RGB(255, 255, 255) .Interior.Color = RGB(31, 56, 100) .HorizontalAlignment = xlCenter End With checkRow = 2 '在庫一覧にない品番を要確認へ書き出す For i = 2 To lastLogRow itemCode = Trim$(CStr(wsLog.Cells(i, "B").Value)) If itemCode <> "" Then If Not dicStock.Exists(itemCode) Then wsCheck.Cells(checkRow, "A").Value = i wsCheck.Cells(checkRow, "B").Value = wsLog.Cells(i, "A").Value wsCheck.Cells(checkRow, "C").Value = wsLog.Cells(i, "B").Value wsCheck.Cells(checkRow, "D").Value = wsLog.Cells(i, "C").Value wsCheck.Cells(checkRow, "E").Value = wsLog.Cells(i, "D").Value wsCheck.Cells(checkRow, "F").Value = wsLog.Cells(i, "E").Value wsCheck.Cells(checkRow, "G").Value = wsLog.Cells(i, "F").Value checkRow = checkRow + 1 End If End If Next i wsCheck.Columns("A:G").AutoFit wsCheck.Columns("B").NumberFormatLocal = "m/d" If checkRow = 2 Then wsCheck.Cells(2, "A").Value = "在庫一覧にない品番はありません。" wsCheck.Range("A2:G2").Merge End If Application.ScreenUpdating = True Application.EnableEvents = True MsgBox "在庫の更新が完了しました。", vbInformation Exit Sub ErrorHandler: Application.DisplayAlerts = True Application.ScreenUpdating = True Application.EnableEvents = True MsgBox "在庫を更新できませんでした。" & vbCrLf & _ "エラー内容:" & Err.Description, vbExclamation End Sub
💡
テクニック

「開発」タブを出さなくても、Alt+F11 で開けます。同じコードを2回貼ると動かなくなるので、標準モジュールは1つだけにしてください。

5

「表示」→「マクロ」→「マクロの表示」から実行する

Excelの「表示」タブの右端の「マクロ」→「マクロの表示」を押し、「在庫更新」を選んで「実行」を押します。「在庫の更新が完了しました。」と出たら「OK」を押します。

💡
テクニック

毎月使うなら、シートにボタンを置いてこのマクロを割り当てることもできます(今回の動画ではボタンは付けていません)。

6

発注の印と要確認を確かめてマクロ有効ブックで保存

在庫一覧の「現在の在庫」に「10月末の在庫+入庫-出庫」が入り、発注点以下の品に「発注」と薄い赤が付きます。見本では24品のうち A-103・B-102・C-201・D-102 の4品が発注になりました。在庫一覧に無い品番は「要確認」シートに出ます(見本では B-202 のゼロが英字のオーになった「B-2O2」の1件)。最後に F12 で「Excel マクロ有効ブック(.xlsm)」を選んで保存します。

💡
テクニック

見本では、24品の現在の在庫と発注の4品を、元のデータから計算した値と突き合わせて、すべて一致しました。それでも最初の月は、何品かを手の計算と見比べてください。ダウンロードした .xlsm はマクロがブロックされて動かないことがあります。その時はファイルを右クリック→「プロパティ」→下の「許可する」にチェック→「OK」を押してから開いてください。

関連レシピ

みねすけ

この内容を、体験会や研修でもっと深く学んでみませんか?

株式会社WEAVEの体験会(120分)や生成AI研修(12時間・助成金対象)で、実践スキルを体系的に身につけられます。会社に推薦すると紹介特典5万円もあります。

会社に研修をリクエストする

人気のレシピ

手書きの図面をAIでCAD化|Claudeが写真1枚からDXFと要確認の一覧1

手書きの図面をAIでCAD化|Claudeが写真1枚からDXFと要確認の一覧

183回視聴
Gemini Notebookの始め方|渡した情報のみから回答するAI2

Gemini Notebookの始め方|渡した情報のみから回答するAI

778回視聴
ChatGPTで作った画像をCanvaで編集する方法|マジックレイヤーで文字も直せる3

ChatGPTで作った画像をCanvaで編集する方法|マジックレイヤーで文字も直せる

2,382回視聴
図面から数量をChatGPTで拾う|外壁・足場・屋根の面積を計算式つきで4

図面から数量をChatGPTで拾う|外壁・足場・屋根の面積を計算式つきで

296回視聴
Gladiaの文字起こしの始め方|日本語設定と話者ごとの識別まで5

Gladiaの文字起こしの始め方|日本語設定と話者ごとの識別まで

906回視聴

コメント

まだコメントはありません。最初のコメントを投稿してみましょう!

次におすすめ

手書きの図面をAIでCAD化|Claudeが写真1枚からDXFと要確認の一覧

手書きの図面をAIでCAD化|Claudeが写真1枚からDXFと要確認の一覧

建設・不動産初級