Question

excel中如何自动列出某个分数段的学生名字？

在sheet1中分别统计了学生的名字和成绩，总分在D列，如何在sheet2中C列（学生名单）中列出相应分数段的所有学生姓名呢？





Answer 1

方法1：

sheet2,C1:

=TEXTJOIN(",",1,IF(Sheet1!$D$3:$D$1000>=LEFT(A2,FIND("分",A2)-1)*1,Sheet1!$A$3:$A$1000,""))

编辑状态下同时按下Ctrl+Shift+Enetr,不用下拉

sheet2，C2:

=TEXTJOIN(",",1,IF((Sheet1!$D$3:$D$1000>=LEFT(A3,FIND("分",A3)-1)*1)*(Sheet1!$D$3:$D$1000<LEFT(A2,FIND("分",A2)-1)*1),Sheet1!$A$3:$A$1000,""))

编辑状态下同时按下Ctrl+Shift+Enetr,下拉

效果图sheet1:

效果图sheet2:

如果不想改的话Sheet2,C12:

=SUBSTITUTE(TEXTJOIN(",",1,IF(Sheet1!$D$3:$D$1000<=LEFT(A12,FIND("及",A12)-1)*1,Sheet1!$A$3:$A$1000,"")),",0","")

方法2：

Sheet2，C2：

=TEXTJOIN(",",1,IF((Sheet1!$D$3:$D$1000>=LEFT(A2,FIND("分",A2)-1)*1)*(Sheet1!$D$3:$D$1000<IFERROR(LEFT(A1,FIND("分",A1)-1)*1,150)),Sheet1!$A$3:$A$1000,""))

编辑状态下同时按下Ctrl+Shift+Enetr如图出现大括号,下拉

Answer 2

1. 先将sheet1的内容按总分从高到低排序，一定要排序，不排序名单会出错

2. 在sheet2的C2单元格输入=IFERROR(TEXTJOIN(",",,OFFSET(Sheet1!$A$2,SUM($B$1:$B1)+1,,B2)),"")

Answer 3

就是自动列出某个分段的学生们，他类似于像那个电子表格似的