|
核查借贷方金额是财务工作中很常见的一项任务了，通过核对，将借贷金额相符的去掉，就可以确认客户余额是由哪几笔形成的，下图是去除了多余列后的效果，只保留客户名称和借贷金额： 蓝色部分表示可以抵消的数据，需要删除，红色的两处虽然金额相同，但由于属于不同客户，需要保留。 如果只是这么点数据，手动删除也就搞定了，但是面对上千行数据，几十个客户，财务mm急的直跺脚，就是想不到好办法，无奈之下向老菜鸟求助。 针对于截图的这种情况，算是比较简单的，同一列中金额没有相同的，其实只需要在后面加一列，使用公式： =COUNTIFS(A:A,A2,B:B,C2)+COUNTIFS(A:A,A2,C:C,B2) 就可以将需要删除的行标注出来，效果如图所示： 筛选结果为1的删掉就好了，有没有觉得很神奇呢？ 这个公式中用的COUNTIFS函数是一个多条件计数的函数，格式为： =COUNTIFS(条件区域1,条件1,条件区域2,条件2) 公式分为两部分，COUNTIFS(A:A,A2,B:B,C2)用来统计同一个客户借方里与贷方有相同金额的个数： 从图中结果可以看到，1都是贷方与借方匹配的数据，也就是贷方标蓝的行。 同理，COUNTIFS(A:A,A2,C:C,B2)得到1的就是借方标蓝的行。 两个COUNTIFS相加后就是全部需要删除的数据，这并不难理解。 但实际问题是，同一列可能会有相同的金额，如图所示： 这时候按公式结果为1去删除可能就会出错，例如5500，在借方有两笔而贷方只有一笔，黄色的这个应该保留。 实际情况中年这样的重复金额还有很多，该怎么办？ 思考两分钟继续往下看……  遇到比较棘手的问题时，如果一时间无法想到一步到位的办法，不妨试试分解自己的思路，利用辅助列将一个复杂的问题变成几个简单的问题。 在这个例子中，难点在于有相同的金额，如果我们给相同金额加个编号，问题是不是可以解决呢？ 要给相同数据加编号，这可是COUNTIF的强项，于是第一个辅助列可以这样做： =COUNTIF(B$1:B2,B2)&"-"&B2 用COUNTIF完成编号，在编号与数据之间添加分隔符号，用&连接起来，可以看到5500这个金额已经有了不同的编号。关于使用COUNTIF创建编号的案例，推荐一篇教程：这个例子如果你学会了，Excel里的自定义排序号问题都难不住你！ 这样的结果有个问题，当金额为空的时候会得到0-这样的相同结果，会对后期计数产生影响，因此需要增加一个IF，当金额为空的时候需要得到一个不会重复的内容，这个内容可以用ROW()来完成，就是当前行号。 将借方和贷方都使用公式做出辅助列后，再用刚开始的公式就能实现需要的结果，辅助列1的公式为： =IF(B2<>0,COUNTIF(B$1:B2,B2)&"-"&B2,ROW()) 将辅助列1的公式右拉就可以得到辅助列2，再使用公式： =COUNTIFS(A:A,A2,D:D,E2)+COUNTIFS(A:A,A2,E:E,D2) 就这个问题而言，可能还有更好的方法，以上也只是提供了一个思路，没有用太复杂的函数或操作，感觉大部分朋友是可以理解的。 当然你遇上的问题可能会有所差异，但是只要有了解决问题的思路，就会有方法。 四月份安排了十二节实用的课程 五月份想学什么现在开始预约 体验整月课程只需9.9 |
|
|
