为什么职场要学excel函数?看这个案例演示:自动盘算快递价钱

2021-11-22 00:07 华体会网页版
本文摘要:在上一篇文章内里,我们讲了如何整理完成一个规范化的表格,以便于下一步的函数盘算。最初的信息内容如图所示。经由整理,我们获得了表2这样的规范化表格。 现在,我们就通过表2来实现快递用度自动盘算,最终实现图中这样的效果:第一步、制作查询表格首先,在表1内里制作好查询表格。表格有2个条件:目的地和重量。 为了制止使用的时候堕落,我们先将2个条件划分设置数据验证。选中B7单元格,点击“菜单栏-数据-数据验证”,在“序列”内里去选取泉源,泉源在表2内里的B列对应区域。

华体会体育全站官网登录

在上一篇文章内里,我们讲了如何整理完成一个规范化的表格,以便于下一步的函数盘算。最初的信息内容如图所示。经由整理,我们获得了表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<VLOOKUP(B7,Sheet2!B2:F32,3,0),0,(INT((B8-VLOOKUP(B7,Sheet2!B2:F32,3,0))/VLOOKUP(B7,Sheet2!B2:F32,5,0))+1)*VLOOKUP(B7,Sheet2!B2:F32,4,0))看上去很长,别怕,我们来剖析一下。先看一下文字版的:第一种条件下=B7目的地的首重价钱+(如果B8重量<目的地的首重重量,返回0)=B7目的地的首重价钱第二种条件下=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<VLOOKUP(B7,Sheet2!B2:F32,3,0),0,(INT((B8-VLOOKUP(B7,Sheet2!B2:F32,3,0)-0.00001)/VLOOKUP(B7,Sheet2!B2:F32,5,0))+1)*VLOOKUP(B7,Sheet2!B2:F32,4,0))最终,我们只需要在B7里选择目的地,在B8里输入重量,就能自动算出快递用度了。固然,如果目的地还要准确到市区县,只要有相应的数据,制作为多级下拉菜单就可以了。

总结:这个案例的函数虽然只用到了if、vlookup、int三个函数,但由于涉及到多个查询引用及盘算转换,也还是比力磨练综合应用能力的,大家可以多多训练,理顺逻辑思路,提高函数处置惩罚能力。《Excel天天训练营》《Excel天天训练营》是加薪学院专为职场人士研发的excel课程,凭据常见办公需求精选案例,今后办公不求人。现在,课程2.0图文版本已升级完毕,体系更完整,解说更到位,学员已突破1000人。课程分为三个篇章:第1章-提高效率(15节课)、第2章-醒目函数(25节课)、第3章-美化图表(10节课),共50节内容。

同时,课程2.0视频版正在更新中。注意:购置课程之后,私信发送“333”,获取课程配套的excel案例文件,同步实练习习,学习效果更佳!另外,视频课程现已提供电脑端播放~。


本文关键词:华体会体育全站官网登录,为什么,职场,要,学,excel,函数,看,这个,案例

本文来源:华体会网页版-www.lbt360.com