Excelで関数だけを使って月別総括集計表を作る1

Excelで関数だけを使って(自動集計できる)月別総括集計表を作る

当ページで提示している月別総括集計表は、東京都三鷹市の商工会にある「記帳の手引き」の表を基に作成しました。
※Excelのバージョンもあがりましたので少し手直しして(Excel2016)再掲しています

私がExcel用にテンプレート化した月別総括集計表は、個別明細の入力をすると費目単位に「自動集計される」部分に特徴があります。

百聞は一見にしかずですね・・以下のとおりです。

上段の「入出金表」のシートに明細データを入力するだけで、
下段の「月別総括集計表」シートの関数が自動的に月、費目を分別、合算して表示してくれるスグレモノなんです。
(手前味噌で恐縮ですが、、、)

今回作成したExcel版では「入力用」シートに入力した内容が自動的に「集計」シートに反映されます。

この表自体は確定申告用資料作成の段階で、あるいは少し形を変えれば、より適した家計簿に役立つ表ですね。
ダウンロードできるようにしていますので関数を使う部分など参考にしていただきたくです。

Excelファイルとしての月別総括集計表

Excel版としてのざっくりな使い方は当ページをご覧いただきたいのですが、このExcel版を使うことにより、「月ごと、費目ごと」の振り分けが自動で行えるだけでなく、ご自身の改良次第では時系列に見たり、前年比較など応用(経営管理)的な使い方もできます。

当ページでは、「紙　→　Excel版」に変える部分について説明しています。

Excelでの月別総括集計表の作り方の概要

表の内容の細かい点なども含めダウンロードファイル(サンプル)及び別ページで確認いただきたいと思いますが、Excelで作成した月別総括集計表は、実はシンプルな関数を組み合わせて使っているだけですね。

また再作成した月別総括集計表では、配列数式を廃止にして、作りやすさを優先しました。
(その変わり、後述する入力する側のシートに1列追加していますが、このほうが改修しやすいですね)

それでは、作成する上でのポイントについて説明します。

シートは2個だけで作れる

使用する最低シート数は2個だけです。
ただし、今回の説明に使用したファイルでは、費目一覧などのシートもつけています。ダウンロードも同様です。

■1枚目のシート：「入出金表」シート(セル選択部分に関数をTEXT関数を使用)


このシートが「入力専用」になります。
利用者はこのシートのみに明細を入力するイメージです。

なお、セルで選択している部分に「月」を表示するために「TEXT関数」を使っています(この関数を使うことで配列数式を回避しています)。

これは、A列の年月日に入れた値を参照して「月」の部分だけ表示するために使っています。
つまり、もう一つのシートである「月別総括集計表」シートは、ここB列の「月」を見るようにしています。

■2枚目のシート：「月別総括集計表」シート(関数使用、上記の「入力用シート」から自動的に判別して表示)

■月別総括集計表シートの全体図

全体画像のセル選択部分にも式が入っています。ほぼ全体ですね(笑)。

一見すると、「うわーナニコレ！？」とか思われるかもしれませんが、実際は一つのセルに入れた式をコピーしまくり状態です。
その実体が関数の「SUMIFS」と「SUM」なんですね。

使用する関数は、ほぼ「SUMIFS」だけで作れる

月別総括集計表シートでは、「費目と月ごと」を求めるために関数を使いますが、そのための関数は「SUMIFS関数」一個でいけます。
もちろん、というか、このシートにある黄色のセルについては、「SUM関数」、と「引き算の数式」を使っていますが、その他のセルについては「SUMIFS」関数しか使っていません。

この、SUMIFS関数を入力するとき、絶対参照(＄マーク)を入れて作ることで、コピペするだけでOKという算段です。
(さらに表の間にある合計行を飛ばしてコピペできます、式がずれません)

(関数の説明の前に)まずは月別総括集計表のダウンロードで内容を見る

実際に関数の説明などは別ページにて説明しています。
その前にまずは、このファイルをダウンロードしておくことをおすすめします。
SUM関数はしばしば使うことはあるかもしれません。しかし(そこまで難しいことはしていませんが)、「SUMIFS」関数に馴染みが無い方もいらっしゃると思います。

画像だけで月別総括集計表シートを作るのも相当シンドいと思います(笑)。
そして、私も細かい説明をするのはシンド・・いや難しいのですね(笑)。

そのためにもまずはダウンロードしておいてくださいませ。