エクセルのVLOOKUP関数はIF関数同様とてもよく使う定番中の定番の関数。何しろシンプルなのにエレガントなふるまいは奥深く、利用する頻度も高く何しろ効率よろしく重宝します。入力のスピードアップはもちろん、転記のミスや漏れもないからとても安心です。IF関数はもちろん、IFFEROR関数、MATCH関数、INDEX関数とメジャー級の関数との組合せも豊富に使えます。今回はVLOOKUP関数の使い方の基本をはじめ、それら関数との組合せもいくつか解説してのご紹介です。

 

注文票など品番を入れると別表にある商品名や単価を自動的転記

商品注文歴に品番を入力すると、同じシートの別の位置にある商品一覧表から、同じ品番の商品名と単価を自動転記する。いちいち商品名や単価を入力する必要がなく、品番の番号だけ入力すれば目的が果たせる。転記ミスもなくとても便利で効率がよくなります。

Vlookup関数-表の左列を検索して指定した列にある値を取り出す

＝VLOOKUP（検索値、範囲、列番号、検索の型）
■「検索値」を「範囲」の左列で探し、見つけた場所から右へ「列番号」の位置にある値を取り出す。「検索の型」が「FALSE（完全一致）」の時には、「検索値」と一致する値を探して表示する。

下図のように、商品注文歴表のA3にある品番「1007」が「検索値」、別表（商品一覧）のデータ部分（G3～I９）がデータ「範囲」で、商品一覧の「品番」を探して一致した品番と同じ行にある2列目にある商品名「コエンザイム」や3列目の「単価」を商品注文歴の表の商品名や単価に取り出して表示することができます。

 

このとき、G3～I9セルにドラッグしてコピーした際に別表の参照範囲がずれないよう「絶対参照」にする必要があります。

 

「絶対値」の指定は、入力した「G3;I9」をマウスで範囲指定した後にF4（ファンクション４）を1回押すことで絶対値マーク「＄」をつけて「$G$3:$I$9」と「絶対参照」に設定することができます。

同じように単価もVLOOKUP関数で転記します。この場合はVLOOKUP関数の構文のうち、「列番号」を「３」にすれば商品一覧表の3列目の単価が表示されます。

自動転記時のエラー表示を消すIFERROE関数、IF関数の組合せ

上図の品番が空欄（未入力）だと、VLOOKUP関数の計算結果がエラー表示となってしまいます。このときエラーメッセージを表示させないよう別の関数を組み合わせて対応をします。

IFERROR関数-式がエラーなら別処理でエラーを表示させない

＝IFERROR（値、エラーの場合の値）
■「値」に指定した式がエラー出ないときはその結果を表示し、エラーの場合は「エラーの場合の値に」に指定した内容を表示する。

IFERROE関数は、エラーの場合は「A」を表示するようにエラー時に表示を指定する関数です。つまり、ここでは「A」のVLOOKUP関数の結果がエラー表示になれば、その値を「””」を入力して「空欄」を表示するということになります。

同じく上図「金額」の計算結果も「C3*D3」がエラーであれば「空欄」を表示したいので、式は=IFERROE(C3*D3,””)となります。

IF関数とVlookup関数-組合せでエラーメッセージを消す関数式

=IF（論理式,真の場合,偽の場合）
■「論理式」にしていした条件が成り立つときには「真の場合」、成り立たないときには「偽の場合」を選びます。

上図のように、IF関数を使ってVLOOKUP関数の結果に表示されるエラーメッセージを消すこともできます。IF関数とVLOOKUP関数の組合せを使います。この組み合わせも頻度高く使います。IF関数は「論理式」が成立すれば「真の場合」、そでなければ「偽の場合」を表示となります。よって、上図での「論理式」は検索値「A3」が「空欄」ならば「空欄」を表示してそうでなければVLOOKUP関数を計算して結果を表示となります。

列番号指定を自動化するMATCH関数とINDEX関数との組合せ

VLOOKUP関数で厄介なと思う問題がこの列番号の指定です。検索範囲が比較的小さな範囲であったり、転記先が単発あるいはその数が少ない場合はその都度列番号の指定をすればいいのですが、範囲が大きいデータであるとか、転記するデータが多い場合、あるいはその並びが順番通りでないときなど、いちいち列番号指定が面倒であり大変になることがあります。

そこで「この列番号の指定を自動化できないか」というケース。ここでは、列番号の指定をMATCH関数で置換え、VLOOKUP関数との組合せで解決することができます。

MATCH関数-範囲内での位置を調べる

＝MATCH（検査値、検査範囲、照合の型）
■「検査値」が「検査範囲」の先頭から何番目にあるかを数値で示す。「照合の型」を「０」にすると、「検査値」と完全に一致する値を探す。

MATCH関数が指定した「検査値」が、「検査範囲」の先頭から何番目にあるかを数値で示すので、指定する列番号と同じ結果となり、直接その数を指定することなく自動的に列番号を指定して結果を示してくれます。つまり、列番号をMATCH関数で置換えればよいということになります。照合の型は通常「0」とします。

INDEX関数とMATCH関数-組合せでVlookup関数を使う

=INDEX（範囲,行番号,列番号）
■上から何行目かを示す「行番号」と、左から何列目かを示す「列番号」を指定し、「範囲」の中から該当位置にあるデータを取り出す。

下図のように商品注文歴は商品名が左端に、商品一覧の単価と品番との関係が別々に用意されているような場合、残念ながらVLOOKUP関数で転記することができません。そこで商品めいからいったん品番をINDEX関数で転記した上で、転記された品番をVLOOKUP関数を使って単価を商品一覧から転記して注文歴をまとめて合計を表示したケースです。

INDEX関数にMATCH関数を組み合わせたのは、INDEX関数の列番号を自動的に表示するためで、IFERROR関数を使って、商品名がないセルの検索は空欄に表示するようにした。商品のラインナップが膨大で複雑な管理が強いられている場合などでVLOOKUP関数が直接利用できないときには有効です。

今回のまとめ

VLOOKUP関数はIF関数同様、とてもよく利用する関数のひとつです。商品のラインナップから見積もり書や発注書、あるいは請求書などにデータを転記して利用するのはもちろん、職場の管理業務や顧客管理にも最適です。入力作業のスピードが格段に効率よくなりミスの防止にもつながります。

扱うデータが大きくなればなるほど威力を発揮するだけに、基本をマスターして活用を図りたいものです。また、エラーメッセージを消す必要性に、転記するデータの指定にMATCH関数と組合わせるなどして、より利用しやすいオリジナルなVLOOKUP関数の利用が期待できます。

今回はIFERROR関数、MATCH関数、INDEX関数との組合せは少々マニアック的なところもありますが、それぞれの構文をしっかりと理解できると、それは積み木のような組合せになるので、積極的に利用してみると関数の幅がより広がると思います。

いかがでしたでしょうか。

 

最後まで読んでくださりありがとうございます。人気ブログランキングに参加中。こちらクリックして頂けましたらうれしく思います。
↓↓↓

Microsoft Officeランキング