スプレッドシートで別ファイルのデータをVLOOKUPで取得する方法

★気に入ったらシェアをお願いします!


lookup

photo credit: IMG_5229 via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルを使っていらっしゃる方であれば多くの方が活用されているであろう関数VLOOKUP

特定のシートや範囲から特定の値で検索をする超便利な関数です。

Googleスプレッドシートでもエクセルと全く同じようVLOOKUP関数を使うことができるのですが、VLOOKUP関数で別のスプレッドシートを検索することができるのはご存知でしたか?

引き続きブログの低品質コンテンツを自動であぶり出すシステムを作成するために、この他のスプレッドシートのデータに対してのVLOOKUPが必ようですので、その方法についてお伝えしていきたいと思います。

では、よろしくお願いします!

スポンサーリンク

今回のお題:ブログの記事リストに検索流入数と滞在時間をVLOOKUPする

最初に今回の目標について整理をしたいと思います。

記事ごとの検索流入数と平均滞在時間を出力するスプレッドシート

まず、あるスプレッドシートに各記事の過去30日分の検索流入数と平均滞在時間が出力されています。

ランディングページごとの検索流入セッション数レポート

ページごとの平均滞在時間レポート

これはGoogleAnalyticsアドオンで毎日自動で更新されるようにできています。

GoogleAnalyticsアドオンを活用して自動で低品質コンテンツをあぶり出す
ブログの低品質コンテンツを常に自動であぶり出すシステムを作る手始めとして、GoogleAnalyticsアドオンで各記事の検索流入数と滞在時間を自動で出力する方法についてお伝えしていきます。

ブログの記事一覧をリストアップしたスプレッドシート

また、一方で以下のようなブログの記事一覧をリストアップしたスプレッドシートがあります。

記事一覧のスプレッドシート

このリストは過去記事一覧はWP CSV Exporterというプラグインで取得したものをベースとして

新規投稿も自動で追加更新されるWordPress記事一覧スプレッドシートの作り方
WordPress記事一覧をCSVで出力するWP CSV Exporterというプラグインなどを活用して、WordPress記事を更新したら自動で新しい記事も追加される一覧をスプレッドシートに作ります。

IFTTTを使って新規の記事は自動でリストに追加されるようにできています。

Google Apps ScriptでWordPressの更新情報をチャットワークに送る
今回はGoogle Apps ScriptとIFTTTを活用してWordPressの記事公開のお知らせを自動でチャットワークに送るシステム を作りましたので、その方法についてお伝えします。

このブログ記事一覧のH列とI列に、先ほどの検索流入数と滞在時間を引っ張って来たいというわけです。

それができればこの表を見るだけで検索流入が少なくて平均滞在時間が少ない、つまり低品質の記事がどれかというのが一発でわかるようになります。そして、それが常に自動更新されるという優れものです。

他のスプレッドシートに対してVLOOKUPをする

では、早速VLOOKUPをしていきたいと思います。

URLを切り出してキーにする

まず、キーとなる検索値を決める必要があります。

GoogleAnalyticsアドオンの出力では、PagePathの情報しかありませんので、これをキーとして使わざるを得ませんね。

ブログの記事一覧のスプレッドシートにはC列にURLはありますが、PagePathの部分だけの列はありません。

ちょちょっと関数を使って、PagePathを生成しましょう。

各記事のURLの冒頭部分「http://tonari-it.com」をのぞいたものがPagePathになります。

関数を二つ組み合わせます。

文字列の長さを求める関数LEN

LEN(文字列)

そして、文字列の右側を指定した文字数だけ切り出す関数RIGHT

RIGHT(文字列,文字数)

を組み合わせて、例えばブログ記事一覧のC1セルのURLからPagePathをG1セルに出力する場合は

=RIGHT(C1,LEN(C1)-20)

とG1セルに記入します。「http://tonari-it.com」の部分の文字数は20文字ですからね。

RIGHT関数とLEN関数でPagePathを生成

あとはこれをすべての行にコピーすればOKです。これでキーとなる検索値ができました。

ちなみに、これらの関数はエクセルと同じですね。

他のスプレッドシートの範囲を参照する

次に他のスプレッドシートの範囲を指定する方法です。

これはスプレッドシートならではのIMPORTRANGE関数を使います。書き方は

IMPORTRANGE(“スプレッドシートID”,”範囲”)

とします。

スプレッドシートIDは該当のスプレッドシートのURLの

https://docs.google.com/spreadsheets/d/◯◯◯◯◯◯◯◯◯○/edit

この○○の文字列の部分です。

範囲の指定はエクセルと同じで、例えば目的のスプレッドシートの「LPへの検索流入」というシートのA1セルであれば

=IMPORTRANGE(“スプレッドシートID”,”LPへの検索流入!A1″)

とします。

H1セルに入力しますと「#REF!」となってしまいますが、セルにカーソルを当てると

他のスプレッドシートへのアクセスを許可

このように「これらのシートをリンクする必要があります。」というコメントが表示されますので「アクセスを許可」しておきましょう。

アクセスの許可が完了するとH1セルに無事に値が表示されるはずです。

VLOOKUPする際にこのアクセス許可が必要になるので、ここでやっておいてください。

VLOOKUPで検索流入数と滞在時間を引っ張ってくる

これでいよいよ準備ができましたのでIMPORTRANGEの応用編ということでVLOOKUP関数を使って目的の値を引っ張ってきます。

VLOOKUP関数は

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

と書きますので、今回の場合H列に検索流入数、I列に滞在時間を求めるとするとそれぞれ

=VLOOKUP(G1,IMPORTRANGE(“スプレッドシートID”,”LPへの検索流入!A:B”),2,FALSE)

=VLOOKUP(G1,IMPORTRANGE(“スプレッドシートID”,”滞在時間!A:B”),2,FALSE)

とすればOKです。

これを全ての記事にコピーすればバッチリ各記事の検索流入数と平均滞在時間を取得できます。

VLOOKUPで検索流入数と平均滞在時間を求める

まとめ

以上、他のスプレッドシートに対してVLOOKUPをする方法についてお伝えしました。

今回はIMPORTRANGE関数が重要なポイントでしたね。

さて、これにてブログでの低品質コンテンツを自動で洗い出すシステムもほぼ完成なのですが、次回少しだけGoogleAppsScriptを修正して完成としたいと思います。

Google Apps Scriptで数列を記入する方法とR1C1形式について
ブログの低品質コンテンツを自動であぶり出すシステムを作成しています。今回はGoogle Apps Scriptで数式を入力する方法とR1C1形式についてお伝えしつつ、システムを完成させたいと思います。

どうぞお楽しみに!

連載目次:SEO的に低品質なコンテンツなコンテンツを自動であぶり出す

  1. GoogleAnalyticsアドオンを活用して自動で低品質コンテンツをあぶり出す
  2. 新規投稿も自動で追加更新されるWordPress記事一覧スプレッドシートの作り方
  3. スプレッドシートで別ファイルのデータをVLOOKUPで取得する方法
  4. Google Apps Scriptで数列を記入する方法とR1C1形式について