palm84.com

革命的愛熊主義者同盟！

トップ > Excel > Excel なんでやねん !　VLOOKUP関数の巻【Excel 2000 - 2003】

Excel なんでやねん !　VLOOKUP関数の巻【Excel 2000 - 2003】

Excel ぷらら (旧@Palm84 )から引っ越し
2021.4.1 移転しました...
  • このページの旧URLは
    • http://www10.plala.or.jp/palm84/vlookup.html
内容古いです！

最終更新日は 2005年(頃?)、ご注意。

私は現在、「Excel2000」しか持っていませんのでバージョン「97」「XP」「2003」などで若干の違いがあるかもしれません。ご了承ください。（堪忍でござるよ。）

INDEX

Excelの【VLOOKUP関数】ってなに？

サイドバー表示 on/off

一般的には垂直参照関数と呼ばれます。
VLOOKUPの「V」はバーチカル（vertical=垂直）、「LOOKUP」は look up で調べるという意味です。
行の上下の値を検索し同じ行の右側のセルの値を返し（表示し）ます。

ここでは「JANコード」を入力すると「商品名」「売価」などのデータが自動で表示されるシートを作成します。

Excelでは水平参照関数だと「HLOOKUP」になります。（「H」＝ホリゾンタル＝horizontal：水平）

検索表示だけでなく、応用した使い方として例えば、Accessに入力したデータ（テーブル）との照らし合わせ、入力漏れ・入力ミスなどのチェックも簡単に実行できますんで拙者の仕事もグ～ンと能率UPなのでござる。（by Hattori)

うまくいかない時は下記を参考に。

FAQ よくある質問

テーブル（参照用データ）の作成

この table とはつまり、データベースのことですね。

同じシート上でもいいのですが、できれば別シートで作成して下さい。

Accessのテーブルデータをシート全体にコピー＆貼付が簡単かもしれません。また、別ファイルからの参照もできます。

  1. 「JANコード」のセルを範囲選択
  2. 【セルの書式設定】→【表示形式】を【文字列】に設定

範囲選択は列全体でもいいのですがファイル容量が大きくなります。

注意点：JANコードから左列にあるデータは参照できませんので必要な場合は列を【切り取り】→右クリック【切り取ったセルの挿入】で調整して下さい。

テーブルに名前を付ける

関数入力の際、テーブルの指定はセル番号での指定でもいいのですが、テーブルの名前で指定する方が便利です。後から修正も簡単ですし。

  1. JANコード列を左にして範囲選択
  2. メニューの【挿入】→【名前】→【定義】をクリック
  3. 任意の名前を入力→【追加】→【OK】をクリック
  • テーブルの範囲選択

f:id:palm84:20210117224648p:plain

※選択範囲を列全体、またJANコードがA列でしたらシート全体を選択でもいいのですが、若干ファイル容量が大きくなります。かなり重くなってしまうことがあります。

  • 【名前の定義】ダイアログ

f:id:palm84:20210117224652p:plain

入力用シートの項目作成

完成後EXCELシートの例
A列 B列 C列 D列 E列
1 JANコード 商品名 サイズ 納価 売価
2 0000049401301 CABIN SUPER MILD ボックス 20本入り 300
3 490xxxxxxxxxx hogehoge ロシアサイズ(S) 12,345 19,800
4 490xxxxxxxxxx hogehoge アメリカサイズ(XS) 12,345 19,800
EXCELシートの作成例
A列 B列 C列 D列 E列
1 JANコード 商品名 サイズ 納価 売価
2 書式を【文字列】に！ （※後で） （※後で） （※後で） （※後で）
  1. 1行目の項目名を入力
  2. 2行目
    • 【A2】の書式を【文字列】に設定
    • 【B2】-【E2】に数式入力（※後述）

3行目以降は、2行目をコピー＆貼り付け（※オートフィルでも）するので、書式など2行目だけキッチリ設定しませう。

ここでの注意点が1つだけ→「JANコード」セルのみ表示形式を【文字列】に設定して下さい。

