家計簿の費目別の合計をSUMIFSで出す方法(予算との差と、費目名がずれたときの落とし穴)【LibreOfficeで計算して確認】

家計簿の明細が1行ずつ並んでいて、費目ごとの合計と、予算との差を自動で出したいときの式を紹介します。明細の費目名がきれいにそろっていない場合に、どう数え漏れるかも、実際に計算して確かめました。

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/category_sum.py)。外部サイトの説明は参照していません。Excelでは未確認です。使う関数はExcelにもありますが、本番のデータに使う前に、数行で結果を確かめてください。

先に結論

費目別の合計は SUMIFS(金額の列, 費目の列, 費目名) で出せます。
予算との差は、予算のセルから合計を引くだけです。超過の判定は IF で付けられます。
明細の費目名が1文字でもずれると、その行は合計に入りません(エラーにもなりません)。末尾のスペースが混ざった「食費」は「食費」と別物として扱われました。
取りこぼしを見つけるには、「全明細の合計」と「費目別の合計の合計」を並べて、差を見る方法が簡単です。

表の作り方

明細はA列が費目、B列が金額で、2行目から36行目まで入っているとします。別の場所(D〜H列)に費目ごとの集計表を作ります。

列	内容	式(2行目の例)
D	費目名	食費(手入力)
E	予算	40000(手入力)
F	合計	`=SUMIFS($B$2:$B$36,$A$2:$A$36,D2)`
G	予算との差	`=E2-F2`
H	判定	`=IF(F2>E2,"超過","範囲内")`

範囲に $ を付けて固定してあるので、2行目の式を下へコピーすれば、日用品・交通費…の行も同じ形で計算できます。

実際に計算した結果

食費・日用品・交通費・光熱費・通信費・趣味の6費目で、明細を入れて計算しました。予算は食費40,000円、日用品8,000円、交通費10,000円、光熱費12,000円、通信費9,000円、趣味15,000円としました(試すための値です)。

費目	合計(F)	予算との差(G)	判定(H)
食費	8,031	31,969	範囲内
日用品	7,850	150	範囲内
交通費	2,147	7,853	範囲内
光熱費	14,775	-2,775	超過
通信費	9,957	-957	超過
趣味	7,490	7,510	範囲内

6費目の合計は50,250円でした。全明細の合計は58,361円で、8,111円が費目別の合計に入っていません。ここから、取りこぼしの中身を調べます。

取りこぼしの正体: 費目名のずれ

今回の明細には、あえて次の5行を混ぜていました。

明細の費目名	金額	「食費」の合計に入ったか
`食費`(末尾に半角スペース)	1,234	入らない
`食費`(末尾に全角スペース)	777	入らない
`食費(外食)`	2,500	入らない
`食費(自炊)`	3,100	入らない
(費目が空欄)	500	入らない

足すと 1,234 + 777 + 2,500 + 3,100 + 500 = 8,111円で、取りこぼしの差額とちょうど一致しました。目で見るとどれも「食費」ですが、SUMIFSは文字が完全に同じものだけを数えます。

直し方は2通り

1. 前方一致にする(「食費(外食)」なども含めたいとき)

条件の費目名の後ろに "*" をつなぎます。

=SUMIFS($B$2:$B$36,$A$2:$A$36,D2&"*")

食費は 15,642円になりました。もとの8,031円に、末尾スペースの2行と「(外食)」「(自炊)」の行(合計7,611円)が加わった数字です。ただし、「食費」で始まる別の費目(たとえば「食費以外」)があると、それも混ざります。費目名の付け方に合わせて使ってください。

2. 前後と全角のスペースを除いてから完全一致で数える

スペースのずれだけを直したいときは、SUMPRODUCT で次のようにします。

=SUMPRODUCT((TRIM(SUBSTITUTE($A$2:$A$36,"　"," "))=D2)*$B$2:$B$36)

食費は 10,042円になりました。もとの8,031円に、スペースのずれた2行(1,234円と777円)が加わった数字で、「食費(外食)」と「食費(自炊)」は入りません。金額の列は数字だけの表で確かめています。

どちらの式でも、ほかの5費目の結果は変わりませんでした。

つまずきやすい点

取りこぼしはエラーにならないので、合計の合計と全明細の合計を並べる確認が必要です(上の例では =SUM(B2:B36) と =SUM(F2:F7) の差が8,111円でした)。
費目名を入力規則のプルダウンにすると、ずれそのものを減らせます(この記事ではプルダウンの設定は検証していません)。
予算の判定は「合計 > 予算」だけです。予算の立て方や、家計の良し悪しの判断はしません。

注意点: この式ができないこと

費目名のずれを自動で正しい費目に直すことはしません。式で数え方を変えるだけです。
複数の月が混ざった明細では、月の条件が別に必要です。その式は日付の列から月別に合計を出す方法で計算して確認しています。
家計の助言や、節約できる金額の保証はしません。

家計簿のひな形が必要なら

この記事の式は、自分で作った表にそのまま使えます。費目別の集計つきの家計簿を最初から探しているなら、運営者が作った月間家計簿(Excelテンプレート・400円)があります。A4で印刷できます。ただし、複数の月をまたいだ通年の集計はできません(1か月ごとに使う形です)。Excelの確認はWindowsデスクトップ版16のみです。この記事の方法で足りる場合は、買う必要はありません。

先月より増えた・減ったの割合を知りたいときは、無料の割合・増減率の計算ツールも使えます(入力は外部に送信されません)。月ごとの繰越の式は、前月繰越を翌月に引き継ぐ方法で確認しています。

まとめ

費目別の合計は SUMIFS で出し、予算との差は引き算、超過は IF で判定する
費目名が1文字でもずれた行は、エラーなしで合計から漏れる(今回は35行中5行・8,111円)
前方一致(&"*")かスペースを除いた完全一致で拾えるが、拾う範囲が違うので表の作りに合わせて選ぶ
式はいずれもLibreOfficeで計算した結果。Excelでは未確認のため、本番の表に使う前に数行で確かめる

確認方法: この記事の式は、LibreOffice Calcで実際に再計算し、35行の明細(費目名の末尾スペース、全角スペース、「食費(外食)」「食費(自炊)」、費目が空欄の行を含む)について、6費目×5種類の式の結果と合計3件の計33件が、独立に計算した期待値と一致することを検証スクリプト(site/verify/category_sum.py)で確認しました。SUMIFS・SUMPRODUCT・TRIM・SUBSTITUTE・IF・SUMはExcelにもある関数ですが、Excelでは未確認です。外部サイトの記述は参照していません。

参考にした情報

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/category_sum.py)