▶店舗運営管理室

［シリーズ：ChatGPTを使ってExcelフル活用］①VBAコードを使えば分析作業が劇的に時短！？

▶店舗運営管理室

この記事を読んでほしい人
□簡単な関数なら普段から使っている人
□ﾋﾟﾎﾟｯﾄﾃｰﾌﾞﾙを何度か使った事ある人
□人とは違う仮説検証をしたい人
□一般的な営業管理データが物足りない人
□分析資料は自分で作りたい人
□VBAやマクロに何度か挫折した人
□ChatGPTの普段使いにﾁｬﾚﾝｼﾞしたい人

これまで店舗の売上や商品の出数を分析する際に、Excelで表やグラフを作っていました。
でも少し複雑な集計をしたい時や、大量のデータを分析する時には関数を使ったり、Excelに詳しい同僚に頼る事が常でした。

しかし、ChatGPTを活用すると
①関数（SUM、AVERAGEなど）の知識不要
②手作業に比べると圧倒的に時短

今回は、手作業とChatGPT活用でどれぐらい時短効果があるのか検証してみました。

１．調査の概要

店舗の情報を分析する際に、ChatGPTを活用すると、どれぐらいの時短効果を発揮するか調査してみました。

２．調査に使う分析作業

１）分析の目的

１ヵ月の会計情報とは言え、来店日や注文履歴、お会計の詳細など膨大なデータがあって、それらを必要に応じて抽出してまとめるだけでも一苦労。

今回は滞在時間を組人数を調べるための準備段階として、必要なデータを抽出して別シートに移すところまで。

２）分析に使う元データ

飲食店を経営している知人からお会計情報や商品注文履歴などのデータを、分析のためにお借りする事が出来ました。そのお店ではリクルートのAirPAYという決済サービスを利用しています。

データはかなりの量です。

「会計明細」というデータには「取引No」や「来店日」「会計時間」など５０列からなるタイトルの情報が５２２１行も入力されています。

手作業では手間がかかり過ぎる…。

３）分析のために作りたいデータ

①今回は５０列あるタイトルから「来店日」「来店時間」「会計時間」「人数」の４列を抜き取り、新しいシートにコピーします。
②「来店日」が空欄の行を削除する。

▼before

▼after

４）VBAコードの基礎知識

VBA（Visual Basic for Applications）とは？
Microsoft Officeアプリケーション（特にExcel）を自動化するためのプログラミング言語です。VBAを使うと、Excel内のデータを操作したり、複雑な計算を行ったり、繰り返しの作業を自動化したりすることができます。

※英会話で例えると

A：日本人にお願いする際は日本語で伝える。
B：英語が話せない人がアメリカ人と対話する際は、翻訳機で日本語を英語に翻訳する。

A：Excelに自動計算や作業をお願いする際はVBAという言語を使用する。
B：Excelへの指示を日本語でまとめ、ChatGPTを使いVBAに翻訳してもらう。
C：翻訳されたVBAコードをExcelに貼り付け指示を伝えれば、自動で計算や作業をしてくれる。

５）VBAコードの生成を指示

ChatGPTに以下のテキストを書き込んで、Excelが理解できるVBAコードに翻訳してもらう。

指示

ExcelのVBAコード生成に関する要求

##現在の状況：
・「Sheet1」という名前のシートに飲食店の顧客来店情報をまとめた表があります。
・表の詳細は以下の通りです。
　・行1: タイトル行
　・データ範囲: A2:AX5222
　・例:
　　A列「取引No」
　　B列「来店日」
　　C列「来店時間」
　　D列「会計日」
　　E列「会計時間」…など。

##マクロの要求：
・「抽出」という新しいシートを作成してください。
・「Sheet1」のB列「来店日」、C列「来店時間」、E列「会計時間」、AG列「人数」を「抽出」シートにコピー&ペーストしてください。

##制約条件：
・「Sheet1」のデータを「抽出」シートにコピーする際、行１にはタイトル、A2から空列を作らずにデータを左詰めで貼り付けてください。
・「来店日」が空白の行はコピーしないでください。
・データを貼り付ける際、書式を「Sheet1」と同じにしてください。

６）生成されたVBAコード

返答

Sub ExtractData()

Dim wsSource As Worksheet, wsDest As Worksheet
Dim LastRow As Long
Dim rng As Range, cell As Range
Dim DestRow As Long

' ソースシートをセット
Set wsSource = ThisWorkbook.Sheets("Sheet1")

' 新しいシートを作成
On Error Resume Next
Set wsDest = ThisWorkbook.Sheets("抽出")
On Error GoTo 0

If wsDest Is Nothing Then
    Set wsDest = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsDest.Name = "抽出"
Else
    wsDest.Cells.Clear
End If

' タイトルをコピー
wsDest.Cells(1, 1).Value = "来店日"
wsDest.Cells(1, 2).Value = "来店時間"
wsDest.Cells(1, 3).Value = "会計時間"
wsDest.Cells(1, 4).Value = "人数"

' データの最終行を取得
LastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

DestRow = 2

' 条件に合ったデータをコピー
For Each cell In wsSource.Range("B2:B" & LastRow)
    If cell.Value <> "" Then
        cell.Copy wsDest.Cells(DestRow, 1) ' 来店日
        cell.Offset(0, 1).Copy wsDest.Cells(DestRow, 2) ' 来店時間
        cell.Offset(0, 3).Copy wsDest.Cells(DestRow, 3) ' 会計時間
        cell.Offset(0, 31).Copy wsDest.Cells(DestRow, 4) ' 人数
        DestRow = DestRow + 1
    End If
Next cell

End Sub

VBAのプログラミング言語を勉強した事がないので、ChatGPTが生成した上記のコードの意味は全くわかりませんが、Excelにコピーしたら要求通りの自動作業が行われました！

７）VBAコードの貼り方

ChatGPTでVBAコードを生成したらExcelに貼り付けて実行ボタンを押すと、要求した通りの自動計算や作業が実行されます。

ただVBAコードの貼り付け先が分からない場合があるので以下の通り簡単にではありますが解説します。

手順１：Visual Basicを開く

手順２：標準モジュールを挿入

手順３：VBAコードを貼り付ける

７.調査結果

１）検証方法

  • ５０列×５，２２２行の元データを、分析に必要な４列×４９６行だけをコピーして、「抽出」という新規で作ったのシートに移す。
  • ①列の削除を手作業での表作成と②VBAコードで表作成にかかる時間を計測する。
  • 手作業では「関数」「フィルター」「ピポットテーブル」などの機能を使わず、単純に列と行の削除で表作成を行う。
  • VBAコードで表作成、既にChatGPTへの指示文（プロンプト）があり、それを貼り付けるところからスタートとする。

２）検証結果

①単純に列と行を手作業で削除していくと２８分３３秒でした。かなり面倒です。しかも手作業なのでミスする可能性も…。

②VBAコードで表作成なら４１秒でした。Excelに貼り付ける最初の１０秒以外はパソコンが自動で計算してくれるのを見てるだけ。

※ちなみに「フィルター」機能を使って手作業をしたら、最短で５９秒で表を作る事が出来ました。

３）まとめ

完全な手作業を比べると圧倒的な時短効果！

ただExcelの色々な機能を駆使すれば同じようなタイムでも手作業で表を作る事は出来ました。

次回はもう少し複雑な処理で検証してみます。

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