【第103回】
「VLOOKUP」関数の検索値が空欄の場合に
表示される「#N/A」を見えなくするには
「VLOOKUP」関数を用いると、別の表から値を引用することができます。具体的には、見積書の「製品コード」欄に製品コードを入力すると、製品台帳から該当する製品コードの「製品名」や「単価」を表示するといった使い方をします。
その際、「製品コード」欄に入力した製品コードが製品台帳に存在しなかった場合に「#N/A」エラーとなるのは入力値の間違いですので、正しい製品コードを入力すれば解決します。しかし、「製品コード」欄に製品コードを入力していない場合に表示される「#N/A」は純粋なエラーではありませんので、何とか見えなくしたいものです。
こういった場合、「IF」関数を用いて「製品コード欄が空欄だったら何もせず、空欄でなかったら「VLOOKUP」関数を用いて製品台帳から製品名や単価を引用する」といった数式を設定するのが本来の方法ですが、既に設定してある数式をあとから手直しするのはかなり面倒な作業となります。
そこで、「条件付き書式」機能を用いて、「#N/A」エラーを見えなくする方法を紹介しましょう。
見積書の「製品名」欄と「単価」欄に「VLOOKUP」関数を用いて、「商品の定価表」からそれぞれ製品名と定価を引用する数式を設定する
製品コードを入力すると、きちんと製品名と定価が引用された
先頭行の「VLOOKUP」関数の数式を下の明細行にコピーした場合、製品コードが入力されていないと、該当するデータが「商品の定価表」にないため、「#N/A」エラーとなってしまう
見積書の明細行〜合計行をすべて選択したうえで、「書式」メニューから「条件付き書式」を選択する
「条件付き書式の設定」ダイアログの「条件1」欄で「数式が」を選択したうえで、その横の欄に「=ISNA(C12)=TRUE」といった数式を設定する。その際、「ISNA」関数の引数は相対参照にすること（絶対参照から相対参照に変えるには［F4］キーを何度か押す）
「条件付き書式の設定」ダイアログの「書式」ボタンをクリックすると、「セルの書式設定」ダイアログが呼び出されるので、条件を満たした場合の書式として、フォントの「色」欄を「白」に設定する
「条件付き書式の設定」ダイアログに戻ったら、［OK］をクリックして、同ダイアログを閉じる
これで明細行の「N/A」は見えなくなったが、「合計」欄までも（N/Aエラーとなっているため）見えない状態になっている
「金額」欄の数式を、「SUMIF」関数を用いて、明細行の「金額」欄の値が0より大きいセルだけを対象に合計を求めるように変更すると、正しい合計値が表示される