从列表中随机挑选名字

Question

我正在尝试使用一个公式，它将允许我从355个名字的列表中随机选择183个名字。我的excel表单将如下所示：

Names  Random.Names

Paty     
Oscar
John
Anna
Jane
Carlos
Maria
Jennifer
Susan
Kayla

在我的实际工作表上，我有更多的名字，但这只是一个例子。我使用了以下公式，但我有几个单元格在随机化后显示#REF。

=IF(ROWS($1:1)>$E$2,"",INDEX($A$8:$A$355,RANDBETWEEN(1,354)))

如果你有更好的配方，或者你知道我做错了什么，请让我知道。

Answer 1

这是因为索引是相对的，所以第8行是1，第355行是355-8+1 = 348。将RANDBETWEEN更改为1,348

任何大于引用单元格数量的操作都会产生错误。

=IF(ROWS($1:1)>$E$2,"",INDEX($A$8:$A$355,RANDBETWEEN(1,348)))

或者您可以引用整个列并使用8,355：

=IF(ROWS($1:1)>$E$2,"",INDEX($A:$A,RANDBETWEEN(8,355)))

Answer 2

您没有介于A8和A355之间的355个名称，只有355-8+1。

因此，修复RANDBETWEEN()

Answer 3

遵循我的previous anwser的逻辑

您只需打开VBA编辑器并粘贴以下代码：

  'By Julio Jesus Luna Moreno
'jlqmoreno@gmail.com
Option Base 1
Public Function UNIQRAND(a As Variant, b As Variant) As Variant
 Application.Volatile
 Dim k%, p As Double, flag As Boolean, x() As Variant
    k = 1
  flag = False
  ReDim x(1)
   x(1) = Application.RandBetween(a, b)
  Do Until k = b - a + 1

   Do While flag = False
   Randomize
    p = Application.RandBetween(a, b)
     'Debug.Assert p = 2
    resultado = Application.Match(p, x, False)
     If IsError(resultado) Then
      k = k + 1
      ReDim Preserve x(k)
      x(k) = p
       flag = True
      Else
       flag = False
      End If
   Loop
   flag = False
  Loop
  UNIQRAND = x
End Function

此函数将完成此任务

Public Function RANDNAMES(Rango As Range, HowMany As Integer) As Variant
 Dim n, p(), x(), i As Variant
  n = Rango.Rows.Count
   If n < HowMany Then
    MsgBox "Number of pairs must be less than number of total elements"
     Exit Function
   End If
   ReDim x(HowMany)
   ReDim p(n)
     p = UNIQRAND(1, n)
   For i = 1 To HowMany Step 1
    x(i) = Application.Index(Rango, p(i))
      Next i
       Debug.Print HowMany
    RANDNAMES = Application.Transpose(x)

End Function