wps表格教程:用ET表格巧妙解决多条件下的成绩统计
发布时间:2023-05-12 13:04:24
在ET表格中,对于班级成绩册的各科求总分、平均分以及排名次等,都可算是轻松的任务。但是,对于多年级多班级混合编排的班级,如果想再类似的操作,那又该如何进行呢?
图1
图1所示为不同学校不同专业混编的示意成绩册。我们需要计算各学校各专业各科目的总分和平均分,并对各学生在各自学校各自专业内排定名次。这项看起来艰巨复杂的任务,在ET表格中正确使用SUMPRODUCT函数,那么完成起来其实也并不算怎么困难。咱们只需如此操作即可:
一、多条件求和
比如我们需要计算工业职专机电专业所有学生的语文成绩总分。分析表格可以看到:学校名称在B2:B16单元格区域,专业名称在C2:C16区域,语文成绩则分布在D2:D16区域。在合适的单元格输入公式“=SUMPRODUCT((B2:B16="工业职专")*(C2:C16="机电"),D2:D16)”,回车后可以得到结果“228”了。看出来了吧?公式中的前两个小括号内就是需要满足的条件,而“D2:D16”就表示需要求和的区域。如果有更多的条件,那就再在前面加小括号就可以了。要注意的是小括号中间用“*”连接。
至于我们希望的各学校各专业各科目的总分,那就得设计如图2所示表格来解决了。
图2
先建好空白表格,如图2所示录入学校名称和专业名称。在N2单元格输入公式 “=SUMPRODUCT(($B$2:$B$16=$L3)*($C$2:$C$16=$M3),D$2:D$16)”,选中该单元格的填充句柄,向右和向下拖动复制公式至整个表格,那么各学校各专业各学科的总分就有了。不太难吧?
由于各学校的专业设置并不相同,所以某些的某些专业的各科总分计算为“0”。如果想追求尽善尽美,那么我们可以用IF函数,当结果为“0”时不显示任何内容,这样可以使结果显示尽量美观一些。公式为“=IF(SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16)=0,"",SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16))”。
二、多条件求平均分
求和与求平均分只有一步之遥,那就是需要统计出各学校每个专业的人数。有了它,那么平均分就简单多了。这同样可以使用SUMPRODUCT函数来完成。
图3
在如图3所示的人数统计表的N18单元格输入公式“=SUMPRODUCT(($B$2:$B$16=$L18)*($C$2:$C$16=$M18)*(D$2:D$16<>""))”,然后向右向下复制公式就可以了。
求平均分即拿总分除以人数。因此,如果人数为“0”,那么求平均分时就会出现问题。因此,在求平均分时,同样需要用IF函数做一个判断。借用前面的两个表格,那么工业职专机电专业的语文平均分公式可写为“=IF(ISERROR(N3/N18),"",N3/N18)”,其余的复制公式即可,如图4所示。
图4
公式中的ISERROR(N3/N18)作用判断“N3/N18”结果是否会出现错误。
如果直接不借助人数统计表格直接求平均分,那么该公式可写为“=IF(SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3)*(D$2:D$16<>""))=0,"",SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16)/SUMPRODUCT(($B$2:$B$16=$L3)*($C$2:$C$16=$M3)* (D$2:D$16<>"")))”。
三、多条件下的排名次
在不破坏原来数据表排序的情况下,为每位学生排定在本校本专业内的名次,看起来很难,但是有了SUMPRODUCT就不一样了。试想一下,借助于SUMPRODUCT函数,我们可以轻松统计出符合多个条件的人数。那么工业职专机电专业总分为“616”的同学的名次,不就是学校为“工业职专”、专业为“机电”、总分“>616”的人数再加上1嘛!
有了这个思路,公式就好写了吧? J2单元格公式为“=SUMPRODUCT(($B$2:$B$16=B2)*($C$2:$C$16=C2)*($I$2:$I$16>I2))+1”,写完后,向下复制公式就行了。结果如图5所示。
图5
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 分栏可以使文档产生杂志排版的效果,使用 Word 的“连续”型分隔符,可以实现在 Word 文档页面的任意位置开始分栏。那么在word如何用
- 今日凌晨,苹果发布了 iOS 14.2、 iPadOS 14.2 、tvOS 14.2 、 watchOS 7.1 开发者测试版
- 下面小编就教你怎么在excel中使用ln函数:在B2单元格中,使用LN函数,参数引用A2单元格,按回车键输入公式,即可返回自然对数。 &nb
- ExcelVBA中for循环语句是怎样用的?下面我们来看一下。01、我们先创建一个空的函数。02、在函数体里,就可以创建一个for循环了,使
- 在WPS文字里面删除表格内的内容的操作方法:方法一:1、选中需要删除内容的单元格;2、单击WPS文字----编号----清除----内容即可
- Win10自带了许多小功能,这些小功能都是为了使用户的操作体验感更好而配备的,就像Win10的语音识别系统十分的方便,但是凡事有利必有弊,带
- 本文图文并茂给大家介绍了excel2007数据透视表的操作方法,本文给大家介绍的非常详细,具有参考借鉴价值,需要的朋友参考下吧excel20
- 折线图是我们经常要用到的一种分析类图表。比如用X轴坐标来表示一个月的天数,用Y轴坐标来表示某一数值,统计数据后描点绘图,得到折线图,可以很直
- 使用wps文字编辑了文档,可保存时并不像保存为word格式,想要保存为pdf或者其他格式怎么操作呢?其实非常简单,一起看看下面的教程吧WPS
- 本文介绍Microsoft Excel中LOGINV函数的语法和用法。函数说明LOGINV函数的主要作用是返回 x 的对数累积分布函数的反函
- 在使用Word2010编辑文档的过程中,常常需要知道当前文档一共包含多少段落和多少句。接下来由小编为大家推荐怎样快速知道段落数及句数等,希望
- 今天小雨再给大家分享一下excel中的神操作,掌握后可快速提高工作效率!这个操作就是巧妙的利用【F4】功能键来一键完成一些重复的操作。这样的
- 我们怎样在EXCEL中输入公式呢,看看下面的方法吧:打开一个Excel工作簿,在工作表单元格中选择需要输入公式的单元格,如下图所示。 &nb
- 本文介绍excel中char函数的使用方法,并给出具体的实例来帮助理解char函数。第一,char函数用法解释Excel中char函数以字符
- Win10家庭版1909更新20H2版本一直失败怎么办?现在有不少的朋友使用的还是Win10 1909版本的系统,但是该版本系统目前已经停止
- Excel图表怎么制作双色背景图?之前我们介绍过怎么制作图表,但是还没有介绍过图表的背景是双色的,今天我们就来看看这个双 * 表的制作方法,需
- 通过手机看电影或玩游戏的时候,如果想要更好的体验,我们可以把手机投屏到电视或电脑上,屏幕更大感觉更好。最近,有很多小伙伴反馈,苹果设备升级到
- 一、如果使用搜狗输入法,直接输入“xuanzhhong ”即可看到☑;想要单独的“√”,输入“gou ”二、在word文档中,单击“插入”—
- excelexcel中,有时候为了方便对比数据,我们会在单元格中插入一个迷你数据图表方便查看和对其数据的变化,那么我们该如何创建图表呢?下面
- Excel中经常需要用到饼图,饼图具体该怎么建立呢?接下来是小编为大家带来的excel2003建立饼图的教程,供大家参考。excel2003