ABC分析〜エクセルを使ってグラフを作る〜

マーケティングの分析手法ABC分析(パレートの法則)をエクセルを用いたやり方をご紹介します。今回は、SEO対策の一環としてるため、キーワードを製品に見立ててます。

ちなみに、エクセルと言っても、Goolgleのスプレッドシートを使ってるため、マイクロソフトとはグラフを作る際に操作が異なるため悪しからず。

スポンサーリンク
スポンサーリンク




キーワード(項目)の羅列

まずは、下記画像の様に、A列にブログテーマに関するキーワードを羅列します。

キーワードの羅列

月間検索ボリュームを入力する

次に、赤枠部分の様に、B欄に月間検索ボリューム欄を作成します。

キーワードプランナーを使い、各キーワードの月間検索ボリューム数を調べ、入力します。

※今回は、仮想の数字を入れているため、実際に調べたらこの通りにはなりません。

月間検索ボリューム

月間検索ボリュームの合計を算出する

合計欄を作り、エクセル関数である”=sum”を使いボリュームの総計を算出します。

赤枠部分の様に、キーワードの一番下に、

①”合計”と打ち込み、その横に、”= sum”と入力しエンターをおす

②オレンジの部分をドラッグしてエンターを押すと、合計が算出される

以上の手続きを行うと、右側の赤枠の様に合計値が算出されます。

ボリューム合計

構成比欄の作成

続いて、C 欄に構成比の欄を作成します。

構成比

構成比は=ボリューム÷合計なので、

①”=”を入力した後

②オレンジ枠(意図のボリューム10000)をクリック後、ボリュームの合計をクリック

以上の手続きを踏むと構成比が入力されます。

ただし、このままだと、①の様に、構成比が数字のママです。故に、

②赤枠部分の”%”マークを押す

③表記が%表示になる

青色のカーソル右下の”四角”部分をダブルクリックすると、同じ計算式が以降のセルにも反映される

構成比コピー

累積構成比の欄を作成

今度は、D 列に累積構成比欄を作成します。

累積構成比

下記画像を参考に、

・累積構成比列の1行目は、構成比の数字(16.68%)をそのまま入力

・2行目には、”=sum” と入力し”エンター”を押す

・オレンジ枠(16.68%)をクリック

・紫枠(15.01)をクリックし、”エンター”をおす

以上の手続きをこなすと、キーワードの構成比の合計が反映されます。

累積構成比の算出

あとは、構成比の欄を作成した時と同様に、青カーソルの右下をダブりクリックすれば、計算式がそれ以降のセルにも反映されます。

累積構成比コピペ

これで、数字の算出は終わりです。

グラフの作成

最後はグラフを作成します。

グラフ作成

・まずは、キーワード(A列)とボリューム(B列)の範囲をドラッグ

・続けて、マックの場合”command”を押したまま、 累積構成比をドラッグ

ドラッグし終わったら、下記画像の赤枠を参考に、”挿入”から”グラフ”を選びクリックします。

グラフ挿入

そうすると、グラフが反映されるので、右側のエディタでグラフの”データ”から、”複合グラフ”を選択します。

グラフ編集

・するとグラフが①の様な棒グラフに切り替わるので、グラフエディタより

()グラフ編集

②”カスタマイズ”を選択

③”系列”を選択して”対象”から”累積構成比”を選択す

グラフ編集③

最後に、グラフの種類から”折れ線”を選択し、グラフ軸を”右軸”に設定して手続きは完了です。

左軸をボリューム数、右軸を累積構成比にしグラフの完成です

ABCグラフ

ランクわけ

ABC分類

このリストをランクづけするならこんな感じでしょうか。

BとCは区分けによって異なりますが、Aランクは累積構成比80%までに含まれる範囲が一般的にはAランクとなってる様です。ちなみにロングテールとは、往往にしてCランクの部分が該当します。

スポンサーリンク
スポンサーリンク




スポンサーリンク




シェアする

  • このエントリーをはてなブックマークに追加

フォローする