录入资源池
也就是随机字符串的来源,以全体字母和数字为例:在excel某一列列出相应内容,然后记录好列数,比如F列
资源池 |
a |
b |
c |
d |
e |
f |
g |
h |
i |
j |
k |
l |
m |
n |
o |
p |
q |
r |
s |
t |
u |
v |
w |
x |
y |
z |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
P |
Q |
R |
S |
T |
U |
V |
W |
X |
Y |
Z |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
方法:
原理还是使用RANDBETWEEN函数,但RANDBETWEEN本身只能生成指定数值之间的随机数,所以结合INDIRECT函数拼出资源池对应的坐标,返回对应坐标中的值即可。同时结合concat函数需要几个字符拼接几次即可:
=CONCAT(INDIRECT("F"&RANDBETWEEN(2,63)),
INDIRECT("F"&RANDBETWEEN(2,63)),
INDIRECT("F"&RANDBETWEEN(2,63)),
INDIRECT("F"&RANDBETWEEN(2,63)),
INDIRECT("F"&RANDBETWEEN(2,63)),
INDIRECT("F"&RANDBETWEEN(2,63)))
如上公式就随机生成了6个字符的字符串
序号 | 验证码 |
1 | 3NmBJ1 |
2 | fvb47t |
3 | dMI4e8 |
4 | uUfQBt |
5 | CDLyxl |
6 | ORDYqo |
7 | dARurg |
8 | QLNz6Q |
9 | I4pGVo |
10 | mEL567 |
11 | biyV2X |
12 | 6yQ2kV |
13 | TJAjcX |
14 | 4uYvXK |
15 | Zjmtt3 |
16 | DTXbjA |
17 | dlo2NU |
18 | mht6Rj |