excel表格区域内填充不重复的随机整数-英雄云拓展知识分享

本文分享一个基于公式生成n×n随机整数的解决方案，并且每一个整数都是唯一的。举例来看，下图1显示了生成10行10列的不重复随机整数。

图1

解决方案

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

=SMALL(IF(FREQUENCY(($A2:$J,B1:$K1),ROW(INDIRECT(“1:99”))⑴)=0,ROW(INDIRECT(“1:100”))⑴),RANDBETWEEN(1,100-COUNTA($A2:$J,B1:$K1)))

向右向下拖拉至单元格J10。

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

1）10×10的单元格区域从最左上角的单元格正下方的单元格开始，向下并向右延伸。

2）最左上角单元格右边的1×10单行单元格数组

这里都是相对/绝对混合援用。

工作原理

斟酌使用FREQUENCY函数，不但可以生成通常使用COUNTIF函数能够取得的结果，而且还可以操作由多个单元格区域组成的援用。

让我们从示例中随意选择一个公式，看看其是如何工作的。举例来看，在单元格C8中的公式：

=SMALL(IF(FREQUENCY(($A9:$J$11,D8:$K8),ROW(INDIRECT(“1:99”))⑴)=0,ROW(INDIRECT(“1:100”))⑴),RANDBETWEEN(1,100-COUNTA($A9:$J$11,D8:$K8)))

可以看到，公式援用的两个单元格区域是：D8:$K8和$A9:$J$11，以下图2所示。

图2

公式中的：

FREQUENCY(($A9:$J$11,D8:$K8),ROW(INDIRECT(“1:99”))⑴)

是这类情况下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(INDIRECT(“1:99”))⑴)=0,ROW(INDIRECT(“1:100”))⑴)

转换为：

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(INDIRECT(“1:100”))⑴)

转换为：

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})

转换为：

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

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

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

100-COUNTA($A9:$J$11,D8:$K8)

使用了COUNTA函数，可用于处理多个单元格区域。因此：

RANDBETWEEN(1,100-COUNTA($A9:$J$11,D8:$K8))

转换为：

RANDBETWEEN(1,100⑵7)

其中的27等于单元格区域$A9:$J$11中的20个非空元素加上D8:$K8中的7个非空元素。（注意，将A1:J10区域周边的无关单元格成心地留为空白单元格非常重要）

综上，公式转换为：

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

得到所需的结果。

小结

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