その他のセルは【標準】のまま、または【文字列】以外でで【数値】【ユーザー定義】でもいいです。

その他のセルを【文字列】に設定すると数式がそのまま表示されちゃいます。

関数入力

EXCELシートの例
A列 B列 C列 D列 E列
1 JANコード 商品名 サイズ 納価 売価
2 （※書式は【文字列】） ※数式入力 （※後で） （※後で） （※後で）
  1. 2行目
    • 【B2】に数式入力
  • 一例
    • B2 - =VLOOKUP($A2,data,3,FALSE)
=VLOOKUP(検索値,テーブル名,テーブルの列番号,型)

準備ができましたので、入力用（検索表示用）シートにやっとこさ「数式」の入力です。

  1. 数式を入力するセルをクリック
  2. メニューの【挿入】→【関数】をクリック（または関数貼り付け【fx】ボタン）
  3. 左側の【検索/行列】右側の【VLOOKUP】をクリック→OK
  4. 【数式パレット】に必要な項目を入力。
  • 【関数の貼り付け】ダイアログ画像

f:id:palm84:20210117224704p:plain

【数式パレット】が出ますので必要な項目を入力します。

赤丸部分をクリックするとパレットが折畳まれます。

  • 数式パレットでの入力

f:id:palm84:20210117224656p:plain

項目名 入力する引数・値
検索値 同じ行の「JANコード」セルを指定
（先頭に【$】をつけて下さい。）
範囲 先ほど付けた「テーブル」の名前「data」を指定
列番号 「テーブル」の「JANコード」列を１として何列目かを指定
検索の型 「false」を指定（完全一致）
  • 次の条件の場合の数式は下記の様になります
    • 数式を入れるセル - B2
    • 検索値を入れるセル - A2
    • テーブルの名前 - data
    • 表示するテーブルの列番号 - 3
    • 完全一致 - FALSE
=VLOOKUP($A2,data,3,FALSE)

つまり...

=VLOOKUP(検索値,テーブル名,テーブルの列番号,型)

検索値に【$】（絶対参照）を付けるのは、他のセルにコピーする場合に楽だからです。行番号は相対参照で変わりますが、（検索値の）列(A)はかわりません。“$A2”指定をB3にコピーすると、“$A3”となります。C4にコピーすると“$A4”となります。

※【数式バー】（メニューの【表示】→【数式バー】をチェック）を表示してる場合は下記の画像のやり方でもいいです。

  • 数式の入力

f:id:palm84:20210117224700p:plain

全体にコピー＆貼り付け

EXCELシートの例
A列 B列 C列 D列 E列
1 JANコード 商品名 サイズ 納価 売価
2 （※書式は【文字列】） ※数式入力済 ※ ※ ※

１個１個入力するのは面倒なのでコピー＆貼付で「列番号」のみ修正します。

  1. 2行目
    • 【B2】を【C2】から【E2】に数式コピーして、列番号のみ修正
  2. 行全体（2行目）を下の行へコピー

一旦同じ行のセルにコピーして数式の列番号を修正します。その後、行を全体にコピーします。

  1. 先ほど数式入力したセル（【B2】）をクリック
  2. 「フィルハンドル」（セルの右下の「＋」マーク）を横方向にドラッグ
  3. 横のセルをクリック→関数貼り付け【fx】ボタン（または「＝」ボタン）→【数式パレット】→「列番号」を項目に合わせて修正
  4. 数式の入った（行の）セル全体を選択→「フィルハンドル」を下方向にドラッグ

３のやり方で行全体のセルの数式を修正します。
３の「＝」ボタンが表示されていない場合はメニューの【挿入】→【関数】で。
フィルドラッグを使わずに普通にコピー＆貼り付けでも同じです。

・・・・・・・・・・・・・・・・・・・

以上で完成です。お疲れ様でござる。

エラーが出る場合とか、途中の設定がうまくいかないとかよくありますんで下の「FAQ」を参照して下さい。

FAQ よくある質問

「テーブル」にデータはあるはずなのにエラーが！

