为什么我的计算阶乘的宏在Excel中不起作用

iaqfqrcu  于 2023-10-21  发布在  其他
关注(0)|答案(3)|浏览(100)

请帮助我理解为什么我得到了#值!在Excel中使用以下UDF时出错?
我查找了这个代码,它应该计算任何大小的数字的阶乘,包括那些大于170的数字,这是我的特殊兴趣:

Function FactorialUsingForLoop(val As Long) As Long
    Dim uni_input As Long, xy_Factorial As Long
    Let xy_Factorial = 1
    For uni_input = 1 To val
        xy_Factorial = xy_Factorial * uni_input
    Next
    FactorialUsingForLoop = xy_Factorial
End Function

注意我的工作表的副本。三角形说:公式中的值的数据类型错误。我已经消除了多余的空格,格式是数字!
谢谢你,谢谢

rkkpypqq

rkkpypqq1#

使用https://en.wikibooks.org/wiki/Visual_Basic_for_Applications/Big_Number_Arithmetic_with_Strings#The_VBA_String_Math_Module中的“Multiply”函数,您可以对数字的字符串表示执行阶乘:

Function FactString(n As Long) As String
    Dim i As Long, s
    s = "1"
    For i = 2 To n
        s = Multiply(s, CStr(i)) 'see link above
    Next i
    FactString = s 'return as text
End Function

FactString(459)是1025位数。不知道你能用输出做什么?
14485403354484377321946598467762747909890903376354418365375845955625396614008248762435334105235580367713111676386354517817732216317813131627739098804735552619387696361000728350823018080878612362369972102024834022677419331190003646581872203634520283130933793877130922404915890229584207088813666362515804443836956427867939234940645305786761788625294812604394194311811648253226441130486116296174698732284230693452530000613019235681414109884129686923390790591493053092840252338650743843979540442934522441499732761419205481441571729238180772206283267561602205068359377969242866767033627224184350824364701713347358540970587316052075568936320882958156270299294847780095880319469432857013405651004619680268495021224607907646943617462020856149988907272090333825938106785197889156789076323307207860239236383751068288278126756115213803211186543060092701556735081943005710263322762703394271852483534078236589862938081272791040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

EDIT-注意到方法的链接确实包含了一个阶乘方法:

Function Factorial(ByVal sA As String) As String

jjjwad0x

jjjwad0x2#

使用Double而不是Long,我刚刚尝试过,这些是第一个数字的值,最多为50(增加5):

9fkzdhlc

9fkzdhlc3#

如果你必须计算一个大数字的阶乘(也许是泊松分布等公式的一部分),你可以考虑以下近似:
斯特林公式。斯特林公式指出,
x!π(2 * π * x)*(x / e)^x
此外,对于大量的计算,XNumber,一个免费的Excel加载项工作得很好!!

相关问题