半小时搞定对账，难是难点，胜在一劳永逸

与 30万 粉丝一起学Excel

VIP学员的问题，总表的含税金额要根据项目名称从分表的供应商引用得到，每个供应商一个表，实际上有很多供应商。获取金额后，跟原来的金额核对。

深圳的供应商报价是按单价，也就是说最后还得乘以重量才能得到金额。这里将重量划分成3个区间，每个区间的单价都不一样。

广州的供应商报价是按金额，同样是划分为3个区间。

这里涉及到的知识非常多，卢子分解后进行说明。

1.判断重量属于哪个区间

发货重量写的时候都含有单位，如193.5KG。带单位是不能直接判断的，需要去除才行。

=SUBSTITUTE(H3,"KG",)

用文本函数得到的都是文本型数字，需要加--转换成真正的数字。

= --SUBSTITUTE(H3,"KG",)

判断属于哪个区间可以用IF，当然啦，用MATCH更智能。在公式不熟练的情况下，先做个对应表，也就是列出每个区间的下限，这样更加直观。

用MATCH省略掉第3参数，就是判断重量属于第几个区间。

= MATCH(--SUBSTITUTE(H3,"KG",), {0.01,500.01,1000})

如果常量数组不熟，就直接引用刚刚的对应表，作用一样。

=MATCH(--SUBSTITUTE(H3,"KG",), $Q$3:$S$3)

2.查找项目对应的单价

查找用得最多的是VLOOKUP，除了第3参数第几列不确定外，其他的都确定。

=VLOOKUP(B3,深圳报价!B:I, 第几列,0)

区间在第4、5、6列。前面用MATCH得到的是1、2、3，也就是要MATCH+3才行。

这样单价就出来了。

=VLOOKUP(B3,深圳报价!B:I, MATCH(--SUBSTITUTE(H3,"KG",),{0.01,500.01,1000})+3,0)

3.查找项目对应的所有供应商的单价

供应商少的话可以逐个VLOOKUP，实际有很多供应商，因此这个方法就行不通。

供应商的名称已经列在单元格，只要在后面&报价2个字名称就一样。

间接引用单元格用INDIRECT。

=VLOOKUP(B3, INDIRECT(I3&"报价!b:z"),MATCH(--SUBSTITUTE(H3,"KG",),{0.01,500.01,1000})+3,0)

4.查找项目对应的所有供应商的金额

深圳报价是按单价，广州报价是按金额，本来是无法区分的，因此，我在总表增加一列备注区分。

如果是单价，就乘以重量，否则就乘以1。绕了一大圈，终于搞定金额。

=VLOOKUP(B3,INDIRECT(I3&"报价!b:z"),MATCH(--SUBSTITUTE(H3,"KG",),{0.01,500.01,1000})+3,0) *IF(L3="单价",SUBSTITUTE(H3,"KG",),1)

最后，判断2个金额的差异，这个很简单，直接相减就行。

=G3-J3

同样是对账，有的简单到没朋友，有的难于上青天。

请把「Excel不加班」推荐给你的朋友