Excel関数の組み合わせについてお探しですね。
広告
Excel 鉄板の組み合わせ！IF関数とVLOOKUP関数をネストして空白やエラーを制御する
Excelで商品コードや社員番号を入力すると、別の表から名前や値段を自動で表示してくれるVLOOKUP関数。
実務では本当によく使われる便利な機能です。
でも、検索する値が空っぽのときに「#N/A」っていうエラーが出たり、何も入力していないのに0が表示されたりすると、見た目が悪くなってしまいます。
帳票や管理表として使うときに、これがあるだけで「まだ途中なのかな？」と思われてしまうことも。
そんなときに役立つのが、IF関数とVLOOKUP関数を組み合わせて、空白やエラーの表示をコントロールする方法です。
この記事では、基本的な考え方から実務ですぐ使える数式、気をつけるポイントまで、分かりやすく解説していきます。
IF関数とVLOOKUP関数を組み合わせる理由
VLOOKUP関数は、指定した値を表の一番左の列から探して、同じ行にある別の列の値を取ってくる検索の関数です。
例えば、A列に商品コード、B列に商品名、C列に単価がある「商品マスタ」を用意しておけば、入力した商品コードに対応する商品名や単価を自動で表示できます。
請求書、見積書、在庫表、顧客管理表など、同じ情報を何度も入力する業務では欠かせない関数なんです。
ただ、VLOOKUP関数をそのまま使うと、検索する値が空白だったり、マスタに該当するデータがなかったりすると、エラーが表示されてしまいます。
代表的なのが「#N/A」というエラーで、これは「検索した値が見つかりませんでした」という意味です。
エラー自体はExcelが正しく異常を知らせている状態なんですが、帳票として印刷したり他の人に見せたりするファイルでは、エラー表示が残っているだけで未完成に見えてしまいます。
そこでIF関数を組み合わせると、「検索する値が空白なら何も表示しない」「入力されているときだけVLOOKUPを実行する」といった制御ができるようになります。
IF関数は条件によって表示する結果を切り替える関数なので、VLOOKUPの前に判定を入れることで、不要な検索処理や見た目のエラーを減らせるんです。
つまり、VLOOKUPが「情報を探す係」、IFが「表示するかどうかを判断する係」になるわけですね。
基本の形は次のように考えると分かりやすいです。
“`excel
=IF(検索値=””,””,VLOOKUP(検索値,範囲,列番号,FALSE))
“`
例えば、A2セルに商品コードを入力して、商品マスタがF2:H100にある場合、商品名を取得する式は次のようになります。
“`excel
=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$100,2,FALSE))
“`
この式では、A2が空白なら空白を返して、A2に値が入力されていればVLOOKUPで商品名を探します。
最後の「FALSE」は完全一致を意味していて、商品コードや社員番号のように正確に一致させたい検索では基本的に指定しておくべきものです。
省略すると近似一致として動いてしまう場合があって、意図しない結果を返すことがあるので、実務では明示しておくと安心です。
検索値が空白のときに何も表示しない基本式
IF関数とVLOOKUP関数の組み合わせで一番よく使うのは、入力するセルが空白のときに結果欄も空白にする式です。
例えば、見積書で商品コードを入力する前から商品名欄に「#N/A」がズラッと並んでいると、入力する人にとっても確認する人にとっても見づらい表になってしまいます。
入力前の行は空白のままにしておいて、コードが入った行だけ自動表示されるようにすると、表全体がスッキリします。
基本の数式は、検索する値のセルが空白かどうかを先に判定します。
A2に商品コードを入力して、F2:H100の商品マスタから2列目の商品名を返したい場合は、次のようにします。
“`excel
=IF(A2=””,””,VLOOKUP(A2,$F$2:$H$100,2,FALSE))
“`
この式の読み方は、「もしA2が空白なら空白を表示して、そうじゃなければVLOOKUPを実行する」です。
IF関数の1つ目に条件、2つ目に条件が当てはまる場合の結果、3つ目に条件が当てはまらない場合の結果を指定します。
VLOOKUP関数は3つ目に入っているので、A2に値があるときだけ実行されるんです。
ここで大事なのは、空白を表す「””」の使い方です。
ダブルクォーテーションを2つ続けた「””」は、Excelでは空文字と呼ばれていて、見た目上は何も表示しない値として扱われます。
セル自体には数式が入っていますが、画面上は空白に見えるので、帳票や一覧表の見た目を整えるのに便利です。
ただし、完全な未入力セルとは違うので、別の集計や判定で影響が出る場合があることは覚えておきましょう。
また、参照する範囲には絶対参照を使うのが基本です。
上の例では「$F$2:$H$100」のようにドル記号を付けています。
これを付けずに下の方向へコピーすると、参照範囲が「F3:H101」「F4:H102」のようにずれてしまって、正しく検索できなくなることがあります。
VLOOKUPを複数行にコピーして使う表では、検索範囲を固定する意識が大切です。
ちなみに、Excelのデータを表形式で管理している場合は、範囲をテーブル化しておくとより扱いやすくなります。
テーブルにすると行を追加しても参照範囲が自動で広がるし、数式の意味も読み取りやすくなります。
例えば商品マスタを「商品表」というテーブルにしておけば、構造化参照を使って管理しやすい式にできます。
大量のデータや長く使う業務ファイルでは、範囲指定だけじゃなくてテーブル化も検討してみるといいですよ。
#N/AなどのエラーをIFERRORで制御する
IF関数で検索値の空白を制御しても、入力されたコードがマスタに存在しない場合は、VLOOKUP関数が「#N/A」を返してしまいます。
これは「該当する値が見つからない」という意味なので、数式の動きとしては間違いではありません。
でも実務では、「未登録」「確認してください」「該当なし」など、使う人に伝わりやすい表示に変えたほうが親切ですよね。
こういうエラー制御には、IFERROR関数を組み合わせる方法がよく使われます。
IFERROR関数は、数式がエラーになった場合に指定した値を返す関数です。
VLOOKUP全体をIFERRORで囲むと、検索値が見つからない場合でも見やすい表示にできます。
“`excel
=IF(A2=””,””,IFERROR(VLOOKUP(A2,$F$2:$H$100,2,FALSE),”未登録”))
“`
この式では、まずA2が空白かどうかをIF関数で判定します。
A2が空白なら何も表示せず、A2に値があればVLOOKUPを実行します。
その結果、該当するデータが見つかれば商品名を表示して、見つからずエラーになった場合は「未登録」と表示します。
空白の制御とエラーの制御を分けて考えることで、式の意味が理解しやすくなるんです。
エラーのときに空白を表示したい場合は、次のように「未登録」の代わりに「””」を指定します。
“`excel
=IF(A2=””,””,IFERROR(VLOOKUP(A2,$F$2:$H$100,2,FALSE),””))
“`
ただし、何でも空白にすればいいとは限りません。
入力ミスやマスタの登録漏れに気づきたい表では、エラーを空白にしてしまうと問題が見えなくなる可能性があります。
例えば請求金額や在庫数に関わる表で、該当なしを空白にしてしまうと、あとから「未入力なのか」「検索できなかったのか」が区別しにくくなります。
業務で使うファイルでは、見た目のきれいさとミス発見のしやすさのバランスを考えることが大事です。
IFERROR関数は便利なんですが、すべてのエラーをまとめて処理してしまう点にも注意が必要です。
VLOOKUPでよく出る「#N/A」だけじゃなくて、列番号の指定ミスによる「#REF!」や、数式の構造に問題がある場合のエラーまで隠してしまうことがあります。
検索値が見つからない場合だけを処理したいなら、IFNA関数を使う選択肢もあります。
IFNAは「#N/A」だけを対象にするので、数式ミスを見逃しにくいのが利点です。
“`excel
=IF(A2=””,””,IFNA(VLOOKUP(A2,$F$2:$H$100,2,FALSE),”未登録”))
“`
初心者のうちはIFERRORでも十分実用的ですが、業務ファイルの品質を高めたい場合は、どのエラーを隠してよいのかを意識すると安全です。
特に複数人で使うファイルでは、単にエラーを消すんじゃなくて、入力する人が次に何をすべきか分かる表示にすることが大切ですよ。
実務で失敗しないネスト式の作り方と注意点
IF関数とVLOOKUP関数を組み合わせると、空白やエラーを柔軟にコントロールできますが、式が長くなるほどミスも増えます。
特に多いのは、カッコの閉じ忘れ、参照範囲のずれ、列番号の指定間違い、完全一致の指定漏れです。
数式を一度に完成させようとすると原因を見つけにくくなるので、まずVLOOKUP単体で正しく検索できることを確認して、そのあとIFやIFERRORを追加する順番がおすすめです。
実務で使いやすい完成形としては、次の式を覚えておくと便利です。
“`excel
=IF(A2=””,””,IFERROR(VLOOKUP(A2,$F$2:$H$100,2,FALSE),”未登録”))
“`
この式は、A2が空白なら空白、A2に入力があれば検索、見つからなければ「未登録」と表示する構成です。
見積書、発注書、社員名簿、商品台帳など、いろんな業務表に応用できます。
単価を返したい場合は列番号を3に変えて、部署名を返したい場合はマスタ表の該当列に合わせて列番号を調整します。
一方で、VLOOKUPには構造上の制限もあります。
検索する値は指定範囲の一番左の列に置く必要があって、戻り値はその右側の列から取得します。
また、列番号で戻り値を指定するので、マスタ表の途中に列を追加すると、意図しない列を参照してしまうことがあります。
列の追加や削除が頻繁にある表では、マスタの構成を固定する、テーブル化して管理する、またはExcel 365やExcel 2021以降で使えるXLOOKUP関数を検討するのも一つの方法です。
ただし、VLOOKUPは古いExcel環境でも使えるので、社内でバージョンが混在している場合には今でも有力な選択肢です。
XLOOKUPが便利だからといって、すべてのファイルを置き換える必要はありません。
共有する相手のExcelバージョン、既存ファイルとの互換性、担当者の理解度を踏まえて選ぶことが大切です。
特に長年使われている業務ファイルでは、安定して動くVLOOKUPにIFやIFERRORを加えるだけでも、十分に使いやすさを改善できます。
また、空白やエラーの制御は、見た目を整えるだけじゃなくて、作業ミスを減らすための設計でもあります。
検索値が未入力の行は空白にして、マスタに存在しない値は「未登録」と表示して、数式ミスは隠しすぎないようにする。
この考え方を持っておくと、単に数式をコピーするだけじゃなくて、業務に合った表を作れるようになります。
最後に、IF関数とVLOOKUP関数を組み合わせるときは、まず「何を空白にしたいのか」「どのエラーを表示したくないのか」「入力する人に何を知らせたいのか」を決めてから式を作ると失敗しにくくなります。
Excelの関数は、複雑にするほど高度に見えますが、実務で大切なのは誰が見ても意図が分かって、あとから修正しやすいことです。
IFとVLOOKUPの鉄板の組み合わせを正しく使えば、入力前の空白制御、未登録データの表示、帳票の見た目改善まで、日々のExcel作業を大きく効率化できます。
ぜひ明日からの業務で活用してみてください！
広告
