Unsplash@spaxex

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

スポンサードリンク

 

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

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

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

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

上図のように、この場合商品注文歴表のA3にある品番「1007」が「検索値」、別表(商品一覧)のデータ部分(G3~I9)がデータ「範囲」とし、その左端の列を縦に探して一致したG9セルと同じ行の2列目にある商品名「コエンザイム」を表示します。このとき、G3~I9セルにドラッグしてコピーした際に別表の参照範囲がずれないよう「絶対参照」にしておきます。

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

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

Chek①!-$の入力、「A3」を「$A3」と列だけの絶対参照に

絶対値の「$」は直接入力してもいいのですが、「F4」キーを使うと簡単に入力することができます。「F4」を連続して押すと、「$A$3」(行列ともに固定)➡「A$3」(行だけ固定)➡「$A3」(列だけ固定)➡「A3」(固定しない)の順番に切り替わります。

上図の商品名にVLOOKUP関数を入力する際、「A3」を「$A3」と行だけ固定に指定すると、縦にドラッグしても横にコピぺしても修正することなくVLOOKUP関数を正しく入力することができます。

VLOOKUP関数【例題1】

※例題は有料版にてご提供しています。(例題1は有料版お試しで無料で利用できます)

解説を読み例題を解くことでVLOOKUP関数の基本をマスターできます。例題には「例題」シートと「解答」シートの2つのシートがあります。例題シートの例題を実際に解いて、解答シートで確認出来ます。

有料版はこちら➡エクセルVLOOKUP関数でほかの表から必要なデータを自動転記の基礎と応用の保存有料版

 

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

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

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

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

IFERROE関数は、エラーの場合「A」とそうでない場合「B」とで値を変える関数。つまり、=IFERROE(A,B)となります。上図では「A」にはVLOOKUP関数の式が指定されています。このVLOOKUP関数の結果がエラー表示になれば「B」に「””」を入力して「空欄」を表示するということになります。

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

 

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

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

 

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

VLOOKUP関数【例題2】

※例題は有料版にてご提供しています。

解説を読み例題を解くことでVLOOKUP関数の基本をマスターできます。例題には「例題」シートと「解答」シートの2つのシートがあります。例題シートの例題を実際に解いて、解答シートで確認出来ます。

有料版はこちら➡エクセルVLOOKUP関数でほかの表から必要なデータを自動転記の基礎と応用の保存有料版

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

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

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

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

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

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

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

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


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

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

VLOOKUP関数【例題3】

※例題は有料版にてご提供しています。

解説を読み例題を解くことでVLOOKUP関数の基本をマスターできます。例題には「例題」シートと「解答」シートの2つのシートがあります。例題シートの例題を実際に解いて、解答シートで確認出来ます。

有料版はこちら➡エクセルVLOOKUP関数でほかの表から必要なデータを自動転記の基礎と応用の保存有料版





 

今回のまとめとお知らせ

今回のまとめと有料版記事のお知らせがあります。

まとめ

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

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

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

お知らせ

今回の記事に例題を用意して、別途有料版でお届けしています。解説だけではなかなか理解しずらい点、例題に取り組むことでしっかりとマスターすることができます。例題は完成版に例題、そして解答をセットし解説と合わせて実際に関数を入力し、期待通りの結果が表示できるか学習と確認ができます。

有料版の例題についてのお問い合わせについては、メールにて対応致しております。なお、有料版は広告は一切ありません。ぜひ、ご利用ください。

VLOOKUP関数をマスターされる際に、少しでもお役に立てましたらうれしく思います。

 

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

Microsoft Officeランキング

スポンサードリンク