中小企業経営に科学を　

会計ソフト設定時のコツで説明したとおり、売上高やその他の経費の月ごとの対応関係にズレがある場合には、まずそれを修正しておいて下さい。（まぁここでは多少のずれは気にせず進んでみましょう。）

ここから先はサンプルデータを元に進めます。みなさんもダウンロードして置いてください

まず、データは[suii]というシートのようになっているはずです。

これから行う分析は「縦に月／横に勘定科目」という形式でなければなりませんので、A1:Y35を指定してコピーしてください。そしてそのまま[sheet1]のA1セルを選択して、「形式を選択して貼り付け」を選んで、右下の方の「行列を入れ替える」をチェックして「OK」してください。

うまくいくと、"suii(行列入替後)"のようになります

いよいよこれからが本番です。

①シートの列を計科目とその他の科目に分ける（目的変数と説明変数に分ける）

「純売上高」や「当期売上原価」のような「集計用」の項目の列を切り取って、一番右に寄せてしまいましょう。（処理後は計科目移動後シートを参照）

②分析を行う

[ツール]－[分析ツール]を選択して、「回帰分析」を選んでください。
ここでの設定は、以下のようになります。

 
入力Y範囲　AJ1:AJ25　←経常利益のX1年4月～X3年3月（目的変数）
入力X範囲　B1:AB25　　←売上高～雑収入のX1年4月～X3年3月（説明変数）
あとは該当箇所をチェックしておいて頂いて、「OK」です。


するといきなり

という、エラーメッセージが出てしまいます。
このエラーこそがエクセルで統計分析をおこう時の最大の問題点です。
分析する説明変数が16個までしか指定できないのです。

③エクセルに合うように列（科目）を削除する。

削除の仕方ですが、適当にというわけにはいきません。ここでは詳しい説明は省略して、
以下の

・販売員旅費
・発送配達費
・修繕費
・事務用消耗品費
・通信交通費
・水道光熱費
・租税公課
・接待交際費
・賃借料
・雑費
・受取利息

をバッサリとやってみたいと思います。
これで、何とか16個に納まります。

④再度分析を行う

もう一度②の作業を行います。
今度は、
入力Y範囲　Y1:Y25
入力X範囲　B1:Q25
として、「OK」を押します。
今度は計算ができたはずです。

ここで、出来上がった新しいシートを見ていきましょう。
このシートの中で大切なのは、回帰統計の中の補正Ｒ２の値です。これがこの分析の確からしさを表しています。
重回帰分析ではこの値は0.9xという値（1に近いほど確からしい）になります。この「0.999439」という数値は、まあまあ当てはまりの良い値となります。もう少しこの値が大きくならないか、検討してみましょう。

科目の中に「利益と関係度の低い費目」が含まれている可能性があるからです。

⑤利益と関係の無い科目を削除する

それでは「利益と関係度の低い科目」とはどの科目でしょうか？
これは、分散分析表の「P-値」という数値に着目すると分かります。
P-値は危険度をあらわす数値で、大きいほどこの分析に当てはまらないことを示します。

今、地代家賃の0.923285が一番大きくなっています。
そこで、先ほどの「16変数」のシートに戻って、このシートから地代家賃費を削除して、再度、回帰分析をしてみてください。

今度は「補正Ｒ２が0.999509」と先ほどと比べて、あがってきているのが分かります。
このようにして、「P-値」が高いものを順に削除していきます。

ある程度削除すると、補正Ｒ２が逆に下がり始めます。
補正Ｒ２が最高値になるのが、この分析での最高値になります。

・備品・消耗品費
を削除してみましょう。削除した後の補正Ｒ２は0.999518となります。

さらに、ここでP-値が最も高い「支払手数料」を削除して、回帰分析をおこなうと、補正Ｒ２は0.9993となり、下がってしまうのが分かります。
つまり、支払手数料は削除してはいけないということになります。

従って
・売上高1
・売上高2
・商品仕入高
・販売員給与
・広告宣伝費
・支払手数料
・役員報酬
・従業員賞与
・法定福利費
・厚生費
・減価償却費
・保険料
・管理諸費
・雑収入
から「経常利益」が計算できるということになります。

様々な勘定科目がある中で、経常利益がたったの14項目で説明できてしまうのです。

ちなみに、算式であらわすと

経常利益
=売上高1*0.984129+売上高2*0.969614-商品仕入高*0.86579-販売員給与*1.36219-広告宣伝費*1.26294-支払手数料*0.68443-役員報酬*0.93115-従業員賞与*1.19946-法定福利費-0.84585-厚生費*4.3387-減価償却費*1.06729-保険料*11.9753-管理諸費*1.86956+雑収入*1.011123-92145.3

となります。
つまり、上記科目が経常利益を考える上で非常に大切（利益に貢献している）で、その他の科目は無視しても良いということになります。

+（プラス）科目を増やして、-（マイナス）のを減らせば利益が出るということになります（計算上他の科目は増減しても経常利益には関係ないということになります）。

更に、関係している科目の重要度は分散分析表の「係数」の値になります。
これらをグラフにすると、以下のようになります。

いかがでしたか？
次からは、何をどう分析すると、何が分かるのかを考えていきたいと思います。

注意事項と補足

エクセルの分析ツールの回帰分析では説明変数を16個までしか指定できません。会計ソフトの設定時に、なるべく勘定科目を少なくした方が、分析の手間は省けると思います。
ただし、余り勘定科目を少なくしすぎると、他の分析をするときに弊害があるかもしれませんので、注意してください。

P-値=確率分布が与えられ、確率変数Ｘの値ｘが得られたとき、その値より大きい確率変数の値が起こる確率の総和

T値=偏回帰係数の値／係数の標準誤差

補正Ｒ２＝自由度調整済み寄与率
＝1-（（ｎ-1）÷（ｎ-ｐ-1）×（1-Ｒ*2）)
寄与率は説明変数の数を増やすほど、その変数が有用なものであろうとなかろうと、高い値になっていく。そこで、無意味な変数を説明変数として使ったときには、数値が下がるように、自由度を補正した寄与率が使われる。

[関連記事]

会計ソフトから月次推移データをエクスポートする

エクセルで「利益⇔売上・販管費」の相関分析をする　
計算結果から因果関係を推測する　
売上増加の秘密を探る　
どの得意先に売ったら儲かるのか　
失敗事例　
エクセルで「売上や販管費」の相関分析をする際の会計ソフトの設定上の注意点　
期待する分析結果が出ない場合もある　
重回帰分析がうまくいかない場合