大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel関数 Vlookupの$を使った便利な使い方についてご紹介します。$を使えば取得したい範囲がずれることがないので便利です。

はじめに

こちらの記事ではExcelの関数 Vlookupについてご紹介します。

vlookupは一覧表から任意のデータを取得することができますので大変便利です。

この記事をよむメリット

  • Vlookupの基本的な設定がわかります
  • Vlookupに『$』を使って簡単にデータを取得することができます

目次から見たい項目へ移動すると便利ですよ。

目次

Excel関数 Vlookupでできること

Excel関数のVlookupは任意の範囲から指定したデータを取得することができます。

Excelの関数でもかなり使い勝手のいい関数で、私もよく使っています。

Excel関数 Vlookupで任意の範囲から指定したデータを取得できます。

ここでは果物の列のNo.1の行のデータ『レモン』を取得しています。

ExcelVlookup取得結果

指定するのは取得する範囲、検索値、列番号、完全一致or不完全一致です。

4つの引数があります。それぞれどのように設定するか説明していきます。

引数に指定するもの(取得範囲、検索値、列番号、完全一致or不完全一致)

引数としては2番目ですが、先に取得範囲を説明します。

Excel関数のVlookupで範囲を設定

Excel関数のVlookup 範囲を設定

赤枠で囲った部分が取得する範囲になります。

この範囲で指定したところをVlookupで取得することができます。

Excel関数Vlookupで検索値を設定

Excel関数Vlookup 検索値を設定

この範囲の中で左端の列で取得したい値を指定します。

『検索No.』のセル『B9』に入力した値を取得したいので検索値は『B9』を指定しています。

Excel関数Vlookupで列番号を設定

Excel関数Vlookup 列番号を設定

範囲を指定した中の取得したい列番号を指定します。指定した範囲の一番左が1になります。

果物の列は指定した範囲の2列目になりますので、『2』を指定しました。

Excel関数Vlookupで完全一致or不完全一致を入力する

Excel関数Vlookup 列番号を設定

完全一致(FALSE)or不完全一致(TRUE)の引数は入力しない場合不完全一致(TRUE)になります

ここは迷わず『FALSE』を入力した方がいいかと思います。

不完全一致『TRUE』だと予期しない値が入力されて検索ミスにつながったりします。

例えば、セル『B9』の検索No.に『13』を入力すると値が近い『3』の値を拾ってきたりします。

なので、今回は『FALSE』を使って完全一致した値のみが検索結果となるようにしています。

注意点(検索値は、一番左の列でしか検索できません)

Excel関数Vlookupで検索する値は必ず左端でしか指定できませんので注意が必要です。

Vlookupを使う場合は表を作成する最初の段階で検索する値を一番左にしておくといいかと思います。

Excel関数 Vlookupでデータを取得する($を使わない場合)

Vlookupの引数を1つずつ入力していたら大変なのでできれば1つだけ入力してあとはコピーしたいです。

ですが、『$』を使わないで、Excel関数のVlookupを入力したセルをコピーすると検索値と取得する範囲がずれます

データ範囲と検索値がずれる(横にコピーした場合)

Vlookupの数式を入力したセルを横方向にコピーしてみます。

『$』を使わない場合Excel関数Vlookupを横方向にコピーするとずれます

Excel関数Vlookup 横方向にコピーした場合ずれます

この場合一見するとうまくいったように見えますが、検索値と範囲がずれてしまいます。

横方向にコピーするとずれています

1列目です。

Excel関数Vlookup 横方向にコピーした場合の1つ目

2列目です。検索値と範囲を『$』で固定していないのでずれます。

Excel関数Vlookup 横方向にコピーした場合の2つ目がずれる

3列目です。ずれていってしまいます。

Excel関数Vlookup 横方向にコピーした場合の3つ目がずれる

4列目です。もちろんずれます。

Excel関数Vlookup 横方向にコピーした場合の4つ目がずれる

検索値と範囲を『$』で固定していないのでずれていってしまいます。

データ範囲と検索値がずれる(縦にコピーした場合)

縦にコピーした場合も同じで検索値と範囲がずれてしまいます。

縦方向にコピーしてみます。

『$』を使わない場合Excel関数Vlookupを縦方向にコピーするとエラーになります

Excel関数Vlookup 縦方向にコピーした場合の結果エラーになる

