Excelで平均を出そうとしたら、なぜか #REF! や #DIV/0! が混ざって思った値にならない…。特に「特定行だけ除外したい」「エラーは無視したい」といった集計では、数式が一気に複雑になります。この記事では、J列の平均から「エラー」と「A列がtの行」を除外する具体例を使って、#REF! を根本的に防ぐ書き方とデバッグのコツを整理します。
Excelで平均を計算したら#REF!が出るシナリオ
まずは前提となるシート構成と要件を整理します。
- 各行の F:H 列の平均 を J列 に計算している
- シート上の特定セル（例：B3 や B4）で、J列全体の平均 を出したい
- ただし次の条件で集計したい
- エラー（#DIV/0! など）は無視して平均したい
- A列が「t」の行は平均から除外したい
この要件を満たすために、最初は次のような式でうまく動いていました（例：範囲が 6～53 行の場合）。
=AVERAGE(
FILTER(
IFERROR(J6:J53,0),
(IFERROR(J6:J53,0)<>0) * (A6:A53<>"t")
)
)
ところが、データが増えて J列の範囲を J6:J53 → J6:J65 に拡張したあたりから、式を修正しても #REF! が出るようになってしまった…という相談が非常に多いです。
結論から言うと、原因は次の2点がほぼすべてです。
- A列側に #REF! などのエラーが潜んでいる
- 数式内で指定している範囲の行数が揃っていない
特に、A列にエラーがある状態でそのまま A6:A65<>"t" のように条件評価を行うと、その1つのエラーが原因で式全体が #REF! になることがあります。ここをしっかりガードしてあげることがポイントです。
元の数式の考え方を分解して理解する
まずは、もともとの考え方（AVERAGE + FILTER + IFERROR）が何をしているかを整理してみましょう。
=AVERAGE(
FILTER(
IFERROR(J6:J65,0),
(IFERROR(J6:J65,0)<>0) * (A6:A65<>"t")
)
)
この数式は、ざっくり言えば次の3ステップです。
IFERROR(J6:J65,0)で J列のエラーを 0 に置き換える(IFERROR(J6:J65,0)<>0) * (A6:A65<>"t")で- J列が 0 以外
- かつ A列が「t」以外
FILTER(…)で、そのマスクが 1 の行だけを抽出し、その平均を取る
このロジック自体はとても合理的です。ところが、下記のような状況があると一気に壊れます。
- A列のどこかに #REF! や #VALUE! などのエラーがある
J6:J65とA6:A65のように、範囲の行数や開始位置がズレている
特に「隠し列」や「昔の計算をコピーして残している列」にエラーがあると、見た目では気づかず、条件式だけが #REF! で壊れてしまいます。
#REF!の主犯は「条件側のエラー」
FILTER 関数の構造は、
=FILTER(抽出したい範囲, 条件（同じ行数の配列）)
という形ですが、次のどちらでもエラーになります。
- 抽出したい範囲 にエラーがある
- 条件側の配列 にエラーがある
今回のケースでは、次の部分が問題になりがちです。
(A6:A65<>"t")
もし A列のどこかに #REF! があると、Excel はこの比較を行った時点で 配列全体をエラー とみなすことがあります。その後の *(掛け算) を通じて FILTER の条件も丸ごと壊れ、結果として AVERAGE まで連鎖して #REF! になってしまいます。
A列のエラーを IFERROR でガードする
この問題を根本的に避けるために、条件式側でも IFERROR を使うのが定石です。
例えば、A列にエラーがあった場合は、強制的に「t」扱いにして集計から除外してしまう、という考え方が安全です。
IFERROR(A6:A65, "t")<>"t"
こうすることで、
- A列にエラーがある場合 → 一旦「t」とみなす →
"t"<>"t"は FALSE → 平均対象からは外れる - A列が「t」そのものでも FALSE になり、同じく対象外
という動きになります。「エラーがある行も含めて平均を取りたい」というケースは現場ではほぼ無いので、「エラー行は問答無用で除外する」という割り切りで十分実用的です。
範囲の不一致でもトラブルの元に
もう一つ多いのが、範囲の「行数ズレ」です。
| 指定範囲 | 行数 | 状態 |
|---|---|---|
| J6:J65 | 60行 | OK |
| A6:A53 | 48行 | NG（行数が違う） |
FILTER や AVERAGEIFS などの関数では、
- 平均範囲
- 条件範囲
の行数が揃っていることが大前提です。どこかを増やした時は、「平均範囲だけ伸びて条件範囲がそのまま」になっていないかを必ず確認しましょう。
エラーに強い数式①：LET + FILTER + AVERAGE
ここからは、実務で使いやすく、かつエラーに強い数式を紹介します。まずは、元の考え方（FILTER + AVERAGE）をきれいに書き直したパターンです。
=LET(
scores, IFERROR(J6:J65, 0),
mask, (scores<>0) * (IFERROR(A6:A65, "t")<>"t"),
AVERAGE(FILTER(scores, mask))
)
LET を使うメリット
LET 関数は、「名前を付けて中間結果を使い回せる」関数です。上記の数式は、次のような意味を持ちます。
| 名前 | 内容 | 役割 |
|---|---|---|
| scores | IFERROR(J6:J65, 0) | J列の値。エラーは 0 に置き換え |
| mask | (scores<>0) * (IFERROR(A6:A65, "t")<>"t") | 「スコアが 0 以外」かつ「A列がt以外」の行だけ 1 になるマスク |
| AVERAGE(FILTER(scores, mask)) | — | マスクが 1 の行だけを抽出し、その平均を計算 |
書いていることはシンプルですが、
- J列のエラーは 0 に統一
- A列のエラーはすべて「t」扱いで除外
- 0 に置き換えた行も平均から外す
というポリシーがしっかり一貫しています。
0 を除外する理由と、あえて含めたい場合
この数式では scores<>0 という条件を入れているため、
- 元々の J列がエラー → 0 に置き換え → 平均対象外
- 元々の J列が 0 → 平均対象外
になります。
「エラーの代わりの 0 は除外したいが、本当の 0 は含みたい」という場合には、J列の作り方を変えるのがおすすめです。
- J列の数式自体で IFERROR を使い、エラーのみ空白にする（0はそのまま）
例：J列の1行目（J6）で、
=IFERROR(AVERAGE(F6:H6), "")
とし、これを下までコピーしておけば、「エラー行は空白」「0は0」として区別できます。この場合、集計側では空白を無視して平均させれば OK です。
もし「0も含めて平均したい」のであれば、mask の作り方を少し変えます。
=LET(
scores, IFERROR(J6:J65, ""),
mask, (scores<>"") * (IFERROR(A6:A65, "t")<>"t"),
AVERAGE(FILTER(scores, mask))
)
- J列のエラー → 空白にする
- 空白行だけ除外する
- 0 は普通の数値として残す
という形です。運用ルールに合わせて、どちらかのパターンを選びましょう。
AVERAGE 部分も IFERROR でガードする
FILTER の結果が 1件もない場合、AVERAGE(空) となってしまい、#DIV/0! などのエラーが返ることがあります。見た目を整えたい場合は、最後を IFERROR で包むのがおすすめです。
=LET(
scores, IFERROR(J6:J65, 0),
mask, (scores<>0) * (IFERROR(A6:A65, "t")<>"t"),
pool, FILTER(scores, mask),
IFERROR(AVERAGE(pool), "")
)
これなら、条件に合う行が 1件もないときは 空白 を返してくれます（必要に応じて「0」や「対象なし」などの文字列に変えても構いません）。
エラーに強い数式②：AVERAGEIFS で簡潔に書く
「LET や FILTER はまだ慣れていない…」という場合、AVERAGEIFS を使う方法もとても実用的です。条件が少ない場合はこちらの方が読みやすくなります。
=AVERAGEIFS(
J6:J65,
J6:J65, "<>#DIV/0!",
A6:A65, "<>t"
)
この数式は次のように動きます。
- 平均範囲：
J6:J65 - 条件1：
J6:J65が #DIV/0! ではない - 条件2：
A6:A65が t ではない
AVERAGEIFS は、基本的に「数値だけ」を平均し、条件に合わない行は自動的に除外します。そのため、
- A列が t の行は完全に無視
- J列が #DIV/0! の行も無視（条件で除外）
という動きになります。
J列に他種類のエラーが混じる可能性がある場合
注意したいのは、AVERAGEIFS では 平均範囲にエラーが残っているとエラーになる可能性があることです。#DIV/0! 以外に、
- #REF!
- #VALUE!
- #N/A
などが混じり得る運用の場合、次のようにしておくとより安全です。
- J列の計算式自体に IFERROR を入れておく
例：J列（行単位の平均）を=IFERROR(AVERAGE(F6:H6), "")のように書いておく - AVERAGEIFS 側ではエラー文字列ではなく「空白」「0」かどうかを条件にする
例えば、空白以外かつ A列が t 以外を対象にするなら、
=AVERAGEIFS(
J6:J65,
J6:J65, "<>",
A6:A65, "<>t"
)
のようにすることもできます。
最終推奨：堅牢な LET + FILTER 版の解説
運用の変化（行が増える、列が増える、たまに手入力でエラーが入る等）まで考えると、次のような「堅牢版」を用意しておくと安心です。
=LET(
scores, IFERROR(J6:J65, 0),
okA, IFERROR(A6:A65, "t")<>"t",
okJ, scores<>0,
pool, FILTER(scores, okA*okJ),
IFERROR(AVERAGE(pool), "")
)
各部分の意味を、もう一度表で整理します。
| 名前 | 計算内容 | 意味・役割 |
|---|---|---|
| scores | IFERROR(J6:J65, 0) | J列の数値を取得。エラーは 0 に置き換え |
| okA | IFERROR(A6:A65, "t")<>"t" | A列が「t」でもエラーでもない行だけ TRUE |
| okJ | scores<>0 | スコアが 0 以外の行だけ TRUE（エラー置換された 0 も除外） |
| pool | FILTER(scores, okA*okJ) | 条件を満たす scores だけを取り出した配列 |
| IFERROR(AVERAGE(pool), “”) | pool が空なら空白を返す | 対象が1件もないときでも #DIV/0! にならない |
この形にしておけば、
- J列のエラーや 0 を自動的に除外
- A列の t やエラーも自動的に除外
- 条件に合うものが無いときもエラーではなく空白を返す
という、かなり実務向きの動きになります。
#REF!を防ぐためのチェックリスト
ここまでの内容を、#REF! が出たときに最初に確認したいポイントとして整理します。
| 確認項目 | チェック方法 | 対策 |
|---|---|---|
| A列にエラーがないか | 一時的に列を表示し、フィルターで エラー を抽出 | 元データを修正、もしくは数式側で IFERROR(A6:A65,"t") としてガード |
| 範囲の行数が揃っているか | 数式バーで各範囲を選択し、ステータスバーの「行数」を確認 | J6:J65 と A6:A65 など、すべて同じ行数になるよう修正 |
| 結合セル・削除行が混ざっていないか | 範囲内で右クリック → セルの書式設定 → 配置タブ →「セルを結合する」のチェックを確認 | 範囲内の結合セルを避ける、削除行がある場合は範囲指定を見直す |
| フィルター結果が空になっていないか | FILTER 部分のみを別セルに入力してみる | 必要に応じて最後を IFERROR(AVERAGE(...),"") にし、見た目のエラーを抑制 |
段階的にデバッグするコツ
複雑な数式のまま原因を探そうとすると、どこでエラーが出ているのか分からなくなります。次のように「部品ごと」に切り分けて確認すると、トラブルシューティングが一気に楽になります。
1. まず FILTER 単体を試す
=FILTER(
J6:J65,
(J6:J65<>0) * (A6:A65<>"t")
)
いきなり AVERAGE まで含めずに、まずは FILTER だけを入力してみます。
- ここで #REF! が出る → 条件か範囲指定が怪しい
- ちゃんと値が返る → AVERAGE の方に問題がある可能性
2. LET で中間結果の配列を確認する
次に、LET で中間結果をそのまま返してみると、どの行が TRUE/FALSE になっているかが分かります。
=LET(
scores, IFERROR(J6:J65,0),
mask, (scores<>0) * (IFERROR(A6:A65,"t")<>"t"),
mask
)
この式を入力すると、0 と 1 の配列（TRUE/FALSE）が返ってきます。
- 本来含めたい行が 0 になっていないか
- 含めたくない行が 1 になっていないか
を見ながら、条件式のロジックを修正できます。
3. 少しずつ元の形に戻す
FILTER まで問題ないことが確認できたら、最後に AVERAGE をかぶせます。
=LET(
scores, IFERROR(J6:J65,0),
mask, (scores<>0) * (IFERROR(A6:A65,"t")<>"t"),
AVERAGE(FILTER(scores, mask))
)
ここで初めて #DIV/0! が出るようであれば、単純に「対象が1件も無い」だけなので、IFERROR で包むだけで解決します。
条件を増やしたいときの応用例
実務では、「A列がt以外」以外にも、様々な条件を追加したくなることがあります。例えば、
- 日付列（例：B列）が、特定の期間内の行だけ
- 部門コード列（例：C列）が、特定部署の行だけ
- J列のスコアが 50 以上の行だけ
といった条件です。
FILTER 版で条件を追加する例
例えば、「J列が 50 以上」の行だけを対象にしたい場合は、mask に条件を追加します。
=LET(
scores, IFERROR(J6:J65, 0),
okA, IFERROR(A6:A65, "t")<>"t",
okJ, scores>=50,
pool, FILTER(scores, okA*okJ),
IFERROR(AVERAGE(pool), "")
)
条件を増やすたびに mask の中が複雑になるので、okA / okJ / okDate のように名前を分けると読みやすくなります。
AVERAGEIFS 版で条件を追加する例
AVERAGEIFS は、引数を増やすだけで条件を追加できます。
=AVERAGEIFS(
J6:J65,
J6:J65, ">=50",
A6:A65, "<>t",
C6:C65, "=営業部"
)
分かりやすさ重視なら、条件の数が2〜3個程度に収まるうちは AVERAGEIFS も非常に有効です。
設計段階でエラーを減らす3つのコツ
最後に、そもそも #REF! や #DIV/0! を出にくくするための設計上の工夫を3つ紹介します。
1. 行ごとの計算列（J列など）に必ず IFERROR を入れる
「集計側で全部なんとかする」のではなく、行ごとの元データ列でエラーを潰しておくのが一番効きます。
- 平均・比率・割り算 →
IFERROR(計算式, "")かIFERROR(計算式, 0) - 文字列連結 →
IFERROR(計算式, "")
この一手間で、後ろの集計式がかなりシンプルになります。
2. 範囲指定は「表（テーブル）」か「名前付き範囲」を使う
「行が増えるたびに J6:J65 を J6:J200 に書き換える」運用だと、どこかで必ず範囲ズレが起きます。Excel の「テーブル」機能を使えば、行が増えてもテーブル名だけで自動的に範囲が追従してくれます。
- データ範囲を選択 → Ctrl + T でテーブル化
- 数式は
=AVERAGEIFS(tbl[スコア], tbl[スコア], "<>", tbl[フラグ], "<>t")のように記述
これだけで、行数が増えても範囲指定のやり直しから解放されます。
3. 隠し列だからこそ IFERROR を徹底する
列Aのように「フラグ」や「内部用コード」が入っている列は、シートの見た目を整えるために非表示にされがちです。ところが、見えないからこそエラーに気づきにくいという側面があります。
このような列こそ、
- 数式を使っている場合は必ず IFERROR を入れておく
- 手入力の場合も、フィルターで定期的に「エラー」を確認する
といった運用をしておくと安心です。
J列の平均から「エラー」と「A列がtの行」を除外したい、という一見ニッチなケースですが、考え方自体は他の集計にもそのまま応用できます。「どの列のエラーをどのタイミングで潰すか」さえ決めておけば、#REF! に悩まされる時間は確実に減らせます。
ぜひ、LET + FILTER 版と AVERAGEIFS 版の2パターンを、自分の使いやすい方から取り入れてみてください。
コメント