excel数据透视表如何填充不重复的随机整数
时间:2023-01-11 19:03:17
本文分享一个基于公式生成n×n随机整数的解决方案,并且每个整数都是唯一的。例如,下显示了生成10行10列的不重复随机整数。
解决方案
在单元格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,如下所示。
公式中的:
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函数可以操作多个单元格区域。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Win10系统打开文件提示文件系统错误(-2147416359)怎么解决
![](https://img.aspxhome.com/file/2023/28/a245400_0s.jpg)
Win11想改Win10系统?Win11系统改Win10系统教程
![](https://img.aspxhome.com/file/2023/1/a281244_0s.png)
chemdraw怎么让结构自动调整?chemdraw自动调整结构式教程
![](https://img.aspxhome.com/file/2023/30/a270953_0s.png)
excel表格取消冻结的教程
惠普 战X 13 锐龙版笔记本重装win10系统教程
![](https://img.aspxhome.com/file/2023/30/a267914_0s.png)
Excel2010中分数数字格式怎么设置?
![](https://img.aspxhome.com/file/2023/9/a143609_0s.jpg)
WPS文字2013中制作个性页眉页码的方法
Win10 word模板路径在哪?Win10如何修改word模板路径?
![](https://img.aspxhome.com/file/2023/26/a225830_0s.jpg)
Excel中SUMIF函数的用法和SUM函数多条件求和的视频教程
Win7如何架设代理服务器?
![](https://img.aspxhome.com/file/2023/27/a231883_0s.jpg)
Pixelmator Pro 教程「18」,如何在 Pixelmator Pro 中更改照片扩展首选项?
![](https://img.aspxhome.com/file/2023/0/a201920_0s.jpeg)
excel公式不会自动计算怎么办 excel公式自动计算方法
![](https://img.aspxhome.com/file/2023/9/39609_0s.jpg)
[视频]免费升级win10的10大理由之七:全新的内置通用应用
![](https://img.aspxhome.com/file/2023/28/a248906_0s.jpg)
Paint3D如何导出和保存?
![](https://img.aspxhome.com/file/2023/6/a324857_0s.png)
Word2010怎么分栏
![](https://img.aspxhome.com/file/2023/1/21981_0s.gif)
WPS论文排版教程六:如何添加目录
Win7电脑rar文件打不开怎么办?
![](https://img.aspxhome.com/file/2023/2/a288039_0s.jpg)
Win7打印机共享成功但是无法打印如何处理?
![](https://img.aspxhome.com/file/2023/26/a228423_0s.jpg)
如何解决苹果Mac安装Axure首次打开报错的问题?
![](https://img.aspxhome.com/file/2023/1/a203481_0s.jpeg)
电脑调出小键盘打字怎么调?
![](https://img.aspxhome.com/file/2023/30/a268075_0s.jpg)