[求助]双条件查寻外加个对应关系的怎么用vba编程啊？

baldniq · 发表于 2006-8-17 21:31

D1pcAD6u.rar (1.71 KB, 下载次数: 53)

sheet2中是姓名和id的对应关系，sheet3中是时间、id、数量三都相对应关系

想用vba编程把sheet3中的数量填到sheet1对应的单元格中

实际应用中数据量会比较大，sheet3中的数据会达几千个，所以希望程序能优化，谢谢！

爱歌学习 · 发表于 2006-8-18 12:30

Sub findd()
    Dim SQL$
    Set CNN = CreateObject("ADODB.Connection")
    CNN.Open "provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
    For i = 2 To [IV1].End(xlToLeft).Column
        sql1 = sql1 & "数量" & i & ","
        sql2 = sql2 & "("
        sql3 = sql3 & " left join (select 时间,数量 as 数量" & i & " from [sheet3$] where id=" & Sheet2.[a:a].Find(Cells(1, i), LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1) & ") as b" & i & " on a.时间=b" & i & ".时间)"
    Next
    sql1 = Left(sql1, Len(sql1) - 1)
    SQL = "select " & sql1 & " from (" & sql2 & "select 时间 from [sheet1$]) as a " & sql3
    'MsgBox SQL
    [B2].CopyFromRecordset CNN.Execute(SQL)
    CNN.Close
    Set CNN = Nothing
End Sub

V2pnufbK.rar (9.85 KB, 下载次数: 154)

ifhwfu · 发表于 2007-1-30 00:17

好贴，怎么没有人支持？

toopoor · 发表于 2007-2-3 22:47

用sumproduct时候可以把公式改成如下的样子：

=SUMPRODUCT((Sheet3!$A$2:$A$17=$G8)*(Sheet3!$B$2:$B$17=VLOOKUP(H$7,Sheet2!$A$2:$B$5,2,0))*Sheet3!$C$2:$C$17)

yuanzhuping · 发表于 2007-2-3 22:53

学习中。

zhoyojsnt · 发表于 2007-2-4 15:45

        sql1 = sql1 & "数量" & i & ","
        sql2 = sql2 & "("
        sql3 = sql3 & " left join (select 时间,数量 as 数量" & i & " from [sheet3$] where id=" & Sheet2.[a:a].Find(Cells(1, i), LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1) & ") as b" & i & " on a.时间=b" & i & ".时间)"
    Next
    sql1 = Left(sql1, Len(sql1) - 1)
    SQL = "select " & sql1 & " from (" & sql2 & "select 时间 from [sheet1$]) as a " & sql3

请问爱歌学习老师，这些句子是什么意思啊？特别是 sql1 = sql1 & "数量" & i & ","
sql2 = sql2 & "("，代表什么啊？

long0072004 · 发表于 2007-2-4 18:25

我怎么用不了啊。运行错误429？要下载什么补丁是不是啊。

mn860429 · 发表于 2011-3-10 16:50

交叉表方便些

		自动登录	找回密码
密码			免费注册