在 2 列唯一的随机数中生成 5000 条记录 [英] Generate 5000 records in 2 columns of random number that being unique

问题描述

我如何在 2 列 1 到 100 之间唯一的随机数中生成 5000 条记录.

How I can generate 5000 records in 2 columns of random numbers between 1 and 100 that being unique.

例如:

 A            B
----------------
 1            98
 1            23
 37           98
 6            56
 93           18
 .            .
 .            .
 .            .

谢谢

推荐答案

这是一个使用公式的简单方法.它是否合适取决于上下文.

Here is a simple-minded approach using formulae. Whether it would be appropriate would depend on context.

首先在公式"选项卡中将计算选项设置为手动".

First in the Formulas tab set calculation options to 'Manual'.

在a2中输入以下公式:-

Put the following formula in a2:-

=RANDBETWEEN(1,100)

B 将成为辅助列.将以下内容放在 B2 中:-

B is going to be a helper column. Put the following in B2:-

=RANDBETWEEN(1,99)

C 列是您想要的第二个结果.将以下内容放入 C2:-

Column C is the second result that you want. Put the following in C2:-

=IF(B2<A2,B2,B2+1)

根据需要下拉公式.

每次按下立即计算"，您都会得到一组新的随机数.

Each time you press 'Calculate Now', you will get a fresh set of random numbers.

然而，如果你真的需要唯一的行(每一行都不同)，你需要一种不同的方法 - 可以生成一组 4 位数字，将它们分成第一对和最后一对数字，然后过滤掉第一个和第二个相等.

However if you really need unique rows (every row to be different) you'd need a different approach - could generate a set of 4-digit numbers, split them into first and last pairs of digits and filter out ones where first and second were equal.

生成A2中的4位数字:-

Generate the 4-digit number in A2:-

=RANDBETWEEN(1,9998)

取B2中的第一个两位数加一:-

Take the first two-digit number plus one in B2:-

=INT(A2/100)+1

取C2中的第二个2位数字加一:-

Take the second 2-digit number plus one in C2:-

=MOD(A2,100)+1

检查 D2 中的无效数字:-

Check for invalid numbers in D2:-

=OR(ISNUMBER(MATCH(A2,A$1:A1,0)),B2=C2)

设置 E2 中有效数字的运行总数:-

Set up a running total of valid numbers in E2:-

=COUNTIF(D2:D$2,FALSE)

以下是第二种方法如何检查重复行以及行中的重复数字.请注意，您必须生成大约 3,000 行才能获得 2,500 个不同的行:-

Here's how the second approach would look with checking for duplicate rows as well as duplicate numbers within a row. Note that you'd have to generate about 3,000 rows to get 2,500 distinct rows:-

这篇关于在 2 列唯一的随机数中生成 5000 条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！