今回は、予算や営業成績などのデータをExcelを使って分析し、よりコストパフォーマンスを高める方法について見ていく。

ソルバー実行の準備をする

 消費財メーカーでマーケティングを担当しているJさんは、広告を出稿しているWebサイトQ(以下、サイトQ)とWebサイトR(以下、サイトR)の広告費と、両サイトを経由した成約数を分析して、より効果の高い広告予算配分を考えようとしている。こうしたテーマでより良い答えを見つける際には、Excelが持つ「ソルバー」が威力を発揮する。

 ソルバーは、目標とする値を達成するための最適解を算出する機能だ。この機能を使えば、サイトQとサイトRの広告費用配分を最適化できる。そのためには準備として、過去のデータからサイトQとサイトRの回帰直線の一次関数式を割り出す必要がある。手順は本特集の3回目で説明した方法を用いるとよい。

 まず、今回使用するデータを確認しておこう。サイトQとサイトRについて、1月から12月までの「広告費」と「成約数」の実績を記入したExcelシートだ。下の図のように、セルB15とD15にはそれぞれサイトQとサイトRにおける「広告費」と「成約数」の相関係数を示した。用いているのはCORREL関数だ(詳しくは本特集の3回目を参照)。ここで用いているExcelシートの場合、B15を選択して、「数式」タブの「その他の関数」→「統計」から「CORREL」を選び、「配列1」を「B3:B14」、「配列2」を「C3:C14」と設定して「OK」ボタンを押す。サイトQは「0.90」、サイトRは「0.91」と非常に強い相関関係が見られる。

使用するデータを確認する
サイトQとサイトRについて、1月から12月までの「広告費」と「成約数」の実績データを用意した。いずれも相関係数は非常に高い
[画像のクリックで拡大表示]

 次にそれぞれの「広告費」と「成約数」から、サイトQとサイトRの散布図を作成して、回帰直線とその一次関数式を表示する。

 サイトQの場合はB2:C14を選択して、「挿入」タブ→「散布図(x,y)またはバブルチャートの挿入」→「散布図」を選ぶ。さらに「グラフ要素」ボタンから「近似曲線」にチェックを入れ、さらに右横の三角形マークから「その他のオプション」を選び、「近似曲線のオプション」で「グラフに数式を表示する」「グラフにR-2乗値を表示する」にチェックを入れる。下の図はサイトQとサイトRについて散布図を描き、回帰直線とその一次関数式、さらにR2乗値を示したものだ。

サイトQとサイトRの散布図と回帰直線
サイトQとサイトRの散布図を作成して回帰直線(近似曲線)を表示した。また、回帰直線の一次関数式とR2乗値も表示した
[画像のクリックで拡大表示]