すべての SEO が知っておくべき 10 の Google スプレッドシートの数式

この記事はAhrefs公式ブログの日本語訳です。
原文：10 Google Sheets Formulas Every SEO Should Know
（著者：Joshua Hardwick / 原文の最終更新日：June 20, 2017）
※フルスピード註：この記事は2017年6月20日時点の記載をもとに翻訳しています。Ahrefs公式ブログの記事は今後追記・再公開されることがありますことをご了承ください。

Excel は常に SEO の間で人気のツールですが、今ではさらに強力になる可能性のある新しいツール、

Google Sheetsが登場しています。

ほとんどの人はスプレッドシートにある程度の知識があり (そうでない場合でも、基本的に Excel と同じですが、クラウドベースで完全に無料です)、コラボレーションにおいてスプレッドシートがいかに強力であるかを知っていると思います。

しかし、その機能はコラボレーションをはるかに超えています。

Google スプレッドシートは、ウェブサイトからのデータの収集、半自動 SEO ワークフローの作成、ビッグデータセットの操作 ( Site Explorer のエクスポートなど)、アウトリーチキャンペーンのフォローアップの自動化などに使用できます。

この記事では、Google スプレッドシートの数式を 10 個紹介し、それを日常の SEO タスクにどのように使用できるかを示します。

基本から始めましょう…
1. REGEXTRACT を使用して文字列からデータを抽出する
2. 文字列を複数のデータポイントに分割する
3. VLOOKUPを使用して複数のデータセットを結合する
4. IMPORTXMLを使用してWebサイトからデータをスクレイピングする
5. 特定の値の文字列を検索する
6. IMPORTRANGE を使用して他のスプレッドシートからデータをインポートする
7. SQL クエリを使用した QUERY データセット (これは非常に強力です!)
まとめ

基本から始めましょう…

この短いセクションでは、知っておくべき 3 つの基本的な公式を紹介します。

Google スプレッドシートでどのような SEO 作業を行っているかに関係なく、私は（ほぼ）毎回次の 3 つの数式を使用しています。

もし;
例外。
配列式

IF ステートメントから始めましょう。

これは非常に簡単です。条件が true か false かを確認するために使用されます。

構文： =IF(condition, value_if_true, value_if_false)

以下は、キーワードのリストとそれぞれの推定検索ボリュームを含むスプレッドシートの例です (注:これらはKeyword Explorerを使用して収集されたものです)。

仮に、これらのキーワードのいずれかで 1 位にランク付けできる強力なサイトがあると仮定しましょう。ただし、月に 500 人以上の訪問者をもたらす可能性が高いキーワードのみを追求したいと考えています (ランキングが 1 位であると仮定して)。

この調査によると、米国のランキング 1 位 (デスクトップ検索のみ) の CTR はおよそ 29% です。サイドノート。CTR はクエリやデバイスなどによって大きく異なるため、この方法に頼って検索トラフィックを見積もることはお勧めしません。そのため、

クリックストリームデータを使用してトラフィックを推定します。

そこで、500 人以上の訪問者をもたらす可能性が高いキーワード (つまり、検索ボリュームの 29% が 500 以上であるキーワード) に対して「GOOD」を返し、残りのキーワードに対して「BAD」を返す IF ステートメントを作成しましょう。

式は次のとおりです。

=IF(B2*0.29>=500,"GOOD","BAD")

これが何をするのかは次のとおりです (平易な英語で)。

B2*0.29 (つまり、検索ボリュームの 29%) が 500 以上であるかどうかを確認します。
条件がtrueの場合、「GOOD」を返します。falseの場合は「BAD」を返します。

これは現在のデータセットでは非常にうまく機能しますが、数値以外の値を混合物に投入するとどうなるかを見てください。

それは間違いです。

これは、数値以外の値に 0.29 を掛けることができないために発生します (当然のことですが)。サイドノート。条件付き書式設定を追加したので、IF ステートメントが TRUE と評価される場合、セルは緑色で強調表示されます。ステートメントが FALSE と評価された場合、赤色で強調表示されます。

ここでIFERROR が役に立ちます。

IFERROR を使用すると、数式でエラーが発生した場合のデフォルト値を設定できます。

構文： =IFERROR(original_formula, value_if_error)

これを上記の例に組み込んで (エラーがある場合はセルを空白のままにします)、何が起こるかを見てみましょう。

完璧です – これで公式が完成しました!

少量のデータしか扱ったことがない場合は、そのまま次のセクションに進んでください。

ただし、このガイドが SEO 向けであるという事実を考えると、あなたは定期的にかなり大量のデータを扱っていると仮定します。

