スポンサーリンク

表の中からデータ検索して指定した値を見つけるVLOOKUP関数

エクセル関数エクセル数式

こんにちは、とりっぷぼうるです。

元々エクセルとは全く縁のない生活をしていましたが、人員不足などで少しでもデータ入力作業や集計作業を効率化するしか方法が無く、仕方がなくエクセルに取り組み始まました。

波乗りアヒル
波乗りアヒル

作業効率化にはユーザーフォームを使いますが、様々な計算などはエクセル関数が必須です。

エクセル関数を使うことができないと、VBAどころじゃありませんし、成績表も作る事ができないのです。

当然ながらエクセルの専門知識もなく、学校に通うこともできずにただ単にエクセルの書籍とにらめっこしながら取り組んでいます。

波乗りアヒル
波乗りアヒル

でも書籍のままだと実用的でないことが多く、エクセル関数を組み合わせてみたら動いた!という実用例・体験談を書いています。

Excelの専門学校通学や・資格があるわけではありませんので、エクセル関数を組み合わせてみたらエラーにならずに動いてくれているだけなので、掲載の数式などが同じように動く保証はできません。
実際に動いているエクセルは2009と2016です。
スポンサーリンク

VLOOKUP関数をマスターするって必要?

表の中から特定の値を指定してデータ検索する事ってよくありませんか?

見つけたセルの2列右の値とか。

ボウリングでのリーグ戦のレーン抽選でも参加選手の中から番号によりレーン・投球順を決めるので非常によく使いますので、VLOOKUP関数を使って解決しています。

エクセルをうまく使っている人はVLOOKUP関数を駆使していますよ!

波乗りアヒル
波乗りアヒル

そんな事言っても、検索値,範囲,列番号,検索方法ってのがよくわからない!

そうなんです。VLOOKUP関数って使いこなせば便利で作業効率がアップするけど、検索結果がエラーになったり、何となく苦手意識ありませんか?

ここでは実例がボウリングになってしまいますが、番号と名前と他って考えれば、一般的な顧客名簿や商品管理台帳と同じように応用できますよ!

VLOOKUP関数とは?

エクセルのVLOOKUP関数とは、検索のデータ元となる表、簡単に言うと台帳みたいなものかな?

ボウリングのリーグ戦での参加者一覧表です。

この参加者一覧表から、指定した行のセルの値を探して表示させる時に使います。

=VLOOKUP(検索値,範囲,列番号,検索方法)

こう書いてあっても難しいですよね。

範囲

この図は、データの重複チェックでも使用したのですが、先ほどの検索の元データとなる表です。

AQ列が登録順番号、AR列が会員番号(顧客番号や商品番号に置き換えてもいいかな)AS列が名前(商品名に置き換えてもいいかな)こんな感じの元データです。

VLOOKUP関数の式で言うと、「範囲」(AP3:AV42)になります。

COUNTIFで重複データチェック

サンプルなので一部のみ表示(42行目まで実際使用)

実際にはレーン抽選フォーム(ユーザーフォーム)を使って入力したレーン番号と投球順がAW列・AX列に自動入力され、AP列に「&」を使ってAWとAXを結合させた値を自動的に割り振っています。

レーン抽選を行うと下の図のようにレーン番号(AW列)・投球順(AX列)・割り当て(AP列)にそれぞれ自動入力されました。

レーン抽選後

これがレーン抽選後の元データとなります。

検査値

では、次に元データの表から見つけたい値についてです。

簡単に説明すると、このセルの値(数字)を元データから探すって感じで、探す=見つける=検査する=「検査値」と書くと分かりやすいかな?

VLOOKUP関数でデータ検索

スペースの関係で一部抜粋しています

上の画像の通り、VLOOKUP関数を使っていますが、AW列が検査値になっています。

セルAQ103には、検査値AW103の値が91なので元データの範囲(AP3:AV42)の中から探してくれます。

同じようにセルAR103にもAS103にもって感じで緑色のセルには全てVLOOKUP関数が入っていて、同じ行の検査値(AW列)から、登録順・会員番号・名前・AVE・HDCPを元データから探すようになっています。

当然ながら、AW列の値と元データのAP列の値が同じ値が無いと見つけられないって事で、見つければ、表示されることになります。

これにより、レーン抽選した番号などが元データにはランダムに入力されているのですが、この表で番号順に整列させてレコードシートへ転記するようにしています。

通常の並び替えで処理すると、空欄が出た場合に繰り上がってしまって誤ったレコードシートができてしまうのを防止するためです。

参考までにここでの数式にはVLOOKUP関数の前にIFERROR関数を入れてあります。

IFERROR関数が無いとエラー表示になるので、使っています。

VLOOKUPでデータ検索2

このエクセル関数を使っています

