ExcelVBAでの標準偏差の求め方

質問

ExcelVBAでの標準偏差の求め方

使用ソフト：Excel2003

ExcelVBAで標準偏差を求めたいのですが、
求めたいデータが複数ファイル(最大31ファイル)に渡り、
また全ての行を一つのファイルにまとめようとすると9万行以上になります。
一ファイルあたりは3000行前後です。

一つのファイルに全てのデータがあれば
下記のようにStDev関数を使うのですが、
今回は複数ファイルに渡るので使えません。

Application.WorksheetFunction.StDev(.Range(.Cells(1, 1), .Cells(Y, 1)))

また、1つのファイルにまとめるにもExcel2003なので、
65536行までしか行がなくまとめられません。
このような場合、どのように標準偏差を求めればいいのでしょうか？

出来るだけ速度は犠牲にしたくはありませんが、
速度以前に実装すら出来ません…。
どうか先達の皆様、お知恵をお貸しください。

（同じデータに対して平均、最大値も求めていますが、
　それは元ファイルから一つ一つのデータファイルを開いて、
　WorksheetFunctionのSum関数、Max関数を使用して最後に集計、計算をしています）

回答No.2

Wendy02

コードが読めるようでしたら、このようにすれば出来るはずです。
たぶん、数式そのものでも、それぞれの範囲を、引数にすれば取れるはずです。ただ、マクロなら、このようにして、関数を利用しなくても出来るというだけです。

'サンプル
'標準モジュール
Private Ar() As Variant '先頭に置く
Sub GetStndDevP()
　Dim i As Long, cnt As Long
　Dim rng As Range
　Dim buf As Variant
　Dim dMax As Double
　Dim dTotal As Double
　Dim dDevTotal As Double
　Dim dAver As Double
　Dim ret As Double
　Erase Ar
　dMax = -10 ^ 10
　With Worksheets("Sheet1")
　　Set rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
　End With
　Call MakingArray(rng)
　With Worksheets("Sheet2")
　　Set rng = .Range("B1", .Cells(Rows.Count, 2).End(xlUp))
　End With
　Call MakingArray(rng)

　'標準偏差の計算
　For i = LBound(Ar) To UBound(Ar)
　　dTotal = dTotal + Ar(i)
　　If Ar(i) > dMax Then
　　　 dMax = Ar(i)
　　End If
　Next i
　cnt = i
　dAver = dTotal / cnt
　For i = LBound(Ar) To UBound(Ar)
　　dDevTotal = dDevTotal + ((Ar(i) - dAver)) ^ 2
　Next i
　ret = (dDevTotal / cnt) ^ (1 / 2)
　'メッセージボックス
　MsgBox "合計：　" & dTotal & vbCrLf & _
　　　　 "最大値:　" & dMax & vbCrLf & _
　　　　 "標準偏差：　" & ret
End Sub
Sub MakingArray(rng As Range)
Dim n As Long, m As Long
Dim i As Long, j As Long
　m = rng.Rows.Count
　On Error Resume Next
　n = UBound(Ar) + 1
　On Error GoTo 0
　ReDim Preserve Ar(m + n - 1)
　For i = 1 To m
　　Ar(n + i - 1) = rng.Cells(i, 1).Value
　Next
End Sub

回答No.1

noname#119360

一つのワークシートの９万個のセルそれぞれに外部ファイルのワークシートの値を参照、あるいはコピペしてから偏差値をもとめればいいのでは？