计算机应用基础项目化教程—第4模块.ppt

,1,计算机应用基础课程组,计算机应用基础,模块四 电子表格Excel2000,项目一 制作学生会干部基本情况表项目二 统计分析学生成绩项目三 工资表数据分析,项目一 制作学生会干部基本情况表,项目提出现在大型企业管理部门会将单位职工的联络方式及相关信息进行搜集汇总，制作成精美的通讯录。企业员工及相关部门可以使用通讯录方便地查询员工的基本信息，从而极大地提高了工作效率。小李同学是学校里学生会的主要成员，学期初负责学生管理的王老师给了他一个工作任务，即制作学生会干部基本情况表，包括序号、姓名、职务、系别、学号、性别、身份证号和出生日期等内容，制作完成后将内容美化排版。,3,2022/7/8,项目提出,项目分析 通过王老师的要求可知，本项目中主要涉及的工作有工作簿整理设置及学生会干部基本情况表的所有内容的输入排版等，本项目的重点是创建数据表，学会使用各种方法输入不同类型的原始数据，对相应的数据进行格式化设置，并使用常用的格式工具来规范表格。,4,项目一 制作学生会干部基本情况表,项目一 制作学生会干部基本情况表,项目一样文效果,相关知识点1. Excel 2010的窗口组成Excel 2010启动成功后，屏幕上显示的Excel 2010应用程序窗口。Excel 工作窗口一般由两个窗口组成，其中，大窗口是Excel 2010应用程序窗口，小窗口为Excel 2010工作簿窗口。,6,项目一 制作学生会干部基本情况表,项目一 制作学生会干部基本情况表,图4-2Excel 2010工作窗口,项目一 制作学生会干部基本情况表,相关知识点2.工作簿和工作表在Excel中，一个工作簿就是一个Excel文件，是工作表的集合体，工作簿就像日常工作的文件夹。当打开一个新的工作簿文件时系统默认由3个工作表组成，分别是Sheet1、Sheet2和Sheet3，用户可以根据需要增加工作表的数量，但是最多只能放255张工作表。工作表是显示在工作簿窗口中的表格，是工作簿文件的基本组成部分。每张工作表都以标签的形式排列在工作簿的底部。Excel工作表是由行和列组成的一张表格，默认由1048576行和16384列构成，行号用数字1、2、1048576等来表示，列号用英文字母AZ、AAAZ、BABZ命名。当需要进行工作表切换时，只需要单击相应的工作表标签名称即可。,项目一 制作学生会干部基本情况表,相关知识点3.单元格和单元格区域工作表中行和列交叉的区域称为单元格。单元格是工作表中用于存储数据的基本单位，每一个单元格都有一个固定的地址，地址编号由列号和行号组成，命名时列号在前，行号在后，如单元格“B6”。矩形单元格区域的地址表示方法为“左上角单元格地址：右下角单元格地址”，如“A7：E10”表示为左上角单元格A7右下角单元格是E10的单元格区域。=L(注意：可以给区域命名，区域名第一个字母必须是字母、汉字或下划线，且不可与引用地址相同，最长可达255个字符，区域名在引用中可以使用。,相关知识点4.常用数据类型及输入Excel中数据有很多类型，最常用的数据类型有文本型、数值型和日期型等。文本型数据可以包括字母、数字、空格和符号，其对齐方式为左对齐；数值型数据包括09、()、+和-等符号，其对齐方式为右对齐。快速输入有很多技巧，例如，利用填充柄自动填充、自定义序列填充、按“Ctrl+Enter”组合键可以在不相邻的单元格中自动填充重复数据等。5.单元格的格式化设置单元格的格式化设置包括设置数据的类型、单元格的对齐方式、字体、字号及单元格的边框和底纹等。,项目一 制作学生会干部基本情况表,项目一 制作学生会干部基本情况表,相关知识点6.多工作表的操作多工作表的操作，包括对工作表的重命名以及工作表的复制、移动、插入和删除等操作，对工作表操作时一定要先选定工作表标签。工作表之间数据的复制、粘贴以及单元格引用等，都是在工作表单元格之间进行的。,项目实施任务一 工作簿、工作表的整理设置任务二 输入“学生会干部基本情况表”内容任务三 格式化“学生会干部基本情况表”任务四 打印工作表,项目一 制作学生会干部基本情况表,1.新建工作簿新建“学生会干部基本情况表.xlsx”文档，保存在E盘根目录下。2.添加、删除和重命名工作表，编辑工作表名称及标签颜色重命名工作表“Sheet1”为“学生会干部基本情况表”，并设置标签颜色为蓝色，删除工作表“Sheet2”和“Sheet3”。,项目一制作学生会干部基本情况表 任务一工作簿、工作表的整理设置,项目实施,1.选定单元格单元格的选择即单元格的激活，是常用的单元格操作之一，包括单个单元格的选择、多个单元格的选择以及整行或整列选择。2.输入标题文字、日期及表头文字按照样文效果，输入标题及各列表头文字。,项目一制作学生会干部基本情况表 任务二 输入“学生会干部基本情况表”内容,项目实施,3.输入学生会干部情况信息按照样文效果，输入员工的序号、姓名、职务、系别、学号、性别、身份证号码和出生日期等基本信息。,项目一制作学生会干部基本情况表 任务二 输入“学生会干部基本情况表”内容,项目实施,1.设置单元格合并设A1：L1单元格区域合并且居中对齐。2.设置字体设置标题：隶书、字号24号、加粗；表头文字：宋体、字号11号；设置数据区域：宋体、字号9号。3.格式的复制将A2单元格的字体设置加粗，并用格式刷将格式复制给B2:L2单元格区域。,项目一制作学生会干部基本情况表 任务三 格式化“学生会干部基本情况表”,项目实施,4.设置数字格式化设置“序号”统一为4位数字，不足4位的，高位补零。设置“出生日期”统一为“2001年12月20日”格式。5.设置行高和列宽设置第一行的行高为“30”，第221行的行高为“15”，设置各列列宽为自动调整。6.设置对齐格式设置整张工作表水平对齐和垂直对齐方式均为居中。,项目一制作学生会干部基本情况表 任务三 格式化“学生会干部基本情况表”,项目实施,7.插入工作表行在第2行前面插入一行，新行行高设置为“20”，将J2：L2单元格区域合并并输入文字“制表人：李健”，设置字体：宋体、10号、下划线。8.设置表格边框线和底纹设置表格的外边框线为深红色双线，内边框线为蓝色实线，给标题行文字添加“茶色、背景2、深色25%”的底纹。,项目一制作学生会干部基本情况表 任务三 格式化“学生会干部基本情况表”,项目实施,9.添加批注给姓名为“廖江年”的学生添加批注，内容为“学生会主席候选人”。10.移动或复制工作表复制“学生会干部基本情况表”，并将复制后的工作表重命名为“学生会干部基本情况表备份”。,项目一制作学生会干部基本情况表 任务三 格式化“学生会干部基本情况表”,项目实施,1.设置页面页边距设置文件纸张大小为A4，方向为横向，上、下页边距为5.0，左、右页边距为2.5，并且表格打印在纸张正中间。2.设置页眉/页脚设置页脚中页码和总页数。3.设置打印区域设置打印区域为A1：L21，并在打印的每页表格中添加第13行的表头信息。,项目一制作学生会干部基本情况表 任务四 打印工作表,项目实施,4.设置分页工作表较大时，Excel一般会自动为工作表分页，另外可以根据需要对工作表进行人工分页。在Excel中可手动插入分页符，分页包括水平分页和垂直分页。5.分页预览分页预览可以在窗口中直接查看工作表的分页情况，其优越性还体现在分页预览时，仍可以像平常一样编辑工作表，直接改变设置的打印区域大小，还可以方便地调整分页符的位置。,项目一制作学生会干部基本情况表 任务四 打印工作表,项目实施,项目一 制作学生会干部基本情况表,1.设置起始包含的工作表的表数默认情况下，Excel在创建工作簿时，自动包含了名为“Sheet1”“Sheet2”和“Sheet3”的3张工作表。而很多时候工作表的数目不一定符合各用户的要求，此时可以通过设置来改变新建工作簿时所包含的工作表数目。2.查找替换在数据处理过程中，查找与替换是一项非常重要和常用的功能，可以快速定位到指定内容的单元格，或替换掉指定内容。在数据量较大、数据分析比较分散的情况下，查找和替换功能可以大大提高查找和修改工作的效率。,拓展提高,项目一 制作学生会干部基本情况表,3.冻结窗口和拆分窗口(1)冻结窗口比较复杂的大型表格，常常需要在滚动浏览表格时，固定显示表头标题行(或表格列) 。(2)拆分窗口Excel的拆分窗口功能可使同一个数据表格中的不同数据可以在同一个窗口显示出来，从而达到快速对比的目的。4.设置数据有效性当需要输入大量数据时，有时难免会出错，那么用户可以把一部分检查工作交给计算机来处理，这就需要提前对单元格数据的有效性进行设置。,拓展提高,项目一 制作学生会干部基本情况表,1.启动Excel 2010应用程序，创建一个工作簿，并将工作表标签命名为“成绩表”，同时设置工作表标签为红色。2.输入数据内容，要求输入过程中“考号”列要用填充的方式录入。3.各科成绩内容要求数据类型为数值型，小数位数为2，设置“姓名”列的数据类型为文本。4.设置标题，要求标题使用合并单元格，并且水平、垂直方向均居中。标题文字字体为黑体，字号为20，加粗，颜色为标准色红色，加双下划线，行高设置为27磅。,自主训练,5.设置列标题字段，设置行高22，字体为隶书，字号为16，加粗，颜色为蓝色，文字在水平、垂直方向居中，设置单元格底纹为浅绿色。6.设置第316行行高为15，设置单元格区域A3：A16的格式：字体为方正姚体，字号为12，颜色为黑色。7.设置正文其他单元格内容，字体为Times New Rome，字号为11，颜色为黑色，文字在水平、垂直方向均居中，各列宽度设置为“最适合的列宽”。8.设置工作表正文外边框为红色最粗双线，内边框为蓝色最细单实线。9.保存文档到桌面，工作簿的名称为“成绩表”。,项目一 制作学生会干部基本情况表,自主训练,项目二 统计分析学生成绩,项目提出学期结束时，根据学校安排，大学语文李老师需要对任课班级中学生的成绩、名次和等级等进行各种统计分析，根据课程考核安排，学生的成绩由作业情况、考勤情况、期中成绩和期末成绩组成，在传统情况下，教师需要手动统计学生的到课情况，登记学生的作业及考试成绩，根据以上大量的数据进行计算和汇总，并最终计算学生的总评成绩，这样统计工作量极大而且容易出错，李老师想使用电子表格完成，他制作了4张Excel工作表，分别为“作业表”“考勤表”“成绩表”和“分析表”，分别下图所示。,26,2022/7/8,项目提出,项目分析 本任务首先要将学生“作业表”和“考勤表”中的数据转换为分数，将数据复制填入学生“成绩表”中。在“作业表”中需要用AVERAGE函数计算六次作业的平均分，在“考勤表”中，用“”“”或“”分别表示学生到课、迟到和旷课三种情况。每一个“”得10分，每一个“”得5分，“”不得分，可利用COUNTIF函数计算“”“”的个数，在分别乘以10和5后相加得到“考勤分”。,28,项目二 统计分析学生成绩,项目二 统计分析学生成绩,项目分析在“成绩表”中需要根据考勤分、作业、期中和期末成绩按比例计算学生总分，用RANK函数统计名次，用IF函数判断学生是否具备三好学生资格，以及用IF函数的嵌套统计考试等级，利用条件格式将符合要求的数据突出显示。在“分析表”中需用COUNT函数统计班级人数，MAX函数统计最高分，MIN函数计算最低分，利用函数与公式的混合应用求及格率、优秀率并统计各分数段学生的人数及比率，最后使用图表显示“期末成绩”各分数段的学生人数。,相关知识点 1.运算符号公式中的运算符有以下4类：(1)算数运算符：完成基本的数学运算，返回值为数值。如+(加)、-(减)、*(乘)、/(除)、%(百分比)和(指数)。(2)比较运算符：用来比较两个数大小的运算符，返回值只有两种：TRUE和FALSE。如=(等于)、(大于)、=(大于或等于)、(不等于)。(3)文本运算符(&)：用于将一个或多个文本连接为一个组合文本的运算符。如在单元格中输入“=山东&青岛”。返回结果为“山东青岛”。(4)引用运算符(：)：用于合并多个单元格区域。例如，A1：G1表示引用A1到G1之间的所有单元格(以A1和G1为顶点的长方形区域)。,30,项目二 统计分析学生成绩,相关知识点2.单元格的引用(1)相对引用。相对引用的意义是指当公式在复制或移动时公式中引用单元格的地址会随着公式的移动而自动改变。相对引用使用“H2”这样的方式来表示。(2)绝对引用。绝对引用是指当公式在复制或移动时，公式中的各单元格的地址不会随着公式的移动而改变，用“$H$2”这样的方式来表示。(3)混合引用。当用户需要固定某行引用而改变列引用，或者要固定某列引用而改变行引用时就要用到混合引用。混合引用采用“$H2”或“H$2”等形式表示，如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用将不变。(4)三维地址引用。在Excel中，不但可以引用同一工作表中的单元格，还能引用同一工作簿中不同工作表的单元格，也能引用不同工作簿中的单元格(外部引用)。不同的工作簿中单元格的引用格式为“工作簿名+工作表名！+单元格引用”。,31,项目二 统计分析学生成绩,相关知识点 3.公式公式是在工作表中对数据进行分析的等式，可以对工作表中的数值进行加、减、乘和除等运算。公式以等号“=”开始，其后才是公式的内容。公式的输入和编辑等操作都可以在编辑栏中完成。在单元格中显示的并不是公式本身，而是公式计算的结果。公式中通常包含有函数。4.常用函数Excel 2010函数共有13类，包含400多个函数，涵盖了财务、日期、工程、逻辑、数学、统计和文本等各种不同领域的数据处理任务。函数的语法为：函数名(参数1,参数2,)单击编辑栏左侧的“插入函数”按钮，可方便地插入各种函数。,32,项目二 统计分析学生成绩,相关知识点6.条件格式Excel提供的“条件格式”功能可以对选定区域中各单元格中的数值进行判断，如果判断结果在指定的范围内，则动态地为单元格设定格式，包括数字、字体、边框线和填充等设置。,33,项目二 统计分析学生成绩,项目实施 任务一 计算表格数据 任务二 统计分析表格数据 任务三 使用条件格式突出显示信息,34,项目二 统计分析学生成绩,项目实施1.用AVERAGE函数计算作业“平均分”在“作业表”中，利用AVERAGE函数计算6次作业成绩“平均分”。2.计算“考勤分”在“考勤表”中，利用COUNTIF函数统计每位同学的全勤、请假的次数，并用公式计算考勤分。3.计算“总评分”把“考勤分”和作业“平均分”粘贴到“成绩表”中，利用公式计算“总评分”。,35,项目二 统计分析学生成绩 任务一 计算表格数据,项目实施 4.计算“名次”根据“总评分”成绩使用RANK.AVG函数统计名次。 5.判断“三好学生资格”及“考试等级”1)根据“总评分”成绩计算填入“是否合格”列根据“总评分”成绩，使用IF函数判定是否具备三好学生资格。2)根据“总评分”计算相应的“等级”列,36,项目二 统计分析学生成绩 任务一 计算表格数据,项目实施1.计算班级人数及成绩的最高分、最低分用COUNT函数求学生人数；使用MAX函数求最高分；用MIN函数求最低分。2.求及格率、优秀率3.统计“期末成绩”各分数段的学生人数使用COUNTIF函数计算相应的“是否合格”及“等级”列。4.统计“期末成绩”各分数段的学生比率,37,项目二 统计分析学生成绩 任务二 统计分析表格数据,项目实施 使用Excel中的条件格式功能，可以预置一种单元格格式，并在指定的某种条件被满足时自动应用于目标单元格。可以预置的单元格格式包括边框、底纹和字体颜色等。此功能可以根据用户的要求，快速对特定单元格进行必要的标识，以起到突出显示的作用。 使用条件格式设置“期末成绩”不及格的分数显示为浅红填充色深红色文本。,38,项目二 统计分析学生成绩 任务三 使用条件格式突出显示信息,自主训练利用本项目所学知识，打开“津贴表(素材)”文件。要求如下：1.利用SUM函数计算“应发工资”。2.利用公式计算“实发工资”：实发工资=应发工资-缺勤扣款。3.利用IF函数计算“是否全勤”，如果缺勤扣款数额为0，则显示“全勤”；否则显示“否”。4.利用RANK函数计算排名。5.利用自动求和函数计算合计行数据。6.根据月标准额，利用COUNTIF函数统计处长、副处、正科、副科、科员和工勤的人数。,39,项目二 统计分析学生成绩,项目提出山东新盛玩具厂财务人员需要记录每位员工每月生产的某种玩具的数量；需要根据生产的玩具的数量以及玩具的单价计算该员工的计件工资；需要根据员工的计件工资、基本工资和应扣项目等计算应发工资和实发工资；需要根据计件工资表统计出该月各类产品数量、汇总情况和各生产车间该月的生产情况；还要根据以上数据制作相应的图表。为完成以上工作，财务处的李会计制作了两张工作表，即员工计件工资表和员工工资总表，如下图示。,40,项目三 工资表数据分析,项目三 工资表数据分析,41,2022/7/8,项目提出,项目提出由于财务管理的需要，需要进行以下七项工作：(1)计算员工的计件工资和各车间的总产值；(2)计算员工的应发工资和实发工资；(3)将员工按所属车间和实发工资排序；(4)筛选出实发工资位于10001500元的员工信息，以便安排补助；(5)对各产品的生产量进行分类汇总；(6)使用数据透视表，统计各车间各产品的生产量；(7)生成各车间各类产品生产量统计图表。,42,项目三 工资表数据分析,项目分析 在“产品单价表”中，已经有各产品的“单价”，可以利用VLOOKUP函数在“产品单价表”中查找某产品的“单价”，再填入相应的“产品单价”列中。利用公式可以计算“产值”和“计件工资”，“计件工资”为“产值”的10%。在“员工工资总表”中，先引用“员工计件工资表”中的“计件工资”，然后利用公式计算“应发工资”和“实发工资”。,43,项目三 工资表数据分析,项目分析 有了工资数据，可以利用Excel的筛选功能，将工资较低(如“实发工资”位于10001500元)的员工筛选出来，以便安排补助等。应用高级筛选前，要先设置筛选条件。可以利用“分类汇总”功能，统计本月每一种玩具的生产数量。分类汇总前一定要先对“产品名称”进行排序。使用数据透视表，可以统计各车间各产品的生产量。在数据透视表的基础上，使用图表功能生成各车间各类产品生产量统计图表。,44,项目三 工资表数据分析,相关知识点1.VLOOKUP函数主要功能：在表格或单元格区域的第一列中查找指定的数值，然后返回该区域中该数值所在行中指定列处的数值。默认情况下，表是以升序排序的。使用格式：VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)2.SUMIF函数主要功能：根据指定条件对若干符合条件的单元格求和。使用格式：SUMIF(Range,Criteria,Sum_range),45,项目三 工资表数据分析,项目三 工资表数据分析,相关知识点3.数据的排序对数据排序有两种情况：一种是以单一条件(即只有一个关键字)排序的简单排序法；另一种是按照两个以上的关键字进行排序的方法(也叫高级排序)。排序方式有升序和降序两种，升序中数字按照从小到大的顺序排序，降序则顺序反转，空格总排在后边。4.数据的筛选数据筛选是使数据清单中只显示满足条件的数据记录，而将不满足条件的数据记录从视图中隐藏起来。Excel筛选分为数据自动筛选和高级筛选。自动筛选是指根据用户设定的筛选条件，自动将表格中符合条件的数据显示出来；而高级筛选指的是由用户自定义多重筛选条件的筛选操作，属于比较复杂的数据筛选操作。,项目三 工资表数据分析,相关知识点5.分类汇总分类汇总是指对工作表中的某一项数据进行分类，再对需要汇总的数据进行汇总计算。汇总方式包括求和、计数、求平均值、求最大值和求最小值等。6.数据透视表数据透视表是一种交互式工作表，用于对现有工作表进行汇总和分析，可快速合并和比较大量数据。创建数据透视表后，可以按不同的需要、根据不同的关系来提取和组织数据。,项目三 工资表数据分析,相关知识点7.图表在Excel 2010中，图表是指将工作表中的数据用图形表示出来，使用图表会使得用Excel编制的工作表更易于理解和交流，使数据更加有趣、吸引人、易于阅读和评价，也可以帮助用户分析和比较数据。,项目三 工资表数据分析,项目实施任务一 员工计件工资表数据填充任务二 员工工资表数据填充任务三 数据排序任务四 数据筛选任务五 按“产品名称”分类汇总任务六 使用数据透视表统计各车间各产品的生产量任务七 制作产品统计分析图,1.利用函数查找“产品单价”先利用VLOOKUP函数在“产品价格表”中查找“产品单价”。2.利用公式计算“产值”和“计件工资”按照“产值=产品单价数量”“计件工资=产值10%”公式计算“产值”和“计件工资”。3.利用函数计算各车间总产值利用SUMIF函数计算各车间的总产值。,项目三 工资表数据分析 任务一 员工计件工资表数据填充,项目实施,在“员工工资总表”中，先引用“员工计件工资表”中的“计件工资”，然后利用公式计算“应发工资”和“实发工资”。应发工资=计件工资+基本工资实发工资=应发工资水电费房租公积金,项目三 工资表数据分析 任务二 员工计件工资表数据填充,项目实施,由于员工的信息是按收集的先后或者其他顺序录入的。即使出现多条相同数据也很难发现，并且也不方便查找数据信息。处理“员工工资总表”工作表中的数据，将同一车间的员工放在一起，按“实发工资”进一步排序。,项目三 工资表数据分析 任务三 数据排序,项目实施,Excel中数据筛选包括数据的自动筛选和高级筛选两种。自动筛选提供了快速访问数据的功能，通过简单的操作，用户就可以筛选掉那些不想显示或不想打印的数据。1.自动筛选在“员工工资总表”中，筛选出“实发工资”在10001500元的员工。2.高级筛选,项目三 工资表数据分析 任务四 数据筛选,项目实施,2.高级筛选相对于自动筛选，高级筛选可以根据复杂条件进行筛选，而且还可以把筛选的结果复制到指定的地方，更方便进行对比。(1)“与”关系多条件筛选筛选出“实发工资”位于10001500元的员工信息。(2)“或”关系多条件筛选筛选实发工资2500元或实发工资1500元的员工信息。,项目三 工资表数据分析 任务四 数据筛选,项目实施,在数据表格中只知道筛选和排序是不够的。分类汇总是一种很重要的操作，是分析数据的一项有力的工具。分类汇总能对工作表中的某一项数据进行分类，再对需要汇总的数据进行汇总计算。汇总方式包括求和、计数、求平均值、求最大值和求最小值等。1.简单的分类汇总求各类产品名称生产数量的总和2.分级显示数据3.创建多级分类汇总4.清除分类汇总,项目三 工资表数据分析 任务五 按“产品名称”分类汇总,项目实施,数据透视表是一种交互式工作表，可以快速合并和比较大量数据，可以旋转数据表的行和列，并从不同的角度对源数据进行各种汇总，可以显示感兴趣区域的明细数据，还可以按不同的需要、按照不同的关系来提取和组织数据。使用数据透视表，统计各车间各产品的生产量。,项目三 工资表数据分析任务六 使用数据透视表统计各车间各产品的生产量,项目实施,Excel工作表中的数据往往看起来不够直观明了，有时需要对多组数据进行对比、分析，借助图表功能，将表中数据按照需要生成某种类型的图表，利用图表的直观性，用户可以很容易地发现数据的某些关系、信息或规律。 用图表显示各车间生产各类产品的数量。,项目三 工资表数据分析 任务七 制作产品统计分析图,项目实施,通过合并计算可以对来自一个或多个源区域的数据进行汇总，并建立合并计算表。这些源区域与合并计算表可以在同一个工作表中，也可以在同一个工作簿中的不同工作表中，还可以在不同的工作表中。,项目三 工资表数据分析,拓展提高合并计算,打开“抽样调查(素材)”文件，利用给定工作簿中的数据，按照要求进行操作。操作要求见课本P165。,项目三 工资表数据分析,自主训练,