|
最近，收到一老铁求助，让我帮忙编写一下计算快递运费的公式，觉得有点意思，就在跟大家分享一下，这个公式的制作过程和心得，下面的讲解有点快，你可系好安全带！ 老铁发来表格的样子很随意，完全看不出是个已经工作的人做的，数据如下：  思路： 从表中的数据来看，某个快递公司把重量为NKG的快递送到某个省，需要3步： 1：获取某快递公司送达某省运费的基础信息； 2：根据快递续重的规则，编写运费计算公式； 3：将上两步的公式合并出最终公式； 然后根据这个思路，就来一步一步实现案例的功能吧。 在Excel中，能同时根据2个条件进行查询的函数几乎没有，但是，我们可以技术手段，将2个条件合并成1个，实现的方法有2种： 创建辅助列，将2列表格进行拼接，合并成一列；在公式中拼接，需要函数的支持数组模式（可忽略）；我这里只讲解第一种，相比较而言，创建辅助列比较容易理解，而创建辅助列的第1步就是如何在指定位置前插入一列，如果你选中位置不同，步骤上会有所区别： 选中A列，ctrl+shift+=快捷键或鼠标右键，弹出菜单，选择插入；选中A列中的任一单元格，ctrl+shift+=或鼠标右键菜单选择插入，都弹出【插入】窗口，选择【整列(C)】,点确定  接着就是拼接数据，在A1录入公式=B1&C1,输入移至A1右下角，鼠标变为＋，双击，完成其他公式填充，至此辅助列创建完成。 现在完成第一步，接着就要找到合适的函数，单条件又支持精准查找的，我常用VLOOKUP和HLOOKUP,INDEX+MATCH组合,其中只有VLOOKUP和INDEX+MATCH比较适合。可能这3个函数的用法你都烂熟于心了，不过，还是按找惯例，简单回忆下3个函数的基础用法。 函数不多，也得按序来说，第1个出场的就是： VLOOKUP(查找值,查找范围,结果在查找范围的列数,查询模式)查询模式：0：精准模式，1为模糊查询； 简单一带而过，至于更详细的用法，我会在之后VLOOKUP文中详细介绍，不过这都是后话。回看本例，查找值分别：快递公式在K列，快递的目的地在H列，查找最终公式为：K单元格&H单元格， 查找范围：A1:F32；结果在查找范围的列数：首重0.5KG，0.5-1KG，续重0.5的列数分别为4，5，6；比如读取运费计算表的第一条数据：K2&H2对应的0.5KG对应的值：=VLOOKUP(K2&H2,A1:F32,4,0);  下面该介绍MATCH和INDEX函数了： MATCH(查找值,查找的行或列,查找模式)查找模式：有3个值：0(精准用法），-1和1（模糊查询； 功能：查找范围是行，返回值所在的列数，若范围是列，则返回是行数。 INDEX(引用范围,行,列)功能：根据行数和列数，返回引用范围对应位置的值 使用思路如下： 1.将查找值(K2&H2)和查找的列（A:A）代入MATCH函数变为：MATCH(K2&H2,A:A,0),返回值在A列中的位置; 2.用MATCH返回的行数位置，加上读取单元格在引用范围A1:F32中的列数分别为4,5,6,便能读取想找的数据，公式如下： =INDEX(A1:F32,MATCH(K2&H2,A:A,0),4);  这样就完成第一步，接下来就是； 源数据中显示，运费的计算是以0.5kg基数进行划分的，以送往河南的A快递公司的数据为例： 快递重量<0.5kg,会按0.5kg算，收费：16.8元（位于D列，列数4）；快递重量>0.5kg且<=1.0kg 都算1.0kg,收费18.0元 （位于E列，列数5）当快递重量>1.0kg时，每增重0到0.5KG，即叠加5.4元（位于F列，列数6）若将上面的条件编写成一个公式，需要先解决一个问题，超过1KG后，是以0.5的整数倍向上进行取整，就需要借助CEILING函数： CEILING(数值,进位基数)而本例中的进位基数就是0.5，函数的功能就是将数值以0.5整数倍的方式进行向上取整，比如CEILING(1.0003,0.5),返回1.5；在回看3个条件，如果把快递重量，都用CEILING处理一下，上面的条件就变为： CEILING(重量,0.5)=0.5，费用16.8；CEILING(重量,0.5)=1 费用18.0；CEILING(重量，0.5)>1,费用：18+((CEILING(重量,0.5)-1)/0.5)*5.4对比发现，将等于1的情况，代入第3个式子结果同样是18，所有2，3条件可以合并成1个式，即最终的模拟公式用IF表示： IF(CEILING(重量,0.5)<=0.5,16.8,18+((CEILING(重量,0.5)-1)/0.5)*5.4) 到这，编写的公式中的所有问题都已解决了，剩下就是将实际引用代入公式了，只不过需要注意的是，计费表的重量为G,需要除上1000，换成KG。 说到编写成公式，其实就没有太复杂了，只是将对应的实际数据代入上面的计算运费的公式就好。其中的重量在表格的I列，若求A公司寄390G物品到河南的费用是多少？ 重量所在的单元格为I2，将重量转为KG=I2/1000; 在读取运费相应基础数据上，我使用VLOOKUP函数为例介绍： 16.8对应函数式：VLOOKUP(K2&H2,A1:F32,4,0)；18的代替公式为：VLOOKUP(K2&H2,A1:F32,5,0);5.4即为：VLOOKUP(K2&H2,A1:F32,6,0)将上面实际数据代入模拟公式： =IF(CEILING(I2/1000,0.5)<=0.5,VLOOKUP(K2&H2,A1:F32,4,0),VLOOKUP(K2&H2,A1:F32,5,0)+((CEILING(I2/1000,0.5)-1)*2*(VLOOKUP(K2&H2,A1:F32,6,0)) 因为这个公式需要应用的其他单元格，且其中函数VLOOKUP中查找范围不能发生变化，需要进行绝对引用处理，就公式中的A1:F32替换$A$1:$F$32即可。 不过，可能有人要说了：如果快递公司的信息有增加，还需要手动修改公式，这样不好吧？ 的确，能解决这种问题的方法不止一个，我知道使用成本最小的就是开启智能表格，具体操作如下图：  开启之后，系统会自动使用智能表格引用范围，当数据增加会自动扩充引用范围，是不是很贴心呢？ 最后还有一个值得思考的问题，除了用技术手段，真的没有别的方法彻底解决这个问题中的多条件查询吗？ 其实导致在这个例子出现多条查询的终极原因，是表格数据结构的设计上的缺陷，知道了原因，比较好的解决方式就是重构表格结构，一个好的数据表结构，能让你的项目更简洁，效率也更高。 就拿本案例来说吧，又该如何重构呢？ 这就要从筛选运费的基础条件说起，本例共2个条件：1.快递公司，2,地区列表；该遵循什么原则拆分呢？ 拆分原则之一就是根据数据来源，必须方便操作，否则频繁的操作也能累死人啊。本例中的运费基础数据通常来自一个快递公司，就以公司名称为工作表名，拆分成多个表，既能方便管理数据，又能便以查询读取相关数据，进而简化查询条件。 如果按公司名称分表，这样其中一个条件就出现在引用范围的路径中，这样自然就减少了查询条件。比如将快递A公司的数据存放A表，B公司存在B工作表，原来的查询范围：A1:F32变为indirect(H2&”!A1:F32”),这样的查找条件自然就变成1个了，原来的公式为=VLOOKUP(K2,INDIRECT(H2&”!A1:F32”,3,0)。 不过，这样做会有一个小问题，就是要做到引用范围自动随着数据增加而扩充的话，就需要用函数的方法来实现，有兴趣的网友，可以自己尝试处理一下。 如果你在做一个项目之前，首要考虑的是表格结构的设计，毕竟数据结构会影响整个项目的各个方面。如屏幕前的你，对项目制作之前，还需要做哪些准备工作感兴趣，
|
|
|
