计算机导论 课件 ch06 电子表格处理 (Excel)

电子表格处理（Excel）第六章计算机导论职业教育计算机类专业系列教材01表格创建及编辑PARTONEExcel2010窗口的组成工作簿是Excel中用于保存数据文件，每个工作簿可以有多张不同类型的工作表，默认包含3张工作表，最大支持255张工作表。工作表类似人们日常生活中的应用表格，由行和列构成。单道批处理系统Excel2010启动成功后，屏幕上显示Excel2010窗口。脱机I/O的工作模式工作簿、工作表在Excel中，一个Excel文件就是一个工作簿，一个工作簿由多张工作表组成，一张工作表由若干个单元格组成。单元格是组成工作簿的最小单位。工作簿与工作表之间的关系类似于财务工作中的账簿和账页的关系。工作簿、工作表和单元格是Excel中的重要概念。工作簿是指用来存储并处理工作数据的文件。当创建一个新的工作簿文件时，系统默认由3张工作表组成，用户可以根据需要增减工作表的数量。单元格工作表中行列交叉处的长方形格子称为单元格。单元格是工作表中用于存储数据的基本单位，可以存储Excel2010应用程序所允许的任意类型的数据。Excel的所有数据只能放入单元格内。每个单元格均有一个固定的地址，地址编号由列号和行号组成，如A1、B2等。工作表中当前正使用的单元格称为活动单元格。活动单元格的标志是其四周加有主题色的粗线边框。工作表中的行号、列号和活动单元格工作表的操作工作表的操作包括新建、重命名以及工作表之间的复制、移动、插入、删除等。新建工作表创建工作簿以后，如果3张工作表不能满足操作要求，可以通过增加工作表的方式向工作簿添加新的工作表。添加工作表的方法有两种：01使用【Shift+F11】组合键，可以在当前工作表左边插入一张新的工作表。02使用右键快捷菜单新建工作表。右击任意工作表标签，在弹出的快捷菜单中选择“插入”命令，弹出“插入”对话框，在“常用”选项卡中选择“工作表”选项，单击“确定”按钮，可在选中工作表的左边插入一张新的工作表，并且插入的工作表将变为活动工作表。操作系统的发展工作表的其他操作用户可根据实际需要对同一工作簿中的工作表进行选中、移动和复制、删除和重命名等操作。在对工作表进行编辑之前应先选中工作表，使该工作表成为活动工作表。选中工作表的方法主要有以下几种：01选中单张工作表直接单击工作表标签即可选中一张工作表。02选中多张连续工作表选中第一张工作表的标签，然后按住【Shif】键并单击最后一张工作表标签，可以同时选中这两张工作表之间的所有工作表。操作系统的发展03选中多张不连续的工作表按住【Ctrl】键并依次单击需要选中的不相邻的多张工作表标签。04选选中工作簿中全部的工作表在任意一个工作表标签上右击，在弹击的快捷菜单中选择“选定全部工作表”命令即可。工作表的其他操作用户可根据实际需要对同一工作簿中的工作表进行选中、移动和复制、删除和重命名等操作。在对工作表进行编辑之前应先选中工作表，使该工作表成为活动工作表。选中工作表的方法主要有以下几种：Excel的数据类型单元格中的数据可以设置为数字、货币、时间、百分比、分数、科学计数、文本等类型，Excel也可以根据输入数据自动设置为近似的数据类型。另外，Excel还提供了快速填充功能。基本数据类型01文本型文本型数据可以包括字母、数字和其他特殊字符的任意组合。对于电话号码、邮政编码等数据也常作为字符处理，此时只需在输入数字之前加上一个英文状态下的单引号，Excel就会把它当作文本型数据处理。02数字型数字型数据具有数学意义上的大小，除包括0～9数字，还包括+、-、E、e、%及小数点（.）和千分位符号（，）等特殊字符，它们的组合必须符合数学规定。当输入的数据长度超过单元格宽度时，Excel自动以科学计数法表示。Excel的数据类型03日期时间型日期时间型数据是表示日期和时间的数据，按日期和时间的表示方法输入即可。输入日期时，用“-”或“/”分隔日期的年、月、日。输入时间时应用“：”分隔，Excel默认以24小时制计时，若想采用12小时制，时间后带AM或PM。例如，1999-10-05、1999-12-07、1999/05/08、13：20：15、8：23：15AM等为正确的日期时间型数据。若要输入当天的日期，可使用快捷键【Ctl+；】；若要输入当前时间，可使用快捷键【Ctrl+Shift+；】。基本数据类型04逻辑型逻辑型数据是表示真假的数据，包括表示逻辑真假的TRUE和FALSE。Excel的数据类型自动填充数据Excel可以对一些规律性的数据进行自动填充，如等差数列、等比数列、具有时序性的日期和时间序列、规律变化的文本型数据等。例如，利用自动填充输入学号序列的操作步骤如下：01选中填充区域的起始单元格，输入序列的初始值（如输入2013021001）并按【Enter】键。02移动鼠标指针到初始值单元格右下角的填充柄（鼠标指针呈十字形“+”）。03按住鼠标左键拖曳填充柄经过待填充的区域。04释放鼠标左键，序列数据按规律被填充到鼠标经过的各单元格。数据编辑数据编辑包括移动、复制、删除数据，查找和替换数据等操作。单元格格式化行高、列宽、显示和隐藏行高是指工作表每一行的高度，各行的高度可以自由调整；列宽是指每一列的宽度，各列的宽度也可以根据需要调整。通常会采用以下三种方法调整行高或列宽：01使用对话框设置行高和列宽选中相应的列或行，单击“开始”菜单“单元格”组中的“格式”下拉按钮，在弹出的下拉菜单中选择“行高”或“列宽”命令，在弹出的“行高”或“列宽”对话框中可输入适当的数值来调整行高和列宽。02自动调整行高和列宽选中相应的列或行，然后单击“开始”菜单“单元格”组中的“格式”下拉按钮，在弹出的下拉菜单中选择“自动调整行高”或“自动调整列宽”命令即可。单元格格式化03拖曳鼠标调整行高和列宽以调整行高为例，将鼠标指针移到需要调整行高的行的分隔线上，当其变为双向对拉箭头形状时，双击即可根据单元格的内容自动调整行高；或者在鼠标指针变为双向对拉箭头形状时，按住鼠标左键进行拖曳，此时会在屏幕上出现当前行高提示条，可以根据提示的数值确定需要的准确行高，当达到适当的高度时释放鼠标即可。列宽的调整与行高的调整方法相似。行高、列宽、显示和隐藏行高是指工作表每一行的高度，各行的高度可以自由调整；列宽是指每一列的宽度，各列的宽度也可以根据需要调整。通常会采用以下三种方法调整行高或列宽：单元格格式化Excel中的表格线是虚拟的，为了让打印出的表格有表格线，需为选中的单元格或单元格区域加上边框线，从而使表格的内容更清晰。边框线可以通过“设置单元格格式”对话框的“边框”选项卡添加。“边框”选项卡的内容如下：边框01线条在“样式”列表框中有多种可供选择的线条样式，默认为“实细线条”。颜色默认为“自动”，可根据需要选择颜色。02预置其中有三个按钮，即无、外边框、内部，单击相应按钮后在“边框”选项组中可预览效果，若要取消所设置的边框，可单击“无”按钮。03边框其中有多个边框按钮，单击不同的边框按钮可以为表格设置所需的边框。单元格格式化如果用户认为工作表中的白色背景太单调，可以为其添加背景颜色及图案。使用“设置单元格格式”对话框中的“填充”选项卡即可进行表格的背景颜色或者背景图案的设置。背景单元格条件格式化用户面对电子表格中的黑白数字难免会感到枯燥，如果能为工作表中不同类型的数据添加不同的颜色，则不仅让工作表看上去更有活力，而且可使统计数据更直观。在Excel中使用条件格式功能就可突出显示所关注的单元格或单元格区域，更直观地显示相应数据。01设置条件格式条件格式是指当指定条件为真时，Excel自动应用格式于单元格，即规定单元格中的数据在满足某一事先设定的条件时，用该条件对应的格式显示数据。02更改条件格式设置了条件格式后还可以对其进行更改，或者再添加其他条件格式来显示数据，Excel2010的条件格式可以添加多个条件。03清除条件格式单击“开始”菜单“样式”组中的“条件格式”下拉按钮，在弹出的下拉菜单中选择“清除规则”命令，即可删除条件格式。公式和函数的使用Excel通过数学公式或者函数对单元格中的数据进行运算。在Excel中，计算公式均以“=”开头。计算公式中可以包含四则运算符、常量数值、单元格引用、单元格区域引用和Excel内嵌函数等，如“=C3+D3+E3+F3”。地址引用Excel公式中经常要引用单元格。单元格引用通过引用单元格地址完成，称为单元格地址引用。单元格地址引用分为相对引用和绝对引用两种。01相对引用相对引用是Excel中最常见的一种引用方式。相对引用引用的单元格相对于创建和使用公式的单元格的位置而言是变化的，其变化体现在使用相对引用公式所在的单元格位置发生变化或者公式被复制到其他单元格，其引用的单元格也会随之变化。例如，公式“=A2*B1”中A2与B1表示相对引用。公式和函数的使用02绝对引用如果希望使用公式的单元格的位置发生变化或者公式被复制后计算结果保持不变，则需要采用绝对引用。在引用单元格的列号和行号之前分别加入符号“$”便为绝对引用。例如，公式“=$A$2*B1”中$A$2为绝对引用。地址引用03混合引用在公式中同时使用相对引用和绝对引用，称为混合引用。例如，公式“=$A2*B$1”为混合引用。$A2表示行不变，而列号采用相对引用；B$1则表示行号变动时列号绝对不变。04引用同一工作簿中不同工作表中的单元格在使用表格的过程中，有时需要引用同一工作簿中不同工作表的单元格或单元格区域中的数据，可以方便以后统计不同工作表中的数据。这种引用的格式为“工作表名称!单元格地址”。例如，“=Sheet1!A1+Sheet2!A1+Sheet3!A1”，表示计算工作表Sheet1到Sheet3中所有A1单元格中值的和，也可以用求和函数SUM（）公式“=SUM（Sheet1：Sheet3!A1）”替代。地址引用05引用其他工作簿中的单元格如果引用其他工作簿中工作表的单元格，采用的格式为“’工作簿存储地址[工作簿名称]工作表名称’!单元格地址”。例如，“=SUM（C：\MyDocuments\[Book2.xlsx]Sheet1：Sheet3‘!A3）”，表示计算C盘MyDocuments文件夹中的工作簿Book2中Sheet1到Sheet3中所有A3单元格中值的和。公式和函数的使用函数的基本格式：函数名（参数1，参数2，…，参数n）。其中，函数名是每个函数的唯一标识，它决定了函数的功能和用途。参数是函数运算所需要的、可以变化的量。

