[已解决]A列是一组0.47~0.63的随机数据，B列是1和0的随机值，1带便正确，0代表错误，如何算...

lbport · 发表于 2017-3-29 07:48

A列是一组0.47~0.63的随机数据，B列是1和0的随机值，1代表正确，0代表错误，比如
            A             B             C ...
1       0.47             1
2       0.4752       0
3       0.5133       0
4       0.5524       1
:
:
1000  0.62999       0
A列中不同的区间比如A1：A50或A20：A105 它们所对应的B列数值的平均值也会不一样，因为此时B列只有0和1两种数值，即也可以看成是A这个区间的概率，现在假设数据有10000行，我想在A列中取区间N，区间的范围是2500到3500，想问有没有函数或者图表，能分析或求出当选择的空间区域为多少时，B列的平均值，即概率，能达到最大。

最佳答案

月排行榜 / 总排行榜

香川群子

2017-4-1 20:29

代码确实有bug，计算结果是错的。

更正如下：

Sub test()
Dim ar, i&, i1&, m&, m1&, m2&, n&, n1&, r#, s$, t&, t1&, tms#
tms = Timer
m = 10000 '设置数据总行数
ar = [a1].Resize(m, 2)
m1 = m / 10 * 3 '取值个数下限
m2 = m / 10 * 4 '取值个数上限
For n = m1 To m2 '在取值范围内设置取值个数n
t = 0
For i = 1 To n '遍历1-n得到起始n个数据的概率统计值
t = t + ar(i, 2)
Next
t1 = t: If t / n > r Then r = t / n: i1 = 1: n1 = n
For i = 1 To m - n '继续按取值n个进行位移遍历
t = t - ar(i, 2) + ar(i + n, 2) '位移时去掉第一个、加上最后1个，变成新的概率统计值
If t > t1 Then t1 = t: If t / n > r Then r = t / n: i1 = i + 1: n1 = n
Next
DoEvents
Application.StatusBar = Format(Timer - tms, "0.0s ") & Format((n - m1 + 1) / (m2 - m1 + 1), "0.0%")
Next
s = "i = " & i1 & " : n = " & n1 & " : r = " & Format(r, "0.00%")
[d1] = s: [d2] = i1: [d3] = n1
[d4] = "=SUM(B" & i1 & ":B" & i1 + n1 - 1 & ")": [d5] = "=D3/D2"
MsgBox Format(Timer - tms, "0.00s") & vbCr & s
End Sub

复制代码

注意：如果设置m=10万，那么耗时将指数级增加，大约需要运行1个小时。

跳转到最佳答案楼层

tgydslr · 发表于 2017-3-29 09:14

A列对应的b列都有对应值，就是你说的概率，现在你想要的区间的概率值指的是？

lbport · 发表于 2017-3-29 10:09

tgydslr 发表于 2017-3-29 09:14
A列对应的b列都有对应值，就是你说的概率，现在你想要的区间的概率值指的是？

比如如果是要一个N=2500的区间，就是分别算出A1：A2500，A2：2501，A3：A2502....A7501：A10000的对应B列的概率，如果N=3000，则计算A1：A3000....A7001：A10000,对应B的概率，以此类推，因为我不知道计算哪个区间能使对应的B列的概率最大，但我知道我必须要让所选的区间在2500到3500，即总数的三分之一左右，假设一种情况，当我设定N=2800的时候，对应的B501:B3300的概率是55%，这个概率是当N=2800的最大概率了，并且比其他N的所有概率都来的大，则这是我需要的结果，最大概率确定下来是55%，区间也能确定是2800，并且发生在第501行到第3300行，这样我就可以找到A列中第501行，即A501，比如说数值是0.5233，找到第3300行，即A3300，比如说数值是0.5825,则0.5233~0.5825这个区间就是我要的区间

lbport · 发表于 2017-3-29 10:51

没有人知道吗？

tgydslr · 发表于 2017-3-29 11:29

lbport 发表于 2017-3-29 10:51
没有人知道吗？

B501:B3300的概率是指的是这些值的平均？

lbport · 发表于 2017-3-29 11:49

tgydslr 发表于 2017-3-29 11:29
B501:B3300的概率是指的是这些值的平均？

B501：B3300 由于只有1和0 如果求平均值，假设有1000个1，1800个0，则=1000/2800，如果求1出现的概率，不也是=1000/2800，因为只有0跟1，所以我简化了运算，反正1代表正确，0代表错误，这么理解就好了。就当求正确率也行。

tgydslr · 发表于 2017-3-29 14:06

lbport 发表于 2017-3-29 11:49
B501：B3300 由于只有1和0 如果求平均值，假设有1000个1，1800个0，则=1000/2800，如果求1出现的概率， ...

=RIGHT(MAX(--(ROUND(SUBTOTAL(1,OFFSET(B1,,,ROW(2000:10000))),4)&row(2000:10000))),4)

复制代码

lbport · 发表于 2017-3-29 14:35

tgydslr 发表于 2017-3-29 14:06

这公式是什么意思啊？能解释一下吗？我复制到C1后，返回0652，这是？

tgydslr · 发表于 2017-3-29 14:43

本帖最后由 tgydslr 于 2017-3-29 15:13 编辑

lbport 发表于 2017-3-29 14:35
这公式是什么意思啊？能解释一下吗？我复制到C1后，返回0652，这是？

这是个数组公式，需要三键结束才能返回正确结果的
=RIGHT(MAX(--(ROUND(SUBTOTAL(1,OFFSET(B1,,,ROW(2000:10000))),4)&ROW(2000:10000))),4)
OFFSET(B1,,,ROW(2000:10000))说的是从B1开始往下去2000到10000个数
SUBTOTAL(1,OFFSET(B1,,,ROW(2000:10000)))就是对上述的那些组的数分别取平均值
ROUND(SUBTOTAL(1,OFFSET(B1,,,ROW(2000:10000))),4)是为了处理方便对那些平均值四舍五入到小数点后四位
ROUND(SUBTOTAL(1,OFFSET(B1,,,ROW(2000:10000))),4)&ROW(2000:10000)是加上所取得位数，比如取2000位的平均值为0.8571，这个数就写成0.85712000……这是文本，需要转为数字，所以前面加--号
MAX(--(ROUND(SUBTOTAL(1,OFFSET(B1,,,ROW(2000:10000))),4)&ROW(2000:10000)))就是对上述值取最大值（后四位的数不影响前面的值的大小）
最外层就是去这个最大值后四位也就是你要的最大时的N值
刚才发现一点小遗漏，公式修正一下

=MID(MAX(--(TEXT(SUBTOTAL(1,OFFSET(B1,,,ROW(2000:10000))),“0.0000”)&ROW(2000:10000)&1)),7,4)

复制代码

预防取到尾数为0时，整个数位后面的0都不显示而返回错误结果，比如取2000位时，加入平均数是0.8571，这样的数是0.85712000，如果碰巧这个数位上取得最大值，那么最大值就成0.85712了，这样取后四位就不是2000位了，变成5712不返回正确结果。而上述公式不会出现上述情况

lbport · 发表于 2017-3-29 14:53

tgydslr 发表于 2017-3-29 14:43
这是个数组公式，需要三键结束才能返回正确结果的

三键结束后要向下填充吗？三键后返回的结果是5959，这代表什么意思呢？

		自动登录	找回密码
密码			注册

[已解决]A列是一组0.47~0.63的随机数据，B列是1和0的随机值，1带便正确，0代表错误，如何算...

相关帖子