らくらくIT活用術
PC活用講座 - Excel
「Excelの便利機能活用術」
業績データのばらつき具合を求めて業務に有効活用
ITライター 立山秀利
仕事で業績データを分析して次に活かすことは、業種・業務を問わず共通して求められるもの。有効なデータ分析指標の一つが、データのばらつき具合を数値化する「標準偏差」だ。今回は標準偏差をExcelのSTDEV.S関数で求める方法とともに、標準偏差の値に読み解き方・活かし方の例も紹介する。
「標準偏差」をSTDEV.S関数で求める
業務で標準偏差をExcelで求め、次に活かす例として図1を見てほしい。ある部署で顧客向けのイベントを2つの会場で毎月開催しており、その場で自社のノベルティグッズを配布しているとする。2つの会場（A会場とB会場）における毎月の配布数が図1の表のB2～C364に、1年分記録してあるとする。
この業績データを分析し、両会場それぞれ次回イベントで、ノベルティグッズをどのぐらい用意しておけばよいか調べたいとする。用意する数が少なすぎると、配布できない顧客が発生してしまい、逆に多すぎると、余ってしまい余計なコストがかかる。
図1
図1は集計として、14行目にSUM関数で合計、15行目にAVERAGE関数で平均を求めている。
合計と平均は両会場でまったく同じである。しかし、各月の配布数をよく見ると、A会場とB会場でばらつき具合に差がある。A会場の配布数の最小は6月の259、最大は7月の332であり、その差は73だ。一方、B会場は最大が7月の410、最小は9月の215であり、その差は195と大きい。合計と平均が同じで、ばらつき具合にこのような違いがある。
ばらつき具合は「標準偏差」によって数値化できる。標準偏差の値が大きければ、ばらつき具合が大きく、標準偏差の値が小さければ、ばらつき具合が小さいとわかる。
Excelには標準偏差を求めるSTDEV.S関数がある。書式は次の通り。
-----------------------
STDEV.S(数値)
-----------------------
引数には、標準偏差を求めたいデータが入ったセル範囲を指定する。今回の例のデータで、A会場の標準偏差をB16セルに求めるなら、次のようにSTDEV.S関数を入力すればよい。引数には、A会場のデータが入ったB2～B13セルを指定する。
-----------------------
=STDEV.S(B2:B13)
-----------------------
すると、B16セルに標準偏差として、20.72767が求められた（図2）。
図2
同様にB会場の標準偏差をC16セルに求めるには、「=STDEV.S(C2:C13)」と入力すればよい。引数には、B会場のデータが入ったC2～C13セルを指定する。すると標準偏差は63.70386と求められた（図3）。
図3
標準偏差の小数点以下を四捨五入すると、A会場は21、B会場は64であり、3倍以上も異なる。つまり、B会場はA会場よりも、ノベルティグッズ配布数のばらつき具合が3倍以上多いとわかる。
標準偏差をもとに次回配布数を出す
さらに、先ほど求めた標準偏差を利用すると、両会場の次回イベントで、ノベルティグッズをどの程度用意すればよいかわかる。
統計学的に、データのうち約68％が標準偏差-1個分～標準偏差1個分に収まり、かつ、約95％標準偏差-2個分～標準偏差2個分に収まるというルールがある（図4）。ただし前提として、データの分布はグラフの縦軸を個数、横軸を値とした場合、図4のような形状とする。このような分布は「正規分布」と呼ばれ、詳しい説明は割愛するが、一般的に多くのデータにあてはまる。
図4
このルールから、次回イベントでノベルティグッズを用意すべき数を求めてみよう。A会場は標準偏差が21であり、平均は300だ。平均から標準偏差-2個分は300 - 21 × 2 = 258、標準偏差+2個分は300 + 21 × 2 = 342となる。つまり、配布数は約95％の確率で258～342個の範囲に収まっており、次回もその範囲を目途に用意すればよいとわかる。
一方、B会場は標準偏差が64なので、平均から標準偏差-2個分は300 - 64 × 2 = 172、標準偏差+2個分は300 + 64 × 2 = 428となる。そのため、たとえば次回は428個を目途に用意すれば、配布できない顧客の発生を95％の確率で防ぐことができ、かつ、多く用意しすぎて余計なコストがかかることもなくなる。
今回紹介した標準偏差をもとにしたデータ分析は、金融商品の価格変動リスク、工業製品の品質のばらつきなど、さまざまなシーンで利用されている。ExcelのSTDEV.S関数を活用しつつ、自分の業務に応用してみよう。
その他の【Excel】を見る
立山秀利（たてやま・ひでとし）
カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。
（監修：日経BPコンサルティング）