一个函数中包含的多个参数，称为参数列表。参数用圆括号括起来，参数和参数之间以逗号分隔。函数的参数可以是数字、文本、逻辑值、单元格引用、名称等，也可以是公式或函数。例如，求和函数SUM的格式是SUM（n1，n2，…），其功能是对所有参数n1，n2…的值求和，SUM为函数名。Excel的常用函数公式和函数的使用制作学生学籍表和成绩表制作学生学籍表01制作标题启动Excel2010，新建工作簿，在工作簿的当前工作表中选中A1：G1单元格区域，单击“开始”菜单“对齐方式”组中的“合并后居中”按钮，输入标题“学生学籍表”；设置字体格式为“华文中宋、16磅”，单击“字体”组中的“填充颜色”下拉按钮，在弹出的下拉菜单中选择黄色，再单击“字体颜色”下拉按钮，在弹出的下拉菜单中选择红色。02在A2～G2单元格依次输入表头文字内容，选中A2：G2区域，单击“开始”菜单“对齐方式”组中的“居中”按钮，并在“字体”组中设置字体格式为“黑体、14磅、加粗”。03填充输入“学号”列数据选中A3：A23区域，打开“设置单元格格式”对话框，选择“数据”选项卡，在“分类”列表框中选择“文本”选项。在A3单元格输入起始数据2013021001，再将鼠标指针移至填充柄，当指针变为十字形时，按住鼠标左键，拖曳至所需位置后释放鼠标即可。制作学生学籍表和成绩表制作学生学籍表04输入其他各列数据“姓名”“性别”“籍贯”列的数据内容直接输入即可。设置“出生日期”列数据类型为日期型：先选中E3：E23区域，打开“设置单元格格式”对话框，选择“数据”选项卡，在“分类”列表框中选择“日期”选项，再从“类型”列表框中选择日期格式，然后依次输入出生日期数据。输入“入学成绩”数据，再选中F3：F23区域，设置数据类型为数值型，小数位数为1位。设置“联系电话”列的数据类型为文本型，并且输入联系电话数据。05选中A2：G23区域，单击“开始”菜单“字体”组中的“边框”下拉按钮，选择其下拉菜单中的“所有框线”命令，即给所有单元格都加上边框线。06保存工作簿，命名为“学生学籍表”。制作学生学籍表和成绩表学生成绩表制作01启动Excel2010新建工作簿，右击工作表标签，在弹出的快捷菜单中选择“插入”命令，弹出“插入”对话框，选择“工作表”选项，新建两张工作表。右击工作表标签Sheet1，在弹出的快捷菜单中选择“重命名”命令，此时该标签处于编辑状态（黑底白字），输入“第一学期”，然后单击空白单元格，完成重命名。用同样的方法将Sheet2更名为“第二学期”，Sheet3更名为“第三学期”，Sheet4更名为“第四学期”，Sheet5更名为“成绩汇总”。02选中“第一学期”工作表作为当前工作表，再选中A1：J1单元格，单击“合并后居中”按钮，输入文字“学生成绩表”；字体格式设置为“华文中宋、16磅”；在A2：J2中依次输入表头数据，字体设置为“黑体、加粗、14磅”，对齐方式设置为居中。03给数据区域A2：J23加上边框。制作学生学籍表和成绩表学生成绩表制作06保存工作簿，命名为“学生成绩表”。04选中D3：F23区域，单击“开始”菜单“样式”组中的“条件格式”下拉按钮，在弹出的下拉菜单中选择“突出显示单元格规则”→“小于”命令，在弹出的“小于”对话框中输入60，在“设置为”下拉列表框中选择“自定义格式”选项，弹出“设置单元格格式”对话框，设置加粗标准色为红色。05选中G3单元格，在编辑栏中输入公式“=AVERAGE（D3：F3）”，再用填充柄功能向下填充，得到所有学生的平均分；选中H3单元格，在编辑栏中输入公式“=SUM（D3：F3）”，

