この記事を読むのに必要な時間は約 12 分です。

「SFAには商談や顧客の情報が貯まっているのに、うまく分析・活用できていない」という声をよく聞きます。過去の商談実績から、将来の売上を予測したり、受注につながる成功要因を特定できたりするといいですよね。ただ、「そんな分析は専門知識のあるデータサイエンティストじゃないとできない」と思い込んでいませんか。実は、そのような分析も簡単にできるのです！

というわけで、前回に引き続き普段私たちが使い慣れているExcelだけ、しかもVBAも関数も使わず、マウス操作だけでできるSFA分析のやり方をご紹介します。一般に機械学習と呼ばれている内容を普段使っているExcelだけで実現してみますので、今までデータ分析に興味はあったものの避けてきた方や、RやPythonといったツールを勉強しようと思ったものの途中になってしまっている方もぜひ読んで試してください。

最初にExcelの標準アドイン「分析ツール」を設定しよう

今回の分析もExcelの標準アドイン「分析ツール」を使いますので、設定されていない方は、前回の記事を参考にして「分析ツール」を使えるようにしてください。Excelのメニューの［データ］タブに［データ分析］が表示されるようになったら、準備OKです。早速先に進むことにしましょう。

分析を始める前に：回帰分析の基礎のキソ

今回のデータ分析のテーマは「売上や受注に影響を与えている要因を、数多くのデータの中から探り出す」です。これが分かれば営業担当者の動き方も、マネージャーのマネジメントの仕方もずいぶんと変わってきます。この分析が［データ分析］の中にある［回帰分析］でできるのです。

早速クリックして始めたいところではありますが、ちょっと待ってください。最小限理解しておきたい「回帰分析の基礎のキソ」がありますので、そちらを手短にご説明します。

「回帰分析」とは売上高や受注率など、予測したいデータを説明するモデルを作るための手法です。たとえば「売上は足で作るものだ」というレトロな言い回しは、「売上高＝n百万円×訪問回数」のようなモデルを表現したものだと言えるでしょう。実はこれが回帰分析の書き方で、この例での売上高を一般的に目的変数と呼び、訪問回数を説明変数と呼びます。なので、一番簡単な回帰分析の式は以下のようになります。
目的変数＝a×説明変数＋b

ここでのa（先ほどの例の「n百万円」）を回帰係数と言います。aはプラスだけでなくマイナスの値を取ることもあり、aの絶対値が大きいほど目的変数を説明する力が強いことを意味します。

また、bは定数項または切片と呼び、説明変数がゼロのときの目的変数の値です。先ほどの「売上は足で作る」の例のように売上高や受注率の構成要素を調べる場合、説明変数がゼロ（例：訪問しない）のときは当然売上がゼロとなるはずなので、bをゼロに指定することが多いです。この後でご紹介するケース事例でもbをゼロとして分析しています。

ただ、訪問回数だけでは売上高を説明できないのと同じように、説明変数が1つで事足りることはなかなかありません。なので、一般的には説明変数の数を増やした下の式が回帰分析の基本形です。
目的変数＝a1×説明変数1＋a2×説明変数2＋・・・＋an×説明変数n＋b
「1つの目的変数を1つ以上の説明変数で説明する」「それぞれの説明変数には回帰係数がセットでついてくる」ということだけ覚えておいてください。

ケース基礎：年間売上高の予測モデルを作る

それでは、回帰分析を実際にやってみましょう。下の表はある機械メーカーの営業部門での顧客データの一覧です。顧客の「年商」や「営業利益率」「設備投資比率」「累計取引年数」といった説明変数を使って、「年間売上高」という目的変数を説明するモデルを作ります。

モデルを作るというと難しそうですが、やることはとても簡単。［データ分析］メニューをクリックして［回帰分析］を選ぶと、［回帰分析］ウィンドウが開きます。［入力Y範囲］は目的変数を、［入力X範囲］は説明変数を意味しますので、［入力Y範囲］には「年間売上高」を、［入力X範囲］には「年商」～「累計取引年数」を指定して、［OK］をクリックします。

するとExcelが下のような表を出してくれます。聞いたことのない用語と数字が並んでいますが、無視して下の方に行くと「係数」という項目があります。これが先ほど説明した回帰係数のことを指します。つまり、先ほどのデータからExcelが
年間売上高＝-0.07284×年商＋2836.534×営業利益率＋432.2362×設備投資比率＋470.3221×累計取引年数
というモデルを自動で作ってくれたのです。

ケース応用：年間売上高の予測モデルの説明変数を絞り込む

と、ここまでが一般的な回帰分析なのですが、ここからがデータサイエンスの面白いところ。ここで「年間売上高を予測するのに、本当に4つの説明変数が必要なのか、実はもっと短くて簡単なモデルがあるのではないか」と考えてみるのです。このようにモデルから説明変数を減らしていき、もっとも効率的なモデルを見つけることを「変数選択」と呼びます。一般的には機械学習の手法を使うものなのですが、それに近いことを［データ分析］の［回帰分析］だけでもやれるのです。

