[已解决]筛选符合条件的信息，自动按顺序显示

828221

sheet2中输入关键字，能在sheet1中查找出，自动显示，并按顺序显示。研究好几天了，愁死了，求高手帮助。

最佳答案

月排行榜 / 总排行榜

xdragon

2013-12-2 17:13

1. =IF(ISERROR(SMALL(IF(Sheet1!$A$3:$G$8=$D$1,ROW(Sheet1!$A$3:$A$8)),ROW(A1))),"",INDEX(Sheet1!$A$1:$G$8,SMALL(IF(Sheet1!$A$3:$G$8=$D$1,ROW(Sheet1!$A$3:$A$8)),ROW(A1)),COLUMN(A1)))

复制代码

跳转到最佳答案楼层

xdragon

1. =IF(ISERROR(SMALL(IF(Sheet1!$A$3:$G$8=$D$1,ROW(Sheet1!$A$3:$A$8)),ROW(A1))),"",INDEX(Sheet1!$A$1:$G$8,SMALL(IF(Sheet1!$A$3:$G$8=$D$1,ROW(Sheet1!$A$3:$A$8)),ROW(A1)),COLUMN(A1)))

复制代码

tgydslr

1. 附件里什么都有

复制代码

xdragon

VBA方法哈：

1. Sub test()
   
2.     Dim name, r As Integer
   
3.     Dim Conn As Object, Rst As Object
   
4.     Dim strConn As String, SQL As String
   
5.     Dim i As Integer, PathStr As String
   
6.     Set Conn = CreateObject("ADODB.Connection")
   
7.     Set Rst = CreateObject("ADODB.Recordset")
   
8.     PathStr = ThisWorkbook.FullName
   
9.     Select Case Application.Version * 1
   
10.     Case Is <= 11
    
11.         strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
    
12.     Case Is >= 12
    
13.         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    
14.     End Select
    
15.    
    
16.     r = Sheet1.Cells(Rows.Count, 1).End(3).Row
    
17.     name = Sheet1.Cells(2, Sheet1.Range("A2:G" & r).Find([d1]).Column)
    
18.     Conn.Open strConn
    
19.    
    
20.     SQL = "select * from [sheet1$A2:G" & r & "] where " & name & "='" & [d1] & "'"""
    
21.     Set Rst = Conn.Execute(Left(SQL, Len(SQL) - 1))
    
22.     Range("A3:G" & Cells(Rows.Count, 1).End(3).Row).ClearContents
    
23.     Range("A3").CopyFromRecordset Rst
    
24.     Rst.Close
    
25.     Conn.Close
    
26.     Set Conn = Nothing
    
27.     Set Rst = Nothing
    
28. End Sub

复制代码

josonxu

方法可能没他们的简略   高手们看看我的可不可以修改些