我有 100 個不同的客戶端，只有 3 種型別（A、B 或 C）。我想（隨機）選擇 3 個 A 型客戶、2 個 B 型客戶和 30 個 C 型客戶——我們可以在 C 列中添加“y”。
不知道如何從這里開始 - 感謝您的任何提示。
uj5u.com熱心網友回復：
兩個步驟：
- 將每種型別的客戶端保存到一個陣列中（共 3 個陣列）
- 從每個陣列中隨機選擇 x 個客戶端
uj5u.com熱心網友回復：
使用字典計算每種型別并隨機選擇行，直到所有計數為零。
Option Explicit
Sub pick()
Const LIMIT = 1000000 ' limit iterations to solve
Dim wb As Workbook, ws As Worksheet
Dim lastrow As Long
Dim dict As Object, key, bLoop As Boolean
Dim n As Long, x As Long, sType As String
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A", 3
dict.Add "B", 2
dict.Add "C", 30
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
bLoop = True
With ws
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C2:C" & lastrow).Cells.Clear
Do While bLoop
' select random row
x = lastrow * Rnd() 1
sType = Trim(.Cells(x, "B"))
' check if needed
If Len(.Cells(x, "C")) = 0 And dict(sType) > 0 Then
.Cells(x, "C") = "Y"
dict(sType) = dict(sType) - 1
' check if finished
bLoop = False
For Each key In dict
If dict(key) > 0 Then bLoop = True
Next
End If
' avoid infinite loop
n = n 1
If n > LIMIT Then
For Each key In dict.keys
Debug.Print key, dict(key)
Next
MsgBox "Too many iterations to solve", vbCritical, "limit=" & LIMIT
Exit Sub
End If
Loop
End With
MsgBox "Done in " & n & " iterations", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/342819.html
上一篇：VBA查詢表回圈
下一篇：ExcelVBA從TD中提取資料