计算快递费更优雅的一种方式－结合Power Query与AFE（高级公式环境），用一张表统一所有报价，并只用一个函数计算所有情况下的快递费。

本文链接：https://blog.csdn.net/weixin_38775843/article/details/135074267

本文讨论了不同快递公司的报价表差异，并揭示了它们共有的计价逻辑。通过标准化过程，提出如何使用PowerQuery将各种格式的报价表转换为统一格式，以便高效批量计算运费。作者还提供了自定义函数，实现快速且灵活的快递费用计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

什么格式的报价表才是我们真正所需要的？

不同的快递公司，甚至同一家快递公司的不同业务，他们的报价表都会是不一样的，这使得我们在计算快递费的时候感到非常头疼。

以下4个报价表就各有各的特点：
Pasted image 20231218211317
Pasted image 20231219000352
Pasted image 20231218211349
Pasted image 20231218211403

A快递公司3KG以下根据重量所在的区间，各有对应的一口价。3KG以上再按每KG收续重费用。
B快递公司根据包裹的重量，分别有不同的首重、首重运费、续重运费。
C快递公司则将相同价格的省份合并到了一起，但其计价其实是最简单，1KG以上首重一口价，超过1KG再收续重费用。
D快递公司1KG以下按首重一口价，超过1KG，则以阶梯定价的方式分段计算续重费用。

要批量化地，高可复用性地解决问题，必须要能找出不同事物所共用的逻辑。

这些报价虽然看似各不相同，但还是可以找到它们的共通之处的。特别是A、B、C这三家，他们有明显的共同的逻辑：

当我们知道了包裹的重量，首先判断这个重量落到计价的哪个区间内？是0-1，还是1-2，还是3- $\infty$ 。
根据区间，找出对应的计算参数。
根据它落在区间找出对应的首重，首重可能是区间的上限（如A快递0-3KG内的区间），也可能是区间的下限（如B公司3-5KG），也有可能是单独的一个值（如B公司0-3KG）。
根据它落在区间找出对应的首重价格。
根据它落在区间找出对应的续重价格,对于不计算续重的情况则没有，或者为0。
计算快递费用： ${首重价格}+（{包裹重量}-{首重}）\times {续重价格}$
D公司可能稍微复杂一点点。如果包裹重量超过了3KG的话，它就没有直接的3KG（或者更高，5KG，10KG）的首重价格了。但是很容易计算出来3-5KG，这个区间的首重价格，就是 ${1KG首重价格}+（3-1）\times {(1-3KG续重价格)}$
所以，这4家快递公司的报价表，看似各不相同，其实还是有一套共用的逻辑的。只不过是在具有使用的时候，各自演生出了一些不同的变化而已。

这样就清楚了，我们其实只要构造如下这张标准的价格表，就可以把上述所有快递报价容纳在里面。 Pasted image 20231218215715
比如，我们要计算A快递从浙江发往江苏3.5KG的包裹的费用。只需要通过筛选这张价格表，找到上图中红框内的两行数据，就可以提取到首重3、首重价格0.54、续重价格0.10这几个计算参数。

怎样构建上面这个报价表？

因为一行上要分出不同的快递公司、价格区间、始发省、目标省、计价项目，显而易见的，这张表的行数会非常之大，很难用手工完成。即使用手工完成了，也不便于以后再更新报价。所以，我们需要的一个自动把上面这4张不同格式的二维格价表，转换成1张统一格式的一维格价表的功能。

很显然Power Query（下文简称PQ）是最适合干这个的。

虽然PQ很强大，但是为了方便起见，我们还是从A、B两表稍做一些优化，再使用PQ进行转换。

将A快递报价表头稍加修改，优化成如下格式：
Pasted image 20231218220743

将B快递报价表头也稍加修改，优化成如下格式：
Pasted image 20231218220809

A、B 快递报价表的转换：

读取工作表。
将快递公司、始发省、目标省三列合并成一列，合用"-"进行连接。
转置表，对重量范围、首重两列进行向下填充，并提升第一行为标题。
选中前三列，并操作逆透视其他列。
将重量范围进行拆分列，分到下限与上限。对快递公司、始发省、目标省合并出来的那一列也进行同样的拆分，进行还原。
对各列进行重命名和设置数据格式，便得到我们所需要的格式了。

C 快递报价表的转换

