VBAを用いて条件付きの平均値、標準偏差を求めたいです（Ecxel 2016使用）

お世話になります。
ExcelでのVBAを教えてください

A列にデータ、B列にコメントがある表が有ります。
B列のコメントが未入力のA列のデータを用いてC列に平均値と標準偏差を出したいと考えています
下記の様にやってみたのですが、コメント入力されているデータも使用されてしまいます。
どこが違うのかご教授いただけますでしょうか
よろしくお願いします。

Fro i=1 to Cells(Row.Count,"A").End(xlUp).Row
If Cells(i,"B")<>"" then
Range("C1")=WorksheetFunction.Round(WorksheetFunction.Average(Range(Cells(1,"A"),Cells(i,"A"))),2)
Range("C2")=WorksheetFunction.Round(WorksheetFunction.StDev(Range(Cells(1,"A"),Cells(i,"A"))),2)

No.3 ベストアンサー 回答者： Pにゃん 回答日時： 2018/06/20 00:24

> どこが違うのか

というご質問ですので、まず、文法エラーから。
1行目FroはForの打ち間違いですよね。
また、Row.Countではなく、正しくはRows.Countです。

このコードを実行すると「対応するEnd If」がありませんとか、
「Next」がありませんとか、Excelに怒られます。

それらを追加して、実行しても、i=1の時、4行目でエラーが出ます。
「WorksheetFunctionクラスのStDevプロパティを取得できません」
といって止まりました。この場合、データが１個しかないので、
標準偏差の計算は無理があるわけです。

さらに、論理的な間違いとして、
If Cells(i,"B")<>"" then　の部分。

これは、B列のコメントが未入力「でないならば」
という意味になります。意味が逆です。
コメント入力されているデータも使用されてしまうとしたら
原因はこれでしょう。
というか、それ以前にまともに動かないはずなんですが。

そもそも、StDevで標準偏差を求めるなら、
for文でループさせる必要はありません。
ワークシート関数の機能を使って処理を完遂させるのか、
ループや条件分岐などの制御文を使って目的を果たすのか、
どちらかにしましょう。

Average や StDev を使うならば、めぐみんさんの回答が非常にスマートで
高速に処理できるかと思いますので、ありがたくパクりましょう(笑)。
ただ、「B列のコメントが未入力」の場合を処理したいので
xlCellTypeConstants ではなく、xlCellTypeBlanks を指定して
以下のように書かれるとご希望通りの動作になるかと。

Sub Test1()
Dim r As Range
Set r = Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeBlanks).Offset(, -1)
Range("C1") = WorksheetFunction.Round(WorksheetFunction.Average(r), 2)
Range("C2") = WorksheetFunction.Round(WorksheetFunction.StDev(r), 2)
Set r = Nothing
End Sub


for文 や IF文を使って制御するのが質問者様の意図ならば、
以下のような書き方も、わかりやすくて良いと思います。

Sub Test2()
Dim m As Long
Dim n As Long
Dim i As Long
Dim S As Double
Dim st As Double
Dim Heikin As Double

n = Cells(Rows.Count, "A").End(xlUp).Row '--- データ行のカウント

'--- 平均値を先に求める
For i = 1 To n
If Range("B" & i) = "" Then '--- ｺﾒﾝﾄが未入力なら
m = m + 1 　　'--- その数をｶｳﾝﾄして
S = S + Range("A" & i) '--- 合計して
End If
Next
Heikin = S / m
Range("C1") = WorksheetFunction.Round(Heikin, 2) '--- 平均値を出力。

'--- 標準偏差を算出
m = 0 '--- 変数を再使用するのでﾘｾｯﾄ
S = 0
For i = 1 To n
If Range("B" & i) = "" Then '--- ｺﾒﾝﾄが未入力なら
m = m + 1 　　'--- ｶｳﾝﾄして
S = S + (Range("A" & i) - Heikin) ^ 2 '--- 偏差を二乗して合計
End If
Next
st = Sqr(S / (m - 1)) 　　 '--- (m-1)で割って平方根を取る←括弧を忘れると計算がおかしくなる
Range("C2") = WorksheetFunction.Round(st, 2) '--- 標準偏差を出力。