この場合、数百、場合によっては数千のセルにわたって数式をドラッグするのに非常に多くの時間を費やしていると推測されます。

「 ARRAYFORMULA 」と入力します。

構文： =ARRAYFORMULA(array_formula)

基本的に、ARRAYFORMULA は元の数式を配列に変換するため、単一の数式を記述するだけで、複数の行にわたって同じ数式を反復できるようになります。

そこで、セル B2 以降の数式をすべて削除し、現在セル B1 にある数式全体を ARRAYFORMULA でラップしましょう。

=ARRAYFORMULA(IFERROR(IF(B2:B29*0.29>=500,"GOOD","BAD"),""))

魔法。

これが基本的な内容です。さらに便利な公式をいくつか見てみましょう。サイドノート。これらの各数式がどのように機能するかを示す

スプレッドシートを次に示します(

注:数式を含むセルは黄色で強調表示されます)。この投稿には、これらのスプレッドシートをさらに含める予定です。

1. REGEXTRACT を使用して文字列からデータを抽出する

REGEXTRACT は、正規表現を使用して文字列またはセルから部分文字列を抽出します。

構文： =REGEXEXTRACT(text, regular_expression)

以下に、これに関する潜在的な使用例をいくつか挙げます。

URL のリストからドメイン名を抽出します (例を参照するには読み続けてください!)。
URL を抽出します (つまり、ルートドメインなし)。
URL が HTTP または HTTPS を使用しているかどうかを確認します。
大量のテキストから電子メールアドレスを抽出します。
URL のリストから、特定の単語が含まれる URL と含まれない URL を特定します (例: 「/category/guest-post」スラッグを含む URL)。

「私たちに書いてください」ページの URL (つまり、ゲスト投稿の機会) のリストからルートドメインを抽出すると仮定します。

列 B には、これを行うための REGEXTRACT 式を記述できます。

必要な正規表現構文は次のとおりです。^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)

サイドノート。正規表現に詳しくない場合は (心配しないでください。私も正規表現が得意ではありません)、選択肢は 2 つあります: (i) 基本を学ぶ — Regexr.comをチェックしてください。 (ii) 何でも問題がある場合は、Google で解決策を見つけてください。必要です — 正直に言うと、ちょっとしたグーグル検索で何が見つかるかは驚くべきことです。

最終的な式は次のとおりです。

=REGEXEXTRACT(A2,"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)")

これをセル B2 に貼り付けると、ドメインが抽出されました。

これを ARRAYFORMULA と IFERROR でラップして列全体を完成させましょう。

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)")),"")

サイドノート。この数式がどのように機能するかを示すスプレッドシートは次のとおりです。

2. 文字列を複数のデータポイントに分割する

SPLIT は、区切り文字を使用して文字列をフラグメントに分割します (つまり、分割します) 。

構文： =SPLIT(text, delimiter)

以下に、これに関する潜在的な使用例をいくつか挙げます。

プロスペクトのフルネームを「名」列と「姓」列に分割します。
URL をHTTP プロトコル、ルートドメイン、URL スラッグの3 つの列に分割します。
カンマ区切りの値のリストを複数の列に分割します。
ルートドメインをドメイン名とドメイン拡張子(.com、.org など)の 2 つの列に分割します。

スプレッドシートにAhrefs のチームメンバー (フルネーム)の素晴らしいリストがあります。

以下は、セル B2 でこれらを名と姓に分割するために使用できる簡単な SPLIT 式です。

=SPLIT(A2," ")

サイドノート。区切り文字としてスペース (つまり ” ”) を使用しています。これは、文字列を分割する場所を SPLIT 式に指示するためです。

もう一度、これを IFERROR と ARRAYFORMULA でラップして、リスト全体を 1 つの数式で分割しましょう。

=IFERROR(ARRAYFORMULA(SPLIT(A2:A," ")),"")

ルートドメインをサイト名とドメイン拡張子に分割する別の数式の例を次に示します。

=SPLIT(A2,".")

サイドノート。これがスプレッドシートです。

3. VLOOKUPを使用して複数のデータセットを結合する

VLOOKUP を使用すると、検索キーを使用して範囲を検索でき、その範囲内の特定のセルから一致する値を返すことができます。

構文： =VLOOKUP(search_key, range, index_key)

以下に、これに関する潜在的な使用例をいくつか挙げます。

複数のソースからのデータの結合 (例: ドメインのリストと、別のシートからの対応する Ahrefs DR 評価の結合)。
値が別のデータセットに存在するかどうかを確認する（たとえば、アウトリーチ見込み客の 2 つ以上のリストにわたる重複を確認する）。
見込み客のリストとともに電子メールアドレスを (連絡先のマスターデータベースから) 取得します。

