TOPページ > Excel・IT開発 > Excel実務のノウハウ > Excelで関数だけを使って月別総括集計表を作る1

Excelで関数だけを使って月別総括集計表を作る1|あべっきぃの超Excel

このエントリーをはてなブックマークに追加

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

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

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

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

自動集計できる月別総括集計表

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

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

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


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

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

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


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

表の内容の細かい点なども含めダウンロードファイル(サンプル)及び別ページで確認いただきたいと思いますが、Excelで作成した月別総括集計表は、実はシンプルな関数を組み合わせて使っているだけですね。
また再作成した月別総括集計表では、配列数式を廃止にして、作りやすさを優先しました。
(その変わり、後述する入力する側のシートに1列追加していますが、このほうが改修しやすいですね)


とりわけExcelのバージョンがあがり、「SUMIFS」という関数が使えるようになり作りやすくなったと思います。

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


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

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


  • 入力用シート → このシートに明細を入力(入力はこのシートだけ)
  • 集計シート  → 入力された明細が自動的に振り分けられる(閲覧専用)

■1枚目のシート:「入出金表」シート(セル選択部分に関数をTEXT関数を使用)
規則正しく入力するだけでOK
このシートが「入力専用」になります。
利用者はこのシートのみに明細を入力するイメージです。

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

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

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

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

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

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


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

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

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


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

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

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

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


このページの執筆者について

Excel実務のノウハウ 関連ページ


グローウィン有限会社
東京都三鷹市下連雀3-27-1 三協ビル2階-B 地図を見る
トップページ
入力代行関連
Excel・IT開発
中国アテンド
会社概要
プレスリリース
お問い合わせ
お問い合わせ内容一覧

英文ページ(English)
中文ページ
プレスリリース お問い合わせフォーム