今回は、TRANSPOSE関数～TRIMMEAN関数までをご紹介しております。
TRANSPOSE関数
読み方： トランスポーズ
TRANSPOSE(配列)
縦横を入れ替えた表をする
TREND関数
読み方： トレンド
TREND(既知のy,[既知のx],[新しいx],[定数])
重回帰分析による値を予測する
TRIM関数
読み方： トリム
TRIM(文字列)
不要なスペースを削除する
TRIMMEAN関数
読み方： トリムミーン
TRIMMEAN(配列,割合)
異常値を除いて平均値を算出します
次のようなデータがあるので、予測値を算出する。
できれば、様々なケースの予測を同時に算出したい場合、Excelの関数をつかうことで、手早く算出することができます。
B列には、売上高。C列には、来店客数のデータがあります。
せっかくデータがあるので、売上高を1100にするには、どのぐらいの来店客数があればいいのかを算出したいわけですね。
B列とC列のデータを目視から、「このぐらいじゃない」というのは、マズいわけですね。
来店客数の予測を算出すること自体は、関数で手早く算出できるのですが、前提条件として、売上高と来店客数に、関係性があるのか、ないのか、ということがあげられます。
そこで、F1に、相関係数を算出することから始めます。
F1に設定している数式は、
=CORREL(B2:B12,C2:C12)
CORREL関数は、相関係数を算出できる関数です。
この値が、「1」に近いので、因果関係まではわかりませんが、何らかの関係性は強いと判断できます。
逆に「0」に近ければ、「関係性はない」ということがわかります。
来店客数が増えれば、売上高があがることがわかったわけです。
これを踏まえたうえで、売上高に必要な来店客数を算出していきます。
=INT(TREND(C2:C12,B2:B12,E4:E12))
という数式を設定します。
予測値を算出するだけならば、FORECAST関数を使うことで算出できますが、複数の予測値を算出するには、TREND関数をつかいます。
予測値を算出するにあたり、回帰直線のy=ax+bに基づき引数を設定していきます。
既知のyには、来客数の予測値を算出したいので、C2:C12を設定します。
既知のxには、売上高がxにあたりますので、B2:B12を設定します。
新しいxには、売上高の予定値を設定しますので、E4:E12を設定します。
今回は、定数は省略しています。
また、算出結果に小数点が表示されます。
来店客数なので、INT関数をつかって、整数化しています。
以前は、配列関数にしなければいけませんでしたが、今は、スピル機能が追加されましたので、F12まで予測値を算出してくれます。
このように、ちょっとしたデータでも、複数の予測値を素早く算出することができますので、今までの資料にも、このような関数を使いすることで、違った角度から資料を見ることができるかもしれませんね。
データをまとめた表があります。
広告をしてみた結果、来店客数に影響があるのかないのか、例えば、広告代を増やしたら、もっとお客さん来てくれるのかなど、気になるわけですね。
当然のことながら、経営とか見た目などフィーリングで判断するわけにはいきません。
そこで、この二つのデータに「相関関係」はあるのかないのかを知るには、ある関数を使うことで比較的簡単に、確認することができます。
【相関関係の強弱を数値化（相関係数）】
相関係数を算出するには、「CORREL関数」という関数が用意されています。
今回は、広告代を「X」、来店客数を「Y」とします。
=CORREL(B2:B13,C2:C13)
二つのデータの範囲をそれぞれ引数に設定するだけで、算出することができます。
算出された数値は、何を意味するのかというと、相関係数「1」に近いほど、関係性が強く、「0」に近いほど、関係性が弱いので、今回は、「0.99」という算出結果から、「1」に近いことから、関係性が強いことがわかりました。
せっかく、関連性がわかったので、広告代を思い切って、奮発して、50000にしたら、来店客数はどのぐらいになるのか、予想することもできます。
【FORCAST.LINER関数で予測】
そこで使用する関数が、「FORECAST.LINEAR関数」。
FORECAST.LINEAR関数は、回帰直線による予測値を算出することができる関数です。
引数の設定が簡単なので、使わないのはもったいない関数の一つです。
なお、FORECAST.LINEAR関数は、Excel2016から登場した新しい関数です。
=FORECAST.LINEAR(F3,C2:C13,B2:B13)
2338と予測値を算出することができました。
【複数の予測値を算出したいなら、TREND関数】
FORECAST.LINEAR関数は簡単で便利ではあるのですが、欠点があって、複数の予測値を算出することができません。
広告代が30000だったら？35000だったら？と広告代の数値を入力し直すのは面倒です。
そこで、複数の予測値を算出したい場合は、「TREND関数」を使います。
F7の数式は、
=TREND(C2:C13,B2:B13,E7:E11)
F列に広告代別の予測来店客数が算出されました。
広告代が50000のときに、2338.711と算出されています。
先程の、FORECAST.LINEAR関数で算出した結果と同じ算出結果になっていますね。
ところでTREND関数は、配列数式で算出する必要があるので、Ctrl+Shift+Enterで確定する必要が、『かつて』はありましたが、Office365のExcelに新たに追加された『スピル機能』によって、そのままEnterキーで確定するだけで、連続するセルに数式を設定してくれます。
そのため、以前に比べて、配列数式が苦手だった人でも、使い勝手がよくなっています。
