《计算机应用基础》课件第4章 中文EXCEL 2003

目录子项目一制作校运会比赛成绩表子项目二统计和分析校运会比赛成绩表子项目三制作校运会奖章证书子项目一：制作校运会比赛成绩表任务一：建立校运会比赛成绩登记表任务二：格式化校运会比赛成绩文档任务三：设置成绩工作表的边框、底纹任务一：建立校运会比赛成绩登记表能力目标：能创建Excel2003工作表；能用序列填充Excel2003工作表；能插入及删除行和列；能对部分单元格设置有效性数据。知识目标：熟悉Excel2003软件的运行环境和界面组成；掌握Excel2003工作表和工作簿的创建、保存、打开与关闭的方法；掌握Excel2003插入及删除行、列的方法；熟悉Excel2003序列填充的方法；掌握Excel2003数据有效性的设置方法。教师展示学生训练由学生按照此模板样式，自主尝试创建“女子跳远比赛成绩登记表（学生组）.xls”；插入一列；以序列填充；设置第一跳、第二跳、第三跳三列数据的有效性范围为0~10，给出提示信息：请输入0~10之间的数据，如3，3.5等。教师演示Excel工作簿及工作表的新建、保存、打开以及常规数据的输入方法。Excel2003工作界面的介绍；教师演示插入和删除行、列的操作。教师具体介绍填充数据的几种方法：自动填充填充等差序列填充等比序列填充日期序列自定义序列教师讲解工作簿：即Excel2003中的一个文件，由一组工作表组成，类似一个账本，而每个工作表相当于这个账本中的一页。一个工作簿最多可以包含255个工作表，一个新工作簿默认有3个工作表，分别命名为sheet1、sheet2、sheet3。工作表的名字可以修改，其个数也可以增减。教师讲解单元格：工作区中的每一格称为单元格，它是Excel2003表格存储信息的基本单位，其中可以存储数字、文本、日期和货币等各种信息。活动单元格是用户正在进行操作的单元格，边框为黑粗框。单元格地址：由列标和行号值来命名，如B4表示的是第B列第4行的单元格地址。描述某个工作表中的单元格地址用“工作表名！单元格”表示，如“sheet3！B4”，表示“sheet3”工作表中的第B列第4行的单元格地址。单元格区域：由多个单元格组成的区域，表示方法为“起始单元格地址：结束单元格地址”，如“A1：B2”，表示包含A1、A2、B1、B2四个单元格的区域。描述某个工作表中的区域用“工作表名!区域名”，如“sheet2”表中第2行第3列至第5行第6列区域描述为“sheet2！C2：F5”。输入的几点技巧在输入分数时，如果直接输入“2/5”，Excel2003会将其视作日期“2月5日”。为避免将分数视作日期，我们输入分数时，应先输入“0”，再输入一个空格，再输入分数值。如“01/2”。在往已经有内容的单元格中添加内容时，如果不想把原来的内容覆盖掉，就一定要双击这个单元格再输入，或者在选中单元格后使用编辑栏来输入。输入的几点技巧如果输入的文本数据较长，需要在单元格内换行时，按下【Alt+Enter】组合键即可。如果输入较大的数值数据后，单元格内可能会显示为符号（#######），表明该单元格的宽度不够。只需选定此单元格后，再选择“格式→列→最适合的列宽”命令来调整即可。

