Excel 常用 6 组公式组合

为什么坚持写书 —— 一名 IT 培训师的心里话

第 1 组：VLOOKUP + IFERROR

📋示例数据（A1:B6）

表格

查找单元格：D2 = 张三，E2 放公式 ✅解决问题：根据姓名查手机号，找不到不弹出 #N/A，显示文字

=IFERROR(VLOOKUP(D2,A:B,2,0),"无此人")

• VLOOKUP：按值查找。参数 1：找谁 D2；参数 2：查找区域 A:B；参数 3：返回区域第 2 列；参数 4：0 = 精确匹配
• IFERROR：捕获报错。参数 1：要运行的公式；参数 2：出错时展示内容

第 2 组：SUMIFS + COUNTIFS（多条件求和、计数）

📋示例数据（A1:C7）

表格

条件：部门 = 销售部，产品 = 笔记本 ✅解决问题：同时满足两个条件，算条数、算总销售额 多条件计数公式：

=COUNTIFS(A:A,"销售部",B:B,"笔记本")

多条件求和公式：

=SUMIFS(C:C,A:A,"销售部",B:B,"笔记本")

• COUNTIFS：多条件计数。成对写：条件区域 1，条件 1，条件区域 2，条件 2，全部满足才计数
• SUMIFS：多条件求和。参数 1：要求和的数字列 C:C；后面成对：条件区域 + 条件

第 3 组：IF + AND + OR

📋示例数据（A1:B5）

表格

✅规则：笔试≥60 并且 面试≥60，合格；或者 笔试 + 面试≥150，合格；其余不合格 ✅解决问题：复杂多条件判断，自动出结果

=IF(OR(AND(A2>=60,B2>=60),A2+B2>=150),"合格","不合格")

• AND：并且，里面所有条件全部成立才为真
• OR：或者，任意一个条件成立就为真
• IF：分支判断。参数 1：判断条件；参数 2：成立显示；参数 3：不成立显示

第 4 组：INDEX + MATCH（逆向查找，VLOOKUP 升级版）

📋示例数据（A1:B5）

表格

查找单元格：D2=13800138002，E2 放公式 ✅解决问题：查找值在左边、要返回右边内容，VLOOKUP 做不到，这个组合可以

=INDEX(B:B,MATCH(D2,A:A,0))

• MATCH：找行位置。参数 1：找谁 D2；参数 2：在哪一列 A:A；参数 3：0 精确匹配，返回行号
• INDEX：按行号取内容。参数 1：从哪一列拿数据 B:B；参数 2：第几行（MATCH 返回的数字）

📋示例数据（A1:A4）

表格

✅解决问题：自动算周岁，表格每天打开自动更新年龄

=DATEDIF(A2,TODAY(),"Y")&"岁"

• TODAY：无参数，自动获取系统今天日期，会自动变
• DATEDIF：算两个日期差。参数 1：起始日期 A2；参数 2：结束日期 TODAY ()；参数 3："Y" 整年、"M" 整月、"D" 天数

第 6 组：FILTER + COUNTA（365/2021 新版专属，溢出筛选）

📋示例数据（A1:B6）

表格

✅解决问题：一键把财务部所有人自动筛选出来，溢出展示；再统计一共多少人 筛选公式（放一个单元格，自动往下溢出）：

=FILTER(A:A,B:B="财务部","没有数据")

统计筛选出来人数：

=COUNTA(FILTER(A:A,B:B="财务部"))

• FILTER：按条件筛选。参数 1：要输出的内容 A:A；参数 2：筛选条件 B:B="财务部"；参数 3：没匹配到时提示文字
• COUNTA：统计有内容的单元格，空白不算；COUNT 只统计纯数字