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

Question

お世話になります。
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)

Pにゃん · Accepted Answer

> どこが違うのか

というご質問ですので、まず、文法エラーから。
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で統一して運用しましたが、
これによる問題は特に発生しませんでした。

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

WindFaller · Answer

こんばんは。ワークシート関数を使わないで、出してみました。標準偏差と、不偏標準偏差とは、間違えないほうがよいです。私は、一度、お客さんの手に渡ってから、検証したら間違いに気がついたことがあります。一般的には、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

めぐみん_ · Answer

ようするに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

こんな感じでは如何？

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

> どこが違うのか

こんばんは。

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

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング