第1个回答 2021-03-25
在咨询公司、VC/PE/Hedge Fund等基金混迹多年,一直靠着Excel的各种技巧安身立命和升职加薪。可能是因为程序员出身的原因,在学会以上各个答案提到的装逼炫酷图表、快捷键和一些略复杂的函数(VLookup等)之外,总是希望从更深的层次去探索Excel及各类Office软件,直到遇到了数组函数和VBA编程。这些技能一旦掌握能将工作效率提高数倍甚至是十倍以上,然而周围却很少有人掌握。如果对这些技能感兴趣,可以关注这个活动:粉丝回馈:程序让你更性感 - 数据冰山 - 知乎专栏。
数组函数和VBA编程,简直就是为程序员而生的,数组函数充满了数据库的思维,而VBA本身就是彻头彻尾的编程,再加之各种接口,能够将Office各套软件以及OS下的各种功能完美结合在一起。因为Excel+VBA是图灵完备的,最后辅以Excel简单高效的数据呈现界面,所以在我的心目中,Excel+数组函数+VBA,简直就是网页前端+客户端+后台程序+数据库。感觉学会了这些,某种意义上就是成为了Full Stack Developer(全栈工程师),各互联网公司梦寐以求想招到的人。
一、数组函数
数组函数往往会和Index、Indirect及Address等地址相关和数据块相关的函数搭配使用,如果不考虑效率的话,基本可以替代各种SQL语句了。
数组函数之案例1:计算某类产品的总价值
计算AA产品的总价值,替代select sum(产品数量x产品单价) from ... where 产品编号=‘AA’
{=SUM(IF(($B$4:$B$8="AA"),($C$4:$C$8)*($D$4:$D$8),0))}
如果没有数组函数,使用sumif等函数也会很复杂。
数组函数之案例2:挑选不重复的值并计算总和
左边的白色区域是原始数据,右边的彩色区域使用了数组函数的输出区域。数组函数实现了两大功能:
黄色区域:将不重复的name+month筛选出来。
蓝色区域:替代了select sum(tot) from ... group by name, month,将name+month对应的tot进行加总
如果没有数组函数,只能使用Pivot Table等复杂方式,不仅程序开销很大,而且还不够灵活。