SEO担当者が絶対に知っておくべきスプレッドシート関数7選

こんにちは!LANYでSEOコンサルタントをしていますきら(@kira__i08)です。

いきなりですが、僕はスプレッドシートが大好きです。好きすぎて仕事だけでなく、私生活で体重を管理したり、時間を管理したりするときにもスプレッドシートを活用するくらい。

この記事ではそんなスプレッドシート大好き人間が、SEO担当者が絶対に知っておくべきスプレッドシートの関数と業務での活用方法を紹介します。

「スプレッドシートやエクセルは苦手…」

「もっと効率化できると思うけど具体的な方法がわからない…」

「仕事が多すぎて時間がない…」

上記のような悩みを抱えたSEO担当の方はぜひご一読くださいませ。

目次

スプレッドシートの関数を使いこなせれば他の業務の時間を確保できる

SEOの業務ではスプレッドシートやエクセルを使う機会が多いです。

触れている時間が長いので、関数を活用して工数を削減できれば、業務効率の大幅な改善につながります。

関数を使いこなせれば他の業務の時間を確保できる

キーワードや記事の管理、進捗管理や分析など、SEO業務をスプシやExcelで行っている人がほとんどです。また関数を使えば、ミスなく作業できるメリットもあります。

SEO担当者のミッションは「メディアやサイトの検索流入数を最大化すること」だと思います。

スプレッドシート関数を使い業務を効率化できれば、優先度が高く成果に直結する仕事に多くの時間を充てられるのです。

SEO担当者にはやるべきことがたくさんあります。

  • SEO戦略の策定や見直し
  • コンテンツ制作の仕組み構築
  • 流入数やCVの分析
  • 効果測定
  • 施策の立案etc

これらKPIに直結する業務により多くの時間を投入するために、スプレッドシートの関数を活用するのです。

手動だと2時間かかる作業も関数を使えば10分で終わることもあります。

スプレッドシートの関数を使いこなすことで、ミスなく作業の時間を圧縮し、優先度の高い仕事により多くの時間を投入できるようになるのです。

※無料お役立ち資料:「【完全版】SEO対策の教科書の動画投影スライド」をダウンロードする>>こちらから

絶対に覚えておきたい7つのスプレッドシート関数

ここからは実際にSEOの業務に活かせるスプレッドシートの関数と業務での実際の活用方法を紹介します。

ここで紹介する7つの関数は、今あなたが担当している業務にすぐに活かせると思うので、一つ一つ暗記するくらいの熱量で読んでください!

1.VLOOKUP関数

まずはみんな大好きVLOOKUP関数です。

スプシやエクセルに慣れている人は、当然のように使っている関数であるため紹介しようか迷いましたが、念のため記載しました。

僕も本当によく使っています。それほど使用頻度が高いので、知らない人は絶対にここで覚えてください。

VLOOKUP関数は「表データを縦方向に検索し、指定した条件に合う行の指定した範囲のデータを返す関数」です。

わかりやすく伝えると、大量のデータの中から指定した条件にあった値を抽出してくれる関数です。

  • 戻り値:指定した条件に一致する値
  • 構文:= VLOOKUP(検索値, 範囲, 列番号, 検索の型)
  • 検索値:検索する値を入力。(検索する対象)
  • 範囲:検索値が含まれるセルの範囲を指定
  • 列番号:指定した範囲から取り出したいデータの列番号を指定
  • 検索の型:完全一致(0/FALSE)、近似一致(1/TRUE)のどちらかを指定(多くの場合完全一致を指定する)

活用方法:キーワード管理やコンテンツ管理に活用できる

担当しているメディアやサイトの規模感によって異なりますが、管理しているキーワードが1,000〜10,000個、もしくはそれ以上になることもあります。

この中から目視で必要な情報を探すには時間がかかってしまうでしょう。そんな時に活躍するのがVLOOKUP関数です。

たとえば、下記のようにキーワードマスタから、特定のキーワードの検索ボリュームや制作優先度など、紐づくデータを抽出したいときはVLOOKUP関数の出番。

VLOOKUP関数

