小型物流公司会有托运业务,一般会有指定的线路,在指定时间内进行运输配送。有些商家可能会采取托运方式进行发货,将货品提前放在物流公司,公司统一发货到达目的地。

托运的价格有些是按照体积计算,不同路线,不同体积会有不同价格。

由于同个路线可以进行合并一个车发货,所以有一种计价方式,是依据合并的体积进行计价。

如下图:现在,我们需要计算出客户的报价。

Excel 中 vlookup 搭配 sumif 函数的新用法-风君雪科技博客

完成这个效果,只需要编写函数公式,这里会涉及使用到三个函数。点我领取《35 个常用函数说明》

下面,我们一起来看看。

1、sumif 函数,对相同路线,进行汇总求和。

由于具体报价是对同个路线汇总后,再进行查询的。所以在利用查询函数查询之前,我们需要先用 sumif 函数按条件(相同路线)对体积进行汇总求和。

公式如下:

=SUMIF($B$2:$B$33,B2,$C$2:$C$33)

Excel 中 vlookup 搭配 sumif 函数的新用法-风君雪科技博客

sumif 函数的语法规则:

=sumif (条件区域,条件,求和区域)

对应的也就是 :

条件区域:$B$2:$B$33, 城市这一列

条件:B2, 江门佛山

求和区域:$C$2:$C$33,体积列。

汇总之后,我们需要将体积跟区间进行查询匹配。

2、match 函数,匹配对应位置。

由于参数表是一个二维表,也就是我们需要进行交叉匹配。这里我们先使用 match 函数匹配对应体积区间(0,5,10,20,50)的位置。

Excel 中 vlookup 搭配 sumif 函数的新用法-风君雪科技博客

公式如下:

=MATCH(
   SUMIF($B$2:$B$33,B2,$C$2:$C$33),
   $H$1:$L$1,
   1)

Excel 中 vlookup 搭配 sumif 函数的新用法-风君雪科技博客

match 函数主要用来匹配位置的。

=MATCH (查找值,查找范围,查找类型)

第三参数中,查找类型有三种方式:

0 精确匹配

1 小于

-1 大于

Excel 中 vlookup 搭配 sumif 函数的新用法-风君雪科技博客

对应公式:

查找值:sumif 汇总后的值

查找范围:$H$1:$L$1,不同体积数的列表

查找类型:1 小于。比如说 0-5 这个区间内,体积数值是 2 返回 0 这一列的位置,所以这里要写小于也就是 1。

Excel 中 vlookup 搭配 sumif 函数的新用法-风君雪科技博客

match 函数得出的数值 + 1,对应的位置就是 vlookup 函数中的第三参数 —— 结果需要返回的列数。

所以下面我们用 vlookup 函数查询最终结果。

3、vlookup 函数,返回符合条件的值。

利用 vlookup 函数查询每个路线下的价格。

公式如下:

=VLOOKUP(
  B2,
  $G$2:$L$13,
  MATCH(SUMIF($B$2:$B$33,B2,$C$2:$C$33),$H$1:$L$1,1)+1,
  0)

Excel 中 vlookup 搭配 sumif 函数的新用法-风君雪科技博客

=VLOOKUP (要查找的值,查找区域,要返回的结果在查找区域的第几列,精确匹配或近似匹配)

要查找的值:B2 ,江门佛山

查找区域:$G$2:$L$13 参数表

要返回的结果在查找区域的第几列:根据 match 函数所得的列数 + 1。因为列数从查找列开始数的,所以需要加上 1。

精确匹配或近似匹配:0 精确匹配。

现在,对应的报价就得出来了。

4、总结一下

本文讲解的是,按条件汇总值后,进行交叉近似匹配的公式编写。

❶ sumif 函数先汇总同路线的值;

❷ 针对 sumif 函数得出的结果,利用 match 函数去匹配不同体积数(0,5,10,20,50)的相对位置;

❸ 用 vlookup 函数返回最终结果,match 函数 + 1 得出的结果就是 vlookup 第三参数的列数。

整个完成的过程并不难,这里的关键在于,对需求进行拆解后,利用函数工具,去一步步完成我们的所需效果。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小爽

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,所有文章均包含本声明。