站长家园(原代码之家)(www.adminjie.com)网站源码,微信源码,游戏源码,商业源码分享平台。
当前位置:网站首页 技术文章 网络编程 正文

实用Excel技巧分享:学习怎么做多因素排名统计表?

时间:2022-06-14 [网络编程]作者:fabuyuan 浏览:3 次

在之前的文章《实用Excel技巧分享:两个为表格添加背景图的方法》中,我们学习了表格背景图的方法。今天我们来聊聊统计表,看世界杯学做多因素排名统计表,快来看看!

手机如何做表格:点击查看

四年一度的世界杯激战正酣,每一名球迷都为自己喜欢的球队加油助威,更加关心这支球队所在小组的战况。随着第一轮比赛的结束,小组赛达到一个高潮,同组队伍的每一个进球都可以影响全局的结果。虽说现在的媒体信息很发达了,时事新闻,发展预测随处可见,不过如果能够有一个自己输入数据后就能计算、预测出小组排名结果的电子表格,无疑是非常方便的,今天就和大家分享一个这样的比赛成绩统计表。

表格的最终效果如下图所示:

功能:只要在黄色区域中输入比赛结果,就会自动计算出各项指标以及最终排名。

价值:

①战况数据实时统计

②预测排名

当然,更加重要的意义在于:通过这个表格的设计思路学到很多细节的知识点,如果自己工作中遇到计算综合排名的问题都可以借鉴。下面就来看看具体的制作过程。

因为各小组的结构完全一致,我们仅以A组数据为例进行说明。整个表格的制作分为三个个阶段:确定整体思路、排版设计和公式设计。

一、确定整体思路

在进行表格设计之前,要对相关的规则和需求进行整理,明确可以有哪些基础数据,最终需要得到什么结果,然后再去设计中间的环节。

在这个成绩统计表中,我们的基础数据就是每场结束后的两个数字(双方的进球数);最终需要的结果就是小组排名。

如何根据每场的结果得到排名,还需要对排名规则进行了解:

上图中是网的解释,算是一个比较复杂的计算规则了,对于这个规则我们可以归纳为以下几点:

①根据每场比赛需要判断各队的胜负情况,胜记3分,平记1分,负记0分;

②积分相同时按净胜球多少排名(净胜球就是进球数-失球数);

③净胜球还相同时按进球数多少排名。

④对于进球数还相同的情况,要按比赛的犯规情况再去判断。

因为我们的基础数据仅仅是双方每场的进球数,所以第1~3点可以实现,而第4点无法实现。

接下来就需要根据这些信息去设计表格。

二、排版设计

1、数字单独存放

排版设计要掌握的第一个原则是一个单元格只存放一种类型的数据。例如5:2这个比分,如果放在一个单元格,在后期进行统计分析时就需要把两个数字分别提取出来再进行计算,非常不方便。因此,在设计表格的时候需要重点考虑这个问题。下面来看看是如何处理的:

为了便于大家理解,我们将单元格边框显示出来,可以清楚地看到,实际上每个得分都是用了三个单元格,将两队的成绩与冒号分开存放,中间斜线的四个单元格,以及第四行中球队名称都是使用了合并单元格进行处理。

通过合理地使用合并单元格,既满足了将数字单独存放的要求,又符合视觉需求,很直观地就能看到比赛结果。

2、合理利用辅助列

排版设计的第二个原则就是要简化输入步骤以及简化计算过程。这点可以借助辅助列和公式来实现,例如在这个表格中,需要填写斜线下方黄色区域的比分,斜线上方的比分是用公式进行引用的:

为了统计每队的胜场、平场和负场数,用到了一些辅助列:

关于辅助列,有些朋友可能觉得过于麻烦,总是希望直接用公式得到结果,其实这是一个误解。在一些逻辑关系比较复杂或者计算步骤较多的模板表格中,善于利用辅助列可以简化公式的难度,降低运算量,同时便于使用过程中对表格的维护,因为计算过程都是通过辅助列实现的,如果需要调整某一处的计算方法,只需要改对应的地方即可。

以上是对这个成绩统计表设计的说明,看起来很多,其实都不难,接下来就看看这个表格里都用了哪些公式和函数。

三、公式设计

1、胜场的判断:=IF(F5>H5,1,0)

利用IF函数直接比较F5和H5,当F5大于H5为胜,得到1,反之得到0。

胜场次数计算:=SUM(O5:Q5)

直接对前面的数据求和。

2、平场的判断:=IF(AND(F5=H5,F5<>" "),1,0)

与胜场有点区别,平场判断时还需要加一个条件,也就是进球数不为空的时候才去比较,因此加了AND函数去做判断。同时满足进球数相等并且不为空这两个条件时才算打平,记为1,反之为0。

平场次数计算:

同样是直接求和。

