您的位置: 首页 > EXCEL技巧 > Excel函数 >

用数组公式生成不重复的随机整数列

时间:2013-12-14 整理:docExcel.net

要在Excel中生成不重复的随机整数列,例如将1-22这22个数进行随机排列,通常用在辅助列中输入RAND函数并排序的方法来实现。如果不用辅助列和VBA,用数组公式也可以实现。在A2单元格中输入数组公式:

=LARGE(ROW($1:$22)*(1-COUNTIF($A$1:A1,ROW($1:$22))),INT(RAND()*(23-ROW(A1))+1))

公式输入完毕按Ctrl+Shift+Enter结束,然后拖到填充柄填充公式到A23,即可在A2:A23中生成1-22这22个数,并随机排序。

  

说明:

1. “ROW($1:$22)”产生一列包含1-22的垂直数组,如果需要更多的数值,将“22”改为所需数值即可。

“1-COUNTIF($A$1:A1,ROW($1:$22))”用COUNTIF函数判断已产生的数值,如果某个数字已在A列出现,则其对应位置为0,否则为1。

上述两项相乘后得到一个包含“0”和未出现数字的数组,并作为LARGE函数的第一个参数。例如在A9单元格中两项相乘的结果为数组:

{0;0;3;4;0;6;0;8;9;10;11;0;0;14;15;16;17;18;19;20;0;22}

其中“13、7、5、1、12、2、21”这7个数已在A列中出现,其对应位置为“0”。

2.“INT(RAND()*(23-ROW(A1))+1)”为LARGE函数的第二个参数,其作用是产生一个随机整数,以A9单元格为例,由于已出现7个数字,还有15个数字未出现,故随机数的最大值为15,该项产生一个1-15之间的随机整数。

如果要在行中生成随机整数列,可用下面的数组公式,以B3单元格为例:

=LARGE(COLUMN($A3:$V3)*(1-COUNTIF($A3:A3,COLUMN($A3:$V3))),INT(RAND()*(23-COLUMN(A3))+1))

然后向右拖到公式到W3即可。也可选择B3:W3继续向下填充公式在多行中产生随机整数列,如图。

单击下载xls格式的示例文件: 不重复的随机整数.xls

用数组公式获取一列中非空非零值 问题:用数组公式获取一列中非空非零值
回答:...数据,先用“IF($A$1:$A$10<>0,ROW($1:$10), )”来产生一个数列“{1;2; ;4; ; ; ; ; ;10}”,然后用SMALL函数来获取非空数值,最后用OFFSET函数返回单元格数据。OFFSET函数也可以用INDEX函数代替,如B1单元格中的数组公式可以写成: =INDEX($...
怎样约束EXCEL表格内的一数列,让不满住条件时显 问题:怎样约束EXCEL表格内的一数列,让不满住条件时显示为0
回答:此时用IF函数,单元格里输入IF=(B1》1,“正确结果”,“0”),下拉公式,如B1列》1的满足条件,输出字符串“正确结果”,不满足条件输出字符“0”
在Excel表格中怎样把A列的奇数列和B列的偶数列合 问题:在Excel表格中怎样把A列的奇数列和B列的偶数列合并排序?
回答:将公式=MID(A1,1,1)&MID(B1,1,1)&MID(A1,2,1)&MID(B1,2,1)&MID(A1,3,1)&MID(B1,3,1)&MID(A1,4,1)&MID(B1,4,1)&MID(A1,5,1)&MID(B1,5,1)&MID(A1,6,1)&MID(B1,6,1)&MID(A1,7,1)&MID(B1,7,1)&MID(A1,8,1)&MID(B1,8,1)&MID(A1,9,1)&MID(B1...
Excel提示“不能更改数组的某一部分”是怎么回事 问题:Excel提示“不能更改数组的某一部分”是怎么回事
回答:...格中的公式或修改公式后按回车键,Excel提示“不能更改数组的某一部分”是怎么回事? 答:该单元格中的公式数组公式,并且是多单元格数组公式,即该数组公式为位于多个单元格中的数组公式。如果要修改多单元格数...
用颜色标记包含数组公式的单元格 问题:用颜色标记包含数组公式的单元格
回答:当工作表中包含大量多单元格数组公式时,有时为了方便编辑这些数组公式,可能希望将工作表中的数组公式单独标记出来,以区分非数组公式,这时可以用下面的VBA代码来实现。 选择包含数组公式的工作表,按Alt+F11,打开VBA...
用数组公式求某个区域中最大的几个值 问题:用数组公式求某个区域中最大的几个值
回答:...出某个数值区域中最大的或最小的几个值,可以用下面的数组公式,假如数值在A1:B10区域中。 1.将公式返回的结果放在某一列中。 求出该区域中最大的3个值,并将其放在D1:D3区域中:先选择D1:D3,然后在编辑栏中输入数组公式...
相关推荐: