VLOOKUP関数

Excelの入力作業で、時間を無駄にしていませんか？

例えば、毎月作成する請求書。

「商品名は…天ぷらそば、単価が…えーっと864円」といちいち手入力していては、入力ミスも起きるし、時間もかかってしまいます。

予め、単価表を作っておいて、リストから商品を選ぶと、自動的に単価も引っ張って入力してくれる。こんなことを実現してくれるのが、VLOOKUP関数です。

 

VLOOKUP関数は「表引き」、すなわち、予め用意されているテーブル（表）のデータを参照して、編集中の表やフォームに値を自動入力するための関数です。この関数を入力用のシートに組み込んでおけるかどうかで、作業スピードや正確性に格段に差が出ます。

 

メリットは入力時の作業スピードや正確性が向上するだけではありません。テーブルを変更すると、VLOOKUP関数で自動入力されたデータも、すべて一度に更新されます。ひとつひとつ修正する手間がかからず、修正漏れも発生しません。

 

非常に便利なVLOOKUP関数ですが、実は「数式が長くてややこしい！」と苦手意識を持つ人が多い関数でもあります。頑張って、VLOOKUP関数の数式の意味合いを理解して、使いこなしましょう！

 

VLOOKUP関数は、テーブル（表）を縦に検索して、検索条件に合致する値を持つセルを見つけたら、同じ行で、そのセルの右側にある指定された列のセルの値を取り出す、というものです。

 

VLOOKUP関数の形式は次のようになっています。

　＝VLOOKUP（①、②、③、④）

　　①　検索キー（が置かれたセル）

　　②　テーブルの範囲（または予め定義されたテーブル名）

　　③　ヒットした時に取り出すセルの列位置

　　　　　左端の列を1とする列の相対的な番号を指定します。

　　　　　（例）右隣のセルであれば2、さらに右のセルなら3

　　④　検索条件

　　　　　通常は0（またはFALSE）を指定します。　　

　　　　　　0（またはFALSE）：完全一致、見つからなかった時は「#N/A」エラーが出る

　　　　　　1（またはTRUE、または省略）：検索キーを超えないテーブル上の最大値の行が選ばれる

 

【便利知識】

VLOOKUP関数のVはvertical（垂直に）の頭文字から来ています。垂直にLOOKUP（検索）するのでVLOOKUPです。似たような関数で、HLOOKUP関数、つまりテーブルの先頭行を水平に（horizontal）探すというものがありますが、一般にテーブルは行ごとにアイテムを増やす形で作成されますので、VLOOKUP関数の方が圧倒的によく使われます。

 

【便利知識】

VLOOKUP関数を含む数式はドラッグなどでコピーされることが多く、コピー先の数式内で参照範囲が変わらないようにするため、②のテーブルの範囲は「絶対参照」の形で記述します。

 

【便利知識】

④の検索条件で、1（またはTRUE）は、例えば成績表の「0点～30点未満はE、30点～50点未満はD、50点～70点未満はC、70点～90点未満はB、90点～100点はA」というような時に使われます。

0点から100点まで1点刻みのテーブルにして、検索条件を0（またはFALSE）にしても良さそうですが、73.5点など小数点の付いた点数にも対応しようとすると、テーブルが膨らんで大変です。1（TRUE）の検索にすれば、テーブルは「0　E」「30　D」「50　C「70　B」「90　A」「100　A」の6行分で済みます。　

 

【便利知識】

④の検索条件を手前の「,」も含めて省略すると1（TRUE）と見做されます。最後に「,」が付いていると0（FALSE）と見做されます。非常に紛らわしいので、④は省略しないことをお勧めします。

 

VLOOKUP関数を用いるには、まず検索の対象となるテーブル（単価表など）を作成する必要があります。

 

ここでは、単価表と請求書を例にとって説明します。

まずは、参照の対象となるテーブル（この例では「出前単価表」）です。

 

テーブルを準備する上での留意点には次のようなことが挙げられます。

・検索対象はテーブルの一番左（左端）の列です。それ以外の列を検索対象にすることはできません。

・検索する順は、縦、つまり上から下にです。

・左端の列に重複（同じ文字列）が無いように注意します。

　（仮に重複するものがあった場合、下の方の行が選ばれることはありません。）

・左端の列の文字列の中に「空白文字」が混じらないように注意します。

　（文字列の間や前後に空白文字が混じっていると、「#N/A」エラーが起きがちです。）

・左端が空白セルの行（全くの空白行も含む）が混ざらないように注意します。

　（商品などが増えることに備えて、テーブルの後ろに予備の行を設けることは構いません。）

・テーブルの大きさ（列数と行数）に制限はありません。（Excelの制約は受けますが・・）

・テーブルの作成場所に制限はありません。

・検索条件で1（またはTRUE）を使用する時は、左端の列が昇順で並んでいる必要があります。

 

【便利知識】

