超实用！公司大神用Excel做了一张自动查询表，我跪着收藏了！ / 开普饭

每天一点小技能

职场打怪不得怂

编按：都市办公中，在眼光缭乱的报价数据中做各种查询工作也已然成了打工人的日常。这种日常又琐碎的工作，有时肉眼可以轻松搞定，但是更多时候，肉眼却只会带来低效率和失误。那么，如何快速查询快递价格后秒杀做出最优判断呢？其实可以用Excel这样做……

现在各个快递公司间的竞争非常厉害，都为了不同的服务对象制定了不同的收价服务，试图抢占更多的用户。

所以，当你选择快递公司时，面对眼前这样一份报价单，眼花就是一定的了。

那么，如何快速精准的找到今日份最佳合作公司呢？这事当然是交给Excel了！

高效地应用Excel查询一件货物的运费，只需要动动鼠标，输入几个数据，就可以得到费用总和。效果如下。

下面是两家著名快递公司，顺丰速运（EXPRESS）和DHL的价格清单和分区。

大家一起来看看，如何在繁杂的数据中精确查询吧！

注意：EXPRESS（顺丰）快递公司有两个不同业务，数据分别在“国际标快”和“国际特惠”两个Excel工作簿中；DHL快递公司只有一个业务，数据在名为“暂1”的Excel工作簿中。

一

数据整理

1.设计格式，创建名称和数据验证

①首先需要新建一个Excel工作簿，整理一下收货地区。过程就不详述了，最后的结果如下，格式可以随个人喜好调整。

②按照下图所示，点击菜单栏上的“公式”，打开“名称管理器”，建立名称。

③如下图所示，创建数据验证。其中E列、F列和G列是三级下拉菜单。

④在H列创建数据验证。

2.整理源数据的结构

为了确保数据计算的一致性，大家还需要对源数据的结构做一些调整。

比如下图中右侧公司的清单上，文件和包裹的最小计费重量是2公斤，而左侧公司的清单上，最小计费重量是2.5公斤。由于不同公司的报价单在细节上有些许差异，对源数据的结构调整就是必要的前期操作。在这里，作者把右侧清单上的最小计费重量调整为2.5公斤。

另外，两家公司对于超重部分的重量规定也不一样，因此大家要把这部分的数据调整到相同的起始行，并给他们定好相同的属于区域。

3.整理分区

把各自快递业务的分区整理出来，如下图中的M列和N列。

二

设置计算公式

运费计算规则：

货物没有超重时，直接查询重量和目的地，即可查到快递费用；货物超重时，用重量乘以对应的价格来计算出快递费用。

1.在单元格K15中输入公式：

=IFERROR(ROUND(IF(I14<=2.5,< span="">IF(H14="文件",VLOOKUP(I14,INDIRECT(F14&"!$B$8:$K$12"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE),VLOOKUP(I14,INDIRECT(F14&"!$B$13:$K$17"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)),IF(IF(E14="EXPRESS",I14>=20,I14>=31),VLOOKUP(I14,INDIRECT(F14&"!$B$74:$K$80"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)*I14,VLOOKUP(I14,INDIRECT(F14&"!$B$18:$K$72"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE))),2),"")

函数解析：

① 这么长的一串函数，剔除最外层的IFERROR函数和ROUND函数后，其实就是由一个IF函数构成的。

② IF(H14="件",VLOOKUP(I14,INDIRECT(F14&"!$B$8:$K$12"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE),VLOOKUP(I14,INDIRECT(F14&"!$B$13:$K$17"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE))这一部分是当判断条件 “I14<=2.5” 为真时，函数的运算部分。这部分整体上是一个IF函数。如果H14是文件，那么就到“$B$8:$K$12”这个地方去查找运费；否则，就到包裹“$B$13:$K$17”那里去查找运费。

③ 确定跳转到相应的工作表。需要利用INDIRECT函数根据单元格F14中的内容跳转到对应的工作表中去。

④ 这部分中的INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0))组合，其作用是由MATCH函数确定单元格G14中的国别在N列中的位置，再由INDEX返回分区（M列）的信息。

⑤ MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1部分是由MATCH函数返回数据区域的列数信息，最后由VLOOKUP函数模糊查找数据。

⑥ IF(IF(E14="EXPRESS",I14>=20,I14>=31),VLOOKUP(I14,INDIRECT(F14&"!$B$74:$K$80"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)*I14,VLOOKUP(I14,INDIRECT(F14&"!$B$18:$K$72"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)))这一部分，是当判断条件“I14<=2.5”为假时，函数的运算部分。这部分也是一个if函数，它可以进行条件判断。当货物超重时，通过indirect函数跳转到超重价格区域“< span="">$B$74:$K$80”，计算运费；当货物未超重时，则直接在$B$18:$K$72中查找价格。

2.在单元格K15中输入公式：=IFERROR(ROUND(K14*(1+J14),2),"")

这是很简单的数学计算公式，就不再赘述了。

总结：

今天的教程本身并没有什么难度，不要被那么长的公式吓住了。其实它就是IF函数配合INDIRECT函数，根据不同的条件在不同的工作表区域中抓取数据的常规操作。

对于今天的教程，大家可以下载课件，多多练习哦~

扫一扫添加老师微信

在线咨询Excel课程