引言

哈喽，艾瑞贝蒂🧑🧏

我是Connie，一名冲浪爱好者，每天在WPS社区参与冲浪解答。前两天社区里一位小伙伴发了个问答贴（点此看详情），求助根据提供的物流报价单计算物流费用。

让我们看看如何利用WPS完成物流自动核算查询。

声明：本文示例中的数据均为虚构，如有雷同，纯属意外。

物流报价单

物流自动核算查询界面

期望达到效果如图所示

选择“省份”、“到站地”，输入“重量”之后，会自动根据所填写的信息在“物流报价单”中匹配对应的“单价”，计算出物流合计金额。

✅原始数据分析

根据“物流报价单”中的信息可以得知：

1. 不同“省份”、不同“到站地”、不同重量的物流单价是不一样的。

1. “省份”与“到站地”之间有关联关系，但是每个“省份”对应的“到站地”个数不一样，有1个（比如：“上海”、“北京”），也有2个。

1. “到站地”为“其它地州市”在多个“省份”中出现。

这份“物流报价单”的格式填写不规范，导致我们在处理数据的时候存在一定的困难，最好的方式当然是重新按规范的格式编写“物流报价单”，然后再根据新的“物流报价单”进行数据的提取，但是作为使用单位有时候未必会愿意配合重新编写。

当我们拿到这样一份不规范的数据，是否也可以达到我们期望的效果的。跟着我一步步做。

✅设计思路

📌创建关联关系表及一二级菜单联动

【目标】“省份”、“到站地”之间存在关联关系，设置“省份”为一级菜单，“到站地”为二级菜单，当一级菜单更改时，二级菜单随之更改，一级二级菜单实现联动。

【操作步骤】

（1）将“物流报价单”中的“省份”、“到站地”两列信息复制到一个空白表格中，并在“上海”、“北京”下新增一行空白行。（注：后续数据处理需要用到）

（2）数据处理

①在C2单元格输入“=B2”，在D2单元格输入“=B3”，选中C2:D3，进行填充。此步目的是将省份下的到站地都转置成一行显示。填充完成后，将C、D列复制并“粘贴为数值”方式粘贴至B、C列，然后将D列以及C列中的“0”删除。

②选中A列，“取消合并单元格”，删除空行。

处理完成后数据如下

（3）设置一级菜单

①在新建的关联关系表中操作。选中（A2:A32）单元格，点击“公式”菜单下的“名称管理器”按钮，输入一级菜单名称“省份”，引用单元选为（A2:A32）单元格。

②选中“物流自动核算查询”表格中的A2单元格，如下图所示，从“数据”菜单选中“有效性”图标，设置数据有效性，有效性条件中的“允许”选择“序列”，“来源”填入“=省份”（注：此处的“省份”对应的是刚才在“名称管理器”中设置的一级菜单的名称）。

（4）设置二级菜单，并与一级菜单联动

①在新建的关联关系表中操作。选中（A2:C32）单元格，点击开始菜单下的查找，选中定位，定位有内容的单元格。

②点击“公式”菜单下的“指定”按钮，在指定名称对话框中选择“最左列”。完成二级菜单的名称及与一级菜单的关联关系的设置。

③选中“物流自动核算查询”表格中的B2单元格，如下图所示，从“数据”菜单选中“有效性”图标，设置数据有效性，有效性条件中的“允许”选择“序列”，“来源”填入“=INDIRECT(A2)”。（注：A2单元格未选中内容时，确定后可能会弹出源数据有误的提示框，确定即可。）

菜单实现的联动效果如下，选中“省份”后，“到站地”下拉列表会自动关联对应“省份”下的“到站地”。

📌单价自动获取

（1）已知省份、到站地、重量，获取单价

根据省份、到站地，定位到到站地所在的行号；再根据重量所在的范围，确定所在的列号。行号和列号组合，就可以得到单价。

定位行号的公式：

IF(B2="其它地州市",MATCH(A2,'Price List'!$A$3:$A$62,0)+1,MATCH(B2,'Price List'!$B$3:$B$62,0))

公式解释：

如果B2为“其它地州市”，则行号为A2所在行号+1，否则返回B2所在行号。

定位列号的公式：

MATCH(C2,{0,1001,3001,5001,8001,15001,20001,25001,30001},1)

公式解释：

在数组{0,1001,3001,5001,8001,11001,15001,20001,25001}中查找小于或等于C2的最大数值所在的位置。

根据行列号获取单价的公式：

INDEX('Price List'!$C$3:$K$62,IF(B2="其它地州市",MATCH(A2,'Price List'!$A$3:$A$62,0)+1,MATCH(B2,'Price List'!$B$3:$B$62,0)),MATCH(C2,{0,1001,3001,5001,8001,11001,15001,20001,25001},1),1)

公式解释：

在“物流单价表”的C3:K62区域中，根据行号、列号索引返回对应单元格的值。

（2）对特殊情况进行处理

①如果A2为空时，“单价”返回“请选择省份”。

②如果A2不为空，但是B2中显示的内容不是对应“省份”下的“到站地”，即出现错误时，“单价”返回“请选择到站地”。

判断出现错误的公式：

ISNA(HLOOKUP(B2,INDIRECT("Sheet3!A"&MATCH(A2,Sheet3!A:A,0)&":C"&MATCH(A2,Sheet3!A:A,0)),1,FALSE()))=TRUE()

公式解释：

先找到A2在关联关系表（Sheet3）中所在的行号，然后查找B2是否在该行中，如果不在则返回N/A。

③如果C2为空时，“单价”返回“请输入重量”。

⭐⭐⭐综上所述，完整的公式如下：

=IF(A2="","请选择省份",IF(ISNA(HLOOKUP(B2,INDIRECT("Sheet3!A"&MATCH(A2,Sheet3!A:A,0)&":C"&MATCH(A2,Sheet3!A:A,0)),1,FALSE()))=TRUE(),"请选择到站地",IF(C2="","请输入重量",INDEX('Price List'!$C$3:$K$62,IF(B2="其它地州市",MATCH(A2,'Price List'!$A$3:$A$62,0)+1,MATCH(B2,'Price List'!$B$3:$B$62,0)),MATCH(C2,{0,1001,3001,5001,8001,11001,15001,20001,25001},1),1))))

菜单实现效果如下，未选择省份时，单价处会提示“请选择省份”；如已选择省份但未选择到站地，或已选择省份但到站地选择错误时，单价处会提示“请选择到站地”。

本案例所用到的文件【点此查看】

往期作品：

【AI模板】AI助力校园社团活动

【AI模板】高效！省心！AI模板让你轻松搞定演讲稿创作！

【教育行业案例分享】：我和【老师】的故事：奇妙的AI老师助手

【WPS应用案例】邮件合并快速制作邀请函