VBAを使って商品の売上を自動で取得させるとガチ便利だった件

みなさんこんにちは。
北海道の某小売店で朝から働くおじさん（主任）です。
今回で3回目の投稿になります。前回はMakeとDify、そして生成AI（ChatGPT）を使って、売上応答Botを作りました。前回の記事はこちら。
LINEで「商品の売上個数」を聞ける！ノーコードで作る売上応答Bot【Make×Dify】

今回もまたデジタルツールを使って何か業務改善しようと思います。
ちょうどVBAに関して学ぶ機会があり、ChatGPTを使えば気軽にコードを書いてくれるとのことで、これを活用して何か作業を自動化するマクロを組んでみたいと思います。VBAに関してはまったく触ったことがない初心者ですが、頑張っていこー！

販売計画書の作成作業を効率化したい

私のお店では、1週間の中で特に売り込む曜日を決めています。その売り込む曜日に対して、販売計画書を作成し、上司に提出します。そして、その日が終わった後に売上や反省を入力し、その反省を生かしまた次週の販売計画書を作成し提出します。そして毎週売上改善に取り組む……いわゆるPDCAサイクルというものですね。

PDCAサイクルとは、「Plan（計画）→ Do（実行）→ Check（評価）→ Act（改善）」の4つのプロセスを繰り返すことで、業務やプロジェクトを継続的に改善していく手法です。製造業や品質管理の現場で広く使われてきましたが、近年ではあらゆるビジネスシーンや個人の目標管理にも応用されています。

販売計画書は下図のようなものになります。（※一部情報は黒塗りしています）

この販売計画書は、チラシに掲載されている商品ごとにそれぞれどのぐらい売るのかを記載します。例えば
りんご　258円　計画数量　200　計画金額　50（千）
バナナ　138円　計画数量　400　計画金額　55（千）
のような感じです。
そして、実際に販売してみた結果を赤枠の部分に入力します。その際に、チラシ商品の販売数量と金額を膨大なデータから調べて手入力しているわけですが、結構時間がかかる作業になります。ここをボタン一つで入力できるマクロを組みたいと思います。

※ちなみに、各チラシ商品の販売数量と金額を入力するフェーズは、PDCAサイクルで言うC（Check）に該当します。

完成品

作成開始

使用ツール

• VBA
• ChatGPT（VBAコードを書いてもらいます）

実装するマクロの流れ

Excelフォーマット作成

まず今回使用する販売計画書のExcelフォーマットを作成します。3つのシートで形成されます。

↑「計画書」シート

↑「商品名」シート

↑「単品実績」シート

商品に対してそれぞれJANコードが設定されています。JANコードとは、商品の識別に使用される13桁または8桁の数字になります。今回のマクロにはこのJANコードを利用していきます。

マクロの中身

今回作るマクロの中身としては、

1. 「商品名」シートの JANコード列（B列） をもとに、「単品実績」シートの JANコード（A列） を照合
2. 一致する行の販売数量（C列） と販売金額（D列） を「商品名」シートの販売数量（C列）、販売金額（D列） に転記
3. 販売金額は 千円単位で四捨五入（例：12,500 → 13）
   ※四捨五入する理由は、販売計画書の販売金額が入るセルが小さいため

このような流れになります。また、「商品名」シートのJANコードは、手入力する必要があります。 「単品実績」シートからそれぞれコピペで貼り付けました。「商品名」シートの品名も手入力ですが、計画書を作成した段階で必ず入力してある必要があるので、マクロ作成で効率が左右される作業ではありません。JANコードの手入力は、このマクロを使う上で必要な手動作業になります。

VBAコードを書いてもらおう

先ほど記載したマクロを実行するVBAコードを書いていきます。VBAに関してはまったくの初心者ですので、ChatGPTに質問して、VBAコードを書いてもらいます。ChatGPTに質問する際、使用するExcelファイルをアップロードすることで、そのファイルに対するVBAコードを書いてくれます。

先ほど作成した販売計画書のExcelフォーマットをChatGPTにアップロードしたうえで、質問しましょう。

質問した内容
VBAコードで、添付ファイルの商品名シートの指定されたJANコードの販売数量と販売金額を、単品実績シートの指定されたJANコードの販売点数と販売金額を抽出するマクロを組みたい。
販売金額は千円単位にしたい。（12500は、四捨五入して13）

結構ざっくりした質問をしてしまいましたが（記事を書きながら気づきました。日本語崩壊してますね笑）、それに対し返ってきたコードがこちら。