读取原表后，到标题进行修改，修改结果如下：
添加一列 $1-\infty$ 对应的首重价格。因为表上没有直接指出，为了让计算机看懂，需要人为明示给它。
其他逆透视与拆分列的操作与A、B公司差不多，不再赘。对于省份填在一个单元格的问题，只需要添加一个自定义列，对目的省进行文本拆分成列表，再将列表扩展到新行即可。新手同学如果没看明白的，可以下载文末的附件进行查看。
其他修改列名与数据格式的操作，参照A、B公司即可。最后再添加一列[快递公司]，值为“C快递”

D快递报价表的转换

D快递报价表的转换与C也基本很类似，要注意的要从列名进行修改，使得我们可以从列名中拆分出我们所需要的数据。 Pasted image 20231218224907

另外，对于 1-3、3-5、5-10、10-20、 $20-\infty$ ，这几个区间要分别添加列，以描述首重价格。其中：1-3的首重价格就是1KG的价格；后面3-5的首重价格就是，前面的1-3的首重价格再加上（3-1）*（1-3的续重价格）。
$\times {（上限3-下限1）}$ 后面的也依此类推。

其他步骤与C公司相同，最后要添加上一列[快递公司]，值为“D快递”。还要再加上一列[首重]，值等于[下限]。

拼接4个快递报价

将上述4个转换好的价格表，进行追加查询为新查询的操作，并对列和行进行适当的排序调整，便得到我们最终想要的统一格式的报价表了。

自定义函数计算快递费

打开AFE，写入自定义函数如下：

EXPRESSFEE=
    LAMBDA(
        company,start,end,weight,price_table,
        /* 
        请保持price_table的列依次为：快递公司,始发省,目标省,下限,上限,计价项目,价格,首重
        */
        LET(
        company_list,CHOOSECOLS(price_table,1),
        start_list,CHOOSECOLS(price_table,2),
        end_list,CHOOSECOLS(price_table,3),
        low_list,CHOOSECOLS(price_table,4),
        high_list,CHOOSECOLS(price_table,5),
        //按快递公司、始发省和目标省这3项，筛选相关的行。这里面是包括了跟所有包裹重量有关的计价标准的行。
        area1, FILTER(price_table, (company_list = company) * (start_list=start)*(end_list=end)),
        //保留第5-8列，也就是：上限,计价项目,价格,首重 这4列。
        area2, CHOOSECOLS(area1, 5, 6, 7, 8),
        weight_limit, CHOOSECOLS(area2, 1),
        //将XLOOKUP的第5个参数选1，则会匹配大于或等于weight的最小的那个数。比如2.5KG没有相应的首重的，所以需要查找大于它的最近的首重，可能会是3KG。
        nearset_weight, XLOOKUP(weight, weight_limit, weight_limit, , 1),
        /* 筛选出上限等于nearset_weight的行。这里面所有的行，就都跟我们最终计算价格相关了。
        一般会有两行，一行是首重，另一行是续重。也有可能只有一行首重。*/
        related_row, FILTER(area2, weight_limit = nearset_weight),
        //首重价格所在的行
        first_weight_row, FILTER(related_row, CHOOSECOLS(related_row, 2) = "首重价格"),
        //续重价格所在的行，也有可能不存在的。
        continue_weight_row, FILTER(related_row, CHOOSECOLS(related_row, 2) = "续重价格"),
        //首重
        first_weight, CHOOSECOLS(first_weight_row, 4),
        //首重价格
        first_price, CHOOSECOLS(first_weight_row, 3),
        //续重价格
        continue_price, IFERROR(CHOOSECOLS(continue_weight_row, 3), 0),
        //统一的计算快递费的公式
        first_price + continue_price * (ROUNDUP(weight, 0) - first_weight)
    )
)

Pasted image 20231218231535

该公式各步骤的含义已经在释注文本中了。

现在我们就可以自由地使用这个函数了。
当我们在单元格中输入“=EXPRE”，系统就会提示有EXPRESSFEE这个函数可以用。
Pasted image 20231218231957

我们选择这个函数，紧着它又会提示我们参数需要哪些。如下图：依次为快递公司、始发省、目标省、价格表。
Pasted image 20231218231923

当我们依次输入各参数后，就可以直接得到结果了。 Pasted image 20231218232228

注意：上面写的”快递报价表“，其实是一个命名区域。
我们也可以使用统一快递报价表!$A$2:$H$297进行替换。

至此，即使不同的快递发出情况都出现在在一个表里，我们也可以使用一个一致的公式进行统一计算了。感觉还是挺优雅的，就如下图：
Pasted image 20231218233004

即使以后快递报价有了调整，我们也只需要修改一下几张报价表的源表，再刷新一下PQ，就又可以得到一个新的统一格式的报价表了，又可以进行统一计算了。

示例文件下载

链接：百度网盘
提取码：1234