エクセルで関数や数式を入力したとき、セルに空白があると、エラーや「0」が表示されます。IF関数を使えばエラーを非表示にはできますが、この場合は、AGGREGATE関数を使いましょう。エラーを回避しながら合計や平均などの計算が可能です。
AGGREGATE関数でエラーを回避
IF関数を使わずにエラー表示なしで計算する
IF関数を使えばエラーを非表示にできますが、エラーを無視して強引に計算することはできません。例えば「途中にエラー値があっても合計を求めたい」というような場合は、AGGREGATE関数を使いましょう。「集計方法」と「オプション」を指定することで、エラーを回避しながら合計や平均などの計算が可能です。
| AGGREGATE | 非表示の行やエラー値を無視して、合計や平均などの集計値を返す |
|---|---|
| =AGGREGATE(集計方法,オプション,参照1,[参照2],…) | |
「集計方法」の値と対応する関数
| 集計方法 | 対応する関数 | 機能 |
|---|---|---|
| 1 | AVERAGE | 平均を求める |
| 2 | COUNT | 数値の個数を求める |
| 3 | COUNTA | データの個数を求める |
| 4 | MAX | 最大値を求める |
| 5 | MIN | 最小値を求める |
| 6 | PRODUCT | 積を求める |
| 9 | SUM | 合計を求める |
| 12 | MEDIAN | 中央値を求める |
| 13 | MODE.SNGL | 最頻値を求める |
| 14 | LARGE | 大きいほうからの順位を求める |
| 15 | SMALL | 小さいほうからの順位を求める |
| 集計方法は1から19まで用意されているが、よく使うものに絞った。 | ||
指定できる「オプション」と動作
| オプション | 動作 |
|---|---|
| 0 または省略 |
ネストされた(入れ子になった)SUBTOTAL関数とAGGREGATE関数を無視する |
| 1 | 非表示の行、ネストされたSUBTOTAL関数とAGGREGATE関数を無視する |
| 2 | エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視する |
| 3 | 非表示の行、エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視する |
| 4 | 何も無視しない |
| 5 | 非表示の行を無視する |
| 6 | エラー値を無視する |
| 7 | 非表示の行とエラー値を無視する |
AGGREGATE関数でエラーを無視して計算する
ここではエラーを無視して平均を計算するため、集計方法に「1」、オプションに「6」を指定して、「=AGGREGATE(1,6,D2: D6)」と入力(❶)。すると、計算範囲にあるエラーはすべて無視し、平均が求められる(❷)。
※この記事は『エクセルが2週間で身につく(楽)上達講座』(マキノ出版)に掲載されています。
