こんにちは。
小売業の商品部で働くふひーです。
私は会社で働く際、作業の多くの時間を「Excel」の使用に費やしています。
しかし、Excelを頻繁に使っているにもかかわらず、知識不足のため活用できていない機能が多いのも事実です。
データ量が増えたり、定型作業が多くなるほど、Excelの機能を活用することはますます重要になります。
そこで今回は、短時間で知識不足を補い、作業時間を短縮するために、ChatGPTの力を借りることにしました。
作業の背景・今回行ったこと
小売業では、商品を販売する際、単に陳列するだけでなく、
お客さまに試食していただいたり、実際に使用していただいたりする取り組みも行っています。
お客さまにサンプルとして配布した商品については、
毎月FORMSを通じて、配布数や売価変更(売変)の有無などを
各店舗から報告を受けています。
各店から送られてきたFORMSの回答は、
- 取りまとめた上で、指定の書式に置き換える
- 未報告の店舗をリスト化し、催促連絡をする など、
毎回行っているこの作業をどのようにすれば、
より簡単に効率よく進めるかChatGPTに相談してみました。
ChatGPTの提案はいろいろありました。
色んな提案の中から、「毎回同じ帳票フォーマットに変える・定期的にデータを更新して使う」に向いていると、おすすめされた「Power Query」に挑戦！
そして、まとまった内容から「未報告店舗」を「マクロ」で抽出してみました。
完成したもの
毎月、「指定のフォルダ」にファイルを追加するだけで、
集計用ファイルに自動で更新される仕組みです。
併せて、マクロを実行して未報告店舗も抽出・更新する流れになっております。
使用したツール(機能)
・ChatGPT
・Excel
-Power Query & マクロ
📚作成の流れ
まずは、ChatGPTを活用し、作りたいイメージを伝え質問します。
回答が戻ったら、順番に処理します。
＜Power Queryで作成する方法＞
■ 初回のセットアップ(最初の一度だけ)
自動でファイルの更新を行うためのセットアップします。
- 毎月のデータファイルを追加していく、
専用フォルダを準備 - 新しいExcelで「集計用」のブックを作成
- 「集計用」ブックで［
データ］→［データの取得］→［ファイルから］→［フォルダーから］→ １のフォルダを指定 - 選択画面が現れたら［
結合および読み込み］を選択 - 結合画面が出たら、Sheetを選択し、
Power Queryを起動して「編集」
■ 指定したフォーマットに置き換えるための各種条件設定
[1]
[2]
[1]のFormsデータを、[2]のフォーマットに変更を依頼しました。
【適用したステップ】は、下記映像です。
➀必要な情報(店舗名・配布期間・配布数・売変有無)以外は削除
②売変処理完了→１、していない→０
③元の「売変したか」列の削除
④「配布数合計」と「売変」列を選択後、ピポット解除
⑤「実施した期間」と「属性」列を結合
⑥「実施した期間」と「属性」列を削除
⑦横展開をするための[列のピポット]
⑧「売変」の値を数字から文字へ変更「1」→完了、「0」→未
- 各歯車マークの条件設定詳細は、下記映像です。
②
⑤
⑦
これで初回の条件設定は完了！
次からは、専用のフォルダにデータファイルを入れて、
「集計用」ファイルで[データ] → [すべて更新]をクリックするだけで、データが自動で指定の書式に追加されます。
＜マクロ設定方法＞
Power Queryでまとめた「集計結果」から「未報告店舗」をまとめるマクロを設定します。
【マクロの使い方】
-
Alt + F11→ VBAエディタを開く - [
標準モジュール]に各VBAコードを貼り付け -
F5で実行
➀「集計結果」から「未報告店舗」を抽出するVBAコード
Sub 未報告店舗まとめ更新()
Dim wsSrc As Worksheet, wsDst As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim monthName As String
Dim nextRow As Long
' Power Query出力シート
Set wsSrc = ThisWorkbook.Sheets("集計結果") ' ← Power Queryの出力シート名に変更
lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
lastCol = wsSrc.Cells(1, wsSrc.Columns.Count).End(xlToLeft).Column
' 出力シートの準備
On Error Resume Next
Set wsDst = ThisWorkbook.Sheets("未報告店舗まとめ")
If wsDst Is Nothing Then
Set wsDst = ThisWorkbook.Sheets.Add
wsDst.Name = "未報告店舗まとめ"
End If
On Error GoTo 0
' 見出し
wsDst.Cells(1, 1).Value = "店舗名"
wsDst.Cells(1, 2).Value = "月度"
wsDst.Cells(1, 3).Value = "記録日時"
' 既存データをクリア（毎回リフレッシュ）
wsDst.Range("A2:C" & wsDst.Rows.Count).ClearContents
' 月度ごとに走査
For j = 2 To lastCol Step 2
monthName = wsSrc.Cells(1, j).Value ' 例: 「5月度」「6月度」
For i = 3 To lastRow
' 配布数が空白の場合だけ出力
If IsEmpty(wsSrc.Cells(i, j)) Or wsSrc.Cells(i, j).Value = "" Then
nextRow = wsDst.Cells(wsDst.Rows.Count, "A").End(xlUp).Row + 1
wsDst.Cells(nextRow, 1).Value = wsSrc.Cells(i, 1).Value ' 店舗名
wsDst.Cells(nextRow, 2).Value = monthName ' 月度
wsDst.Cells(nextRow, 3).Value = Now ' 記録日時
End If
Next i
Next j
MsgBox "未報告店舗を月度ごとに抽出しました！", vbInformation
End Sub
②Power Queryでデータを更新すると、
連動して抽出を一括実行するVBAコード
Sub 更新してNull抽出()
Dim wb As Workbook
Dim found As Boolean
' 「集計結果」シートを持つブックを探してRefreshAll
For Each wb In Application.Workbooks
On Error Resume Next
found = Not wb.Worksheets("集計結果") Is Nothing
On Error GoTo 0
If found Then
wb.RefreshAll
On Error Resume Next
Application.CalculateUntilAsyncQueriesDone ' 対応しているExcelで有効
On Error GoTo 0
Application.Wait Now + TimeValue("0:00:03") ' 必要なら待機時間を延長
Exit For
End If
Next
' 抽出実行
Call Null配布数まとめ更新
End Sub
注意！
ChatGPTで提案したコードは、自分が保存しているシート名と異なる場合があるので、エラー防止のため、コードのシート名を確認してください。
これでマクロ設定は完了！
次からは、[開発] → [マクロ] → シート選択 → [実行]するだけで、データが自動で抽出・追加されます。
ChatGPT使用しながら気づいたこと
- とても便利！ただ、万能ではない
- 質問によって回答が変わるので、質問はより詳しく
- 写真などのイメージも活用できる
- ただし、無料プランは制限があるので、映像添付すると時間制限が発生してしまう
- 教えてくれるメニュー名が必ずしも一致しない
あとがき
初めての使用で教えてくれるメニュー名がExcelと異なったり、
Power Queryも初操作で戸惑うことはありましたが、
これを作ったことにより、今後毎月の定例作業が30分→1分に短縮できたので、
今後その時間に別の業務を取り組むことができるようになりました。
これで初回スタートを切りましたので、
他のExcel作業も簡潔化して業務効率改善に結びつけること間違いなし！
ここまで読んでいただきありがとうございます。