Excel(エクセル)でエラーを無視して合計や平均などの関数を使用する方法|AGGREGATE関数の使い方

スポンサーリンク
スポンサーリンク

Introduction

Ver. all 2010 2013 2016 2019 2021 365

エクセルで、エラーを無視して合計などの計算をする方法です。

AGGREGATE関数を使用します。

「19」種類の関数を数字で指定することで、エラーや空白を無視して計算結果を表示することができます。

 


AGGREGATE関数・・・エラーや非表示セルを無視して集計する

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

  • 集計方法・・・使用する関数を数字で指定する(必須)

【集計方法一覧】

集計方法関数集計方法関数
1AVERAGE11VAR.P
2COUNT12MEDIAN
3COUNTA13MODE.SNGL
4MAX14LARGE
5MIN15SMALL
6PRODUCT16PERCENTILE.INC
7STDEV.S17QUARTILE.INC
8STDEV.P18PERCENTILE.EXC
9SUM19QUARTILE.EXC
10VAR.S
  • オプション・・・無視する値を数字で指定する(必須)

【オプション一覧】

オプション動作
0 か省略ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視
1非表示の行、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視
2エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視
3非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視
4何も無視しない
5非表示の行を無視
6エラー値を無視
7非表示の行とエラー値を無視
  • 範囲1 ,[範囲2], … ・・・集計する範囲を指定する(必須)

 

「AGGREGATE」は「アグリゲート」と読むにゃ

スポンサーリンク

エラーを無視して関数を使用する方法

下の表の「E8」ですが、AVERAGE関数を使用しています。

平均したい上の範囲にエラーのセルがあるため、この場所もエラー表示になってしまいます。

では、この場所で「AGGREGATE関数」を使用してみます。

 

関数を挿入するセルを選択したら[関数の挿入]ボタンをクリック。

[関数の分類]を[数学/三角]か[すべて表示]に切り替えて[AGGREGATE」を選んで[OK]をクリック。

次に[引数の選択]画面が表示されます。

使用したい関数に応じてどちらかを選びます。

[集計方法,オプション,配列,順位]を使用する際の関数
LARGE、SMALL、PERCENTILE.INC、QUARTILE.INC、PERCENTILE.EXC、QUARTILE.EXC

[集計方法,オプション,参照1,…]を使用する際の関数
AVERAGECOUNTCOUNTA、MAX、MIN、PRODUCT、STDEV.S、STDEV.P、SUM、VAR.S、VAR.P、MEDIAN、MODE.SNGL

使用頻度が高さそうな関数を赤くしたのですが、全て[集計方法,オプション,参照1,…]の方ですね。

普段は[集計方法,オプション,参照1,…]を選択すると覚えておきましょう。

今回も下の[集計方法,オプション,参照1,…]を選んで[OK]をクリック。

 


AGGREGATE関数・・・エラーや非表示セルを無視して集計する

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

  • 集計方法・・・使用する関数を数字で指定する(必須)

【集計方法一覧】

集計方法関数集計方法関数
1AVERAGE11VAR.P
2COUNT12MEDIAN
3COUNTA13MODE.SNGL
4MAX14LARGE
5MIN15SMALL
6PRODUCT16PERCENTILE.INC
7STDEV.S17QUARTILE.INC
8STDEV.P18PERCENTILE.EXC
9SUM19QUARTILE.EXC
10VAR.S
  • オプション・・・無視する値を数字で指定する(必須)

【オプション一覧】

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

※ネストとは関数を入れ子状にして複数使用すること

  • 範囲1 ,[範囲2], … ・・・集計する範囲を指定する(必須)

 

引数は下のように設定します。

  • [集計方法]は、今回AVERAGE関数を使用するので「1」を入力
  • [オプション]は、エラーを無視したいので「6」を入力
  • [参照1]に、平均したい範囲を選択します。

[OK]をクリックすれば結果が表示されます。

スポンサーリンク

まとめ

「AGGREGATE関数」は「オプション」で非表示の行を計算するかも指定できます。

非表示の行を無視して計算する関数で有名なものには「SUBTOTAL関数」があるのですが、「AGGREGATE関数」は「SUBTOTAL関数」より細かい指定が可能です。

「SUBTOTAL関数」のパワーアップ版のイメージですね。

Excel(エクセル)でフィルター抽出後など、非表示の行を計算しないで、合計や平均を算出する方法|SUBTOTAL関数の使い方
エクセルで、オートフィルターなど使用して抽出した結果や、非表示にした行を省いて合計や平均の計算、個数を数えたい時などに場合に便利なSUBTOTAL関数の説明です。

 

エラーと非表示行の両方を無視できるにゃ