ExcelのXLOOKUPやVLOOKUPで検索結果をスピル(配列)として一気に表示すると、マスタに無い値だけ#N/Aが混ざって見た目が崩れることがあります。この#N/Aを消す書き方は、使っている関数によって最短ルートが違います。本記事ではXLOOKUP第4引数・IFNA外包・FILTER第3引数・IFERRORの4パターンを、Excel 2024の実機画面付きで整理します。
結論: 関数別に最短の書き方が決まっている
スピル配列で#N/Aを消す方法は、次の4パターンに整理できます。使っている関数から逆引きしてください。
| 使っている関数 | 最短の書き方 | 代替値の自由度 |
|---|---|---|
| XLOOKUP | 第4引数 "該当なし" | スカラのみ(配列は不可) |
| VLOOKUP・INDEX+MATCH・LOOKUP | IFNAで外包 | スカラ・配列どちらも可 |
| HSTACK・VSTACK・BYROW の合成配列 | IFNAで全体を外包 | スカラ・配列どちらも可 |
| FILTER(空結果で#CALC!) | 第3引数 "該当なし" | スカラ・配列どちらも可 |
念のため1万行規模で計算時間を実測したところ、XLOOKUP単体・XLOOKUP第4引数・IFNA外包・IFERROR外包のいずれも、手元のExcel 2024環境では体感差はありませんでした(測定条件によっては揺れます)。速度で選ぶ必要はなく、可読性と機能(配列代替の要否など)で決めて構いません。
XLOOKUPのスピル#N/Aは第4引数が最短
XLOOKUP関数は、第4引数に「見つからない場合の代替値」を直接書けます。検索値を配列で渡してスピルしているときも、第4引数が配列全体のどの#N/Aにも効きます。
=XLOOKUP(E2:E6,A2:A6,B2:B6,"該当なし")
代替値を行ごとに変えたい場合だけ、IFNAに切り替えます。XLOOKUP第4引数は配列を受け付けず、配列を渡すと#VALUE!になるため、行別の代替値にはIFNAの第2引数が必要です。
=IFNA(XLOOKUP(E2:E6,A2:A6,B2:B6),G2:G6)
VLOOKUP・INDEX+MATCHはIFNAで外包する
XLOOKUP以外のルックアップ関数(VLOOKUP、INDEX+MATCH、LOOKUPなど)には「見つからない場合の代替値」引数がありません。これらを使う場合はIFNA関数でスピル配列全体を包むのが最短です。
Excel 2024(Microsoft 365)では、VLOOKUPに配列の検索値を渡すと自然にスピルします。旧形式の「1セル1検索値」の感覚から離れて、配列ごと一気にIFNAで包めます。
=IFNA(VLOOKUP(E2:E6,A2:C6,2,FALSE),"該当なし")
=IFNA(INDEX(B2:B6,MATCH(E2:E6,A2:A6,0)),"該当なし")
HSTACK・VSTACK・BYROWの合成配列はIFNAで一括処理
2列以上の結果をまとめて取りたいときや、BYROWとLAMBDAで行単位に処理したいときは、合成後の配列にIFNAを1つだけ付けるのが最短です。各関数ごとに代替値を書く必要はありません。
=IFNA(HSTACK(XLOOKUP(E2:E6,A2:A6,B2:B6),XLOOKUP(E2:E6,A2:A6,C2:C6)),"該当なし")
なお、XLOOKUPの戻り範囲に複数列を指定して配列検索をするとスピルは先頭1列分しか展開されません。複数列が必要ならHSTACKで個別に並べるのが確実です。
=IFNA(BYROW(E2:E6,LAMBDA(x,XLOOKUP(x,A2:A6,B2:B6))),"該当なし")
FILTERの空結果は第3引数で処理する(IFNAでは拾えない)
FILTER関数は条件に一致する行が0件だと#CALC!エラーを返します。このエラーはIFNAでは拾えません。FILTERには「空の場合」を渡す専用の第3引数があるので、そちらを使ってください。
=FILTER(B2:B6,A2:A6>10000,"該当なし")
FILTERの基本的な使い方や複数条件、IFERRORで包んだ場合のデメリットは、ExcelのFILTER関数の使い方で詳しく解説しています。
IFNAで拾えない3種のエラー
IFNAは#N/A専用です。#N/A以外のエラーはそのまま通過します。次のエラーはIFNAでは消せません。
| エラー | IFNAで拾える | IFERRORで拾える | 推奨の対処 |
|---|---|---|---|
| #N/A | ○ | ○ | XLOOKUP第4引数 / IFNA外包 |
| #CALC!(FILTER空など) | × | ○ | FILTER第3引数 |
| #DIV/0! | × | ○ | 分母をIFで判定 |
| #VALUE! | × | ○ | 型の見直し |
| #REF! | × | ○ | 参照の見直し |
IFERRORは万能に見えますが、全種類のエラーを無差別に握り潰す副作用があります。本来は#DIV/0!で気付くはずだった分母ゼロや、#REF!で気付くはずだった参照切れまで見えなくなるため、普段は#N/Aだけを意図的に処理するIFNAを優先する方が安全です。
スピル範囲に書き込むと親セルが#SPILL!で崩壊する
「#N/Aが出ている行だけ手で書き換えればいい」と考えて、スピル範囲の中に別の値や式を入れると、スピル全体が壊れます。親セル(アンカー)が#SPILL!に変わり、それ以降のセルの展開が止まります。
直し方はシンプルで、スピル範囲内に入れてしまったセルを削除すれば、親セルのスピルが自動で復活します。スピル結果を編集したいときは、常に親セルの式を書き換えるのが鉄則です。
応用: ISNAでスピル全体のTRUE/FALSE配列を作る
ISNA関数もスピルに対応しているため、#N/Aかどうかの真偽値配列を一気に作れます。未存在の件数カウントや、条件付き書式でのハイライトに使えます。
=SUMPRODUCT(--ISNA(XLOOKUP(E2:E6,A2:A6,B2:B6)))
条件付き書式で使う場合は、適用範囲を選んで「数式を使用して書式設定するセルを決定」を選び、次の式を入力します(先頭セルが$Gの行であれば$を付けて列を固定)。
=ISNA($G2)
コピペ用定型式10本
業務でそのまま使える書き方を10本まとめました。セル範囲は実際のデータに合わせて書き換えてください。
=XLOOKUP(E2:E6,A2:A6,B2:B6,"該当なし")=IFNA(XLOOKUP(E2:E6,A2:A6,B2:B6),G2:G6)=IFNA(VLOOKUP(E2:E6,A2:C6,2,FALSE),"該当なし")=IFNA(INDEX(B2:B6,MATCH(E2:E6,A2:A6,0)),"該当なし")=IFNA(HSTACK(XLOOKUP(E2:E6,A2:A6,B2:B6),XLOOKUP(E2:E6,A2:A6,C2:C6)),"該当なし")=IFNA(BYROW(E2:E6,LAMBDA(x,XLOOKUP(x,A2:A6,B2:B6))),"該当なし")=FILTER(B2:B6,A2:A6>10000,"該当なし")=SUMPRODUCT(--ISNA(XLOOKUP(E2:E6,A2:A6,B2:B6)))=AVERAGE(IFNA(XLOOKUP(E2:E6,A2:A6,B2:B6),""))=ISNA($G2)よくある質問
XLOOKUPの第4引数とIFNA外包はどちらが推奨ですか
スカラの代替値で済むなら第4引数が最短です。行ごとに違う代替値を出したい場合だけIFNAに切り替えてください。1万行規模で計算時間を測っても、両者に体感差はありませんでした。
VLOOKUPはスピル時代でも使えますか
Excel 2024(Microsoft 365)では、VLOOKUPに配列の検索値を渡すとそのままスピルします。ただしExcel 2019・2016など動的配列非対応の環境で開かれるファイルでは旧来の書き方に戻してください。
IFERRORとIFNAはどう使い分けますか
IFNAは#N/A専用、IFERRORは全エラー対応です。IFERRORは#DIV/0!や#REF!などのバグも無差別に握り潰してしまうため、本来は気付きたい別のエラーが見えなくなるリスクがあります。意図が明確なIFNAを優先してください。
スピル範囲を選択してからIFNAを入れれば効きますか
スピル範囲に別の式を入力するとアンカーが#SPILL!で崩壊するため、この方法は使えません。必ず親セル(アンカー)の式を=IFNA(...)の形に書き換えてください。
まとめ
- XLOOKUPはスカラ代替なら第4引数、行別代替ならIFNA外包
- VLOOKUP・INDEX+MATCH・LOOKUPはIFNA外包一択(Excel 2024は配列検索でスピルする)
- HSTACK・VSTACK・BYROWで合成した配列は外側にIFNAを1つ
- FILTERの空結果は#CALC!になるため第3引数で対処する
- IFNAは#N/A専用で、#CALC!・#DIV/0!・#VALUE!は拾えない
- IFERRORは全エラー対応だが、バグを隠蔽するリスクがある
- スピル範囲の個別セルに書き込むと親アンカーが#SPILL!で崩壊する
XLOOKUPの基本的な使い方はXLOOKUP関数の使い方、複数条件での検索はExcelでXLOOKUP関数の複数条件検索を行う方法、XLOOKUPで複数列をまとめて取りたいときはXLOOKUP関数で複数列のスピル配列検索結果を作成する方法を参照してください。
IFERROR による全エラー一括処理と IFNA による #N/A 限定処理の使い分け、スピル範囲に IFERROR をかける場合の挙動については、IFERROR 関数 完全リファレンス で詳しく解説しています。
TEXTSPLIT で分割した結果から「2 番目」「最後」「N 番目」の要素を INDEX や TAKE / DROP で取り出したい場合は、Excel TEXTSPLITで2番目・最後・N番目の要素を取り出す（6パターン） もあわせてご覧ください。CHOOSECOLS / INDEX / TAKE+DROP / TEXTBEFORE+AFTER / BYROW など 6 パターンで使い分ける方法をまとめています。
コメント