使用 Excel 中的公式等函数

发布于 2024-10-09 13:38:08 字数 569 浏览 2 评论 0原文

我正在尝试使用公式来获取字母表中的字母。

公式：

=Keytable(RANDOM,ROW())

函数：

Function KeyTable(seed As Long, position As Long) As String
    Dim i As Long
    Stop
    Dim calpha(1 To 26) As String
    Dim alpha(1 To 26) As String

    For i = 1 To 26
        alpha(i) = Chr(i + UPPER_CASE - 1)
    Next i

    For i = 1 To 26
        calpha(i) = alpha(seed Mod 27 - i)
    Next i
    Stop
    KeyTable = calpha(position)
End Function

结果：

#Value!

当我单步执行该函数时，它永远不会到达第二个停止点。怎么了？

原文

I am trying to use a formula to get a letter of the alphabet.

Formula:

=Keytable(RANDOM,ROW())

Function:

Function KeyTable(seed As Long, position As Long) As String
    Dim i As Long
    Stop
    Dim calpha(1 To 26) As String
    Dim alpha(1 To 26) As String

    For i = 1 To 26
        alpha(i) = Chr(i + UPPER_CASE - 1)
    Next i

    For i = 1 To 26
        calpha(i) = alpha(seed Mod 27 - i)
    Next i
    Stop
    KeyTable = calpha(position)
End Function

Result:

#Value!

When I step through the function, it never gets to the second stop. What is wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

琉璃繁缕 2024-10-16 13:38:08

RANDOM 不是 Excel 中的函数。 RAND() 是，它返回 0 到 1 之间的 float。您需要一个 integer 来进行模数计算。

要获取随机整数，请使用：

INT ((upperbound - lowerbound + 1) * RAND() + lowerbound)

然后，一旦 seed Mod 27 - i 变为 0 或更小，该函数就会终止，因为在 VBA（或大多数语言）中无法使用 0 或更小的索引对数组进行索引。

但实际上，对于随机字母，您所需要做的就是：

=CHAR(RANDBETWEEN(65,90))

RANDOM is not a function in Excel. RAND() is and it returns a float between 0 and 1. You need an integer to do modulus calculations.

To get a random integer, use:

INT ((upperbound - lowerbound + 1) * RAND() + lowerbound)

Then, once seed Mod 27 - i becomes 0 or less, the function dies because arrays can't be indexed with 0 or less in VBA (or most languages).

But really all you need to do for a random letter is this:

=CHAR(RANDBETWEEN(65,90))

回复收藏 0 原文

场罚期间 2024-10-16 13:38:08

此代码将返回随机字母：

Function GetLetter()
    Dim letters As String
    Dim randomIndex As Byte

    letters = "abcdefghijklmnopqrstuvwxyz"
    randomIndex = Int((26 - 1 + 1) * Rnd() + 1) //Get random number between 1 and 26

    GetLetter = VBA.Mid$(letters, randomIndex, 1)
 End Function

This code will return random letter of alphabet:

Function GetLetter()
    Dim letters As String
    Dim randomIndex As Byte

    letters = "abcdefghijklmnopqrstuvwxyz"
    randomIndex = Int((26 - 1 + 1) * Rnd() + 1) //Get random number between 1 and 26

    GetLetter = VBA.Mid$(letters, randomIndex, 1)
 End Function

回复收藏 0 原文

~没有更多了~