vba 等間隔の列に対しての計算

vbaでの等間隔の列に対しての計算について教えてください
1日のデータが19列＊約3000行で数か月分(19列＊日数分の列)並んでおり
各行ごとに19列のデータの内1件のデータ20日分に対しStDevP(標準偏差)
を算出したいのですがうまくいきません
①下記にあるmidbandを算出しているAverageIfの様なコードが作れれば理想的なのですが
StDevPifとかは無く”エクセル 等間隔列 範囲指定”などで検索してみたのですが
他に良い方法が見つからず仕方なくfor next文でセルアドレスをつなげて
（hani=ws2.Cells(2,27),Cells(2,46),Cells(2,65),Cells(2,84),、、、、Cells(2,388),Cells(2,407)、、こんな感じで20日分）
②hani変数にしてsigmaを算出する式に代入して実行してみたのですが
WorksheetFunctionクラスのStDevPプロパティを取得できません　のエラーになってしまいます
構文がどこかおかしのかとセルに書き出して確認し、問題無さそうなので試しにコピーして
変数haniと入れ替えて実行した所、うまくいきました（構文自体は問題なく出来ている？）

出来れば①の様なすっきりした構文で作る方法が有れば理想

無理で有れば現状の②の方法で何が問題なのか（修正方法）

につきましてアドバイスを頂きたくご教示の程よろしくお願いいたします


　i=2　　　　　　'//行番号
　ma = 20 '//算出日数
　retsusuu=19　　　　　　　　　　　　　　　　'//1日分のデータ列数
　hretsu　　　　　　　　　　　　　　　　　　'//算出範囲の先頭列
eretsu = hretsu + retsusuu * ma '//算出範囲の最終列
　owaretsu = hretsu + 6 '////算出データの先頭列

ws2.Activate
’①
midband = WorksheetFunction.AverageIf(ws2.Range(Cells(1, hretsu), Cells(1, eretsu)), ws2.Cells(1, owaretsu), ws2.Range(Cells(i, hretsu), Cells(i, eretsu)))

For BB = 0 To ma
If BB = 0 Then
hani = "ws2.Cells(" & i & "," & owaretsu & ")" & ","
ElseIf BB > 0 And BB < 20 Then
owaretsu = owaretsu + (BB * retsusuu)
hani = hani & "Cells(" & i & "," & owaretsu & ")" & ","
Else
owaretsu = owaretsu + (BB * retsusuu)
hani = hani & "Cells(" & i & "," & owaretsu & ")"
End If
owaretsu = hretsu + 6 '////算出データ列　初期化
Next BB

MsgBox hani　　'//msgboxで確認
ws2.Cells(2, 10).Value = hani　'//セルに書き出して確認
’②
sigma = WorksheetFunction.StDevP(hani)　⇒　プロパティを取得できませんエラーでダメ

No.2 ベストアンサー 回答者： めぐみん_ 回答日時： 2022/05/17 21:32

＞（hani=ws2.Cells(2,27),Cells(2,46),Cells(2,65),Cells(2,84),、、、、Cells(2,388),Cells(2,407)、、こんな感じで20日分）

最初のセルだけ別シートなのかどうかは不明ですけど、UNIONメソッドとRangeのOffsetプロパティでいけるのではないかなと。

https://www.sejuku.net/blog/88187
https://excel-ubara.com/excelvba1/EXCELVBA383.html

この回答へのお礼

早々にご回答ありがとうございます
unionは調べたのですがoffsetとの組み合わせをもう一度調べて
配列のご回答と併せてやってみます
又結果報告させていただきます

お礼日時：2022/05/17 21:43

No.6 回答者： めぐみん_ 回答日時： 2022/05/19 10:22

もし連結した文字列を使用したいのなら、まず区切り文字を , から _ 等に変えておき

sigma = WorksheetFunction.StDevP(Split(hani , "_"))

とSplit関数を使えば _ で区切られた配列にしてくれるかと。
, ですと Cells の中も区切ってしまうのでそれを避ける必要あり。

この回答へのお礼

ご回答ありがとうございます

Split関数を使えば _ で区切られた配列にしてくれる
OFFSETで１９列毎に範囲を移動させる。
必要な範囲をRange型変数にSetステートメントで代入する

この辺がなるほどなので時間が取れる時に試行してご報告させていただきます

お礼日時：2022/05/19 22:39

No.5 回答者： めぐみん_ 回答日時： 2022/05/19 10:02

No.3です。

＞これはaverageifの算出例でしょうか？

はいそうです。

＞hani変数には
＞hani=ws2.Cells(2,27),Cells(2,46),Cells(2,65),、、、

コードを見るとExcelの横着も含めてですけど変数にはセルの値が連結されるだけであり、セルアドレスが入る訳ではないですね。
それに連結するくらいなら最初に

