湖南龙网

  1. 首页 > 生活百科 > >

excel公式怎么强制返回数组( 二 )

生活百科小知识


=SUM(0+DEC2BIN(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,)))
解析为：
=SUM(0+DEC2BIN(OFFSET(A1,{1;2;3;4;5}-1,,,)))
解析为：
=SUM(0+DEC2BIN(OFFSET(A1,{0;1;2;3;4},,,)))
解析为：
=SUM(0+DEC2BIN({9.2;1.1;5.5;7.4;3.3}))
解析为：
=SUM(0+{“1001″;”1″;”101″;”111″;”11”})
解析为：
=SUM({1001;1;101;111;11})
结果为：
1225
再提醒一下，虽然以这种方式求二进制之和并不实用，但我们这里重在用其来演示公式技术 。
这种包含OFFSET函数的公式技术可用于多个函数，这些函数最初拒绝数组输入 。在第三个示例中，使用了DOLLARDE函数，公式如下：
=SUM(DOLLARDE(A1:A5,2))
结果为#VALUE!，而公式：
=SUM(DOLLARDE(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),2))
可以正确地处理数组，并得到想要的结果32.5 。
第四个示例：
=IMSUM(COMPLEX(A1:A5,1))
结果返回错误值，而公式：
=IMSUM(COMPLEX(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),1))
解析为：
=IMSUM(COMPLEX(OFFSET(A1,{0;1;2;3;4},,,),1))
解析为：
=IMSUM(COMPLEX({9.2;1.1;5.5;7.4;3.3},1))
解析为：
=IMSUM({“9.2+i”;”1.1+i”;”5.5+i”;”7.4+i”;”3.3+i”})
【excel公式怎么强制返回数组】得到想要的结果：
26.5+5i
但是，这种包含OFFSET函数的公式技术并不是使我们能够强制这些“顽固”的函数产生数组返回值的唯一方法 。在《Excel公式技巧03：INDEX函数，给公式提供数组》中，讲解了从INDEX函数强制返回数组的技术，在这里也可以使用 。上述示例公式的等效的INDEX函数构造如下：
=SUM(0+DEC2BIN(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1)))))
=SUM(DOLLARDE(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),2))
=IMSUM(COMPLEX(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),1))
实际上，因为INDEX函数是非易失性的，所以可以说这个版本的公式更优于OFFSET版本的公式，尽管多使用了两个函数N和IF 。
现在，看看最后一个示例，该示例涉及到很少使用的CELL函数 。在这里，我们尝试与第一个示例中的INDIRECT/ADDRESS函数组合类似的方式使用此函数，即指定参数info_type为“address”来获得要传递给SUM的一组单元格引用（当然是在通过INDIRECT函数处理后） 。公式：
=SUM(INDIRECT(CELL(“address”,A1:A5)))
会解析为：
=SUM(INDIRECT(“$A$1”))
这不是我们想要的结果 。
像上文介绍的那样，先尝试在公式中添加OFFSET函数来解决这个问题：
=SUM(INDIRECT(CELL(“address”,OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,))))
这次仍没有达到我们预想的结果，尽管是数组公式输入，但这里的OFFSET函数并未操作ROW函数构造中的所有元素 。实际上，公式中的：
ROW(A1:A5)-MIN(ROW(A1:A5))
我们希望返回的结果是：
{0;1;2;3;4}
但实际上不是这样，结果是：
0
等价于：
ROW(A1)-MIN(ROW(A1:A5))
只是处理了ROW(A1:A5)数组中的第一个元素 。
奇怪！完全不是我们期望的数组公式，也不确定CELL函数拒绝接受生成数组输入的原因 。不知道有没有人知道？
这样，现在需要采取更多的强制办法：
=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N(INDEX(ROW(A1:A5)-MIN(ROW(A1:A5)),,)),,,))))
解析为：
=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N(INDEX({0;1;2;3;4},,)),,,))))
解析为：
=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N({0;1;2;3;4}),,,))))
解析为：
=SUM(INDIRECT(CELL(“address”,OFFSET(A1,{0;1;2;3;4},,,))))
解析为：

推荐阅读


上一篇：excel怎么获取最大年增长率对应的值

下一篇：冬美人,冬美人图片,各种冬美人图片大全大图