IF函数用法数例

作者:gouweicao78来源:www.exceltip.net推荐讲师:冯美旺发表于:2011-03-07

IF函数可以说是最常用的函数了,很多人都会使用,不过真要用好IF函数并没有看起来的那么简单。下文所举的各种例子,有哪些是您会的,又有哪些值得您借鉴的呢?

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

  1、冗长繁杂型:

  (1)多余的判断,比如:ISNUMBER、ISERROR、ISNA等信息函数本身返回的就是逻辑值,一些习惯编程的人,经常会写出=IF(ISNUMBER(……)=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,"")

  这两个都是中规中矩的按照常规写法来做。下面从思路的跳跃方面进行分析:

  利用逻辑值与数值关系的互换技巧

  【前提】既然是A1/B1的计算,当然不会想在A1输入“中国”、B1输入“日本”这样的数据,而是输入数值。

  【技巧】既然是数值,在逻辑判断中,就可以利用逻辑值与数值的转换关系来解题。

  【解题】
  1、=IF(B1,A1/B1,"")——利用B1是数值,当B1是空单元格或0时,0等价于FALSE。

  2、=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)

  对于解释1、解释2,很明显解释1我们容易理解,解释2似乎枯燥了些,没关系,它实际上是一个可以拓展的思路,包括数组公式中的条件相乘,不正是我们汉语理解里面“条件1成立且条件2成立且……条件n成立”吗。

  下面的例子说的是逻辑位置的变换:

  【举例1】还是公式=IF(B1<>0,A1/B1,""),我们看看变身:=IF(B1=0,"",A1/B1)省略了1个字符,呵呵。

  【举例2】公式=IF(A1>=20,3,IF(A2>=10,2,1)),变身:=IF(A1<10,1,IF(A2<20,2,3))省略了2个字符,呵呵。

  这是两个很简单的例子,从运算量的角度来说,并没有多大的区别,但逻辑条件稍稍一改,公式就短了。这逻辑条件还不是我们设的嘛,先说A再说B,还是先说B再说A,如果结果没啥两样,而又有简单方法,那么避繁就简。别看这小小省略字符,好像非参加竞赛弄个最短公式不可,事实上这些解题对人的思维方式锻炼也是很有用的,关键时刻,还可以解决困难,比如减少1层嵌套,见:

  【举例3】学了数组公式的朋友都很经常看到类似=SMALL(IF(A$1:A$100="张三",ROW($1:$100),65536),ROW(1:1))的公式,有时候在复杂的数组公式中,往往要用到ROW(INDIRECT())等多层嵌套,使用Excel2003的朋友,总在担心7层嵌套限制问题。首先我们分析一下这个公式,意思是满足条件是返回对应行号、否则返回一个大的数字(常用65536来返回那一行的空白单元格,实际上多数人表格根本用不到1万行),那么只要返回的“大数”在范围内,也无所谓65536,主要问题是要ROW($1:$100)怎么弄出来。

  变身1:

  =SMALL(IF(A$1:A$100<>"张三",65536,ROW($1:$100)),ROW(1:1))

  ——咦?举例1不是刚刚费劲要变短的嘛?

  变身2:

  =SMALL((A$1:A$100<>"张三")*60000+ROW($1:$100),ROW(1:1))

  ——哈哈,原来变身1是醉翁之意不在酒啊。

  看一下,少了个IF函数,少了1层嵌套,耶!

↵ 返回文章列表