間違いをご指摘ください。

選択範囲の標準偏差の計算を求めようとと以下のマクロを作ってみました。

Sub test()
Dim Dsum As Variant
Dim Msum As Variant
Dim cnt As Integer
Dim myi As Range
cnt = 0
Dsum = 0
Msum = 0
For Each myi In selection
If myi.Value <> "" Then
Msum = Msum + myi.Value
Dsum = Dsum + myi.Value ^ 2
cnt = cnt + 1
End If
Next
If cnt <> 0 Then
Cells(11,1) = Msum / cnt          '平均
Cells(11,1).NumberFormatLocal = "0.00_ "
If cnt > 1 Then
Cells(12,1) = ((Dsum * cnt - Msum ^ 2) / (cnt * (cnt - 1))) ^ 0.5  ’標準偏差
Cells(12,1).NumberFormatLocal = "0.00_ "
Else
Cells(12,1) = 0
End If
End If
End Sub


8.35, 8.35, 8.35, 8.35, 8.35, 8.35, 8.35, 8.35
と入力された8個のセルを選択しマクロを実行すると標準偏差で

実行時エラー '5':
プロシージャの呼び出し、または引数が不正です。

と出てきてしまします。
原因・間違いをご指摘お願いします。
標準偏差の計算式が間違っているのでしょうか?

回答の条件
  • 1人2回まで
  • 登録:2009/06/11 23:20:52
  • 終了:2009/06/13 22:55:58

回答(2件)

id:airplant No.1

airplant回答回数220ベストアンサー獲得回数492009/06/12 00:55:10

ポイント35pt

例題の数値では、分散が0になっています。

0の場合でも、Excelの内部処理の誤差で(Dsum * cnt - Msum ^ 2)がマイナスになることがあります。平方根が求められないエラーですので、絶対値にすれば大丈夫です。

Cells(12,1) = ((Dsum * cnt - Msum ^ 2) / (cnt * (cnt - 1))) ^ 0.5
' ↓
Cells(12,1) = (abs(Dsum * cnt - Msum ^ 2) / (cnt * (cnt - 1))) ^ 0.5

ちなみに、プログラム変更前を見ると、次の値でした。

? Dsum * cnt - Msum ^ 2

 -9.09494701772928E-13

蛇足ですが、標準偏差はマクロを組まなくても、関数STDEVAやピボットテーブルで簡単に求めることができます。

id:zuguimo

ありがとうございます。今回マクロを使う必要からworksheetfunktion.stdevも使いませんでした。

2009/06/12 07:22:07
id:taknt No.2

きゃづみぃ回答回数13537ベストアンサー獲得回数11982009/06/12 07:03:40

ポイント35pt

Cells(12, 1) = Abs((Dsum * cnt - Msum ^ 2) / (cnt * (cnt - 1))) ^ 0.5 '標準偏差


平方根( ^ 0.5 ) をとる場合、0以上じゃないとエラーになります。

計算結果は マイナスとなってしまうので、Absでマイナスをとるか

途中の計算で マイナスにならないようにするかのどちらかですね。

標準偏差を 求めるには  STDEVPというワークシート関数でもできます。

http://www.pursue.ne.jp/Document_xls/xls0023.htm

id:zuguimo

Dsum * cnt - Msum ^ 2がマイナスになる理由がわかりませんが(コンピューター計算の都合上でしょうが)マクロの答えと関数の答えが微妙に違わないか心配ですが。とにかくありがとうございました。

2009/06/12 07:39:09
  • id:tdoi
    今、手元に確認環境がないので、コメントで。

    Cells(12,1) = ((Dsum * cnt - Msum ^ 2) / (cnt * (cnt - 1))) ^ (1/2)


    でどうです?

    たぶん、0.5の「.」の解釈の問題だと思います。
  • id:kn1967
    (1/2) でも同じエラーになりますよ。

    原因は他にありますし、解決策も複数あるのですけど
    回答がついているのでオープンされるまで待ちましょうか・・・。
  • id:airplant
    >今回マクロを使う必要からworksheetfunktion.stdevも使いませんでした。
    ここの意味が良くわからなかったのですが、セルを選択して計算結果(標準偏差)をどこかのセルへ入れる場合でもWorksheetFunctionは使えます。

    ちなみに使った場合には、次のように非常に短いステップになります(データの個数には30個という制限あり)。

    Sub test2()
      Cells(11, 1) = WorksheetFunction.Average(Selection)
      Cells(12, 1) = WorksheetFunction.StDev(Selection)
      Range(Cells(11, 1), Cells(12, 1)).NumberFormatLocal = "0.00_ "
    End Sub
  • id:zuguimo
    今回は範囲内のデータを加工した後、標準偏差を計算する必要があったのでWorksheetFunctionは使いづらかったのです。例えば、2, 4, 8, 16, 32とあった場合、それぞれ1, 2, 3, 4, 5と変換して考える必要がありました。
  • id:airplant
    了解しました。
    なお、vba中の数値や配列もWorksheetFunctionで利用できますので、サンプルを掲載しておきます(2が底のLogにしてあります)。

    Sub test3()
      Dim vVal() As Variant
      Dim lRow As Long, iCol As Integer
      
      vVal() = Selection
      For lRow = 1 To Selection.Rows.Count
        For iCol = 1 To Selection.Columns.Count
          vVal(lRow, iCol) = Log(vVal(lRow, iCol)) / Log(2)
        Next
      Next
      
      Cells(11, 1) = WorksheetFunction.Average(vVal)
      Cells(12, 1) = WorksheetFunction.StDev(vVal)
      Range(Cells(11, 1), Cells(12, 1)).NumberFormatLocal = "0.00_ "
    End Sub
  • id:zuguimo
    終了した後もいろいろとありがとうございます。
    配列は苦手ですので後でゆっくり見てみます。
    ありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません