【スプレッドシート】VLOOKUPで別シートの値を参照する方法

業務では何かと使用頻度の高いVLOOKUP関数。

基本的な知識から別シートの表を参照する方法などの解説をしていきます。

 

 

テンプレート

「細かい説明はいいからすぐ使えるテンプレートが欲しい！」

そんな時には下記コードをコピペにて使用してください。

大体これで事足りると思う。

より詳しく中身を知りたい方は読み進めてもらうと一個一個説明していくよ！

IMPORTRANGEとは？

まず、最も重要な関数である「IMPORTRANGE」

これは別のスプレッドシートを読み込み、さらに読み込んだシートの範囲まで拾ってくれるスプレッドシート独自の関数だよ。

構造はこんな感じ

第１引数には読み込みたいスプレッドシートのURL

第２引数には第１引数で読み込んだスプレッドシートの参照したい範囲

実際に参照してる風景を画像付きで説明すると

• 

まずは適当にスプレッドシート上に表を作りました。

この表を参照していきたいので、上記URLをコピーしておきます。

• 

次に新しいブックを作ってそこに関数を入力。

sampleってブックのB2からD9までのセルを範囲に拾いたいので

このようなコードを入力しております。

通常であればこれで表示される・・・が

あれれ～？なんかエラーになってるぞ？

 

「独立したいけどスキルなし」の私が独立して法人化まで達成した方法

 

実はこれ、「読み込むための許可」が必要ですよって言うエラーなんです。

セルにマウスをあわせると「許可しますか？」みたいなポップアップが出てくるので「OK」を押せば

• 

このように無事に表示がされます。

「・・・。」

作った表と違いますねぇ～。

なぜなんでしょう？

答えはシートの指定をしていないので１ページ目のシートを参照しているのです。

• 

今回はシート３７に作ってあるので、範囲の前に「シート37!」を付け加える事で、無事参照出来ました。

バックカラーやボーダーみたいな装飾は読み込まないようですね。

VLOOKUPの基礎知識

VLOOKUP関数についての説明になります。

まずは関数の構成です。

第１引数には検索ワード

第２引数には範囲

第３引数には第２引数で指定した範囲内の番号

第４引数はtrueで近い数字、falseで完全一致。

第４引数に関してはとくにこだわりが無ければfalseがデフォルトになると思います。

では、IMPORTRANGEで取得した表を元にVLOOKUPで抽出してみましょう。

• 

今回は検索ワードを変動させたいので「F行」に検索キーワードを入れています。

検索ワードを参照したVLOOKUPのコードがこちら

F5セルの文字と一致する物をB4からB11までの範囲内で探して、完全一致したセルの範囲指定した範囲の左から２番目のセルを呼び出す。

最初の範囲がB行から始まっているので、２番目となるとC行が呼び出されます。

VLOOKUP並べ替え済みをTRUEにすると？

VLOOKUPの「並べ替え済み」通常はfalseで完全一致として使うと思うのですが、これをTrueにした場合はどのような挙動になるのか？

下記、検証した画像になります。

• 

IDの数字を適当に順不同にして並べ替えてみました。

そして検索キーワードはF行です。

結果としては

「検索キーワードを上から探して行き、現在の検索ワードが検索対象のワード以下ならば数字が低い方を参照する。」

さらにこれ

「一度通り過ぎた数字は戻って検索しない」

っというオプション付きです。

言葉にすると非常に難しいですねぇ。

一個ずつ解説していくと

• F3はB3と一致しているのでC1を抽出
• F4はB6と一致しているのでC6を抽出
• F5はB6からスタート、最低値が４で一致する物が無いので低い方の２であるB6に切り捨てられてC6を抽出
• F6はB8と一致しているのでC8を抽出
• F7、F8、F9はF8から始まり、全て9以下なのでB8の数値が近いとされC8が抽出

一体どういう使い方をする時に使うのかちょっと不明ですねぇ。

まとめ

エクセルでは簡単に出来る「他のシートの値を参照させる」っと言った行為もスプレッドシートだと関数必須になるので、結構手間ですね。

VLOOKUPで一番の難敵は第４引数である「並び替え」の部分だと思います。

この記事を読んでもよく分からない時にはとりあえず「false」を入れときましょう。

googleさんにはデフォルトをfalse設定にしておいて欲しいと切に願っています。