製品の品質管理や売上のばらつきを把握したい場合は、Microsoft社の表計算ソフトであるエクセルがおすすめです。
エクセルの関数を使えば、データのばらつきを表す標準偏差を簡単に算出できます。
本記事で、エクセルで標準偏差を算出する手順を確認していきましょう。
エクセルで算出できる標準偏差とは
エクセルで算出できる標準偏差は、データ内の値の不規則具合を表す統計量です。
標準偏差が小さいほどデータが平均値に集中していると判断でき、大きいほどデータが平均値から散らばっているといえます。
主に、品質のばらつきが適正値に収まっているかを見たり、商品販売数の期待値を算出したりするなど、ビジネスの場で活用される機会が多いでしょう。
例えば、1月の売り上げが3つの店舗で以下の表のとおりだったとします。
| 店舗 | 売り上げ |
| A店 | 680,000円 |
| B店 | 720,000円 |
| C店 | 700,000円 |
一方で、2月の売り上げが以下の表のとおりと仮定しましょう。
| 店舗 | 売り上げ |
| A店 | 500,000円 |
| B店 | 700,000円 |
| C店 | 900,000円 |
どちらの月も平均の売り上げは700,000円と変わりありませんが、標準偏差では1月の売れ上げが「16329」、2月の売り上げが「163299」と2月の売り上げの方が店舗ごとのばらつきが大きいことがわかります。このように、標準偏差を算出することで、平均だけでは見えてこなかったデータの傾向が見えます。
そのため、標準偏差を活用することで、売上を伸ばすためのより効果的な対策を取れるようになるでしょう。
標準偏差はどうやって求める？
標準偏差はデータの値と平均値の差を2乗して合計した数値を、データの総数で割った正の平方根で算出できます。
実際に算出するとなると、以下の計算が必要になります。
- データの平均値
- 平均値の差
- 平均値の差を2乗した合計
- 2乗した合計を総数で割った正の平方根
これらを計算するのは大変ですが、エクセルの関数を使えば簡単な操作で標準偏差の算出が可能です。
そのため、数学の知識のない方でも標準偏差をデータとして用いることができます。
エクセルで標準偏差を算出できる関数の違い
エクセルで標準偏差を算出するために利用される関数は、主に以下の2種類です。
- STDEV.P関数
- STDEV.S関数
それぞれの関数の特徴や使い方について見ていきましょう。
STDEV.P関数
調査対象となる集合全体を現す母集団の総量に対しての標準偏差を求めたい場合は、STDEV.P.関数を使用します。
例えば、工場で製作された製品の品質を調べたい際、一部ではなくすべての製品を対象としたい場合に適しています。
STDEV.P関数の構文は以下のとおりです。
STDEV.P（数値1, 数値2, …）
引数は最大で255個まで指定できます。
また、STDEV.P関数と同じ機能を持つSTDEVP関数もエクセルには存在していますが、旧バージョンの関数なので、基本的にはSTDEV.P関数を使いましょう。
STDEV.S関数
すべての数ではなく、無作為に選ばれた一部から母集団の標準偏差を求める際に使われる関数です。
例えば、工業品の品質チェックをする際、日本全国にあるすべての工場から製品をチェックするのは手間がかかります。
そのような場合は、とあるロットを抽出して計算することで、全体の標準偏差を推測します。
そこで使用されるのがSTDEV.S関数です。STDEV.S関数を使用すれば、全体の標準偏差を推測できますが、STDEV.P関数を使用した際よりも数値が大きくなる点には注意しなければいけません。
STDEV.S関数の構文は以下のとおりです。
STDEV.S（数値1, 数値2, …）
引数は最大で255個まで指定できます。
また、STDEV.S関数と同じ機能を持つSTDEVS関数もエクセルには存在していますが、旧バージョンの関数なので、基本的にはSTDEV.S関数を使いましょう。
エクセルで標準偏差を算出する手順
エクセルでは以下の3ステップで標準偏差を算出できます。
- 表を作成する
- 平均を算出する
- 標準偏差を算出する
実際にエクセルで標準偏差を算出できる関数である「STDEV.P関数」を使って、標準偏差を算出する手順について確認していきましょう。
標準偏差を算出する手順①表を作成する
今回は、プロフィールの表に記入された年齢から標準偏差を算出してみたいと思います。B列に「名前」、C列に「性別」、D列に「年齢」の項目を作成しました。
表には背景色や罫線などを適用するなど適宜調整して、見やすい見た目にしておきましょう。
また、表の作り方がわからないという方は、以下の記事で基本的な表作成の手順を解説しています。表作成はデータをまとめるうえで必須のスキルなので、この機会に作り方を確認しておきましょう。
標準偏差を算出する手順②平均を算出する
STDEV.P関数を使えば一発で標準偏差を算出できますが、平均がどの程度の数値なのかを把握するために、年齢の平均値を先に求めてみましょう。
エクセルで平均を算出する際は、AVERAGE関数を使います。AVERAGE関数とは、引数に指定した数値の平均値を返してくれる関数です。
例えば、 範囲E5からD12までに計算したい数値が含まれている場合、数式バーに=AVERAGE(E5:E12)と入力することで、これらの数値の平均が返ってきます。
まず、F2とF3に算出した平均を出力するための表を作成しましょう。
続いて、F3に「=AVERAGE(」と入力し、年齢が記入されているD3からD10までをドラッグで選択します。選択できたら、Enterキーを押して確定すると、F3に年齢の平均値が出力されます。
今回はわかりやすいように、F3を選択した状態でホームタブの「数値」から「桁区切りスタイル」をクリックして、小数点は切り捨てておきましょう。
標準偏差を算出する手順③標準偏差を算出する
平均が求められたら、標準偏差の算出に移ります。まず、G2に標準偏差を出力するための簡易的な表を作成しておきましょう。
作成した表のG3に「=STDEV.P(」と入力したら、D3からD10までをドラッグで選択し、Enterキーで確定しましょう。すると、年齢の標準偏差が求められて、G3に結果が出力されます。
また、エクセルにはSTDEV.P関数だけでなく、さまざまな便利な関数がデフォルトで用意されています。
以下の記事では、経理業務を効率化できる関数を紹介しているので、こちらもあわせて確認し、日々の業務にぜひ取り入れてみてください。
エクセルの標準偏差をグラフ化する手順
標準偏差の不規則性を視覚的にわかりやすくするためには、表をグラフ化することがおすすめです。
エクセルなら標準偏差に対応したグラフの作成もできるので、その作り方について見ていきましょう。
標準偏差をグラフ化する手順②グラフを作成する
まずは、標準偏差が反映されていないノーマルなグラフを作成します。
グラフには名前と年齢を表示させたいので、名前が記入されているB3からB10をドラッグで選択し、Ctrl（Command）キーを押しながらD3からD10をドラッグで選択してB列とD列を複数選択しましょう。
対象を選択できたら、挿入タブの「グラフ」から「集合縦棒」を選択すると、名前と年齢が反映された縦棒のグラフがワークシート内に挿入されます。
表と被らない見やすい位置にドラッグで移動させておきましょう。
標準偏差をグラフ化する手順②グラフに標準偏差を追加する
グラフが挿入されたら、一度グラフ内の余白部分をクリックしてグラフを選択しましょう。
一度選択することでグラフのデザインタブが開くので、「グラフのレイアウト」内の「グラフ要素を追加」を選択します。
メニューにある「誤差範囲」から「標準偏差」をクリックすることで、縦棒グラフ内に標準偏差の範囲を示したグラフが新たに追加されます。
表からグラフに変換することで、異常値や外れ値の見落としを減らせるため、標準偏差を求める際は、あわせてグラフへの変換も行うようにしましょう。
エクセルで算出した標準偏差の計算が合わないのはなぜ？
エクセルと手計算で算出した標準偏差の数値の計算が合わない場合があります。このような場合に考えられるのが、適切な関数を使用できていないことです。
エクセルで標準偏差を算出する関数は複数あり、それぞれ求め方は異なります。
例えば、STDEV.P関数ではデータに文字列やFALSEが含まれている場合は計算に含めません。しかし、STDEV.PA関数ではデータに文字列やFALSEが含まれている箇所を0として計算します。
そのため、使う関数によって含めてほしい計算がされていなかったり、逆に省いてほしい計算が含まれていたりします。
ほかにも、母集団全体から標準偏差を求めるSTDEV.Pと母集団の一部から標準偏差を求めるSTDEV.S関数でも、最終的な計算結果は異なるため、注意が必要です。
エクセルの関数を効率的に学ぶなら
STDEV.P関数のように、エクセルで算出したいデータがある場合は関数が使用できると業務の効率化につながります。
しかし、関数は理解が難しく独学でマスターするのは難しいでしょう。
そこでおすすめなのがExcel基礎セミナー講習です。Excel基礎セミナー講習なら、VLOOKUPやROUND、COUNTなどさまざまな関数を授業内で学べます。
また、エクセルの作業を自動化させる機能であるマクロや、マクロを実行するために必要なプログラミング言語であるVBAを学びたいなら、Excelマクロ・VBAセミナーもチェックしてみてください。
Excelマクロ・VBAセミナーを受講することで、フォルダ作成やメール送信の自動化ができるため、業務の効率がさらに上がります。
エクセルの基礎は押さえられているという方は、ぜひExcelマクロ・VBAセミナーでスキルアップにチャレンジしてみてはいかがでしょうか。
エクセルの標準偏差についてまとめ
今回は、エクセルで標準偏差を算出する手順を紹介しました。標準偏差を使えば、データの不規則具合を把握できるため、品質や在庫管理などビジネスの場で役立ちます。
通常では複雑な計算が必要になる標準偏差も、エクセルでは関数で簡単に算出可能です。
また、長い構文も覚える必要はなく、数式バーに関数名を途中まで入力すれば、補完機能によって呼び出しができます。
標準偏差の算出方法を押さえて、ぜひ仕事で活用してみてください。
- エクセルを使って転職したい・基本操作を知りたい→Excel基礎セミナー
- エクセルでマクロやVBAを組み業務の負担軽減や効率化を図りたい→Excelマクロ・VBAセミナー
- エクセルの資格が欲しい→MOS対策短期集中講座
こちらから自分に合うエクセルの講座をお選びください。
