生成AIでExcelマクロを書かせてみた
itはじめに
皆さんの会社でも、日々のデータ処理にExcelを使っているのではないでしょうか？売上や勤怠の集計など、多くの作業が「手作業」で行われています。しかしこの手作業が、時間やミスの原因になってしまうことも多いのが現実です。会計や財務部門では重要な作業の多くがスプレッドシートに頼っている一方で、マクロを駆使して自動化できるのは一握りの“Excel上級者”だけでしたjournalofaccountancy.com。
そこで注目されているのが生成AIです。AIの進化により、自然言語で指示を出すだけで複雑なVBAマクロを生成することが可能になってきましたjournalofaccountancy.com。実際に中小企業のDX事例でも、ChatGPTとExcelマクロを組み合わせて帳票作成や在庫データ作成を自動化した企業が登場していますcity.shizuoka.lg.jp。
簡単な実験をしてみたので以下に紹介します。
実験の概要
今回のテーマは、**「従業員ごとの出勤簿ファイルから月次の勤怠集計を自動生成する」**こと。小規模事業者が紙やExcelで管理しているような勤怠データを、ボタン一つでまとめられたら便利ですよね。
使用したデータと条件
- 勤怠表のフォーマット：氏名・部門・日毎の出勤/在宅/出張/休み区分、始業時刻・終業時刻・実働時間などを記録したExcelファイル。各従業員1ファイル。(Microsoftのサイトのものを使わせていただきました。https://www.microsoft.com/ja-jp/office/pipc/template/result.aspx?id=14483)
- フォルダ構成：対象となるファイルはすべて同じフォルダ内に保存。ファイル数は毎月変わるため自動的に読み込む必要がある。
- 出力内容：年月・部名・氏名・出勤日数・在宅日数・勤務時間合計・残業時間合計（1日7時間超過分）を1行にまとめた集計表。
これらの要件をChatGPT５ plusに伝え、VBAマクロを生成してもらうことにしました。
生成AIへの指示と試行錯誤
1. 初回の指示と問題点
最初は「出勤簿を集計するVBAマクロを作ってください」というざっくりとした依頼を投げたところ、マクロは生成されたものの、日本語の文字列が文字化けしてしまいました。日本語部分が乱れており、とても実行できる状態ではありませんでした。
ファイル出力すると文字コードの問題が出るようだったので、プロンプト上に直接VBAコードを出力するように指示をしたことで、この問題は解決しました。(若干スマートではありませんが、これをコピペしてExcelファイルに自分で貼り付ける)
2. 実働時間の計算ミス
次に遭遇したのは勤務時間の合計が0になってしまう不具合です。これは、実働時間が「7:00」のように日付を持たない時刻で保存されているが、IsDateという関数で判定するコードになっていたことから計算がスキップされていたことが原因でした。修正を依頼すると「セルが空欄でなければシリアル値として時間数に変換する」というCDbl(値) * 24で計算するように改良しました。これは特にこうするように具体的指示をしたわけではなく、問題点を画像コピーして指摘すると自分で解決してくれました。
3. ヘッダが出力されない問題
さらに、集計結果シートにヘッダ行が生成されず、いきなりデータが書き込まれる現象も発生しました。これはシートがすでに存在する場合にはヘッダを書き込まない仕様だったためで、これを指摘すると、ヘッダ行が空欄なら必ず書き込むよう条件を追加して解決しました。
4. 最終的なマクロ
数回の修正依頼を経て、以下のような完成版マクロが生成されました。フォルダを選択すると全従業員分の勤怠ファイルを読み込み、集計結果を「集計結果」シートに追記していきます。日本語の区分にも問題なく対応し、勤務時間や時間外時間も正しく計算します。
Option Explicit
Sub 出勤簿集計()
Dim folderPath As String, fileName As String
Dim srcWb As Workbook, ws As Worksheet
Dim summaryWs As Worksheet, summaryRow As Long
Dim fileCount As Long
Dim fDialog As FileDialog
' 「集計結果」シートを取得または作成
On Error Resume Next
Set summaryWs = ThisWorkbook.Sheets("集計結果")
On Error GoTo 0
If summaryWs Is Nothing Then
Set summaryWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
summaryWs.Name = "集計結果"
End If
' ヘッダ行が空なら作成
If summaryWs.Cells(1, 1).Value = "" Then
summaryWs.Cells(1, 1).Value = "年月"
summaryWs.Cells(1, 2).Value = "部名"
summaryWs.Cells(1, 3).Value = "氏名"
summaryWs.Cells(1, 4).Value = "出勤日合計"
summaryWs.Cells(1, 5).Value = "在宅日合計"
summaryWs.Cells(1, 6).Value = "勤務時間合計"
summaryWs.Cells(1, 7).Value = "残業時間合計"
End If
' フォルダ選択ダイアログ
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
fDialog.Title = "集計対象フォルダを選択してください"
If fDialog.Show <> -1 Then
MsgBox "処理をキャンセルしました。"
Exit Sub
End If
folderPath = fDialog.SelectedItems(1)
If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
' フォルダ内のExcelファイルを順に処理
fileName = Dir(folderPath & "*.xls*")
Do While fileName <> ""
fileCount = fileCount + 1
Set srcWb = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
Set ws = srcWb.Sheets("勤怠管理表")
If Not ws Is Nothing Then
Dim baseDate As Variant, ym As String
Dim dept As String, empName As String
Dim shukkin As Long, zaitaku As Long, workTime As Double, overtime As Double
Dim r As Long, cat As String, t As Variant, hours As Double
baseDate = ws.Range("B5").Value
If IsDate(baseDate) Then
ym = Format(CDate(baseDate), "yyyy年mm月")
Else
ym = ""
End If
dept = ws.Range("I4").Value
empName = ws.Range("I5").Value
For r = 8 To ws.Rows.Count
If ws.Cells(r, 2).Value = "" Then Exit For
cat = ws.Cells(r, 4).Value
t = ws.Cells(r, 8).Value
If t <> "" Then
hours = CDbl(t) * 24
Else
hours = 0
End If
Select Case cat
Case "出勤"
shukkin = shukkin + 1
workTime = workTime + hours
If hours > 7 Then overtime = overtime + (hours - 7)
Case "在宅"
zaitaku = zaitaku + 1
workTime = workTime + hours
If hours > 7 Then overtime = overtime + (hours - 7)
Case "出張"
workTime = workTime + hours
If hours > 7 Then overtime = overtime + (hours - 7)
End Select
Next r
summaryRow = summaryWs.Cells(summaryWs.Rows.Count, 1).End(xlUp).Row + 1
summaryWs.Cells(summaryRow, 1).Value = ym
summaryWs.Cells(summaryRow, 2).Value = dept
summaryWs.Cells(summaryRow, 3).Value = empName
summaryWs.Cells(summaryRow, 4).Value = shukkin
summaryWs.Cells(summaryRow, 5).Value = zaitaku
summaryWs.Cells(summaryRow, 6).Value = workTime
summaryWs.Cells(summaryRow, 7).Value = overtime
End If
srcWb.Close False
fileName = Dir()
Loop
MsgBox fileCount & "件のファイルを集計しました。"
End Sub
生成された集計表
最終的に生成された集計表は以下のようになりました。複数名の勤怠データが同一シートにまとめられ、勤務時間や残業時間も自動計算されています。
「出勤日合計」「在宅日合計」「勤務時間合計」「時間外時間合計」が1行にまとまっているため、月ごとの勤怠状況を一目で把握できます。
やってみてわかったこと
今回の実験から見えた学びをまとめます。
- 具体的な指示が大事
AIに「出勤簿を集計して」と言うだけでは不十分です。どの列に何が入っているのか、どのように計算するのかを細かく伝えることで、精度の高いコードが返ってきました。Journal of Accountancyでも、詳しく手順を書いてプロンプトを作成することが重要だと述べていますjournalofaccountancy.com。 - 試行錯誤を前提にする
一度で完璧なコードが出ることは少なく、何度か修正依頼を繰り返すことで完成に近づきました。修正依頼に特別なスキルは必要なく、ただエラーの出たコードやExcelの結果をスクリーンショットでコピーしてプロンプトに入れると、どう修正すべきか自分で考えてくれます。 - 生成AIは魔法ではないが大きな助っ人
ChatGPTは関数やマクロを自動生成でき、初心者でも扱える自動化スクリプトが作れます。多くの中小企業で行われているExcel業務は繰り返し作業が多く、AIを導入することで作業効率を向上させることが可能です。
こんな場面で役立つ
今回の例は単純な集計でしたが、VBA＋生成AIの組み合わせは様々な場面で活躍します。
- ファイル数や名称が一定でない場合
毎月増減する勤怠ファイルや、名前が変わるデータを自動で読み込めます。 - 集計結果の項目が増える場合
マクロに項目追加の処理を組み込めば、数値や指標が増えても自動対応できます。 - 大量データの再集計や可視化
ピボットテーブルやグラフ生成まで自動化すれば、データ分析のスピードが大幅にアップします。
他の事例の紹介
- 帳票作成と在庫データの自動化（岩本工業株式会社）
静岡市のDXモデル事例では、生成AI（ChatGPT）とExcelマクロを組み合わせ、加工指示用の帳票3種類と在庫データ作成を連携・自動化した例が紹介されていますcity.shizuoka.lg.jp。日々の帳票処理が大幅に効率化されたそうです。 - 営業データの清掃と集計（会計事務所）
Journal of Accountancyの記事では、売上データをダウンロード・加工・集計する作業をGPT‑4が生成したマクロで自動化し、ピボットやレポート作成まで完結させた事例が紹介されています。従来は上級ユーザーしかできなかった作業が、適切なプロンプトで誰でも実現できると述べていますjournalofaccountancy.com。 - Excel関数やVBAコードの自動生成（コンサルティング会社）
ひなたコンサルティング様の記事では、ChatGPTに「売上を月別に集計したい」と伝えるだけで最適な関数を提案してくれるなど、Excel業務の生産性向上にAIが役立つ事例が紹介されていますhinataconsult.com。
おわりに
勤怠管理のような繰り返し業務は、AI＋マクロの力で大幅に効率化できます。とはいえ、AIに任せきりにするのではなく、**「どんな処理をしてほしいのか」**を具体的に伝えることが成功のカギだと感じました。
まだまだ発展途上の技術ですが、今回の例のように小規模事業者でも手軽に導入できるツールとなりつつあります。ご興味のある方は、ぜひ自身の業務で試してみてください。研究会でも今後、さらなる活用事例やノウハウを共有していきたいと思います。
生成AIを上手に活用して、毎日の業務をもっとラクに、もっと効率的にしていきましょう！
＼ 最新情報をチェック ／
