excel表格区域内填充不重复的随机整数
发布时间:2022-04-03 21:59:58
本文分享一个基于公式生成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函数可以操作多个单元格区域。
猜你喜欢
- Excel2013拆分表格的方法1、打开Excel 2013 中需要拆分的表格,将鼠标光标定位在需要拆分的位置。【视图】——【窗口】——【拆
- 我们在word文档排版过程中,一般的操作过程是选定文字,然后设定各种格式与样式,反复多次的调整与操作。修改时也是要把所有要改的文字样式都选上
- 在Word中编辑文件,总会涉及到设置几级标题的问题,一级的又该如何设置呢?下面随小编一起来看看吧。word设置一级标题的步骤打开Word,点
- 在 iPadOS 14 发布以前,很多用户对 Apple Pencil 的定位还只是停留在“绘图工具”,之所以产生这样的感觉,是因为使用 A
- 我们中文排版时,通常首行都缩进两个字符,那么这个简单的设置在最新的Word 2013里面又该如何设置呢?下面Microsoft酋长就来介绍一
- excel表格中有很多种功能,同时每个功能都非常实用,本文为大家介绍的是excel如何制作下拉菜单,给大家展示带使用下拉菜单中的选项,使关联
- word怎么设置才能尽可能的减少打印页数?要打印的word文档有很多页,即使双面打印也要浪费很多纸张,该怎办呢?下面分享一种设置方法可以减少
- 在Word2013中设置SmartArt图形文本艺术字样式的步骤如下所述:第1步,打开Word2013文档窗口,双击需要设置艺术字样式的Sm
- 转易侠扫描王是一款图片文字识别工具,且还支持将识别出来的文字进行翻译,本文中介绍的是该软件的安装和识别图片文字的方法如何识别图片中的文字?转
- 第1步,打开Word2013文档窗口,并打开一个Word2010或Word2003文档,用户可以看到在文档名称后边标识有“兼容模式”字样。单
- 昨天,苹果才给大家推送iOS 16.1正式版,今天苹果又向 iPhone 、iPad 用户推送了 iOS 16.2 / iPadOS 16.
- word2003怎么添加添加带图片LOGO的页眉与页脚?一般我们页眉页脚插入的都是数据或者文字,很少有添加图片的,但是如果想在页眉页脚中添加
- 【查找与替换】,是将输入在【查找内容】的字符串置换为【替换为】内容的功能,而当【替换为】是空白,则【查找内容】中的内容就会变成空白……也就是
- word2007怎么设置首字下沉步骤一:打开word2007,新建一片文档。 步骤二:把光标插入点移到
- 方法一问:请问,怎样才能在word中正常使用wps文件?答:一般来说,wps打开word文件是比较顺利的,但word打开wps文件时,若设置
- 我打开一篇Word文档,图片显示慢,等到花儿都谢了……有捷径吗?上菜:糖醋打印预览打开文档时,快速点击“打印预览”按钮,图片就会立刻清晰的显
- word2010网格线怎么画图形?word2010中想要绘制一些图形,我们可以使用网格线绘制,下面我们就来看看详细的教程,需要的朋友可以参考
- Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。很多小伙伴不知道其实
- 在报纸中,我们经常会看到一张图片被嵌入到了文字中(如下图),你是不是以为这中效果只有使用专业的排版软件才可以实现?易捷PDF转换成Word可
- 一个合理的硬盘分区方式可以方便固态硬盘的用户快速重装系统以及查找需要的软件游戏,可以更好的发挥出ssd的性能。今天小编就给大家带来了512g