vlookup函数练习_为什么职场要学excel函数？看这个案例演示：自动计算快递价格...

在上一篇文章里面，我们讲了如何整理完成一个规范化的表格，以便于下一步的函数计算。

最初的信息内容如图所示。

经过整理，我们得到了表2这样的规范化表格。

现在，我们就通过表2来实现快递费用自动计算，最终实现图中这样的效果：

第一步、制作查询表格

首先，在表1里面制作好查询表格。

表格有2个条件：目的地和重量。

为了避免使用的时候出错，我们先将2个条件分别设置数据验证。

选中B7单元格，点击“菜单栏-数据-数据验证”，在“序列”里面去选取来源，来源在表2里面的B列对应区域。

这样，B7单元格的目的地就实现了下拉菜单选取。

然后，B8单元格要填入重量，就必须为数字，通过数据验证，能够禁止别人输入非数字格式。

选中B7单元格，点击“菜单栏-数据-数据验证”，在“小数”里面选中“大于”，填入“0”。

这样，只要在B8单元格输入文字，就会弹出提示框，并且要求重填。

第二步、写函数公式

一个快递的重量，需要用if函数做个判断，判断重量是否超过首重，如果没超过，就直接是首重费用；如果超过了，就应该是首重费用+续重费用。

比较麻烦的是续重费用！

我们先来看一个示例：

假如快递重量为1.5KG，那么续重的重量是1.5KG-首重1KG=0.5KG。

用int函数对0.5除以1的值进行取整(这里1是续重的标准1KG)，得到0。

因此续重费用应该是(0+1)*6=6(这里6是续重的价格6元),得到6。

但另外还有个问题，这些10、1、6、1数据都是变化的，是根据目的地不同而不同，而且以后也可能进行修改，所以都需要用vlookup函数进行查询引用。

图中案例公式为

=VLOOKUP("上海",B2:F32,4,0)

代表着在B列到F列的第2行和第32行这个区域内，在B列查找“上海”，返回右边第4列，也就是续重价格这一列的值，因此结果为6。末尾的参数0表示精准查询。

将这些内容综合起来，我们可以写出一个完整的函数公式。

注意：虽然这里数据很多用的是1，但考虑到这些价格标准随时可能会调整，因此不应该在函数公式里直接用1来做计算。否则下一次修改了价格标准，这个函数公式结果就出错了。

表1里的B9单元格公式为：

=VLOOKUP(B7,Sheet2!B2:F32,2,0)+IF(B8

看上去很长，别怕，我们来分解一下。

先看一下文字版的：

第一种条件下

=B7目的地的首重价格+(如果B8重量

第二种条件下

=B7目的地的首重价格+(如果B8重量>=目的地的首重重量，返回续重费用)=B7目的地的首重价格+B7目的地续重费用

续重费用的公式就是将数据全部用vlookup函数进行查询获得。

INT((B8重量-目的地的首重重量)/目的地的续重标准+1)*目的地的续重价格

不过到这一步，还没有结束。

对于数学计算这一类的问题，一定要注意各种临界值的验证。

当B8输入1.5的时候，结果为10+6=16是正确的。

但是当B8输入2的时候，结果为10+12=22，是错误的。

因为2KG，其续重为1KG，应该还是10+6才对。

这里就是临界值出了问题，检查会发现，只有重量为2、3、4、5这些续重1KG的整数倍数时候，会出现多增加1个续重单位的问题。

那么，这种情况怎么办呢？

在int函数部分，我们将B8重量-首重重量这里，再减去一个极小的数字，比如-0.00001，这样int后的结果就不是0，而是小于0，结果就不会出错了。

最终公式为

=VLOOKUP(B7,Sheet2!B2:F32,2,0)+IF(B8

最终，我们只需要在B7里选择目的地，在B8里输入重量，就能自动算出快递费用了。

当然，如果目的地还要精确到市区县，只要有相应的数据，制作为多级下拉菜单就可以了。

总结：这个案例的函数虽然只用到了if、vlookup、int三个函数，但由于涉及到多个查询引用及计算转换，也还是比较考验综合应用能力的，大家可以多多练习，理顺逻辑思路，提高函数处理能力。

《Excel天天训练营》

《Excel天天训练营》是加薪学院专为职场人士研发的excel课程，根据常见办公需求精选案例，从此办公不求人。

目前，课程2.0图文版本已升级完毕，体系更完整，讲解更到位，学员已突破1000人。课程分为三个篇章：第1章-提高效率(15节课)、第2章-精通函数(25节课)、第3章-美化图表(10节课)，共50节内容。同时，课程2.0视频版正在更新中。

注意：购买课程之后，私信发送“333”，获取课程配套的excel案例文件，同步实操练习，学习效果更佳！另外，视频课程现已提供电脑端播放~