【Excel问伊答205】高手书写Excel函数公式的内在逻辑揭秘

逻辑判断可以算是函数与公式的基本功了，但是不同的人写出来的公式，却也差别甚大，有冗长繁杂的“累赘”，有中规中矩的“易懂”，有天马行空的“精妙”。

我们边看例子边解析

1.冗长繁杂型

1）多余的判断，比如ISERROR,ISNA等IS类函数人本身返回的就是逻辑值，一些习惯编程的人，经常会写出

=IF(ISNA(VLOOKUP()=TRUE,……)

这里跟一个=TRUE是没有必要的。

2）多余的嵌套，比如如果A1小于10，返回1；如果A1大于等于10且小于20，返回2；如果A1大于等于20，返回3。很多初识公式的朋友见到“且”就想到“AND“，没有分析嵌套本身的逻辑关系，经常会写出

=IF(A1<10,1,IF(AND(A1>=10,A1<20),2,IF(A1>=20,3)))

实际上IF(条件，条件为真时返回的结果，条件为假时返回的结果)，上面公式本身第2个IF就是在第1个IF的条件为假的情况下发生的，也就是本身就包含了A1>=10，因此AND是没有必要的，同理，第3个IF也是没有必要的，只需写成

=IF(A1<10,1,IF(A1<20,2,3))

2.中规中矩型

相信多数朋友学习Excel是要“用”而不是要“炫”的。如果自己写一个公式，自己都看不懂，那又怎么“用好”它呢？（当然有朋友会追求尽可能减少公式的长度）
Excel的函数与公式，是很接近“自然语言”的，因此，写好一个公式，实际上相当于“说好一句话”。

在计算以B1为分母的公式，比如=A1/B1，为了避免B1未输入数据会被当做0或者B1输入0产生#DIV/0!错误，我们就会这么说“当B1不为0时，才进行A1/B1计算，否则不显示

那么写出的公式就是：

=IF(B1<>0,A1/B1,””)

这公式简单易懂。那如果还要规定A1未输入数据或输入0时，也不显示，那么公式可写成：

=IF(AND(A1<>0,B1<>0),A1/B1,””)

这两个都是中规中矩的书写规范来做的。

3.天马行空型（思维跳跃）

1）利用逻辑值与数值的转换关系，还是用上面的的例子，既然是A1/B1的计算，当然不会想在A1输入“中国”、B1输入“足球”这样的文本，而是输入数值。既然是数值，在逻辑判断中，就可以利用逻辑值与数值的转换关系

那么上面两个公式可写成：

=IF(B1,A1/B1,””)

B1引用的是数值，当B1单元格为空或输入0时，B1=FALSE,（因为在逻辑判断是0=FALSE)

=IF(A1*B1,A1/B1,””)

这是啥公式啊？本身计算的除的怎么又冒出了乘？

解释1：如果A1或B1中有一个0，A1*B1都会等于0而被视为FALSE；

解释2：还是数值与逻辑值的关系，把A1看成一个条件，逻辑判断中那么A1<>0可以直接用A1代替，也就是AND(A1<>0,B1<>0)可以直接用AND(A1,B1)；或者看成单值计算中的条件相乘(A1<>0)*(B1<>0)

2）利用逻辑位置的变换，还是以公式=IF(B1<>0,A1/B1,””)为例，我们写成：=IF(B1=0,””,A1/B1),你会现已经少了一个字符。

上面的公式变身，从运算量的角度来说，并没有多大的区别，但逻辑条件稍稍一改，公式就短了。这逻辑条件还不是我们设的嘛，先说A再说B，还是先说B再说A，如果结果没啥两样，而又有简单方法，那么避繁就简。

别看这小小省略字符，事实上这些解题对人的思维方式锻炼也是很有用的，关键时刻，还可以解决困难，比如减少1层嵌套。见例：

B1=SMALL(IF(A$1:A$8<>”surda”,ROW($1:$8),100),ROW(1:1))

C1=SMALL((A$1:A$8=”surda”)*100+ROW($1:$8),ROW(1:1))

首先我们分析一下公式，意思是满足条件则返回对应行号、否则返回一个大的数字（这里我设为100），我们可以看到C1公式比B1公式少了一个IF嵌套，但执行结果是同样的。

常规排序法

=SMALL(IF(满足条件，ROW，大数)，row(1:1))

利用逻辑位置的变换，排序公式变身

=SMALL((不满足条件)*大数+ROW),row(1:1))

小结：以上介绍了Excel中逻辑判断的相关概念以及书写公式的不同方法所带来的不同效果，具体是属于哪一类型，看自身的条件和实际情况^举例，但作为Excel公式编写者，我们力求在效果的一致的前题下，保持函数公式的长度最短。~_^,

