Function snxdavg(snxd1#, snxd1#, xs#) As Variant
Dim avg#
avg = Application.Round(Average(snxd1, snxd1), xs + 1)
Select Case xs.Value
Case 0
snxdavg = Application.IIf(RoundUp(avg * 2, 0) = avg * 2, IIf(Application.Mod(Round(avg, 0), 2) = 1, RoundDown(avg, 0), RoundUp(avg, 0)), Round(avg, 0))
Case 1
snxdavg = Application.IIf(RoundUp(avg * 20, 0) = avg * 20, IIf(Application.Mod(Round(avg * 10, 0), 2) = 1, RoundDown(avg * 10, 0) / 10, RoundUp(avg * 10, 0) / 10), Round(avg * 10, 0) / 10)
Case 2
snxdavg = Application.IIf(RoundUp(avg * 200, 0) = avg * 200, IIf(Application.Mod(Round(avg * 100, 0), 2) = 1, RoundDown(avg * 100, 0) / 100, RoundUp(avg * 100, 0) / 100), Round(avg * 100, 0) / 100)
Case 3
snxdavg = Application.IIf(RoundUp(avg * 2000, 0) = avg * 2000, IIf(Application.Mod(Round(avg * 1000, 0), 2) = 1, RoundDown(avg * 1000, 0) / 1000, RoundUp(avg * 1000, 0) / 1000), Round(avg * 1000, 0) / 1000)
End Select
End Function
本帖最后由 大灰狼1976 于 2022-4-14 10:38 编辑
参数名称有重复;工作表函数使用太多,而且与VBA函数使用混乱,iif不是工作表函数却被加了Application.,round、rounddown、roundup是工作表函数,有的加了Application.,有的没加,mod虽然是工作表函数,但直接用Application.mod是错误的,可以直接使用VBA的mod,例如 if 10 mod 3 = 1 then...
再说一句,不要看iif看上去能简化代码,实际效率低下,而且里面所有的分枝都会进行计算。
|