Excel——使用INDEX和SMALL实现条件筛选

时间:2023-12-30 17:16:56

如下图所示,如何实现Excel自带的筛选功能呢?(对的,就是软件自带的功能)

Excel——使用INDEX和SMALL实现条件筛选

如何实现:

B13的公式:=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$10=B$12,ROW($1:$10)),ROW(A1))),"")

C13的公式:=IFERROR(INDEX(C:C,SMALL(IF(A$1:A$10=B$12,ROW($1:$10)),ROW(A1))),"")


一、对B12设置数据有效性:

先设置数据源F12:F15,包含所有类别的问题

Excel——使用INDEX和SMALL实现条件筛选设置有效性条件为序列,来源为F12:F15
Excel——使用INDEX和SMALL实现条件筛选


二、设计虚拟数值

C2位置:=ROUND(RAND()*100,0)

RAND()返回0~1的随机数;

ROUND,对数值进行四舍五入。


三、筛选对应的行数

=IF(A$1:A$10=B$12,ROW($1:$10))

如果故障类型一列中有与筛选项目相同的内容,返回其行数。如下图所示:

注意:先选中F1:F10,输入公式后,按住Ctrl+Shift,敲击Enter。不能先按住Ctrl+Shift,敲击Enter,再向下填充。

Excel——使用INDEX和SMALL实现条件筛选


四、将行数列以升序返回有效数值

=SMALL(IF(A$1:A$10=B$12,ROW($1:$10)),ROW(A1))

以升序返回数据列的有效数字。如下图所示:

Excel——使用INDEX和SMALL实现条件筛选

注意:先在F1中输入函数后,按住Ctrl+Shift,敲击Enter后,向下填充。与上面的输入方法不同。


五、返回对应行数的机型

=INDEX(B:B,SMALL(IF(A$1:A$10=B$12,ROW($1:$10)),ROW(A1)))

返回B列中对应行数的单元格内容。

注意:与上面一样,先在F1中输入函数后,按住Ctrl+Shift,敲击Enter后,向下填充。

Excel——使用INDEX和SMALL实现条件筛选


六、使用IFERROR解决错误值的显示问题。

=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$10=B$12,ROW($1:$10)),ROW(A1))),"")

注意:与上面一样,先在F1中输入函数后,按住Ctrl+Shift,敲击Enter后,向下填充。Excel——使用INDEX和SMALL实现条件筛选


七、数目一列,原理与故障类别一致,将B列改为C列即可。

=IFERROR(INDEX(C:C,SMALL(IF(A$1:A$10=B$12,ROW($1:$10)),ROW(A1))),"")

Excel——使用INDEX和SMALL实现条件筛选