Excel嵌套函数

发布时间:2021-06-02 Excel函数大全

嵌套函数,就是将一个函数作为另一个函数的参数,通过嵌套函数可以将一些简单的函数一层层相扣,从而解决复杂的计算。(在公式中,最多可以嵌套64层函数)

对于较长的嵌套函数,看起来会比较“吓人”,不过不要被其外表蒙蔽,嵌套函数也仅是常用函数的使用,是个“纸老虎”,并没有那么难学。

❶ 嵌套函数的要求和原则

嵌套函数看起来复杂,通常有大批量或重复性的数据处理需求。

因此,写嵌套函数时要保持公式、数据的简洁,便于理解。函数之间互相嵌套的原则:内层函数须符合外层函数参数的规则。

❷ 怎样阅读嵌套函数

阅读嵌套函数最常用的方法就是拆分,像剥洋葱一样,一层层剥开。

括号是函数的一个重要组成部分,一对括号就是一层函数,顺着括号,由外向内一层层剥开,就能看出各函数的结构了。

如下图所示,打开“IF嵌套函数.xlsx”文件,先试着分析一个简单的IF嵌套函数。

学会威力无穷的Excel嵌套函数,新手也能有高手范儿!

 

学会威力无穷的Excel嵌套函数,新手也能有高手范儿!

 

这里使用的公式是

=IF(E2>=80," ☆☆☆☆ ",IF(E2>=70," ☆☆☆ ",IF(E2>=60," ☆☆ ")))

将光标置于公式编辑栏时,Excel会自动根据函数的不同层次,将括号标上不同的颜色。一对括号前后的颜色是一致的,这里由外至内依次是黑色、红色和紫色。

通过分析可以发现,公式中只有一个IF函数 , 但是包含了3层,嵌套了2层,黑色的是最外层,红色是第1层嵌套,紫色是第2层嵌套。

①由最外层可以知道,如果E2>=80,就会输出“☆☆☆☆”,公式运算结束。如果E2<80,继续往下执行。

②此时E2单元格中的值小于80,分析第一层嵌套可知,如果E2>=70,即 80>E2>=70,就会输出“☆☆☆”,公式运算结束。如果E2<70,继续往下执行。

③此时E2单元格中的值小于70,分析第二层嵌套可知,如果E2>=60,即70>E2>=60,就会输出“☆☆”,公式运算结束。

至此,这个公式分析完成。看到这里或许你会产生疑问,如果平均成绩小于60,会显示什么(见下图)。

学会威力无穷的Excel嵌套函数,新手也能有高手范儿!

 

❸ 怎样写出嵌套函数

那么,该怎样写这样的嵌套公式呢?

这样长的公式,看似需要很强的逻辑才能办得到。其实不然。如果公式较短,思路很容易理清楚,可以直接写;如果公式较长,可以采用“先分步,后整合”的方法来写。

打开“素材\判断闰年.xlsx”文件,如下图所示。

学会威力无穷的Excel嵌套函数,新手也能有高手范儿!

 

(1)选函数

① 通过对本案例的分析,如果要实现通过判断输出“闰年”或“不是闰年”,可使用IF函数。

② 判断能被4整除而不能被100整除,或者能被400整除的年份,则需要使用OR函数。OR函数用于在其参数组中,任何一个参数逻辑值为TRUE,即返回TRUE;任何一个参数的逻辑值为FALSE,即返回FALSE。OR函数格式如下。

OR(logical1, [logical2], …)

logical1, logical2,…):logical1是必需的,后续逻辑值是可选的。这些是1~255个需要进行测试的条件,测试结果可以为TRUE或FALSE。

③ 判断能被4整除而不能被100整除,则需要使用AND函数。AND函数用于返回逻辑值。如果所有参数值为逻辑“真(TRUE)”,那么返回逻辑值“真 (TRUE)”,否则返回逻辑值 “假(FALSE)”。AND函数格式如下。

AND(logical1,logical2,…)

logical1:必需参数。要测试的第一个条件,其计算结果可以为TRUE或FALSE。

logical2, ...:可选参数。要测试的其他条件,其计算结果可以为TRUE或FALSE,最多可包含255个条件。

④判断整除,则需要使用MOD函数。MOD函数用于返回数字除以除数后得到的余数,结果的符号与除数相同,其格式如下。

MOD(number,divisor)

number:必需参数。表示要在执行除法后找到其余数的数字。

divisor:必需参数。表示除数。

(2)分步写出公式

年数能被4整除:MOD(A2,4) =0。

不能被100整除:MOD(A2,100)>0。

能被400整除的年份:MOD(A2,400)=0。

(3)逐步整合

能被4整除而不能被100整除的年份:

AND(MOD(A2,4)=0,MOD(A2,100)>0)。

能被4整除而不能被100整除,或者能被400整除的年份:

OR(AND(MOD(A2,4)=0,MOD(A2,100)>0), MOD(A2,400)=0)。

根据判断输出“ 闰年” 或“ 不是闰年”:

=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)>0), MOD(A2,400)=0)," 闰年 "," 不是闰年 ")。