AGGREGATE関数 SUM ではできない入れ子やエラー値の影響を受けずに合計を計算する

エクセルで合計するときに使うのは SUM 関数が有名で、エクセルを使う人はほぼ誰でもが一度は使ったことがあると思います。

とても頻繁に使われる SUM 関数ですが、表にエラー値があると影響を受けて合計を計算できませんよね。

そんな時に、ぜひ使ってほしいのは AGGREGATE 関数です。

影響を受けないための対策では、IFERROR 関数などでエラー値を回避するという方法がありますが、毎回そのためにIFERROR 関数を書くのも面倒だし、数式がその分長くなってシンプルさを欠いてしまい分りにくくなってしまうと言うデメリットがあります。

合計する元のデータには手を加えず、無視して合計してくれれば良いのに、という願いを叶えてくれるのが AGGREGATE 関数です。

合計はSUM関数を使う
エラーが出てくるときは AGGREGATE 関数を使う
小計には SUBTOTAL 関数を使う
AGGREGATE まとめ

合計はSUM関数を使う

値の合計値を計算するときに最も単純で簡単な方法はSUM関数を使うことです。

合計のセルに＝SUM(と書いて次にマウスで範囲を選ぶ、それから括弧を綴じてEnterキーを押せば合計のできあがり。

図でSUM関数のパラメータが「E3#」となっているのは、スピルです。
スピルについては、こちらに少し記事を書いていますので参照してください。

または、合計範囲を選択しておいて[Shift]+[Alt]+[=]を同時に押せば自動で選択範囲をSUM関数で合計する式が範囲の下に入力されできあがり。一瞬です。

ショートカットについてはこちらにまとめていますので良かったら参照してみてください。

Point

範囲を指定するときにプラス一行空白のセルも同時に選択して[Shift]+[Alt]+[=]を押しても同じ動きになります。更に範囲＋下に1行と右に一列空白のセルを選択して[Shift]+[Alt]+[=]を押すと縦計と横計更に縦横計を同時にしかも一瞬で入力することができます

しかし、エラーのセルが一つでもあるとSUM関数は影響を受けてエラー表示になってしまいます。

入力が簡単で、すぐに合計できて便利な反面エラーに影響されるというデメリットがあるのです。

勿論それで問題なければ簡単ですばやく入力できるSUM関数をお勧めします。

エラーが出てくるときは AGGREGATE 関数を使う

それは嫌だという時は、エラーを避けて合計を計算してくれるAGGREGATE関数があります。

AGGREGATE関数は合計の他に平均（AVERAGE）やカウント（COUNT）、最大値（MAX）や最小値（MIN）と他にも色々な計算ができる関数です。

なので、設定パラメータが増えてしまいますが、その分、エラーへの対応や（後で説明する）入れ子にも対応できる優れものです。

是非覚えておいてください。

AGGREGATE関数でエラーを無視して合計を計算
第三引数に「E3#」となっているのはスピルです。
スピルについては、こちらに少し記事を書いていますので参照してください。

合計を求めるためにSUM 関数の代わりに使う時は、 AGGREGATE 関数の第一引数は9にします。

そして、エラーを無視するための設定値6を第二引数に設定します。（6はエラーを無視して合計する設定です。）そして、SUMと同じように合計のセル範囲または個別のセルを第三引数に設定します。

その他の設定値0～7で色々設定できますので以下のTIPSを参照してください。

第一引数：9
第二引数：6
第三引数：合計範囲または合計リスト

=AGGREGATE(9,6,A1:A5)
=AGGREGATE(9,6,A1,A2,A3,A4,A5)

例えば、A2にエラー値があった場合、SUM関数ではエラーとなりますが、 AGGREGATE 関数ではそのエラーのセルを無視して他の合計を得ることが出来るようになりました。

AGGREGATE 関数の合計モードでの第二引数の無視の条件は以下の通りです。

	入れ子	非表示	エラー
0	x
1	x	x
2	x		x
3	x	x	x
4
5		x
6			x
7		x	x

小計には SUBTOTAL 関数を使う

表の行数が少ないときは良いのですが、行数が増えてきた時は、一定の括りで小計を使うことで見やすい表にすることが出来ます。

SUM 関数で小計毎に範囲を指定して合計し、総計は各小計のセルを個別に指定して SUM 関数で合計することで対応したりします。

でもこのやり方は、ちょっと手間が掛かって面倒ですね。

1つ2つの場合でも面倒ですが、さらに小計がもっと多い場合は超大変ですし、行を追加したり減らしたりを色々やっているうちに、セル参照がずれてしまったりするので、始めからやり直しになった、なんてこともしばしば起こります。

行数が増えて見にくくなったときに、項目を分類しその分類ごとに並べて区分し小計を入れると分かりやすい表に仕上げることが出来ます。

こういう場合、分類ごとの小計はSUM関数ではなく SUBTOTAL 関数を使います。

そして、総合計も SUBTOTAL 関数を使って全体を範囲選択します。便利なのは途中にある SUBTOTAL 関数を除く必要がないこと。 SUBTOTAL 関数が勝手に途中のSUBTOTAL関数の計算結果を除いて計算してくれますから、個別にセルの選択をする必要がなくセル指定がずれてまたやり直しということがなくなります。

SUBTOTAL 関数も多機能関数です。合計として SUM 関数の代わりに使う時は、SUBTOTAL関数の第一引数は9にします。 SUBTOTAL 関数については、こちらの記事にも解説していますから参照してみてください。

第一引数：9
第二引数：合計範囲または合計リスト

=SUBTOTAL(9,A1:A5)
=SUBTOTAL(9,A1,A2,A3,A4,A5)

SUM関数と同じで、一つでもエラーのセルがあると SUBTOTAL 関数も影響を受けてエラー表示になってしまいます。

やはり、エラー表示が嫌だという場合は、これも AGGREGATE 関数を使います。

使い方は先ほどの説明とほぼ同じですが今回は合計関数の入れ子もありますから、総合計の第二引数には2を設定します。

この設定はエラーと入れ子を無視して合計の意味です。また、途中にある小計のSUBTOTAL 関数の代わりにはエラーを除く設定の第二引数6で AGGREGATE関数を使います。

SUBTOTAL 関数の代わりに AGGREGATE 関数を使う
小計の AGGREGATE 関数の第二引数は、エラー無視の6を設定する
総合計の AGGREGATE 関数の第二引数は、エラーと入れ子無視の設定の2を設定する

いちいち第二引数を考えて設定するのが面倒なので、どちらも2で設定しても問題ありません。

第一引数：9
第二引数：小計＝6、合計＝2（いちいち面倒な場合はどちらも2）
第三引数：合計範囲または合計リスト

=AGGREGATE(9,2,A1:A5)
=AGGREGATE(9,2,A1,A2,A3,A4,A5)

これで、途中にエラーが起きても小計や合計に影響を受けない表が完成しました。

総合計のセル指定もシンプルで簡単ですから保守性にも優れていて、行を追加したり修正したり、再利用の場合も手間が少なくてすみますので是非利用してみてください。

最後に AGGREGATE 関数の第二引数で設定できる項目の「非表示を無視」は、（行番号をクリックして行選択し右クリックメニューから非表示を選択して）行を非表示にしたときに、その行は合計に含めないということです。また「入れ子を無視」は SUBTOTAL 関数と AGGREGATE 関数を除いた合計になります。

AGGREGATE まとめ

合計はSUM関数を使う
エラーを無視して合計するときは AGGREGATE 関数を使う
途中に小計を入れるときは SUBTOTAL 関数を使う
エラーと合計の入れ子をどちらも無視するときは、 AGGREGATE 関数

どうでしたでしょうか。
本当に簡単な表の場合は SUM 関数でサッと作成、それ以外は AGGREGATE 関数を使うことをお勧めします。

今回は以上です。
長文でしたが最後まで有り難うございました。