大学计算机基础-基于计算思维课件 第5章 Excel基本应用5.3 课堂考勤成绩计算

大学计算机基础——基于计算思维（Windows10+Office2016）5.3.1知识点解析第5章Excel基本应用5.3课堂考勤成绩计算excel单元格数据可以快速的复制和粘贴。可以选择需要复制的单元格和单元格区域，按下ctrl+C复制数据，选择需要复制到的第1个单元格位置，按下Ctrl+V就实现了单元格内容的粘贴。也可以通过“开始”选项区/“剪贴板”组的“复制”和“粘贴”按钮来实现。1.单元格内容复制

在粘贴到目标位置后，将会出现粘贴选项按钮

，可以单击打开，进行各种选择性的粘贴，如

表示将内容及格式都复制过来，而

表示只将单元格内容复制过来，而不复制单元格格式等。Excel2.文本数据的输入

如图所示，在单元格A1中，输入你自己的身份证号码（如。你会发现，输入的身份证号码显示为4.32302E+17；单元格编辑栏的内容变成2.文本数据的输入

这是因为Excel工作表的单元格，默认的数据类型是“常规”，在单元格中输入时，系统自动判断其为数字型数据，因此以科学计算法来表示该数据。要解决以上问题，可以通过如下方式之一来实现：（1）在数字型文本数据前添加英文单引号“’”。（2）将该单元格数字格式设置为“文本”。3.数据有效性设置

例如，如果希望在C1单元格中输入身份证号码时，必须输入18位字符，可以将其有效性设置为“允许的文本长度为18”(选中需要设置的单元格，单击数据菜单项，在出现的对话窗中选择数据验证)。如图所示。在某些时候，需要将数据限制为一个特定的类型，如整数、分数或文本，并且限制其取值范围。4.公式与相对引用

如图所示，由于李春的总工资等于B2单元格的值加上C2单元格的值，因此可以在D2单元格中输入“=B2+C2”。“=B2+C2”就是Excel中的公式，表明D2单元格引用了B2单元格的数据和C2单元格的数据，将他们的和作为自己单元格的内容。要计算许伟嘉的总工资，可以将D2单元格的内容复制到D3单元格。可以看到，D3单元格的公式为“=B3+C3”。4.公式与相对引用

相对引用是指当把公式复制到其它单元格时，公式中引用的单元格或单元格区域中代表行的数字和代表列的字母会根据实际的偏移量相应改变。D2中的公式“=B2+C2”表明“D2的值是等于当前单元格的前2列单元格加上前1列列单元格的和”，因此，将D2单元格中的公式复制到D3后，系统认为“D3的值也应该等于当前单元格的前2列单元格加上前1列单元格的值”，因此公式自动变化为“=B3+C3”。在进行公式复制时，公式中引用的地址发生了变化，这是因为引用B3和C3单元格时，使用的是“相对引用”，在此公式中，B3和C3称之为相对地址。

功能是返回参数列表中非空值的单元格个数。5.函数与统计函数COUNTA

Excel函数是系统预先定义的，使用一些称为参数的特定数值来执行计算、分析等任务的特殊公式，并返回一个或多个值。可以用函数对某个区域内的数值进行一系列运算。例如，SUM函数对单元格或单元格区域进行加法运算。COUNTA函数的语法格式为：COUNTA(value1,value2,...)Value1,value2,...为所要计算的值，参数个数为1到255个，代表要计数的值、单元格或单元格区域。COUNTA函数如图所示，需要统计A1:A4单元格中非空单元格的个数，可以在B3单元格输入函数“=COUNTA(A1:A4)”，则B3单元格中的内容为3，即表示A1:A4中有3个非空的单元格。5.自动换行

6.IF函数

假设公司本月的绩效工资与出勤有关：全勤（出勤天数>=22）员工的绩效工资为2520元，非全勤员工的绩效工资为2000元。为了计算“李春”的绩效工资，需要根据其出勤情况（B2）来进行判断，如果B2中为“全勤”，其绩效工资（C3）为2520，否则为2000。可以用图来表示这种逻辑关系。（1）Logical_test：逻辑表达式。（2）Value_if_true：如果逻辑表达式的值为真，则返回该值。（3）Value_if_false：如果逻辑表达式的值为假，则返回该值。IF(Logical_test,Value_if_true,Value_if_false)6.IF函数

