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)))" "、"，以三键组合结束。

向右下方填写公式，获取单元格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。

第二步：

小(如果(购买记录表！$B$2:$B$12=购买询价！$B$1，ROW(购买记录表！$B$2:$B$12)，ROWS(购买记录表！B:B))、ROW(A1))

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

使用ROW(A1)作为SMALL函数的第二个参数，它是最小的。

ROW(A1)为动态数值，公式填充一行，行数增加一，即公式在单元格B4时为ROW(A1)，公式在单元格B5填充时为ROW(A2)，到达单元格B6时为row (a3).

这样，在第一步的数组中找到了第一个、第二个和第三个小值，即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列第四行的值，即客户名称李四。因为公式的IF部分是数组计算，所以公式以三键组合结束。

填写公式，得到B采购记录表第6列和第11列的值。

公式被填充到右边，它自动成为搜索购买记录表的C列和D列中第4、6和11行的值。

第四步：

INDEX(购买记录表！B:B，SMALL(IF)(购买记录表！$B$2:$B$12=购买询价！$B$1，ROW(购买记录表！$B$2:$B$12)，ROWS(购买记录表！B:B))、ROW(A1)))" "

在最后加上“”，这一步就是容错处理。将空单元格与空文本相结合以返回空文本的功能不会显示超出结果数量的部分。

实现序列号自动填充。

在单元格a4中输入公式：

=IF(OR($B$1="，" B4=" "，"，COUNTIF($B$4:B4，$B$1))" "

该公式的意思是：

如果$B$1的名称为空，或者对应行的B列为空，则不会填充序列号。否则，序列号是该名称在列B中出现的次数.

COUNTIF($B$4:B4，$B$1)是B1单元格的指定名称在行数增加的区域中出现的次数。