エクセルのAVERAGEIFS関数を使う場合に、数値欄の「#N/A」エラーを除いた平均値を出す方法をご紹介します。まずは、AVERAGEIFS関数の使い方を初心者にもわかりやすく解説します。
Excelで平均値を求めるときに使われるのがAVERAGEIFS関数です。AVERAGEIFS関数の魅力としては、平均値を求める際に特定の条件を指定できるところです。例えば、「#N/A」エラーを除いて平均値を求めたい場合に利用できます。
自己紹介
IT業界でのキャリアを活かし、業務効率化に欠かせないMicrosoft365や便利ツールを紹介しています。少しのコツで膨大な可能性を最大限引き出すためのヒントやテクニックを提供し、読者のビジネスパフォーマンス向上に貢献することを目指しています！
AVERAGEIFS関数の基本構文
AVERAGEIFS関数は、平均値を求める際に特定の条件を指定できるところが魅力です。構文は次の通りです。
=AVERAGEIFS(平均対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2],…)
| 平均対象範囲 | 平均値を計算したい数値の範囲 |
| 条件範囲 | 条件を検索する範囲を指定 |
| 条件 | 条件範囲で一致する条件を指定 |
例えば、=AVERAGEIFS(A:A, B:B, “Excel“, B:B, “エクセル”)と関数を組むことが出来ます。
例の式では、平均対象範囲をA列で範囲指定、B列を条件範囲としています。条件範囲のB列にExcelまたはエクセルと入力されているA列の数値の平均を計算することが出来ます。
AVERAGEIFS関数の使い方
こちらの章では、AVERAGEIFS関数を具体的な例を使って解説していきます。
わかりやすくするために、表を作成してみました。
内容としては、斎藤さんと田中さんが商品ABを販売した個数と金額が記載されています。
H2セルに斎藤さんが販売した商品Aの平均金額を求めていきます。
それでは、H2セルに斎藤さんが販売した商品Aの平均金額を求めていきましょう。
=AVERAGEIFS(E2:E7, B2:B7, G2, C2:C7, H1)
| 平均対象範囲 | E2:E7 | 販売金額の平均なので、E列を範囲指定 |
| 条件範囲① | B2:B7 | 名前が記載されているB列を指定 |
| 条件① | G2 | 斎藤さん名前が記載されているG2セルを指定 |
| 条件範囲② | C2:C7 | 販売商品が記載されているC列を指定 |
| 条件② | H1 | 商品名が記載されているH1セルを指定 |
斎藤さんが販売した商品Aの平均金額をAVERAGEIFS関数で求めることが出来ました。
[ 条件①, 条件② ]でセルを指定していますが、[ “斎藤”, “商品A” ]で条件を指定することも出来ます。
ただし、個人的にはデータ分析をするとき条件はセルで指定することをオススメします。理由としては、条件を指定しているセルの入力を変えると関数が自動的に紐づくからです。例えば、G2セルを[ 田中 ]に変更、H1セルを[ 商品B ]に変更した場合は、田中が販売した商品Bの平均金額を求めることが出来るということです。
実際にG2セルとH1セルを[ 田中 ], [ 商品B ]に変更してみましょう。
G2セルとH1セルを変更することで、田中が販売した商品Bの平均金額を求めることが出来ましたね。
応用：「#N/A」エラーを除いて平均値を求める方法
AVERAGEIFS関数を使用するとき、平均値を求める数値欄に「#N/A」エラーがあると、AVERAGEIFS関数も「#N/A」エラーになります。この「#N/A」エラーを除いて平均値を求める方法をご紹介します。具体的には、演算子の <> を使用していきます。
わかりやすくするために、表を作成しました。この表を使って、斎藤さんが販売した商品Aの平均金額を求めていきたいと思います。
E列に「#N/A」が表示されていては、販売金額の平均を求めることが出来ません。具体的には、AVERAGEIFS関数で、販売金額が「#N/A」でないセルの平均金額を求める指示をしなければなりません。「○○でない」と指示する場合は、演算子の <> を使用することができます。
実際の関数式を見ていきましょう。
=AVERAGEIFS(E2:E7, B2:B7, G2, E2:E7, “<>#N/A”)
| 平均対象範囲 | E2:E7 | 販売金額の平均なので、E列を範囲指定 |
| 条件範囲① | B2:B7 | 名前が記載されているB列を指定 |
| 条件① | G2 | 斎藤さん名前が記載されているG2セルを指定 |
| 条件範囲② | E2:E7 | #N/Aエラーが記載されているE列を指定 |
| 条件② | “<>#N/A” | 演算子の<>を使用して、#N/Aでないセルを指定 |
このようにAVERAGEIFS関数で、「#N/A」エラーを省いくことができます。
関数をコピペして試してみてください。
