[已解决]自定义函数遇到困难

sunshinegg

想自定义一个函数
函数内容是 stdev/average

我写的内容如下：
Function cv(arr)
cv = WorksheetFunction.StDev(arr) / WorksheetFunction.Average(arr)
End Function

结果：
如图1，如果选区是连续的。可以工作



如图2，如果选区是分开的。会出错

最佳答案

月排行榜 / 总排行榜

香川群子

2014-3-13 18:36

也许这样更准确，不容易错：

1. Function f(ParamArray Nums())
   
2.     For i = 0 To UBound(Nums)
   
3.         k = k + Nums(i).Count '首先统计所有区域中单元格总数量k
   
4.     Next
   
5.     ReDim a(1 To k)
   
6.     For i = 0 To UBound(Nums)
   
7.         For j = 1 To Nums(i).Count
   
8.             a(k) = Val(Nums(i).Cells(j))
   
9.             k = k - 1
   
10.         Next
    
11.     Next
    
12.     f = WorksheetFunction.StDev(a) / WorksheetFunction.Average(a)
    
13. End Function
    

复制代码

跳转到最佳答案楼层

独奏

这样试试

1. Function cv(arr, brr)
   
2. cv = WorksheetFunction.StDev(arr) / WorksheetFunction.Average(brr)
   
3. End Function
   

复制代码

sunshinegg

独奏 发表于 2014-3-12 18:00
这样试试

谢谢你的帮助
不过，这样改还是不太行
第一，如果我以后要计算3个分开的选区，这样又不行了
第二，这样计算2个分开的选区是行了，但是只计算一个选区又不行了

总之，这样改太不灵活了。 我想要的效果是和excel自带的average函数一样，可进可退，选一个，选二个，选几个都可以

独奏

一个区域也行啊，选两次区域就好啦，不过你要多区域的我就无能为力了

香川群子

sunshinegg 发表于 2014-3-13 11:12
谢谢你的帮助
不过，这样改还是不太行
第一，如果我以后要计算3个分开的选区，这样又不行了

选一个、选二个、选三个参数自由这一点，我可以帮你

但是你的运算规则呢？

如果有arr(1)、arr（2）、……arr（n） 这样n个区域，你的计算方法是什么样的呢？

For i = 1 to n
  cv = cv + WorksheetFunction.StDev(arr(i)) / WorksheetFunction.Average(arr(i))
Next

是这样子么？
还是会变成：
For i = 1 to n - 1
  cv = cv + WorksheetFunction.StDev(arr(i)) / WorksheetFunction.Average(arr(i+1))
Next

sunshinegg

香川群子 发表于 2014-3-13 14:10
选一个、选二个、选三个参数自由这一点，我可以帮你

但是你的运算规则呢？

运算规则就是将 所有选中的单元格的标准差stdev, 除以所有选中的单元格的平均数average



以上图为例，cv=stdev(a1:a4,c1:c4)/average(a1:a4,c1:c4)

香川群子

1. Function f(ParamArray Nums())
   
2.     ReDim a(1 To (1 + UBound(Nums)) * Nums(0).Count)
   
3.     For i = 0 To UBound(Nums)
   
4.         For j = 1 To Nums(i).Count
   
5.             k = k + 1
   
6.             a(k) = Val(Nums(i).Cells(j))
   
7.         Next
   
8.     Next
   
9.     f = WorksheetFunction.StDev(a) / WorksheetFunction.Average(a)
   
10. End Function
    

复制代码

使用参数数组即可。
但为了计算，要把数据先合并到一个数组中去。

香川群子

也许这样更准确，不容易错：

1. Function f(ParamArray Nums())
   
2.     For i = 0 To UBound(Nums)
   
3.         k = k + Nums(i).Count '首先统计所有区域中单元格总数量k
   
4.     Next
   
5.     ReDim a(1 To k)
   
6.     For i = 0 To UBound(Nums)
   
7.         For j = 1 To Nums(i).Count
   
8.             a(k) = Val(Nums(i).Cells(j))
   
9.             k = k - 1
   
10.         Next
    
11.     Next
    
12.     f = WorksheetFunction.StDev(a) / WorksheetFunction.Average(a)
    
13. End Function
    

复制代码

香川群子

补充：我在数据读入数组的过程中，为防止错误直接用了a(k) = Val(Nums(i).Cells(j))

但这样做可能会把非数值内容转换为=0，造成平均值计算错误。

所以，或许应该改成：
If IsNumeric(Nums(i).Cells(j)) Then a(k) = Nums(i).Cells(j)

请楼主自己判断。