随机选择另一列的一列子集中的值

发布于 2024-11-27 12:14:39 字数 229 浏览 0 评论 0原文

我可以用来随机选择 A 列中与给定 B 值关联的值的最简单公式是什么。因此，在下表中，我希望随机选择 A，其中 B = 3。因此，我在第 1 行 (5.4) 和第 3 行 (4.2) 之间随机选择。请注意，该表可以任意大。

    A     B

1   5.4   3          
2   2.3   1
3   4.2   3
4   9.2   2
    ...   ...

原文

What is the simplest formula I can use to randomly choose a value in column A that is associated with a given B value. So in the table below, I'm looking to randomly choose an A where B = 3. So I'm randomly choosing between row 1 (5.4) and row 3 (4.2). Note that this table can be arbitrarily large.

    A     B

1   5.4   3          
2   2.3   1
3   4.2   3
4   9.2   2
    ...   ...

分享到QQ

分享到微博

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

发布评论

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

玩物 2024-12-04 12:14:39

从概念上讲，您可以通过多种方式完成此操作，但这里有一种 (VBA)，您可以使用一组可能的选择，然后从该列表中获取随机元素：

创建一个采用范围和搜索值的 udf
循环遍历该行如果它等于您的搜索值，则获取单元格偏移量 -1 中的值并将其存储在数组中。
完成后，您将获得一个包含所有可能答案的数组。使用 rand Between 函数并为其指定数组的 lbound 和 ubound。
返回第 i 个元素，其中 i 是它选取的随机数。

更新：
下面是一个代码示例，它循环遍历指定数字的范围，如果找到它，则会将 A 列值添加到可能结果的数组中。然后生成一个随机数并用于从该列表返回一个随机值。

Function GetRand(ByVal cell_range As Range, ByVal criteria As Double) As Double

Dim cell As Range
Dim rNum As Long
Dim i As Long
Dim possibleChoices() As Double
ReDim possibleChoices(1 To cell_range.Count)

i = 1
For Each cell In cell_range
    If cell.Value = criteria Then
        possibleChoices(i) = cell.Offset(0, -1).Value
        i = i + 1
    End If
Next

rNum = Application.WorksheetFunction.RandBetween(1, i - 1)
GetRand = possibleChoices(rNum)

End Function

优化：
这是同一功能的更灵活的版本。它需要 3 个参数 - 您想要查看的范围、您想要查找的内容以及您想要从中获得随机结果的单元格的偏移值。它还使用变体，因此您可以搜索文本或数字。所以在你的情况下，你会写：

=GetRand(B1:B5, 3, -1)

这是代码：

Function GetRand(ByVal cell_range As Range, _
                 ByVal criteria As Variant, _
                 ByVal col_offset As Long) As Variant

Application.ScreenUpdating = False
Dim cell As Range
Dim rNum As Long
Dim i As Long
Dim possibleChoices() As Variant
ReDim possibleChoices(1 To cell_range.Count)

i = 1
For Each cell In cell_range
    If cell.Value = criteria Then
        possibleChoices(i) = cell.offset(0, col_offset).Value
        i = i + 1
    End If
Next

rNum = Application.WorksheetFunction.RandBetween(1, i - 1)

GetRand = possibleChoices(rNum)
Application.ScreenUpdating = True

End Function

Conceptually you could do it a number of ways, but here's one (VBA) where you'd use an array of possible choices then get a random element from that list:

Create a udf that takes a range and the search value
Loop through the row and if it equals your search value, get the value in the cell offset -1 and store it in an array
Once you are done, you'll have an array of all possible answers. Use the randbetween function and give it the lbound and ubound of your array.
Return the i element where i is the random number it picked.

UPDATE:
Here is a code example that loops through the range for the number you specify, and if it find it, it adds the A column value to an array of possible results. Then a random number is generated and used to return a random value from that list.

Function GetRand(ByVal cell_range As Range, ByVal criteria As Double) As Double

Dim cell As Range
Dim rNum As Long
Dim i As Long
Dim possibleChoices() As Double
ReDim possibleChoices(1 To cell_range.Count)

i = 1
For Each cell In cell_range
    If cell.Value = criteria Then
        possibleChoices(i) = cell.Offset(0, -1).Value
        i = i + 1
    End If
Next

rNum = Application.WorksheetFunction.RandBetween(1, i - 1)
GetRand = possibleChoices(rNum)

End Function

Optimization:
Here is a more flexible version of the same function. It takes 3 paramteres - the range you want to look in, what you want to find, and the offset value of the cell you want a random result from. It also uses Variants, so you can search for text or numbers. So in your case, you'd write:

=GetRand(B1:B5, 3, -1)

Here is the code:

Function GetRand(ByVal cell_range As Range, _
                 ByVal criteria As Variant, _
                 ByVal col_offset As Long) As Variant

Application.ScreenUpdating = False
Dim cell As Range
Dim rNum As Long
Dim i As Long
Dim possibleChoices() As Variant
ReDim possibleChoices(1 To cell_range.Count)

i = 1
For Each cell In cell_range
    If cell.Value = criteria Then
        possibleChoices(i) = cell.offset(0, col_offset).Value
        i = i + 1
    End If
Next

rNum = Application.WorksheetFunction.RandBetween(1, i - 1)

GetRand = possibleChoices(rNum)
Application.ScreenUpdating = True

End Function

回复收藏 0 原文