エラー値を無視して合計や平均の計算したい：Excel基本講座

スポンサードリンク

更新:2025/3/2;作成:2014/11/23

AGGREGATE関数を使ってエラー値を無視して計算する

配列数式を使って複数のエラー値を無視して計算する

エラー値を無視して平均値などを計算する

1種類のエラーを無視して合計する

エラー値を無視して計算する　　　　　Topへ

• 数式を入力したら・・・期待した値が表示されずエラー値が表示されることがあります。
  通常がエラー値が表示されないように対処することが多いと思います。
  • エラー値の対処法は　エラー値とその対策　をご覧ください。
• ここでは、エラー値が表示されている場合にそのままエラー値を無視して計算したい・・・といったケースについて書いてみます。
  下図のようないくつかのケースを例に説明してみます。
  普通にSUM関数を使うと9行目のようにエラーが返されてしまう場合に、１０行目のように計算したいということです。
  

AGGREGATE関数を使ってエラー値を無視して計算する　　　　Topへ

• 複数のエラー値を含む場合はAGGREGATE関数が最も適しているように思います。
• Excel2010で追加されたAGGREGATE関数を使って、エラー値を無視した計算ができます。
  
  • AGGREGATE関数の詳細については　Excel(エクセル)基本講座：AGGREGATE関数　をご覧ください。
  C9セルの数式は　=AGGREGATE(9,6,C2:C7)　としています。

AGGREGATE関数を入力する手順

1. 関数名を正確に覚えていなくても、=a　と入力すると、Aで始まる関数がリスト表示されます。
   AGGREGATE まで[↓]キーを押して、選択し、[Tab]キーを押すと、セルに入力されます。
   
2. =AGGREGATE(　と入力されて、引数の集計方法がリスト表示されます。
   ここでは、合計したいので　9 - SUM を[↓]キーを何回か押して選択します。
   [Tab]キーを押して 9を入力します。
   
3. =AGGREGATE(9,　9の後ろにカンマ( , )を入力すると、引数のオプションがリスト表示されます。
   6 - エラー値を無視します　を[↓]キーを何回か押して選択します。
   [Tab]キーを押して 6 を入力します。
   
4. =AGGREGATE(9,6,　とカンマを入力して、計算するセル範囲　C2:C7)　を入力します。
   [Enter]キーで数式の入力を確定します。
   マウスでセル範囲　C2:C7 を選択してもOKです。
   
5. エラー値を無視して計算ができました。
   

配列数式を使って複数のエラー値を無視して計算する　　　　Topへ

• Excel for Microsoft365やExcel2021以降では配列数式がそのまま入力して利用できます。(動的配列式)
  より一層、配列数式を使う機会が増えてきそうな気がします。

IFERROR関数を使った配列数式(1)

• 配列数式を使って、エラー値を0に置き換えて計算しています。
  D9セルには　=SUM(IFERROR(D2:D7,0))　と入力しています。
  配列数式なので　Excel2019以前のバージョンでは [Ctrl]+[Shift]+[Enter]で入力します。
  

IFERROR関数を使った配列数式(2)

• E9セルは　配列数式を使って、エラー値を0に置き換えて計算しています。
  Excel for Microsoft365、Excel2021など動的配列式が使える場合は
  =SUM(IF(ISERROR(C2:C7),0,C2:C7))　でOKです。
  Excel2019以前では　=SUM(IF(ISERROR(C2:C7),0,C2:C7))　と入力して、[Shift]+[Ctrl]+[Enter]で数式の入力を確定して、配列数式にします。
  {=SUM(IF(ISERROR(C2:C7),0,C2:C7))}　と数式がかっこで囲む必要があります。
  

エラー値を無視して平均値などを計算する　　　　Topへ

• AGGREGATE関数の場合は引数を 1（AVERAGE:平均） とするだけでOKです。
  =AGGREGATE(1,6,C2:C7) とします。
• エラー値を無視して平均値を計算したい場合は、0ではなく ""(空白)を返すようにします。
  これらは配列数式ですので、Excel for Microsoft365、Excel2021など動的配列式が使える場合はそのまま[Enter]です。
  =AVERAGE(IFERROR(D2:D7,""))
  =AVERAGE(IF(ISERROR(E2:E7),"",E2:E7))
  Excel2019以前では[Shift]+[Ctrl]+[Enter]で数式の入力を確定します。
  

1種類のエラーを無視して合計する　　　　Topへ

• 下図のように　#N/Aエラーが計算したいセル範囲にある時、C9セルのように =SUM(C2:C7) では　#N/Aエラーが返されます。
  #N/A と等しくないセルを合計する(#N/A 以外のセルを合計する)と考えると、C10セルのように　=SUMIF(C2:C7,"<>#N/A")　として計算することができます。
• 同じく、#VALUE! エラーだけがある場合は、=SUMIF(D2:D7,"<>#VALUE!")　で計算することができます。
• =SUMIF(E2:E7,">0")+SUMIF(E2:E7,"<0")でも計算できます。
  この数式は複数のエラーを無視して合計することもできます。