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))
}
両方の言語を知っていれば,移植もそう難しくはないのでは?

● 「統計学関連なんでもあり」の過去ログ--- 048 の目次へジャンプ
● 「統計学関連なんでもあり」の目次へジャンプ
● 直前のページへ戻る