造数常用的Excel表达式
Excel造数用到的高频表达式
随机生成0与1，显示为“是”与“否”
=if(randbetween(0,1)=1,"是","否")
=if(randbetween(0,1)=1,"餐饮","生活日用")
=if(randbetween(0,1)=1,"交通出行","购物")
=if(A1="已激活",1,0)
=if(A1<50%,"等级3",if(A1<80%,"等级2",if(A1<100%,"等级1","等级4"))
记得if函数里需要用英文双引号""
计算日期距离今天有几天
=datedif(E3,today(),"d")
生成随机时间
=text(rand()*("00:1440")+"24:00:00","hh:mm:ss")
输入1显示正常，输入0显示未激活
单元格格式-自定义-
[=1]正常;[=0]未激活;
[=1]已绑定;[=0]未绑定;
VLOOKUP默认是模糊匹配
场景：找到银行对应的编号
解决办法：VLOOKUP(C1,$A$1:$B$1,FALSE)
这里要注意的是，锁定表格按F4，精确匹配写FALSE，不然不是精准的，会出错
根据百分比设置等级
业绩按照从高到低排序，在业绩中，取前10%为等级A，取前20%为等级B，取40%为等级C，取剩下10%为等级E
解决办:1：假设数据在A1，
=LOOKUP(PERCENTRANK($B:$B,$B2),{0,0.1,0.3,0.7,0.9;"E","D","C","B","A"})
解决办法2：=IF(A1<80%,"一般",IF(A1<90%,"良",IF(A1<100%,"优","不及格")))
IF使用场景：将150%，或大于100%的百分数，显示成100%
解决办法：假设数据在A1，
=IF(A1>=100%,100%,A1)或者=IF(A1>=1,1,A1)，单元格设置成百分比
IF使用场景：正数换为0，负数取绝对值
解决办法：假设数据在A1，
=IF(A1>0,0,ABS(A1))
求余数，将大数变小
场景：一列中有多个0，多个大数字，需要将大数字变为个位数，
解决办法：可以是取得某个数的余数，或者保留大数字的最高位，或者取对数
=MOD(A1,5)，对5取余数
多列合并为一列
解决办法：=TEXTJOIN("",TRUE,A1:E1)
填充空白行
解决办法：【定位】-【空值】，在空白行输入=上一单元格，按Ctrl+Enter，全部填充
多列需要将其上下叠加成一列
解决办法：将数据到Word中，【布局】-【转换为本文】，在弹出的窗口中设置【段落标记】，然后将数据复制粘贴到表格中
多行文本合并成一行
解决办法1：选中要合并的文本，调整列宽到足够宽；
依次单击【开始】-【填充】-【内容重排】即可
解决办法2：=TEXTJOIN("",A1:A5)
内容，复制次数，按列排序
解决办法1：假设内容数据在A1，复制次数在B1，在C1单元格输入=REPT(A1&CHAR(10),B1),CHAR(10)是一个换行符，然后将结果，复制到Word中，然后再复制回Excel中就好
随机生成数字
=randbetween(0,1)生成0或1
=randbetween(100,1000)生成100到1000之间的整数
=randbetween(1,99)/100
=rand()随机生成0~1之间的小数
保留两位小数的随机数
- round(A1/100,2)，A1用randbetween(10,20)，就可以生成0.1~0.2之间的小数，并保留2位小数；用ROUND函数保留两位小数点，round(A1,2)
- 或者生成0.01至1之间包含两位小数的随机数，randbetween(1,100)/100
大批量填充
写完表达式，=100+row(A1)，在A1:A1000，选中后，Ctrl+D，复制表达式
合并两个单元格字符串
将两列合并为一列，=A1&B1，就可以将A1和B1的两个单元格里的内容连接为一个字符串
