近期处理数据用到的excel公式,觉得比较有用,记录一下:
1、在某个范围内产生随机数
1.1 RAND()函数来生成随机数(同时返回多个值时是不重复的)
1.2 RAND()函数返回的随机数字的范围是大于0小于1。因此,也可以用它做基础来生成给定范围内的随机数字。
生成制定范围的随机数方法是这样的,假设给定数字范围最小是A,最大是B,公式是:=A+RAND()*(B-A)。
举例来说,要生成大于60小于100的随机数字,因为(100-60)*RAND()返回结果是0到40之间,加上范围的下限60就返回了60到100之间的数字
1.3 上面RAND()函数返回的0到1之间的随机小数,如果要生成随机整数的话就需要用RANDBETWEEN()函数了,
如:=RANDBETWEEN(1,100)生成大于等于1小于等于100的随机整数。
这个函数的语法是这样的:=RANDBETWEEN(范围下限整数,范围上限整数),结果返回包含上下限在内的整数。
注意:上限和下限也可以不是整数,并且可以是负数。
1.4 RAND()和RANDBETWEEN()是生成随机数的基础函数,也可以灵活变通。比如说要生成0.01至1之间包含两位小数的随机数,则可用下图的公式实现:
=RANDBETWEEN(1,100)/100
1.5 随机生成指定的几个词语,可用choose函数和randbetween函数结合来实现;
如:=CHOOSE(RANDBETWEEN(1,4),"严重警告","警告","关注","安全") 可随机产生这4个词。
1.6 对某个等级得到相应范围的数值:=IF(B18="安全",RANDBETWEEN(200,250),IF(B18="关注",RANDBETWEEN(50,100),IF(B18="警告",RANDBETWEEN(8,15),IF(B18="严重警告",RANDBETWEEN(1,7)))))
2、excel中如果经常遇到需要取一列数的左边3位或者右边5位的问题,需要用到的函数是left()函数、right()函数和mid()函数。
left()函数为取左边函数。right()函数为取右边函数。mid()函数为取中间函数。
2.1 在单元格里输入“=left(A1,3)" :取A1单元格内容的左三位字符
2.2 在单元格里输入”=right(A1,5)" :取A1单元格内容的右5位字符
2.3 Excel中的MID函数可以用来从指定的字符串中截取出指定数量字符的函数。MID函数的形式如下:MID(text, start_num, num_chars)
text是一串我们想从中截取字符的“字符串“;
start_num是一个数字,是指从”字符串“的左边第几位开始截取;
num_chars也是数字,是指从start_num开始,向右截取的长度。
mid()函数——取中间函数:
mid(A1,3,5)的意思是:取A1这个数从第3个数字开始总共5位。
mid(A1,1,3)的意思是:取A1这个数从第1个数字开始总共3位。
这样用这一个函数就满足了上面取左取右的问题,而且更灵活,任何数的任何位置的数都能随便取出来。
3、时间格式的转换
若B4单元格为:20170104,想将其转化为2017-01-04 =TEXT(DATE(LEFT(B4,4),MID(B4,5,2),RIGHT(B4,2)),"yyyy-mm-dd")
若C4单元格为:20160301,想将其转化为2016/03/01 =TEXT(DATE(LEFT(C4,4),MID(C4,5,2),RIGHT(C4,2)) ,"yyyy/mm/dd")
若D4单元格为:2016/03/01,想将其转化为20160301 =TEXT(D4,"yyyymmdd") 反转换 2016/03/01到20160301
4、text函数的使用(如果希望在常规格式下,仍然显示为希望有的格式,可以使用text函数)
主要是日期、时间等格式的调整,因为时间格式的在把格式调整为常规时会变成一串数字,对统计非常有影响
Text函数,有人称它是万能函数。其实,text的宗旨就是将自定义格式体现在最终结果里。Text函数主要是将数字转换为文本。当然,也可以对文本进行一定的处理。
TEXT函数的语法:TEXT(value,format_text) Value:为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用。 Format_text:为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。
text返回的一律都是文本形式的数据。如果需要计算,可以先将文本转换为数值,然后再计算。文本型数值遇到四则运算会自动转为数值。但文本会不参与sum之类的函数运算。
4.1 基本的数字处理方式
text函数的基本写法:text(数值,格式)
=TEXT(12.34,"0"),含义是将数字12.34四舍五入到个位,然后以文本方式输出结果。这个公式也可以简写成:text(12.34,0),当只有一个0的时候,引号可以不加。
text(数值,"0.0"),可以看到效果是保留一位小数。写几个0,代表想要数据形成几位数。
“0”:数字占位符。如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。例如:=text(12.34,"00000"),就显示为:00012。
“#”:数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于“#”的数量,则按“#”的位数四舍五入。例如:“###.##”,12.1显示为12.10;12.1263显示为:12.13。
“G/通用格式”:以常规的数字显示,相当于“分类”列表中的“常规”选项。
前导0的效果,想显示几位就写几个0,可以配合rept函数来写,rept(字符,重复次数)
4.2 text在日期时间处理方面的应用。
先说说日期这种特殊的数据类型,日期“2010-3-12”,其实是数字40249,如下图所示。
所以,=text(40249,"yyyy-mm-dd")可以显示2010-03-12。 text是把日期所代表的真正的数字,来转成所需要的日期格式的文本。
而20110506,要显示2011-05-06的话,要用上面介绍的0的方法:=text(20110506,"0-00-00")。
上图所示公式中:yyyy可以用e来代替。mm,表示显示两位月份,m显示一位。中间的连接号,还可以换成其他文本。 text的结果是文本,如果text返回"2010-10-10"的话,再去设置格式就改变不了了。 mmm、mmmm,ddd、dddd等等都有各自的含义。ddd代表英文星期。中文的星期用aaa和aaaa。 时间里面有个m,和月份相同,所以,单独使用m的时候,系统默认是月份。m必须和h或s同用,才能表示分。[M]带中括号的时候,也表示分。因为加中块号是时间的特殊表示方式
5、任意范围内的日期和时间
D4单元格:2017-01-04 09:00:00 E4单元格:2017-01-04 11:28:00
F4单元格输入公式:=TEXT(D4+(E4-D4)*RAND(),"yyyy-mm-dd hh:mm:ss") 结果:2017-01-04 09:48:40
G4为在F4的基础上再加上半小时左右:
在G4单元格输入公式:=TEXT(F4+TIME(0,RANDBETWEEN(28,30),RANDBETWEEN(0,50)),"yyyy-mm-dd hh:mm:ss") 结果:2017-01-04 10:19:16