可以使用IF函数来实现。IF函数的语法格式为：参数的含义分别是：（1）如果Logical_test的结果为TRUE（真），就返回Value_if_true的值作为结果。（2）如果Logical_test的结果为FALSE（假），则返回Value_if_false的值作为结果。

Logical_test是计算结果可能为TRUE或FALSE的任意值或表达式。Logical_test可使用的比较运算符有：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）以及<>（不等号）。因此，“李春”的绩效工资对应的函数是“=IF(B2="全勤",2520,2000)”。如图所示。6.IF函数

其执行过程是：7.条件格式

如果你是一名教师，在分析考卷时，经常要进行的工作有：排名前20%的学生是谁，排名后20%的学生是谁？低于平均值的学生是谁？如果你是一名销售经理，你关心的可能会是：在过去五年的利润汇总中，有哪些异常情况？过去两年的营销调查反映出哪些倾向？这个月谁的销售额超过￥50,000？哪种产品卖得最好？7.条件格式

因此，在某些时候，需要将一些特别的数据突出显示出来。Excel的条件格式有助于快速完成上述要求，它可以预置一种单元格格式，并在指定的某种条件被满足时自动应用于目标单元格。可预置的单元格包括：边框、底纹、字体颜色等。例如，将单元格区域A1:D1所有大于100的单元格用红色字体标注出来，可以通过设置条件格式来实现。如图所示。大学计算机基础——基于计算思维（Windows10+Office2016）5.3.2任务实现第5章Excel基本应用5.3课堂考勤成绩计算1.任务分析

完成课堂考勤成绩计算，要求：（1）将所有学生名单复制到“课堂考勤成绩”工作表中，并不得改变工作表的格式。（2）编排学生学号，起始学生的学号为“01301001”，学号自动加1递增。（3）出勤区域的数据输入只允许为迟到、请假或旷课，并将考勤数据复制到工作表中。（4）计算学生的缺勤次数。（5）计算学生的出勤成绩。计算出勤成绩=100-缺勤次数×10。（6）计算学生的实际出勤成绩。如果学生缺勤达到5次以上（不含5次），实际出勤成绩计为0分，否则实际出勤成绩等于计算出勤成绩。（7）将80分（不含80）以下的实际出勤成绩用“浅红填充色深红色文本”特别标注出来。（8）隐藏工作表的网格线，设置出勤表打印在一页A4纸上，并水平和垂直居中。将“相关素材.XLSX”中的学生名单复制到“课堂考勤成绩”工作表中。操作步骤如下：2.实现过程

1）复制学生名单①打开“相关素材.XLSX”工作簿，选择“学生名单”工作表的A2:A37单元格区域，按下Ctrl+C，对选择的单元格区域进行复制。②切换到“大学计算机成绩.XLSX”工作簿的“课堂考勤成绩”工作表，定位到单元格B5，按下Ctrl+V，将姓名粘贴到“课堂考勤成绩”工作表中。③单击粘贴区域的粘贴选项按钮

，选择粘贴值按钮

，则在未改变工作表的现有格式的前提下，实现了学生名单的复制。

①由于学号数据为文本类型，因此，在A5单元格中首先需要输入英文单引号“’”，然后输入学号01301001。②选择A5单元格，移动鼠标，当鼠标变为黑十字时，按住鼠标左键向下拖动填充柄，拖动过程中填充柄的右下方出现填充的数据，拖至目标单元格（A40）时释放鼠标。③单击如图所示的“自动填充选项”，扩展开选项菜单，选择“不带格式填充”，这样，就即实现了学号的自动填充，也没有破坏其原来表格样式。2.实现过程2）编排学号起始学生学号“01301001”，学号自动加1递增进行编排。操作步骤如下：

选择C5:L40单元格区域。单击“数据”选项卡/“数据验证”组的数据验证按钮，弹出如图所示的对话框。2.实现过程3）设置出勤数据验证出勤区域的数据输入只允许为迟到、请假或旷课，可以通过设置数据有效性来实现。操作步骤如下：

③在“设置”选项卡中，允许选择序列，来源中输入“迟到,请假,旷课”，注意分隔符为英文逗号。④单击确定按钮。这样，对C5:L40单元格区域，就只允许选择性输入迟到、请假或旷课了。⑤将“相关素材.XLSX”中的考勤数据复制到“课堂考勤成绩”工作表中，并不改变工作表的格式。2.实现过程3）设置出勤数据验证由于学生如果有缺勤的情况，就会在对应的单元格标记上“迟到”、“请假”或“旷课”，因此，可以通过COUNTA函数来统计学生所对应的缺勤区域中非空值的单元格个数，从而得到学生的缺勤次数。操作步骤如下：

