みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルを使っていらっしゃる方であれば多くの方が活用されているであろう関数VLOOKUP。
特定のシートや範囲から特定の値で検索をする超便利な関数です。
Googleスプレッドシートでもエクセルと全く同じようVLOOKUP関数を使うことができるのですが、VLOOKUP関数で別のスプレッドシートを検索することができるのはご存知でしたか？
引き続きブログの低品質コンテンツを自動であぶり出すシステムを作成するために、この他のスプレッドシートのデータに対してのVLOOKUPが必ようですので、その方法についてお伝えしていきたいと思います。
では、よろしくお願いします！
今回のお題：ブログの記事リストに検索流入数と滞在時間をVLOOKUPする
最初に今回の目標について整理をしたいと思います。
記事ごとの検索流入数と平均滞在時間を出力するスプレッドシート
まず、あるスプレッドシートに各記事の過去30日分の検索流入数と平均滞在時間が出力されています。
これはGoogleAnalyticsアドオンで毎日自動で更新されるようにできています。
ブログの記事一覧をリストアップしたスプレッドシート
また、一方で以下のようなブログの記事一覧をリストアップしたスプレッドシートがあります。
このリストは過去記事一覧はWP CSV Exporterというプラグインで取得したものをベースとして
IFTTTを使って新規の記事は自動でリストに追加されるようにできています。
このブログ記事一覧のＨ列とＩ列に、先ほどの検索流入数と滞在時間を引っ張って来たいというわけです。
それができればこの表を見るだけで検索流入が少なくて平均滞在時間が少ない、つまり低品質の記事がどれかというのが一発でわかるようになります。そして、それが常に自動更新されるという優れものです。
他のスプレッドシートに対して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文字ですからね。
あとはこれをすべての行にコピーすれば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をする方法についてお伝えしました。
今回はIMPORTRANGE関数が重要なポイントでしたね。
さて、これにてブログでの低品質コンテンツを自動で洗い出すシステムもほぼ完成なのですが、次回少しだけGoogleAppsScriptを修正して完成としたいと思います。
どうぞお楽しみに！
連載目次：SEO的に低品質なコンテンツなコンテンツを自動であぶり出す
- GoogleAnalyticsアドオンを活用して自動で低品質コンテンツをあぶり出す
- 新規投稿も自動で追加更新されるWordPress記事一覧スプレッドシートの作り方
- スプレッドシートで別ファイルのデータをVLOOKUPで取得する方法
- Google Apps Scriptで数列を記入する方法とR1C1形式について