3、负场的判断:=IF(F5

与胜场的判断方法一样,只是公式中把大于符号改成小于符号。

负场次数计算:

直接求和即可。

以上这部分内容是将每场比赛结果变成具体的数字,看起来有点麻烦,但是没什么难度。接下来将辅助列隐藏,仅显示胜场、平场和负场的统计次数即可。

4、进球数、失球数和净胜球数的统计:

进球数:=SUM(F5,I5,L5)

失球数:=SUM(H5,K5,N5)

净胜球数:=AA5-AB5

这部分公式非常简单,都是加减运算。

5、积分的计算

按照规则,胜场记3分,平场记1分,负场记0分,积分的公式就是:=R5*3+V5*1

至此,计算排名的各项指标我们都算出来了,分别是:积分、净胜球和进球数。

在根据这三项指标计算排名之前,还需要做一个过渡,将三项指标根据各自的优先级(权重)进行量化,变成可以比较的数字,可以在AF列使用这样的一个公式来实现:=AD5+AC5%+AA5%%,结果如下。

是不是感觉这个公式挺奇怪的,百分号是什么意思呢?

我们重点来解释一下这个公式,这里用到三个单元格:AD5(积分)、AC5(净胜球)和AA5(进球数)

在排名规则中明确说到,先根据积分,积分相同时判断净胜球数,再相同才判断进球数。那么如何将这种按顺序比较多数据转化为只比较一个数据呢?可以参考利用权重计算多因素排名的方法。例如指标A权重是50%,指标B权重30%,指标C权重20%,那么综合得分就是A*0.5+B*0.3+C*0.2,如此虽然有A、B、C三个排名因素,但只需要比较综合得分即可获得排名。

这时这就需要人为的指定权重,将需要先后比较的数据转化为一个数据。

公式=AD5+AC5%+AA5%%原本的写法应该是:=AD5*100+AC5*10+AA5,在AG列输入公式,结果如下。

也就是将每个指标按照优先级分别扩大100倍、10倍、1倍后相加,使每个指标不会在同一个数位上,而是分别位于百位、十位和个位,如此既保证了各指标的优先等级,又将多个指标化成了一个数据。(注:当前倍数设置只适合积分、净胜球、进球数据都是小于10的情况。)

在足球比赛中,数字都比较小(一般都不满10),如果要计算的数据量级比较大的话,仅仅差10倍是不够的,经常会遇到相差100倍或者10000倍的情况。在这种这种情形下,公式里就会有很多个0,此时可以变个思路,将扩大改为缩小,%的作用就是将数据缩小100倍,%%的作用就是将数据缩小10000倍,这就是=AD5+AC5%+AA5%%的由来。

经过了这么多步骤,终于到了计算最终排名的时候,一起来看看排名公式吧。

6、排名的计算

公式非常简单,就是rank函数的标准用法:=RANK(AF5,$AF$5:$AF$8)

再来复习一下rank函数的含义:=rank(要计算名次的数据,参与排名的区域,升序或降序),其中第三参数省略时按照降序排名,得分最高者为第一名。

最后隐藏所有的辅助列,一个成绩统计表就完成了:

四、小结

大家可能会有个感觉,设计一个这样的模板真的好麻烦,用这么多的辅助列,而且有些地方的公式还不能下拉,要一个一个填写,有没有不用辅助列可以直接下拉的方法呢?

肯定是有的,不过使用起来未必方便,那会用到大量的数组公式和复杂的函数。当然就这个表格来说,肯定还有继续优化的可能。老菜鸟曾经花了一周时间改进一个计算销售排名的报表模板,过程中反复的测试,修改。但是用户的体验非常好,因为只需要填写基础数据,就可以看到最想要的结果,中间的计算过程都是辅助列(单独存放在一个sheet里的),都用的是比较简单的函数,即便是有需要修改的时候,用户也能自己解决。

对于报表模板来说,设计时间的长短不是衡量好坏的标准,而是使用的方便程度和维护的难易度。希望大家能够从今天这个例子领悟到一些设计模板的思路,可以让自己的工作变得更加高效,让自己的表格变得更加智能。

相关学习推荐:excel教程

以上就是实用Excel技巧分享:学习怎么做多因素排名统计表?的详细内容,更多请关注站长家园其它相关文章!

本文标签:  Excel

转载请注明来源:实用Excel技巧分享:学习怎么做多因素排名统计表?

本文永久链接地址:https://www.adminjie.com/post/13443.html

免责声明:
本站所发布的一切资源仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。

附:
二○○二年一月一日《计算机软件保护条例》第十七条规定:为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬!鉴于此,也希望大家按此说明研究软件!

版权声明:
一、本站致力于为软件爱好者提供国内外软件开发技术和软件共享,着力为用户提供优资资源。
二、本站提供的部分源码下载文件为网络共享资源,请于下载后的24小时内删除。如需体验更多乐趣,还请支持正版。
三、我站提供用户下载的所有内容均转自互联网。如有内容侵犯您的版权或其他利益的,若有侵犯你的权益请:提交版权证明文件到邮箱 2225329873#qq.com(#换为@) 站长会进行审查之后,情况属实的会在三个工作日内为您删除。

  • 站长家园(原代码之家)会员升级
  • 最新文章
    • vue创建项目时sass是什么意思

      vue创建项目时sass是什么意思

      vue创建项目时使用的sass是强化css辅助工具的意思,是对css的扩展;sass是由buby语言编写的一款css预处理语言,和html有一样严格的缩进风格,...

    • vue的插件是什么

      vue的插件是什么

      vue的插件是为应用添加全局功能的一种强大而又简单的方式;vue插件是使用vue开发的一些工具,在调用时只需要很少的代码就能实现需要的效果,便于后期维护和提升开...

    • PHP 还能再“快”吗?

      PHP 还能再“快”吗?

      PHP开发效率高,速度快,这个优势都是大家公认的,那么PHP还能变得更快吗?当然可以!人家有Node有Go,PHP也有Swoole,当Swoole接管了PHP...

    • 35+个chrome插件,让你工作学习事半功倍!

      35+个chrome插件,让你工作学习事半功倍!

      有句话,事半功倍,其必然是借助了某些思想和工具。下面给大家精选35+个chrome插件分享给大家,含15+前端插件,总有一款值得你停留!如下常见插件就不多说了:...

    • thinkphp是面向对象的吗?

      thinkphp是面向对象的吗?

      thinkphp是面向对象的;thinkphp是一个免费开源的、快速的、简单的、面向对象的轻量级PHP开发框架,是为了简化企业级应用开发和敏捷WEB应用开发而诞...

    热门文章