すべてのSEOが使用するのが難しい7つの高度なExcelハック
Excelは多くのマーケターにとっての生き方です。
調査データの分析に使用する場合でも、売上データの計算に使用する場合でも、ある時点で、Excelドキュメントを開くことになります。
Excelやその他の表計算ソフトウェアについては、関数がたくさんあるということです。
これは、セットアップしようとしたときにスタックする方法がたくさんあることを意味します。
毎日Excelを使用する必要がある場合、すべてを手動で行う必要はありません。
それは膨大な時間の浪費です。
ほとんどのマーケティングプロはこれらの機能を十分に備えていますが、彼らが言うように、それは単なる学習プロセスです。
降りたら、行ってもいいでしょう。
SEOのためにExcelを学ぶべき理由
ある種のスプレッドシートソフトウェアをまだ使用していない場合は、使用する必要があります。
マーケティング戦略の開発で最も重要な部分の1つは、マーケティングプラン、毎週のSEO追跡、または年次報告書のいずれであっても、メトリックのレポートです。
販売およびマーケティング計画テンプレート0
スプレッドシートは、すべてのデータを1つのアクセスしやすいスペースに整理するのに役立ちます。
特にSEOのようなものになると、整理することが重要です。特に、数百または数千のキーワードがある場合。
ExcelがSEOにとって非常に優れたツールである理由は、Excelには、物事を可能な限り簡単にするように設計されたアドオンとプラグインがあるためです。
ニール・パテルとの相談
私の代理店があなたのウェブサイトへの大量のトラフィックをどのように促進できるかをご覧ください
SEO-大量のSEOトラフィックのロックを解除します。実際の結果をご覧ください。
コンテンツマーケティング-私たちのチームは、共有され、リンクを取得し、トラフィックを引き付ける壮大なコンテンツを作成します。
有料メディア-明確なROIを備えた効果的な有料戦略。
電話を予約する
たとえば、SEOTools for Excelには、マーケター向けに設計された既成の関数が付属しています。
SEOtools
このようなツールは、Excelを使用する必要があるが、Excelが気に入らない場合に必要になることがあります。
あなたはあなたが誰であるかを知っています。
ただし、特殊なツールの他に、Excelには、キーワードのキュレーション、リストのセグメント化、およびデータの分析に役立つ多くの数式があります。
これらはすべてSEOの必需品です。
しかし、多くの人は、これらの数式の使用方法を理解していないため、Excelを敬遠しています。
という事は承知しています。特定の数式の使い方を学んだり覚えたりしなければならないのは、残念なことのように感じるかもしれません。
しかし、そうである必要はありません。
ここで、Excelはそのような大きな謎を表現する必要はないことをお伝えします。
実際、それがどのように機能するかを知ったら、実際にそれを使用することを楽しむかもしれません。
したがって、これ以上面倒なことはせずに、Excelの組み込み数式を使用してSEOゲームを実際に強化する7つの方法を次に示します。
1.「IF」式を使用してキーワードカテゴリを作成します
キーワード調査を行うときに学ぶことの1つは、長いリストを採用することです。
Googleのキーワードプランナーのような無料のツールを使用している場合でも、大きなリストを取得することになります。
SEOkeywordplanner
AhrefsやMozのような他のツールは、何千ものキーワードアイデア、ボリュームメトリック、および競争数のリストを提供する場合があります。
それはたくさんのデータです。
それをスプレッドシートに入れると、その情報を行と列に分類するのに役立ちます。しかし、それを理解する方法も必要です。
これらのキーワードを使用可能な情報にセグメント化する方法が必要です。
たとえば、Keyword Plannerで複数のシードキーワードを使用する場合は、次のようになります。
キーワードプランナー01
シードキーワードをキーワードのアイデアから分離する提案のリストをエクスポートすると、「広告グループ」列が表示されます。
キーワードプランナー2
このリストを、「ツール」という単語が含まれているキーワードだけに分解したいとします。
手動で見ると、700以上の行を並べ替える必要があります。やりたくないです。
代わりに、Excelの数式=IFを使用して並べ替えます。
最初に行うのは、すべての広告グループを検索するため、[広告グループ]列を削除することです。
次に、検索ボリュームの横に「カテゴリ」列を追加します。
スクリーンショット21418 4 00 PM
最初に使用可能なセルに「ツール」という単語を入れて、次の数式を使用します。
= if（isnumber（search（ "tools、A12））、" Tools "）
次に、最初の列（私のキーワードリスト）で「ツール」キーワードが見つかると、「ツール」カテゴリに分類されます。
キーワードプランナー20180214 at 13 36 47 csv
スプレッドシート全体でこの数式を使用して、すべてのキーワードを特定のカテゴリに分類できます。
これはSEO組織にとって画期的なものです。
たとえば、「ローカル」という単語を含むキーワードのみが必要な場合は、「ローカル」カテゴリを作成できます。
これは、高速検索のためにすべてのキーワードのアイデアを整理するための非常に便利なハックです。
そうすれば、同じExcelファイルにアクセスする必要があるが、理由が異なる人々のチームと協力している場合、データが混乱することはありませんが、人々は必要なものを見つけることができます。
Mozには、ここにあるすべてのスプレッドシートデータに対してこれを実行する方法の良い例があります。
すべての行と列に対してこれを行うには、マルチセルARRAY数式を使用できます。
dc52ac8b 0860 4ba1 b416 30c84dd61518
これにより、すべてのsiに同じ複雑な数式を挿入する必要がなくなります。
スプレッドシートのセルをngleします。
700以上のセルを使用する必要がない場合でも、便利な数式です。
2.データの外れ値を見つけるためのピボットテーブルを作成します
組織がない場合、スプレッドシートのデータの分析は混乱する可能性があります。
カテゴリでフィルタリングできる場合でも、リストを一瞥するだけでは外れ値や「不良データ」を見つけることができない場合があります。
良いニュースは、Excelには、ピボットテーブルを使用してデータの正と負の傾向を見つける簡単な方法があることです。
セルを作成するには、スプレッドシートでセルを強調表示して、[挿入]>[ピボットテーブル]をクリックします。
ピボットテーブルを挿入します
次に、分析するデータとピボットテーブルを配置する場所を選択するように求めるダイアログボックスが表示されます。
ピボットテーブルを作成する
テーブルはハイライトしたセルに自動的に配置されますが、新しいテーブルを作成したり、このボックスから別の場所に配置したりすることもできます。
[OK]をクリックすると、列に対応するフィールドボックスが表示されます。
ピボットテーブルフィールド
フィールドは、必要な場所にドラッグアンドドロップしたり、フィルタリングしたり、列を追加したりできます。
完全にカスタマイズ可能で、必要に応じて後で追加または削除できます。
これらのテーブルは、リンクやURLの整理、DA / PAによるインバウンドリンクのグループ化、ドメインごとのキーワード検索、特定の列のドロップダウンの作成などに使用できます。
機能的なピボットテーブルがどのように見えるかの例を次に示します。
条件付き書式
データをより読みやすい方法で整理するだけでなく、データを分類するためのいくつかのオプションを提供します。
上記の例では、特定のキーワードのアクションあたりのコスト（CPA）の高低をすぐに見つけることができます。
これは、ピボットテーブル内の条件付き書式で行われます（色分けが含まれていることがわかります）。
このようなピボットテーブルは、キーワードを使用してAdWords戦略を通知する場合や、特定のキーワードの検索ボリュームやクリック単価（CPC）の数が多いか少ないかをすばやく特定する場合に役立ちます。
外れ値を見つけるために、何百ものキーワードのリストを上下にスクロールする必要はありません。
そして、それもかなり良さそうです。
3.「SUBSTITUTE」を使用してボリューム番号を変換します
Excelでデータを並べ替える方法はたくさんあります。
ただし、適切なオプションを見つけることが生産性の鍵となります。
キーワードプランナーなどのキーワードツールからデータをダウンロードする場合、CSVファイルが常にきれいに見えるとは限りません。
たとえば、私のキーワードボリュームは次のようになります。
スクリーンショット21418 4 53 PM
公平を期すために、私がキーワードプランナーを使用しているときもこのように見えます。
キーワードプランナーグーグルアドワーズ
しかし、これらのボリュームを検索したり、値の高いものから低いものへと整理したりしようとすると、形式に少し問題が生じます。
Excelは、「10K」や「1M」などの用語に基づいてリストを並べ替えることができません。 MとKはそれを捨てます。
実数が必要です。
ありがたいことに、Excelには、ボリュームを適切に並べ替えるのに役立つやや簡単な数式があります。
まず、KとMを置き換えて、「000」または「000000」に変換します。
「LowtoHigh」などの新しい列を作成します。
スクリーンショット21418 5 05 PM
新しい列のセルを選択し、=SUBSTITUTEを挿入します。
テキストとダイナミックメニュー
数式は次のようになります。
= SUBSTITUTE（C2、” K”、” 000”）
セル番号は、変換する行に応じて変わります。
数式を挿入すると、次のようになります。
スクリーンショット21418 5 07 PM
そして最終結果：
スクリーンショット21418 5 07 PM2
同じ式を使用して、Mを「000000」に置き換えることもできます。次のようになります。
= SUBSTITUTE（C2、” M”、” 000000”）
これはすべてのKとMの世話をする必要があります。
次の数式（セル番号を置き換える）を使用して、両方を同時に実行することもできます（たとえば、100K〜1Mの範囲がある場合）。
= SUBSTITUTE（SUBSTITUTE（C2、” K”、” 000”）、“ M”、“ 000000”）
新しい列の最小、最大、および平均の検索ボリュームを見つけるために使用できるいくつかの式もあります。
最小の式は次のとおりです。
最小検索ボリューム
そして最大：
maximumsearchvolume
そして平均：
averagesearchvolume
これらの数式を使用するには、列を追加する必要がある場合があるため、適切にラベルを付けるようにしてください。
手動で多くの並べ替えや再配置を行うことなく、高から低、低から高の列をすばやく識別できるはずです。
結局のところ、これらの式はあなたの時間とエネルギーを節約することになっています。
4.「REGEXTRACT」を使用して特定のデータを抽出します
もちろん、SEOスプレッドシートに含まれるデータはキーワードだけではありません。
URL、ドメイン名、ブログのタイトル、または電子メールアドレスを並べ替える必要がある場合があります。
たとえば、HTTPとHTTPSを使用する特定のブログ投稿またはランディングページを検索する必要がある場合があります。
image19
いつでもExcelの検索バーを使用して個別に検索できますが、URLのリストが長い場合は、時間がかかる可能性があります。
代わりに、REGEXTRACTを使用して、リストから特定のデータをプルします。
ただし、REGEXTRACTは他のExcel数式とは異なります。特別な構文（文字列）が必要です
コードの）動作します。
詳細については、こちらをご覧ください。
REGEXTRACTの構文を含む完全な式は、次のようになります。
= REGEXEXTRACT（A2、” ^（？：https？：\ / \ /）？（？：[^ @ \ n] + @）？（?: www \。）？（[^：\ / \ n] + ）」）
最後の面白い部分は構文です。
数式全体をコピーして新しい列に貼り付ける（セル番号を変更する）と、結果は次のようになります。
image34
ARRAY数式を使用して、これを一度に複数の行に追加できます。
全体は次のようになります。
= IFERROR（ARRAYFORMULA（REGEXEXTRACT（A2：A、” ^（？：https？：\ / \ /）？（？：[^ @ \ n] + @）？（?: www \。）？（[^： \ / \ n] +）”））、“”）
面倒ですが、機能します。
この例を使用すると、次のようになります。
フォーミュラ2の再抽出
最終結果は次のとおりです。
regextract forumla3
これは最初の設定に少し時間がかかりますが、特に大量のデータを並べ替える場合は、長期的には数え切れないほどの時間を節約できます。
プロセスについて、またはREGEXTRACTで構文を使用することについてまだ混乱している場合は、Ahrefsにこの数式がどのように機能するかを示すスプレッドシートがあります。
これは、Excelの数式を使用するのが必ずしも好きではない人にとっては命の恩人です。
5.「PROPER」式を使用してタイトルタグをフォーマットします
あなたがあなたのSEOスプレッドシートでしなければならないかもしれないもう一つの退屈な仕事はあなたのデータを再フォーマットすることです。
たとえば、タイトルタグやキーワードは小文字でインポートされたかもしれませんが、大文字にする必要があります。
スクリーンショット21418 5 50 PM2
テキストを大文字にしたすべての行と列を並べ替えるわけではありません。
または、少なくとも、次のようなPROPER式を使用して、小文字と大文字をまとめて簡単に切り替えることができるため、そうしないことをお勧めします。
= PROPER（C2）
簡単ですよね？
新しい列を作成し、そこに数式を挿入します。
次のようになります。
スクリーンショット21418 5 49 PM
そして、最終的な結果は次のようになります。
スクリーンショット21418 5 50 PM
これは、大文字にしたいタイトルタグがたくさんある場合にもうまく機能します。
HLOOKUP式（後で説明します）を使用して、個々のタイトルタグを検索できます。
d30cz2g5jd7t8z.cloudfront
PROPER式を使用して、大文字と小文字が混在している場合でも、すべて大文字のフォーマット名を再フォーマットすることもできます。
スクリーンショット21418 5 55 PM
小文字のキーワードまたはタイトルタグを大文字（頭字語など）に変更する場合は、式UPPERを使用できます。
外観は次のとおりです。
スクリーンショット21418 5 59 PM
ご想像のとおり、LOWERの式はプロセスを逆にします。
スクリーンショット21418 5 59 PM2
これは本当に単純な式ですが、多くの人がそれを忘れているので、私はそれを含めました。
また、マーケターは、キーワード、タイトルタグ、またはその他のテキストを調べて、物事を活用する時間が常にあるとは限りません。
したがって、すばやく簡単に実行できる方法がある場合は、それを使用する必要があります。
6.「VLOOKUP」を使用して大きなスプレッドシートを検索します
一部のマーケターは、データをさまざまなスプレッドシートに分割することを好みます。
キーワード調査用、メール用、ドメイン用などがあります。
ただし、そのすべての情報を1か所にまとめている場合もあります。
SEO情報の大規模なデータベースがあると便利ですが、これらのさまざまな列、行、またはグラフをすべて並べ替えて、必要なものを見つけるのは面倒な場合があります。
2012 01 10 032029 1
前述したように、データがまだある程度整理されていない場合、Excelの検索機能は制限されます。
また、複数の行と列に分散しているデータがある場合は、さらに困難になります。
したがって、そのための公式があります（これは、「そのためのアプリがあります」のExcelバージョンです）。
次のようなVLOOKUP式を使用します。
= VLOOKUP（A4;'ルックアップテーブル'！A2：D1000; 4; FALSE）
たとえば、同じスプレッドシートの2つのシート間で特定のキーワードを検索する場合は、次のようになります。
Excel2でvlookup関数を使用する方法
この式は最初は少し複雑に見えるかもしれませんが、分解すると簡単になります。
最初のセクションは、検索しているアイテムの名前です。引用符で囲みます。
スクリーンショット21418 6 30 PM
次に、列範囲を追加します。
スクリーンショット21418 6 31 PM
次に、列のインデックス番号を追加する必要があります。検索する範囲の最初の列は1、2番目の列は2というように続きます。
最初の列でキーワードを見つけようとしているので、1を使用し、完全に一致させたいので、その後に「TRUE」ステートメントを続けます。
スクリーンショット21418 6 33 PM
完全に一致する必要がない場合は、「FALSE」を使用することもできます。
VLOOKUPは、必要なデータが最初の列（左端の列）にあり、データが昇順（A〜Z）である場合にのみ機能することに注意してください。
したがって、VLOOKUP検索を実行する前に列をフォーマットする必要がある場合があります。
それでもなお便利です。
これは、次のように、商品の価格や特定のカテゴリを検索する場合にも使用できます。
エクセルのヒントvlookupカテゴリ
行を検索する必要がある場合は、代わりにHLOOKUP式を使用できます。
（Vは垂直検索用、Hは水平検索用です。）
違いvlookuphloo
kup
HLOOKUP式の例は、次のようになります。
= HLOOKUP（D3;'ルックアップテーブル'！A1：D10; 2; FALSE）
HLOOKUP関数の例については、ここをクリックしてください。
どちらも、特にドキュメント内の複数の行、列、またはシートを検索する必要がある場合に、必要なものをすばやく見つける方法です。
必要に応じて、2つの別々のドキュメント間で両方の機能を使用し、ドロップダウンとピボットテーブルから検索することもできます。
成功に不可欠なトップ21のVLOOKUPのヒントとコツ
両方の数式を使用してシート間の異なるデータを見つけて計算する方法については、たくさんのチュートリアルがあります。
あなたが望むなら、あなたは本当にこれらの2つの公式でプロになることができます。
そのため、従来の検索バーよりもはるかに優れています（しゃれは意図されていません）。
7.「COUNTIF」を使用して重複をすばやく識別します
スプレッドシートのすべてのデータが正しいわけではありません。
すばやく簡単に並べ替え、重複を削除して、1日を進める方法が必要になります。
そのアクションのExcelの式はCOUNTIFであり、次のようになります。
= COUNTIF（A：A、A2）
最初の列（列A）に、重複をチェックするアイテムのリストがあるとします。
まず、新しい列を作成し、次のように列Aのセル範囲を使用して数式を入力します。
スクリーンショット21418 7 00 PM
次に、塗りつぶし数式をドラッグして他のセルにコピーすると、次のように表示されます。
スクリーンショット21418 7 03 PM
TRUEは倍数が存在することを示し、falseは正確な重複がないことを意味します。
これは、完全に一致するものだけを見つけるため、キーワードリストで重複を検索するためのちょっとした公式です。
したがって、上記の例でわかるように、「seo戦略」という単語は異なる時間に表示されますが（たとえば、seoマーケティング戦略）、重複とは見なされません。
それらの重複を単に強調したい場合は、条件付き書式を使用してそれを行うこともできます。
[ホーム]>[条件付き書式]>[セルルールを強調表示]>[値の重複]をクリックします。
スクリーンショット21418 7 14 PM
次に、複製に使用する色を選択できます。
これは、それらを強調表示したいだけで、削除する準備ができていない場合に便利です。
それらを削除する場合は、[データ]> [重複の削除]をクリックし、[列]で、重複を削除する列をオンまたはオフにします。
スクリーンショット21418 7 13 PM
プロセス全体には数分かかり、データをすばやくクリーンアップできます。
これらの数式の中には、「習得」するのに時間がかかるものもありますが、Excelには、役立つボタンと簡単な数式がたくさん用意されています。
COUNTIF式はその1つです。
結論
スプレッドシートを使用したり、数式をいじったりするのが嫌いな人はたくさんいます。
わかった。
一部の数式は、覚えるのが少し複雑でかさばります（REGEXTRACTを参照）。
ただし、SEOやマーケティングリサーチにスプレッドシート（特にExcelスプレッドシート）を使用している場合は、実際にいくつかの数式をブラッシュアップする必要があります。
具体的には、VLOOKUPやCOUNTIFなど、データをすばやく検索またはクリーンアップするのに役立つものを調べます。
SUBSTITUTEを使用してボリューム数を変換できることは、キーワード調査の必須条件です。
そして、ピボットテーブルのようなより複雑な（しかし役立つ）ものを作成する方法を学ぶことは、最終的には非常に多くの時間とエネルギーを節約するでしょう。
SEOはそれなりに複雑です。
その過程でいくつかのExcelハックを学ぶことで、負担を軽減します。
