「統計学関連なんでもあり」の過去ログ--- 048

No.22839　VBAでの購買間隔の平均を計算　　【波音】　2019/09/20(Fri) 12:27

業務上の事情があってExcel VBAで「購買月間隔の平均」を計算するユーザー定義関数を作っています（作りました）。
自分でチェックしたところ，正しく動作はしているようですが。。。
記憶をたよりに書いていることもあり，何かもう少しスマートに書ける気がするようなしないような。。。

という状況ですので，もう少しスマートに書く方法があればご教示いただきたいです。

やりたいことは以下のようなものです。

例えば，私が毎月購買していれば4月〜3月までの12セルすべてに「1」が入ります。
もし購買していない月があれば「空白」が入ります。
仮に4,5,6；9,10；3に購買しなかった場合，空白は3, 2, 1ということになるので，その平均は(3 + 2 + 1) / 3という計算です。

この計算結果は2.00となりますが，意味としては「この人は平均的に2ヶ月間隔で買っていない(2ヶ月間隔でブランクがある)ということ。

VBAコード

Option Explicit

' 指定したセル範囲において，入力値と入力値の間の空白をカウントし，
' 　空白数の平均を計算して返す関数

' ID×日付の購買頻度クロス集計表で，IDごとの購買間隔の日数の平均を求めるために作った関数

Function betweenAve(datRng As Range)

Dim dat As Variant                  ' 関数の引数で取得したアドレスにある数値を格納する配列
Dim n As Integer, i As Integer      ' 配列の長さn，forの繰り返し用i

Dim blankCount As Integer           ' 空白セルをカウントして格納していくハコ
Dim sumN As Integer                 ' 購買日間隔がいくつかるかカウントして格納していくハコ

Dim mySum As Double, ans As Double  ' 空白セルを累計して格納するハコ

' まずは引数で指定されたセル範囲のアドレスから，セルに入力されている値をdatへ格納
' 二次元配列として格納されるので，行ベクトルの長さを取得
dat = Range(datRng.Address).Value
n = UBound(dat, 2)

' 各種変数を初期化しておく
blankCount = 0
sumN = 0
mySum = 0

' セルの値を1つずつチェックしていき，空白があればblankCountに「1」を格納していく
' 　それをmySumへ累計していく
For i = 1 To n
    If dat(1, i) = "" Then              ' i番目のセルが空白がどうかチェック
        blankCount = blankCount + 1     ' 空白なら「1」を代入
    Else
        If i <> 1 Then                  ' 空白でない場合，そのセルが最初のセルかどうかチェック
            If dat(1, i - 1) = 0 Then   ' 最初のセルでなければ，1つ前のセルをチェックして空白なら，
                sumN = sumN + 1         ' 　sumNに+1カウント
            End If
        Else
            '何も処理しない
        End If
        
        mySum = mySum + blankCount      ' mySumへ空白の数を累計していく
        blankCount = 0                  ' 1ループの処理が終わるたびにblankCountを初期化する
    End If
Next i

' 最終セルが空白の場合，ループ内で数がカウントされないのでループ後にカウントする
mySum = mySum + blankCount

' 最終セルが空白の場合，購買日の間隔数もカウントされないのでループ後にカウントする
If dat(1, n) = "" Then
    sumN = sumN + 1
End If

' 購買日数間隔の平均を計算
 ans = mySum / sumN

' 戻り値
 betweenAve = ans

End Function

No.22840　Re: VBAでの購買間隔の平均を計算　　【波音】　2019/09/20(Fri) 12:29

Excelのワークシートのスクリーンショットも添付します。
このようなデータイメージです。

No.22883　Re: VBAでの購買間隔の平均を計算　　【ts】　2020/01/10(Fri) 18:02

Function 空白期間の平均(対象範囲 As Range)

Dim 前, 今, 分母

分母 = 0

前 = "最初なので，前はありません"

'--------------------------------

For Each 今 In 対象範囲

If 今 = "" And 前 <> "" Then

'空白期間の始まりだから

分母 = 分母 + 1

End If

前 = 今

Next
'--------------------------------

空白間隔の平均 = WorksheetFunction.CountBlank(対象範囲) / 分母

End Function

No.22884　Re: VBAでの購買間隔の平均を計算　　【aoki】　2020/01/10(Fri) 19:08

R だと，rle() を使えば簡単です。
data = c(
"111111111111",
"000110011110",
"110011001100",
"111111000000",
"101010101010")

for (str in data) {
	str.vec = unlist(strsplit(str, ""))
	result = rle(str.vec)
	result = result$length[result$values == 0]
	if (length(result) == 0) {
		print(0)
	} else {
		print(mean(result))
	}
}
結果
[1] 0
[1] 2
[1] 2
[1] 6
[1] 1
rle もソースを見れば簡単な関数です。余計なものを除けば以下のごとし。
rle2 = function(x) {
	x = unlist(strsplit(str, ""))
	y = x[-1] != x[-n]
	i = c(which(y), n)
	lengths = diff(c(0, i))
	values = x[i]
	return(list(lengths=lengths, values=values))
}
両方の言語を知っていれば，移植もそう難しくはないのでは？