常用Excel公式及使用小tips，不定时更新

目录

从给定字符串中随机截取一段指定长度的字符串

毫秒转化为日期(timeStamp)

随机日期及偏移

快速填充

数据转置

随机抽取样本

判断某区域中的数据在另一区域中是否存在

分列

---

---

从给定字符串中随机截取一段指定长度的字符串

=MID(Sheet2!$A$4,RANDBETWEEN(1,Sheet2!$A$5),5)

参数

1：给定字符串

2：开始位置：公式中Sheet2!$A$5 =LEN(A4)，计算了给定字符串的长度，然后从1到字符串长度范围内获取随机数字作为开始位置

3：字符个数

毫秒转化为日期(timeStamp)

分析jtl文件常用：1683185435311  ->  2023-05-04 15:30:35

批量转换使用excel公式

=TEXT((INT(B2/1000)+8*3600)/86400+70*365+19,"yyyy-MM-dd hh:mm:ss")

单个时间戳转换使用以下网站，支持逆向转换。记得选择毫秒

时间戳(Unix timestamp)转换工具 - 在线工具

随机日期及偏移

=TEXT(RANDBETWEEN(2020/1/1,2022/12/31),"yyyy") #年

=TEXT(RANDBETWEEN(2020/1/1,2022/12/31),"yyyy-mm") #月

=TEXT(RANDBETWEEN(2020/1/1,2022/12/31),"yyyy-mm-dddd") #日

组合条件：根据A2的值判断要生成的日期格式

=IF(A2="月",TEXT(RANDBETWEEN($J$2,$K$2),"yyyy-mm"),IF(A2="年",TEXT(RANDBETWEEN($J$2,$K$2),"yyyy"),TEXT(RANDBETWEEN($J$2,$K$2),"yyyy-mm-dd")))

偏移：根据日期格式判断偏移量，年：不变；月：到本年12月；日：随机向后1天或7天

=IF(A2="年",D2,IF(A2="月",TEXT(D2,"yyyy")&"-12",D2+IF(RAND()>0.5,7,1)))

快速填充

比如：需要填充10000行

1. A1输入1
2. 开始 -> 填充 -> 序列

1. 确定后A1 -> A10000已经填充
2. 在其他列首行输入需要填充的公式，然后双击单元格右下角就可以填充10000行。

数据转置

选中 - 复制 - 选择性粘贴

随机抽取样本

数据如下，想要随机抽取人员

1. 序号处输入随机函数公式=RANDBETWEEN(1,20)
2. 人员工号处运用VLookup: =VLOOKUP($E7,$A:$C,column(B2),0)

判断某区域中的数据在另一区域中是否存在

如图：列A中的字段是源数据，列D中的是目标数据。我需要将目标数据中不存在的字段从源数据中删除，从而得到目标数据。所以需要先找出来哪些需要删掉，于是需要对比两组数据。

于是，在C1中输入：=VLOOKUP(A1,Sheet2!D:D, 1,0)

四个参数分别表示：要查找的值，查找区域，要返回的结果在查找区域的第几列，精确匹配（0）或近似匹配（1）

表示要在区域Sheet2!D:D中的第一列，按照精确匹配的方式查找cell A1中的值。找不到显示#N/A，则很容易分辨哪些字段不存在，需要被删除。

分列

一列分成多列：Data -> Text to Columns