会計ソフトなどから出力した推移表の金額をもとにして、平均値か中央値で予測の収支金額を入力するExcelマクロ

2025年8月3日

独立して以降、会計ソフトなどから残高試算表を推移表形式で出力し、Excelで加工を行って報告資料などとして使用しています。

よくやる作業としては会計ソフトに入力し確定させた毎月の実績値から、今後の見通しなどを会社の代表者と相談しながら予想し、1年間の収支の予想を計画・作成し、作成した予想の金額に基づく納税予測や納税の計画などもサービスとして提供しています。

個人事業主やフリーランスの人であれば、毎年1月1日から12月31日までの1年間が事業年度になり、この期間に関して、経理をしていくことになりますから、例えば1月から6月までは会計ソフトなどにデータ入力が完了し、売上や経費などの数値が確定している場合、その確定した数値をもとに7月～12月までの（まだ数字が確定してない）今後の数字を予測する。といったイメージになります。

作業としては、Excelを画面に表示したうえで代表者から今後の事業の予定や見通しなどの話を伺い、それらを参考にして予想の数値入力し、入力した数値をパソコンの画面でその場で確認してもらっています。

手作業でも全く問題はないのですが家賃や、税理士報酬、サブスクリプションサービス、水道光熱費など、毎月定額のものや、定額ではないけど毎月大きな変動がないものについては、ある程度入力作業を自動化し、予想の収支を作成する際の「たたき台」にできれば便利かなと思いました。

で、自動化する場合はどのようにしたらよいか生成AIなどの力を借りながら考えて作ってみたので、その過程や考え方を記事にしてみようと思います。

会計ソフトなどから出力した推移表の金額をもとにして、平均値か中央値で予測の収支金額を入力するExcelマクロのイメージ

文章でやりたいことをつらつらと書いてきましたが、文章だけだとイメージが掴みにくいところがありますので、マクロの実行動画を作ってみました。

実際に動作しているところを見ていただく方がやりたいことの理解が早いかと思います↓

前提条件としては会計ソフトから出力した残高試算表等の金額を、推移表形式のひな型に貼り付けた状態でマクロを実行し、まだ金額が確定してない部分（動画であれば7月～12月分）の予想をそれまでの「平均値」または「中央値」のどちらが好きな方で作成する。といったマクロになります。

会計ソフトなどで特定の月まではデータ入力が完了し、金額が確定した状態でデータを出力することを前提しているので、

売上は6月まで入っているが、経費については4月までしかはいっていない。という状態は想定はしていませんが、仮にそういった場合においても予測で金額を入力ことができるように、一応設定をしています。

（動画の方の35秒以降部分を見てもらえれば確認できます）

会計ソフトなどから出力した推移表の金額をもとにして、平均値か中央値で予測の収支金額を入力するExcelマクロコードとその内容

今回生成AIなどを参考にしながら作成したマクロが下記のコードになります↓

Option Explicit

Sub 平均を計算して表示()

'■実績の収支金額を元にして平均値や中央値で予想の収支推移表を作成するマクロ

'変数宣言

Dim i As Long
Dim rng As Range
Dim choice As Long
Dim account_name As String
Dim lastrow As Long

lastrow = Cells(Rows.Count, 2).End(xlUp).Row

'■処理実行部分

For i = 3 To lastrow

    account_name = Range("b" & i).Value  'B列に記載された勘定科目名を変数へ格納
    Set rng = Nothing '変数を初期化
    
    On Error Resume Next

        'C列～N列のうち空白になっているセルを変数に格納する。
        
        Set rng = Range(Range("c" & i), Range("n" & i)).SpecialCells(xlCellTypeBlanks)
    
    On Error GoTo 0
        
    If Range("t" & i).Value = "no" Then  'T列に「no」と入力されている場合
    
        '※粗利益や小計などの集計項目については収支予測は行わない。
        '予想不要のデータの場合、T列に「no」と入力する
        
    ElseIf rng Is Nothing Then   'C列～N列のうち空白セルがない場合
        
        MsgBox "【" + account_name + "】には空欄がないため予想収支を作成しません。"
     
    Else
    
        rng.Select
        
        choice = MsgBox("【" + account_name + "】の予想について、平均値か中央値を選択してください" _
        & vbCrLf & "平均値で予測する：はい（Y）をクリック" _
        & vbCrLf & "中央値で予測する：いいえ（N）をクリック" _
        & vbCrLf & "予測しない場合：キャンセルをクリック", vbYesNoCancel + vbInformation)
     
        Select Case choice
        
        Case vbYes
           
           rng.Value = Range("r" & i).Value '平均値（R列で計算した金額）で予想金額作成
           rng.Interior.ColorIndex = 36
        
        Case vbNo
           
           rng.Value = Range("s" & i).Value '中央値（S列で計算した金額）で予想金額作成
           rng.Interior.ColorIndex = 34
        
        Case vbCancel
           
           ' 処理スキップ（何もしない。次の勘定科目へ）
        
        End Select
    
    End If

Next i

MsgBox "予想金額の入力完了", vbInformation

End Sub

大まかな流れとしては会計ソフトなどから出力した実績値の推移データをExcelに貼り付けたタイミングで平均値や中央値をエクセルのセルで計算させておき、その計算結果を使って予想の金額を入力して行く形になります。