用户可以按下【Ctrl+；】输入当前日期，按下【Ctrl+Shift+；】输入当前时间。自动填充初值为纯数字或纯文本，拖动填充柄相当于复制数据。但若在拖动时，按下Ctrl，则可对纯数字进行序列填充。初值为数字和文本组合，拖动填充柄时字符不变，数字递增。但若在拖动时，按下Ctrl，则可对组合进行复制。如图所示教师演示数据的有效性设置提示：若在“有效性条件”的“允许”下拉框中选择的是“序列”，则在来源中用“,”将各内容分隔开。练习创建如效果图所示的”职工工资统计表.xls“：将“部门”列删除，在表格最下面增加一行信息，信息内容依次为“李平男1980-11-2技术员130015020080”。在姓名前加上一列：序号。以当前顺序为每一位职工加上序号为：001，002，以此类推,用自动填充的方法。设置性别列的数据有效性为“男，女”，职称列的有效性设置为“高工，技术员，工程师，文员”。练习设置基本工资的数据有效性为1000到2000之间.并设置如果输入错误数据将出现警告,并有系统提示：输入错误，只可在1000与2000之间。设置奖金列有浮动提示为：九月奖金。修改制表日期为“2009-10-20”。保存“职工工资统计表.xls”。效果图如下：任务二：格式化校运会比赛成绩文档能力目标：能使用”格式→单元格“命令设置工作表中数据的数字类型、对齐方式以及字体等；能设置行高和列宽；能对数据设置”条件格式“。知识目标：掌握数据的数字类型、对齐方式以及字体等的设置方法；掌握行高和列宽的设置方法；掌握数据”条件格式“的设置方法。教师展示学生训练由学生自主尝试在上一讲建立的工作表的基础上操作：设置表格的标题文字合并居中,并设置A2:F2单元格内的内容居中用鼠标拖动调整第一行的行高；设置A1单元格内的标题字体为黑体、14号、加粗、深蓝色教师演示数据的数字类型、对齐方式以及字体等的设置。用鼠标和用菜单命令两种方式调整行高、列宽，并能调整多行以及多列。使用”条件格式“命令来设置条件格式。学生对做错或未完成的操作进行完善。练习打开”职工工资统计表.xls“：设置表格的标题居中，黑体，24号字体，蓝色。设置表格内字体仿宋，16号字体，颜色为深蓝，对齐方式为水平居中。将基本工资大于1500的数据设置为红色。将扣款大于50的数据设置蓝色，加粗。练习将全体职工的出生日期转化为‘1900年1月1日’的形式。设置表格行高为21,设置B，E，F，G，H，I，J列为最适合的列宽，D列列宽为20。设置所有的数值数据为保持两位小数，负数为红色。 保存“职工工资统计表”。效果图如下：任务三：设置成绩工作表的边框、底纹能力目标：能进一步对Excel2003的工作表数据进行美化和修饰；能够使用自动套用格式修饰工作表；能管理工作表。知识目标：掌握Excel2003工作表数据的边框和底纹的设置方法；掌握自动套用格式的使用方法；掌握工作表的插入、删除、重命名以及复制等操作；了解工作表和工作簿的保护操作。教师展示学生训练由学生自主尝试在上一讲建立的工作表的基础上操作：设置A1单元格为浅青绿颜色的底纹，A2:F21单元格区域的底纹颜色为茶色；对A2:F21单元格区域的外边框设置为深蓝色的双线，内边框设置为紫罗兰色的单实线；学生训练A2:F2单元格区域的图案设置为12.5%的红色图案。将当前的工作表重命名为“完成格式设置后的成绩登记表”。复制当前工作表，并重命名为“统计分析成绩登记表一”，点击“保存”按钮，保存文件。教师演示工作表数据的边框和底纹的设置方法。自动套用格式方法的使用。工作表的插入、删除、重命名以及复制等操作。工作表和工作簿的保护操作。学生对做错或未完成的操作进行完善。练习打开”职工工资统计表.xls“：设置表格的外部边框为红色粗实线，表格内线为蓝色细虚线。为表格加上浅黄色的底纹。将工作表命名为“九月工资统计表”。复制一份新的工作表，并命名为“十月工资统计表”，将其移动到“九月工资统计表”后。练习使用“自动套用格式”的“彩色一”修饰美化“十月工资统计表”。对“十月工资统计表”进行保护，并只允许此工作表的所有用户进行“选定锁定单元格”和“选定未锁定的单元格”操作。保存“职工工资统计表”。效果图如下：子项目二：统计和分析校运会比赛成绩表4.2.1任务一：使用公式和函数统计成绩表4.2.2任务二：排序和分类汇总成绩表4.2.3任务三：筛选成绩表4.2.4任务四：为成绩表创建图表任务一：

