楼主
liuguansky |
Q:如何按条件在有效数据中随机抽取数据？
A：用如下代码可以实现：
- Public s As Boolean
- Private Sub CommandButton1_Click()
- s = True
- Dim arrs, arr, i&, t&, arrt(), k%, m&, n&, arrg()
- k = CInt(Cells(3, 2).Value)
- t = Cells(Rows.Count, 1).End(3).Row
- arrs = Range("a9:d" & t)
- Application.ScreenUpdating = False
- With Range("a9:d" & t)
- .AutoFilter Field:=4, Criteria1:="<>无效报价"
- ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
- ("D9:D" & t), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
- xlSortNormal
- With ActiveSheet.AutoFilter.Sort
- .Header = xlYes
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
- Sheet2.Cells.Clear
- Range("a10:c" & t).SpecialCells(xlCellTypeVisible).Copy Sheet2.Cells(1, 1)
- With Sheet2.Range("a1").CurrentRegion
- arr = .Resize(.Rows.Count - k, 3)
- End With
- Sheet2.Cells.Clear
- .AutoFilter
- .Value = arrs
- End With
- ReDim arrt(1 To k, 1 To 3)
- ReDim arrg(1 To k, 1 To 1)
- Cells(6, 1).Resize(3, 3).ClearContents
- Application.ScreenUpdating = True
- m = UBound(arr, 1)
- Do Until s = False
- For i = 1 To k
- arrg(i, 1) = Int(Rnd() * m + 1)
- Next i
- For i = 1 To k
- For n = 1 To 3
- arrt(i, n) = arr(arrg(i, 1), n)
- Next n, i
- Cells(6, 1).Resize(k, 3) = arrt
- DoEvents
- DoEvents
- DoEvents
- Loop
- End Sub
- Private Sub CommandButton2_Click()
- s = False
- End Sub
具体示例文件如下：
|