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

更新日：2020-03-08

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

Excel版の月別総括集計表について、今回は具体的にどんな関数が使用されているのかなどを説明します。

月別総括集計表シート

※当ページはこちらのページの続きです。
また、ここで説明しているファイルはダウンロードできますので確認しながら見てもらえるとよいと思います。

月別総括集計表ファイルの構成

基本的にはダウンロードしたファイルを見ていただければ分かりますが、このファイルは、管理がしやすいように3シート構成にしていますが、実質2シートで成り立っています。

入出金表シート
月別総括集計表シート

この2つのシートを使っており、それぞれのシートに役割をもたせています。
1つのシートが「入力用」でもう一つのシートが「集計用」です。

ちなみにこの作りはAccessに見られる作り方ですね。「テーブルとレポート」が今回の作りに当たります。次のページも参考になるかもしれません。
ExcelとAccessの違いをExcelの目線で言うと・・

月別総括集計表シートの関数

若干例外はありますが、基本的にこの月別総括集計表ファイルは、2つのシートから成り立ち、「月別総括集計表シート」で表示されるデータは、もう一方のシートである「入出金表シート」のものです。

この月別総括集計表の技術的なキモは「月別総括集計表シート」です。月別総括集計表シートの各セルに「SUMIFS関数」を入力しています。
SUMIFSで出来上がっている表

「SUMIFS関数」ほぼ単一の関数で成り立っています。

ここで取り上げている関数は「SUMIFS」です。非常に似ている機能と名前に「SUMIF」という末尾に「S」がつかない関数があります。
そして本文以下で登場するのは「SUM」です。混乱しないようご注意ですね。

「ほぼ」ですので実際には他にも関数を使っています。
それは、これはメジャー関数の代表である「SUM関数」、と「数式(引き算)」ですね。
SUM関数と数式

このシートで使用している関数はこれらだけです。シートの末尾に備考列などありますが、基本的にこのシートでは、もう一方の「入出金表」シートのデータを集計するためのシート構成にしています。

つまり極端な言い方をすると、月別総括集計表シートでは「セルの入力を想定していない→関数と数式の集計だけ」というイメージになります。

入出金表シートの関数

では、入出金表シートではどうでしょうか？
当ページ加筆前の説明ではなかったのですが、このシートにも一つ関数を入れました。「TEXT関数」です。
入出金表シートで使用している関数はTEXT関数のみ
TEXT関数を入れているのは、A列に入力されている「年月日」から「月だけ」の表示をさせるためです。というのも、この「月」列で表示されている値を、月別総括集計表シートに入れているSUMIFSの条件で使用するからです。

なお、TEXT関数を入れたほうが個人的に便利と思い入れていますが、手入力で行うならばこのシートに関数は不要になります(つまり必須の関数ではないということですね)。

SUMIFS関数について

月別総括集計表シートで使用している「SUMIFS関数」をなぜ使っているのか・・それは、今回の月別総括集計表シートの構成にピッタリ合うからです。

この月別総括集計表シートでは「2種類の集計」から成り立っています。

費目単位で集計する
月単位で集計する

それぞれの単位(粒度)で集計します。「費目単位 × 月単位」というルール？で集計する必要があります。
そして、それを実現するために、SUMIFSを使用するという流れになっています。

合計値算出に複数の条件があるときはSUMIFS関数が便利

SUMIFS関数は複数の条件に合致する数値を合計できます。
はい、まさにうってつけです(笑)。

すでに月別総括集計表シートを見ておわかりの方もいらっしゃるかもしれませんが、この集計の構成はまさにピボットですね。クロス集計というやつですね。

ダウンロードできますサンプルでは、このSUMIFSをコピペできるようにセルの参照で「絶対参照」にしている部分もあります。集計セルのかたまりですから必須です(笑)。

ただ、そのため少し難しく見えるかもしれませんが、SUMIFSは、先に挙げた2つの条件を満たすようにセルを入れているだけですね。

SUMIFSが見ているセルの場所
SUMIFSの引数がそれぞれどこを見ているのかは上記のとおりです。が、、ちょっと分かりづらいですのでサンプルを直接確認いただければと思います。

月別総括集計表ファイルのダウンロード(サンプル)ファイルについて

なお、ダウンロードできるサンプルファイルには、「費目一覧」というシートがついています。これは、「入出金表」シートの費目列での選択用に「入力規則」という機能を使っています。
「費目」列の値を選択式にするために使っています。
これも費目を手入力するならば不要な機能です。
※ただし、費目の入力間違えをすると「SUMIFS」で見つからなくなりますのでご注意です。

Excel化した月別総括集計表ファイルはダウンロードできます。
今回および前回のページの参考にしてください。

ダウンロード用ファイル、ツールのご利用について

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