sum函数和sumif函数在使用offset函数产生的三位引用时有舍区别

财务经理M9

最近在努力学习EXCEL函数，扒了论坛里好多经典帖子，看过大家推荐的chenjun版主的一篇帖子《浅谈在引用函数中使用数组参数产生的多维引用及其应用》，第三部分实例中第一个例子用了一条公式求各学生成绩的汇总，公式如下：=SUMIF(OFFSET($C$20,ROW($B$21:$B$26)-ROW($B$20),,1,3),"<>")。我试着用sum替换了一下公式：=SUM(OFFSET($C$20,ROW($B$21:$B$26)-ROW($B$20),,1,3))。返回结果从表面上看起来跟sumif返回的结果一样。而且公式还短点。在此，我想请教一下，sumif和sum在这种情况下有什么区别么？
我也扒出过chenjun版主以前的一段话，讲的很精辟：
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]
[tr] [/tr]

excel的关于引用的帮助中写，可以直接使用三维引用区域的函数有SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV、STDEVA、STDEVP、STDEVPA、VAR、VARA、VARP  和 VARPA。这些函数只能对Sheet1:Sheet3!A1:E10这样直接的三维引用有效，同时这些函数都可以用一个表达式产生的结果数组作参数，所以不能用于由OFFSET或INDIRECT以数组参数产生的所谓的三维引用。 INDIRECT函数和OFFSET函数都不能用直接的三维引用。 可以用由OFFSET和INDIRECT产生的三维引用作参数的函数有一个特点，那就是函数的某些参数可以用引用而且只能用引用，如SUMIF的第1和第3参数、所有数据库函数的第1和第3参数，如果满足了这个条件，那么这个参数就可以用由OFFSET和INDIRECT产生的三维引用，并且函数返回一个数组，元素排列对应每一个引用。 如果某一函数的某个参数既可以用引用又可以用表达式产生的结果数组，那就不能用由OFFSET和INDIRECT产生的三维引用作参数。

我对这段话的理解是sum函数不能使用offset函数产生多维引用的参数，而sumif可以。可是上面的两个公式得到相同的结果后，我就迷惑了。sum函数不也能用于这种情况么？不知道是不是我理解错了，求高人指点。

gdw831001

=SUMIF(OFFSET($C$20,ROW($B$21:$B$26)-ROW($B$20),,1,3),"<>")返回的是一个数组，外面应该在套一个=sum(SUMIF(OFFSET($C$20,ROW($B$21:$B$26)-ROW($B$20),,1,3),"<>"))数组公式,,,,=SUM(OFFSET($C$20,ROW($B$21:$B$26)-ROW($B$20),,1,3))也是一个数组公式offset外面应该在套一个n来降维，，=SUM(n(OFFSET($C$20,ROW($B$21:$B$26)-ROW($B$20)),,1,3))也是数组公式，，个人见解，哈哈

财务经理M9

一、感谢高手的热情指导！
二、我个人愚见是sum函数也可以用于由OFFSET或INDIRECT以数组参数产生的所谓的三维引用，在chenjun版主的那份经典帖子中，同一个列子还使用了subtotal函数公式达到了同样的目的，公式为：=SUBTOTAL(9,OFFSET($C$20,ROW($B$21:$B$26)-ROW($B$20),,1,3))。我也是看了这个公式，才想直接用sum函数替换sumif函数试试的，结果相同，而且公式更简洁一些。但看到此类公式中大多数人都使用了sumif函数来写，所以怕自己对sumif函数理解不够透彻，故有此一问。
三、在写函数的过程中，我对sum、sumif、countif函数有点小体会。我觉得sum函数是后两个函数的根。一般sumif、countif能完成的运算，sum的数组运算也能完成。前两个函数形式更精简，所以更高级，但更高级就意味着对参数选择的条件更苛刻，如合并区域就不能作为其参数，sum虽然公式长点，似乎低级些，但它对参数类型的适应性却更宽广些。它也能完成相关运算，而且运算逻辑是一步一步手写出来的，不是电脑一步到位的，能让人看清高级函数的运算脉络。如1、用条件单元格与条件区域做相等计算，得到一个真假数组；2、用n函数或其他很多方法把真假数组转换成01数组；3、再用01数组与求和区域数据相乘，结果是满足条件的数值保持不变，不满足条件的值变为0；4、最后用sum求和，就实现了sumif的效果。公式形式如：=SUM(N(A15=A15:A23)*B15:B23)。countif就更明显了。1、用条件单元格与条件区域做相等计算，得到一个真假数组；2、用n函数或其他很多方法把真假数组转换成01数组；3、用sum对01数组求和，就实现了countif的效果。公式形如：=SUM(N(H25=$C$24:$C$38))。
四、基于上述考虑，个人觉得sum函数是两个条件计算函数的根，两个条件计算函数是在sum的基础上进化出来的高级函数。所以，儿子能完成的事情，老子一定能完成，而且是按部就班的完成，规规矩矩的完成。因此，我提问的两个公式，个人感觉没啥区别。一个是儿子完成的，一个是老子完成的，而且老子这次完成的比儿子更漂亮。不知道说的对否？
五、我冒昧提出一种说法“函数进化”。也即有些函数体现出了明显的发展进化走向高级的演化趋势。除了上面说的三个外，我还总结了另外几个函数的“进化论”。可能有些牵强，往各位高手指教。第一组：row、column、match。这三个函数都是返回一个位置信息，所以常与查找函数配合从而发挥更大作用。区别是前两个函数返回的是单元格在整个工作表中的位置信息，而match则进化到了返回目标引用在指定区域里的位置信息。第二组：vlookup、hlookup、lookup。这三个函数大家都能了解，根是lookup，那两个是在根的基础上进化出的更精致的函数。这三个函数我自己总结为“查询定位函数”。先是要在目标区域里查找目标值，找到后再对其定位，确定位置信息，最后返回指定位置信息的结果值。第三组：choose、index、offset。这三个函数我自己总结为“定位查询函数”。功能是根据指定位置信息，查找一个数据区域中指定位置的值。Choose是查找一维数组中的特定一个值，index是查找二维数组中的特定一个值，offset是查找二维数组中特定的一个区域，功能不断增强，进化的更加完美。
六、搞清楚这三组函数关系后，我对各位高手写的函数搭配使用的道理才算是稍微明白了些许，而不是硬生生的死记公式。根据上面提到的函数特性，为了取得适应的位置坐标信息，row、column、match函数就常会和vlookup、hlookup、lookup、choose、index、offset这些需要定位的查询函数搭配起来使用。
以上纯属个人愚见，本人学习excel不久，很多东西似懂非懂，论坛里我已经扒了好多金回家，在此写点感悟。希望对各位有用。如有哪里说的不对，还请各位高手指正！

财务经理M9

忘了另外两个返回与位置信息有关值的函数：large、small。这两个函数也常与定位查询函数配合使用，个人觉得很高大上，不好驾驭。

ljchb

结果不一样的。外面再套一个SUM函数就知道了。