Question

在EXCEL中如何实现多列不同数据的查重？

如简图，用于财务对账，其中筛选重点是C列,G列金额，但ABC列和EFG列的项目数不相同，要求是找出根据C列和G列中相同的金额（最好还根据A列中的摘要一同筛选，实现则加分），并一一对应的独有标记，在此例图中则为A3:C3对应E2:G2，A2:C2对应E3:G3并做出独有的标记，完美实现者本人全部财富值送上！
如图，以H列为界，找出左右两边金额相同的业务并做出一对一的标记，注意两边的业务数量不一致，最好根据金额筛选，如果能同时根据摘要筛选可加分，注意H列左边的借方金额等于H列右边的贷方金额！





Answer 1

在H列建立A:C与E:G对对账标记，输入以下公式，然后向下填充公式

=COUNTIFS(C:C,G1,A:A,"*"&LEFT(E1,3)&"*")

得到1的说明左右符合，得0的说明不符合，如果得2或2以上，说明同列有重复。

 

详见附图附件

 

涂黑的地方可能是关键字，如果E列为“***”报，N列为“***”经手，那么建议在Excel表格中添加两列辅助列，分别获得E和N列的关键字，比如F列和O列为E列有N列的关键字，然后在R2和S2单元格分别输入以下公式，然后向下填充公式

=COUNTIFS(H:H,P2,F:F,N2)

=COUNTIFS(G:G,Q2,F:F,N2)

Answer 2

想问一下楼主，E列的数据，都含有公司两个字，并且前面就是公司名称而没有其他的多于字符了？

问这个问题是在考虑到底是

用复杂一点的公式来完成匹配；还是

用VBA，在找到金额相同的数据的情况下（可能不止一条），把文字的相关度最大的那条挑选出来

看到楼主的数据了。感觉如下：

1. 直接用金额一对一地判断，应该可以得到绝大部分的对应关系，（同样金额如果超过1个则提示重复而不做判断），如果一对一判断成功，将D列的凭证号取出放在对账结果列作为主要参照值（当然还可以增加摘要结果列）

2. 在D列使用条件格式突出显示对账成功（或不成功）的凭证号，以利于下一步的人工对账

3. 如果楼主的数据不是太多，以上1,2应该已经筛选出了大部分的数据。但如果数据超过2000条，估计重复的金额就会大大增加，这种情况下，楼主可以考虑用VBA探测摘要的数据相关度来处理重复金额的问题

主要还是看楼主的数据量情况。。如果，楼主认同，而需要帮助，在数据不是太敏感的情况下，可否将源数据采摘一部分发来，因为模拟这样的数据，会花很多无谓的时间和精力

追问

按部门来算的话，其实最多的也不到1000条，而且一般的做法是一张表分两部分做，相当于每次只要判断500行的数据，金额全部在贷方的一张，在借方的一张，金额相同的数据不会很多，这个是可以保证的

追答

那就按照1,2这个思路，

在空白列比如Q列，列出不重复的数据能对应的D列的凭证号，Q2：

=IF(O2=0,IF(COUNTIF(G:G,N2)>1,"借方重复",INDEX(D:D,MATCH(N2,G:G,0))), IF(COUNTIF(F:F,O2)>1,"贷方重复",INDEX(D:D,MATCH(O2,F:F,0))))

选择左边的数据区域，条件格式：

=AND($D2<>"",COUNTIF($Q:$Q,$D2))

会突出显示已经在Q列匹配成功的凭证

暂时未把匹配成功的E列的摘要列出（一个简单的Vlookup就能搞定），关键在于匹配思路

如图效果

Q列【#N/A】的就是数据没有找到

附件可参考

追问

功能基本实现了，果然高手！但还有几个问题需要请教，一是在您的表格中相同项目变色效果是如何实现的？二是在实际工作中的原始表格会简化一些，左边全部只有借方有数字，右边只有贷方有数字，希望能简化一下公式，最后就是能否在对账匹配列中同时提取凭证字号和摘要？谢谢！

追答

1. 匹配变色，就是我在上次追答中说的【条件格式】。条件格式的设置窗口我也在图上截取了的。

2. 原匹配公式是同时匹配借贷双方的，只要是独一无二匹配上的，就会把左边对应的凭证号列出。

   如果借贷方分别匹配（左边是借方、右边是贷方）那Q2公式可以改为：

   =IF(COUNTIF(F:F,O2)>1,"数据重复",INDEX(D:D,MATCH(O2,F:F,0)))

3. 如果要提取匹配的相应摘要，建议再起一列（如R列），R2公式：

   =VLOOKUP(Q2,D:E,2,0)

   下拉就可以。如果匹配不到的，同样会有#N/A出现。个人认为不影响工作，如果不是要打印，反而是可以作为提醒

Answer 3

你好，看了你的描述还是不能理解，表达思路可以在清晰点吗