Sub 販売実績を抽出して転記()

    Dim ws商品名 As Worksheet
    Dim ws実績 As Worksheet
    Dim lastRow商品名 As Long
    Dim lastRow実績 As Long
    Dim janDict As Object
    Dim i As Long, jan As String
    Dim 販売数量 As Variant, 販売金額 As Variant

    Set ws商品名 = ThisWorkbook.Sheets("商品名")
    Set ws実績 = ThisWorkbook.Sheets("単品実績")
    Set janDict = CreateObject("Scripting.Dictionary")

    lastRow実績 = ws実績.Cells(ws実績.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow実績
        jan = Trim(ws実績.Cells(i, 1).Value)
        If jan <> "" Then
            販売数量 = ws実績.Cells(i, 3).Value
            販売金額 = ws実績.Cells(i, 4).Value
            janDict(jan) = Array(販売数量, 販売金額)
        End If
    Next i

    lastRow商品名 = ws商品名.Cells(ws商品名.Rows.Count, 2).End(xlUp).Row

    For i = 2 To lastRow商品名
        jan = Trim(ws商品名.Cells(i, 2).Value)
        If janDict.exists(jan) Then
            販売数量 = janDict(jan)(0)
            販売金額 = janDict(jan)(1)
            ws商品名.Cells(i, 3).Value = 販売数量
            ws商品名.Cells(i, 4).Value = WorksheetFunction.Round(販売金額 / 1000, 0)
        End If
    Next i

    MsgBox "販売実績を転記しました。", vbInformation

End Sub

すごいですね。知らなくてもこんなコード書いてくれるとは……これ自分が書いたことにして周りにドヤれるな。

Excelでマクロ実行

先ほどChatGPTに書いてもらったVBAコードを、Excel画面で貼り付けます。
どこに貼り付けるかというのも、ついでにChatGPTが返答してくれてました。

1. Excelで Alt + F11 を押して VBAエディタを開く
2. メニューから「挿入」→「標準モジュール」
3. 上記コードを貼り付ける

VBAコードを貼り付けた画面はこちら

そして、このマクロを実行するボタンを設置します。ボタンの設置方法もChatGPTに聞けば答えてくれます。今回は、「商品名」シートに、マクロ実行ボタンを設置します。

1. Excel画面に戻り、「商品名」シートを開く
2. リボンの「開発」タブをクリック
3. 「挿入」→「フォームコントロール」→「ボタン（フォームコントロール）」を選択
4. シート上にボタンをドラッグして配置
5. マクロの一覧が出るので、「販売実績を抽出して転記」を選択し「OK」

この手順でボタン設置しました。ボタンテキストも編集すれば見やすくなります。設置した図はこちら

これで設定は完了です。ボタンを押すだけで販売点数、販売金額が入力されます。

使ってみた感想

今回作ったものを、同僚の主任の方に使用してもらったところ、
「これ毎回数字拾うのめちゃめちゃ時間かかってたから助かるわ！」
「ボタン一つで勝手に入力されるの操作簡単でいいな！」
と好評ではありましたが、
「JANコードを自分で入力するのは時間かかるなーでもまぁ、販売数量と販売金額入力するよりはマシだわ」
という評価もありました。
確かに、自動化のために別の手入力作業が増えてしまっていました。JANコードの手入力は基本的にコピー＆ペーストで行えますが、その作業が苦手な人もいるかもしれません。JANコード抽出の自動化マクロを組むか……JANコードを利用しない他のパターンか……考えられることはたくさんありそうです。
同僚から使った結果のフィードバックをもらいましたが、フィードバックをもらうことによる気づきは重要だと感じました。
フィードバックにはネガティブな意見をもらうこともあると思いますが、よりよく改善できる機会が与えられたと前向きに捉えるようにしよう。やっぱりちょっと凹んじゃうけど

最後に

今回はVBAを利用してExcelマクロを組み、業務改善に取り組んでみました。今回行ったことはRPA（Robotic Process Automation） と呼ばれています。

RPAとは、人がパソコンでやっている定型業務を、ソフトウェアロボットで自動化する仕組みになります。

RPAツールは様々なものがあるのですが、社内システムには制約が多く、現状では利用が簡単とは言えません。そんな中VBAは社内システムの制約が受けづらく、ChatGPTを使えば簡単にコードを組むことができるので、誰でも簡単に活用できるRPAツールだと感じました。
社内のみんなにVBAの使い方と、生成AIを使ったコード作成の行い方を広めて、RPAを身近なものにしたいなーと思います。

ここまで見ていただき、ありがとうございました！