如上图是一种很常见的快递费报价单的格式。不同的快递公司在不同的时间,首重的范围可能会有差异,首重的价格会有差异;续重分段方式和计价也会有差异。如果用传统的单元格公式计算价格,公式会很复杂难懂,复用性也不高;特别是遇到续重的分段方式也发生改变时。
有没有一种可能,我们可以构建一个函数,就像使用原生函数一样输入收发地、重量、首重、首重价格、续重分段列表、续重价格表6个参数后就得到想要的快递费?而且任意更改价格表,或者收发地与重量,都能得到正确的快递费。如下图:
从Office365有了高级公式环境(AFE)后,可以很方便地定义这个函数了。
函数主体如下:
SFCOST=LAMBDA(
key,
weight,
key_of_price_table,
first_weight,
first_weight_price,
continue_weight_limit,
continue_weight_price,
LET(
//下文中会有SUBLIST函数的说明
continue_weight,SUBLIST(first_weight,continue_weight_limit),
IF(
weight<=first_weight,
//按首重计费
XLOOKUP(key,key_of_price_table,first_weight_price),
//首重费用+续重费用,下文中会有SFCONTINUECOST函数的说明
XLOOKUP(key,key_of_price_table,first_weight_price)
+SFCONTINUECOST(weight-first_weight,continue_weight,XLOOKUP(key,key_of_price_table,continue_weight_price))
)
)
);
函数本身不复杂,就是重量小于首重的话,快递费为首重的快递费,重量超过首重的话,再加上续 重对应的快递费。函数引用了另外两个函数。一个是SUBLIST
,是自定义的用以计算各段续重的计价范围的函数。如果上图的价格,首重是1,续重对应的重量上限分别是3、10、20、9999(代表无限大),则我们要的计算结果是2,7,10,9979,也就是后一个数减前个数形成的数组。另一个是SFCONTINUECOST
,是自定义的计算续重费用的函数。
SUBLIST
函数如下:
SUBLIST=LAMBDA(
a,
list,
MAKEARRAY(1,COUNT(list),lambda(x,y,INDEX(HSTACK(a,list),1,y+1)-INDEX(HSTACK(a,list),1,y)))
);
SFCONTINUECOST
函数如下:
SFCONTINUECOST=LAMBDA(
weight,continue_weights,continue_prices,
LET(
first_continue_weight,INDEX(continue_weights,1,1),
first_continue_price,INDEX(continue_prices,1,1),
IF(
weight<=first_continue_weight,
weight*first_continue_price,
first_continue_weight*first_continue_price+
SFCONTINUECOST(
weight-first_continue_weight,
DROP(continue_weights,,1),
DROP(continue_prices,,1)
)
)
)
);
函数逻辑也很简单:如果剩余重量小于等于第一个续重区间,就用剩余重量 * 该续重区间的单价;如果剩余重量大于第一个续重区间,就用该续重间区的最大重量 * 该续重区间的单价,再加上按去掉这部分续重得量的剩余重量、去掉第一个续重区间的剩余期间、去掉续重单价表的第一列的剩余价格,以同样的计算逻辑再进行计算的结果。其实就是递归计算: f ( n ) = a + f ( n − 1 ) f(n)=a+f(n-1) f(n)=a+f(n−1)。
SFCOST
函数使用方式如下:
相对于传统函数,AFE函数有定义逻辑更接近业务逻辑,使用也更便捷,更易于重复使用的优点。