「#N/A」エラーが出るということは、該当のものが見つからなかった、つまり、検索キーとして入力した文字列が間違っている（入力ミス）、テーブルに登録されている検索対象のセルの文字列が間違っている（テーブル作成）、VLOOKUP関数で参照するテーブルの範囲が間違っている（ずれている、など）、などが原因です。根気よくエラーの原因をつぶしましょう。

 

【便利知識】

テーブルは必ずしもVLOOKUP関数を実行するシートと同じシートに在る必要はありません。むしろ、テーブルだけのシートを別にする方が普通です。テーブルを別のExcelファイルの形にすることもできます。

　（例）同じシート　　　=VLOOKUP(F5,$B$2:$D$18,2,0)

　　　　別のシート　　　=VLOOKUP(B5,'単価表'$B$2:$D$18,2,0)　

　　　　別のファイル　　=VLOOKUP(B5,[F17単価表.xlsx]'単価表'$B$2:$D$18,2,0)　

　　　　　　テーブルのファイル（FY17単価表.xlsx）がExcelで開かれているとき

　　　　別のファイル　　=VLOOKUP(B5,[C:￥document￥[FY17単価表.xlsx]'単価表'$B$2:$D$18,2,0)

　　　　　　テーブルのファイル（FY17単価表.xlsx）が開かれていないとき

  

次に、テーブルを検索するシート（この例では「請求書」）です。

 

この例では、3桁の「商品番号」をB列のセルに入力すれば、自動的に同じ行のC列のセルに「商品名」、D列のセルに「単価」が入力され、後はE列のセルに「数量」を入力すれば、F列のセルに商品毎の「金額」と（この図には表れていないセルに）「合計（請求）額」が自動算出されることを想定しています。

 

上述のようにこの例のVLOOKUP関数は以下の形になります。

（例）請求書シートの5行目

　　C5のセル：　=VLOOKUP(B5,単価表!$B$2:$D$18,2,0)　　2：商品名は表の2列目

　　D5のセル：　=VLOOKUP(B5,単価表!$B$2:$D$18,3,0)　　3：単価は表の3列目

 

【便利知識】

商品番号が未入力の時点では、上述のようなVLOOKUP関数のみからなる数式の箇所には「#N/A」、計算式だけの数式の箇所には「#VALUE」が表示されてしまい、見苦しくなります。

これを避けるため、一般的に次の例のように「IF関数」と組み合わせて、あるセルが空白だったら、このセルは空白、さもなければこの数式を実行、という形の条件付き数式にします。

（例）請求書シート5行目の実際の数式　

　　C5のセル：　=IF(B5="","",VLOOKUP(Bn,単価表!$B$2:$D$18,2,0))

　　D5のセル：　=IF(B5="","",VLOOKUP(Bn,単価表!$B$2:$D$18,3,0))

　　F5のセル：　=IF(B5="","",D5*E5)　　　　　　　　　　　　単価（D5）x 数量（E5）

 

【便利知識】

この例のように、テーブルを検索するシートには複数の行があり、各行毎に検索条件（この例では商品番号）を変えた入力がされることが多いです。

一般的には、1つの行ができあがったら、その行のセル群（具体的にはB5～F5のセル）をまとめて選択し、

下にドラッグして一気に数式をコピーして完成させます。

VLOOKUPのテーブルの範囲指定が相対参照だと、コピー先の数式内でテーブル範囲がずれてしまい、「#N/A」エラーの元になります。必ず、絶対参照で範囲指定するか、テーブル名を定義して指定しましょう。

 

上述の例では「商品番号」というコードを使った検索でした。

コードを用いる方法は重複を防止でき、入力が簡単というメリットがありますが、一方で、コード管理をしなければならない、外部の人にコードは出てしまうのはまずい、などのデメリットもあります。

後者に対してはコード欄を非表示にする、あるいは印刷範囲に含めないというような逃げ手もありますが、面倒です。

 

【便利知識】

コード入力の代わりに、商品名をプルダウンリストにして選ぶ形ですれば、長い文字列を入力することなく、入力ミスも防げます。

 

プルダウンリストを作るセル（この例では、請求書2シートのB5）を選んで、「データ」タブの「データの入力規則」メニューから「データの入力規則」をクリック。

「データの入力規則」のダイアログボックスが表示されますので、「入力値の種類」を「リスト」に設定し、「元の値」欄の「↑」マークをクリック。商品名群（この例では、単価表2シートのB3からB18まで）を選んで「Enter」キーを押下。「OK」ボタンを押すと、当該セルではプルダウンリストから選ぶ形での入力しかできなくなっています。

プルダウンリストによる入力セルを縦にドラッグすればその次の行も同じプルダウンリスト入力のセルとしてコピーされます。

 

【便利知識】

リストにするデータ範囲（この例では単価表2シートのB3からB18まで）に名前（例：「品名」）を付けて、

「元の値」欄にその名前（例：「=品名」）を入力することもできます。

テーブルに名前を付ける場合と同様、プルダウンリストについても、名前を定義する方法の方が項目の追加などの時の修正が楽に行えて、スマートと言えます。