下記を確認して下さい。

  • 作成シートとテーブル双方の「JANコード」セルの書式設定は【文字列】になっているか？
  • 「4.90123E+12」のように「指数表示」になっていないか？
  • 元データの「JANコード」セルに「数値表示」などの書式設定がされていた場合再設定がうまくできていない場合があります。

データが少ないなら双方の「JANコード」セルを1個ずつWクリックしてみてください。

これで解決できないなら「書式の再設定」が必要と思われます。下記の指数表示の戻し方も参考にしてみて下さい。

なぜ【文字列】に設定するのか？

これは個人的主観というか環境によって違うのかもしれません。

  • 短縮JANコードの場合先頭に「00000」をつけますが「数値表示」では表示されません。
  • 関数から取り出したデータは【文字列】データになっている場合が多いので。（「VALUE」関数などで数値に変換することは可能ですけど。）
  • 色々試したんですけど「JANコード」のように12桁以上の数字の場合、【文字列】にした時が最もエラーが少なかったようです。（注意点はありますが。）
  • 「LEFT」関数などを使い「メーカー名」などの自動表示も簡単なので。
  • [01-09]のような表示形式のコードを検索値にしますと、標準では日付表示に変わってしまったりします。（この例ですと[1月9日]になります。）
未入力セルのエラー表示をなくしたい。

色々方法はありますが私はとりあえず「IF」関数を使ってます。

最初に入力したセルの数式↓を修正します。

=VLOOKUP($A2,data,3,FALSE)

↓↓↓↓↓↓

=IF($A2="","",VLOOKUP($A2,data,3,FALSE))

最後の括弧を忘れないように。

後は同様の手順でフィルドラッグ（コピー＆貼り付け）して下さい。

ファイルを開くのに時間がかかる。

数多くのセルに（標準以外の）「書式設定」がされているとファイルを開くのに20～30秒ほどかかる場合があります。（これはスペックの高いPC上でも同じです。）

テーブルのデータ件数が多い場合は要注意です。

一度シート全体の【書式のクリア】を実行し必要なセルのみ書式を再設定してみて下さい。

【書式のクリア】→メニューの【編集】→【クリア】→【書式】

「テーブル」シートを削除したい・・・「数式」を消したい・・・

入力（表示）用シートには検索結果が表示されていますがこれはあくまで計算結果なので「テーブル」を削除するとすべてエラーになります。またそのままコピー貼り付けしても数式がコピーされるだけです。

つまりは「値」として確定していません。印刷するだけならいいのですが表示シートのみ切り離すとすべてエラーになってしまいます。そこで【値の貼り付け】を実行します。

  1. Ctrl-Aキーで全て選択（または左上の[A]と[1]の間をクリック）
  2. 右クリック【コピー】そのままもう一度右クリック【形式を選択して貼り付け】→【値】にチェック→OK

これで数式がなくなり値が確定されます。

この【値の貼り付け】はとってもよく使いますんで「ツールバー」の【ユーザー設定】で常にボタンを表示しておくと便利かもしれません。（他にも【数式】のみ【書式】のみの貼り付けもできますし。）

指数表示の戻し方

Excelの仕様ですが12桁以上の数値は標準で指数表示となります。
→こんなかんじで→「4.90123E+12」
書式の再設定などでこの表示が出現！したりします。

もっと簡単な方法発見しますた！
「JANコード」セルを【文字列】に設定したら指数表示になってしまいますた～
  • データ件数が少ない場合は

セルをWクリック、または「F2」キー→「ENTER」キー

  • 元データの件数が多く、【数値】などで書式設定されている場合。

この場合、上記の方法では面倒なので。

  1. 元データを一旦テキスト（.txt）ファイルで保存します。
  2. Excelのメニュー【ファイル】→【開く】でファイルの種類を【テキストファイル】に指定して該当ファイルを選択します。
  3. ウィザード画面で該当列（「JANコード列」）を【文字列】に設定します。
  • Accessから貼り付けたデータなら

Ctrl＋Zキー、または【編集】→【元に戻す】を繰り返して貼付直後に戻した後、書式設定（【文字列】）します。

ほな！