コンテンツ管理についても、同様に活用できます。

記事のタイトルやURL、カテゴリー、公開日など、コンテンツのメタデータをマスタにまとめていれば、VLOOKUP関数を使って検索し取得できます。

目視で検索して取得すると時間がかかるし、ミスしてしまう可能性もあります。VLOOKUP大量のデータから検索し、必要なデータを抽出したいときに使いたい関数です。

2.UNIQUE関数

UNIQUE関数は「指定した範囲から(複数の値の中から)重複を除いて、固有の値だけを抽出できる関数」です。

大量のキーワードやコンテンツデータの中から重複しない固有のデータを抽出するときに役立ちます。

  • 戻り値:重複しない固有の値(配列)
  • 構文:= UNIQUE(範囲,[by_column],[exactly_once])
  • 範囲:重複を取り除きたいデータの範囲を指定。
  • [by_column]:列か行のどちらの重複を削除したいか指定。
  • 列:false、行:true(省略しても問題なく、デフォルトはfalse)
  • [exactly_once]:trueを入力すると重複していない値のみを抽出する。(デフォルトはfalse)

※[by_column]、[exactly_once]はあまり使いません。重複を削除したい場合は範囲のみ指定すればOKです。

活用方法:キーワードデータの整形に活用できる

下記のようにキーワードの重複を取り除くときに活用できます。

キーワードデータの整形に活用

サイトやメディア立ち上げ時のキーワード選定やキーワードの追加時に重複を目視で確認し、削除するのはミスが出たり、時間がかかったります。

そこでUNIQUE関数を活用すれば、ミスなく一瞬で重複を削除できます

50個や100個のキーワードデータならまだ目視でも可能かもしれませんが、1,000個や2,000個のデータを扱う場合はUNIQUE関数を活用しましょう。

キーワードデータの整形以外には、コンテンツ管理や進捗管理にも活かせます。重複を削除したいときはUNIQUE関数がおすすめ。

3.ARRAYFORMULA関数

ARRAYFORMULA関数は「配列数式から返された値を複数行、複数列に表示できる関数」です。(ARRAYFORMULAは「ARRAY=配列」と「FORMULA=数式」を組み合わせた造語)

もう少しわかりやすく伝えるなら、数式や関数を1つのセルに入力するだけで、指定した範囲すべてにその結果を反映してくれる関数です。

  • 戻り値:数式や関数の値を配列で返す
  • 構文:= ARRAYFORMULA(配列数式)
  • 配列数式:1つのセルを対象とする数式ではなく、複数のセル(範囲)を指定した数式
      例)= ARRAYFORMULA(B1:B30+C1:C30)

例えば、国語と数学の点数の合計を求める場合、下記のD列にそれぞれ数式を入力する必要があります。

数式を入力

しかし、ARRAYFORMULA関数を使えば、セルD2に入力するだけでD列に合計が出力されるので、各行に数式を入力する手間が省けます。

ARRAYFORMULA関数

活用方法:大量のデータを扱うときに活用できる

SEOの業務では大量のデータを扱うことが多いです。

例えば、下記のようにコンテンツ管理シートを作ったときに、検索ボリュームを手入力、もしくはそれぞれの行に数式を入力するのは面倒です。

大量のデータを扱うときに活用

そこでキャプチャのようにセルD2にARRAYFORMULA関数を入力すれば、それぞれのキーワードの検索ボリュームを自動で出力してくれます。

4.COUNTIF関数(COUNTIFS関数)

COUNTIF関数は「指定した条件に当てはまるセルの数をカウントする関数」です。

文字通り「COUNT関数」と「IF関数」を組み合わせた関数。主に分析業務で力を発揮する関数です。

  • 戻り値:条件に当てはまるセルの数
  • 構文:
    = COUNTIF(範囲, 検索条件)
    = COUNTIFS(範囲1, 検索条件1, 範囲2, 検索条件2, …)
  • 範囲:カウントしたいセルが含まれている範囲
  • 検索条件:カウントしたい条件を数値や文字列で指定する

活用方法:主に分析業務で活用できる