2.实现过程4）计算缺勤次数①选择M5单元格,单击M5单元格编辑栏区域的“插入函数”按钮，弹出如图所示的“插入函数”对话框，选择“统计”类别及COUNTA函数，单击确定按钮。

2.实现过程4）计算缺勤次数②在图所示“函数参数”对话框中，将光标定位到Value1区域，用鼠标左键选择C5:L5单元格区域，Value1区域中显示“C5:L5”，单击确定按钮。这时，M5单元格的值为3，M5编辑区的内容为“=COUNTA(C5:L5)”。③选择M5单元格，鼠标指针指向M5单元格的填充柄，双击鼠标左键，完成自动复制填充。④单击“自动填充选项”，扩展开选项菜单，选择“不带格式填充”。由于学生的计算出勤成绩=100-缺勤次数×10，因此可以用公式来实现。操作步骤如下：

2.实现过程5）计算出勤成绩①选择N5单元格，输入“=100-M5*10”，输入回车确认。这时，N5单元格的值为70，N5单元格编辑栏的内容为“=100-M5*10”。②选择N5单元格，鼠标指针指向N5单元格的填充柄，双击鼠标左键，完成自动复制填充。③单击“自动填充选项”，扩展开选项菜单，选择“不带格式填充”，完成公式复制。由于学生的实际出勤成绩与缺勤次数有关，如果学生缺勤达到5次以上（不含5次），实际出勤成绩计为0分，否则实际出勤成绩等于计算出勤成绩。因此可以用IF函数进行判断实现。操作步骤如下：

2.实现过程6）计算实际出勤成绩①选择O5单元格,单击O5单元格编辑栏区域的“插入函数”按钮，弹出如图所示的“插入函数”对话框，在“搜索函数”区域输入“IF”，单击转到按钮，选择“IF”函数，单击确定按钮。②在如图所示的“函数参数”对话框的Logical_test输入处，用鼠标左键单击N5，这时，Logical_test显示N5，从键盘输入>=50，在value_if_true中输入N5，在value_if_false输入处，输入0，单击确定按钮。这时，O5单元格的值为70，O5单元格编辑栏的内容为“=IF(N5>=50,N5,0)”。

2.实现过程6）计算实际出勤成绩③选择O5单元格，鼠标指针指向O5单元格的填充柄，双击鼠标左键，完成自动复制填充。④单击“自动填充选项”，扩展开选项菜单，选择“不带格式填充”。

2.实现过程7）实际出勤成绩特别标注由于需要将80分（不含80）以下的实际出勤成绩用“浅红填充色深红色文本”特别标注出来，可以通过条件格式来实现。操作步骤如下：①选择实际出勤成绩单元格区域O5:O40，单击“开始”选项卡/“样式”组/“条件格式”按钮/“突出显示单元格规则”按钮/“小于”按钮。②在弹出的如图所示的“小于”对话框中，第1个输入框输入80，设置为“浅红填充色深红色文本”，单击确定按钮。可以看到，实际出勤成绩小于80的单元格用“浅红填充色深红色文本”特别标注出来。

2.实现过程8）打印设置要设置出勤表打印在一页A4纸上，并水平和垂直居中。可以通过“页面布局”选项卡的“页面设置”组来实现。操作步骤如下：①单击“页面布局”选项卡/“页面设置”组/“纸张大小”按钮，选择“A4”。②单击“页面布局”选项卡/“页面设置”组/“页边距”按钮，选择“自定义边距”，在弹出的“页面设置”对话框的“页边距”选项卡中，设置上、下页边距为3cm，左、右页边距为2cm，勾选“水平”和“垂直”居中方式。如图所示。

2.实现过程8）打印设置③单击上图中的“打印预览”按钮，可以看到工作表在两页上显示，如图所示。单击“无缩放”按钮，在弹出的选项中选择“将工作表调整为一页”，就可以看到工作表在一页上显示了。为了隐藏工作表的网格线，可以通过设置Excel的“选项”来实现。操作步骤如下：

