excel公式技巧：在方形区域内填充不重复的随机整数 _知识经验

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

文章插图
图1
解决方案
在单元格A1中输入数组公式：
=SMALL(IF(FREQUENCY(($A2:$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）10×10的单元格区域从最左上角的单元格正下方的单元格开始，向下并向右延伸。
2）最左上角单元格右侧的1×10单行单元格数组
这里都是相对/绝对混合引用。
【excel公式技巧：在方形区域内填充不重复的随机整数】工作原理
考虑使用FREQUENCY函数，不仅可以生成通常使用COUNTIF函数能够获得的结果，而且还可以操作由多个单元格区域组成的引用。
让我们从示例中随便选择一个公式，看看其是如何工作的。例如，在单元格C8中的公式：
=SMALL(IF(FREQUENCY(($A9:$J$11,D8:$K8),ROW(INDIRECT(“1:99”))-1)=0,ROW(INDIRECT(“1:100”))-1),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”))-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(INDIRECT(“1:99”))-1)=0,ROW(INDIRECT(“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(INDIRECT(“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})
转换为：
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})