Excel获取不重复数据结果(二)

Excel获取不重复数据结果(一)中介绍了利用公式获取不重复用户名的方法。通常情况下,我们希望更进一步:利用这些获取到的名称,来作为筛选项,然后查看每个筛选项的细项指标结果。例如,查看每个用户下单的订单明细:

操作方法:

1、在文件→选项→自定义功能区中,勾选开发工具选项

2、在开发工具选项卡中选择插入→列表框

3、选择列表框,右键选择设置控件格式,输入内容如下(即我们将数据来源中的数据放到控件列表中,控件将根据我们的选择将列表中人名对应的数字映射到E1单元格):

4、在A列添加数据序号,F1单元格输入:=VLOOKUP(E1,$A$2:$B$20,2,0)。用以返回选择的人名。

5、组织H1:L2表格,在H3单元格输入:=IFERROR(INDEX(订单明细!$A$1:$A$32,SMALL(IF(订单明细!$B$1:$B$32=$F$1,ROW(订单明细!$A$1:$A$32),99999),ROW(A1))),””) ,按ctrl+shift+enter结束。下拉即可获取F1单元格人名所对应的订单号明细。

6、由于订单号唯一,我们就可以利用简单点的offset函数获取订单对应的其他信息,在I3输入:=IFERROR(OFFSET(订单明细!$A$1,MATCH($H3,订单明细!$A$2:$A$32,0),COLUMN(A1),1,1),””) ,填充其他单元格即可。

主要难点为第5步中的公式,解读如下:

1、利用公式IF(订单明细!$B$1:$B$32=$F$1,ROW(订单明细!$A$1:$A$32),99999),返回一个列表LIST,即如果订单明细!$B$1:$B$32中的名称和F1相同,则返回该名称所在的行数,否则返回一个很大的值

2、再利用small函数,获取上面LIST中的第k个最小值,即该名称第k次出现的行号

3、利用index返回订单明细表中的该位置的订单号数据。

4、使用iferror函数进行美化。

百度网盘,文件下载:

链接: https://pan.baidu.com/s/10eWwu6KuvAahrIXT_eXBFQ

密码: jmbh

Excel获取不重复数据结果(一)

为一致为获取不重复的数据记录,可以利用Excel的高级筛选、数据透视表等功能进行操作。当高级筛选功能的好处在于快速,易操作,可以操作多列;坏处在于复用性差,数据量变更后得重新筛选。

我们可以利用公式完成对数据的去重操作,如下表中获取用户名称列表。

操作方法:

1、上表中,明细数据表命名为”订单明细”

2、在结果表中,B2中,输入=IFERROR(INDEX(订单明细!$B$2:$B$32,MATCH(,COUNTIF($B$1:B1,订单明细!$B$2:$B$32),)),””),按ctrl+shift+enter结束,向下拉到结果显示为空时,表示已经获取了所有用户名称。

以上公式的原理在于:

首先使用COUNTIF计算在$B$2:$B$32区域中是否存在$B$1:B1区域的值,如果有则返回1,没有则返回0。此处返回一个1和0匹配列表LIST,1表示这个用户名已经出现在结果区域了

再利用match去匹配第一个0在LIST中的位置,返回具体的位置代号

最后使用INDEX函数,获取订单明细!$B$2:$B$32对应LIST位置的未出现过的用户名

IFERROR为美化修饰函数,使表格看起来更整洁。

文档下载请参见第二篇。

Excel获取不重复数据结果(二)