こんにちは。ヒトツメです。
前回は集計表の枠線の作り方について解説していきましたが、今回は、集計表の中身を関数で作る作り方について考えてみたいと思います。
集計表を作るのはどんなときか
はじめに、そもそもなぜそんな集計表を作るかという話ですが、家計簿をつけるときにサマリーシートを作って一覧化したい場合や、経理処理のときに日々処理している伝票データから試算表を作る場合があげられます。
日々処理している明細表から、それを集計した表を作成し、見やすいデータを作り、報告や管理に利用するということです。業務以外でも、先に挙げた家計簿を作る場合など、使う場面は様々です。
一般的には、そういった集計データを作る場合、初めから、そういった報告用のデータを作るというのも一つの手段です。しかしながら、操作量が多いため、毎月作るのが手間という点であまりお勧めできません。また、少し報告シートの中身を変えたいと思ったときに、改変しにくいというのがネックになります。
なので、明細表を作り、そこから関数等を使って集計データを作ることを強く推奨します。この時、もちろんピボットテーブルを使うというのも一つの手段ですが、ピボットテーブルだと、レイアウトがある程度決まっているため、意外と万能ではありません。そこで、今回のように、関数でこれを作る作り方を覚えておく方が良いと思います。
三つの○○IFと○○IFS
大抵、集計というと合計値を出すのが大半ですが、こういった集計データを作るための関数として、Excelでは、○○IFや○○IFSと呼ばれる関数が存在します。
一覧にすると次のような形です。
| IF | IFS | ||
| SUM | SUMIF | SUMIFS | 合計 |
| COUNT | COUNTIF | COUNTIFS | 個数 |
| AVERAGE | AVERAGEIF | AVERAGEIFS | 平均 |
| 一つの条件のみ | 複数条件に対応 |
いくつもあって覚えるのが大変！と思われるかもしれませんが、やっていることは基本的に二つだけなので、そこまで大変ではありません。
○○IF関数
まず、左側にある○○IFという関数から見ていきます。
例えば次のような表があるとして、「1,500円以上の明細に関する合計値が欲しい」と思った場合、次のような関数を使います。
=SUMIF(C:C,”>=1500″)
例えば「=SUM(C:C)」と記載すると、C列のすべての数値が合計された金額が表示されますが、SUMIF関数は、二つ目の引数に用いられた条件に該当するもののみを結果として出します。
この時、上記のように「”>=1500″」とすることで、「1,500円以上のもののみ」という条件を付与しており、結果としてこの条件に該当するものが合計された金額が結果として出されることになります。
おなじように、AVERAGEIFなら、条件に該当するものの平均値を、COUNTIFなら、条件に該当するものの個数を、結果として返却します。
○○IFS関数
ただ、これだと、集計表を作ることはできません。というのも、例えば「食費に該当するものの合計値が欲しい」となった場合、金額を見るだけでは判断できず、費目も見る必要があるからです。
こういった場合に役に立つのが〇〇IFS関数です。食費だけを切り出す場合、次のような記載をします。
=SUMIFS(C:C,A:A,”食費”)
このように、〇〇IFS関数では、二つ目の引数に配列を、三つ目の引数に配列に対する条件を入れます。条件の対象となる配列と条件は、基本的にいくつでも入れることができ、例えば「4月の食費」とすれば、次のように記載することで、結果を出すことができます。
=SUMIFS(C:C,A:A,”食費”,B:B,”>=2022/4/1″,B:B,”<2022/5/1″)
この場合も、AVERAGEIFSなら平均を、COUNTIFSなら個数を結果として返却します。
途中経過を出すFILTER関数
このように、○○IFS関数を使うことで、特定の条件に該当する数値の合計や平均、個数を出すことができるので、集計表は関数で意外と簡単に作れてしまいます。
ただ、合計や平均、個数など、結果だけではなく、条件に該当する「配列」を結果として返却することはできないのか、という疑問がわいてきます。
そこで出てくるのが、FILTER関数です。
例えば、今回の例で次の式を入れたときに出てくる結果は、「3,600」ですが、合計をする前の、「={1000;1200;1400}」の部分を切り出して結果として返却することはできないのか、ということです。
=SUMIFS(C:C,A:A,”食費”,B:B,”>=2022/4/1″,B:B,”<2022/5/1″)
この場合、最近実装されたFILTER関数を使って、次のようにして出すことができます。
=FILTER(C:C,(A:A=”食費”)*(B:B>=DATE(2022,4,1))*(B:B<DATE(2022,5,1)))
FILTER関数は、第一引数の配列に関して、第二引数の条件がTRUEになるもののみを配列として返却します。複数条件をANDでつなぐ場合には、それぞれの条件を「()」でくくって「*」で繋げてあげることで対応可能です。ちなみに、ORでつなぐ場合には、「+」で繋げることで対応可能です。
このようにすると、結果として、「={1000;1200;1400}」という結果が返却されることとなります。
以上より、次の二つは同じことをしており、同じ結果を返却することになる、ということがわかります。
=SUMIFS(C:C,A:A,”食費”,B:B,”>=2022/4/1″,B:B,”<2022/5/1″)
=SUM(FILTER(C:C,(A:A=”食費”)*(B:B>=DATE(2022,4,1))*(B:B<DATE(2022,5,1))))
さいごに
集計表を作るという話から、最後は最新関数のFILTER関数の話へと、少し話は逸れてしまいましたが、このように、関数を駆使することで、明細データ含まれるデータベースと、そこから作り出される集計表を分けて管理することが可能になります。
その最大のメリットはやはり、明細データに継ぎ足しをしたりしても同じように結果が自動で得られる、ということと、集計表の形式を変えたい場合でもあまり手間をかけずに修正をすることができるということです。
過去作られていなかったような集計データでも、過去の明細を同じ形式で保存し続ければ、過去との比較も簡単にできるので、こういった関数を駆使して、データをうまく管理できるようになると、業務効率化が一気に図れると思います。
コメント