Set r = Range("A2:E2")

のように必要な範囲をRange型変数にSetステートメントで代入するのが宜しいかと。
あとはOFFSETで１９列毎に範囲を移動させる。
もしかしたらUNIONは必要なかったかもです。（最初にセル範囲を固定できるなら）

No.3,4 のイミディエイトウィンドウでの結果（r.Address と rr.Address）を見て頂ければわかるかもですが、最初に設定したセル範囲が移動している又組み込まれている違いに気づいて頂ければ参考になるかなと。

この回答へのお礼

めぐみん　様　tatsumaru77　様

色々、アドバイスありがとうございました
結果的に　めぐみん　様　にアドバイス頂いたunionを使い
下記コードでシンプルにうまくいきました
そもそも、アドレスの文字列を繋げて使おうとした発想が間違っていて
オブジェクト変数として代入しなければいけなかったことが理解出来ました。
独学自己流なので基本的な理解、説明不足が有り、お手数おかけいたしました。
tatsumaru77　様　にご教示頂いた配列も試したのですがなぜかうまくいかず
今回は断念しましたが、いずれ他の場面で再挑戦したいと思います。
ありがとうございました
又何か機会が有りましたら宜しくお願いいたします。

Dim hani As Range

Set hani = ws2.Cells(i, owaretsu)

For BB = 1 To ma - 1
owaretsu = owaretsu + (BB * retsusuu)
Set hani = Union(hani, Cells(i, owaretsu))
owaretsu = foundcell.Offset(0, 6).Column '////当日終値の列　初期化
Next BB

sigma = WorksheetFunction.StDevP(hani)

お礼日時：2022/05/22 13:32

No.4 回答者： めぐみん_ 回答日時： 2022/05/17 22:08

No.3です。

ちょっとミスしました。

Debug.Print rr.Address, WorksheetFunction.AverageIf(rr, "<>", rr) '★　修正

結果：
$A$2:$E$2 3
$A$2:$J$2 5.5
$A$2:$O$2 8

No.3 回答者： めぐみん_ 回答日時： 2022/05/17 22:04

No.2です。

例えば、

Sub try()
Dim r As Range
Dim rr As Range

Set r = Range("A2:E2")

Do

If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If

Debug.Print r.Address, WorksheetFunction.AverageIf(r, "<>", r)

Set r = r.Offset(, 5)

Loop Until WorksheetFunction.Count(r) = 0

End Sub

結果：
$A$2:$E$2 3
$F$2:$J$2 8
$K$2:$O$2 13

この回答へのお礼

回答ありがとうございます
説明がうまくなかったと思うのですが、これはaverageifの算出例でしょうか？
補足させていただくと

sigma = WorksheetFunction.StDevP(hani)⇒　プロパティを取得できませんエラー

この部分（標準偏差算出）がうまく行かなくてはまっております、hani変数には
hani=ws2.Cells(2,27),Cells(2,46),Cells(2,65),、、、
の様に19列おきに２０件のデータから算出させようとしております
何故か変数haniと入れ替えてhaniの全文で実行した所、うまくいきました（アドレスの結合文自体は問題なく出来ている？）
これも、何が間違っているのか（変数だとだめなのか？）が判明しません
もし、何かアドバイスが有りましたらお手すきの時にご教示頂ければありがたいです
unionに関しても配列のご回答内容と併せて時間があるときに試行してみようと思います
又報告させていただきます

お礼日時：2022/05/18 21:56

No.1 回答者： tatsumaru77 回答日時： 2022/05/17 21:02

直接の回答ではありませんが、配列の形式にしてStDevPを呼び出せば、

良いかと思います。
以下、サンプルプログラムです。
case3が今回の参考になるかと。

Option Explicit
Public Sub sample()
Dim sigma As Double
Dim arrV As Variant
Dim arrD() As Double
Dim i As Long

sigma = WorksheetFunction.StDev(100, 88, 90)
Debug.Print "case1", sigma

arrV = Array(100, 88, 90)
sigma = WorksheetFunction.StDev(arrV)
Debug.Print "case2", sigma

For i = 0 To 2
ReDim Preserve arrD(i)
If i = 0 Then
arrD(i) = 100
ElseIf i = 1 Then
arrD(i) = 88
Else
arrD(i) = 90
End If
Next
sigma = WorksheetFunction.StDev(arrD)
Debug.Print "case3", sigma

End Sub

実行結果
case1 6.42910050732864
case2 6.42910050732864
case3 6.42910050732864

この回答へのお礼

早々にご回答ありがとうございます
なんとなく理解できそうです
配列を調べてからやってみようと思います
又結果を報告させていただきます

お礼日時：2022/05/17 21:45