今天一位朋友传来数据表，要求：
根据“购买记录表”中的记录，如下：
在“购买查询”表中实现查询指定顾客的购买记录，效果如下：
本问题，有两个关键点：
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。
第二步：
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部分是数组计算，所以公式以三键组合结束。
公式向下填充，得到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单元格指定姓名出现的次数。
网友评论