请帮助我理解为什么我得到了#值!在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
注意我的工作表的副本。三角形说:公式中的值的数据类型错误。我已经消除了多余的空格,格式是数字!
谢谢你,谢谢
3条答案
按热度按时间rkkpypqq1#
使用https://en.wikibooks.org/wiki/Visual_Basic_for_Applications/Big_Number_Arithmetic_with_Strings#The_VBA_String_Math_Module中的“Multiply”函数,您可以对数字的字符串表示执行阶乘:
FactString(459)
是1025位数。不知道你能用输出做什么?14485403354484377321946598467762747909890903376354418365375845955625396614008248762435334105235580367713111676386354517817732216317813131627739098804735552619387696361000728350823018080878612362369972102024834022677419331190003646581872203634520283130933793877130922404915890229584207088813666362515804443836956427867939234940645305786761788625294812604394194311811648253226441130486116296174698732284230693452530000613019235681414109884129686923390790591493053092840252338650743843979540442934522441499732761419205481441571729238180772206283267561602205068359377969242866767033627224184350824364701713347358540970587316052075568936320882958156270299294847780095880319469432857013405651004619680268495021224607907646943617462020856149988907272090333825938106785197889156789076323307207860239236383751068288278126756115213803211186543060092701556735081943005710263322762703394271852483534078236589862938081272791040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
EDIT-注意到方法的链接确实包含了一个阶乘方法:
Function Factorial(ByVal sA As String) As String
jjjwad0x2#
使用
Double
而不是Long
,我刚刚尝试过,这些是第一个数字的值,最多为50(增加5):9fkzdhlc3#
如果你必须计算一个大数字的阶乘(也许是泊松分布等公式的一部分),你可以考虑以下近似:
斯特林公式。斯特林公式指出,
x!π(2 * π * x)*(x / e)^x
此外,对于大量的计算,XNumber,一个免费的Excel加载项工作得很好!!