如何在Excel中找出成绩小于60分的课程名称，并在相应位置罗列出来？

Question

本人要在L列中列出单个学生成绩不合格（小于60分）的课程名称，名称之间用顿号隔开，怎么用函数实现？

Answer 1

L2输入
=IF(C2<60,C$1,"")
回车并向右和向下填充。
或
输入
=IF(COLUMN(A:A)>COUNTIF($C2:$K2,"<60"),"",INDEX($C$1:$K$1,SMALL(IF($C2:$K2<60,COLUMN($A:$I),4^8),COLUMN(A:A))))
数组公式，输入后先不要回车，按Ctrl+Shift+Enter结束计算，再向右和向下填充。

Answer 2

=INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($1:$1)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=2,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($2:$2)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=3,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($3:$3)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=4,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($4:$4)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=5,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($5:$5)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=6,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($6:$6)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=7,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($7:$7)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=8,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($8:$8)))&""&IF(SUMPRODUCT((C2:K2<60)*1)>=9,"、","")&INDEX($1:$1,SMALL(IF(C2:K2<60,COLUMN(C:K),256),ROW($9:$9)))&""

CTRL+SHIFT+回车