职场人收藏!5大Excel万能公式对照表,什么情况套哪个一目了然
职场里最常见的Excel场景是什么?答案往往是统计和查找——统计各部门业绩、查找员工信息、核对订单数据。很多人面对这类需求时,要么逐行手动筛选,要么记了一堆函数却不知道该用哪个。其实,Excel高手之所以快,不是他们记得函数多,而是他们清楚知道:不同场景,该用哪个公式。下面把最常用的万能公式按场景整理一遍,看完就知道遇到什么情况该套哪个模板。
多条件统计:一个公式搞定计数和求和
统计类需求是职场最高频的操作——算人数、算业绩、算达标数量。核心痛点在于:条件往往不止一个,比如“销售一部且金额大于500的订单数”或“华东区3月份A产品销售额”。这时候COUNTIFS和SUMIFS就是最趁手的工具。
COUNTIFS用于计数,语法是COUNTIFS(条件区域1,条件1,条件区域2,条件2,…),几个条件成对出现,全部满足才计入。有人用它统计“迟到人数”“某产品出现次数”“达标量”,参数可以无限叠加,用起来很灵活。
SUMIFS用于求和,第一个参数是求和区域,后面跟着一对对“在哪列找、找什么”。它的参数顺序是:先想要求和哪一列,再一对对指定条件区域和条件。这个公式在日常工作中出现频率极高,有人用它汇总“销售部3月业绩”“华东区A产品销量”,熟练之后比数据透视表还快。
需要注意的是,条件区域和求和区域的行数必须一致,否则会报错。文本和日期条件要加英文双引号,数字条件可以直接写比较运算符。
COUNTIFS用于计数,语法是COUNTIFS(条件区域1,条件1,条件区域2,条件2,…),几个条件成对出现,全部满足才计入。有人用它统计“迟到人数”“某产品出现次数”“达标量”,参数可以无限叠加,用起来很灵活。
SUMIFS用于求和,第一个参数是求和区域,后面跟着一对对“在哪列找、找什么”。它的参数顺序是:先想要求和哪一列,再一对对指定条件区域和条件。这个公式在日常工作中出现频率极高,有人用它汇总“销售部3月业绩”“华东区A产品销量”,熟练之后比数据透视表还快。
需要注意的是,条件区域和求和区域的行数必须一致,否则会报错。文本和日期条件要加英文双引号,数字条件可以直接写比较运算符。
多条件查找:复杂查询用什么公式最稳
查找是另一个重灾区。当需要根据两个甚至更多条件查数据时,VLOOKUP就力不从心了。经典解法是LOOKUP函数的多条件写法:LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),结果区域)。原理是每个条件判断返回TRUE/FALSE,相乘后全部满足的变成1,用0除后变0,其他行变成错误值;LOOKUP自动忽略错误,找到最后一个0所在位置并返回对应结果。
多条件查找还有几个替代方案。XLOOKUP是微软推出的升级版函数,语法更简洁,支持左右双向查找,还能直接设置查不到时的提示文字,告别#N/A的尴尬。INDEX+MATCH组合则是资深办公人的备用神器,适配所有版本,灵活度更高,但公式写起来稍复杂。
对于需要提取所有符合条件的结果(一对多查找),经典的INDEX+SMALL+IF数组公式可以实现逐条提取,公式是=INDEX(结果列,SMALL(IF(条件区域=条件,ROW($2:$11),4^8),ROW(A1)))&""。如果是Office 365或WPS最新版,可以用FILTER函数一步搞定,语法是=FILTER(返回区域,(条件1)*(条件2),"无数据"),乘号代表“并且”,加号代表“或者”,理解起来很直观。
多条件查找还有几个替代方案。XLOOKUP是微软推出的升级版函数,语法更简洁,支持左右双向查找,还能直接设置查不到时的提示文字,告别#N/A的尴尬。INDEX+MATCH组合则是资深办公人的备用神器,适配所有版本,灵活度更高,但公式写起来稍复杂。
对于需要提取所有符合条件的结果(一对多查找),经典的INDEX+SMALL+IF数组公式可以实现逐条提取,公式是=INDEX(结果列,SMALL(IF(条件区域=条件,ROW($2:$11),4^8),ROW(A1)))&""。如果是Office 365或WPS最新版,可以用FILTER函数一步搞定,语法是=FILTER(返回区域,(条件1)*(条件2),"无数据"),乘号代表“并且”,加号代表“或者”,理解起来很直观。
逻辑判断和容错处理:让表格自动做决定
想让表格自动做判断?IF函数是最基础的工具,语法是IF(判断条件,成立结果,不成立结果)。单条件判断用它就够了,但遇到多条件组合就需要AND或OR来配合。
需要所有条件同时满足时,用IF(AND(条件1,条件2,…),成立,不成立)。AND像串联电路,全部为真才通过。有人用它判断“业绩≥100万且满意度≥90分才发奖金”这类场景,条件可以一直往后加,非常灵活。
只需要满足任一条件时,用IF(OR(条件1,条件2,…),成立,不成立)。OR像并联电路,有一个为真就通过。比如判断“销售额>5万或好评率100%即可评为优秀”,用这个组合最省事。
还有一个配套技巧:容错处理。任何可能报错的公式外面套一层IFERROR(原公式,"出错时显示的内容"),查不到数据时就不再出现刺眼的#N/A,而是显示你预设的友好提示。这个技巧能让表格专业度提升一个档次。
需要所有条件同时满足时,用IF(AND(条件1,条件2,…),成立,不成立)。AND像串联电路,全部为真才通过。有人用它判断“业绩≥100万且满意度≥90分才发奖金”这类场景,条件可以一直往后加,非常灵活。
只需要满足任一条件时,用IF(OR(条件1,条件2,…),成立,不成立)。OR像并联电路,有一个为真就通过。比如判断“销售额>5万或好评率100%即可评为优秀”,用这个组合最省事。
还有一个配套技巧:容错处理。任何可能报错的公式外面套一层IFERROR(原公式,"出错时显示的内容"),查不到数据时就不再出现刺眼的#N/A,而是显示你预设的友好提示。这个技巧能让表格专业度提升一个档次。
数据去重和合并:快速整理混乱名单
名单去重、汇总同类数据,这类需求也很常见。统计不重复个数,旧版Excel可以用SUMPRODUCT(1/COUNTIF(区域,区域)),新版直接用=COUNTA(UNIQUE(区域))更简洁。UNIQUE函数可以提取唯一值列表,配合SORT还能自动排序。
想把同一类的内容合并到一个单元格里?TEXTJOIN是专门干这个的。比如把所有销售部员工姓名合并到同一格,公式是=TEXTJOIN(",",TRUE,IF(部门列="销售部",姓名列,""))。TRUE参数表示自动忽略空值,结果干净利落。这是数组公式,365版本直接回车确认,旧版需要按Ctrl+Shift+Enter三键。
还有一类常用场景是文本提取和清洗。比如从"订单号AB12345CD"中提取数字,可以用=LOOKUP(9^9,MID(单元格,MATCH(1,MID(字符串,ROW(1:99),1)^0,0),ROW(1:99))*1)。分离汉字和数字则用LENB和LEN的差值——中文按两个字节算,英文按一个字节算,一减就能拆开。
想把同一类的内容合并到一个单元格里?TEXTJOIN是专门干这个的。比如把所有销售部员工姓名合并到同一格,公式是=TEXTJOIN(",",TRUE,IF(部门列="销售部",姓名列,""))。TRUE参数表示自动忽略空值,结果干净利落。这是数组公式,365版本直接回车确认,旧版需要按Ctrl+Shift+Enter三键。
还有一类常用场景是文本提取和清洗。比如从"订单号AB12345CD"中提取数字,可以用=LOOKUP(9^9,MID(单元格,MATCH(1,MID(字符串,ROW(1:99),1)^0,0),ROW(1:99))*1)。分离汉字和数字则用LENB和LEN的差值——中文按两个字节算,英文按一个字节算,一减就能拆开。
Excel公式不需要死记硬背,关键是记住几个核心模板,遇到对应场景直接套用就行。多条件统计用COUNTIFS和SUMIFS,复杂查找用LOOKUP或XLOOKUP,逻辑判断用IF配合AND/OR,容错记得套IFERROR,数据去重和合并有UNIQUE和TEXTJOIN。掌握这几类,日常80%的表格问题都能覆盖。
如果追求更高效率,新版本Excel还有FILTER、VSTACK、LET等新函数可以让公式写法更简洁。但对于大多数职场人而言,熟练用好这几个万能模板,效率已经能提升一大截——剩下的时间,用来陪家人或者提升自己,不香吗?
如果追求更高效率,新版本Excel还有FILTER、VSTACK、LET等新函数可以让公式写法更简洁。但对于大多数职场人而言,熟练用好这几个万能模板,效率已经能提升一大截——剩下的时间,用来陪家人或者提升自己,不香吗?
当前文章无评论,是时候发表评论了