[举例]=IF(A1>100,A1+B1,0) 和 =(A1>100)*(A1+B1)，这两个公式执行的结果是一样的。但前者在A1<=100的情况下是直接返回0的，而不用计算A1+B1这个表达式，后者则任何情况下都必须计算A1+B1。如果将A1+B1替换为一个复杂运算，那么效率差异就会很明显了。

所以，在书写一些逻辑判断的公式时，就结合实际情况用”符号运算符”代替”IF”，因为，IF有个优势，它只计算条件表达式返回true或false所对应的第二或第三个参数的其中一个表达式，而不会两个都计算，提高公式执行的效率。

最近几篇微信文章，想看的话，回复如下关键字词：

想看“有效性的bug如何禁止”，请回复：有效性

想看“几个工作簿数据并到一起”，请回复：合并

想看“公式运算结果出现了错误该怎么办”，请回复：错误

想看“VLOOKUP函数的情感故事，情诗写给谁”，请回复：找到你

想看“IF函数内心的秘密私语”，请回复：如果爱

想看“随机函数是个啥杂用呢”，请回复：随机

想看“忘记了excel保护密码怎么破”，请回复：破

想看“30岁的梦想，只是年薪10万，你实现了吗”，请回复：梦想

想看“各种基础excel技巧几个100例”，请回复：技巧

想看“根据姓名查找出女生的照片”，请回复：照片

想看“个人所得税里的速算扣除数的秘密！”，请回复：个税

想看“如何用PPT制作抽奖的大转盘”，请回复：转盘

想看“用PPT进行图文排版或一对多VLOOKUP查询”，请回复：图文

想看“几十个好用的函数公式是什么”，请回复：好函数

想看“vlookup非首列的查询怎么办”，请回复：非首列

想看“indirect函数怎么用”，请回复：indirect

想看“隔行插入怎么做”，请回复：隔行

想看“sumproduct函数有哪些用法”，请回复：sumproduct

想看“财务上的万元单位怎么显示”，请回复：万元

想看“珍藏10年的excel技法，高人看懂”，请回复：十年

想看“智能动态过滤有效性是啥来头，噱头”，请回复：过滤

想看“了解一些ppt资源和ppt制作技巧”，请回复：PPT资源或PPT

想看“工资条的制作方法”，请回复：工资条

想看“50个吊炸天的excel技巧”，请回复：基础

想看“个人所得税的算法”，请回复：个税

想看“财务数字的分栏如何一个函数公式搞定”，请回复：分栏

想看“index函数和match函数的用法”，请回复：index

更多关键词查询，请自己搜索历史文章慢慢看，或者自己尝试用一些关键字词搜索，一般是两个字。

【点滴积累，每天进步】

{想看更多李则见老师亲自编写的微信文章，

请点击“人形图标”查看历史消息…}

————————————————————————–

以Excel、PPT、Word、思维导图等软件学习、研究、交流为发轫，开启您职场高效能之旅，提升您的结构思考力，让职场生活更加高效开心畅通！【Office办公软件职业讲师李则见创立维护，课约助理qq976278411】

————————————————————————–

看完后觉得好，请不吝您的评价，点赞！！！你的点赞是我的动力！

赠人玫瑰之手，经久犹有余香。

The roses in her hand,the flavor in mine.

———————————————————–—————

下面是我的绝对经典excel课程售卖，以下是我的隆重推荐！！！

【省了789元，毁了你一生，改变的机会就在这里！】

魔法一样的Excel-数据达人的脱胎换骨通关绝技

原价1788元，现在只需要789元，这个最低价格只在这个元旦小长假期间才有！

购买链接，点击最底部“阅读原文”

学完之后将使你在excel领域建立绝对优势，

改变你的2015年整个职场发展轨迹！！！

适用人群：

职场上需提升Excel【综合实战技能】的朋友们（最好有初级的Excel操作基础），Excel超级小白的朋友们请飘过

简介

【本套课程视频左下方都有#练习案例#，请购买学员务必完成练习在qq群内提交，鼓励创新】

【视频细节化讲解操作】

【案例下载实操，购买后观看窗口左下角可以下载实操案例】

【综合实战的内容】

【不是小白入门课程】

【也不是高级的复杂的难懂的课程】

【市场价值20000元左右】

【最实战最综合最易学最经典】

【一旦购买，无限期收看】

欢迎投稿 职场/创业方向. 邮箱wangfzcom(AT)163.com：王夫子社区 » 【Excel问伊答205】高手书写Excel函数公式的内在逻辑揭秘

标签：excel