Excel大神必备!28个硬核公式让你工作效率翻倍
打工人不会这些公式，别说你会Excel！建议收藏备用
在日常办公中，Excel公式用得好，下班下得早！今天就为大家整理了28个超实用的Excel公式(WPS用户也适用哦)，从基础到进阶，全面覆盖各种办公场景，学会这些让你的工作效率提升10倍！请先点赞收藏开始学习吧！
一、基础计算：从"手动算到凌晨"到"公式一键出结果"
1. 加权成绩/绩效计算：SUMPRODUCT才是yyds
场景：期末成绩按"语数外占6:3:1"加权，月度绩效按"业绩70%+考勤30%"核算，手动计算能算到怀疑人生。
公式：
=SUMPRODUCT(数据区域, 权重区域)
// 示例：=SUMPRODUCT(C8:E8,$C$5:$E$5)（C-E列是各科成绩，$C$5:$E$5是权重）
神操作：用F4键快速切换单元格引用方式（绝对引用$C$5/混合引用$C5/相对引用C5），避免重复输入。
2. 成绩/等级判定：IF函数嵌套的"俄罗斯方块"
场景：考试分数≥90标"优秀"，80-89标"良"，低于80标"及格"。
公式：
=IF(B5>=90,"优秀",IF(B5>=80,"良","及格"))
避坑指南：IF函数最多嵌套7层（Excel 2019及以上支持更多），复杂判断建议用IFS函数（=IFS(条件1,结果1,条件2,结果2)）。
3. 数值合格判断：ABS函数简化双重条件
场景：检测产品重量是否在-5g到5g范围内（误差≤5g为合格）。
公式：
=IF(AND(A4>=-5,A4<=5),"合格","不合格")
// 更简洁版：=IF(ABS(A4)<=5,"合格","不合格")（ABS取绝对值）
二、查找与匹配：从"大海捞针"到"精准定位"
6. 单条件查询：VLOOKUP的"黄金搭档"
场景：从员工信息表（A列工号，B列姓名）中，根据工号查姓名。
公式：
=VLOOKUP(A4,F:G,2,0)
参数拆解：
- 第1参数：要查的"工号"（A4）；
- 第2参数：查找范围（F:G，需包含工号和姓名列）；
- 第3参数：返回列号（G列是第2列，所以填2）；
- 第4参数：0=精确匹配（必须写，否则容易报错）。
7. 多列查询：COLUMN函数让公式"自动跑"
场景：从课程表（A列课程名，B列周一，C列周二...）中，查某课程周一、周二、周三的安排。
公式：
=VLOOKUP($A4,$E:$G,COLUMN(B1),0)
技巧：COLUMN(B1)会自动返回B列的列号（2），向右拖动公式时，COLUMN(C1)→3、COLUMN(D1)→4，无需手动修改列号！
三、条件统计：从"数羊式计数"到"一键出数"
17. 单条件求和：SUMIF的"懒人福音"
场景：统计部门A的总销售额（A列部门，B列销售额）。
公式：
=SUMIF(C:C,"部门A",D:D)
语法：=SUMIF(条件区域, 条件, 求和区域)。
18. 通配符查询：*和?的"魔法"
场景：统计所有以"102"开头的订单金额（A列订单号，B列金额）。
公式：
=SUMIF(A:A,"102*",B:B)
注意：*代表任意多个字符（如"1023""102ABC"都算），?代表单个字符（如"102?"能匹配"1023"但不能匹配"102"）。
19. 多条件计数：COUNTIFS的"组合拳"
场景：统计部门A且销售额>1万的订单数量（A列部门，B列销售额）。
公式：
=COUNTIFS(A:A,"部门A",B:B,">10000")
// 单条件版：=COUNTIF(B:B,G5)（统计B列等于G5的数量）。
四、文本处理：从"复制粘贴到崩溃"到"自动清洗"
21. 身份证信息提取：MID+MOD+DATEDIF的"王炸组合"
场景：从18位身份证号（A列）提取性别、生日、周岁。
公式：
- 性别：=IF(MOD(MID(A4,15,3),2),"男","女")（MID取第15-17位，MOD取余，奇数为男）；
- 生日：=--TEXT(MID(A4,7,8),"0-00-00")（MID取第7-14位，TEXT格式化为日期，--转为数值）；
- 周岁：=DATEDIF(D4,TODAY(),"y")（DATEDIF计算两个日期的年份差，TODAY()取当前日期）。
22. 多单元格合并：&和PHONETIC的"分工战"
场景：合并姓名（A列）、电话（B列），或合并多列备注（A4:K4）。
公式：
- 基础合并：=A4&"-"&B4（结果："张三-13800138000"）；
- 多列合并：=PHONETIC(A4:K4)（仅合并文本，忽略数字/公式结果）。
五、进阶应用：从"普通职员"到"数据高手"
24. 双向查询：VLOOKUP+MATCH的"黄金CP"
场景：从成绩表（A列姓名，B列语文，C列数学...）中，查"张三"的"数学"成绩。
公式：
=VLOOKUP(G4,$A$3:$E$9,MATCH(H4,$A$3:$E$3,0),0)
// MATCH(H4,A3:E3,0)会自动查找"H4"（数学）在标题行的列号（如C列是第3列），VLOOKUP用这个列号返回结果。
25. 多条件求和：SUMIFS的"降维打击"
场景：统计部门A且销售额>1万的订单金额（A列部门，B列销售额）。
公式：
=SUMIFS(B:B,A:A,"部门A",B:B,">10000")
// 对比SUMIF：SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2...)。
26. 中英文分离：LENB+LEFT/RIGHT的"文字游戏"
场景：从混合文本（A列）中提取中文（如"Apple苹果"→"苹果"）和英文（→"Apple"）。
公式：
- 中文：=LEFT(A4,LENB(A4)-LEN(A4))（LENB统计字节数，中文占2字节，英文占1字节，差值即为中文字符数）；
- 英文：=RIGHT(A4,2*LEN(A4)-LENB(A4))（总长度-中文字符数×2=英文字母数）。
六、实用小技巧：从"手忙脚乱"到"从容不迫"
27. 日期与星期：TODAY+TEXT的"时间管理师"
场景：在报表中显示"今天日期"和"今天星期几"。
公式：
- 今天日期：=TODAY()（自动更新为系统当天日期）；
- 今天星期几：=TEXT(TODAY(),"aaaa")（输出"星期四"）。
28. 文本日期组合：&+TEXT的"格式大师"
场景：将A列的"20250904"转为"2025-09-04"。
公式：
=A4&TEXT(B4,"-00-00") // 假设B4是"0904"
// 更严谨版：=A4&"-"&TEXT(B4,"00-00")
技巧：TEXT里的0是占位符，"00-00"能确保月份/日期不足两位时补0（如"9"→"09"）。
七、千万别学excel学习建议：从"死记硬背"到"灵活运用"
- 先模仿后创新：找10份真实报表，用公式直接套用到自己的工作中（比如用SUMIFS统计自己的月度开销）。
- 拆解公式逻辑：遇到复杂公式（如=SUMPRODUCT(($A$4:$A$9=G4)*($B$3:$E$3=H4)*$B$4:$E$9)），用F9键分段计算（选中部分公式按F9，查看中间结果）。
- 建立"公式库"：把常用公式按场景分类（如"查找类""统计类"），存在备忘录里，遇到问题直接搜关键词。
八、高频问题答疑
Q：VLOOKUP总报错#N/A，怎么解决？
A：3步排查：① 检查查找值是否在查找区域的第一列；② 确认第4参数是0（精确匹配）；③ 用TRIM函数清理文本中的空格（如=TRIM(A4)）。
Q：SUMIF和SUMIFS有什么区别？
A：SUMIF是"单条件求和"（=SUMIF(条件区域,条件,求和区域)），SUMIFS是"多条件求和"（=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2...)）。
Q：PHONETIC函数合并不了数字，怎么办？
A：PHONETIC只能合并文本，数字会被忽略。可以把数字转为文本（选中列→设置单元格格式→文本），或用TEXT(数字,"0")强制转文本后再合并。
这些公式不是"花架子"，而是能直接解决你工作中90%痛点的"效率武器"！建议收藏本文，遇到问题随时翻。
测试题（答案见文末）
- 要统计部门A且销售额>1万的订单金额，应使用哪个公式？ A. =SUMIF(A:A,"部门A",B:B) B. =SUMIFS(B:B,A:A,"部门A",B:B,">10000") C. =VLOOKUP("部门A",A:B,2,0) D. =COUNTIFS(A:A,"部门A",B:B,">10000")
- 从身份证号"32010219900307XXXX"中提取生日，正确的公式是？ A. =MID(A2,7,8) B. =TEXT(MID(A2,7,8),"0-00-00") C. =--TEXT(MID(A2,7,8),"0-00-00") D. =DATEDIF(MID(A2,7,8),TODAY(),"y")
- 要合并A列姓名和B列电话（用"-"分隔），最方便的公式是？ A. =A2&B2 B. =A2&"-"&B2 C. =VSTACK(A2:B2) D. =TEXTJOIN("-",,A2:B2)
答案
- B（SUMIFS支持多条件求和，符合"部门A+销售额>1万"的要求）。
- C（MID提取8位生日字符串，TEXT格式化为"0-00-00"，--将文本转为数值日期）。
- B（&连接符+自定义分隔符，直接合并两列内容）。
欢迎关注：千万别学excel，这里有数千篇excel相关视频和文章，带你玩转excel！祝你早日升任CEO，迎娶白富美，走上人生巅峰～(￣▽￣～)~