基于Excel应用多指标评分系统设计与实现.docVIP

基于Excel应用多指标评分系统设计与实现 　　摘 要：比赛评分、教材评比等多用单一指标衡量，评价结果并不尽合理，若从多个指标评价，将更准确。对于多指标评分，可以使用Excel的数据有效性、函数及保护功能实现，并设定各分数段数量及比率使评分工作更客观、公正。 　　关键词：多指标评分；Excel；数据的有效性；条件格式；保护 　　中图分类号：TP391.1 文献标识码：A 　　1 引言 　　在日常工作和生活中，我们常会遇到各种评价问题，如果仅从单一指标上对被评价事物进行综合评价不尽合理，因此往往需要将反映被评价事物的多项指标的信息加以汇集，得到一个综合指标，以此来从整体上反映被评价事物的整体情况[1]。多指标评分，要求每位评委对各个指标打分，通过设定各指标权重，得到评分总和及最小分值、最大分值等统计指标。在多指标综合评价中，指标的权重系数是指各指标对决策问题的相对重要程度[2]。评委打分时不可避免地带有主观色彩，为限制打分集中性地偏高或偏低，可用设定评分总和分数段比率的办法来限定打分，这样会使分值更客观、公正。该评分系统能够广泛地应用于比赛评分、教材评比等，可用C#、Visual Basic等程序设计语言来实现，但EXCEL也能实现该系统功能，并且相比较程序语言开发工具来说更简单易用。这里将以“教材评比”系统为例介绍实现办法。 　　2 系统概述 　　制订评价指标体系，应满足指标的完备性，即要求制订的教材质量评价指标体系不能遗漏关于教材的任何重要方面的情况，应全面地 系统地本质地反映再现和涵盖教材的各方面情况[3]。“教材评比”系统具体地描述为：有10位评委为某出版社教材打分，共8个指标，每个指标可选分值为0—10分，其中“职业性”和“实践性”这两个指标的权重系数是2，其余的权重系数为1，总分最高为100分。系统能自动以不同颜色显示每本教材的最大、最小得分，能统计“所有教材的最小分值”、“所有教材的最大分值”、“内容质量总成绩”及“组内最低分”，并要求90—100分的比率不能高于10%，70—90分的比率不能高于30%，评分完毕，系统能够统计每本教材的平均分值及位次。下面将详细地介绍该系统??实现过程。 　　3 实现过程 　　3.1 根据教材和评价指标建立表格 　　在Excel中新建一个工作簿，并按照教材（以10本教材为例）评价指标及统计条目做出一个评分工作表，其中行标题为教材名，列标题为8个指标，并在右侧添加“所有教材的最小分值”、“所有教材的最大分值”、“90—100分”数量统计等条目，具体如图1所示，将C3至J12区域的单元格设为数字格式，以输入各位评委对教材的打分。 　　3.2 冻结窗格 　　以C3为基准冻结窗格，当向下滑动滚动条，首行不动，确保评价每本教材都能随时看到评价指标；向右滑动滚动条，教材名称不变，确保能将每个指标移到离教材最近的地方，方便评价。 　　3.3 设定数据的有效性 　　一般在比赛规则中会明确评分的取值范围，以此为依据，在系统中通过设定输入框的取值来实现这一要求，否则系统报错。在本系统中，选中C3：J12单元格区域，点击“数据”菜单的“有效性”子菜单弹出“数据有效性”对话框，如图2所示，在“允许”项目中选择“序列”，在“来源”项目中输入取值范围，并以逗号隔开。 　　3.4 条件格式的设定 　　当输入全部评分时，系统将以不同颜色来区分最高分及最低分，能够让人一目了然。这一点的实现方法是选择数据区域C3：J3，点击“格式”菜单的“条件格式”子菜单，打开“条件格式”对话框，如图3所示。首先将单元格数值设置为“等于”“MAX（$C$3：$J$3）”，格式设为“红色”，这样将第一本教材的最高分设为了红色。同理，将单元格数值设置为“等于”“MIN（$C$3：$J$3）”，格式设为“绿色”，将第一本教材的最低分设为了绿色。最后，选择数据区域C3：J3，拖动填充柄至C12：J12即可。 　　3.5 最小、最大及和的计算 　　在本案例中，需要计算“每种教材的最小分值”、“每种教材的最大分值”、“总成绩”及“组内最低分”。总成绩的计算公式为：“K3=C3+D3+E3*2+F3*2+G3+H3+I3+J3”，选中K3单元格，拖动填充柄至K12单元格即可计算出所有教材的总成绩。其余三项的计算公式，如表1所示。 　　3.6 各个分数段的教材数量及比率的计算 　　在这里主要用到COUNTIF及COUNTA函数，COUNTIF函数能够计算某个区域内满足给定条件的单元格数目；COUNTA函数能够计算某个区域内所包含的数值个数，如表2所示。 　　3.7 锁定及保护功能结合使用 　　为避免评委修改及删除教材名、指标名和统计条目名，可将锁定及保护功能结合使用对工作表进行设置。新建一个工作簿后，工