• このエントリーをはてなブックマークに追加

【Zaco Excel】VLOOKUP関数で検索した値を抽出しよう

  • このエントリーをはてなブックマークに追加

複数表のデータ管理をするならまずは知っておこう

こんにちは。Zaco Ushioです。
Excelには便利な関数が沢山ありますが、本日は複数のデータ(表)を管理するのにまずは知っておきたいVLOOKUP関数について紹介しよう。

「この表のこの列の値を参照して、あの表のこの列の値を引用したい」

みたいな時に使用する。

スポンサーリンク
Sponsords Link

VLOOKUP関数の使い方

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

まずは難しいことより、下記より、端末管理表とスイッチポート収容表の管理方法を例としたファイルをダウンロードしてみてほしい。

VLOOKUPの使用例ファイルをダウンロードはこちら

◆VLOOKUP使い方

まずは目指す結果

<端末一覧>
下記のような各部に設置しているパソコンやサーバなどの端末、IPアドレスを管理している一覧表があったとする。データが一元管理される「マスター表」の位置づけだ。
※当方ネットワークエンジニア故、例が一般的じゃないかもしれないですが悪しからず。。

VLOOKUP_端末一覧

<スイッチポート収容表>
端末一覧にある端末を、スイッチのポートにどのように収容するか、ポート収容表に管理したい。VLOOKUPの出番となるのが、上記端末一覧「マスター表」からポート収容表へデータを引用する部分だ。
※VLOOKUPを使用しなければ、「マスター表」を参照しながら数値をコピーペースト、コピーペースト…。「マスター表」の端末一覧に変更が発生すれば、ポート収容表も修正…。そう一番嫌な、データの二重管理となり二度手間三度手間になる。

VLOOKUP_ポート収容

今回の例では、端末IDを投入すれば、端末IDに一致する”用途”と”VLAN ID”を表示させる。
※まどろっこしいですが、”絶対参照($)”をあえて無しで進め、後から修正する流れで解説しています。

①検索値(引用するキーとなる値)を設定
=VLOOKUP(検索値,範囲,列番号,検索方法)

検索値とは…「マスター表」端末一覧と一致して、データを引用させるキーとなる値

=VLOOKUP(C4…端末IDを投入するセルを選択,

VLOOKUP_ポート収容2

②範囲(引用元となるマスター表)を設定
=VLOOKUP(検索値,範囲,列番号,検索方法)

範囲とは…引用元となるマスター表で、必ず”検索値”が一番左の列となるよう範囲を選択する

=VLOOKUP(C4,端末一覧!B3:I37…マスター表,

VLOOKUP_ポート収容3

★コツ
該当の表のデータ部分の一番左上(検索値を一番左の列とする)を選択。
Ctrl + Shift + ”→キー”
Ctrl + Shift + ”↓キー”
どうですか?データ部分が一発で選択できると思う。
Excelショートカットキーの記事も参照

③列番号(引用したいデータの列数)を設定
=VLOOKUP(検索値,範囲,列番号,検索方法)

列番号…「マスター表」端末一覧の”検索値”列から何列目か

=VLOOKUP(C4,端末一覧!B3:I37,2…2列目の用途を引用したい,

VLOOKUP_ポート収容4

④検索方法(近似一致/完全一致)を選択
=VLOOKUP(検索値,範囲,列番号,検索方法)

検索方法…TRUE(近似一致)かFALSE(完全一致)を選択。
機会があれば応用編で書きますが、基本的にFALSE(完全一致)を使用することとしましょう。

=VLOOKUP(C4,端末一覧!B3:I37,2,FALSE)

VLOOKUP_ポート収容5

これで引用できましたね。

⑤あとは必要なセルに同様にVLOOKUPの数式をコピー
!!!!!!!!!!→ここで問題が起きる。起きるよね。このままコピペすると、”検索値”も”範囲”もズレてしまうのだ。

こうしないといけない。絶対参照を指定。

=VLOOKUP($C4,端末一覧!$B$3:$I$37,2,FALSE)

ショートカットキーの記事でも書いたが、データを選択した時点で”F4”キーを押せば絶対参照が選択できる。上記のようにしておけば、入力セルの下にコピぺ(もしくはオートフィル)しても、隣の列にコピペしてもズレないですね。

※例
$C4→ C列は固定して、行数は変動
C$4→ C列は変動、4行目を固定
$C$4→ C列、4行目両方を固定

いかがでしたでしょうか。
VLOOKUPは色々な応用が可能な関数ですね。Excelをデータベースっぽく使用する際には必ず必要となります。是非習得しましょう。

Zaco Ushio

スポンサーリンク
Sponsords Link
  • このエントリーをはてなブックマークに追加

ZacoDesign

スポンサーリンク
Sponsords Link