|
本帖最后由 heftyguy 于 2020-1-8 13:30 编辑
无重复抽样代码报错,欢迎大家指正。
Sub sample()
Dim pl As Integer
Dim yb As Integer
Dim p As Integer
Dim i As Integer
Dim j As Integer
Dim N As Integer
pl = InputBox("批量", "输入")
p = InputBox("比例(%)", "抽样")
yb = pl * p / 100
yb = Round(yb, 0)
'输入产品编号及随机数
N = 2: i = 1
Do
Sheet1.Cells(N, 1) = i
Sheet1.Cells(N, 2) = Rnd
N = N + 1: i = i + 1
Loop Until i > pl 'MsgBox (Range("B" & CStr(N)))
'同名排序及不同名排序
N = N - 1
M = 2: i = 1
Do
Sheet1.Cells(M, 3) = WorksheetFunction.Rank(Sheet1.Cells(M, 2), Range("B2", "B" & CStr(N)), 1) '同名次序
Sheet1.Cells(M, 4) = WorksheetFunction.Rank(Sheet1.Cells(M, 2), Range("B2", "B" & CStr(N)), 1) + WorksheetFunction.CountIf(Range("B2", "B" & CStr(M)), "B" & CStr(M)) - 1 '不同名次序,但似乎countif()不工作
M = M + 1
Loop Until i > pl
'按照不同名次序结果升序,但没有运行
ActiveWorkbook.Worksheets("sample").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("sample").Sort.SortFields.Add2 Key:=Range("D2", "D" & CStr(N)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("sample").Sort
.SetRange Range("A1", "D" & CStr(N))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'输出抽样结果
j = 1: M = 2
Do
Sheet1.Cells(M, 7) = Sheet1.Cells(M, 1)
Sheet1.Cells(M, 8) = Sheet1.Cells(M, 4)
M = M + 1: j = j + 1
Loop Until j > yb
End Sub
|
-
界面
-
-
抽样.zip
21.2 KB, 下载次数: 3
源文件
|