用不重复的随机整数填充excel表格区域

本文分享了一个基于公式生成nn个随机整数的解决方案，每个整数都是唯一的。例如，下面的图1显示了10行10列的非重复随机整数的生成。

图1

解决办法

在单元格A1中输入数组公式：

=SMALL(IF(FREQUENCY)($ a 2: $ J $ 11，B1:$K1)，ROW(INDIRECT("1:99"))-1)=0，ROW(INDIRECT("1:100"))-1，RANDBETWEEN(1，100-COUNTA($A2:$J$11，B1:$K1))

向下拖动到单元格J10。

通常，该矩阵被放置在工作表中的某个位置。对于输出结果左上角单元格中的公式，引用的两个单元格区域包括：

1)1010单元格区域从左上角单元格正下方的单元格开始，向下向右延伸。

2)左上角单元格右侧的110单行单元格阵列。

这里是所有相对/绝对混合参考。

操作原理

考虑到FREQUENCY函数，我们不仅可以生成通常使用COUNTIF函数可以获得的结果，还可以操作由多个单元格区域组成的引用。

让我们从例子中挑选一个公式，看看它是如何工作的。例如，单元格C8中的公式：

=SMALL(IF(FREQUENCY((a 9:美元J$11，D8:美元K8)，ROW(INDIRECT("1:99"))-1)=0，ROW(INDIRECT("1:100"))-1，RANDBETWEEN，100-COUNTA(a 9:美元J$11，D8:美元K8))

可以看到，公式引用的两个单元格区域是D8:$K8和$ a93360 $ j $11，如下图2所示。

图2。

在公式中：

FREQUENCY($ a 9: $ J $ 11，D8:$K8)，ROW(间接(" 1:99"))-1)

在这种情况下是COUNTIF函数的有用替代，它可以用来返回由单元格区域中的一些值组成的数组，并且执行这些计数的单元格区域不是单个连续区域，而是两个这样的区域。这里需要注意的是FREQUENCY函数的一个特性，即返回的数组数多于传递给它的元素数。因此，上述结构解决如下：

{0;1;0;0;0;1;0;0;0;1;0;1;0;0;0;0;0;0;1;0;1;0;1;1;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;1;1;0;0;0;1;0;0;0;1;0;0;0;0;0;0;0;0;1;0;1;0;0;1;1;1;0;1;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;1;1;1;0;0;0;1;0;1;0;0;1;0}

显然，我们对这个数组中的零感很感兴趣，所以我们在IF函数中将上面的内容设置为零，其中IF函数的参数value _ IF _ true的值是一个从0到99的整数数组，所以：

IF(FREQUENCY(($A9:$J$11，D8:$K8)，ROW(internal(" 1:99 "))-1)=0，ROW(internal(" 1:100 "))-1)

转换为：

IF({ 0；0;0;0;0;1;0;1;0;0;0;1;0;1;0;0;0;0;0;0;1;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;1;1;1;1;0;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;1;0;0;1;0;0;0;0;0;1;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;1;0;1;1;0;0;0;1;1;1;0;0;1}=0，ROW(INternet(" 1:100 "))-1)

转换为：

IF({ 0；0;0;0;0;1;0;1;0;0;0;1;0;1;0;0;0;0;0;0;1;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;1;1;1;1;0;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;1;0;0;1;0;0;0;0;0;1;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;1;0;1;1;0;0;0;1;1;1;0;0;1}=0,{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99})

转换为：

中频({真；真；真；真；真；假；真；假；真；真；真；假；真；假；真；真；真；真；真；真；假；真；假；假；真；真；假；真；真；真；真；真；真；真；真；真；假；假；假；假；真；真；真；真；假；假；真；真；真；真；真；真；真；真；真；真；真；真；假；真；真；真；假；真；真；假；真；真；真；真；真；假；假；真；真；真；真；真；假；真；真；真；真；真；真；真；真；假；真；假；假；真；真；真；假；假；假；真；真；FALSE}，{ 0；1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99})

结果为：

{0;1;2;3;4;假；6;假；8;9;10;假；12;假；14;15;16;17;18;19;假；21;假；假；24;25;假；27;28;29;30;31;32;33;34;35;假；假；假；假；40;41;42;43;假；假；46;47;48;49;50;51;52;53;54;55;56;57;假；59;60;61;假；63;64;假；66;67;68;69;70;假；假；73;74;75;76;77;假；79;80;81;82;83;84;85;86;假；88;假；假；91;92;93;假；假；假；97;98;FALSE}

现在，成功地创建了一个不在公式单元格下面的行或右边的单元格中的所有值组成的数组，剩下的就是从此数组中随机选择一个数值。

实现这一目标的一种方法是将上述数组传递给小的函数，并指定参数k的值为合适的随机数。由于数组中的数字元素数等于100减去所引用的区域的元素数，因此可以将其用于函数函数的顶端参数：

100-COUNTA(a 9:美元J 11美元，D8:美元K8)

使用了统计出勤异常人数函数，可用于处理多个单元格区域。因此：

朗道(1，100-COUNTA(9:日元11美元，8:美元K8))

转换为：

兰伯特(1,100-27)

其中的27等于单元格区域$A9:$J$11中的20个非空元素加上8:美元K8中的七个非空元素。(注意，将A1:J10区域周边的无关单元格有意地留为空白单元格非常重要)

综上，公式转换为：

=SMAll({ 0；1;2;3;4;假；6;假；8;9;10;假；12;假；14;15;16;17;18;19;假；21;假；假；24;25;假；27;28;29;30;31;32;33;34;35;假；假；假；假；40;41;42;43;假；假；46;47;48;49;50;51;52;53;54;55;56;57;假；59;60;61;假；63;64;假；66;67;68;69;70;假；假；73;74;75;76;77;假；79;80;81;82;83;84;85;86;假；88;假；假；91;92;93;假；假；假；97;98;FALSE}，RANDBETWEEN(1，73))

得到所需的结果。

小结

频率函数、COUNTA函数可以操作多个单元格区域。