再用填充柄功能向下填充，得到所有学生的总分；选中I3单元格，在编辑栏中输入公式“=RANK（H3，H$3：H$23）”，再用填充柄功能向下填充，得到所有学生的名次；选中J3单元格，在编辑栏中输入公式“=IF（G3>=85，“一级”，IF（G3>80，“二级”，IF（G3>75，“三级”，“继续努力”）））”，再用填充柄功能向下填充，得到所有学生的奖励等级的值；选中D26单元格，在编辑栏中输入公式“=COUNTIF（D3：D23，“>=90”）/COUNTA（D3：D23）”，再用填充柄功能向右填充“英语”和“体育”的优秀率。2PARTTWO图表及数据透视表图表Excel提供了丰富的图表功能，为用户提供更直观、全面的图形数据显示效果。Excel的图表类型可以分为标准类型与自定义类型两大类，其中标准类型包括柱形图、条形图、折线图、饼图、XY（散点图）、面积图、圆环图、雷达图、曲面图、气泡图、股价图。其中，每种标准图表类型都包括多种不同形式的图表。不同类型的图表所表达的意义也不尽相同，如折线图表示趋势走向，柱形图强调数量的差异。生成图表时，用户根据自己的需求进行选择即可。当工作表中的数据源发生变化时，图表中对应项的数据也自动更新。图表创建图表Excel中的图表分为两种形式：一种是嵌入式图表，它和创建图表的数据源放置在同一张工作表中，打印时，数据和图表同时打印；另一种是独立图表，它是一张独立的图表工作表，打印时与数据表分开打印。利用图表工具或直接按【F11】键都可以生成图表。利用图表工具生成图表的操作步骤如下。Excel提供的11种标准图标类型格式化图表图表建立完成后，如果显示的效果不太美观，可以对图表的外观进行适当的格式化，即对图表的各个对象进行修饰，使其协调、美观。图表包括图表区、绘图区、图表标题、数值轴标题、分类轴标题、图例等。使用“图表工具-格式”或“图表工具-布局”快速工具栏能够完成图表的格式化设置。图表组成图表修改图表若用户发现生成图表时所选择的图表类型不合适，或者所选取的数据区域不对，都可以修改图表。以更改图表类型为例，单击“图表工具-设计”快速工具栏“类型”组中的“更改图表类型”按钮，弹出“更改图表类型”对话框，选择一个新的图表类型即可。图表数据透视表和数据透视图创建数据透视表单击“插入”菜单“表格”组中的“数据透视表”下拉按钮，在弹出的下拉菜单中选择“数据透视表”命令，在弹出的“创建数据透视表”对话框中选择数据区域，单击“确定”按钮，弹出“数据透视表字段列表”任务窗格，在“选择要添加到报表的字段”列表框中，将相应字段拖到列标签、行标签、数值栏，完成对数据透视表的创建操作。编辑数据透视表在创建了数据透视表后，由于统计数据随时更新，这时就需要更改数据透视表的数据源等，即对数据透视表进行编辑。创建数据透视表不需要数据透视表或数据透视图时，可以将其删除，不会影响相关联的数据。数据透视表作为一张独立的工作表，删除时就像删除工作表一样，右击其标签名，在弹出的快捷菜单中选择“删除”命令即可。删除数据透视表的方法：选中需要删除的数据透视表覆盖的全部数据单元格，按【Delete】键即可。制作图表创建成绩优秀率比较图表01打开“学生成绩表”工作簿，选择“第一学期”工作表。02选中D25：F26区域，单击“插入”菜单“图表”组中的“柱形图”下拉按钮，在弹出的下拉菜单中选择“簇状柱形图”命令。03单击“图表工具-布局”快速工具栏“标签”组中的“图表标题”下拉按钮，设置图表标题为“成绩优秀率比较图”，单击“图例”下拉按钮，设置图例。04选择“图表工具-格式”快速工具栏，分别选择数据系列和图表区，在“形状填充”下拉菜单中选择填充颜色为深紫色，并保存当前工作簿。制作图表创建数据透视表选中B2：F23区域，单击“插入”菜单“表格”组中的“数据透视表”下拉按钮，在弹出的下拉菜单中选择“数据透视表”命令，弹出“创建数据透视表”对话框，选择要分析的数据，设置数据透视表的位置，单击“确定”按钮，弹出“数据透视表字段列表”任务窗格，在“选择要添加到报表的字段”列表框中将“性别”拖到“行标签”列表框，“英语”“数学”拖到“数值”列表框，并将求和项修改为平均值。3PARTTHREE数据处理数据排序按单一条件筒单排序单一条件简单排序的方法：光标定位在要排序列的某个单元格中，单击“数据”菜单“排序和筛选”组中的“升序”按钮或“降序”按钮，或者单击“开始”菜单“编辑”组中的“排序和筛选”下拉按钮，在弹出的快捷菜单中选择“升序”或“降序”命令，可以对光标所在的列进行排序，其他数据按照排序列自动调整。数据排序在排序时，可以指定多个排序条件，即多个排序的关键字。首先单击“数据”菜单中的“排序”按钮，弹出“排序”对话框。在默认条件中只有一个主要关键字，即选择的第一个排序关键字。单击“添加条件”按钮，可以增加若干个次要关键字，次要关键字按照其添加次序排序。排序时，如果主要关键字的值相同，则按照第一个次要关键字的值排序，依此类推。多重排序“排序”对话框数据筛选自动筛选一般用于条件简单的筛选操作，符合条件的记录显示在原来的数据表格中。在工作表中选中单元格，单击“数据”菜单“排序与筛选”组中的“筛选”按钮，执行自动筛选功能，则在工作表的列标题名的右侧出现下拉按钮。单击该按钮在下拉列表中选择“数字筛选”→“自定义筛选”命令，系统弹出“自定义自动筛选方式”对话框，根据实际情况设置筛选条件，筛选需要的数据。自动筛选选择“自定义筛选”命令“自定义自动筛选方式”对话框数据筛选使用高级筛选时必须在工作表中构造条件区域，该区域由条件标记和条件值构成。条件标记和数据区域的列标记相同，可以直接从数据区域中复制；条件值则需要根据筛选要求在条件标记下方构造，是执行高级筛选的关键部分。构造高级筛选的条件区域时需要注意：如果条件区域放在数据区域的下方，那么两者之间至少应有一个空白行；如果条件区域放在数据区域的上方，则数据区域和条件区域之间也应间隔一个或多个空白行，但一般不这样设计，以免影响数据区域的使用。高级筛选数据筛选高级筛选01单列多条件如果某个条件标记下面输入了两个或多个筛选条件，则将其称为单列多条件，此时只要在条件标记下自上而下依次输入筛选条件即可。02多列单条件多列单条件是指筛选条件由多个标记构成，每个条件标记下面只有一个筛选条件。03多行单条件多行单条件的构造要求：如果多个筛选条件需要同时满足，则必须分布于条件区域的同一行，这就是所谓的“与”条件；否则筛选条件必须分布于条件区域的不同行，即“或”条件。分类汇总Excel提供的分类汇总功能可以对某字段具有相同性质的数据进行求和、计数、求平均值等计算。分类汇总时，必须先按所依据的分类字段排序，再对此字段同类的数据进行统计运算。创建分类汇总后，还可隐藏或显示分类汇总数据。若要进行隐藏或显示数据，可以单击工作簿左上角不同级别的三个分类汇总按钮。学生成绩表的数据处理启动Excel，打开“学生成绩表”工作簿文件，在“第一学期”工作表中选择除标题外的单元格区域（A2：J23），单击“数据”菜单“排序和筛选”组中的“排序”按钮，弹出“排序”对话框，设置主要关键字为“数学”，降序排列。排序自动筛选01在“第一学期”工作表中选中“总分”列（H2：H23），单击“数据”菜单“排序和筛选”组中的“筛选”按钮，单击“总分”列的下拉按钮，选择“数字筛选”→“自定义筛选”命令，系统弹出“自定义自动筛选方式”对话框，设置第一个条件为“大于”，值为“250”，

第二个条件为“小于”，值为“200”，两个条件的连接关系选择“或”。02复制已筛选出的数据，插入一张新工作表，粘贴筛选数据，将工作表重命名为“250分以上或200分以下的学生”。学生成绩表的数据处理高级筛选在“第二学期”工作表中输入数据。首先在D26：F27单元格区域输入筛选条件；然后单击“数据”菜单“排序和筛选”组中的“高级”按钮，系统弹出“高级筛选”对话框，分别指定列表区域、条件区域和结果显示位置；