Excel VBA——一对多查询，几行代码代替复杂公式

如下顾客消费样表：

 

能不能随意查询每个顾客的消费记录，而且随着消费记录的增多，查询结果也可自动更新？

结果如下：

 

当时，韩老师在Excel345｜INDEX+SMALL查询顾客消费记录 一文中给出了两个公式：

一个是查询记录的INDEX+SMALL数组公式：

=INDEX(B:B,SMALL(IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)),ROW(A1)))&""

<Ctrl+Shift+Enter>三建结束，即得G1单元格指定消费者第一次消费记录中的姓名，公式向下向右填充，可得该消费者的所有消费记录。

 

另一个是给消费记录添加序号的公式：

=IF(OR($G$1="",G4=""),"",COUNTIF($G$4:G4,$G$1))&""，得所有消费记录的序号。

 

以上提到的公式方法，有两个小小的瑕疵：一是如果数据量很大，数据公式执行较慢；二是分两个公式完成，不如一步完成的好。

今天韩老师给几行代码一次完成查询功能：

Sub xf()
    Dim i%, k%, irow%          '定义整形变量
    irow = Range("a1").CurrentRegion.Rows.Count   'irow为当前数据表数据行数
    k = 4                     '因为查询数据从F4单元格开始显示，所以K赋初值4
    Range("f4", "i" & irow).Clear   '清除显示区域数值，以备显示下一次查询结果
    For i = 2 To irow
        If Range("b" & i).Value = Range("g1").Value Then
            Range("f" & k) = k - 3
            Range("g" & k).Value = Range("b" & i).Value
            Range("h" & k).Value = Range("c" & i).Value
            Range("i" & k).Value = Range("d" & i).Value
            k = k + 1
        End If
     Next
End Sub

当然，如果数据较多，也可使用定义数组的方法。

如图：

 

查询结果如下：

 大家可以收藏这几行代码，轻松搞定多条记录查找的问题。