変数選択をおこなうときに使うのが、下の表のオレンジ色で塗られた4か所の数字です。

その中で最初に見るのが一番下の「P-値」です。P-値は１から0の間を取る数で、この値が小さい説明変数ほどその係数が0ではないと確かに言える、ということを示します。そのように見ると「営業利益率」のP-値は十分に小さいのに対し、「年商」や「累計取引年数」のP-値がかなり大きくなっていることに気が付きます。つまり、「年商」や「累計取引年数」を変数から削除できそうだということです。が、まずは慎重にP-値が一番大きな「年商」だけを削除してみます。

先ほどの［回帰分析］ウィンドウの［入力X範囲］で、「年商」が入らないように範囲を指定して［OK］を押します。

その分析結果が下の表です。ここで、先ほどは触れなかった「重決定R2」「補正R2」「有意F」を使って、モデルの出来を評価してみます。

「重決定R2」はモデルによる説明力を示し、1に近いほど説明力が高いことになります。先ほどの4変数のとき（0.949808）と比べてほとんど説明力が落ちていないことが見てとれます。

次に、少ない変数で効率的に説明できているかどうかを見るのが「補正R2」です。これも１に近いほど説明力が高いのですが、先ほど（0.719693）よりも大きく上がっています。

3つ目がこのモデル全体が統計的に安定しているか（確からしいか）をチェックする「有意F」です。この項目は数字が0に近いほど統計的に安定しています。先ほどの4変数のとき（0.004802）よりさらに小さく、安定したモデルだということが分かります。

この3つの数字から、先ほどの4変数のモデルよりも、「年商」を説明変数から省いた3変数のモデルの方が良いモデルであることが確かになりました。が、「P-値」を見ると「累計取引年数」のP-値が先ほど（0.730521）よりも大きくなっていますので、先ほどと同じように［入力X範囲］からこの説明変数を省いて回帰分析をしてみます。

その結果が下の表です。

先ほど比べて「重決定R2」はさほど変化がないままで（0.949034→0.948463）、「補正R2」は改善しており（0.765379→0.798244）、「有意F」もさらに改善している（0.000762→0.000088）ため、説明変数が「営業利益率」と「設備投資比率」の2つだけのモデルの方が先ほどの3変数のモデルよりも総合的に良さそうだと分かります。また「設備投資比率」の「P-値」も改善している（0.370702→0.173347）ため、これ以上変数を減らす必要はなさそうです。

ちなみにこの場合の年間売上高の予測モデルは下のようになります。
年間売上高＝2881.979×営業利益率＋516.8242×設備投資比率
以下の、最初に作ったモデルよりもずいぶんシンプルになりました。
年間売上高＝-0.07284×年商＋2836.534×営業利益率＋432.2362×設備投資比率＋470.3221×累計取引年数

回帰分析をするときの要注意ポイント

ただ実際にデータを入れて分析する際に気を付けてほしい、「多重共線性」という問題があります。説明変数の中で「線形関係」があると、分析した値が不安定・不確かになるという問題で、通常の統計解析パッケージではそれを回避するようになっていますが、Excelの［回帰分析］にはその機能がないので、使い手側でチェック・回避する必要があります。

「線形関係」の中身の説明は、それだけで記事2つ分くらいになってしまいますので、実務的な話に絞ることにします。実際の分析現場でよく起きる線形関係は、説明変数の中に相関係数の高い組み合わせがあるというもの。そのため、回帰分析をする際には、前回の記事で紹介した［相関］分析の手法を使って説明変数同士の相関をチェックし、0.9以上または-0.9以下となる組み合わせの片方を省いてから［回帰分析］をするようにしてください。

Excelの標準アドインだけでも自社の「売上モデル」「受注の方程式」を見つけられる

Excelに標準で搭載されている［回帰分析］や［相関］だけでも、表の見方さえ分かっていればこのような「変数選択」が簡単にできます。そしてこれを使うことで、SFAに入っている様々な説明変数と売上高や受注率などの目的変数を使って、皆さんの営業組織独自の「売上モデル」「受注の方程式」を見つけることが可能になります。

例えば先ほどのケースでは、優先的に確認すべき顧客の情報は「営業利益率」と「設備投資比率」の2つでした。このことが分かっていると、有価証券報告書などを使ってこれらの情報を確認しておくことで、事前に精度高く顧客を優先順位付けできるようになり、売上を期待できない顧客に力を割きすぎてしまうということを防げます。また、SFAに入力する顧客データの項目を効率的に絞り込むことも可能になります。このように営業活動やマネジメントの効率化に直結する分析手法ですので、ぜひ試してみてください。

トライツコンサルティングは、データ分析を基点としたSFAの有効活用や営業戦略づくりのパワーアップを支援しています。「SFAに蓄積したデータを詳しく分析したい」「データから営業活動のヒントを見つけたい」とお考えの方はご相談ください。