Chiquilin Site■05.06.18_Excel：検索結果が空白の時に空白を返す
★その１：「近似値検索」と「絶対一致検索」
まずはじめに。数式は「空白」を返すことができません。何もないセルを参照すると結果は「0」になります。数式でよく使われる「""」は「空白（Null）」ではなく「空白文字列（vbNullString）」なので「0文字の文字列」として扱われます。ここを勘違いしていると思わぬところで計算ミスを起こすので注意が必要です。検索系の関数で参照したセルに何も入力されていない時「0」ではなく「""」が返るようにしたい場合は少々手間がかかります。エラー対策まで一緒にやろうとすると更に長ったらしくなりがちです。
検索の関数は大きく分けて二種類あります。「近似値検索」と「絶対一致検索」です。
●近似値検索 (あいまい検索)：
LOOKUPと検索の型が「TRUE」の時の VLOOKUP・HLOOKUP関数
検索結果が一致しない時は近似値(検査値未満の最大値)が返ります。
MATCH関数も 検索の型を「1」か「-1」かで近似値検索できます。
●完全一致検索 (絶対検索)：
検索の型が「FALSE」の時の VLOOKUP・HLOOKUP関数
完全に一致した時のみ結果が返ります。不一致の場合は「#N/A」が
返ります。
MATCH関数では 検索の型を「0」にすると 完全一致検索となります。
どちらの方法もそうですが 検索結果に該当するものがない場合は「#N/A」が返ります。
★その２：簡単なエラー対策
検索結果が「#N/A」にならないようにする為には 数式を工夫する必要があります。
確実なのは検索範囲のリストを検査値入力のリストに利用する方法です。これを使えば検査範囲に該当する名前しか入力できなくなります。
まずはこのようなリストを作成します。
E3セル上で [データ]→[入力規則]
→「設定」タブの[入力値の種類]から[リスト]を選択
→[元の値]を「B3:B12」に設定して[OK]
こうしておけば「該当しない」はずはありませんから エラーは返りません。ただしこの方法はリストの値に重複がないことが前提です。範囲を名前定義すれば別シートにリストを作成することもできますがリストが沢山ある時には 余計に煩雑になってしまいます。
次に数式で対処する場合の方法です。先ほどと同じシートだとして
■F3セル
=IFERROR(VLOOKUP(E3,B3:C12,2,FALSE),"")
★Excel2003以前の場合
=IF(ISNA(MATCH(E3,B3:B12,0)),"",VLOOKUP(E3,B3:C12,2,FALSE))
もしくは
=IF(COUNTIF(B3:B12,E3)=0,"",VLOOKUP(E3,B3:C12,2,FALSE))
ここまでは簡単です。
★その３：検査結果が空白の時に「0」ではなく空白を返す
大抵の場合は「その２」で解決しますが このままでは検査結果に何も入力されていない時「0」になってしまいます。
1. [ツール]→[オプション]→「表示」タブから[ゼロ値]のチェックを外す
2. [表示形式]の[ユーザー定義]を「#」にする
などでも回避できますが この場合 検査結果が「0」の時にも同様に空白が返ってしまいます。検査結果が必ず文字列なら 数式の最後に「&""」を付けてやれば良いだけなのですが……
これらを一緒にやろうとすると
1. 「#N/A」エラー対策
2. 参照元が空白の時
3. 参照元が「0」の時はエラーとみなさない
のみっつに対応させる必要があります。IF関数でネストしてやっても良いのでしょうが 長くなりますので数式を単純にしたいなら以下の方法がよいかと思います。
■F3セル
=IF(ISERROR(1/LEN(VLOOKUP(E3,B3:C12,2,FALSE))),"",
VLOOKUP(E3,B3:C12,2,FALSE))
→参考ページ (008.xls)
参照元のセルが空白の時は LEN関数は「0」を返します。それを分母として除算することで 空白の時だけ「#DIV/0!」エラーが返るようにします。ISERROR関数は「#N/A」でも「#DIV/0!」でもエラーなら対象に含まれますから 結果として上記のみっつの条件を満たすことになります。
つまり「
=IF(ISERROR(1/LEN(数式)),"",数式)」のように設定すればよいです。
検索結果が数値の時は「=IFERROR(1/IFERROR(1/数式,""),"")」のようにしてもいいかと思います。