アウトリーチの見込み客のリスト (つまり、 Site Explorerから取得した、競合他社の Web サイトにリンクしている多数の人々) のリストがあると仮定しましょう。また、別のスプレッドシートに連絡先情報 (電子メールアドレス) のマスターデータベースもあります。

Site Explorer のエクスポート (注: この例では多くのデータが必要ないため、ここでは多くの列を削除しました)。

マスター連絡先データベース — これは、VLOOKUP 関数を使用してクエリを実行するデータベースです。サイドノート。VLOOKUP データ用の 2 つの新しい (空の) 列 (フルネームと電子メールなど) を Site Explorer エクスポートシートに追加しました。これは、次のいくつかのスクリーンショットで示されます。

すでに持っている連絡先情報を探すのに時間を無駄にしたくないので、VLOOKUP を使用してマスターデータベースにクエリを実行し、これらの見込み客の連絡先情報がすでにあるかどうかを確認してみましょう。

ここで使用する式は次のとおりです。

=VLOOKUP(D2:D,'Master contact database'!A:C,2)

OK、電子メール列にも同じことを行いましょう。また、両方の数式を IFERROR と ARRAYFORMULA でラップします。

=IFERROR(ARRAYFORMULA(VLOOKUP(D2:D,'Master contact database'!A:C,3)),"")

サイドノート。これがスプレッドシートです。

4. IMPORTXMLを使用してWebサイトからデータをスクレイピングする

IMPORTXML を使用すると、XML、HTML、RSSなどのさまざまな構造化データタイプから (XPath クエリを使用して) データをインポートできます。

言い換えれば、Google スプレッドシートを離れることなくウェブをスクレイピングできるのです。

構文：=IMPORTXML(url, xpath_query)

以下に、これに関する潜在的な使用例をいくつか挙げます。

URL のリストからメタデータをスクレイピングします (タイトル、説明、h タグなど)。
Web ページから電子メールアドレスをスクレイピングする。
Web ページからソーシャルプロフィール (Facebook など) をスクレイピングする。
RSS フィードから lastBuildDate をスクレイピング (これは、Web サイトをロードすることなく、サイトが最近更新されたかどうかを確認する非常に卑劣な方法です!)

キーワード調査に関する投稿のメタタイトルを取得したいと仮定します。

HTML では、メタタイトルが「2017 年にキーワード調査を行う方法 – Ahrefs のガイド」となっていることがわかります。

メタタイトルを取得するために使用する XPath クエリは非常に単純です:「//title」

式は次のとおりです。

=IMPORTXML("https://ahrefs.com/blog/keyword-research/","//title")

数式でセル参照を使用することもできます。これにより、多数の URL のデータのスクレイピングが非常に簡単になります。

サイドノート。残念ながら、IMPORTXML は ARRAYFORMULA では機能しないため、これを手動で下にドラッグすることになります。

IMPORTXML は、基本的なメタタグのスクレイピングに限定されません。事実上あらゆるものをスクレイピングするために使用できます。XPath を知っているだけの話です。

以下に、役立つ可能性のある XPath 式をいくつか示します。

ページ上のすべてのリンクを抽出します: "//@href" ;
ページ上のすべての内部リンクを抽出します: "//a[contains(@href, 'domain.com')]/@href" ;
ページ上のすべての外部リンクを抽出します: "//a[not(contains(@href, 'domain.com'))]/@href" ;
メタディスクリプションを抽出: "//meta[@name='description']/@content" ;
H1 を抽出: "//h1" ;
ページから電子メールアドレスを抽出します: "//a[contains(@href, 'mailTo:') or contains(@href, 'mailto:')]/@href" ;
ソーシャルプロファイル (LinkedIn、Facebook、Twitter など) を抽出します "//a[contains(@href, 'linkedin.com/in') or contains(@href, 'twitter.com/') or contains(@href, 'facebook.com/')]/@href"。
lastBuildDate を抽出します (RSS フィードから): "//lastBuildDate"

次の操作を行うことで、任意の要素の XPath を見つけることができます (Chrome の場合)。

右クリック > 検査 > 右クリック > コピー > XPath のコピー

サイドノート。

これがスプレッドシートです(この中にはたくさんの例が含まれています :D)

5. 特定の値の文字列を検索する

SEARCH では、文字列に値が存在するかどうかを確認できます。次に、文字列内で値が最初に見つかった位置を返します。

構文：=SEARCH(search_query, text_to_search)

