与 30万 粉丝一起学Excel
作为会计,经常都会用到这些公式,一定要学好。
1.计算余额(结余)
F1是标题,如果直接加会变成错误值,嵌套N是为了让标题变成0。
=N(F1)+D2-E2
有的时候,数据源是从其他地方复制过来的,或者导出来的。这些空白单元格里面都有空文本,导致结果变成#VALUE!。
遇到这种情况,可以所有单元格都嵌套N,不管是什么文本,嵌套了都当成0处理。
=N(F1)+N(D2)-N(E2)
2.核对明细跟总表是否一致
左边是明细(多条记录),右边是总表(1条记录)。因此用总数-对应料号之和,不是0的就有问题。
=F2-SUMIF(A:A,E2,B:B)
同理,左边也可以用SUMIF先求和,再查找右边的对应值,查找值除了可以用VLOOKUP,也可以用SUMIF。VLOOKUP后面会说。
=SUMIF(A:A,A2,B:B)-SUMIF(E:E,A2,F:F)
语法:
=SUMIF(条件区域,条件,求和区域)
3.根据姓名查找所有列的对应值
=VLOOKUP($G2,$A:$E,COLUMN(B1),0)
先来看最基本的查找,然后再说明楼上的公式。
=VLOOKUP($G2,$A:$E,2,0)
语法:
=VLOOKUP(查找值,查找区域,返回区域第几列,0)
基本工资在区域的第2列,因此第三参数写2。
同理,岗位工资在区域的第3列,写3。
=VLOOKUP($G2,$A:$E,3,0)
同理,值班补助在区域的第4列,写4。
=VLOOKUP($G2,$A:$E,4,0)
同理,生活补贴在区域的第5列,写5。
=VLOOKUP($G2,$A:$E,5,0)
目前列数比较少,手工修改下也挺快的,如果列数很多,就不太方便。因此COLUMN就派上用场,专门右拉生成数字2、3、4、5。
最后将COLUMN嵌套进去,就是完整的公式。
4.根据完成率,判断在哪个区间,并获得奖励
VLOOKUP第四参数写0是精确查找,省略掉就是按区间查找。查找的时候,一定要列出区间的下限。
=VLOOKUP(B2,$E$2:$G$7,3)
账龄的区间判断,跟这个做法是一样的。
联系客服