2.实现过程9）隐藏网格线①选择“文件”选项卡的“选项”按钮，在弹出的如图所示的“Excel选项”对话框中，单击“高级”选项。②滑动滚动条到“此工作表的显示选项”，确定当前选择的工作表为“课堂考勤成绩”，清除“显示网格线”复选框可以看到，当前工作表中不再显示网格线。大学计算机基础——基于计算思维（Windows10+Office2016）5.3.3总结与提高第5章Excel基本应用5.3课堂考勤成绩计算1.数据类型

系统默认的单元格数据类型为“常规”，在输入内容后，系统则会根据单元格中的内容，自动判断数据类型。例如，如果A1单元格的类型为“常规”，则在其输入“43230219950110001”，系统会自动判断其为数字型，因此将其以“4.323E+16”的科学计数数据显示。

可以在输入数据时通过前导符等形式，限制输入的数据类型。常用的通过录入限制数据类型的方法如表所示。表5-2录入不同数据类型说明数据类型录入说明示例文本（1）当输入的文本超出单元格的宽度时，需要调整单元格的宽度才能完整显示。（2）输入数字型文本时，可以在数字前输入单引号“'”。输入身份证号码：'43230219950110001数值（1）输入“3/4”时，系统自动将其转化为日期3月4日。（2）要输入分数，需要在其前面输入0和空格。输入分数：03/4日期年月日之间用“/”或“-”号分隔。输入日期：2014/3/21时间时分秒间用“：”分隔。输入时间：12：32：051.数据类型

在Excel2016中，复制内容之后，在目标单元格上单击鼠标右键，在选择性粘贴右侧有个箭头，单击箭头会出现选择性粘贴的所有粘贴方式，当鼠标停在某个粘贴选项上时，在单元格中会出现最终粘贴样式的预览。粘贴分为：2.选择性粘贴（1）粘贴：将源区域中的所有内容、格式、条件格式、数据有效性、批注等全部粘贴到目标区域。（2）公式：仅粘贴源区域中的文本、数值、日期及公式等内容。（3）公式和数字格式：除粘贴源区域内容外，还包含源区域的数值格式。数字格式包括货币样式、百分比样式、小数点位数等。

2.选择性粘贴（4）保留源格式：复制源区域的所有内容和格式，它与“粘贴”的不同点是：当源区域中包含用公式设置的条件格式时，在同一工作簿中的不同工作表之间用这种方法粘贴后，目标区域条件格式中的公式会引用源工作表中对应的单元格区域。（5）无边框：粘贴全部内容，仅去掉源区域中的边框。（6）保留源列宽：与保留源格式选项类似，但同时还复制源区域中的列宽。这与“选择性粘贴”对话框中的“列宽”选项不同，“选择性粘贴”对话框中的“列宽”选项仅复制列宽而不粘贴内容。（7）转置：粘贴时互换行和列。（8）合并条件格式：当源区域中包含条件格式时，粘贴时将源区域与目标区域中的条件格式合并。如果源区域不包含条件格式，该选项不可见。

2.选择性粘贴（9）值：将文本、数值、日期及公式结果粘贴到目标区域，不复制格式。（10）值和数字格式：将公式结果粘贴到目标区域，同时还包含数字格式。（11）值和源格式：与保留源格式选项类似，粘贴时将公式结果粘贴到目标区域，同时复制源区域中的格式。（12）格式：仅复制源区域中的格式，而不包括内容。（13）粘贴链接：在目标区域中创建引用源区域的公式。（14）图片：将源区域作为图片进行粘贴。（15）链接的图片：将源区域粘贴为图片，但图片会根据源区域数据的变化而变化。类似于Excel中的“照相机”功能。

3.自动填充使用Excel提供的自动填充功能，可以极大地减少数据输入的工作量。通过拖动填充柄，就可以激活自动填充功能。利用自动填充功能可以进行文本、数字、日期、公式等序列的填充和数据的复制。（1）自动填充或复制，除了之前所示的拖动鼠标方式外，也可以在鼠标变为黑十字时，双击鼠标左键来实现自动填充。（2）也可以在鼠标变为黑十字时，单击鼠标右键，拖动鼠标到需要填充的最后位置，松开鼠标，在弹出的快捷菜单中选择需要的填充模式来实现自动填充。（3）除了可以根据选择的单元格或单元格区域自动填充外，还可以通过“开始”选项卡/“编辑”组/“填充”按钮/“系列”来实现等差、等比等序列的自动填充。