以下にいくつかの使用例を示します。

特定のサブドメインが URL に存在するかどうかを確認します (これは、URL のリストを一括で分類する場合に便利です)。
キーワードをさまざまな意図ベースのカテゴリ (ブランド、コマーシャルなど) に分類します。
URL 内の特定の望ましくない文字を検索します。
URL 内の特定の単語/フレーズを検索して、リンク候補を分類します (例: 「/category/guest-post」、「resources.html」など)。

SEARCH の実際の例を見てみましょう。

以下は、Ahrefs.com の上位 300 ページ以上のリストです (注:このデータの収集にはSite Explorerを使用しました)。

サイドノート。上のスクリーンショットでは、いくつかの列を削除してデータをクリーンアップしました。Site Explorer では、実際にはこれよりもはるかに多くの情報が得られます (例: 各 URL のトップキーワード、トラフィック量、検索量、位置など)。

URL に /blog/ が含まれるページはすべてブログ投稿です。コンテンツ監査中に、これらの各ページに「ブログ投稿」というタグを付けたいとします。

SEARCH (IF ステートメントと組み合わせたもの – これについてはガイドの前半で説明しました) を使用すると、これを数秒で実行できます。式は次のとおりです。

=IF(SEARCH("/blog/",A2),"YES","")

これを IFERROR と ARRAYFORMULA でラップして整理しましょう。

他にもいくつかの便利な式を次に示します。

URL のリストから「write for us」ページを見つけます =IF(SEARCH("/write-for-us/",A2),"Write for us page","")。
URL のリストでリソースページを検索します =IF(SEARCH("/resources.html",A2),"Resource page","")。
ブランド検索語を検索します (キーワードのリスト内): =IF(SEARCH("brand_name",A2),"Branded keyword","") ;
内部リンク/外部リンクを (送信リンクのリストから) 識別します=IF(SEARCH("yourdomain.com",A2),"Internal Link","External Link")。

サイドノート。これがスプレッドシートです(このスプレッドシートにもいくつかの例が含まれています)。

6. IMPORTRANGE を使用して他のスプレッドシートからデータをインポートする

IMPORTRANGE を使用すると、他の Google スプレッドシートからデータをインポートできます。

Google ドライブ上にある必要もありません。他の人のものである可能性があります (注: この場合、シートにアクセスする許可が必要になります!)

構文：=IMPORTRANGE(spreadsheet_ID, range_to_import)

以下にいくつかの使用例を示します。

「マスター」スプレッドシートを利用してクライアント向けシートを作成します。
複数の Google スプレッドシートにわたってデータを検索および相互参照します (つまり、IMPORTRANGE と VLOOKUP を組み合わせて使用します)。
データ検証で使用するために別のシートからデータを取得します。
VLOOKUP を使用して「マスター」スプレッドシートから連絡先データを取得します

IMPORTRANGE の動作例を見てみましょう。

これは、仮想の SEO クライアントとその予算のリストを記載したシートです。

このクライアントリストを別の Google シートで使用したいとします。次の式を使用して、このデータ範囲全体をインポートできます。

=IMPORTRANGE("SPREADSHEET_KEY","'SheetName'!A2:A")

サイドノート。ここでスプレッドシートのキーを見つけます。

また、これらのクライアント用に構築されたリンクをマスタースプレッドシートに記録していると仮定します。1 つの列にはリンク URL があり、もう 1 つの列にはリンクがどのクライアントに対するものであったかを記録します。

次のように IMPORTRANGE を使用して、データ検証を使用してすべてのクライアントのドロップダウンを作成できます。

このドロップダウンは、マスタースプレッドシートにクライアントを追加または削除するたびに自動的に更新されます。サイドノート。

これがメインのスプレッドシート(およびデータスプレッドシート) です。

7. SQL クエリを使用した QUERY データセット (これは非常に強力です!)

QUERY は VLOOKUP を強化したようなものです。SQL を使用してデータをクエリできるため、データのクエリ/取得を非常に詳細に行うことができます。

構文：=QUERY(range, sql_query)

以下にいくつかの使用例を示します。

特定の見込み客についてマスターリンク見込み客データベースにクエリを実行します（たとえば、ゲスト投稿機会としてタグ付けされ、DR が 50 を超え、連絡先の詳細が存在する見込み客のみを検索します）。
「マスター」スプレッドシートからデータを取り込む、非常に詳細なクライアント向けドキュメントを作成します。
大規模なオンサイト監査をクエリして、注意が必要なページのみを抽出します。

タグ付けされた「ブログ投稿」のシートに戻りましょう。