表の中から探すエクセル関数VLOOKUP

会員番号で選手名を入力で使うエクセル関数

2017年7月1日更新

ボウリング場でのリーグ戦などの成績表でリストの中から選手名を探すことってありますよね。例えば、リーグ参加者の会員番号と名前と性別を記録している会員名簿があり、その中からリーグ参加者を会員番号で探すことなど。会員番号と名前を記憶していれば早いかもしれませんがそうはいかないですね。そんな時に便利なエクセル関数がVLOOKUPですね。

スポンサーリンク


VLOOKUP関数

VLOOKUP関数は調べたい元となる会員名簿(リスト)一覧表を用意したうえでの話になりますが、探したい範囲の中から条件に一致したデータを取り出してくれる関数です。ボウリング風に書くと、会員番号を入力しただけで、その会員番号の名前や性別を探して表示してくれます。

エクセル数式vlookup

エクセル数式vlookup

上の図で左の表がリーグ参加者一覧表、右の表が会員名簿一覧表です。セルB列に参加選手の会員番号をを入力すると、会員名簿一覧表から名前と性別を探してくれてC列に名前を、G列に性別を表示してくれます。

列Dと列Fはボウリングリーグ戦でHDCPを使うと思いますが、リーグ戦のルールで様々なHDCPがあると思います。男女別HDCPなど西船ボウルでは7種類のHDCPのルールが組み合わせでできているので、複雑です。この列の使用例は、エクセル数式を組み合わせてHDCPを計算するページを別途作りましたので、そちらをご覧ください。

エクセル数式組み合わせでHDCPを計算する
男女別HDCPや先投げHDCPは別途計算など

VLOOKUP関数を数式で書く

エラー表示(#N/A)となっている部分にVLOOKUP関数を入れてありますが、エクセルの検索結果を表示したいセルにVLOOKUP関数を入力する下記のようにように表示されますよね。

=VLOOKUP(検査値、範囲、 列番号、[検索方法])

ボウリング風に書き換えると、図の左の表(参加者リスト)の登録番号1番の人であれば、

=VLOOKUP(会員番号[セルB5]、会員名簿[セルJ5からL15までの範囲]、会員名簿の左から2列目(K列)の名前、完全一致だけ選ぶ)

と、なりますね。数式に直すと

=VLOOKUP(B5,J5:L15,2,FALSE)

となります。

こうすると、リーグ参加者1番の人の会員番号を入力すると名前が会員名簿の中から探して表示されます。次に性別ですが、同じ数式ですが列番号を変えるだけです。会員名簿の2列目が名前で3列目(L列)が性別になっているので2を3に変更すると性別がG5に表示されます。

リーグ登録者2番目の方の時は、1番目の検査値がB5なので、2番目に方はB6に変更するだけです。これを繰り返せば、完成しますがいちいち数式を書き続けるのは大変なのでコピーしてエクセル作業効率をアップしたいですよね。ただし、単純にセルのコピーをしてしまうと数式が狂っちゃいます。

一番最初にやりがちなコピーはマウスの右クリックしてコピーを選んで貼り付けをしちゃいますが、そうすると検査値のB5がB6・B7となってコピーしてくれるのはいいのですが、検査範囲のJ5:L15までJ6:L16・J7:L17という具合に一緒にずれてしまいます。こうなってしまうと会員名簿のリスト外になってしまいます。ということは、探せなくなります。そこでコピーする時はセルをコピーしてもずれないようにロック(固定)しましょう。

会員名簿を固定してコピー

エクセルの本では、セルを固定することを「絶対参照」そして、コピーしたらずれてしまうことを「相対参照」と書いてあります。先ほどの例で挙げると、セルB列はコピーしたらずれてほしいので「相対参照」、会員名簿のセルJ5:L15はコピーしてずれてはいけないので「絶対参照」にすれば、解決します。では絶対参照にする方法ですが、とても簡単です。

$マークをつけるだけ!

固定したいセル範囲「J5:L15」に$マークをつけると「$J$5:$L$15」となります。これだけで会員名簿一覧表はロックされているのでコピーしても大丈夫になりました。

3種類のLOOKUP関数

エクセルLOOKUP関数には実は3種類あります。

  1. LOOKUP関数
  2. VLOOKUP関数
  3. HLOOKUP関数

この3つとも全て、表の中から探す役割は同じなのですが、微妙に違います。西船ボウルのリーグ戦の成績表では、会員名簿から参加者登録に使うVLOOKUP関数と、リーグスケジュール対戦表からレコードシートにレーン番号や選手名などを記載する際にHLOOKUP関数を使っていて、LOOKUP関数は使っていません。3つの違いはこのようになっています。

LOOKUP関数

表の中から探す役割のエクセル関数ですが、リストに空欄が無ければよいのですが、会員名簿は空き番号があったり退会者など番号があっても名前が空欄になります。そうすると一番近い番号の人の名前が表示されてしまうので、不都合があり使っていません。

VLOOKUP関数

エクセルVLOOKUP関数

リストの中から横方向に探してくれます

 

すでにご紹介の通りです。

西船ボウルのリーグ成績表で3種類のLOOKUPの中でいちばん多く使っています。

探したいリストの中から縦方向に探してくれます。

HLOOKUP関数

対戦表の中から探すHLOOKUP関数
リーグ対戦表からレーン番号を割り当てる
エクセルHLOOKUP関数

対戦表のリストの中から縦方向に探してくれる

 

VLOOKUP関数と基本的には同じなのですが、探したいリストの中から縦方向に探してくれます。

リーグ対戦表からレーン番号を割り当てる時に使っています。

詳しい説明は長くなるので、対戦表ページを今度作りますね。

スポンサーリンク









24時間以降~180日後までボウリングもネットで簡単ご予約ができます。
もちろん空き状況も15分単位で確認できます。