4.8 体育课成绩换算统计表
案例背景
体育课考试不同于文化课考试，体育课属于术科，其考试一般来说是在户外运动场上实施的，例如800米中长跑考试，体育教师是直接用秒表记录的学生跑的实际用时，形式如3'40”，或 4'16”，再如掷实心球考试时直接用米尺丈量出投掷的实际距离，体育教师需要将这些测试成绩转换成百分制成绩，然后按照各单项测试所占比例计算出本学期的体育课成绩。
假定某中学初二年级本学期体育课要进行800米中长跑和掷实心球测试。本案例按照中学生体育健康标准，通过使用Excel函数制作制作体育成绩换表，然后制作学生体育成绩统计表。
要实现本案例中的功能，学员应该掌握以下EXCEL技术点
●基础知识 基本的数学四则运算，数字的文本格式转化为数字格式的方法，数字的自定义格式。
●函数应用 文本函数LEFT，MID，RIGHT， 查找与引用函数FIND ，SEARCH函数 VLOOKUP函数
●综述 逻辑函数IF的嵌套
最终效果展示
4.8.1创建体育成绩换算表
Step 1创建工作簿、重命名工作表
创建工作簿“体育课成绩表.xls“，然后将工作表重命名为”掷实心球“，”800米“和“成绩表”。
Step 2创建”掷实心球“单项成绩换算表
①光标单击工作表标签“掷实心球”，在单元格A1输入“男生标准”，在单元格C1输入“女生标准”，选中单元格A1:B1，单击常用工具栏“合并及居中”按钮，然后单击常用工具栏“格式刷”，单击单元格C1完成格式的复制。
②在单元格A2输入“掷实心球（米）”，单元格B2输入“单项得分”，在单元格C2输入“掷实心球（米）”，单元格D2输入“单项得分”。
③选中单元格区域A3:D24，分别输入男生和女生“掷实心球”单项考试对应投掷米数换算百分制成绩标准。
④选中单元格区域A1:D24，按图5 样式为表格设置边框。
Step 3创建”800米“单项成绩换算表
⑴光标单击工作表标签“800米”，在单元格A1输入“男生标准”，在单元格F1输入“女生标准”，选中单元格A1:E1，单击常用工具栏“合并及居中”，然后单击常用工具栏“格式刷”，单击 F1完成格式的复制。
⑵分别在单元格A2，B2,C2,D2,E2中输入字段名“800米(分.秒)”，“提取分”，“提取并准换秒为分”，“800米(分.秒)转换”，“单项得分”，选中单元格区域A2:E2，按组合键复制，单击单元格F2，按组合键粘贴，完成换算表字段名的设置。
⑶在单元格区域A3:A23分别输入跑800米对应时间区间的分段点，在单元格区域E3:E23分别输入对应百分制成绩。
⑷选中单元格B3，单击单击常用工具栏“插入函数”按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”文本“，在”选择函数“选项框中选择“LEFT”。
⑸单击“确定”按钮，弹出“函数参数“对话框，在第一个参数框“TEXT”中光标单击单元格A3（或直接输入”A3”）,在第二个参数框“Num_chars“中输入”1“。
⑹单击“确定”按钮即可完成第一个分段点中“分“的提取。
⑺双击单元格B3右下角的填充柄，即可完成其余分段点中“分“的提取。
⑻选中单元格C3，单击单击常用工具栏“插入函数”按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”文本“，在”选择函数“选项框中选择“MID”。
⑼单击确定按钮弹出“函数参数“对话框，在第一个参数框“TEXT”中光标单击单元格A3（或直接输入”A3”）, 在第二个参数框”Start_num”中输入“3“，在第三个参数框“Num_chars“中输入”1“。
⑽单击确定按钮完成秒的提取。
⑾然后在编辑栏修改公式为“=MID(A3,3,1)/60”，按键确认即可完成第一个分段点中“秒“到“分”的转换。
⑿选中单元格C4，在编辑栏输入公式： “=MID(A4,3,2)/60”，按键确认即可完成第二个分段点中“秒”的提取和转换。
⒀双击单元格C4右下角的填充柄，即可完成其余分段点中“秒“的提取和转换
⒁选中单元格区域D3: D23，输入公式“=B3+C3“，按组合键即可完成选中区域的公式编辑。
⒂参照800米“男生标准“换算表制作方法，按照下降一个分段点标准制作800米“女生标准“换算表。
⒃参照图21样式为男生换算表和女生换算表设置边框和图案。
4.8.2登统学生体育考试成绩
Step 1输入学生体育考试成绩表字段信息
在单元格区域A1:H1中分别输入字段名“姓名“，“性别“，“掷实心球（米）单项得分1“，“800米成绩“，“800米换算“，“单项得分2 “，“学期总成绩“。
Step 2输入学生原始信息
在单元格区域A2:A21，B2:B21，C2:C21,E2:E21分别输入学生的姓名，性别，掷实心球原始成绩和800米中长跑的原始成绩。
Step 3统计“掷实心球“单项成绩
①单击工作表标签“成绩表“，选中D2单元格，输入公式：
” =IF(B2="男",VLOOKUP(C2,掷实心球!$A$3:$B$24,2,1),VLOOKUP(C2,掷实心球!$C$3:$D$24,2,1)) “
按键确认即可完成第一个学生“掷实心球“成绩的统计任务。
②双击单元格D2右下角的填充柄，即可完成其余学生“掷实心球“单项成绩统计任务。
Step 4实施“800米“单项成绩换算
①选中F2单元格，输入公式：“=IF(E2<>"",LEFT(E2,1)+MID(E2,3,2)/60,"") “
按键确认即可完成第一个学生800米成绩的文本记录转换成以“分“为单位的数值记录。
②双击单元格F2右下角的填充柄，即可将其余学生800米成绩的文本记录转换成以“分“为单位的数值记录。
Step 5统计“800米“单项成绩
①选中单元格G2，输入公式：“=IF(F2="","",IF(B2="男",VLOOKUP(F2,'800米'!$D$3:$E$23,2,1),VLOOKUP(F2,'800米'!$I$3:$J$23,2,1))) “
按键确认即可完成第一个学生“800米“成绩的统计任务。
②双击单元格G2右下角的填充柄，即可完成其余学生“800米“单项成绩统计任务。
Step 6统计学期总成绩
按“掷实心球“和“800米“单项成绩各章50%的比例计算学期总成绩。
①选中H2单元格，输入公式：“=ROUND(D2*50%+G2*50%,0) “
按键确认即可完成第一个学生学期总成绩统计工作。
②双击单元格H2右下角的填充柄，即可完成其余学生学期总成绩统计工作。
Step 7格式化成绩表
①选中单元格区域F2:F21，按快捷键弹出“单元格格式”对话框，切换到数字选项卡，在左侧的“分类”列表框中选择“自定义”，在右侧的“类型“框中输入”0.0#"′"“。
②单击“确定”按钮即可将所选区域显示为如图33所示的方式
③选中单元格区域A1:H21，按照如图34所示样式为表格设置边框和文字的显示方式。
至此，体育课成绩转换及登统工作全部完成。
关键知识点讲解
1. LEFT函数
函数名称：LEFT和LEFTB
主要功能：LEFT 基于所指定的字符数返回文本字符串中的第一个或前几个字符。
LEFTB 基于所指定的字节数返回文本字符串中的第一个或前几个字符。此函数用于双字节字符。
使用格式：LEFT(text,num_chars)
LEFTB(text,num_bytes)
参数说明：
Text 是包含要提取字符的文本字符串。
Num_chars 指定要由 LEFT 所提取的字符数。
●Num_chars 必须大于或等于 0。
●如果 num_chars 大于文本长度，则 LEFT 返回所有文本。
●如果省略 num_chars，则假定其为 1。
Num_bytes 按字节指定要由 LEFTB 所提取的字符数。
应用示例 (LEFT)
应用示例 (LEFTB)
2.MID函数
函数名称：MID 和MIDB
主要功能：MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。
MIDB 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。此函数用于双字节字符。
使用格式：MID(text,start_num,num_chars)
MIDB(text,start_num,num_bytes)
参数说明：
Text 是包含要提取字符的文本字符串。
Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。
Num_chars 指定希望 MID 从文本中返回字符的个数。
Num_bytes 指定希望 MIDB 从文本中返回字符的个数（按字节）。
函数说明
●如果 start_num 大于文本长度，则 MID 返回空文本 ("")。
●如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。
●如果 start_num 小于 1，则 MID 返回错误值 #VALUE!。
●如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。
●如果 num_bytes 是负数，则 MIDB 返回错误值 #VALUE!。
应用示例：
应用示例 (MIDB)
3. RIGHT函数
函数名称：RIGHT和RIGHTB
主要功能：RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。
RIGHTB 根据所指定的字符数返回文本字符串中最后一个或多个字符。此函数用于双字节字符。
使用格式：RIGHT(text,num_chars)
RIGHTB(text,num_bytes)
参数说明：
Text 是包含要提取字符的文本字符串。
Num_chars 指定希望 RIGHT 提取的字符数。
Num_bytes 指定希望 RIGHTB 根据字节所提取的字符数。
函数说明
●Num_chars 必须大于或等于 0。
●如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。
●如果忽略 num_chars，则假定其为 1。
应用示例 (RIGHT)
应用示例 (RIGHTB)
4.FIND函数
函数名称：FIND和FINDB
主要功能：
FIND 用于查找其他文本字符串 (within_text) 内的文本字符串 (find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号。也可使用 SEARCH 查找其他文本字符串中的某个文本字符串，但是，FIND 和 SEARCH 不同，FIND 区分大小写并且不允许使用通配符。
FINDB 用于查找其他文本字符串 (within_text) 内的文本字符串 (find_text)，并基于每个字符所使用的字节数从 within_text 的首字符开始返回 find_text 的起始位置编号。此函数用于双字节字符。也可使用 SEARCHB 查找其他文本字符串中的某个文本字符串。
使用格式：FIND(find_text,within_text,start_num)
FINDB(find_text,within_text,start_num)
参数说明：
Find_text 是要查找的文本。
Within_text 是包含要查找文本的文本。
Start_num 指定开始进行查找的字符。within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。
提示：
使用 start_num 可跳过指定数目的字符。例如，假定使用文本字符串“AYF0093.YoungMensApparel”，如果要查找文本字符串中说明部分的第一个“Y”的编号，则可将 start_num 设置为 8，这样就不会查找文本的序列号部分。FIND 将从第 8 个字符开始查找，而在下一个字符处即可找到 find_text，于是返回编号 9。FIND 总是从 within_text 的起始处返回字符编号，如果 start_num 大于 1，也会对跳过的字符进行计数。
函数说明
●如果 find_text 是空文本 ("")，则 FIND 会匹配搜索串中的首字符（即：编号为 start_num 或 1 的字符）。
●Find_text 中不能包含通配符。
●如果 within_text 中没有 find_text，则 FIND 和 FINDB 返回错误值 #VALUE!。
●如果 start_num 不大于 0，则 FIND 和 FINDB 返回错误值 #VALUE!。
●如果 start_num 大于 within_text 的长度，则 FIND 和 FINDB 返回错误值 #VALUE!。
应用示例
示例1（Find）
示例2（Find）
示例 (FINDB)
在以下示例中，FIND 返回 2，因为空文本 ("") 位于字符串中的第二个位置，而 FINDB 返回 3，因为每个字符均按字节进行计数，而第一个字符包含 2 个字节，所以第二个字符从第三个字节开始。
5. SEARCH函数
函数名称：SEARCH和SEARCHB
主要功能：
SEARCH 返回从 start_num 开始首次找到特定字符或文本字符串的位置上特定字符的编号。使用 SEARCH 可确定字符或文本字符串在其他文本字符串中的位置，这样就可使用 MID 或 REPLACE 函数更改文本。
SEARCHB 也可在其他文本字符串 (within_text) 中查找文本字符串 (find_text)，并返回 find_text 的起始位置编号。此结果是基于每个字符所使用的字节数，并从 start_num 开始的。此函数用于双字节字符。此外，也可使用 FINDB 在其他文本字符串中查找文本字符串。
使用格式：SEARCH(find_text,within_text,start_num)
SEARCHB(find_text,within_text,start_num)
参数说明：
Find_text 是要查找的文本。可以在 find_text 中使用通配符，包括问号 (?) 和星号 (*)。问号可匹配任意的单个字符，星号可匹配任意一串字符。如果要查找真正的问号或星号，请在该字符前键入波形符 (~)。
Within_text 是要在其中查找 find_text 的文本。
Start_num 是 within_text 中开始查找的字符的编号。
提示：
使用 start_num 可跳过指定数目的字符。例如，假定使用文本字符串 AYF0093.YoungMensApparel，如果要查找文本字符串中说明部分的第一个 Y 的编号，则可将 start_num 设置为 8，这样就不会查找文本的序列号部分。SEARCH 将从第 8 个字符开始查找，而在下一个字符处即可找到 find_text，于是返回编号 9。SEARCH 总是从 within_text 的起始处返回字符编号，如果 start_num 大于 1，也会对跳过的字符进行计数。
函数说明：
●SEARCH 和 SEARCHB 在查找文本时不区分大小写。
●SEARCH 和 SEARCHB 类似于 FIND 和 FINDB，但 FIND 和 FINDB 区分大小写。
●如果没有找到 find_text，则返回错误值 #VALUE!。
●如果忽略 start_num，则假定其为 1。
●如果 start_num 不大于 0（零）或大于 within_text，则返回错误值 #VALUE!。
应用示例 (SEARCH)
应用示例 (SEARCHB)
在下列示例中，SEARCH 返回 2，因为空文本 ("") 位于字符串的第二个位置，而 SEARCHB 返回 3，因为每个字符是按字节进行计数的，第一个字符含有两个字节，因此第二个字符将从第三个字节开始。
案例公式解析
单元格D2中的公式为：” =IF(B2="男",VLOOKUP(C2,掷实心球!$A$3:$B$24,2,1),VLOOKUP(C2,掷实心球!$C$3:$D$24,2,1)) “
因为第一个学生的性别为“女”，所以公式化简为：“=VLOOKUP(3.8,掷实心球!$C$3:$D$24,2,1) ”由于“3.8”介于区间分段点3.6和3.9之间，按照VLOOKUP函数模糊查找“向下”对应的规定，“3.8”对应于“3.6’”，而“3.6”得对应百分制成绩是“30”分，所以公式返回“30”。
单元格G2中的公式为：=IF(F2="","",IF(B2="男",VLOOKUP(F2,'800米'!$D$3:$E$23,2,1),VLOOKUP(F2,'800米'!$I$3:$J$23,2,1))) 添加的第一个逻辑判断IF(F2="","", IF(公式)) 是为了保证在单元格F2还没有输入成绩的情况下公式返回“空值“，否则的话就会返回”0“，在单元格F2非空的情况下，公式化简为：”=IF(B2="男",VLOOKUP(F2,'800米'!$D$3:$E$23,2,1),VLOOKUP(F2,'800米'!$I$3:$J$23,2,1))) “
因为第一个学生的性别为“女”，所以公式继续化简为：
“=VLOOKUP(4.17′,'800米'!$I$3:$J$23,2,1) “由于“4.17′”介于区间分段点4.133’和4.267’之间，按照VLOOKUP函数模糊查找“向下”对应的规定，4.17′”对应于“4.133’”，而“4.133’”得对应百分制成绩是“75”分，所以公式返回“75”。
案例拓展
完善“800米“单项成绩换算公式
在上面的案例中工作表“800米“中，换算表缺少”0 “分档，并且换算公式还不能够适应每一档的换算需要，前面学习了FIND函数和SEARCH函数的使用方法，下面使用嵌套函数设计一个通用公式实现每个时间断点的换算。
Step 1增加时间区间的分段点
①单击工作表标签“800米“，在单元格A24中输入”59'30〃“
②在单元格B24中输入公式“=LEFT(A25,FIND("'",A25,1)-1)“
按键确认即可完成时间“分“的提取。
③在单元格C24中输入公式:
” =MID(A24,FIND("'",A24,1)+1,FIND("〃",A24,1)-FIND("'",A24,1)-1)/60 “
按键确认即可完成时间“秒”的提取及换算。
④在单元格D24中输入公式“=B24+C24”，按键确认，在单元格E24中输入“0”。
⑤选中单元格区域B24:D24，向上拖曳单元格D24右下角的填充柄至第二行即可完成公式的更新。
⑥选中单元格区域A24:E24，按组合键复制，单击单元格F24，按组合键粘贴。
Step2重新格式化”800米”换算表
仍按原来样式为表格设置边框。
|
