こんにちは。ヒトツメです。
今日は、Excelの表で、途中計算を示す小計の入れ方と、どの方法によるのがベストかについて考えていきたいと思います。
Excelの小計機能
そもそも小計とは、項目ごと一部分の合計を指し、下の表でいうと、青くなっている行を指します。
赤い行には、青い行の合計が入り、こういったものを、一般に総計と言ったりします。
こういった合計の計算は、単純なSUM関数だと飛ばすべき行を人の手で指定しなければなりません。例えば、4月の「販管費合計」を出すために、単純に「=SUM(D2:D7)」としてしまうと、全てが合計されてしまい、「4,200」となってしまいます。
そこでExcelでは、データタブの中に「小計」という機能を持っており、グループ化した部分の合計値を自動で計算して出してくれる機能を持っています。
ただ、グループ化をしたくない何らかの事情があったりした場合、関数でこれを解決する必要があります。
SUMIFS関数を使う
そこで、一つの方法として考えられるのが、SUMIFS関数を使うことです。項目名に「小計」という文字列が使われている部分だけを足すように、総計行の関数を設定すれば、上記のように、二重に足されてしまう、という問題を解決できます。
例えば、次の図のような場合に、「=SUMIFS(B1:B10,A1:A10,”*小計*”)」と入れるという解決策です。
ちなみに、SUMIFS関数については、次の記事で詳しく解説しています。
また、「”*小計*”」と記載している、「*」（ワイルドカード）については次の記事で解説しています。
もっとも、SUMIFS関数だと、項目名の指定の仕方を間違えるとおかしな結果になってしまいます。「小計としてまとめているわけではないが、総計には含めたい」といった内容の行が発生した場合に、うまく処理できません。
AGGREGATE関数で解決する
そこで考えられる解決策は、AGGREGATE関数です。この関数は、様々な計算方法やオプションが用意されており、第一引数に「9」を入れると指定範囲の合計値を、さらに第二引数に「0」を入れると指定範囲内に含まれるAGGREGATE関数のセルを無視してくれます。
小計行にも同じようにAGGREGATE関数が使われている場合、「=AGGREGATE(9,0,B1:B10)」と入れることで、総計を得ることが出来ます。
AGGREGATE関数のセルを無視するので、何階層にもわたって小計が設定されている場合でも、総計をきちんと出すことが出来ます。
ちなみに、オプション内容（一部）を表にまとめると、次のようになります。
| 集計方法 | 関数 | |
|---|---|---|
| 1 | AVERAGE | 平均 |
| 2 | COUNT | セルの個数 |
| 3 | COUNTA | 空白でないセルの個数 |
| 4 | MAX | 最大値 |
| 5 | MIN | 最小値 |
| 6 | PRODUCT | 全部を掛けた値 |
| 7 | STDEV.S | サンプルに基づく標準偏差の推定値 |
| 8 | STDEV.P | 全ての値を用いた標準偏差 |
| 9 | SUM | 合計 |
| オプション | 動作 |
| 0 または省略 | 指定配列に含まれるSUBTOTAL関数とAGGREGATE関数を無視 |
| 1 | 非表示の行、指定配列に含まれるSUBTOTAL関数とAGGREGATE関数を無視 |
| 2 | エラー値、指定配列に含まれるSUBTOTAL関数とAGGREGATE関数を無視 |
| 3 | 非表示の行、エラー値、指定配列に含まれるSUBTOTAL関数とAGGREGATE 関数を無視 |
さいごに
このように、集計表の小計の出し方としては、様々な方法があります。グループ化が使える場合は、小計機能を用いるのも一つの手ですが、そうでない場合は、①項目名なども指定してしっかりと作りこむならSUMIFS関数、②比較的単純な階層構造になっている場合で簡単に作るならAGGRRGATE関数、と使い分けるのが良いと思います。
こういった集計表は、作った本人以外も関数をみるケースが多いので、周囲のExcelスキルに併せて作るということも意識しながら、色々な方法を使えると、より良いと思います。
コメント