如何在电子表格中做抽奖函数

你真烦

我想在表格中的25个人中进行抽奖，抽5个或10个，但其中6、7、8不能中奖，怎么设置函数,拜托各位大神帮下忙，谢谢！每人只能有一次中奖机会

魂断蓝桥

Sub 查询()
Dim cnn As Object
Dim Sql As String, R
Randomize
R = Rnd
Set cnn = CreateObject("ADODB.CONNECTION")
cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Sql = "select * from [Sheet1$A1:B26] where 序号 not in (6,7,8) order by rnd(" & R & "-序号)"
Sql = "select top 5 * from (" & Sql & ")"
[c:d] = ""
[c2].CopyFromRecordset cnn.Execute(Sql)
cnn.Close: Set cnn = Nothing
End Sub

02761752696

路过  帮顶   不明白不能中奖的 放在候选名单做什么...............黑幕   (*^__^*)

一把小刀闯天下

'6、7、8是你领导的吧，哈哈哈

Option Explicit

Const NUM = 10

Dim cnt As Long, flag As Boolean, arr

Sub test()
  If Not flag Then Call reset
  If cnt = 10 Then MsgBox "已抽完:" & NUM: Exit Sub
  cnt = cnt + 1
  Cells(cnt + 1, "h") = cnt: Cells(cnt + 1, "i") = arr(cnt, 1)
End Sub

Sub reset()
  flag = False: cnt = 0: [h:i].ClearContents: Call rnddata
End Sub

Function rnddata()
  Dim i, t, n
  arr = Range("b2:b" & Cells(Rows.Count, "b").End(xlUp).Row)
  For i = 6 To 8
    t = arr(i, 1): arr(i, 1) = arr(UBound(arr, 1) - 8 + i, 1)
    arr(UBound(arr, 1) - 8 + i, 1) = t
  Next
  For i = 1 To UBound(arr, 1) - 3
    n = Int(Rnd * (UBound(arr, 1) - 3)) + 1
    t = arr(i, 1): arr(i, 1) = arr(n, 1): arr(n, 1) = t
  Next
  flag = True
End Function