キーワード調査――カテゴリを使って実用性を高める（後編 Excelの配列数式を使ったデータ整理）

この記事は、前後編の2回に分けてお届けしている。後編となる今回は、前編で集めた大量のキーワードデータを、Excelの配列数式を用いて処理する方法を紹介しよう。

前編では、君が中古車に関する内容を扱うWebサイトを運営しているという前提で、有名な自動車メーカーのブランド名と地名を含む検索キーワードを、順列組み合わせで大量に生成し、それぞれの検索キーワードに関して検索ボリュームを調べた。

後編では、Excelでがっちり作業する番だ。

カテゴリを作成する

手始めにキーワードのカテゴリを作成し、続いてExcelの配列数式を使ってデータセット内の各キーワードを分類していこう。

例として、地域、車のブランド、車に基づいたキーワード別にふるい分けると、実に興味深いものになった。

これから紹介するのはExcelの上級者向けテクニックで、実際に各自が実行する時は、それぞれが問題を解決する必要が出てくるであろうことを覚悟しておこう。でも、やり抜けば、実にクールな結果が得られるはずだ。

まず「カテゴリ」というシートを作り、組み合わせキーフレーズの元にしたキーワード群をカテゴリに分け、そのカテゴリに属するキーワードの一覧を作る。これは、このあとに作るシートから参照するマスターデータとなる。

カテゴリ名ごとに見出しを付けてカテゴリ表を作り、検索キーワード候補から、各カテゴリに属するキーワードを入力していく。

例ではブランド名には「bmw」「audi」「mercedes」などのキーワードを、状態には「new」「used」などのキーワードを入力してある。

カテゴリを横に並べているが、列と列の間に関係はない。たとえば上図では1行目を横に見ると「bmw　new　aberdeen……」と並んで見えるが、そのように横に見るものではなく、縦方向に見て考えてほしい。つまり、「Brand」のカテゴリに「bmw」「audi」などの項目が入っていて、また別の「Condition」のカテゴリには「new」「used」などの項目が入っているという具合だ。

次に新しいシートを用意して（たとえば「検索ボリューム」シート）、キーワード検索ボリュームの表を作る必要がある。キーワード、検索ボリューム、カテゴリ名の列を作ろう。

次がちょっとした見せ場だ。Excelの配列数式を使い、カテゴリマーカーとキーワードのリストに含まれている単語とで文字列をマッチングさせ、そのカテゴリに属するキーワードを特定していく。分類されたリストはこんな感じだ。

このリストを作るために使った数式は、たとえば次のようなものだ。

{=IF(SUM(NOT(ISERROR(FIND(カテゴリ!$A$2:$A$7,$A2)))*1)>0,C$1,"【非】"&LOWER(C$1))}

ただし、上記の数式をそのままExcelに入力してもうまく動作しない。以下で解説する「[Ctrl]＋[Shift]＋[Enter]キーの配列数式ワザ」を使わなければうまくいかないものなので、気が急く人も、今すぐExcelに向かわずに、続きを読んでほしい。

「'カテゴリ'![セルの範囲]」の部分は、カテゴリ表内でマッチングさせたいカテゴリの列を参照している。「$A2」が調べたいキーワード、この場合だと「audi」で、「C$1」はカテゴリ列の名前、この場合は「ブランド名」になる。

また、数式に奇妙な波括弧（「{ }」）が入っているのに気づいていることだろう。この波括弧は、数式として入力するのではなく、「配列数式」というExcelの機能によって自動的に入力されたものだ。この「配列数式」の機能によって、この数式が正しく動作するんだ。

データを1つにまとめる

今から紹介する手順に沿って、初めて作ったキーワードのカテゴリ別リストがExcelでうまく処理できるかどうか確かめよう。

→ サンプルのExcelシートをダウンロードする

1. 数式をExcelに貼り付け、下のスクリーンショットで赤線で囲まれている数式の部分を選択する。

   

2. カテゴリ表が入ったシートのタブをクリックし、マッチングさせたいカテゴリを選択してから[F4]キーを押して絶対参照にする。

   

   ここで項目が入力されていない空白セルも含めて範囲を指定してしまうとうまくいかない場合があるので注意。

3. ここで[Ctrl]キーと[Shift]キーを押しながら[Enter]キーを押すのがミソだ。この[Ctrl]+[Shift]+[Enter]キーが、Excelに「入力した数式を配列数式として扱え」という指示なのだ。各セルをそうして埋めていくと（または配列数式で完成させたセルをオートフィルで拡張すると）とほら！ キーワードが分類できた。1行目のオートフィルタを使って、たとえばD列で「状態」の行だけに絞り込むと、「new」「used」「second hand」などの状態を含むキーワードに絞り込んで確認できる。

   

キーワード調査にこうした高度なテクニックを使うのはなぜか？

新しいサイトのアーキテクチャを最初からデザインする際に、あるいは既存サイトの強化を考える際に、君はどんなことを考えているだろうか。おそらく、そうした再の新しいコンテンツグループの有効性の証明、動的メタテンプレートの変更、あるいはキーワード戦略決定の明確化は常に、データに基づいてなされてきたはずだ。

大量かつ広範囲なデータセットを扱う能力があり、それだけ大量のデータでも分類できる手法を知っていれば、より強い自信を持って決断を下せるのは間違いない。少しの練習と忍耐を厭わない人なら、この意見に賛成してくれるだろう。いろいろな業界でよく使われるカテゴリの例としては、次のようなものがある。

• 性別 （「男性用」「女性用」「女児用」「男児用」）
• 時期 （「クリスマス」「バレンタインデー」「復活祭」）
• 都市名や州名 （「ニューヨーク」「ワシントン」「ニューハンプシャー」）
• 色 （「赤」「緑」「青」）

そのほかに、製品グループや製品名、買い手の意図（調査、レビュー、購入）、そしておそらく、サイトアーキテクチャにおけるカテゴリページの位置をダイレクトに反映するグループ名（第1層、第2層）だって使える。悪くないよね。

どのようなデータを使うべきか？

答えは「全部」だ。見つけられるものは何もかも。手に入る限りのものを利用しよう。ここがポイントだ。グーグルの検索ボリュームだけに頼りたくはないだろうし、もし可能なら、データセットの中にランキングデータや分析の項目を作るべきだ。

そのためにはExcelにVLOOKUP関数があることを神様に感謝するよ。でも、この件に関してはここでは詳しく説明しきれないので、Excelのヘルプなどで使い方を学んでほしい。

次に何をすべきか？

そうだなぁ、次にすべきこととして最もふさわしい行動をお勧めするとしたら、ピボットテーブルを使ってデータを分析することだろう。

ピボットテーブルの作成は、データをさまざまな視点から深く掘り下げて素早く分析する実に簡単な方法だ。僕はピボットテーブルのチュートリアルを書いたが（キーワード調査のための素敵なチャートの作り方も説明してある）、その内容はキーワード調査をする人に的を絞ったものになっている。

筆者のrichardbaxterseoについて ―― Richard Baxterは、ロンドンにあるSEOエージェンシーSEOgadget.co.ukの創立者。同社は、大規模サイト構造、キーワード調査、テクニカルSEO、ソーシャルメディア技術コンサルティング、および競争の激しい業界におけるリンクビルディング業務を専門にしている。