数据类业务测试过程中常用的excel函数(一)
- 2026-03-30 10:01:30
分享几个数据测试过程中好用的、常用的excel函数，版本要求2021及以上。主要有let、filter、mid、substitute、xlookup、subtotal 共6个，介绍了6个函数的功能、语法、使用例子。
1.let
功能：定义变量+复用变量，就是给一段计算起个名字，后面直接用名字代替，不用再写一串公式
语法：let(变量名1，计算公式1，
变量名2，计算公式2，
....
最终结果)
例子：
总结：相当于把公用、重复的公式抽取成到一个变量中，类似于以前的函数。
2、filter
功能：按条件筛选整批数据，自动溢出结果，简单表述就是 给个区域，给个条件，把符合条件的行 列都吐出来
语法： FILTER(要筛选的区域, 筛选条件, [没找到时显示什么])
例子： =FILTER(A2:A10, B2:B10="技术部", "无数据") 一次性把技术部的A列 姓名都列出来，自动溢出，无需下拉
混合场景：
2 和3函数经常搭配使用，功能更强大，例子
=LET( data, A2:C100, --data表示数据区域
dept, INDEX(data,,2), --dept表示第2列部门
score, INDEX(data,,3), --score表示第3列分数
FILTER(data, (dept="技术部")*(score>=80), "无匹配") ) --筛选技术部& 分数大于等于80分
进行例子实操：对原始表输入如下公式后，出现了下面 红框的结果
3、xlookup
功能：找东西的万能工具，有查找需求可优先使用它，替代旧函数
语法： XLOOKUP(查找值, 查找区域, 返回区域, [无匹配时返回值], [匹配模式], [搜索模式])
匹配模式：0=精确匹配，为默认值；
1=近似匹配升序
-1=近似匹配降序
-2=通配符匹配
搜索模式：1=从上到下（默认）
-1=从下到上
2=二进制升序
-2=二进制降序
例子： =XLOOKUP("张三", A2:A100, C2:C100, "无此人", 0) A列为姓名 C列为薪资，根据姓名查出薪资 ，且精确匹配姓名=张三 搜索为默认1 从上到下
4、subtotal
功能：筛选统计的专属工具，涉及筛选后、不统计隐藏数据计算需要使用它
语法： SUBTOTAL(功能代码, 数据区域1, [数据区域2], ...)
功能码：都是针对筛选后的可见数据。
109=求和 101=计数 103=非空计数
102=平均值 104=最大值 105=最小值
例子： =SUBTOTAL(109, A2:A100) A列是金额，筛选后数据求和
混合场景：
3和4混合使用：
A 列 = 姓名，B 列 = 部门，C 列 = 薪资 ，先找薪资最大值，然后根据薪资最大值查找到姓名
公式为：=XLOOKUP(SUBTOTAL(104, C2:C100), C2:C100, A2:A100, "无数据", 0)
实际工作中的例子，动态计算两行日期的间隔天数：D列为日期
公式：
=IFERROR(INT(D2-LOOKUP(9^9,FILTER(D1:D$2,SUBTOTAL(103,OFFSET(D$2,ROW(D1:D$2)-ROW(D$2),0,1,1))))),"")
筛选后动态计算：
5、mid
功能：从中间截取字符，取第几位到第几位。
语法： MID(文本, 从第几位开始截, 截多少位)
例子：A2=abcdefg
=mid(A2,3,5) 从第3位开始，取5位，结果为cdefg
6、substitute
功能：批量替换字符，用于改内容、删内容、替换关键字
语法： SUBSTITUTE(要处理的文本, 要被替换的字符内容, 替换成什么内容, [第几个]) 可替换第1次、第2次、第n次 、全部替换
例子：如A2为 ss-ad-qq 要将ad改为u
=SUBSTITUTE(A2,"ad","u")
写在最后，
日常数据逻辑的测试过程中，会用到excel处理数据，可以逐渐积累起来。
好记性不如烂笔头，再强的记忆，也抵不过落笔的踏实。坚持记录、坚持输出，把灵感留住，把思考沉淀，把日常的点滴都变成成长的底气。
