首页 > Excel专区 > Excel教程 >

excel表格跨表查询:查询指定顾客的购买记录

Excel教程 2021-12-29 21:27:45

今天一位朋友传来数据表,要求:

根据“购买记录表”中的记录,如下:

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

本问题,有两个关键点:

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


标签: excel函数excel函数公式excel表格制作excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.