求各班级前五名和整级前十名（中式排名）-《Excel应用大全》系列论坛-ExcelHome技术论坛

求各班级前五名和整级前十名（中式排名）

DIANNAOYUAN Lv.2

2025-9-18 23:49 关注

求各班级前五名和整级前十名（中式排名）

提取前十名和前五名分数段人数 - 副本.zip 2025-9-18 23:49 上传

37.97 KB, 下载次数: 13

赏

227阅读

9回复倒序

3650659193 Lv.2 2楼

2025-9-19 10:20

提示: 作者被禁止或删除内容自动屏蔽

3650659193 Lv.2 3楼

2025-9-19 10:20

提示: 作者被禁止或删除内容自动屏蔽

hcm19522 Lv.7 4楼

2025-9-19 11:47

J4 輔助=SUMPRODUCT((B$4:B$474=B4)*(I$4:I$474>I4)/COUNTIFS(B$4:B$474,B$4:B$474,I$4:I$474,I$4:I$474))+1

K5下拉右拉{=IFERROR(OFFSET($C$1,RIGHT(SMALL(IF(($B$4:$B$474=IF(K$3<>"",K$3,J$3))*($J$4:$J$474<=5),$J$4:$J$474/1%%+ROW($4:$474)),ROW(A1)),4)-1,(K$3=$L$3)*7),"")

zpy2 Lv.7 5楼

2025-9-19 14:56

//SELECT * FROM `总分表` LIMIT 20;
create temp table aa as
SELECT 姓名,dense_rank() over( partition by 班别 order by 总分 desc) 名次,班别 FROM `总分表`;
create temp table bb as
select 班别||'</td><td>' as '班别',姓名 ||'</td><td>'||名次 d from aa where 名次<=5 order by 班别 COLLATE NATURAL_CMP;
//

//select * from bb;
cli_create_two_dim~bb~班别~d;
select colIdxf[0:]{iif(`%s` is null,'</td><td>',`%s`) `%s`} from bb_two_dim;

Screenshot_2025-09-19-14-54-03-711_com.mmbox.xbrowser.pro.jpg

zpy2 Lv.7 6楼

2025-9-19 15:05

引用: zpy2 发表于 2025-9-19 14:56
//SELECT * FROM `总分表` LIMIT 20;
create temp table aa as
SELECT 姓名,dense_rank() over( par ...

//SELECT * FROM `总分表` LIMIT 20;
create temp table aa as
SELECT 姓名,dense_rank() over( partition by 班别 order by 总分 desc) 名次,班别 FROM `总分表`;
create temp table bb as
select 班别||'</td><td>' as '班别',姓名 ||'</td><td>'||名次 d from aa where 名次<=5 order by 班别 COLLATE NATURAL_CMP;
//

//select * from bb;
cli_create_two_dim~bb~班别~d;
select colIdxf[0:]{'姓名</td><td>名次' `%s`} from bb_two_dim where rowid=1 union all
select colIdxf[0:]{iif(`%s` is null,'</td><td>',`%s`) `%s`} from bb_two_dim;