エクセルでエラー値(#div/0)を除く条件付きで統計値（平均値など）を出す方法【コピペ用テンプレートあり】
はじめに
エクセルで AVERAGE などの統計値を算出する関数の参照セル中に、 #div/0! や #N/A などのエラー値が含まれている場合、
このような場合、 AGGREGATE 関数を使用することで、エラー値を無視して統計値を算出できます。
下にサンプルのセルデータがあるので、
使用例
以下の 全セルをコピー ボタンをクリックし、エクセルの A1 セルに貼り付けると、 H3 セルに平均値（下記の場合 19）が算出されます。
A, C, E列のデータを書き換え、 H3セル中の参照セルを書き換えることで、
関数の詳細
AGGREGATE 関数は、 AVERAGE, COUNT, MAXなどの関数の代わりに使用できます（オプションで機能を切り替え）。
集計方法, オプション, 配列, 順位 / 配列2, ... )集計方法で指定した集計値（統計値）を算出する。
配列のデータを使って算出されるが、
オプションによって無視（除外）する項目を指定できる。
配列2, 配列3, ... を追加することで、
計算に用いるデータを増やすことができる（最大 配列253）。
集計方法「算出する集計値」を指定する数値（1~19）。
| 数値 | 算出する集計値 | 第4引数 (順位 / 配列2) で設定される値 |
|---|---|---|
| 1 | 平均値 | 集計値の算出に使用する数値データ（追加分） |
| 2 | 数値の個数 | |
| 3 | セルの個数（空白以外） | |
| 4 | 最大値 | |
| 5 | 最小値 | |
| 6 | 積 | |
| 7 | 標本標準偏差 | |
| 8 | 母標準偏差 | |
| 9 | 合計 | |
| 10 | 標本分散 | |
| 11 | 母分散 | |
| 12 | 中央値 | |
| 13 | 最頻値 | |
| 14 | k番目に大きい値 | k (順位) |
| 15 | k番目に小さい値 | |
| 16 | 百分位値(パーセンタイル) | 割合 |
| 17 | 四分位値(クォータイル) | 順位 |
| 18 | 百分位値(パーセンタイル)(範囲外) | 割合 |
| 19 | 四分位値(クォータイル)(範囲外) | 値, 有効桁数 |
オプション「無視するデータの項目」を指定する数値（0~7）。
| 数値 | 無視する項目 |
|---|---|
| 0 | ネストされた SUBTOTAL 関数とAGGREGATE 関数 |
| 1 | 非表示の行、ネストされた SUBTOTAL 関数と AGGREGATE 関数 |
| 2 | エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数 |
| 3 | 非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数 |
| 4 | - |
| 5 | 非表示の行 |
| 6 | エラー値 |
| 7 | 非表示の行とエラー値 |
配列順位 / 配列2 , ... または集計値の算出に必要なパラメータ（
集計方法 = 14~19のとき）。AVERAGE関数は離れたセルも同時に選択できる
エラーのあるセルを含む、統計値の計算では
AVERAGEIF関数などのIFの付いた関数を使用する方法もあります。しかしこれらは、離れたとびとびのセルを同時に選択できません。
そんなときにAGGREGATE関数であれば、役立ちます。
AGGREGATE関数と他の関数との対応表
以下の対応表に従って、 AGGREGATE 関数に置き換えることで、
| 統計値 | 統計値を算出する関数 | AGGREGATE 関数 | 第1引数の値 |
|---|---|---|---|
| 平均値 | AVERAGE(参照セル範囲) | AGGREGATE(1,6,参照セル範囲) | 1 |
| 数値の個数 | COUNT(参照セル範囲) | AGGREGATE(2,6,参照セル範囲) | 2 |
| セルの個数（空白以外） | COUNTA(参照セル範囲) | AGGREGATE(3,6,参照セル範囲) | 3 |
| 最大値 | MAX(参照セル範囲) | AGGREGATE(4,6,参照セル範囲) | 4 |
| 最小値 | MIN(参照セル範囲) | AGGREGATE(5,6,参照セル範囲) | 5 |
| 積 | PRODUCT(参照セル範囲) | AGGREGATE(6,6,参照セル範囲) | 6 |
| 標本標準偏差 | STDEV.S(参照セル範囲) | AGGREGATE(7,6,参照セル範囲) | 7 |
| 母標準偏差 | STDEV.P(参照セル範囲) | AGGREGATE(8,6, 参照セル範囲) | 8 |
| 合計 | SUM(参照セル範囲) | AGGREGATE(9,6,参照セル範囲) | 9 |
| 標本分散 | VAR.S(参照セル範囲) | AGGREGATE(10,6,参照セル範囲) | 10 |
| 母分散 | VAR.P(参照セル範囲) | AGGREGATE(11,6,参照セル範囲) | 11 |
| 中央値 | MEDIAN(参照セル範囲) | AGGREGATE(12,6,参照セル範囲) | 12 |
| 最頻値 | MODE.SINGLE(参照セル範囲) | AGGREGATE(13,6,参照セル範囲) | 13 |
| k番目に大きい値 | LARGE(参照セル範囲, k) | AGGREGATE(14,6,参照セル範囲, k) | 14 |
| k番目に小さい値 | SMALL(参照セル範囲, k) | AGGREGATE(15,6,参照セル範囲, k) | 15 |
| 百分位値(パーセンタイル) | PERCENTILE.INC(参照セル範囲, 割合) | AGGREGATE(16,6,参照セル範囲, 割合) | 16 |
| 四分位値(クォータイル) | QUARTILE.INC(参照セル範囲, 順位) | AGGREGATE(17,6,参照セル範囲, 順位) | 17 |
| 百分位値(パーセンタイル)(範囲外) | PERCENTILE.EXC(参照セル範囲, 割合) | AGGREGATE(18,6,参照セル範囲, 割合) | 18 |
| 四分位値(クォータイル)(範囲外) | QUARTILE.EXC(参照セル範囲, 値, 有効桁数) | AGGREGATE(19,6,参照セル範囲, 値, 有効桁数) | 19 |