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。
第二步:
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列、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单元格指定姓名出现的次数。