ExcelのCONFIDENCE関数の罠

こんなタイトルにしたら怒られるのでは…と思いつつ。
ExcelではCONFIDENCE関数で信頼区間を求められるらしい…が、別途計算してみたら合わず。
調べてみたら、間違えて使いそうになってたことが分かった。

ExcelのCONFIDENCE関数の仕様

結論から言えば、CONFIDENCE関数は母分散が既知の場合のものらしい。
Microsoftの情報 によれば、CONFIDENCE関数は

=CONFIDENCE(B1,B2,B3) 	
=NORMSINV(1 - B1/2)*B2/SQRT(B3)
B1: 有意水準 e.g. 0.05
B2: 標準偏差(母分散の平方根) e.g. 15
B3: 標本数 e.g. 50

となっている。

母分散が未知の場合の信頼区間

母分散が未知の場合と既知の場合では、信頼区間の計算方法が異なる。
具体的には、以下のページにあるように母分散の代わりに不偏分散を使う。
母平均の信頼区間とは何? Weblio辞書

これをExcelで書くと、

=TINV(B1, B3-1)*B4/SQRT(B3)
B1: 有意水準
B3: 標本数
B4: 不偏標準偏差(不偏分散の平方根)

となる。 TINV(B1)はt分布表から求める値に相当する。 また、B4はSTDEV関数で求められる。

誤用した場合

今日やりそうになった間違い。
簡単に言えば、

=CONFIDENCE(B1, B2, B3)

の結果が、

=TINV(B1, B3)*B2/SQRT(B3)

と同じになるはずだと勘違いした。

どのくらい変わるか計算したところ、次のようになった。
標本数が少ない場合のCONFIDENCE関数の誤用

結局のところ、標本数が十分に大きければ不偏分散と母分散が近くなるので、誤用しても誤差程度にしかならないけどね。

まとめ

CONFIDENCE関数が使えるのは、母分散が分かっているか、もしくは標本数が十分な時だけ。
母分散が分からないときは、CONFIDENCE関数を使わない方法が必要。

個人的に、母分散が分からないことのほうが多いと思うのだけど…。

関連記事


  1. Spring Web MVCのViewでExcelを生成して返す
  2. クエリパラメータを使ってお手軽にGitHubのプルリクを作成する
  3. 他の人には読めない形式でGitHubのSecretsの値を読み出す
  4. GitHub Actionsでプルリクのコメントに複数行テキストを投稿する
  5. GitHub Actionsでエラーの時だけ特定の処理を実行する
  6. Tera Termのマクロで、パスワードを扱う操作を自動化する
  7. HugoテンプレートをOGPやTwitter Cardsに対応する

comments powered by Disqus