表格怎么把成绩排名由高到低排序(怎么从表格中筛选出多项想要的数据)
我们为什么要用 O365 版本?因为高阶函数太香了。
案例:
在下图 1 中左侧的数据表中查找出“实际完成“前 n 名的人,将整行数据填写在右侧的表中。
- 结果按“实际完成”值降序排序;
- n 值变化时,右侧的结果自动更新;
- 结果表格的边框会随着区域的增减自动出现和消失。
效果如下图 2、3 所示。
解决方案:
今天的公式,会用到不少高阶的 O365 函数。
1. 在 G2 单元格中输入以下公式:
=INDEX(SORTBY(A2:A13,C2:C13,-1),SEQUENCE(E2))
公式释义:
- SORTBY(A2:A13,C2:C13,-1):
- 参数含义为 (要列出的区域, 按哪个区域排序, 降序排序);
- 这里表示按 C 列降序排序后,将对应的 A 列区域罗列出来;
- SEQUENCE(E2):生成从 1 开始,步长为 1,截止数为 E2 单元格数值的一组序列,即 {1;2;3};
- INDEX(...,...):在第一个参数的区域中提取出排在序列中第 m 位的值,m 为第二个参数的值;由于第二个参数是数组,所以最终提取出来的是一个动态数组区域
2. 在 H2 单元格中输入以下公式 --> 下拉复制公式:
=IF(ISBLANK(G2),"",XLOOKUP(G2,A:A,B:C))
公式释义:
- ISBLANK(G2),"":如果 G 列为空则显示空值;
- XLOOKUP(G2,A:A,B:C):否则,在 A 列查找 G2 单元格的值,返回 B、C 列的对应行
到了这里还有个小问题:查找结果表没有边框,强迫症表示不能忍。
3. 选中 G2:I13 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”
4. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”:
=G2<>""
5. 在弹出的对话框中选择“边框”选项卡 --> 选择“外边框”--> 点击“确定”
6. 点击“确定”
现在数据表的非空区域就自动添加了边框。
修改名次,不仅查询结果会自动更新,数据表边框也会自动出现。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。
专栏
Excel从入门到精通
作者:Excel学习世界
99币
77人已购
查看
赞 (0)