Excel-双条件查询(SUMPRODUCT,INDEX,MATCH)

爱必应

学校同事问到:

在 Excel 中,若要根据学生名条里的班级和座号二个条件,如何查询其姓名?

如下图,想要查询指定的班级和座号所对应的姓名,该如何处理?

Excel-双条件查询(SUMPRODUCT,INDEX,MATCH)

 

【公式设计与解析】

选取储存格A1:C26,按 Ctrl+Shift+F3 键,定义名称:班级、座号、姓名。

1. 使用阵列公式

储存格G2:{=INDEX(姓名,MATCH(1,(班级=E2)*(座号=F2),0),0)}

输入完成要按 Ctrl+Shift+Enter 键,Excel 会自动加上「{}」。

複製储存格G2,贴至储存格G2:G7。

(1) MATCH(1,(班级=E2)*(座号=F2),0)

在阵列公式中,利用 MACTH 函数里的双条件:班级=E2和座号=F2,找寻传回结果为「1」的位置。因为班级和座号的排列组合具唯一性,其会传回 0 和 1 数字组成的阵列,而且其中只有一个是 1。

其中 班级=E2 和 座号=F2 会传回判断结果的 TRUE/FALSE 阵列,而「*」运算子相当于执行逻辑 AND 运算,运算过程中会将 TRUE/FALSE 阵列,转换为 1/0。

(2) INDEX(姓名,第(1)式,0)

将式子(1)传回的位置代入 INDEX 函数,传回对应的姓名。

 

2. 使用非阵列公式

储存格G2:=INDEX(姓名,SUMPRODUCT((班级=E2)*(座号=F2)*ROW(姓名))-1,0)

複製储存格G2,贴至储存格G2:G7。

(1) SUMPRODUCT((班级=E2)*(座号=F2)*ROW(姓名))-1

SUMPRODUCT 函数中利用双条件:班级=E2和座号=F2,传回对应姓名的列号。因为班级和座号的排列组合具唯一性,所以传回的列号就是班级和座号对应的姓名位置。(-1在此的用意是因为第一个姓名是从第 2 列开始)

(2) INDEX(姓名,第(1)式-1,0)

将式子(1)传回的位置代入 INDEX 函数,传回对应的姓名。

原文链接:,转发请注明来源!

发表评论