SUM関数でエラーがあると合計できないそんな時は AGGREGATE 関数

あなたは、 AGGREGATE 関数ってご存じですか？列の合計や、行の合計は、SUM関数を使って簡単にできますよね。でも、ちょっと待ってください。もし、ひとつでもエラーのセルがあると、SUM関数はエラーとなってしまって、合計が計算できなくなってしまいますよね。

Excelで合計するときは、どのようにしていますか？

＝A1+A2+。。。と足し算を書いていますか？

それとも、関数を使って＝SUM(A1:A10)としてますか？

例えば、行（横）の計算で出た答えをさらに列（縦）に合計する様な表を作る時って、結構な頻度であると思います。　このとき、行の計算にエラーが起きる事って、あなたにも経験があるのではないでしょうか？

エラーが1つも有ってはダメな表なら仕方なく、エラーを取り除くしかありませんけど、1つくらいエラーは省いて合計しても問題がないんだけど、という時に、エラーをいちいち確認して直したり、しかたなく手入力で対応したりするのは、ちょっと面倒です。

そんな「エラーのセルは除いて合計を求めたいんだけど」ってときに時に AGGREGATE 関数をおすすめします。

とても優秀な関数なので、今まで「合計はSUM関数」と思っていたところを、全部 AGGREGATE 関数で置き換えても良いんじゃね？とすら思っています。

合計がエラーになって表が使えない事例

我が管理部では、毎月末に社員から月報を提出してもらうことになっています。

日々の労働時間を社員の方々に記録してもらい、総合計を計算しなくてはいけません。

部内で話し合い、月報をExcelに入力してもらって業務改善してみよう、ということになりました。

そこで管理部が入力フォームを作成して、全社員に共有し、毎日記録を入力してもらうことにしました。

そして、月末になり、社員全員に提出した貰ったところ、、、

社員から提出してもらったシートをチェックしてみると、簡単に完成しているはずの表は、合計にエラー表示というが多発していて、簡単どころではありません。

エラーの部分を調べて、訂正が必要な社員の人に話をして、再提出して貰います。

入力する社員は手間が増え、管理部の仕事も手間が増えてしまって、これでは、業務改善どころではない、最悪の事態となってしまいました。

合計するSUM関数を全てAGGREGATE関数に書き換えます。

例えば、上の例でいくとSUM(E3:E6）のところを、AGGREGATE(9,6,E3:E6)に書き換えます。

AGGREGATE関数の第一引数の 9 はSUM機能を指定する値、第二引数の 6 は「エラー値を無視します。」という条件を指定するものです。

E7の関数をAGGREGATE関数に変更したことで、エラーを除いた合計値 630 が表示されました。

以上です、使い方は簡単ですよね。

先ほどの項で、第一引数（集計方法）は 9 としてSUM関数の機能、第二引数（オプション）は 6 ですよと説明しました。
この項では、集計方法、オプションの他の番号はなにを意味するのか、説明します。

簡単なものを載せておきます。（他が必要なときは Microsoftサポートを参照ください。）

AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)

オプション	動作
0 または省略	ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
1	非表示の行、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
2	エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
3	非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
4	何も無視しません。
5	非表示の行を無視します。
6	エラー値を無視します。
7	非表示の行とエラー値を無視します。

関数がエラーとなったときに、その合計値を求める関数は、AGGREGATE関数を使うと、エラーのセルを無視して合計を求めることが出来ました。

オプションでは、非表示のセルも無視したり、小計のSUBTOTAL関数やAGGREGATE関数を除いて合計を求めるなど、3を指定すると実現できますので、用途に応じて使い分けることでより便利に使えます。

是非、使える関数の1つとして、記憶しておいていただきたいです。

それでは、以上です

SUBTOTAL関数の使い方については、SUM SUBTOTAL SUMIF 関数で総計小計条件で集計を求めるの記事でも書いています。