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と同様ですので、ここでは解説を割愛します。

【便利知識】

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

テーブルができたら、VLOOKUP関数を使って、テーブル表から合致するデータを抽出（「表引き」）することになります。

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

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

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,[FY17単価表.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」エラーの元になります。必ず、絶対参照で範囲指定するか、テーブル名を定義して指定しましょう。

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

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

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

【便利知識】

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