动态数组才是Excel中重大转折的基础。 正是有了动态数组,那些“新”函数们:FILTER,SORT,UNIQUE等才会发挥更大的作用。想想一下,如果这些函数不支持动态数组,它们的作用要大打折扣。而且,如果没有动态数组,即使有了LET函数和LAMBDA函数,它们的数据处理能力也至少减半。 而且,有了动态函数后,Excel中的那些“老”函数们也如虎添翼。 说到数组,你可以先将它们当作单元格区域的代名词。 比如,B3:B5就是一个区域,在公式里使用的时候就可以当作一个数组。实际上,你可以这么理解,一个单元格就代表一个数据,一个单元格区域就代表一个数组: 这是一个3行1列的数组。 动态数组的意思是公式的结果本身是动态的,可能是一个数据,也可能是一个数组,但是没关系,Excel自己会判断,如果返回一个数据,Excel就在该公式所在的单元格中显示该数据: 如果返回一个数组,Excel也会根据大小,占用相应大小的单元格区域并输出对应的值: 动态数组的好处有很多。 首先,输入数组公式时不用再使用传统三键了:CTRL+SHIFT+ENTER,公式两端还有大括号: 看上去就令人困惑。如果你没有听说过的话,一点也不遗憾,因为在以前,这是避之唯恐不及的大坑。现在很好了,跟普通公式没什么区别,输入公式,回车,完工! 其次,我们不用再关心相对引用和绝对引用了。 以前,我们要想写一个公式,经常需要仔细思考引用类型的问题: 对于初学者来说是个不小的折磨。 现在简单了,只要将需要计算的相关区域放在公式中就可以了: 至于到底使用哪个单元格进行计算,让Excel自己去操心吧。 不过,动态数组最重要的是改变了在Excel中处理数据的方式。 不再需要考虑到底参数是单元格还是区域,也不需要考虑到底返回的是一个值还是一个数组,只要考虑计算逻辑即可。 比如, 在这里,我们不管单元格中计算时具体是哪个单元格相乘,我们的逻辑是: 价格=原价 * 折扣 不管公式是下面的哪个:
反映的都是相同的逻辑:原价*价格。 如果我们使用LET公式就更容易理解了:
这里计算的是B3*C2。
这里计算的是B3:B5*C2:F2。 这两个公式是同样的逻辑,返回值都是原价*折扣,但是根据参数的不同,返回的结果分别是单个值还是数组。 进一步,既然它们是相同的逻辑,就可以统一在一个自定义函数中:
那么下面的公式:
就是用同样的逻辑返回不同的折扣价格结果。 关于动态数组的细节,我们以前介绍过: 这里我们主要介绍一个动态数组对老函数的影响: 传统上,我们使用SUMIFS和SUMPRODUCT进行条件求和。 公式:
计算区域中所有产品名称是A的合计。注意,这里包含所有的小写“a”,因为Excel是大小写不敏感的。 如果要严格区分,SUMIFS函数就不能胜任了。 可以使用下面的公式:
不过,现在,这两个公式都可以使用SUM代替完成:
SUMIFS函数和SUMPRODUCT函数瑟瑟发抖,它们的地盘眼看就要消失了。不过,它们还有一线生机:那就是一次性返回多个计算结果: 假如,我需要在上表中同时计算A,B,C,D的数量: 这个时候使用SUMIFS最简单:
而SUM就完不成这个工作。 但是SUMPRODUCT呢,好像就没有什么必须使用的地方了。 详细解释请看视频 加入E学会,学习更多Excel应用技巧 http://www./portal/learn/class_list Excel+Power Query+Power Pivot+Power BI 自定义函数 底部菜单:知识库->自定义函数 面授培训 底部菜单:培训学习->面授培训 Excel企业应用 底部菜单:企业应用 也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。 |
|