はじめに
データ分析において難しいのは、分析そのものよりも分析結果から何をすべきかを決めることである。
EDAやクロス集計ができても、「結局どこを改善するのか」「それによってどれほどの効果があるのか」を説明できないケースは多い。
本記事では、LLMに帳票設計を担わせることで、分析から活用までをほぼ自動化できるかを検証する。
対象はダミーの家計簿データであるが、業務データへの転用も前提とする。
背景と狙い
以前、GASを用いてExcel帳票を自動生成するプロンプトを公開した。
今回はアプローチを変え、以下を狙った。
- LLMに分析設計そのものを考えさせる
- Excel VBAで帳票を自動生成する
- 人は元データを投入するだけにする
特に重視した問いは次の通りである。
何が起きていて、どうなっており、何をすべきで、その期待効果はいくらか
この問いに、Excel上の数式だけで答えられる構成を目指した。
技術スタック
-
ChatGPT-5.2（thinking）
GeminiやCopilotを利用する企業も多いが、汎用性を重視しChatGPTを採用した。
thinkingでなくとも動作するが、生成される帳票は簡略化される。 -
Excel VBA
業務での利用人口が多く、追加ツールを必要としない点を評価した。
使用プロンプト
GPTsにて、下記の指示（システムプロンプト）を挿入する。
※会話UIをそのまま使ってしまうとほかのチャットに内容が引っ張られてしまい出力の精度が大きく下がる。GPTsにした方が安定する。
期待される出力としてはダミーデータが出現し、そこからやりたい内容を加えれば帳票を作成するVBAが出力される。
※項目が英語のままだったりするので、適宜「日本語にしろ」、と命令したり、元データを貼り付ければ出力されるようにしたい場合は項目とサンプル10件ほど入力プロンプトに入れればいい。元データをそのまま貼り付けると大きすぎてVBAがエラーを吐くので注意。
システムプロンプト：https://github.com/amenohabakiri-hue/gas_spreadsheet_prompt/blob/main/prompts_vba
入力した指示：
家計簿を作成し、データ分析をする。
・EDA
・クロス集計
・何をすべきか？
・その期待効果は？
※作成したシートから具体数値を数式で出す。
上記各帳票を作成せよ。
なお、列名と項目は日本語にする。
出力結果
プロンプトを入力すると、VBAコードが出力される。
これをExcelに貼り付けて実行すると、以下の帳票が自動生成される。
VBAコード
```vba
Option Explicit
' ==========================================
' メイン処理（エントリポイント）
' ==========================================
Sub Main()
Dim wb As Workbook
Set wb = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo ErrorHandler
Call CreateKakeiboDataSheet(wb)
Call CreateEDASheet(wb)
Call CreateCrossTabSheet(wb)
Call CreateActionSheet(wb)
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
wb.Worksheets(1).Activate
MsgBox "処理が正常に完了しました。", vbInformation
Exit Sub
ErrorHandler:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "エラーが発生しました: " & Err.Description, vbCritical
End Sub
' ==========================================
' 1) 家計簿データ作成
' ==========================================
Sub CreateKakeiboDataSheet(wb As Workbook)
Dim ws As Worksheet
Set ws = ForceCreateSheet(wb, "家計簿_データ")
Dim headers As Variant
headers = Array("日付", "区分", "カテゴリ", "サブカテゴリ", "支払方法", "店名", "単価", "数量", "金額", "収支", "年", "月", "月初日", "週", "曜日")
ws.Range("A1").Resize(1, UBound(headers) + 1).Value = headers
Dim expenseCats As Variant
expenseCats = Array("食費", "外食", "住居", "光熱費", "通信", "交通", "日用品", "医療", "教育", "娯楽", "交際", "衣服", "美容", "サブスク", "その他")
Dim incomeCats As Variant
incomeCats = Array("給与", "賞与", "副業", "臨時収入")
Dim payMethods As Variant
payMethods = Array("現金", "クレジット", "デビット", "電子マネー", "口座振替", "QR決済")
Dim shops As Variant
shops = Array("スーパー", "コンビニ", "ドラッグストア", "外食店", "ネット通販", "家賃", "電力会社", "ガス会社", "通信会社", "交通機関", "病院", "学校", "サブスク事業者", "その他")
Dim startDate As Date
startDate = DateSerial(2025, 1, 1)
Dim endDate As Date
endDate = DateSerial(2025, 12, 31)
Dim rowCount As Long
rowCount = 500
Randomize
Dim i As Long
For i = 2 To rowCount + 1
Dim d As Date
d = startDate + Int((endDate - startDate + 1) * Rnd)
Dim isExpense As Boolean
isExpense = (Rnd < 0.85)
Dim kbn As String
Dim cat As String
Dim subCat As String
Dim pm As String
Dim shop As String
Dim unitPrice As Long
Dim qty As Long
If isExpense Then
kbn = "支出"
cat = expenseCats(Int((UBound(expenseCats) + 1) * Rnd))
subCat = PickExpenseSubCategory(cat)
pm = payMethods(Int((UBound(payMethods) + 1) * Rnd))
shop = PickShopByCategory(cat, shops)
unitPrice = PickExpenseUnitPrice(cat)
qty = PickExpenseQty(cat)
Else
kbn = "収入"
cat = incomeCats(Int((UBound(incomeCats) + 1) * Rnd))
subCat = PickIncomeSubCategory(cat)
pm = "振込"
shop = "勤務先"
unitPrice = PickIncomeUnitPrice(cat)
qty = 1
End If
ws.Cells(i, 1).Value = d
ws.Cells(i, 2).Value = kbn
ws.Cells(i, 3).Value = cat
ws.Cells(i, 4).Value = subCat
ws.Cells(i, 5).Value = pm
ws.Cells(i, 6).Value = shop
ws.Cells(i, 7).Value = unitPrice
ws.Cells(i, 8).Value = qty
' 計算列は必ず数式
ws.Cells(i, 9).FormulaR1C1 = "=RC[-2]*RC[-1]" ' 金額
ws.Cells(i, 10).FormulaR1C1 = "=IF(RC[-8]=""支出"",-RC[-1],RC[-1])" ' 収支
ws.Cells(i, 11).FormulaR1C1 = "=YEAR(RC[-10])" ' 年
ws.Cells(i, 12).FormulaR1C1 = "=MONTH(RC[-11])" ' 月
ws.Cells(i, 13).FormulaR1C1 = "=DATE(RC[-2],RC[-1],1)" ' 月初日
ws.Cells(i, 14).FormulaR1C1 = "=WEEKNUM(RC[-13],2)" ' 週
ws.Cells(i, 15).FormulaR1C1 = "=TEXT(RC[-14],""aaa"")" ' 曜日
Next i
' 書式
ws.Columns("A").NumberFormatLocal = "yyyy/mm/dd"
ws.Columns("G").NumberFormatLocal = "#,##0"
ws.Columns("H").NumberFormatLocal = "0"
ws.Columns("I").NumberFormatLocal = "#,##0"
ws.Columns("J").NumberFormatLocal = "#,##0"
ws.Columns("K").NumberFormatLocal = "0"
ws.Columns("L").NumberFormatLocal = "0"
ws.Columns("M").NumberFormatLocal = "yyyy/mm/dd"
Call ApplyBasicFormatting(ws)
' 既定は日付昇順
ws.Range("A1").CurrentRegion.Sort _
Key1:=ws.Range("A2"), Order1:=xlAscending, Header:=xlYes
' ウィンドウ枠固定
ws.Activate
ws.Range("A2").Select
ActiveWindow.FreezePanes = True
End Sub
' ==========================================
' 2) EDA（探索的データ分析）作成
' ==========================================
Sub CreateEDASheet(wb As Workbook)
Dim ws As Worksheet
Set ws = ForceCreateSheet(wb, "EDA")
Dim dataWs As Worksheet
Set dataWs = wb.Worksheets("家計簿_データ")
Dim lastRow As Long
lastRow = dataWs.Cells(dataWs.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then lastRow = 2
Dim rngDate As String, rngKbn As String, rngCat As String, rngPay As String, rngAmt As String, rngMonthStart As String
rngDate = "'家計簿_データ'!$A$2:$A$" & CStr(lastRow)
rngKbn = "'家計簿_データ'!$B$2:$B$" & CStr(lastRow)
rngCat = "'家計簿_データ'!$C$2:$C$" & CStr(lastRow)
rngPay = "'家計簿_データ'!$E$2:$E$" & CStr(lastRow)
rngAmt = "'家計簿_データ'!$I$2:$I$" & CStr(lastRow)
rngMonthStart = "'家計簿_データ'!$M$2:$M$" & CStr(lastRow)
ws.Range("A1").Value = "EDA（探索的データ分析）"
ws.Range("A1").Font.Bold = True
ws.Range("A1").Font.Size = 14
' サマリー
ws.Range("A3").Value = "集計期間（最小日付）"
ws.Range("B3").Formula = "=MIN(" & rngDate & ")"
ws.Range("A4").Value = "集計期間（最大日付）"
ws.Range("B4").Formula = "=MAX(" & rngDate & ")"
ws.Range("A6").Value = "総収入（円）"
ws.Range("B6").Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""収入"")"
ws.Range("A7").Value = "総支出（円）"
ws.Range("B7").Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出"")"
ws.Range("A8").Value = "収支差額（円）"
ws.Range("B8").Formula = "=B6-B7"
ws.Range("A10").Value = "取引件数（件）"
ws.Range("B10").Formula = "=COUNTA(" & rngDate & ")"
ws.Range("A11").Value = "支出平均（円/件）"
ws.Range("B11").Formula = "=AVERAGEIFS(" & rngAmt & "," & rngKbn & ",""支出"")"
ws.Range("A12").Value = "支出中央値（円/件）"
ws.Range("B12").FormulaArray = "=MEDIAN(IF(" & rngKbn & "=""支出""," & rngAmt & "))"
ws.Range("A13").Value = "支出最大（円/件）"
ws.Range("B13").FormulaArray = "=MAX(IF(" & rngKbn & "=""支出""," & rngAmt & "))"
ws.Range("A14").Value = "支出最小（円/件）"
ws.Range("B14").FormulaArray = "=MIN(IF(" & rngKbn & "=""支出""," & rngAmt & "))"
ws.Range("A15").Value = "支出のばらつき（標準偏差）"
ws.Range("B15").FormulaArray = "=STDEV(IF(" & rngKbn & "=""支出""," & rngAmt & "))"
ws.Columns("A").ColumnWidth = 24
ws.Columns("B").ColumnWidth = 18
ws.Range("B3:B4").NumberFormatLocal = "yyyy/mm/dd"
ws.Range("B6:B8").NumberFormatLocal = "#,##0"
ws.Range("B10:B15").NumberFormatLocal = "#,##0"
' 月別推移
ws.Range("A18").Value = "月別推移（支出/収入/差額）"
ws.Range("A18").Font.Bold = True
ws.Range("A19").Value = "月初日"
ws.Range("B19").Value = "支出（円）"
ws.Range("C19").Value = "収入（円）"
ws.Range("D19").Value = "差額（円）"
Dim baseYear As Long
baseYear = 2025
Dim m As Long
For m = 1 To 12
ws.Cells(19 + m, 1).Value = DateSerial(baseYear, m, 1)
ws.Cells(19 + m, 2).Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngMonthStart & ",A" & CStr(19 + m) & ")"
ws.Cells(19 + m, 3).Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""収入""," & rngMonthStart & ",A" & CStr(19 + m) & ")"
ws.Cells(19 + m, 4).Formula = "=C" & CStr(19 + m) & "-B" & CStr(19 + m)
Next m
ws.Range("A20:A31").NumberFormatLocal = "yyyy/mm/dd"
ws.Range("B20:D31").NumberFormatLocal = "#,##0"
' 月別推移チャート（差額）
Dim chartRng As Range
Set chartRng = ws.Range("A19:D31")
Call AddSafeChart(ws, chartRng, "月別推移（支出/収入/差額）", 380, 40, xlLineMarkers)
' 年間カテゴリ支出（上位抽出用の台）
ws.Range("A34").Value = "年間支出（カテゴリ別）"
ws.Range("A34").Font.Bold = True
ws.Range("A35").Value = "カテゴリ"
ws.Range("B35").Value = "年間支出（円）"
Dim expenseCats As Variant
expenseCats = Array("食費", "外食", "住居", "光熱費", "通信", "交通", "日用品", "医療", "教育", "娯楽", "交際", "衣服", "美容", "サブスク", "その他")
Dim c As Long
For c = 0 To UBound(expenseCats)
ws.Cells(36 + c, 1).Value = CStr(expenseCats(c))
ws.Cells(36 + c, 2).Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngCat & ",A" & CStr(36 + c) & ")"
Next c
ws.Range("B36:B" & CStr(36 + UBound(expenseCats))).NumberFormatLocal = "#,##0"
' 降順ソート（年間支出の多い順）
Dim sortRng As Range
Set sortRng = ws.Range("A35").Resize(UBound(expenseCats) + 2, 2)
sortRng.Sort Key1:=ws.Range("B36"), Order1:=xlDescending, Header:=xlYes
' 上位3カテゴリ表示
ws.Range("D34").Value = "支出上位（カテゴリ）"
ws.Range("D34").Font.Bold = True
ws.Range("D35").Value = "順位"
ws.Range("E35").Value = "カテゴリ"
ws.Range("F35").Value = "年間支出（円）"
ws.Range("D36").Value = "1位"
ws.Range("D37").Value = "2位"
ws.Range("D38").Value = "3位"
ws.Range("E36").Formula = "=A36"
ws.Range("E37").Formula = "=A37"
ws.Range("E38").Formula = "=A38"
ws.Range("F36").Formula = "=B36"
ws.Range("F37").Formula = "=B37"
ws.Range("F38").Formula = "=B38"
ws.Range("F36:F38").NumberFormatLocal = "#,##0"
Call ApplyBasicFormatting(ws)
End Sub
' ==========================================
' 3) クロス集計作成（SUMIFSで作る安全なクロス）
' ==========================================
Sub CreateCrossTabSheet(wb As Workbook)
Dim ws As Worksheet
Set ws = ForceCreateSheet(wb, "クロス集計")
Dim dataWs As Worksheet
Set dataWs = wb.Worksheets("家計簿_データ")
Dim lastRow As Long
lastRow = dataWs.Cells(dataWs.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then lastRow = 2
Dim rngKbn As String, rngCat As String, rngPay As String, rngAmt As String, rngMonthStart As String
rngKbn = "'家計簿_データ'!$B$2:$B$" & CStr(lastRow)
rngCat = "'家計簿_データ'!$C$2:$C$" & CStr(lastRow)
rngPay = "'家計簿_データ'!$E$2:$E$" & CStr(lastRow)
rngAmt = "'家計簿_データ'!$I$2:$I$" & CStr(lastRow)
rngMonthStart = "'家計簿_データ'!$M$2:$M$" & CStr(lastRow)
ws.Range("A1").Value = "クロス集計（SUMIFS）"
ws.Range("A1").Font.Bold = True
ws.Range("A1").Font.Size = 14
Dim baseYear As Long
baseYear = 2025
' ---- クロス1: カテゴリ × 月（支出） ----
ws.Range("A3").Value = "【支出】カテゴリ × 月"
ws.Range("A3").Font.Bold = True
ws.Range("A4").Value = "カテゴリ"
Dim m As Long
For m = 1 To 12
ws.Cells(4, 1 + m).Value = DateSerial(baseYear, m, 1)
Next m
ws.Cells(4, 14).Value = "年間合計"
ws.Range("B4:M4").NumberFormatLocal = "yyyy/mm/dd"
Dim expenseCats As Variant
expenseCats = Array("食費", "外食", "住居", "光熱費", "通信", "交通", "日用品", "医療", "教育", "娯楽", "交際", "衣服", "美容", "サブスク", "その他")
Dim r As Long
For r = 0 To UBound(expenseCats)
ws.Cells(5 + r, 1).Value = CStr(expenseCats(r))
For m = 1 To 12
ws.Cells(5 + r, 1 + m).Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngCat & ",$A" & CStr(5 + r) & "," & rngMonthStart & ",B$4)"
Next m
ws.Cells(5 + r, 14).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Next r
' 合計行
ws.Cells(5 + UBound(expenseCats) + 1, 1).Value = "合計"
For m = 1 To 12
ws.Cells(5 + UBound(expenseCats) + 1, 1 + m).FormulaR1C1 = "=SUM(R[-" & CStr(UBound(expenseCats) + 1) & "]C:R[-1]C)"
Next m
ws.Cells(5 + UBound(expenseCats) + 1, 14).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Dim cross1EndRow As Long
cross1EndRow = 5 + UBound(expenseCats) + 1
ws.Range("B5:N" & CStr(cross1EndRow)).NumberFormatLocal = "#,##0"
' ---- クロス2: 支払方法 × カテゴリ（支出） ----
Dim startRow2 As Long
startRow2 = cross1EndRow + 4
ws.Cells(startRow2, 1).Value = "【支出】支払方法 × カテゴリ（年間）"
ws.Cells(startRow2, 1).Font.Bold = True
ws.Cells(startRow2 + 1, 1).Value = "支払方法"
Dim c As Long
For c = 0 To UBound(expenseCats)
ws.Cells(startRow2 + 1, 2 + c).Value = CStr(expenseCats(c))
Next c
ws.Cells(startRow2 + 1, 2 + UBound(expenseCats) + 1).Value = "合計"
Dim payMethods As Variant
payMethods = Array("現金", "クレジット", "デビット", "電子マネー", "口座振替", "QR決済", "振込")
Dim pr As Long
For pr = 0 To UBound(payMethods)
ws.Cells(startRow2 + 2 + pr, 1).Value = CStr(payMethods(pr))
For c = 0 To UBound(expenseCats)
ws.Cells(startRow2 + 2 + pr, 2 + c).Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngPay & ",$A" & CStr(startRow2 + 2 + pr) & "," & rngCat & ",B$" & CStr(startRow2 + 1) & ")"
Next c
ws.Cells(startRow2 + 2 + pr, 2 + UBound(expenseCats) + 1).FormulaR1C1 = "=SUM(RC[-" & CStr(UBound(expenseCats) + 1) & "]:RC[-1])"
Next pr
Dim cross2EndRow As Long
cross2EndRow = startRow2 + 2 + UBound(payMethods)
ws.Range(ws.Cells(startRow2 + 2, 2), ws.Cells(cross2EndRow, 2 + UBound(expenseCats) + 1)).NumberFormatLocal = "#,##0"
Call ApplyBasicFormatting(ws)
ws.Columns.AutoFit
End Sub
' ==========================================
' 4) 何をすべきか？/期待効果（数式で具体数値）
' ==========================================
Sub CreateActionSheet(wb As Workbook)
Dim ws As Worksheet
Set ws = ForceCreateSheet(wb, "改善提案")
Dim dataWs As Worksheet
Set dataWs = wb.Worksheets("家計簿_データ")
Dim lastRow As Long
lastRow = dataWs.Cells(dataWs.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then lastRow = 2
Dim rngKbn As String, rngCat As String, rngAmt As String
rngKbn = "'家計簿_データ'!$B$2:$B$" & CStr(lastRow)
rngCat = "'家計簿_データ'!$C$2:$C$" & CStr(lastRow)
rngAmt = "'家計簿_データ'!$I$2:$I$" & CStr(lastRow)
ws.Range("A1").Value = "何をすべきか？（施策）と期待効果（数式で算出）"
ws.Range("A1").Font.Bold = True
ws.Range("A1").Font.Size = 14
ws.Range("A3").Value = "施策"
ws.Range("B3").Value = "現状（年間支出・円）"
ws.Range("C3").Value = "削減率"
ws.Range("D3").Value = "目標（年間支出・円）"
ws.Range("E3").Value = "期待効果（年間削減・円）"
ws.Range("F3").Value = "期待効果（月間削減・円）"
ws.Range("G3").Value = "狙い（メモ）"
' 施策1: 外食
ws.Range("A4").Value = "外食を10%削減（回数/単価の見直し）"
ws.Range("B4").Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngCat & ",""外食"")"
ws.Range("C4").Value = 0.1
ws.Range("D4").Formula = "=B4*(1-C4)"
ws.Range("E4").Formula = "=B4-D4"
ws.Range("F4").Formula = "=E4/12"
ws.Range("G4").Value = "外食頻度を減らす/ランチ上限を決める"
' 施策2: サブスク
ws.Range("A5").Value = "サブスクを20%削減（未使用解約/プラン変更）"
ws.Range("B5").Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngCat & ",""サブスク"")"
ws.Range("C5").Value = 0.2
ws.Range("D5").Formula = "=B5*(1-C5)"
ws.Range("E5").Formula = "=B5-D5"
ws.Range("F5").Formula = "=E5/12"
ws.Range("G5").Value = "利用実績が低い契約を棚卸し"
' 施策3: 通信費
ws.Range("A6").Value = "通信費を15%削減（プラン/回線の見直し）"
ws.Range("B6").Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngCat & ",""通信"")"
ws.Range("C6").Value = 0.15
ws.Range("D6").Formula = "=B6*(1-C6)"
ws.Range("E6").Formula = "=B6-D6"
ws.Range("F6").Formula = "=E6/12"
ws.Range("G6").Value = "データ容量/セット割の再設計"
' 施策4: 光熱費
ws.Range("A7").Value = "光熱費を5%削減（節電/契約アンペア見直し）"
ws.Range("B7").Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngCat & ",""光熱費"")"
ws.Range("C7").Value = 0.05
ws.Range("D7").Formula = "=B7*(1-C7)"
ws.Range("E7").Formula = "=B7-D7"
ws.Range("F7").Formula = "=E7/12"
ws.Range("G7").Value = "ピーク時間帯の使用量削減"
' 施策5: 交際
ws.Range("A8").Value = "交際費を10%削減（上限ルール化）"
ws.Range("B8").Formula = "=SUMIFS(" & rngAmt & "," & rngKbn & ",""支出""," & rngCat & ",""交際"")"
ws.Range("C8").Value = 0.1
ws.Range("D8").Formula = "=B8*(1-C8)"
ws.Range("E8").Formula = "=B8-D8"
ws.Range("F8").Formula = "=E8/12"
ws.Range("G8").Value = "月予算を決めて超過を抑える"
' 期待効果合計
ws.Range("A10").Value = "合計（期待効果）"
ws.Range("E10").Formula = "=SUM(E4:E8)"
ws.Range("F10").Formula = "=SUM(F4:F8)"
ws.Range("G10").Value = "年間/月間の削減見込み合計"
' 施策の優先順位（期待効果の大きい順）
ws.Range("A12").Value = "優先順位（期待効果が大きい順）"
ws.Range("A12").Font.Bold = True
ws.Range("A13").Value = "順位"
ws.Range("B13").Value = "施策"
ws.Range("C13").Value = "年間削減（円）"
ws.Range("B14").Formula = "=A4"
ws.Range("C14").Formula = "=E4"
ws.Range("B15").Formula = "=A5"
ws.Range("C15").Formula = "=E5"
ws.Range("B16").Formula = "=A6"
ws.Range("C16").Formula = "=E6"
ws.Range("B17").Formula = "=A7"
ws.Range("C17").Formula = "=E7"
ws.Range("B18").Formula = "=A8"
ws.Range("C18").Formula = "=E8"
ws.Range("A14").Value = "1"
ws.Range("A15").Value = "2"
ws.Range("A16").Value = "3"
ws.Range("A17").Value = "4"
ws.Range("A18").Value = "5"
' 年間削減列でソート（降順）
Dim sortRng As Range
Set sortRng = ws.Range("A13").Resize(6, 3)
sortRng.Sort Key1:=ws.Range("C14"), Order1:=xlDescending, Header:=xlYes
ws.Range("B4:B10").NumberFormatLocal = "#,##0"
ws.Range("D4:F10").NumberFormatLocal = "#,##0"
ws.Range("C4:C8").NumberFormatLocal = "0%"
ws.Range("C14:C18").NumberFormatLocal = "#,##0"
Call ApplyBasicFormatting(ws)
ws.Columns.AutoFit
End Sub
' ==========================================
' 共通ユーティリティ（必須・変更禁止）
' ==========================================
' シートを強制的に新規作成
Function ForceCreateSheet(wb As Workbook, sheetName As String) As Worksheet
Dim ws As Worksheet
On Error Resume Next
Set ws = wb.Worksheets(sheetName)
If Not ws Is Nothing Then ws.Delete
On Error GoTo 0
Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
ws.Name = sheetName
Set ForceCreateSheet = ws
End Function
' 基本的な見栄えを整える
Sub ApplyBasicFormatting(ws As Worksheet)
If ws.Cells(ws.Rows.Count, 1).End(xlUp).Row < 1 Then Exit Sub
Dim rng As Range
Set rng = ws.UsedRange
With rng.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' 先頭行に見出しがある場合の装飾（A1行）
If ws.Cells(1, 1).Value <> "" Then
With ws.Range(ws.Cells(1, 1), ws.Cells(1, rng.Columns.Count))
.Interior.Color = RGB(220, 220, 220)
.Font.Bold = True
.HorizontalAlignment = xlLeft
End With
End If
' 3行目が表ヘッダーの場合も多いので装飾（A3行）
If ws.Cells(3, 1).Value <> "" And ws.Cells(3, 2).Value <> "" Then
With ws.Range(ws.Cells(3, 1), ws.Cells(3, rng.Columns.Count))
.Interior.Color = RGB(235, 235, 235)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
End If
ws.Columns.AutoFit
' オートフィルタ（A1起点の表がある場合）
On Error Resume Next
If Not ws.AutoFilterMode Then
If ws.Range("A1").Value <> "" Then ws.Range("A1").CurrentRegion.AutoFilter
End If
On Error GoTo 0
End Sub
' 安全なグラフ作成（AddChart2禁止対応）
Sub AddSafeChart(ws As Worksheet, dataRng As Range, title As String, leftPos As Double, topPos As Double, chartType As XlChartType)
Dim co As ChartObject
Set co = ws.ChartObjects.Add(leftPos, topPos, 520, 260)
With co.Chart
.SetSourceData dataRng
.ChartType = chartType
.HasTitle = True
.ChartTitle.Text = title
.HasLegend = True
End With
End Sub
' ==========================================
' データ生成用ヘルパー
' ==========================================
Private Function PickExpenseSubCategory(cat As String) As String
Dim arr As Variant
Select Case cat
Case "食費": arr = Array("米・パン", "肉・魚", "野菜", "飲料", "調味料")
Case "外食": arr = Array("ランチ", "ディナー", "カフェ", "デリバリー")
Case "住居": arr = Array("家賃", "管理費", "修繕", "家具")
Case "光熱費": arr = Array("電気", "ガス", "水道")
Case "通信": arr = Array("携帯", "固定回線", "端末分割")
Case "交通": arr = Array("電車", "バス", "タクシー", "ガソリン")
Case "日用品": arr = Array("洗剤", "紙類", "雑貨")
Case "医療": arr = Array("病院", "薬", "検診")
Case "教育": arr = Array("学費", "教材", "習い事")
Case "娯楽": arr = Array("映画", "ゲーム", "旅行", "本")
Case "交際": arr = Array("飲み会", "贈答", "冠婚葬祭")
Case "衣服": arr = Array("服", "靴", "小物")
Case "美容": arr = Array("化粧品", "美容院", "ケア用品")
Case "サブスク": arr = Array("動画", "音楽", "クラウド", "アプリ")
Case Else: arr = Array("その他")
End Select
PickExpenseSubCategory = CStr(arr(Int((UBound(arr) + 1) * Rnd)))
End Function
Private Function PickIncomeSubCategory(cat As String) As String
Dim arr As Variant
Select Case cat
Case "給与": arr = Array("基本給", "残業", "手当")
Case "賞与": arr = Array("夏", "冬")
Case "副業": arr = Array("業務委託", "物販", "配達")
Case Else: arr = Array("その他")
End Select
PickIncomeSubCategory = CStr(arr(Int((UBound(arr) + 1) * Rnd)))
End Function
Private Function PickShopByCategory(cat As String, shops As Variant) As String
Select Case cat
Case "住居": PickShopByCategory = "家賃"
Case "光熱費": PickShopByCategory = CStr(Array("電力会社", "ガス会社")(Int(2 * Rnd)))
Case "通信": PickShopByCategory = "通信会社"
Case "交通": PickShopByCategory = "交通機関"
Case "医療": PickShopByCategory = "病院"
Case "サブスク": PickShopByCategory = "サブスク事業者"
Case "外食": PickShopByCategory = "外食店"
Case "日用品": PickShopByCategory = CStr(Array("ドラッグストア", "スーパー")(Int(2 * Rnd)))
Case Else
PickShopByCategory = CStr(shops(Int((UBound(shops) + 1) * Rnd)))
End Select
End Function
Private Function PickExpenseUnitPrice(cat As String) As Long
Dim v As Long
Select Case cat
Case "住居"
v = (Int(6 * Rnd) + 7) * 10000 ' 70,000 - 120,000
Case "光熱費"
v = (Int(8 * Rnd) + 3) * 1000 ' 3,000 - 10,000
Case "通信"
v = (Int(8 * Rnd) + 3) * 1000 ' 3,000 - 10,000
Case "交通"
v = (Int(20 * Rnd) + 2) * 100 ' 200 - 2,100
Case "医療"
v = (Int(40 * Rnd) + 5) * 100 ' 500 - 4,400
Case "サブスク"
v = (Int(15 * Rnd) + 5) * 100 ' 500 - 1,900
Case "外食"
v = (Int(60 * Rnd) + 5) * 100 ' 500 - 6,400
Case "食費"
v = (Int(60 * Rnd) + 1) * 100 ' 100 - 6,000
Case "娯楽"
v = (Int(80 * Rnd) + 2) * 100 ' 200 - 8,100
Case "交際"
v = (Int(100 * Rnd) + 5) * 100 ' 500 - 10,400
Case Else
v = (Int(80 * Rnd) + 1) * 100 ' 100 - 8,000
End Select
PickExpenseUnitPrice = v
End Function
Private Function PickExpenseQty(cat As String) As Long
Dim q As Long
Select Case cat
Case "住居", "光熱費", "通信", "サブスク"
q = 1
Case "外食"
q = Int(3 * Rnd) + 1
Case "交通"
q = Int(10 * Rnd) + 1
Case Else
q = Int(5 * Rnd) + 1
End Select
PickExpenseQty = q
End Function
Private Function PickIncomeUnitPrice(cat As String) As Long
Dim v As Long
Select Case cat
Case "給与"
v = (Int(21 * Rnd) + 20) * 10000 ' 200,000 - 400,000
Case "賞与"
v = (Int(41 * Rnd) + 10) * 10000 ' 100,000 - 500,000
Case "副業"
v = (Int(31 * Rnd) + 3) * 10000 ' 30,000 - 330,000
Case Else
v = (Int(51 * Rnd) + 1) * 1000 ' 1,000 - 51,000
End Select
PickIncomeUnitPrice = v
End Function
1. 家計簿_データ
- ダミーデータを含む元データシート
- 金額、収支、年月、週などはすべて数式
- 実データに差し替えても壊れない構成
2. EDA（探索的データ分析）
- 集計期間、総収入・総支出、差額
- 支出の平均、中央値、ばらつき
- 月別の支出・収入・差額推移
数式も入ることがわかる。たまに間違えることもあるのでどちらかというとこのテンプレを基礎としてピボットテーブルにした方がよい。
3. クロス集計
- カテゴリ × 月（支出）
- 支払方法 × カテゴリ（支出）
見やすいようにしたのと、数式がおかしい部分があったので修正をかけている。
4. 改善提案（何をすべきか／期待効果）
- 削減率を入力するだけで
年間・月間の削減効果を数式で算出 - 施策の優先順位を金額ベースで整理
元データから数字をとり、そこからどの程度数字が改善されるかがわかる。
活用の視点
全体像の把握
EDAシートを見ることで、毎月どのカテゴリにどの程度支出しているかを俯瞰できる。
特に支出・収入・差額を並べることで、黒字月と赤字月の構造が明確になる。
固定費が重いのか、変動費が膨らんでいるのかを初期段階で切り分けられる点は有効である。
無意識で出している支出はなにか？
クロス集計を確認すると、
- 特定の月だけ突出しているカテゴリ
- 特定の支払方法に偏った支出
- 毎月なんとなく発生している支出
といった「意識されにくい支出」が可視化される。
単純な合計表では得られない示唆である。
行動への接続
改善提案シートでは、
- 外食費を10%削減すると年間いくらか
- サブスクを整理すると月いくら改善するか
といった行動と金額の対応関係が明示される。
これにより、効果の小さい施策に時間を使わず、
費用対効果の高い施策から着手できる。
検証結果のまとめ
- 説明可能性の観点では、十分に実用的な分析テンプレートである
- 一方で、「なぜそうなっているのか」という仮説の根拠までは自動化できない
（これは業務知識や文脈理解の領域である） - しかし、テンプレートとしての完成度は高く、LLMの種類に依存しにくい
- 元データを差し替えるだけで、分析設計から実装までを効率化できる点は大きな利点である
おわりに
高度な分析をする手法ではないが、
分析に不慣れな人が、まず「使える形」に到達するための補助線としては有効になる。
また分析に慣れている人にとっては、詳細な分析設計を指示することで、より詳細に成果物をイメージできることが見込める。
Excelという既存資産とLLMを組み合わせることで、
「分析できない」ではなく「どう使うかを考える」段階に進める。
業務データへの応用も容易であり、
データ活用の初動を加速させる手段として有効であると考える。