好好笑

excel 随机生成的数字或字母的公式

May 17, 2011 未分类

第一公式 生成随机的六位数的字母和数字混合

=CONCATENATE(IF(INT(RAND()*2)=0,CHAR(INT(RAND()*9+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*9+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*9+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*9+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*9+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*9+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))))

第二个公式 随机生成六位数数字，不带3和4
=VALUE(CHOOSE(1+INT(7*RAND()),1,2,5,6,7,8,9)&CHOOSE(1+INT(8*RAND()),0,1,2,5,6,7,8,9)&CHOOSE(1+INT(8*RAND()),0,1,2,5,6,7,8,9)&CHOOSE(1+INT(8*RAND()),0,1,2,5,6,7,8,9)&CHOOSE(1+INT(8*RAND()),0,1,2,5,6,7,8,9)&CHOOSE(1+INT(8*RAND()),0,1,2,5,6,7,8,9))

第三个公式 随机生成6为数字

=int(rand()*900000+100000)