使用公式和函数统计成绩表能力目标：能使用公式和函数对表格数据进行计算；能理解单元格的相对引用和绝对引用。知识目标：掌握Excel2003单元格的相对引用与绝对引用的区别；掌握Excel2003公式和函数的使用方法。教师展示学生训练打开“女子跳远比赛成绩登记表（学生组）”.xls，选择“统计分析成绩登记表一”工作表，在G2、H2、I2单元格分别输入平均成绩、最高成绩、排名，在C21单元格输入实际参与人数。先选中B2单元格，点击常用工具栏上的“格式刷”按钮，然后拖动鼠标选择G2:I2单元格区域，设置列标题为相同的格式。选择C21单元格，设置其为仿宋GB_2312，12号字体、加粗、深蓝。学生训练选中A1:I1单元格区域，点击两次格式工具栏上的“合并居中”按钮。先选中D3单元格，点击常用工具栏上的“格式刷”按钮，然后拖动鼠标选择G3:H20单元格区域，设置其为相同的数据格式。再次选择A2:I21单元格区域，设置其底纹填充色为茶色，表格外边框为深蓝色双线，内部框线为紫罗兰单实线。学生训练选择G3单元格，选择常用工具栏上的按钮右侧的下拉三角形，选择平均值，按回车键确认，将鼠标移动到G3单元格的右下角的填充柄上，当光标变化为黑色十字架形状时，拖曳填充柄，覆盖G4:G20的单元格区域，删除G14、G16、G20单元格内没有意义的数据。学生训练选择H3单元格，选择常用工具栏上的按钮右侧的下拉三角形，选择最大值，拖动鼠标选择D3:F3单元格区域作为数据区，按回车键确认，使用同样的方法复制公式，求出其他选手最高成绩，并删除H14、H16、H20单元格内没有意义的数据。学生训练选择D21单元格，选择常用工具栏上的按钮右侧的下拉三角形，选择计数，拖动鼠标选择D3:D20单元格区域作为数据区，按回车键确认，求出参与第一跳的实际参与选手人数，使用同样的方法复制公式，求出第二跳和第三跳的实际参与选手人数。设置计算得到的数据格式为仿宋GB_2312，12号字体、深蓝。学生训练选择I3单元格，点击编辑框上的按钮，打开插入函数对话框，在“选择类别”中选择“全部”，然后在“选择函数”中找到“RANK”函数，单击确定，将弹出“RANK函数参数”对话框。学生训练在弹出的对话框中的“Number”框中，用鼠标左键单击H3单元格。在“Ref”框中，用鼠标左键拖曳选中H3:H20单元格区域，并分别将鼠标定位到H3、H20地址的前面，按F4键，将相对引用转换为绝对引用。在“Order”框中，输入“0”。单击确定即完成函数参数的设置。使用同样的方法复制公式，求出其他选手的排名，并删除I14、I16、I20单元格内没有意义的数据。设置“排名”列的数据格式为仿宋GB_2312，12号字体、深蓝。点击保存按钮，保存文件。教师演示常用工具栏上自动求和按钮“”的使用。使用函数计算工作表数据。使用公式计算工作表数据。单元格的相对引用和绝对引用。在公式中使用函数。相对引用与绝对引用相对引用是指被引用的单元格与引用的单元格之间的位置关系是相对的，当将一个单元格的带有相对引用的公式复制到另外的单元格时，公式中引用的单元格将变成与目标单元格（即获得复制公式的单元格）一样相对位置上的单元格。绝对引用是指被引用的单元格与引用的单元格之间的位置关系是绝对的，无论将公式复制到任何单元格，公式中引用的还是原来的单元格。绝对引用的引用方法是当前引用单元格的行和列的前面加上“$”符号。练习打开”职工工资统计表.xls“：使用公式求出各位职工的实发工资：实发工资=基本工资+补贴工资+奖金-扣款。设置实发工资列的数字格式为“货币”，并以“￥1988.22”形式显示，设置第J列的列宽为“最适合的列宽”。在F24单元格用函数求出基本工资的平均数。在G24单元格用函数求出最高的补贴工资额。在H24单元格用函数求出奖金的总和。练习在I24单元格用函数求出最低的扣款额。增加两列，在K3，L3单元格中分别输入“排名”和“工资标识”，设置为合适的列宽。在“排名”列中，将奖金发放的高低名次排列出来，并设置为整数格式。在“工资标识”列中，将实发工资在2000元以上的，用“较高”标识出来，否则用“一般”标识。练习重新设置表格的字体为仿宋GB_2312，字号为16号，设置表格的外部边框为红色粗实线，表格内线为蓝色细虚线，并加上浅黄色的底纹。选择A1:L1单元格区域，单击两次合并居中按钮。保存“职工工资统计表”。效果图如下：附：常用函数SUM（number1，number2，……）：返回某一单元格区域中所有数字之和。AVERAGE（value1，value2，……）：返回所有包含数值单元格的平均值。MAX（value1，value2，……）：返回所有数值数据的最大值。MIN（value1，value2，……）：返回所有数值数据的最小值。MOD（number,divisor）:返回两个数相除后的余数，number表示被除数，divisor表示除数。附：常用函数ROUND（number，num_digits）：返回某个数字按指定位数舍入后的数字。num_digits>0表示保留num_digits位小数。num_digits=0表示保留整数。num_digits<0表示从整数的个位开始向左对第K位进行舍入，其中K是num_digits的绝对值。如：ROUND（234.567,1）的结果为234.6；ROUND(234.567,0)的结果为235；ROUND(234.567,-1)的结果为230。附：常用函数COUNT（value1，value2，……）：返回各参数中数值型数据的个数。MODE（value1，value2，……）：返回频率最多的数值。INT(number):将数字向下舍入到最接近的整数。如:INT(34.25)的结果为34。ABS(number):计算绝对值。Number表示需要计算绝对值的实数。附：常用函数IF(logical_test,value_if_true,value_if_false):执行真假值判断，根据逻辑计算的真假值，返回不同结果。Logical_test表示计算结果为TRUE或FALSE的任意值或表达式；value_if_true表示logical_test为TRUE时返回的值；value_if_false表示logical_test为FALSE时返回的值。附：常用函数RANK(number,ref,order)(number表示需要找到排位的数字，ref表示数据字列表数组或对数字列表的引用，order表示排序的方式,如果order为零或省略，则以降序方式给出结果，反之按升序方式)：返回某个数字在数字列表中的排位。附：常用函数COUNTIF(range,criteria):统计指定单元格区域中满足条件的单元格的个数。参数range表示的是需要计算单元格个数的区域，参数criteria表示确定的条件。SUMIF(range,criteria，sum_range):根据指定条件对若干单元格求和。其中，参数range表示用于条件判断的单元格区域；参数criteria表示确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；参数sum_range是需要求和的实际单元格。任务二：排序和分类汇总成绩表能力目标：能够对工作表数据进行排序；能够对工作表数据进行分类汇总。知识目标：掌握对工作表数据进行排序的方法；掌握对工作表进行分类汇总的方法。教师展示学生训练打开“女子跳远比赛成绩登记表（学生组）.xls”工作簿，复制“统计分析成绩登记表一”，并将复制得到的工作表更名为“统计分析成绩登记表二”。在“统计分析成绩登记表二”中操作，选定A2:I20单元格区域，选择“数据”菜单的“排序”命令，打开“排序”对话框，设置“主要关键字”为“排名”、升序，次要关键字为“姓名”、降序，“我的数据区域”选择“有标题行”复选框，单击确定。学生训练再次选定A2:I20单元格区域，用同样的方法对工作表进行排序，主关键字设置为“学院”、降序，次关键字设为“无”。选定A2:I20单元格区域，选择“数据”菜单的“分类汇总”命令，打开“分类汇总”对话框，在对话框中设置“分类字段”为“学院”，“汇总方式”为“平均值”，“选定汇总项”中取消原来的“排名”前的勾，勾选“最高成绩”，复选框中选择“替换当前分类汇总”和“汇总结果显示在数据下方”两项，单击确定。保存“女子跳远比赛成绩登记表（学生组）.xls”。教师演示数据的排序操作。数据的分类汇总操作。练习打开”职工工资统计表.xls“，在九月工资统计表中做如下几个操作：复制“九月工资统计表”，并更名为“九月工资排序表”，按职工的实发工资进行降序排序，若实发工资相同，则按基本工资降序排序。复制“九月工资统计表”，并更名为“九月工资分类汇总表1”，分别求出各类职称人员实发工资的平均值。复制“九月工资统计表”，并更名为“九月工资分类汇总表2“，分别求出男女员工的奖金总和。保存“职工工资统计表.xls”。效果图如下：效果图如下：任务三：筛选成绩表能力目标：能对工作表进行自动筛选和高级筛选；能制作数据透视表；能使用单变量求解命令。知识目标：掌握对工作表数据进行自动筛选和高级筛选的方法；掌握数据透视表的制作方法；掌握单变量求解的操作方法。教师展示学生训练打开“女子跳远比赛成绩登记表（学生组）.xls”工作簿，复制“统计分析成绩登记表一”，并将复制得到的工作表更名为“统计分析成绩登记表三”。选定D3单元格，选择“数据”菜单的“筛选”的“自动筛选”命令，单击“最高成绩”右下角的黑三角形，选择“自定义”命令，打开“自定义自动筛选”对话框。设置“最高成绩”为“大于或等于3.5”，单击确定。学生训练选择“数据”菜单的“筛选”的“自动筛选”，取消“自动筛选”。利用高级筛选查找选手中姓王，并且最高成绩大于3.3的选手信息。先设定好条件区域，在K1单元格输入“姓名”，K2单元格输入“王”，L1单元格输入“最高成绩”，L2单元格输入“>3.3”。学生训练选择“数据”菜单的“筛选”的“高级筛选”，打开“高级筛选”对话框，设定“方式”为“将筛选结果复制到其他位置”，用鼠标定位到“列表区域”框中，按住鼠标左键拖动A2:I20单元格区域，设定好列表区域；同样的设定“条件区域”为K1:L2，“复制到”A23单元格，单击确定。保存“女子跳远比赛成绩登记表（学生组）.xls”。教师演示数据的自动筛选操作。数据的高级筛选操作。数据的数据透视表的制作。单变量求解的操作。高级筛选条件区域的几个条件设置如果在同一行，代表两个条件之间是“与”的关系，如果两个条件之间是“或”的关系，则两个限定条件不能写在同一行。如要查询的是姓王的选手或者选手最高成绩大于3.3的选手信息，则不应在L2单元格中输入“>3.3”，而应在L3单元格中输入“>3.3”。练习打开”职工工资统计表.xls“，在九月工资统计表中做如下几个操作：复制“九月工资统计表”，并更名为“九月工资自动筛选表”，用自动筛选筛选出所有男职工工资统计表。复制“九月工资统计表”，并更名为“九月工资高级筛选表”，用高级筛选筛选出职称为工程师，并且实发工资高于1800的人员，放置在以A30开始的一块数据区中。条件区域放置在以A26开始的一块数据区内。练习在“九月工资高级筛选表”中，筛选出基本工资在1600以上，或者奖金在400以上的人员，在原有区域显示筛选结果。条件区域放置在以G26开始的一块数据区内。在“九月工资统计表”中，创建数据透视表到新工作表，并分别求出各类职称的男、女职工的平均实发工资，平均基本工资和平均奖金。保存“职工工资统计表.xls”。效果图如下：效果图如下：任务四：为成绩表创建图表能力目标：能对Excel2003工作表中的数据用图表进行分析。能对工作表进行页面设置也打印设置。知识目标：掌握为工作表数据创建图表的方法。掌握工作表的打印预览和页面设置操作方法。教师展示学生训练打开“女子跳远比赛成绩登记表（学生组）.xls”工作簿，复制“统计分析成绩登记表一”，并将复制得到的工作表更名为“统计分析成绩登记表四”。选中A2:I20数据区域，选择“数据”菜单的“排序”，设置主关键字为“排名”，排序方式为“升序”，单击确定按钮。学生训练按住Ctrl键，选定不连续的两块数据区域B2:B8和H2:H8，选择“插入”菜单的“图表”命令，打开“图表向导-4步骤之1-图表类型”对话框，在“图表类型”栏中选择柱形图，并在右侧“子图表类型”栏目中选择簇状柱形图。将鼠标指针移到“按下不放可查看示例”按钮上，并按住鼠标左键，能看到所选图表的实际效果。学生训练单击“下一步”按钮，出现“图表向导—4步骤2—图表源数据”对话框，在“数据区域”选项卡中，设置写列产生在“列”。单击“下一步”按钮，出现“图表向导—4步骤之3—图表选项”对话框。设置图表标题为“获奖选手最高成绩图”，分类（X）轴为“姓名”，数值（Y）轴为“成绩”，在“数据标志”选项卡中设置数据标签包括“值”。学生训练单击“下一步”按钮，出现“图表向导—4步骤之4—图表位置”对话框，选择“作为其中的对象插入”。单击“完成”按钮，将插入的图表放置到A23:I44单元格区域。右键单击图表工作区域，选择“图表区格式”命令，打开“图表区格式”对话框，选择“填充效果”按钮，设置“纹理”为“花束”，单击“确定”按钮。学生训练在“绘图区”单击鼠标右键，选择“绘图区格式”命令，打开“绘图区格式”对话框，设置绘图区的图案为“碧海青天”的预设渐变填充效果。单击图表标题文本框，设置标题的字体为华文行楷，26号字，字体颜色为红色，底纹为黄色。学生训练单击右边的图例“最高成绩”文本框，设置字号为20，底纹颜色为黄色。同样设置数值轴标题“成绩”和分类轴标题“姓名”的字号为20，底纹颜色为黄色。保存“女子跳远比赛成绩登记表（学生组）.xls”。教师演示用图表向导创建图表。修改图表数据。修饰图表。工作表的打印预览。工作表的页面设置。练习打开”职工工资统计表.xls“，在九月工资统计表中做如下几个操作：复制“九月工资统计表”，放置在“十月工资统计表”之前，命名为“九月工资统计图表”。在“九月工资统计图表”中，按职工的实发工资进行降序排序，若实发工资相同，则按基本工资降序排序。设定数据源为实发工资排名前五名的员工的姓名、基本工资、补贴工资、奖金和实发工资，插入簇状柱形图，系列产生在行，图表标题为“职工工资统计图”，插入后的图表放置在A26：G46区域内。练习复制刚插入的图表到A48：G68区域内，修改图表的标题文字为“华文行楷蓝色”，字体大小为27。修改数据源为工资排名前三名的员工的姓名、基本工资、补贴工资、奖金和实发工资。修改图表类型为“面积图”。设置图表的背景为水滴纹理。练习设置页面为横向打印，纸张大小为A4，页边距上下左右均为2.2，居中方式为水平，自定义页眉为“九月工资统计表”，字体为楷体GB_2312，字号为10号，页脚设置为“第1页，共&页”。保存“职工工资统计表.xls”。效果图如下：子项目三：制作校运会奖章证书4.3.1任务一：创建邮件合并的Excel数据源和Word主文档4.3.2任务二：使用邮件合并功能制作套用文档任务一：创建邮件合并的Excel数据源和Word主文档能力目标：能使用Word2003编辑普通文档；能创建和编辑Excel2003工作表。教师展示教师展示学生训练新建一个Excel文