[excel]查找相同身份证信息的不同缴费金额

需求描述

昨天媳妇给安排了个任务，excel中两个Sheet查重的问题，具体如下：

1. 找到excel两个sheet中的同一个人的不同缴费信息。
2. 找到Sheet1中比Sheet2中多出的人员的信息。

数据如下：

SHEET1

1	玄雾阁	雄霸	888101199003070097	男	200
2	牛家村	聂人王	888101199003077395	男	200
3	牛家村	绝无神	888101199003071436	男	200
4	绝情谷	天山童姥	888101199003074135	女	200
5	绝情谷	独孤求败	888101199003077598	男	200
6	断岳亭	东方不败	888101199003074995	男	200
7	断岳亭	风清扬	888101199003071954	男	200
8	断岳亭	任我行	888101199003078793	男	200
9	南无庙	灭绝师太	888101199003075090	女	300

SHEET2

村社区	姓名	身份证号码	应缴年度	缴费方式	应缴类型	个人缴费
冰火岛	陆小凤	888135195801094123	2018	经办机构自收	正常缴费	500
牛家村	聂人王	888101199003077395	2018	经办机构自收	正常缴费	200
八卦门	花无缺	888135195807074115	2018	经办机构自收	正常缴费	200
绝情谷	独孤求败	888101199003077598	2018	经办机构自收	正常缴费	300
绝情谷	天山童姥	888101199003074135	2018	经办机构自收	正常缴费	200
平安镇	老实和尚	888135195808163822	2018	经办机构自收	正常缴费	100
恶人谷	小鱼儿	888135195808254126	2018	经办机构自收	正常缴费	100
恶人谷	绝无神	888101199003071436	2018	经办机构自收	正常缴费	500
神仙岛	风清扬	888101199003071954	2018	经办机构自收	正常缴费	200
神仙岛	任我行	888101199003078793	2018	经办机构自收	正常缴费	200
神仙岛	灭绝师太	888101199003075090	2018	经办机构自收	正常缴费	300

方法一：使用excel中的函数

1.使用函数VLOOKUP来查找相同身份证信息下的个人缴费金额列

VLOOKUP语法规则如下：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数	说明	输入数据类型
lookup_value	要查找的值	数值、引用或文本字符串
table_array	要查找的区域	数据表区域
col_index_num	返回数据在查找区域的第几列数	正整数
range_lookup	模糊匹配/精确匹配	TRUE/FALSE

结果：

来个gif

2.使用函数COUNTIF来查找相同身份证信息下的不同的个人缴费金额

COUNTIF语法规则如下：

COUNTIF（range，criteria）

参数	说明
range	要计算其中非空单元格数目的区域
criteria	以数字、表达式或文本形式定义的条件

结果：

来个gif

3.使用筛选功能把最终结果筛出来

对第I列进行筛选，把0筛选出来就ok了

结果：

说明：

• #N/A表示的是Sheet1中比Sheet2中多出来的数据
• 剩下的数据是个人缴费金额不同的数据

来个gif

方法二：使用vba实现

先来张gif的效果图

结果：

代码

'****************************************************************
'
' 20181122 write by liuxq
' version 1.0
'
' 功能：将Sheet1中姓名和Sheet2中身份证信息做对比，如果相同再比较分数，
' 如果分数不同则将结果记录到Sheet3中
'
'****************************************************************