縦方向にコピーするとわかりやすくエラー#N/Aが出ています。

縦方向にコピーするとずれます

1行目です。

Excel関数Vlookup 縦方向にコピーした場合の1つ目

2行目です。検索値と範囲がずれます。

Excel関数Vlookup 縦方向にコピーした場合に2つ目ずれる

3行目です。1行ずつずれます。

Excel関数Vlookup 縦方向にコピーした場合に3つ目ずれる

4行目です。もちろんずれます。

Excel関数Vlookup 縦方向にコピーした場合に4つ目ずれる

検索値と範囲を指定しないとずれてしまいますので、次のように『$』を使うとコピーしても検索値と範囲がずれません。

Excel関数関数 Vlookupでデータを取得する($を使った場合)

『$』を使って検索値と範囲を固定してみます。

Excel関数Vlookup 『$』でデータを固定する

方法は簡単で、検索値と範囲の変数の部分に『$』をつけるだけです。

横方向にコピーしてみます。

データ範囲と検索値が固定される(横にコピーした場合)

『$』で検索値と範囲を固定して横方向にコピーした場合です。

2列目です。検索値と範囲がずれていません。

Excel関数Vlookup 『$』を使って横方向にコピーしてもずれない2つ目

3列目です。検索値と範囲がずれていません。

Excel関数Vlookup 『$』を使って横方向にコピーしてもずれない3つ目

4列目です。検索値と範囲がずれていません。

Excel関数Vlookup 『$』を使って横方向にコピーしてもずれない4つ目

検索値と範囲がずれなくなりました。

縦方向でも同じようにコピーしてみます。

データ範囲と検索値が固定される(縦にコピーした場合)

縦方向にコピーしても『$』で固定しておけば検索値と範囲はずれません。

1行目です。検索値と範囲を固定しています。

Excel関数Vlookup 『$』を使った縦方向の場合

2行目です。検索値と範囲はずれていません。

Excel関数Vlookup 『$』を使って縦方向にコピーしてもずれない2つ目

3行目です。検索値と範囲はずれていません。

Excel関数Vlookup 『$』を使って縦方向にコピーしてもずれない3つ目

4行目です。検索値と範囲はずれていません。

Excel関数Vlookup 『$』を使って縦方向にコピーしてもずれない4つ目

ただし、Vlookで検索したい値が検索できていませんので、次の『セルに入力した列番号の参照』を行うとコピーで簡単にほしい検索値を取得できます。

『$』と『セルに入力した列番号の参照』の組み合わせが便利です

Vlookupの式の中の『列番号』を直接入力してもいいのですが、式を一つ一つ入力し直すのは少し手間です。

なので列番号を入力するセルを1行追加します。

列番号を入力するセルを追加します

セルを1行追加して、列番号を示す2~5を入力しています。こんな感じです。

列番号を入力するセルを追加します

この追加したセルをVlookupの『列番号』に参照して列番号を指定します。

列番号をセルから参照します

参照するセルはすぐ上のセルを指定します。コピーしたとき自動で横方向にセルの値を変化させたいので『$』で固定はしません。

列番号をセルから参照します

列番号をセルから参照したらあとは数式を入力したセルをコピーするだけです。

あとは横にセルをコピーすると検索値を取得できます

横方向にセルをコピーします。

あとは横にセルをコピーすると検索値を取得できます

これで、取得したい『果物』から『魚』の項目を取得することができます。

列番号をセルから参照しておけば変更したい場合も変更が簡単なのでおすすめです。

気になる場合は列番号の行を非表示にするといいです

列番号を参照した行が気になる場合は行を非表示にすると見た目がよくなりますので、気になる場合は非表示にするといいです。

気になる場合は列番号の行を非表示にするといいです

これでVlookupで任意の範囲から指定した行の値を取得することができます。

縦方向でも同じように『列番号を参照するセル』を追加すれば簡単にコピーするだけで指定した値を取得することができます。

おわりに

ExcelでVlookupを使う際は、『$』を使って検索値と範囲を指定するとコピーしても検索値と範囲がずれることがありませんので、数式を作成する際に便利です。

Vlookupの『列番号』を指定する際はセルを追加してそのセルから『列番号』を参照すると簡単にコピペで数式を作成できますし、列番号を変更する際も簡単なのでおすすめです。

最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す