見積書や請求書のひな型ファイルを作る際に、明細欄で VLOOKUP関数を使用すると、コード番号を指定するだけで商品リストから商品情報を表引きできるので便利です。ただし、明細欄のコード番号が未入力だと、VLOOKUP関数に「#N/A」エラーが出てしまいます。これを回避する方法を見ていきます。
作例…「#N/A」エラーの出ない明細書を作る
ここでは図1のような注文明細書で、商品情報の表引きと金額の計算が自動で行われるように、「商品名」「単価」「金額」の数式を考えます。
図1 商品名と単価、金額が正しく表示されるように、セルB3～C7とセルE3～E7に数式を入力していく。
作例
商品名を商品リストから表引きするには、VLOOKUP関数を使用します。商品ID が未入力のときにVLOOKUP関数の結果が「#N/A」エラーになるのを防ぐために、IFERROR関数を併用します。
エラーの場合に表示する値を指定する
=IFERROR(値, エラーの場合の値)
表を縦方向に検索してデータを取り出す
=VLOOKUP(検索値, 範囲, 列番号, ［検索の型］)
セルB3 | =IFERROR(VLOOKUP(A3,$G$3:$I$8,2,FALSE),"")
セルC3 | =IFERROR(VLOOKUP(A3,$G$3:$I$8,3,FALSE),"")
セルE3 | =IFERROR(C3*D3,"")
図2 セルB3、C3、E3に数式を入力して、7行目までコピーする。商品ID が入力されている行では、商品名と単価、金額が表示される。商品ID が入力されていない行は空欄になる。
作例解説
手順1
図2の数式を、順を追って説明していきます。まずはエラーを考慮せずに、セルB3の数式を立てます。
VLOOKUP関数の引数［検索値］に「L-101」を入力したセルA3、［範囲］に商品リストのデータが入力されているセルG3～I8を指定します。数式をコピーしたときに商品リストの範囲がずれないように、［範囲］は絶対参照で指定してください。［列番号］に商品名の列「2」を指定し、［検索の型］に完全一致検索（「L-101」と全く同じデータを検索すること）を意味する「FALSE」を指定します。
すると、商品リストの 1行目から「L-101」が検索され、見つかった行の「2」列目にある商品名が求められます。
セルB3のVLOOKUP関数の［列番号］の「2」を「3」に変えれば、単価が求められます。金額は「単価×数量」で計算します。
表を縦方向に検索してデータを取り出す
=VLOOKUP(検索値, 範囲, 列番号, ［検索の型］)
セルB3 | =VLOOKUP(A3,$G$3:$I$8,2,FALSE)
セルC3 | =VLOOKUP(A3,$G$3:$I$8,3,FALSE)
セルE3 | =C3*D3
図3 セルB3に VLOOKUP関数を入力して、商品名を表引きする。セルC3とセルE3にも数式を入力しておく。
手順2
手順1で入力した数式をコピーすると、商品ID が入力されている行では正しく表引きが行われますが、未入力の行にはエラーが表示されてしまいます。
図4 セルB3、セルC3、セルE3の数式をそれぞれコピーする。すると、商品ID が入力されていない行に「#N/A」エラーが表示される。
手順3
手順2のエラーが表示されないようにするには、IFERROR関数を使用します。引数［値］に図3の数式を指定し、［エラーの場合の値］に空の文字列「""」を指定すると、数式にエラーが出る場合にセルを空欄にできます。
エラーの場合に表示する値を指定する
=IFERROR(値, エラーの場合の値)
セルB3 | =IFERROR(VLOOKUP(A3,$G$3:$I$8,2,FALSE),"")
セルC3 | =IFERROR(VLOOKUP(A3,$G$3:$I$8,3,FALSE),"")
セルE3 | =IFERROR(C3*D3,"")
図5 セルB3、セルC3、セルE3の数式を修正して、コピーする。商品ID が入力されている行では、図4と同様に商品名、単価、金額が表示される。商品ID が入力されていない行は空欄になる。
メモ
