excel表格跨表查询：查询指定顾客的购买记录-英雄云拓展知识分享

今天一名朋友传来数据表，要求：

针对“购买记录表”中的记录，以下：

在“购买查询”表中实现查询指定顾客的购买记录，效果以下：

本问题，有两个关键点：

1、购买记录必须是根据指定的姓名展现的；

2、购买记录的序号必须是1、2、3……连续的，且针对查找出的记录数量改变。

公式实现实现按指定顾客查询

在B4单元格输入公式：

=INDEX(购买记录表!B:B,SMALL(IF(购买记录表!$B$2:$B$12=购买查询!$B$1,ROW(购买记录表!$B$2:$B$12),ROWS(购买记录表!B:B)),ROW(A1)))&””，，以3键组合结束。

公式向下和向右填充，即得B1单元格指定的顾客的购买记录。

我们以查找“李4”的购买记录为例来分析：

第一步：

IF(购买记录表!$B$2:$B$12=购买查询!$B$1,ROW(购买记录表!$B$2:$B$12),ROWS(购买记录表!B:B))

用IF函数，建立一新的数组，这一新的数组建立的规则是：

如果购买记录表!$B$2:$B$12区域中的单元格内容等于购买查询!$B$1单元格内容，则返回该单元格所在的行，否则返回全部工作表的行数。

所以：此部份返回的数组是：

{1048576;1048576;4;1048576;6;1048576;1048576;1048576;1048576;11;1048576;1048576}

可以看到：凡是购买记录表B列单元格内容等于李4的，返回的都是对应的行数，不等于李4的，返回的都是全部工作表的行数1048576。

第2步：

SMALL(IF(购买记录表!$B$2:$B$12=购买查询!$B$1,ROW(购买记录表!$B$2:$B$12),ROWS(购买记录表!B:B)),ROW(A1))

在第一步构成的数组中，查找第第一小的数值。

用ROW(A1)做SMALL函数的第2个参数，即第几小。

ROW(A1)是一个动态的数值，公式往下填充一行，行数加1，即当公式在B4单元格时，是ROW(A1)，当公式填充到B5单元格是，是ROW(A2)，当到B6单元格时，是ROW(A3)……

这样，就在第一步的数组中找到了第1、2、3小的值，即4、6、11。

第3步：

INDEX(购买记录表!B:B,SMALL(IF(购买记录表!$B$2:$B$12=购买查询!$B$1,ROW(购买记录表!$B$2:$B$12),ROWS(购买记录表!B:B)),ROW(A1)))

当公式在B4单元格时，返回购买记录表B列第4行的值，即顾客姓名李4。由于公式中IF部份是数组计算，所以公式以3键组合结束。

公式向下填充，得到B列购买记录表B列6、11行的值。

公式向右填充，自动变成查找购买记录表C列、Ｄ列４、6、11行的值。

第4步：

INDEX(购买记录表!B:B,SMALL(IF(购买记录表!$B$2:$B$12=购买查询!$B$1,ROW(购买记录表!$B$2:$B$12),ROWS(购买记录表!B:B)),ROW(A1)))&””

在最后加上&””，这一步是容错处理。用空单元格与空文本合并返回空文本的特质，将超越结果数量的部份不显示出来。

实现序号自动填充

在Ａ4单元格输入公式：

=IF(OR($B$1=””,B4=””),””,COUNTIF($B$4:B4,$B$1))&””

公式含义是：

如果$B$1姓名为空、或对应行Ｂ列为空，就不填充序号；否则，序号为Ｂ列姓名出现的次数。

COUNTIF($B$4:B4,$B$1)，是在一随着行数增加的区域查找B1单元格指定姓名出现的次数。