通过“数据”选项卡/“数据验证”组/“数据验证”按钮设置了数据的有效范围后，如果在单元格中输入了无效数据，Excel会弹出一个如图所示的“警告”对话框，警告用户输入的数据是非法的。单击对话框中的“重试”按钮，可以重新输入有效的数据。4.数据验证默认情况下，Excel对单元格的输入是不加任何限制的。但为了保证输入数据的正确性，可以为单元格或单元格区域指定输入数据的有效范围。

数据有效性可以通过设置“有效性条件”将数据限制为一个特定的类型并限制其取值范围，还可以设置输入提示等。如图所示，可以将A1单元格的输入范围为限定为0～100，当输入错误时，给出“成绩范围为0～100！”的提示，同时取消输入或要求重新输入。4.数据验证

4.数据验证操作步骤如下：①选择A1单元格，在数据有效性设置中，将“有效性条件”的允许设置为“整数”，“数据”选择“介于”，最小值设置为0，最大值设置为100。②“出错警告”选项卡中，在标题栏输入“你输入的不合法”，在错误信息栏输入“成绩范围为0～100分！”，单击确定按钮。这样，在输入错误时，将出现自定义的出错警告。

4.数据验证此外，还可以将单元格区域的内容作为有效性输入的数据限制。如图所示。

5.条件格式“条件格式”中最常用的是“突出显示单元格规格”与“项目选取规则”。“突出显示单元格规格”用于突出一些固定格式的单元格，而“项目选取规则”则用于统计数据，如突出显示高于/低于平均值的数据，或按百分比来找出数据。对同一个单元格（或单元格区域），如果应用有两个或两个以上的不同条件格式，这些条件可能冲突，也可能不冲突：（1）规则不冲突：例如，如果一个规则将单元格格式设置为字体加粗，而另一个规则将同一个单元格的格式设置为红色，则该单元格格式设置为字体加粗且为红色。因为这两种格式间没有冲突，所以两个规则都得到应用。（2）规则冲突：例如，一个规则将单元格字体颜色设置为红色，而另一个规则将单元格字体颜色设置为绿色。因为这两个规则冲突，所以只应用一个规则，应用优先级较高的规则。

5.条件格式因此，在设置多条件的条件格式时，要充分考虑各条件之间的设置顺序。若要调整条件格式的先后顺序或编辑条件格式，可以通过“条件格式”的“管理规则”来实现如果想删除单元格或工作表的所有条件格式，可以通过“条件格式”的“清除规则”来实现

5.条件格式例如，希望将计算考勤成绩在80分以下（不含80分）的学生姓名用红色标识出来，操作步骤如下：此外，也可以通过公式来设定单元格条件格式。①选择“课堂考勤成绩”工作表的姓名单元格区域B5:B40，单击“开始”选项卡/“样式”组/“条件格式”按钮/“新建规则”。

5.条件格式其中，公式“=N5<80”指明的条件格式为：如果N5中的的内容小于80，那么B5单元格的内容以“红色、加粗”突出显示。此外，也可以通过公式来设定单元格条件格式。②在弹出的如图所示的“新建格式规则”对话框中，选择规则类型为“使用公式确定要设置格式的单元格”，在编辑规则中输入公式“=N5<80”（表示条件为“计算考勤成绩<80”），选择“格式”，设置为“红色，加粗”，单击确定按钮。可以看到，计算考勤成绩在80分以下的姓名就以“红色、加粗”突出显示了。

6.插入函数除了可以通过单元格编辑栏区域的“插入函数”按钮

外，还可以通过“公式”选项卡/“函数库”组来选择插入逻辑类、文本类以及数字和三角函数等不同函数。此外，如果公式的计算结果与预期有误，可以通过“公式”选项卡/“公式审核”组的相关功能来进行错误检查。

7.逻辑表达式在函数中，经常要判断某个数所处的区域，例如：（1）如果B3单元格中的数据大于等于100，C3单元格中的结果为“GOOD”，否则为“BAD”。其对应的逻辑表达式为“B3>100”，公式为“=IF(B3>100,"GOOD","BAD")”。（2）如果B4单元格中的数据大于100并且小于等于200，C4单元格返回结果为1，否则为0。其对应的逻辑表达式为“AND(B4>100,B4<=200)”，公式为“=IF(AND(B4>100,B4