近两年,Excel函数我认为经历了跨越式发展。第一个跨越是溢出函数,一个函数可以突破单元格的限制,生成一个区域,如下面的例子:
FILTER函数可以按筛选条件直接生成一个表,UNIQUE可以去除重复值。近期的LET、LAMBDA函数是第二个重大跨越:可以在不编程(无需VBA)的情况下,将复杂的计算过程或者业务逻辑打包成一个自定义函数,进行重复使用。以LAMBDA函数举两个零售业的小例子,请看视频:
视频中生成了两个中文自定义函数,一个叫业绩状态,填入业绩和销售目标自动打对勾或者叉表示业绩是否完成;一个叫货龄(即货物到现在上市了多久),自动按照该商品的上市日期和今天的差值进行分组计算。
业绩状态如果使用传统Excel公式,你每次需要这样输入:
IF(业绩/目标>=1,UNICHAR(10004),UNICHAR(10006))
商品的货龄你需要长窜的公式(IF函数也可):
SWITCH (
TRUE (),
TODAY () - 上市日期 <= 90, "3个月以下",
TODAY () - 上市日期<= 180, "4-6个月",
TODAY () - 上市日期<= 365, "7-12个月",
"12个月以上"
)
实际业务中,你的逻辑可能更为复杂,公式长达数百个字符。在Excel中使用LAMBDA这样的新函数可以将你的计算过程全部打包,生成一个只有几个字符的包裹。后期直接输入包裹名字即可实现全部计算过程。接下来以业绩达成状态为例演示如何操作。
在D列输入以下公式:
LAMBDA(业绩,目标,IF(业绩/目标>=1,UNICHAR(10004),UNICHAR(10006)))(B2,C2)
其中,业绩和目标是计算的参数名称,可以英文也可以中文命名,依据计算的复杂程度,可以是一个或者多个参数;随后是要对参数怎么计算,计算的逻辑和传统的IF语句一致;最后一个部分是计算哪个单元格,我们有两个参数,所以对应选择相应的业绩和目标单元格。
可以看到,计算的结果和直接使用IF语句一致。如果LAMBDA的作用仅仅局限于此,我们也没必要介绍这样一个鸡肋的函数,这增加了公式的复杂度。
接下来是打包这个公式为一个表面简洁函数的过程:在公式选项卡下找到定义名称,定义名称为“业绩状态”,引用位置输入刚才创建的LAMBDA函数(到计算逻辑为止)
这样“业绩状态”函数定义完成,后期在工作簿中就可以复用了。计算货龄的LAMBDA也附上:
LAMBDA(X, SWITCH(TRUE(),TODAY()-X<=90,"3个月以下",TODAY()-X<=180,"4-6个月",TODAY()-X<=365,"7-12个月","12个月以上") )
本文小试牛刀。后期将会介绍更多LAMBDA函数打包零售业务逻辑的案例,通过函数实现类似编程的自动化功能,效率提升。