利用rank函数降序排名
时间:2023-06-04 23:38:59
利用rank函数降序排名!让我们通过下面的例子学习吧!期末考试结束了,初三(14)班的班主任助理王老师需要对本班学生的各科考试成绩进行统计分析,按照下列要求完成该班的成绩统计工作。
1. 在考生文件夹下,将“Excel素材.xlsx”文件另存为“Excel.xlsx”(“.xlsx”为扩展名),后续操作均基于此文件,否则不得分。
2. 在工作簿“Excel.xlsx”最左侧插入一个空白工作表,重命名为“初三学生档案”,并将该工作表标签颜色设为“紫色(标准色)”。
3. 将以制表符分隔的文本文件“学生档案.txt”自A1单元格开始导入到工作表“初三学生档案”中,注意不得改变原始数据的排列顺序。将第1列数据从左到右依次分成“学号”和“姓名”两列显示。最后创建一个名为“档案”、包含数据区域A1:G56、包含标题的表,同时删除外部链接。
4. 在工作表“初三学生档案”中,利用公式及函数依次输入每个学生的性别“男”或“女”、出生日期“××××年××月××日”和年龄。其中:身份证号的倒数第2位用于判断性别,奇数为男性,偶数为女性;身份证号的第7~14位代表出生年月日;年龄需要按周岁计算,满1年才计1岁。最后适当调整工作表的行高和列宽、对齐方式等,以方便阅读。
5. 参考工作表“初三学生档案”,在工作表“语文”中输入与学号对应的“姓名”;按照平时、期中、期末成绩各占30%、30%、40%的比例计算每个学生的“学期成绩”并填入相应单元格中;按成绩由高到低的顺序统计每个学生的“学期成绩”排名并按“第n名”的形式填入“班级名次”列中;按照下列条件填写“期末总评”:
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_0.png)
6. 将工作表“语文”的格式全部应用到其他科目工作表中,包括行高(各行行高均为22默认单位)和列宽(各列列宽均为14默认单位)。并按上述4.中的要求依次输入或统计其他科目的“姓名”、“学期成绩”、“班级名次”和“期末总评”。
7. 分别将各科的“学期成绩”引入到工作表“期末总成绩”的相应列中,在工作表“期末总成绩”中依次引入姓名、计算各科的平均分、每个学生的总分,并按成绩由高到低的顺序统计每个学生的总分排名、并以1、2、3……形式标识名次,最后将所有成绩的数字格式设为数值、保留两位小数。
8. 在工作表“期末总成绩”中分别用红色(标准色)和加粗格式标出各科第一名成绩。同时将前10名的总分成绩用浅蓝色填充。
9. 调整工作表“期末总成绩”的页面布局以便打印:纸张方向为横向,缩减打印输出使得所有列只占一个页面宽(但不得缩小列宽),水平居中打印在纸上。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_1.jpg)
对 应 步 骤
1、打开考生文件夹,复制Excel素材,粘贴,重命名为Excel。打开这份Excel做题即可。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_2.jpg)
点击加号创建新工作表,右击重命名为初三学生档案。标签颜色改为紫色。并按住鼠标左键拖动到最左边。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_3.png)
3、数据选项卡→自文本→选择学生档案→导入
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_4.jpg)
语言选择简体中文936→下一步
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_5.jpg)
分割符号选择Tab键→下一步
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_6.jpg)
选中身份证号码这一列→改成文本格式→下一步
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_7.jpg)
选择位置为A1→确定
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_8.jpg)
接下来对A列进行分列,在身份证号码前插入一个新列
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_9.jpg)
选择A列一整列→数据选项卡→分列
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_10.jpg)
分隔符选择固定宽度→下一步
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_11.jpg)
光标定位学号和姓名中间,画一条线→下一步直到完成
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_12.jpg)
把姓名剪切到B列
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_13.png)
选中A1:G56区域→套用表格样式→随便选择一个样式即可
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_14.jpg)
勾选表包含标题→确定
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_15.png)
点确定后会弹出此对话框,选择是
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_16.jpg)
然后选中表区域→在名称框输入档案→回车确定
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_17.jpg)
4、下面填写性别,出生日期,和年龄这三列。
首先身份证号的倒数第2位用于判断性别,奇数为男性,偶数为女性。先在身份证号码中提取出来倒数第二位,用mid函数提取即可。
mid(提取的数据,提取的位置,提取几位)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_18.png)
再用mod函数判断奇偶。mod取余函数(被除数,除数)
如果倒数第二位/2=0,那么就是偶数,否则就是奇数
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_19.png)
现在用if函数判断,如果为1,那么返回男,否则返回女。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_20.jpg)
现在求出生日期,用mid函数分别提取第7位-第14位,即年月日的对应数字,即
年:MID([@身份证号码],7,4)
月:MID([@身份证号码],11,2)
日:MID([@身份证号码],13,2)
然后用date函数合起来即可,date函数参数(年,月,日)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_21.jpg)
然后选择日期的区域即E2:E56,改成yyyy”年”mm”月”dd”日”的格式,显示##时是因为列宽不够,拉宽点即可
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_22.png)
现在求出生年龄,求周岁需要用到datedif函数,即求两个日期直接的整年数。
datedif(开始日期,结束日期,整年数用y表示)
开始日期为出生的第一天,结束日期为今天,今天是指可以随着时间变化而变化的今天,即今天显示为2019/5/15,明天就显示为2019/5/16。需要用到today函数,即可以随着时间变化而变化的日期。today没有参数。即每一天的日期=today()
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_23.jpg)
最后适当增加行高和列宽,居中对齐。
5、输入姓名需要根据学号,查找对应的姓名。即用vlookup函数查找
vlookup(要查找的数据,查找的范围,返回的值在范围的第几行,0)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_24.png)
根据题目要求,得出学期成绩=平时*30%+期中*30%+期末*40%,即
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_25.png)
求排名用rank函数,rank(排名的数据,排名的范围)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_26.png)
用&链接符,链接第和名这两个字。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_27.png)
期末总评用if函数判断即可。如果成绩>=102,显示为优秀,如果成绩>=84,显示为良好,如果成绩>=72,显示为及格,否则显示为不合格。即:
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_28.png)
6、这道题需要做6个表,我们先点击格式刷,然后按住ctrl键选择依次6个表,此时6个表会变成工作组的状态,修改一个,6个会同时变化。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_29.png)
这时候格式刷刷其中一个表即可。6个会同时变化。
在选中6个表的状态下,改行高为22,列宽为14
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_30.jpg)
着,还是在选中的状态,姓名列输入一个等于号=,然后直接选中语文表中的姓名单元格,直接回车确定
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_31.jpg)
同理选中6个表,用公式填写学期成绩和班级名次。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_32.png)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_33.jpg)
最后的期末总评的题目要求的是,大于90优秀,大于75良好,大于60及格,其余不合格。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_34.jpg)
7、最后的期末成绩表,还是输入一个等于号,然后点击各表对应的单元格,回车确定就可以引用过来了
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_35.jpg)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_36.jpg)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_37.png)
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_38.png)
同理其他各科也都是引用对应表的F2单元格。
总分用sum函数求和即可。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_39.png)
然后选中,单击单元格右下角自动填充
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_40.png)
最后求排名,rank函数(排名的数据,排名的范围),范围要进行锁定不能发生变化。
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_41.jpg)
利用average求平均值,然后向右填充
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_42.png)
选中C3:J46单元格→右击设置单元格格式→数值→保留两位小数
8、选中语文这一列数据→条件格式→项目选取规则→前10项
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_43.jpg)
把10改为1→ 自定义格式
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_44.jpg)
字体选中红色,字形选择加粗
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_45.png)
然后格式刷,刷每一科对应的数据,一列一列的刷,不能一起刷
选中总分这一列的数据,不要选择最后一行,项目选取规则,自定义格式为浅蓝色填充
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_46.jpg)
9、页面布局→页面设置对话框启动器→纸张方向→横向→一页高一页宽
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_47.jpg)
点击页边距→水平居中
![利用rank函数降序排名](https://img.aspxhome.com/file/2023/5/a167715_48.jpg)
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Excel抽奖小系统,简单修改一下可直接套用
![](https://img.aspxhome.com/file/2023/2/a154072_0s.jpg)
竖线在电脑上怎么打
![](https://img.aspxhome.com/file/2023/9/a343027_0s.png)
小编教你罗技鼠标g300s怎么安装驱动
![](https://img.aspxhome.com/file/2023/4/a304133_0s.png)
Win10系统怎么开启键入见解?Win10系统开启键入见解的方法
![](https://img.aspxhome.com/file/2023/26/a219718_0s.png)
csrss.exe是什么程序
![](https://img.aspxhome.com/file/2023/9/a342223_0s.png)
Word2003如何手工绘制表格?
PowerPoint2013提示“无法打开此种文件类型”故障解决
![](https://img.aspxhome.com/file/2023/10/a351649_0s.jpg)
WinXP右下角提示延缓写入失败文件可能损坏或丢失
![](https://img.aspxhome.com/file/2023/29/a252417_0s.jpg)
Excel二维饼形图转换为三维立体饼图的方法是什么
360误删文件恢复 360误删的文件怎么恢复
金山毒霸如何关闭热点?金山毒霸关闭热点教程
![](https://img.aspxhome.com/file/2023/8/a337661_0s.jpg)
usb2.0和3.0的区别_usb2.0和3.0的区别介绍
![](https://img.aspxhome.com/file/2023/3/a300397_0s.png)
excel四舍五入取整公式
![](https://img.aspxhome.com/file/2023/5/55175_0s.png)
在excel单元格中怎么设置以进度条/柱形图来显示百分比?
![](https://img.aspxhome.com/file/2023/6/42346_0s.jpg)
Word怎么在默认视图打开不可编辑文件
![](https://img.aspxhome.com/file/2023/8/32008_0s.png)
Win7笔记本键盘失灵外接也没用的解决方法
![](https://img.aspxhome.com/file/2023/3/a297030_0s.jpg)
wps怎么绘制条形码图文教程
使用Wirecast Pro 12 for Mac与无人机解决方案介绍
麒麟9000处理器怎么样-麒麟9000评测、跑分、参数、相关手机及报价
![](https://img.aspxhome.com/file/2023/3/a296716_0s.jpg)
word怎么取消文本框的轮廓线
![](https://img.aspxhome.com/file/2023/3/31873_0s.jpg)