Question

EXCEL一列数据中随机抽取一个大于0且不重复的函数？

EXCEL一列数据中随机抽取一个大于0且不重复的函数

比如在A1:A100单元格中，里面的数字都是精确到小数点后两位的，其中有大于0等于0的,也有小于0的，需要在B1:B10中抽取10个数字，数字必须是大于0的，且10个数字不能重复，请问这个函数怎做？

Answer 1

'打开表格-按ALT+11-点击插入-点击模块

Sub v_chenhangrong_随机取不重复大于0的数()

a = Split(随机取数1(10), "|", -1, 1)

For i = 1 To 10

Cells(i, 2) = a(i)

Next

'Cells(30, 2) = 50000 - WorksheetFunction.Sum(Range("b1:b29"))

End Sub

Function 随机取数1(num As Integer) As String

Dim a

For j = 1 To 100

If Cells(j, 1) > 0 Then t = t & "|" & Cells(j, 1)

Next

t = Right(t, Len(t) - 1)

b = Split(t, "|", -1, 1)

For m = 1 To UBound(b)

For n = m To UBound(b)

If n = m Then k = 0

If b(m) = b(n) Then k = k + 1

If k > 1 Then Exit For

Next

If k = 1 Then c = c & "|" & b(m)

Next

a = Split(Right(c, Len(c) - 1), "|", -1, 1)

Dim i As Integer

Randomize

Dim Index As Integer

Dim Text As String

Dim arU As Integer

arU = UBound(a)

If num > arU + 1 Then num = arU + 1

For i = 1 To num

Index = Int(Rnd * arU)

Text = Text & "|" & a(Index)

a(Index) = a(arU)

arU = arU - 1

Next

随机取数1 = Text

End Function

Answer 2

Excel怎样快速列举不重复数据序号、位置和值

Excel怎样快速从数据中随机抽取n个不重复显示

Excel怎样快速提取不重复数据个数

Answer 3

加辅助列可以实现，把问题作为内容（邮件主题一定要包含“excel”,本人以此为依据辨别非垃圾邮件，以免误删）,excel样表文件（尽量详细说明现状和目标效果）作为附件发到yqch134@163.com帮你看下

Answer 4

如果需要不重复，就尝试用VBA吧。