Private Sub CommandButton1_Click()
    '关闭屏幕更新可加快宏的执行速度
    Application.ScreenUpdating = False
    
    '---------------常量集，按实际修改--------------
    '表示SHEET1数值起始行
    Const START_SHEET1_INDEX As Integer = 1

    'Sheet1中的相同信息在第几列
    Const ID_SHEET1_INDEX As Integer = 4

    '表示SHEET1分数在第几列
    Const FENSHU_SHEET1_INDEX As Integer = 6

    'Sheet1的身份证号是第几列，如果没有填-1
    Const SHEET1_ID_COUNT As Integer = 4

    'Sheet1一共几列
    Const SHEET1_COLUMNS_COUNT As Integer = 6



    '表示SHEET2数值起始行
    Const START_SHEET2_INDEX As Integer = 2

    'Sheet2中的相同信息在第几列
    Const ID_SHEET2_INDEX As Integer = 3


    '表示SHEET2分数在第几列
    Const FENSHU_SHEET2_INDEX As Integer = 7
    
    
    '最终结果保存到哪个Sheet中
    Const WRITE_SHEET_NAME As String = "Sheet3"
    
    '---------------一些记录的变量------------------
    
    Dim sheet1Array, sheet2Array, resultArray
    
    '用来保存Sheet1的数据
    sheet1Array = Sheet1.Range("A1").CurrentRegion
    
    '用来保存Sheet2的数据
    sheet2Array = Sheet2.Range("A1").CurrentRegion
    
    
    ' 用来保存最终结果集
    ReDim resultArray(1 To Application.Max(UBound(sheet1Array), UBound(sheet2Array)), 1 To SHEET1_COLUMNS_COUNT + 1)
    
    
    '--------------------逻辑处理--------------------
    '记录resultArray的下标
    Dim m As Integer
    m = 0
    
    
    ' 查找到姓名相同分数不同的数据，然后赋值给结果集
    For i = START_SHEET1_INDEX To UBound(sheet1Array)
        cell1 = sheet1Array(i, ID_SHEET1_INDEX)
        d = -1
        For j = START_SHEET2_INDEX To UBound(sheet2Array)
            cell2 = sheet2Array(j, ID_SHEET2_INDEX)
            If cell1 = cell2 Then
                Dim cell1fenshu As Double
                Dim cell2fenshu As Double
            
                cell1fenshu = sheet1Array(i, FENSHU_SHEET1_INDEX)
                cell2fenshu = sheet2Array(j, FENSHU_SHEET2_INDEX)
                If cell1fenshu <> cell2fenshu Then
                    m = m + 1
                    For k = 1 To SHEET1_COLUMNS_COUNT
                        resultArray(m, k) = sheet1Array(i, k)
                    Next
                    resultArray(m, SHEET1_COLUMNS_COUNT + 1) = sheet2Array(j, FENSHU_SHEET2_INDEX)
                End If
            Else
                    d = d + 1
            End If
        Next
        
        ‘ Sheet2中没有Sheet1中该行数据，记录到sheet1Array中
        If d = UBound(sheet2Array) - START_SHEET2_INDEX Then
            m = m + 1
            For k = 1 To SHEET1_COLUMNS_COUNT
                resultArray(m, k) = sheet1Array(i, k)
            Next
            resultArray(m, SHEET1_COLUMNS_COUNT + 1) = "未找到"
        End If
        
    Next

    '---------------保存结果到Sheet中----------------
    '清空工作表
    Worksheets(WRITE_SHEET_NAME).Cells.Clear
    
    '禁止Shee1中SHEET1_ID_COUNT列的身份证信息按科学计数法输出
    If SHEET1_ID_COUNT <> -1 Then
        Worksheets("Sheet3").Columns(SHEET1_ID_COUNT).NumberFormatLocal = "@"
    End If
    
    '将结果集写入到${WRITE_SHEET_NAME}中
    Worksheets(WRITE_SHEET_NAME).Range("A1").Resize(m, SHEET1_COLUMNS_COUNT + 1) = resultArray
    
    '字体加粗
    For i = 1 To m
'        Worksheets(WRITE_SHEET_NAME).Cells(i, SHEET1_COLUMNS_COUNT + 1).Interior.ColorIndex = 3 '设置单元格颜色
        Worksheets(WRITE_SHEET_NAME).Cells(i, SHEET1_COLUMNS_COUNT + 1).Font.Bold = True '设置文字粗体
    Next
    
    Application.ScreenUpdating = True
    MsgBox ("执行完毕！")
End Sub

具体就不细说了，注释已经写的很详细了，有兴趣的同学可以看看。

老习惯，上张流程图吧