【Excel】変動係数
$n$ 個のデータ $x_1,\:x_2,\:\cdots,\:x_N$ があったとき、標準偏差 $\sigma$ は
\[\sigma=\sqrt{V}=\sqrt{\frac{\sum_{i=1}^{N}(x_i-m)^2}{n}}\tag{1}\]
で与えられます。ここで $V$ は分散、$m$ は平均です。標準偏差はデータのばらつき具合を表しますが、平均値や単位の異なるデータ同士で標準偏差を比較しても意味はありません（式の中に平均値からの差分が含まれているので、平均値の大きなデータの標準偏差は大きくなります）。
たとえば、ある集団の身長の標準偏差が 12 cm, 体重の標準偏差が 9 kg だったとしても、身長のほうが体重よりばらつきが多いとは言えないのです。そこで標準偏差を平均で割って無次元の量を定義します。
\[CV=\frac{\sigma}{m}\tag{2}\]
この量を 変動係数（Coefficient of variation）とよび、異なる平均値や単位をもつデータのばらつきを比較するために用いられます。たとえば
平均身長 170 cm 標準偏差 12 cm
平均体重 60 kg 標準偏差 9 kg
というデータがあった場合、変動係数はそれぞれ
身長の変動係数 = 12/170 = 0.07
体重の変動係数 = 9/60 = 0.15
となるので、体重のほうがばらつきが大きいことになります。
Excel 関数 を使って 変動係数 を計算してみましょう。あるクラスから無作為に 10 名を選んで身長と体重のデータを記録して、次のようなデータが得られたと仮定します。
| 番号 | 身長 [cm] | 体重 [kg] |
|---|---|---|
| 1 | 161.24 | 59.99 |
| 2 | 162.67 | 61.48 |
| 3 | 157.99 | 55.86 |
| 4 | 172.04 | 64.74 |
| 5 | 169.97 | 61.02 |
| 6 | 168.4 | 59.47 |
| 7 | 179.52 | 83.33 |
| 8 | 152.22 | 55.85 |
| 9 | 166.18 | 59.7 |
| 10 | 153.19 | 58.19 |
Excel で分母が $n$ の標準偏差（母集団標準偏差）を計算する関数は
=STDEV.P(数値1,[数値2, ...])
です。Excel2007 以前のバージョンでは STDEVP関数を使用してください。一般には引数に範囲を指定して使用します。平均値は AVERAGE関数で計算できるので、D14 セルには
=STDEV.P(C3:C12)/AVERAGE(C3:C12)
と入力すると身長の変動係数 0.0496 が得られます。
同様に D15 セルには
=STDEV.P(D3:D12)/AVERAGE(D3:D12)
と入力して 0.1218 を得るので、体重のほうがデータのばらつきが大きいことがわかります。
あるいは、次のように VBA の標準モジュールに変動係数の値を返す関数を用意しておけば、変動係数の定義式を忘れても簡単に呼び出すことができます。
'[VBA]変動係数を計算する関数 Function CV(x As Range) CV = WorksheetFunction.StDev_P(x) / WorksheetFunction.Average(x) End Function
上のコードを標準モジュールに貼り付けて、適当なセルに
=CV(C3:C12)
と入力すれば、変動係数 0.0496 が得られます。ちなみに、変動係数は百分率で表されることもあり、その場合の定義式は
\[CV=\frac{\sigma}{m}\times 100\tag{3}\]
となります。
エクセルや数学に関するコメントをお寄せください
ChatGPTに変動係数について質問しました
[Q] 変動係数とは何ですか？
[ChatGPT] 変動係数は、データのばらつきを平均値に対して相対的に表した指標です。標準偏差を平均値で割ったもので、以下の式で表されます。
変動係数 = (標準偏差 ÷ 平均値) × 100%
変動係数が大きい場合、データのばらつきが大きくなります。一方、変動係数が小さい場合、データのばらつきが小さくなります。この指標は、異なる平均値や単位を持つデータを比較する際に役立ちます。例えば、平均値が異なる二つのデータの標準偏差を比較する場合、変動係数を計算することで、より公平に比較することができます。また、変動係数は統計解析や品質管理などの分野で広く使用されます。
[Q] 変動係数をExcelで計算する方法を教えてください。
[ChatGPT] データを入力します。平均値を求めます。平均値を求めるには、AVERAGE関数を使用します。例えば、データがA1からA10までのセルに入力されている場合、以下のようにAVERAGE関数を入力します。
=AVERAGE(A1:A10)
標準偏差を求めます。標準偏差を求めるには、STDEV関数を使用します。例えば、データがA1からA10までのセルに入力されている場合、以下のようにSTDEV関数を入力します。
=STDEV(A1:A10)
変動係数を求めます。変動係数を求めるには、標準偏差を平均値で割り、100をかけます。例えば、平均値がB1セルにあり、標準偏差がB2セルにある場合、以下のように変動係数を計算します。
=B2/B1*100%
これらの手順に従って、Excelで変動係数を計算することができます。
【AI連載小説】数学のリズム、エクセルの旋律(13)
「エクセルを使って変動係数を計算します」
真琴：（テーブルの前で座りながら） 今回は変動係数の計算方法について話し合いましょう。変動係数はデータのばらつきを比較するのに便利な指標だよ。
研伸：変動係数か。これは標準偏差と平均を使って計算するんだっけ？
隆治：そうだね。変動係数は標準偏差を平均で割ったものだから、ばらつきを相対的に表現できる。
月子：計算式は 変動係数 = (標準偏差 / 平均) * 100 って感じかな。
真琴：そうそう。変動係数はパーセンテージで表されるから、異なる単位を持つデータを比較するときに有用だよ。
研伸：たとえば、A列に身長（cm）、B列に体重（kg）が入っているとして、身長の変動係数と体重の変動係数を比較するっていうのはアリかもしれないね。
隆治：でも、変動係数には平均がゼロの場合や、分母がゼロの場合に気をつけないといけないんだよね。
月子：その通り。分母がゼロだと変動係数は未定義になっちゃうから、データに注意して計算することが大切だよ。
真琴：他にも変動係数の使いどころや、留意すべきポイントがあれば共有しておこう。
研伸：変動係数は相対的なばらつきを示すから、異なる範囲や単位のデータ同士を比較するときに重宝するよね。
隆治：あと、外れ値の影響が比較的少ないところもメリットだな。
月子：それに変動係数は正規化されているから、データのスケールが違っても比較しやすい。
真琴：良い点がいくつか挙がったね。これをサークルメンバーに伝えて、データ解析の幅を広げていこう。