End Sub


回答文を考えていたら出遅れた上に、ループによる回答が
思いっきりカブってしまいました(涙)。

WindFallerさんの回答はさりげなく、最初からポカよけにTrim関数を
噛ませてあるところが丁寧ですよね。
B列に、半角または全角のスペースなど、見えない文字が入っていると、
「B列のコメントが未入力」という条件からはずれますので、その時には
こういう処理が必要になるわけですが、私は手抜きしました(汗)。

ちなみに標準偏差といえば、標本数をNとして偏差平方和を(N-1)で割って
平方根を取ったものと長年理解していました。
母平均は真の値が通常は不明なので、標準偏差の計算には
サンプリングによる標本の平均値を使わざるを得ず、その分、
統計上、自由度が１減る。なので分母をマイナス１して補正する。
．．．と教わりました。

標本数Nを充分大きくとれれば問題は少ないはずですが､通常は経済的、
時間的、物理的にサンプリングの規模はかなり制限されます。
なので私の場合、現場ではエイヤっと、N-1で統一して運用しましたが、
これによる問題は特に発生しませんでした。

前処理の精度とか、測定のたびに散る値、そう多くない標本数、
製法由来のノイズなど、その他の気になる要素もありまして(笑)

この回答へのお礼

お礼が遅くなり申し訳ありません
また、色々とポカのご指摘ありがとうございます。

また、例をいろいろと教えていただきありがとうございます。
参考にさせていただき、やってみたいと思います

ありがとうございました

お礼日時：2018/06/25 22:29

No.2 回答者： WindFaller 回答日時： 2018/06/19 19:55

こんばんは。

ワークシート関数を使わないで、出してみました。
標準偏差と、不偏標準偏差とは、間違えないほうがよいです。
私は、一度、お客さんの手に渡ってから、検証したら間違いに気がついたことがあります。一般的には、StdDev.P 側のはずです。
平均値は、AverageIf がありますが、StdDev には、条件検索する関数はないようです。
参考にはならないとは思いますが、私は、時々、こういうことで、関数の成り立ちを調べています。

'//
Sub TestStatic1()
Dim i As Long, j As Long, k As Long, m As Long
Dim dblSum As Double
Dim dblDev As Double
Dim avr As Double
Dim dblStdDevP As Double
Dim dblStdDev As Double
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
　If Trim(Cells(i, "B").Value) <> "" Then
　 dblSum = dblSum + Cells(i, "A").Value
　 j = j + 1
　End If
Next i
avr = dblSum / j '平均値

For k = 1 To LastRow
　If Trim(Cells(k, "B").Value) <> "" Then
　 dblDev = dblDev + (Cells(k, "A").Value - avr) ^ 2
　 m = m + 1
　End If
Next k
dblStdDevP = Sqr(dblDev / m) '標準偏差
dblStdDev = Sqr(dblDev / (m - 1)) '不偏標準偏差
'出力
Range("C3").Value = Int(avr * 100 + 0.5) / 100　'平均値
Range("C4").Value = Int(dblStdDevP * 100 + 0.5) / 100　'標準偏差
Range("C5").Value = Int(dblStdDev * 100 + 0.5) / 100　'不偏標準偏差

End Sub

この回答へのお礼

お礼が遅くなり申し訳ありません
また、関数の過ちなどご指摘ありがとうございます。
参考にさせていただきやってみたいと思います

ありがとうございました

お礼日時：2018/06/25 22:16

No.1 回答者： めぐみん_ 回答日時： 2018/06/19 08:07

ようするにB列のセルが空白でない所の同じ行のA列の値が欲しいってなら、

Sub megu()
Dim r As Range

Set r = Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(, -1)
Range("C1") = WorksheetFunction.Round(WorksheetFunction.Average(r), 2)
Range("C2") = WorksheetFunction.Round(WorksheetFunction.StDev(r), 2)

Set r = Nothing
End Sub

こんな感じでは如何？

この回答へのお礼

お礼が遅くなりすみません
参考にさせていただき、やってみます
シンプルで本当に助かります。
ありがとうございました

お礼日時：2018/06/25 22:15