【原】Excel215 | 跨表查询：查询指定顾客的购买记录

每天清晨六点，准时与您相约

问题来源

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

根据“购买记录表”中的记录，如下：

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

本问题，有两个关键点：

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)))&""，，以<Ctrl+Shift+Enter>三键组合结束。

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

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

第一步：

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列单元格内容等于李四的，返回的都是对应的行数，不等于李四的，返回的都是整个工作表的行数1048576。

第二步：

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

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

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

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

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

第三步：

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

当公式在B4单元格时，返回购买记录表B列第4行的值，即顾客姓名李四。因为公式中IF部分是数组计算，所以公式以<Ctrl+Shift+Enter>三键组合结束。

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

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

第四步：

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单元格指定姓名出现的次数。

---