Excel:重复名次也可以查姓名成绩
发布时间:2023-04-04 09:05:40
当老师的,对分析学生成绩大概有瘾。这不,本来我们已经把学生各学科的成绩、总分、名次都排出来了,并按照总分进行了升序排序,但现在又有任课老师过来要求希望能够把自己学科的前10名的学生姓名及成绩找出来。按理说,这个要求并不是很困难,但是麻烦就在于学生各科名次有当老师的,对分析学生成绩大概有瘾。这不,本来我们已经把学生各学科的成绩、总分、名次都排出来了,并按照总分进行了升序排序,但现在又有任课老师过来要求希望能够把自己学科的前10名的学生姓名及成绩找出来。按理说,这个要求并不是很困难,但是麻烦就在于学生各科名次有可能相同,这样的话,前10名的学生其实不一定是10个人,有可能更多。每个学科都要这么做的话,工作量也不小,所以,还是得靠函数和公式来帮忙。
图1 原始成绩表
原始的成绩表如图1所示。姓名位于C2:C92单元格,语文成绩位于D2:D92单元格区域。我们就以查找语文学科的前10名成绩及学生姓名为例。为方便比较结果,图1中我们已经将数据按语文成绩降序进行了排序,实际操作中是不需要事先排序的。
一、名次表的建立
前面我们说过,我们不太容易确定排在前10名的学生共有多少,所以,我们需要使用公式将它们找出来。当然,最好顺便将名次表填写出来。完成结果如图2所示。
图2 成绩排序
将鼠标定位于X3单元格,然后在编辑栏输入公式“=TEXT(SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92)),"第G/通用格式名")”,回车后就可以得到“第1名”的结果。选定X3单元格,向下拖动其填充句柄至出现“第11名”为止。
这里用到了几个函数,感觉上比较复杂。其实思路是这样的:“ROW(1:1)”的结果是“1”,而“LARGE($D$2:$D$92,1)”的结果是在指定的单元格区域中最大的一个数;那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))”可以理解为拿D2:D92单元格区域中的数据与该区域中最大值比较,大于或等于该值及小于该值的则会分别以“TRUE”、“FALSE”的结果保存在一个数组中。
公式中“COUNTIF($D$2:$D$92,$D$2:$D$92))”部分则会统计D2:D92单元格区域中每一个数值出现的次数,也分别保存到一个数组中。所以,我们所用公式中“SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92))”在执行时会得到一个类似于“SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…}/{1;1;2;2;1;2;2;1;2;2;2;2;1;…})”的结果。两个数组中的对应的数据分别相除,再将所有的商相加,正是分数所对应的名次。这种方法即使名次是并列的,也不会影响显示效果。
至于最外层的TEXT函数,则是将得到的结果转换为按指定数字格式表示的文本。也就是本来内层公式运算的结果是数字“1”,现在我们将它显示为“第1名”。
二、分数的查找
将鼠标定位于Y3单元格,在编辑栏中输入如下公式“=INDEX($D$2:$D$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”,然后按下“Ctrl Shift Enter”快捷键,完成数组公式的输入。这一步很关键的,否则不会出现正确的结果。
向下拖动Y3单元格的填充句柄向下至最后一个单元格完成公式的复制。
我们还是简单解释一下公式的思路。
由于D2:D92区域中有很多数据是重复的,这给我们造成了困难。所以,我们要想办法使每一数据都变成唯一。公式中“$D$2:$D$92 1/ROW($D$2:$D$92)”就是给D2:D92区域中每一个数据都加了该数据对应行数的倒数。由于每一数据对应的行数是不一样的,这样,就会使每一数据都变成了唯一的值,并保存到了一个数组中。
公式中的“LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1))”还是返回了上面所得数组中的最大值。本例中的结果是“{96.5}”。
公式中“MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0)”返回的是刚刚得到的最大值在数组中的位置。本例中的结果是“{1}”。
这样,其实Excel最后执行的查询就是“INDEX($D$2:$D$92,1)”了,自然可以返回在$D$2:$D$92区域中的第一个值了。
三、姓名的查找
将鼠标定位于Z3单元格,在编辑栏中输入公式“=INDEX($C$2:$C$92,MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92 1/ROW($D$2:$D$92),0))”,同样按下“Ctrl Shift Enter”快捷键完成数组公式的输入。
向下拖动Z3单元格的填充句柄向下至最后一个单元格完成公式的复制。最后的效果如图3所示。
图3 完成公式的复制
其实您肯定已经明白了,姓名的查找与前面分数的查找是一样的。公式本身也没有什么大的变化。所以,明白了前面的方法,要查找别的什么东西也就方便了。
其它的学科可以照此办理。只要注意变换一下公式中的单元格区域就可以了,我这里就不罗嗦了。
猜你喜欢
- Excel办公技巧可以说是办公中的宝,相信也是大家最宠爱的办公工具之一,其实Excel表格的制作并不仅仅只能统计数据,我们还可以拿来做一些不
- 选中一部分数据时候,excel的状态栏总会显示一部分的数据统计信息,例如数据项本数量,平均值等等。那如何让状态栏显示更多的内容呢?下面小编就
- PDF文档怎么将背景色设置成护眼色?经常需要制作讲解pdf课件,长时间对着白色感觉眼睛很干涩疼痛,香江pdf设置成保护眼睛的背景色,该怎么设
- 最近,一些用户更新了win10版本2004,错误代码为0x800f0955,但他们不知道如何解决。此代码的主要原因是缺少系统文件。解决办法很
- 对数据进行排序并非Excel表格的专利,在Word 2007中同样可以对表格中的数字、文字和日期数据进行排序操作对数据进行排序并非Excel
- “我不会排序。”“即使点了自动筛选,下方数据也不在筛选范围。”你是否也遇到上述的烦恼呢?用 Excel 制作表格时,“排序”、“自动筛选”都
- 前面我们已经知道了在word 2007文档中插入图片的方法,而插入图片后,会发现,图片与文档的内容不是很融合,这只要是图片的背景造成,而现在
- Word是Microsoft公司开发的Office办公组件之一,主要用于文字处理工作。Word文档中合适的行间距能够让我们的文档浏览起来更加
- 这篇教程主要是向脚本之家的朋友介绍excel中复制选择性粘贴的使用方法,教程比较不错,很适合办公人员学习,希望能对大家有所帮助excel中复
- Win10系统如何去除桌面图标的阴影呢?最近有很多的用户反映自己的电脑桌面上的软件图标有阴影,这看起来很不舒服,很多用户想把它去掉,但是又不
- 在word中复制以及粘贴表格要根据其所复制表格(或行)的粘贴位置不同,主要有下面几种具体情况:一、粘贴行要把复制对象加入到某一表格内部,可以
- 我们办公时经常会用到Word进行文档编制和排版。刚开始用WORD的人,面对软件界面众多的工具栏和按纽往往有种不知从何下手,那么word文档如
- Excel是我们经常使用的办公软件,excel表格单元格中间怎么添加线条?今天小编将为大家带来的是excel表格单元格中间怎么添加线条的方法
- 刘会计正准备给公司做个财务报表单,做到一半的时候突然不知道人民币符号怎么打了,以前遇到这种事情的时候都是在网上搜索一下,然后将网上别人输入好
- 具体设置方法如下:1、点击左上角的WPS文字——选项; 2、点击左侧的选项,切换到“拼写检查”; &
- 这篇教程是向脚本之家的朋友分享在excel表格中使用vba函数打开工作薄方法,方法很简单的,有需要的朋友可以参考本文,来看看吧excel中可
- 如何在Word 2007中创建宏:音频记录?Microsoft Words附带了几个预设的键盘快捷键。例如,您可以按住Control键并按字
- 如何在Word 2016中打印整个文档?在Word 2016中打印文档很容易。但是,在打印之前,请确保预览文档,以免对打印结果感到惊讶。请按
- 许多同学在文章开头手动做了个目录,但是自己反复增删、导师不断修改后,每次都用辛苦地把目录重新编排一遍。要把目录里的页码和实际的页码核对一致更
- 1、样式的排版:1、可以在Word 2007自带的样式编辑器里面对样式进行新建、修改和删除。在Word 2007里面,对样式的操作主要是集中