当前位置:首页 > 科技 > 正文

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

与 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不加班」推荐给你的朋友

你可能想看:

有话要说...

取消
扫码支持 支付码