Excel-多条件筛选资料(OFFSET,SMALL,ROW)
根据 Excel 资料表中(参考下左)的资料清单,想要筛选出合于类别并且和指定数量接近的资料,该如何处理?
本例要依两个条件:类别、数量,来筛选资料。以指定数量-99~+99为接近值。
【公式设计与解析】
选取储存格A1:E100,按 Ctrl+Shift+F3 键,勾选「顶端列」,定义名称:项目、类别、编号、数量、状态。
储存格H2:{=IFERROR(OFFSET($A$1,SMALL(IF((类别=$G$2)*(数量<=$G$4+99)*
(数量>=$G$4-99),ROW(编号),""),ROW(1:1))-1,0),"")}
这是阵列公式,公式输入完成要按 Ctrl+Shift+Enter 键,Excel 会自动加上「{}」。
复制储存格H2,贴至储存格H2:H15。
(1) IF((类别=$G$2)*(数量<=$G$4+99)*(数量>=$G$4-99),ROW(编号),"")
条件一:(类别=$G$2),
因为指定数量-100~+100为接近值,所以:
条件二:(数量<=$G$4+99)*(数量>=$G$4-99)
其中的「*」运算相当于执行逻辑 AND 运算。
在阵列公式中,当合于二个条件者,会传回对应的储存格列号,否则传回空字串。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函数根据第(1)式传回的列号,由小至大取出列号。当公式向下复制时,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET($A$1,第(2)式-1,0)
将第(2)式取出的列号代入 OFFSET 函数,可以取得对应的储存格内容。
(4) IFERROR(第(3)式,"")
当公式向下复制时,若传回错误讯息,则以 IFERROR 函数转换为空字串。
同理,
储存格I2:{=IFERROR(OFFSET($C$1,SMALL(IF((类别=$G$2)*(数量<=$G$4+99)*
(数量>=$G$4-99),ROW(编号),""),ROW(1:1))-1,0),"")}
储存格J2:{=IFERROR(OFFSET($D$1,SMALL(IF((类别=$G$2)*(数量<=$G$4+99)*
(数量>=$G$4-99),ROW(编号),""),ROW(1:1))-1,0),"")}