SEOの業務では下記のような分析をするときに役立ちます。

  • 検索ボリュームが1,000以上のKWの数を求める
  • MAUが1,000以上の記事の数を求める
  • 月間のCVが10以上のLPの数を求める
  • テゴリごとの記事数を求める

たとえば下記は、キーワードの検索ボリュームごとの数を調べるためにCOUNTIF関数を使っています。

COUNTIF関数

この例ではキーワードマスタの数が少ないので、目視で数えてもそれほど時間がかかりません。

しかし、キーワードが1,000個ある場合、目視で数えるのは難しいので、COUNTIF関数やCOUNTIFS関数を積極的に使って時間短縮を図ることがおすすめです。

5.SUMIF関数(SUMIFS関数)

SUMIF関数は「指定した条件に当てはまるセルの値を検索し、合致したデータを合計する関数」です。

文字通り「SUM関数」と「IF関数」を組み合わせた関数。COUNTIF同様、主に分析業務で力を発揮する関数です。

  • 戻り値:指定された検索条件に一致するセルの値の合計
  • 構文:
    = SUMIF (範囲,検索条件,合計範囲)
    = SUMIFS (合計範囲,範囲1,検索条件1,範囲2,検索条件2, …)
  • 範囲:検索対象とするセルが含まれている範囲を指定する。
  • 検索条件:検索するための条件を数値や文字列で指定。
  • 合計範囲:合計したい値が入力されているセル範囲を指定する。

活用方法:主に分析業務で活用できる

COUNTIFと似ていますが、SEO業務では下記の分析をするときに役立ちます。

  • キーワードのカテゴリごとの検索ボリュームの合計を求める
  • 記事のカテゴリごとの流入数の合計を求める
  • 記事のカテゴリごとのCVの合計を求める

たとえば下記は、カテゴリごとの合計検索ボリュームを調べるためにSUMIF関数を使っています。

SUMIF関数

COUNTIF同様、上記の例ではキーワードの数が少ないですが、多くなればSUMIF関数を活用することで圧倒的な時間短縮につながります。分析業務には欠かせない関数の1つです。

6.SUBSTITUTE関数

SUBSTITUTE(サブスティチュート)関数は「セル内の文字列を別の文字列に置換できる関数」です。

スプレッドシートの検索と置換機能でもこれは実現できますが、元データを残したい場合はSUBSTITUTE関数が便利でしょう。

  • 戻り値:置換したあとの文字列
  • 構文:= SUBSTITUTE (検索対象のセル,検索文字列,置換文字列,[置換対象])
  • 検索対象のセル:検索と置換を行うセルを指定する。
  • 検索文字列:検索したい文字列を指定する。
  • 置換文字列:置き換えたい文字列を指定する。
  • [置換対象]:検索対象のセルのなかに検索文字列が複数ある場合、何番目の文字列を置換するか数字で指定できる(省略可能)

※[置換対象]は指定しないことがほとんどです。基本的には省略してOK。

活用方法:URLやタイトルなど規則性がある文字列を生成するときに活用できる

SEOの業務では、URLや記事タイトルの変更や生成に活用できます。

下記のキャプチャは、URL(ディレクトリ名)を変更するときにSUBSTITUTE関数を使っています。(/blogを/articleに変更)

SUBSTITUTE関数

また、GoogleアナリティクスのAPI(アドオン)を使ってスプレッドシートで数値を管理している方はこの関数を活かせます。

ページ別のUUやPVを取得すると、ドメイン部分がない下記のように出力されます。

ドメイン部分

下記のようにSUBSTITUTE関数を使ってURLを置換しドメイン部分を取り除き、置換した文字列をVLOOKUP関数の引数にして検索すれば、ページ別の数値を引っ張ってこれるのです。

VLOOKUP関数の引数にして検索

7.IMPORTXML関数

IMPORTXML関数は「Web上の情報をスクレイピングする関数」です。

スクレイピングとは「Web上に公開されているデータや情報を取得すること」です。IMPORTXML関数は、MicrosoftのExcelにはないスプレッドシートのオリジナルな関数です。

