excel - 在 CHAR(RANDBETWEEN(65,90)) 中跳过字母 O、I、Z

Question

我正在尝试以 22AA22 (Number+Number+Alphabet+Alphabet+Number+Number) 格式生成 6 位随机数(100,000 个随机数)在excel中。

Number = 2 to 9 Excluding 0 and 1
Alphabet (in CAPS) = A to Y Excluding O, I, Z

我在用

=RANDBETWEEN(2,9)&RANDBETWEEN(2,9)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(2,9)&RANDBETWEEN(2,9)

在 CHAR(RANDBETWEEN(65,90))如何跳过字母 O、I、Z(小写字母和大写字母)。

Answer 1

将 RANDBETWEEN 拆分为三个并将 CHOOSE 与另一个 RANDBETWEEN 一起使用:

CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,72),RANDBETWEEN(74,78),RANDBETWEEN(80,89)))

所以:

=RANDBETWEEN(2,9)&RANDBETWEEN(2,9)&CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,72),RANDBETWEEN(74,78),RANDBETWEEN(80,89)))&CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(65,72),RANDBETWEEN(74,78),RANDBETWEEN(80,89)))&RANDBETWEEN(2,9)&RANDBETWEEN(2,9)