エクセルでAGGREGATE関数を使ってエラーを無視して合計を求める方法

「エクセルで合計値を求めたいのに、エラーが混ざっていたら合計が求められない。。。」
そんな疑問を持っていませんか？

そんな時に便利なのが「AGGREGATE関数」です。
この記事では、エクセルを使ってエラーを無視しつつ合計を求める方法を詳しくご紹介します。

さらに、AGGREGATE関数は足し算だけでなく、さまざまな集計処理を行うことができます。
最大値や平均値、カウントなどもエラーを無視して計算できるので、とても便利です。

基本的な使い方から応用的なテクニックまで、初心者の方でもすぐに実践できる内容ですので
ぜひ最後までお読みください。

【　この記事の概要　】

この記事を見てできるようになること

この記事を読めば、以下のことができるようになります。

AGGREGATE関数の基本と概要

AGGREGATE関数は、エラー処理を行いながら合計や平均などの計算ができる便利な関数です。
具体的な機能は次の通りです。

AGGREGATE関数の構成

より詳しい情報は、Microsoft公式 – AGGREGATE関数についてを参照してみてください。

AGGREGATE関数の「 集計方法 」の対応番号について

AGGREGATE関数は、次のような関数を選択して使用することができます。

「集計方法」の引数に対応する番号を入力して、目的の関数を実行します。

AGGREGATE関数の「 オプション 」の対応番号について

AGGREGATE関数は、どのような条件で関数を処理するか？を選択できます。

「オプション」の引数に対応する番号を入力して、条件を指定してください。

AGGREGATE関数がよく使われる場面

AGGREGATE関数は、特定の条件下での集計作業において非常に有用です。
以下のような場面で特に役立ちます。

AGGREGATE関数の注意ポイント

AGGREGATE関数を使用する際には、いくつかの注意点があります。
以下の点に留意して使用することで、正確かつ効率的なデータ処理が可能になります。

AGGREGATE関数の使用例をご紹介

それでは、AGGREGATE関数の使用例を2つ紹介します。

以下に紹介する使用例は、すべてコピペしてすぐに実行できるコードです。
気になる例は、ぜひご覧いただき、実際に実行してみてください。

使用例1: エラーを無視して合計を求める

エラー(#N/A)を無視して合計を求める方法を紹介します。

例えば、このようなエクセルデータがあるとします。

合計値を求めたい場合に、範囲内にエラー値(#N/A)が含まれていると「SUM関数」では、計算することが出来ません。
計算結果も、エラー(#N/A)となってしまいます。

エラーを無視して合計を求めたい場合、次のように「AGGREGATE関数」を記述します。

=AGGREGATE(9,6,$B$2:$B$6)

引数の解説

この例では、引数を下記の通り設定しています。

引数の解説

---

• 集計方法　⇒　9
  • 「9」は、「SUM：合計」を意味します。
    ※集計方法一覧をもう一度見る。
• オプション　⇒　6
  • 「6」は、「エラー値を無視します。」を意味します。
    そのため、エラーのセルを無視して合計を計算しています。
    ※オプション一覧をもう一度見る。
• 範囲　⇒　$B$2:$B$6
  • B2からB6までの範囲を合計します。
    ※このように範囲を指定する場合は、絶対参照（$記号）を使用すると、オートフィルを行っても範囲がずれることはありません。

計算結果

このように「AGGREGATE関数」を使用することで、エラーを無視して範囲内の数値を合計することができます。

使用例2: フィルターを使用してデータを絞り込んだ状態の合計

表でフィルターを使用してデータを絞り込んだ場合の合計を求める方法を紹介します。

例えば、このようなエクセルデータがあるとします。

フィルターを使用して商品名「商品A」でデータを絞り込んだ場合、「SUM関数」を使用すると、フィルターを適用した後でも、全てのデータの合計を表示してしまいます。

フィルターで非表示になった行を無視して合計値を求めたい場合、次のように記述します。

=AGGREGATE(9,3,$B$2:$B$6)

引数の解説

この例では、引数を下記の通り設定しています。

引数の解説

---

• 集計方法　⇒　9
  • 「9」は、「SUM：合計」を意味します。
    ※集計方法一覧をもう一度見る。
• オプション　⇒　3
  • 「3」は、「非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。」を意味します。
    そのため、フィルターで非表示の行を無視して合計を計算しています。
    ※オプション一覧をもう一度見る。
• 範囲　⇒　$B$2:$B$6
  • B2からB6までの範囲を合計します。
    ※「$」マークは、絶対参照（$記号）を使用すると、オートフィルを行っても範囲がずれることはありません。

計算結果

このように「AGGREGATE関数」を使用することで、フィルターを適用しても、範囲内の合計値を常に変化させながら確認することができます。

AGGREGATE関数 と SUBTOTAL関数との比較

AGGREGATE関数と同様の処理を行うことができる関数に「SUBTOTAL関数」があります。

先ほど行った「使用例2: 表でフィルターを使用してデータを絞り込んだ場合」については、SUBTOTAL関数を使用しても処理が可能です。

その場合、SUBTOTAL関数では次のように記述します。

=SUBTOTAL(9,$B$2:$B$6)

計算結果

AGGREGATE関数 と SUBTOTAL関数 の違いを解説

では、この２つの関数は何が違うのか？というと以下の通りです。

結果: AGGREGATE関数 と SUBTOTAL関数は、どちらを使用するべきか？

どちらを使用するかは、状況によりますが、次の使い分けがおすすめです。

SUBTOTAL関数については、別記事で解説を行っていますので、合わせてご覧いただけたらと思います。

エクセル SUBTOTAL関数の使い方｜フィルター後の合計・平均を簡単に求める方法

エクセルのSUBTOTAL関数を使って、フィルター適用後のデータ合計や平均を簡単に計算する方法を解説します。SUBTOTAL関数の基本から応用まで、初心者でも実践できるテクニックを詳しくご紹介。さらに、AGGREGATE関数との違いについても触れます。

zangyo-free.com

2024.07.14

AGGREGATE関数のまとめ

AGGREGATE関数は、エクセルでのエラーを無視して合計を求めるなど、多くの集計作業で非常に役立つ関数です。

この記事では、基本的な使い方から応用的なテクニックまでを詳しく解説しました。

ポイントのおさらい

さらに学ぶために

VBA（Visual Basic for Applications）を使用することで、AGGREGATE関数と同様の処理を自動化することが可能です。
関数からさらにステップアップしたい場合には、VBAを使用してみるのも良いでしょう。

この記事が役に立ったと思った方は、ぜひサイトマップから違う記事もご覧ください。

サイトマップ

VBAワークシート関数 VBA 基本設定 VBAとは？ VBAの始め方 VBAのおすすめ設定 セル操作 RangeとCells CurrentRegion Offset Resize 最終行・最終列の取得 変数 変数 オブジェクト変数 グロ...

zangyo-free.com

2024.08.28