スプレッドシート 作業効率化

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

投稿日:

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

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

 

テンプレート

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

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

 

 

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

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

 

IMPORTRANGEとは?

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

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

構造はこんな感じ

 

 

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

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

 

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

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

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

 

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

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

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

 

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

あれれ~?なんかエラーになってるぞ?

 

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

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

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

 

「・・・。」

 

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

なぜなんでしょう?

 

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

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

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

 

VLOOKUPの基礎知識

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

まずは関数の構成です。

 

第1引数には検索ワード

第2引数には範囲

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

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

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

 

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

 

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

 

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

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

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

 

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

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

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

 

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

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

結果としては

 

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

さらにこれ

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

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

 

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

一個ずつ解説していくと

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

 

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

 

まとめ

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

 

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

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

 

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

-スプレッドシート, 作業効率化
-,

Copyright© Well Magazine , 2019 All Rights Reserved Powered by STINGER.