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单行单元格数组
这里都是相对/绝对混合引用。
工作原理
考虑使用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})
结果为：
{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-27)
其中的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函数可以操作多个单元格区域。
excel公式技巧在方形区域内填充不重复的随机整数视频
相关评论：
宗榕奖FREQUENCY函数、COUNTA函数可以操作多个单元格区域。
宗榕奖可以在这个区域中输入公式=RANDBETWEEN(1,100)，然后选择这个区域，条件格式
宗榕奖1. 选中需要调整行高和列宽的区域。2. 在Excel顶部菜单栏中选择“开始”选项卡，并点击“格式”按钮。3. 在“格式”下拉菜单中，选择“行高”或“列宽”命令，分别调整行高和列宽。4. 在“行高”或“列宽”对话框中，输入相同的数值，使得行高和列宽相等，从而实现正方形的效果。需要注意的是，如果在...
宗榕奖1. 在要计算求积的单元格中点击鼠标右键，选中“公式”选项；2. 在弹出的“函数”对话框中，在“统计”类别下找到“PRODUCT”函数，点击确定；3. 在弹出的“函数参数”对话框中，选择要计算求积的范围，点击确定即可；4. Excel会自动计算该范围内所有单元格的值之积，将结果显示在选定的单元格中。使...
宗榕奖1、首先打开或者创建excel数据文件，并添加列，用于条件判断后的结果输出。2、输入公式=IF(AND(G3>60,H3>60,I3>60)，"合格"，"不合格")，AND(G3>60,H3>60,I3>60)表示同时满足面试、笔试、综合素质分值铅好大于60，当三个分数均大于60即返回合格值，否则返回不合格。3、公式输入完成后，按...
宗榕奖区域参数：统计的区域，比如从A2单元格到H8单元格的长方形区域，表述为A2:H8 条件参数：统计条件，比如“>0”、“=book”等 COUNTIF(A2:H8, ">0") 统计A2:H8区域中大于0的单元格总数 COUNTIFS(区域1, 条件1[, 区域2, 条件2]……) 计算区域内符合多个条件的单元格的数量 COUNTIFS(A2:H8, ...
宗榕奖INDEX函数在选择数组形式时，区域是连续的，从左上角到右下角的一个方形区域，如果是引用形式时，区域可以不连续，可以直接输入，也可以按住Ctrl进行选择，但是要注意，各区域必须用“( )”括住，各区域间必须用“,”隔开，如：=INDEX((C5:D10,E12:H16,G6:K10),2,3,2)...
宗榕奖在Excel中输入公式sum（A4:B8），那么进行求和的单元格个数为:10，对应的单元格地址分别为: A4 A5 A6 A7 A8 B4 B5 B6 B7 B8 。
宗榕奖a. 选择需要添加小方格的单元格形成一个正方形区域。b. 在Excel的菜单栏中选择“开始”选项卡，然后点击“合并和居中”按钮。c. 选择“合并单元格”选项，将所选的单元格合并成一个大的单元格。d. 在合并后的单元格内部通过其他方式添加小方格，例如使用矩形自选图形工具添加形状。这些方法可以根据个人...
宗榕奖使用Ctrl+Enter批量填充，具体操作步骤如下：所需材料：Excel。一、首先点击公式栏最左边的单元格栏。二、输入“B2：G10”，按回车键选中这一区域。三、选中B2：G10这一区域单元格后，按F2键进入编辑状态。四、最后在编辑状态按下Ctrl+Enter键即可把编辑的公式批量填充入所选中的全部单元格。
