| 出版日期：2003-12-29 总期号：550 本年期号：50 |
|
 |
Excel中IF函数
及单元格三维地址的应用 《中国电脑教育报》张士昆 每年我校都要招收体育特长生，因参加的人比较多（约400人），测试的项目比较多，所以数据处理的工作量较大。为此，我们用Excel设计了一套表格，解决了这一难题，使得统计轻松快捷。 ★表格设计要求 1. 总表：原始表，所有参加测试学生的原始成绩都在此表输入。 2. 分表：生成表，按专项分，表中需要显示如下内容： （1）考号、姓名、性别、毕业学校、联系电话。 （2）三项素质成绩与对应得分、素质得分（60分）。 （3）专项成绩得分（40分）。 （4）总分（100分）、排名。 ★表头制作 
按照设计要求，总表设计如图1。分表数据自动生成，表头设计如图2。 ★得分的计算 我们参照的是《高等学校本科生体育招生计算表》，可分为两种。一是按时间计分，成绩越佳，数值越小；二是按长度计分，成绩越佳，数值越大。 图3显示为男子100M计算表，从表中可以看出，成绩的步长是0.1秒，分值的步长：11~12秒是0.7分，12.1~13秒是0.5，13.1~14秒是0.4，14.1~15秒是0.3，成绩小于11秒的都是20分，成绩大于15秒的都是0分。利用Excel的函数功能可以把“得分”的公式设计成“IF（M4<=11,20,IF(M4<12.1,19.3-(M4-11.1)7,IF(M4<13.1,12.5-(M4-12.1)5,IF(M4<14.1,3.7-(M4-13.1)4,IF(M4<15.1,3.7-(M4-14.1)3,0))))）”。其中的M4是成绩输入的单元格。该公式可以解释如下：如果输入的成绩小于或等于11秒，其分值是20；否则进入第2层IF函数，如果输入的成绩小于12.1秒，其分值是19.3（11.1秒的分值）与（M4-11.1）7（输入成绩与11.1秒分值之差）之差；如果不是则进入第3层IF函数；依此类推，最后，如果都不是则分值为0。 
按长度计算的项目，只要把IF函数中的“<=”改为“>=”即可。 图4是男子立定跳远计分表，该表与图3所示表不同的是： 
1. 按长度计分；2. 成绩数值越小分值越小；3. 成绩的步长是0.02米；4. 分值步长每10段一组，分别为0.7、0.52、0.4、0.26。“得分”的公式设计成“IF（M4>=2.93,20,IF(M4>2.72,13+(M4-2.73)/270,IF(M4>2.52,7.8+(M4-2.53)/252,IF(M4>2.32,3.8+(M4-2.33)/240,IF(M4>2.13,1.33+(M4-2.14)/226,0))))）”。 ★分表、单元格的复制 1. 选中“分表”，按单项个数将其复制，并将其分别改名为“100M”、“200M”、“铅球”等。 2. 选中“100M”工作表。 3. 选中A5单元格。 4. 单击“总表”。 5. 单击总表中的A5单元格，回车。 6. 把鼠标指针指向A5单元格右下角，当黑点变成“+”时，按住鼠标左键向右拖至E5单元格释放。 7. 重复1~5将三项素质成绩复制到相应单元格。 8. 将单项成绩100M复制到相应的单元格。 9. 将各项得分的公式写入相应的单元格。 10. 在L5单元格（素质分）写入公式“=G5+I5+K5”。 11. 在O5单元格（总分）写入公式“=L5+N5”。 12. 在P5单元格（排名）写入公式“=RANK（P5，P$5：P$50，1）”（设本组有50人，按升序排列）。二类项目按降序，“1”替换成“0”。 13. 选中A5~P5单元格，向下复制到第55行。重复以上步骤复制其他的表。 至此，整个设计完毕。在使用时，只要将所有原始信息输入到“总表”中，各“分表”便自动生成。需要时打印即可。 注意：“分表”不能修改。 |
||||||||||||||||||
