求一条EXCEL的多列对比取数据的公式

t_kong 发布于 2013/05/24 21:16
阅读 3K+
收藏 1

有两个表分别是

Sheet1

Sheet2

通过对比姓名和手机号码,如果两表的姓名和手机号码都相同,则将表Sheet1中的“邮箱”和“地址”分别写入表Sheet2的D、E列中.......

这个EXCEL的公式如何写?

加载中
0
0day
0day

貌似没有这种函数,用VBA好了(进到EXCEL,按ALT+F11),把下面复制进去,运行,搞定!

Sub aa()
    Dim sheet1str1, sheet1str2, sheet1str3, sheet1str4, sheet2str1, sheet2str2 As String
    For i = 2 To 6 'Sheet2的总行数
        sheet2str1 = Trim(Worksheets("Sheet2").Range("A" & i).Value)
        sheet2str2 = Trim(Worksheets("Sheet2").Range("B" & i).Value)
        For t = 2 To 4 'Sheet1的总行数
            sheet1str1 = Trim(Worksheets("Sheet1").Range("A" & t).Value)
            sheet1str2 = Trim(Worksheets("Sheet1").Range("B" & t).Value)
            sheet1str3 = Trim(Worksheets("Sheet1").Range("C" & t).Value)
            sheet1str4 = Trim(Worksheets("Sheet1").Range("D" & t).Value)
            If (sheet2str1 = sheet1str1) And (sheet2str2 = sheet1str2) Then
               Worksheets("Sheet2").Range("D" & i).Value = sheet1str3
               Worksheets("Sheet2").Range("E" & i).Value = sheet1str4
            End If
        Next t
    Next i
End Sub

0
t
t_kong

引用来自“0day”的答案

貌似没有这种函数,用VBA好了(进到EXCEL,按ALT+F11),把下面复制进去,运行,搞定!

Sub aa()
    Dim sheet1str1, sheet1str2, sheet1str3, sheet1str4, sheet2str1, sheet2str2 As String
    For i = 2 To 6 'Sheet2的总行数
        sheet2str1 = Trim(Worksheets("Sheet2").Range("A" & i).Value)
        sheet2str2 = Trim(Worksheets("Sheet2").Range("B" & i).Value)
        For t = 2 To 4 'Sheet1的总行数
            sheet1str1 = Trim(Worksheets("Sheet1").Range("A" & t).Value)
            sheet1str2 = Trim(Worksheets("Sheet1").Range("B" & t).Value)
            sheet1str3 = Trim(Worksheets("Sheet1").Range("C" & t).Value)
            sheet1str4 = Trim(Worksheets("Sheet1").Range("D" & t).Value)
            If (sheet2str1 = sheet1str1) And (sheet2str2 = sheet1str2) Then
               Worksheets("Sheet2").Range("D" & i).Value = sheet1str3
               Worksheets("Sheet2").Range("E" & i).Value = sheet1str4
            End If
        Next t
    Next i
End Sub

如果我的数据量很大....这个方法貌似比较困难。表一30000多行,表二10000多行。用for语句,是会卡死的.......除此之外,还有没有别的方法。。
0
朱__朱
朱__朱
那么多行,用access也比excel强啊。。。。
0
Zenroi
Zenroi
Sub addInf()
    Dim Dic As Object, Arr, k%, Str$
    
    Set Dic = CreateObject("Scripting.Dictionary")
    Arr = Sheets(1).[A1].CurrentRegion
    For k = 2 To UBound(Arr)
        Dic(Arr(k, 1) & Arr(k, 2)) = Array(Arr(k, 3), Arr(k, 4))
    Next
    
    Arr = Sheets(2).Range("A2", [A1].End(4).Resize(1, 5))
    For k = 1 To UBound(Arr)
        Str = Arr(k, 1) & Arr(k, 2)
        If Dic.exists(Str) Then _
            Arr(k, 4) = Dic(Str)(0): Arr(k, 5) = Dic(Str)(1)
    Next
    Sheets(2).[A2].Resize(k - 1, 5) = Arr
    Set Dic = Nothing
End Sub
0
何天贵
何天贵
用excel公式可以搞定,等我用电脑时回复你
0
IdleMan
IdleMan
先合并,再用个函数就好了
0
中山野鬼
中山野鬼

引用来自“t_kong”的答案

引用来自“0day”的答案

貌似没有这种函数,用VBA好了(进到EXCEL,按ALT+F11),把下面复制进去,运行,搞定!

Sub aa()
    Dim sheet1str1, sheet1str2, sheet1str3, sheet1str4, sheet2str1, sheet2str2 As String
    For i = 2 To 6 'Sheet2的总行数
        sheet2str1 = Trim(Worksheets("Sheet2").Range("A" & i).Value)
        sheet2str2 = Trim(Worksheets("Sheet2").Range("B" & i).Value)
        For t = 2 To 4 'Sheet1的总行数
            sheet1str1 = Trim(Worksheets("Sheet1").Range("A" & t).Value)
            sheet1str2 = Trim(Worksheets("Sheet1").Range("B" & t).Value)
            sheet1str3 = Trim(Worksheets("Sheet1").Range("C" & t).Value)
            sheet1str4 = Trim(Worksheets("Sheet1").Range("D" & t).Value)
            If (sheet2str1 = sheet1str1) And (sheet2str2 = sheet1str2) Then
               Worksheets("Sheet2").Range("D" & i).Value = sheet1str3
               Worksheets("Sheet2").Range("E" & i).Value = sheet1str4
            End If
        Next t
    Next i
End Sub

如果我的数据量很大....这个方法貌似比较困难。表一30000多行,表二10000多行。用for语句,是会卡死的.......除此之外,还有没有别的方法。。
如果数据量大,逻辑复杂度高,建议你还是导出到文件,让c程序来洗。我就这么干。。。。
t
t_kong
多谢提点
返回顶部
顶部