如下表所示,小王本月在单位吃了5天早餐、10天中餐、3天晚餐。

补助标准是:早餐:每天3元;中餐:每天5元;晚餐:每天4元。
求:小王本月应得多少伙食补助。
声明:1、以下内容只是为了说明问题,并不表示只有这一种方法。其实,想要得到结果,公式=SUMPRODUCT(A3:A8,{0;3;0;5;0;4})更简洁。
2、本文由JT_man根据Excelhome论坛中的一个贴子整理,其中加了本人对这一问题的理解,不一定正确。要查看原贴请点击这里。
C3单元格中公式是(数组公式,三键齐按:CTRL+SHIFT+ENTER):
=SUM(IF(N(IF(1,A3:A8)),A3:A8)*{0;3;0;5;0;4})
公式计算结果是:0,不能计算出结果。
D3单元格中公式是(数组公式):
=SUM(IF(N(IF({1},A3:A8)),A3:A8)*{0;3;0;5;0;4})
公式计算结果是:77,计算结果正确。
上述两个公式的唯一区别是:公式中的1,一个没加大括号,一个加了大括号。
问题来了:IF({1},区域)与IF(1,区域)到底有什么区别?
IF(1, 这个1没有大括号,告诉程序我这个是真值(TRUE值),返回的结果是对A3:A8区域的引用, 不是数组。而N()函数对于区域引用只会返回第一个单元格,也就是文本“早”转化为数值后的值,结果是0;IF(N(IF(1,A5:A10)),A5:A10)的返回值就等同于IF(0,A5:A10)的返回值,为FALSE【因为本例IF()函数中省略了第3个参数,即逻辑假时返回的值,而默认的逻辑假时返回值是FALSE】。整个公式返回值等同于数组公式=SUM(FALSE*{0;3;0;5;0;4})的返回值,即6个0的和值,最终结果为0。
IF({1},这个{1}有大括号 ,是告诉程序,{1}代表一个数组,可以自动扩展,{1}后面跟随的是一个数组。{1}扩展为与后面A3:A8同行同列大小的数组,然后再进行真假判断,返回一个新的数组。IF({1},A3:A8)自动扩展为IF({1;1;1;1;1;1},A3:A8),返回数组{"早";5;"中";10;"晚";3},外面加N()函数后返回数组{0;5;0;10;0;3}。IF(N(IF({1},A3:A8)),A3:A8)的结果等同于IF({0;5;0;10;0;3},A3:A8)的结果,返回数组{FALSE;5;FALSE;10;FALSE;3}。整个SUM()函数公式结果等同于数组公式=SUM({FALSE;5;FALSE;10;FALSE;3}*{0;3;0;5;0;4})的结果,所以能得出正确答案。
需要说明的是:
1、真值是不分大小的(除0以外)。因为函数中的真值定义是: 不等于0的任何数,所以有=IF({5}、=IF({10}、=IF({3}与=IF({1}结果相同 。上述公式内{1}中的1也可改为任何非0值,例如改为{123}、{-456}。
2、IF({1}返回的是一个数组,即使{1}后面只有一个数值。例如:=IF({1},5),虽然后面只是一个5, 我们在公式栏抹黑公式,按F9键运行,不难发现,这个5 ,其实就是一个数组, 结果是{5}。
同样,利用IF({0,1}函数中数组的自动扩展功能,能够生成新的数组,并在某些时候发挥很大的作用。
假如,A1:A3单元格中的值分别是1,2,3,B1:B3的值分别是"A","B","C",如下图:
数组公式=IF({0,1},A1:A3,B1:B3)中由于第一参数是水平数组,而第二、第三参数实际上是垂直数组,因此三个参数都会自动扩展成相同的维度和维数,即,扩展成IF({0,1;0,1;0,1},{1,1;2,2;3,3},{"A","A";"B","B";"C","C"}),然后按照第一参数的顺序依次在二、三参数中去寻找相应的值:第一参数的第一个值0(false)得到第三参数的第一个值A,第一参数的第二个值1(true)得到第二个参数的第二个值1,第一参数的第三个值0(false)得到第三参数的第三个值B,依次类推,于是抹黑此公式按F9键运行得到的结果就是{"A",1;"B",2;"C",3}。示意图如下:
数组公式=IF({1,0},B1:B3,A1:A3)与数组公式=IF({0,1},A1:A3,B1:B3)返回的数组是一样的。示意图如下:
基于同样的原理,也就能理解为什么数组公式=IF({0,1},D1:F1,B1:B3)返回的结果会是{"A",2,#N/A;"B",2,#N/A;"C",2,#N/A}了,呵呵。
IF({1,0}函数的应用
可用于VLOOKUP等函数中,一是实现从右向左进行VLOOKUP,二是实现多条件查询。具体介绍可点击这里。