エクセル計算式が#VALUE!エラーのセルを空欄にする方法
ボウリングリーグ戦の成績表をエクセルで作っていて、参加者成績一覧表で「#VALUE!」になる事があります。このエラー表示が出た時だけ空欄にしてしまう事ができれば、参加者へ配布できますね。簡単にできるのでエラー表示防止にIFERROR関数の使い方

列番号

列番号は元データの表の検査値と同じ値(数字)の列から右に何列目にあるデータを(セルの値)取り出すかという事です。

セルAQ列は「登録番号」なので元データの表を見ると、右隣ですね。

右に1列だから「1」としたいところなのですが、検査値と同じ列も含めるので「2」となります。

セルAR列は「会員番号」なので、友データの表を見ると、2列右なので「3」、という風に列番号を必要なデータに合わせて変更します。

せっかくなのですべて書くと、「名前」は「4」、「AVE」は「6」、「HDCP」は「7」です。

この為に、元データの表の検査値と同じ値は一番左側の列に配置する必要があります。

Excel-VBAではOffset(0,-1)が使えるからといって、VLOOKUP関数でやるとエラーになりました。

検索方法

最後に検索方法ですが、省略することもできますが私自身は省略した事はありません。

ボウリングのリーグ戦での成績表でも、会員名簿でも、検査値が番号や数字の時だけVLOOKUP関数を使うので、完全一致だけ検索対象にしているからです。

検査値と完全一致する場合のみデータ検索をするには0(ゼロ)を使います。

波乗りアヒル
波乗りアヒル

もちろん画像の通りここでも0を使用しています。

こうすることで、検査値に該当しない値はエラーで、見つけられれば欲しい値が表示されます。

検索方法を省略または1を入力した場合

先ほど書いた通り、省略することもできます。(1も同様)

この場合、見つからなかった場合にエラーにならずに、一番近い数字を勝手に表示してしまいます。

ということで、関係ない数字を出してしまうので個人的には使い物にならないって思っていますので、この方法では使っていません。

波乗りアヒル
波乗りアヒル

何となくVLOOKUP関数をわかってもらえたかな?

VLOOKUP関数のエラー

スポンサーリンク



VLOOKUP関数を使い始めた頃は、複雑な数式なので苦労しました。

エラー表示や思った答えと違う場合などありましたが、番号を入力するだけでデータ検索して表示されるので、間違えが無くなりましたし、日本語文字入力よりは数字の方が入力しやすいので、作業効率がアップしますね。

最後にありがちなエラーになる原因を書いておきました。

違う列の値が表示される

検査値と同じ値を含めて列番号を指定していますか?

列番号が合っているのに違う値が表示される時がある

検索方法を完全一致の「0」を省略していませんか?

見つけられない!

検査値と同じ値の列が元データの表の最左列になっていますか?

検査値または、元データの検査値と同じ値が数式で計算された値になっていませんか?計算された値は数字として認識せず文字列として扱われていますのでVALUE関数で変換しましょう。

このエクセル関数が解決します

計算式の値を数字として読み取るVALUE関数
VLOOKUP関数やVBAで数値を基にデータのやり取りをする時に、直接入力した数字以外はエラーになります。計算式の値は見た目は数字ですが、実は文字列として扱われているのでエラーなんですね。そんな文字列になっている値を数字として扱えるようにしましょう。
エラー表示を空白や他の文字・数字に代えたい

このエクセル関数が解決します

エクセル計算式が#VALUE!エラーのセルを空欄にする方法
ボウリングリーグ戦の成績表をエクセルで作っていて、参加者成績一覧表で「#VALUE!」になる事があります。このエラー表示が出た時だけ空欄にしてしまう事ができれば、参加者へ配布できますね。簡単にできるのでエラー表示防止にIFERROR関数の使い方
スポンサーリンク

作業効率化に絶対必要なエクセル関数

  1. 指定したセルまたはセルの範囲の合計を計算【SUM関数】
  2. 指定範囲セルの入力済みセルの数を数える【COUNT関数】
  3. 指定範囲セルの平均点(アベレージ)を計算【AVERAGE】
  4. もし男性なら「1」そうでなく女性ならば「2」と振り分ける【IF関数】
  5. 小数点以下の処理【INT関数】
  6. 指定範囲セルの最大値を計算【MAX関数】
  7. 指定範囲セルの最小値を計算【MIN関数】
  8. 条件に当てはまる数値の合計を抽出【SUMIF関数】
  9. 表の中からデータ検索して指定した値を探す【VLOOKUP関数】
  10. 日付を入力するとデータが差し替わる表を作る【DGET関数】
エクセル使うならば、関数とVBAを組み合わせて作業効率化しませんか?
波乗りアヒル
波乗りアヒル

エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?

コメント