たとえば競合サイトの情報を取得して分析したいときなどに便利。1つ2つの情報ならコピペで取得すれば良いですが、100、200の情報を取得する場合は時間がかかります。この時間を短縮してくれるのがIMPORTXML関数なのです。

  • 戻り値:指定した条件の値
  • 構文:= IMPORTXML(URL, XPath クエリ)
  • URL:値を取得したいページのURL
  • XPath クエリ:取得したい情報が記述されている部分を指定する。
  • ※XPathとは、マークアップ言語 XML に準拠した文書の特定の部分を指定する言語構文である(出典元:XML Path Language – Wikipedia
  • ※簡単に伝えると、HTMLの要素をタグや属性を使って表す方法です

※XPathについての詳細は「XPath Tutorial|W3Schools Online Web Tutorials」(英語)をご覧ください

活用方法:タイトルやH1などの情報の取得に役立つ

下記はIMPORTXML関数を使ってLANYブログのタイトルを取得しています。

タイトルやH1などの情報の取得

一度にたくさんの情報を取得しようとすると時間がかかる場合もありますが、自動で取得できるため手動よりははるかに効率的です。

LANYのSEOコンサルティングの内容や事例をより詳しく知りたい方へ

SEOおたく

デジタルマーケティング全体で成果に向き合ってくれる
信頼出来るパートナー」など、
おかげさまでご好評いただいております!
クライアント様の改善実績をぜひご覧ください!

>>実績はこちら

関数を覚えれば本質的な仕事にかける時間を捻出できる

さいごにもう一度ここで紹介したスプレッドシート関数を掲載します。

  1. VLOOKUP関数:キーワード管理やコンテンツ管理に活用できる
  2. UNIQUE関数:キーワードデータの整形に活用できる
  3. ARRAYFORMULA関数:大量のデータを扱うときに活用できる
  4. COUNTIF関数(COUNTIFS関数):主に分析業務で活用できる
  5. SUMIF関数(SUMIFS関数):主に分析業務で活用できる
  6. SUBSTITUTE関数:URLやタイトルなど規則性がある文字列を生成するときに活用できる
  7. IMPORTXML関数:タイトルやH1などの情報の取得に役立つ

これらはすべてスプレッドシートが大好きな僕がSEOコンサルティングをしたり、運営サポートをしたりするときに使っている頻度が高い関数です。

7つすべてを一度に覚えるのは難しいので、1つ1つ実際に使いながら覚えてもらえると嬉しいです。上手に活用できれば必ず効率化できます。

そして、スプレッドシートやエクセルにかける時間を減らし、よりKGIやKPIにインパクトが大きい業務の時間を増やし、成果を最大化して欲しいと思います。

SEOコンサルティングとして活動する傍ら、ブログやフリーランス、SEOについてのブログも運営しています。よかったら除いてみてください。

ebook

【無料お役立ち資料】
SEO対策の教科書の動画投影スライド

SEO対策の教科書 YouTube動画の投影スライド お役立ち資料アイキャッチ
  • スライド154枚
  • 1.5時間の無料動画

\SEO担当者のあなたへ/

無料のSEOメールマガジン

週に2回SEOのお役立ち情報をお届けする購読者数2,400人超えの人気メルマガです

  • SEO最新情報をキャッチアップできる
  • SEO成功事例や限定Tipsが届く
  • 限定ウェビナーの情報がいち早く届く

SEO初心者・企業のSEO担当者の方まで役立つ内容のためぜひご登録ください!

* は入力必須項目です。

この記事の執筆者

LANYブログ編集部のアバター

LANYブログは「SEOを中心とするデジタルマーケティングの情報を発信するブログ」です。一次情報に溢れた独自性のあるコンテンツと読者の方が何かしらのアクションが起こせる情報を執筆しています。

この記事を監修した人

LANYブログ編集部のアバター
竹内渓太 SEOコンサルタント

株式会社LANYの代表。株式会社リクルートホールディングスにデジタルマーケティング職で新卒入社。3年間デジタルマーケティングに従事。その後、株式会社LANYを創業。大規模サイトのSEOが得意。

目次