INDEX+SMALL查询顾客消费记录

Excel教程 2021-12-27 21:27:21

问题情境

如下顾客消费样表：

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

结果如下：

公式实现

在G4单元格输入公式：

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

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

给消费记录添加序号：

=IF(OR($G$1=””,G4=””),””,COUNTIF($G$4:G4,$G$1))&””

确定，即得序号1，公式向下填充，可得所有消费记录的序号。

公式解析

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

IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)

如果B列姓名与G1相等，返回对应行，如果不等，返回B列总行数，得一个数组。

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

在刚才的数值中，取最小的值，即得一个“李四”顾客的行。

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

查询第一条消费记录中的姓名。

公式向下向右填充，即得李四的所有记录。

2、=IF(OR($G$1=””,G4=””),””,COUNTIF($G$4:G4,$G$1))&””

如果查询姓名和查询记录姓名中有一个为空，也不填充序号。