|
给你一个我写的通用查询模块:
就象SQL一样,在EXCEL中执行,肯定比SQL要快啦。
Function FiltData(Arr, FiltArr)
'filtarr,列号,筛选条件,例 2,50,">=",3,"abc","like"
'当有符合条件的,返回已经转置后的数组,无符合的,返回空值
Dim Bln As Boolean
Dim i&
Dim j%
Dim n%
Dim ArrRst(), K&
Dim MyRst()
Dim Tmp, Comp$, Col&
For i = 1 To UBound(Arr)
Bln = True
For n = 1 To UBound(FiltArr) Step 3
Col = FiltArr(n)
Tmp = FiltArr(n + 1)
Comp = FiltArr(n + 2)
Select Case Comp
Case "like"
If Not LCase(Arr(i, Col)) Like "*" & LCase(Tmp) & "*" Then Bln = False: GoTo CompEnd
Case "="
If Not Arr(i, Col) = Tmp Then Bln = False: GoTo CompEnd
Case ">="
If Not Arr(i, Col) >= Tmp Then Bln = False: GoTo CompEnd
Case "<="
If Not Arr(i, Col) <= Tmp Then Bln = False: GoTo CompEnd
Case ">"
If Not Arr(i, Col) > Tmp Then Bln = False: GoTo CompEnd
Case "<"
If Not Arr(i, Col) < Tmp Then Bln = False: GoTo CompEnd
End Select
Next n
CompEnd:
If Bln Then
K = K + 1
ReDim Preserve ArrRst(1 To UBound(Arr, 2), 1 To K)
For j = 1 To UBound(Arr, 2)
ArrRst(j, K) = Arr(i, j)
Next j
End If
Next i
If K > 0 Then '进行转置
ReDim MyRst(1 To UBound(ArrRst, 2), 1 To UBound(ArrRst))
For i = 1 To UBound(ArrRst, 2)
For j = 1 To UBound(ArrRst)
MyRst(i, j) = ArrRst(j, i)
Next j
Next i
FiltData = MyRst
End If
End Function
|
|