エラー値を含む範囲で平均や合計を求める方法は?
結論
AGGREGATE関数の第2引数に6を指定することで、エラー値を無視して平均・合計などを計算できます。
詳しい解説
エラー値を含む範囲で集計するにはAGGREGATE関数を使用します。`=AGGREGATE(1, 6, A2:A100)`のように、第1引数に集計方法(1=AVERAGE、9=SUM等)、第2引数にオプション(6=エラー値を無視)を指定します。#N/A、#VALUE!、#DIV/0!などのエラーが混在するデータでも、エラーを除外して正常な値だけで平均や合計を計算できます。VLOOKUP結果にエラーが含まれる場合の集計、計算途中でエラーが発生するセルを含む範囲の分析などに使用します。SUBTOTAL関数との違いは、AGGREGATEはエラー値も無視できる点です。第2引数の値により、非表示行やエラー値の扱いを細かく制御できます。
執筆・検証:田中 けんた|Excel歴15年・MOS資格保持
バージョン対応
| Excel 2016 | Excel 2019 | Excel 2021 | Excel 2024 | Microsoft 365 |
|---|---|---|---|---|
| 対応 | 対応 | 対応 | 対応 | 対応 |
実務での使用例
エラーを無視した平均(AVERAGE)
データ(A列)に#N/Aや#DIV/0!が混在していても、正常な数値だけで平均を計算。第1引数1はAVERAGE、第2引数6はエラー無視
=AGGREGATE(1, 6, A2:A100)エラーを無視した合計(SUM)
VLOOKUP結果などエラーが含まれる範囲(B列)の合計を計算。第1引数9はSUM
=AGGREGATE(9, 6, B2:B100)