VLOOKUP巧算物流计价

原创作者 | 李锐

微信公众号 | Excel函数与公式（ID：ExcelLiRui）

个人微信号 | （ID：ExcelLiRui520）

VLOOKUP巧算物流计价

有粉丝反馈想看结合具体场景、实用性更强的教程，感谢热心粉丝的建议，今天来个实战案例，满足一下粉丝需求。

既然是实战案例，咱们就分案例描述+数据准备+解决方案+原理解析这四部分展开今天的文章吧。

今天要讲的就是VLOOKUP巧算物流计价的案例，看完觉得好的，记得去底部点个好看再分享给朋友，我会根据大家的反馈调整发文内容及写法。

案例描述

物流行业的计价规则肯定和重量和距离相关，这个案例给出某企业的计价规则，包括不同重量对应的重量单价和出货价。

计价金额=出货价+重量单价*重量

下图为不同区域、不同重量对应的重量单价和出货价。

要求计算的数据如下图所示，每行是一笔订单，包含日期、区域和重量，需要你根据这个区域从上图的计价规则里面找到对应的数据，再根据重量是否超过3公斤确定具体的重量单价和出货价。

拿上图第一笔要发往湖北重量15公斤的订单来说，区域是湖北，首先从计价表里找到湖北所在行，如下图

然后再看重量15，属于3公斤以上，所以应该按重量价格7和出货价25计算：

计价金额=25+7*15=25+105=130元

明确怎么算了，现在需要计算的订单很多，不可能这样一单单手动计算，想用Excel公式批量自动计算，这应该怎么做呢？

为了方便公式计算，咱们先进行数据准备，再写公式。

数据准备

实际工作中你要计算的区域和公式引用的条件区域很可能不在同一个工作表，甚至可能不在同一个工作簿中，这时你可以在开始动手写公式之前先进行数据准备。

此案例我们就可以把该企业的计价表和需要计算的报表放在同一张工作表中，便于你直观查看和检验结果。

下图是将两表放置在同一张工作表以后的截图效果

（下图为数据准备完毕后效果）

计价规则已经明确，数据也准备好了，再来写公式。

在看下面的解决方案之前，请你先独立思考，带着思路和问题继续向下看。

解决方案

思路提示：解决这个问题的关键点，是如何根据已知的区域和重量从右侧的计价表中调取对应的重量单价和出货价。

由于重量是否超过3公斤决定对应的单价和出货价在不同列，所以VLOOKUP函数的第三参数的条件构建是一个关键点。

计价金额=重量单价*重量+出货价

根据这个计价公式和数据结构规律，可以用下面一个公式搞定计价自动计算。

D2单元格输入如下数组公式，按ctrl+shift+enter三键输入：

=SUM(VLOOKUP(B2,$F$1:$J$26,(C2>3)*2+COLUMN(B:C),)*IF({1,0},C2,1))

如下图所示。

（下图为公式示意图）

一句话解析：

公式中的(C2>3)*2+COLUMN(B:C)作为VLOOKUP第三参数，用于根据重量条件判断返回数据所在列的位置，当重量超过3公斤返回{4,5}，否则返回{2,3}，再用VLOOKUP查找返回的由重量单价和出货价构成的数组*IF({1,0},C2,1)，最后用SUM将重量单价*重量+出货价求和，按照计价公式计算金额。

这里用到了数组多项计算，所以数组公式不能直接Enter回车，而要同时按下ctrl+shift+enter三键输入，关于数组公式和内存数组的系统讲解在函数中级班。

今天就先到这里吧，希望这篇文章能帮到你！更多干货文章加下方小助手查看。