第3章通过Excel创建并处理电子表格PPT课件

1、NCRE第3 3章 通过 EXCEL EXCEL 创建并处理 电子表格2课程简介n通过电子表格软件进行数据的管理与分析与已成为人们当前学习和工作的必备技能之一。nExcel 是一款目前相当流行、应用广泛的电子表格软件，它是由微软开发、属于 MS OFFICE 套装办公软件中的主要组件之一。n本教材以 Excel 2010 为蓝本，学习 Excel 的重要功能及应用。第 3 章 通 过 E X C E L 创 建 并 处 理 电 子 表 格3课程学习内容u 创建基本电子表格，在表格中输入各类数据。u 对数据及表格结构进行格式化，使之更加美观。u 对工作表及工作簿进行各类操作。u 在工作表中使用公

2、式和函数简化计算。u 对数据进行各种汇总、统计分析和处理u 运用图表对数据进行分析。u 通过宏快速重复执行多个任务u 在不同的人员和程序间共享数据。第 3 章 通 过 E X C E L 创 建 并 处 理 电 子 表 格4第3章 通过 EXCEL 创建并处理电子表格3.1 Excel 制表基础3.2 工作簿与多工作表操作3.3 Excel 公式和函数3.4 在 Excel 中创建图表3.5 Excel 数据分析与处理3.6 Excel 与其他程序的协同与共享第 3 章 通 过 E X C E L 创 建 并 处 理 电 子 表 格5本节案例背景：本节案例背景：Excel 最基本的功能就是制作

3、表格，在表格中记录相关数据，以便日常生活和工作中信息的记录、查询与管理。书娟是一位年青的女性，她刚刚结婚，要负责家庭财政收支；本节将通过Excel制作家庭收支流水账这一简单案例，帮助书娟实现以下基本目标：了解Excel工作环境中的常用术语掌握在Excel中输入数据的各类方法掌握数据及表格结构的格式化方法掌握打印输出表格的方法。了解公式的简单应用3.1 EXCEL制表基础第 3 章 通 过 E X C E L 创 建 并 处 理 电 子 表 格63.1.1 在表格中输入和编辑数据1. Excel表格术语表格术语通过桌面快捷方式、“开始”菜单等途径，均可启动Excel。Excel的窗口界面有别于其

4、他程序（如图3.2所示），必须掌握一些 Excel 特有的常用术语含义及其作用： 工作簿与工作表 工作表标签 行号和列标 单元格、单元格地址与活动单元格 名称框 编辑栏第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础73.1.1 在表格中输入和编辑数据图3.2 Excel 2010 的窗口界面第3章 通过EXCEL创建并处理电子表格列 标活动单元格名称框工作表标签编辑栏行 号3.1 Excel 制表基础83.1.1 在表格中输入和编辑数据输入和编辑数据是制作一张表格的起点和基础，在Excel中，可以利用多种方法达到快速输入数据的目的。2. 直接输入各种类型的数据直接输入各种

5、类型的数据在Excel中，可以输入数值、文本、日期等各种类型的数据。输入数据的基本方法：在需要输入数据的单元格中单击鼠标，输入数据，然后按回车键 Enter、Tab 或方向键。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础93.1.1 在表格中输入和编辑数据按照图3.3所示为创建一个家庭收支情况表输入基础数据：图3.3 在表格中输入基础数据第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础103.1.1 在表格中输入和编辑数据具体操作步骤： 启动Excel，窗口中自动打开一个空白工作簿。 在A1单元格中单击鼠标，输入标题文字“家庭收支流水账”按回车键En

6、ter，光标自动向下跳到B1单元格。 从B1单元格从左向右依次输入表格的列标题：序号、发生日期、摘要、收入金额、支出金额、余额。 在“序号”所在A列的A2单元格中输入第一个序号“01”。注意：在数字前需要先行输入一个西文单撇号“”，将其指定为文本格式，才能正确显示出数字前面的“0”。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础113.1.1 在表格中输入和编辑数据 在“发生日期”所在B列的B2单元格中输入第一个日期“2/1”，以斜杠“/”分隔月日，按回车键Enter确认，单元格中自动显示“2月1日”。提示：Excel提供多种日期显示格式，其具体的设置方式可参见后面的设置

7、数据格式中的介绍。 在“余额”所在F列的F2单元格中输入第一个计算公式：“=D3-E3”(余额收入-支出)，按回车键Enter确认。 依次输入摘要及收入金额。 对工作簿进行保存。Excel2010 版本默认的文件名后缀为 .xlsx，还可以将其保存为其他类型，如存为 Excel 97-2003 工作簿格式（.xls）等。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础123.1.1 在表格中输入和编辑数据3. 向单元格中自动填充数据向单元格中自动填充数据序列填充是Excel提供的最常用的快速输入技术之一。1）序列填充的基本方法）序列填充的基本方法 拖动填充柄 使用“填充”命

8、令 利用鼠标右键快捷菜单第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础133.1.1 在表格中输入和编辑数据2）可以填充的内置序列）可以填充的内置序列数字序列，如1、2、3、；2、4、6、日期序列，如2011年、2012年、2013年；1月、2月、3月；1日、2日、3日文本序列，如01、02、03、； 一、二、三其他Excel内置序列：如英文日期JAN、FEB、MAR；星期序列星期日、星期一、星期二，子、丑、寅、卯第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础143.1.1 在表格中输入和编辑数据3）填充公式首先在第一个单元格中输入某个公式，然后拖动

9、该单元格的填充柄，即可填充公式本身而不仅仅是填充公式计算结果。4）自定义常用序列如“第一小组、第二小组、第三小组、第四小组” 基于已有项目列表的自定义填充序列要点：首先在工作表的单元格依次输入一个序列的每个项目值方法：“文件”选项卡“选项”“高级” “常规” “编辑自定义列表” “自定义序列”对话框第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础153.1.1 在表格中输入和编辑数据删除自定义序列在“自定义序列”对话框的左侧列表中选择需要删除的序列，单击右侧的“删除”按钮。直接定义新项目列表依次单击“文件”选项卡“选项”“高级”，在“常规”区中单击“编辑自定义列表”按钮，在

10、“自定义序列”对话框中单击“新序列”，然后在右侧的“输入序列”文本框中依次输入序列的各个条目：从第一个条目开始输入，每个条目后按Enter键确认。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础163.1.1 在表格中输入和编辑数据4）完成案例中序号和日期的填充）完成案例中序号和日期的填充打开案例工作簿文档“3.1 家庭收支流水账（1）”，完成日期和序号的填充。小技巧：输入序列的第一个项目后，用鼠标直接双击填充柄，序号将自动向下填充到相邻列最后一个数据所在的行。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础173.1.1 在表格中输入和编辑数据4. 控

11、制数据的有效性控制数据的有效性 数据有效性，用于定义可以在单元格中输入或应该在单元格中输入的数据类型、范围、格式等。可以通过配置数据有效性以防止输入无效数据，或者在录入无效数据时自动发出警告。数据有效性可以实现的基本功能：将数据输入限制为指定序列的值，以实现大量数据的快速输入。将数据输入限制为指定的数值范围，如指定最大值最小值、指定整数、指定小数、限制为某时段内的日期、限制为某时段内的时间等。将数据输入限制为指定长度的文本，如身份证号只能是18位文本。限制重复数据的出现，如学生的学号不能相同。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础183.1.1 在表格中输入和编辑

12、数据设置数据有效性的基本方法：设置数据有效性的基本方法：在“数据”选项卡上的“数据工具”组中单击“数据有效性”按钮，从随后弹出的“数据有效性”对话框中指定各种数据有效性控制条件即可。 案例案例：打开案例工作簿文档“3.1家庭收支流水账（2）”，对其中的摘要列设置数据有效性，目的是可以通过下拉列表方式选择摘要内容，摘要栏中允许输入的项目包括：工资奖金、房租、水电气、日常生活费、学习用品、服装鞋帽。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础193.1.1 在表格中输入和编辑数据5. 对数据进行编辑修改对数据进行编辑修改基本方法：双击单元格进入编辑状态，进行修改；或者单击要

13、修改的单元格，然后在编辑栏中进行修改。删除数据：按Delete/Del键；或者在“开始”选项卡上的“编辑”组中，单击“清除”，可指定删除格式还是内容。继续对案例工作簿“3.1 家庭收支流水账（2）”进行修改：将“摘要”改为“收支摘要”二字；将F3单元格中的错误公式删除。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础203.1.2 对表格进行基本整理和修饰为对让表格看起来更加漂亮，也为了增加易读性，需要对输入了数据的表格进行格式化。格式化内容包括：行列操作、字体设置、对齐方式、数字格式、单元格边框和底纹。1. 选择单元格或区域选择单元格或区域在对表格进行修饰前，需要先选择单

14、元格或单元格区域做为修饰对象。在Excel中，选择单元格或区域的方法多种多样，常用快捷方法见表3.1中所列。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础21操操 作作常用快捷方法常用快捷方法选择单元格用鼠标单击单元格选择整行单击行号选择一行；用鼠标在行号上拖动选择连续多行；按下Ctrl键单击行号选择不相邻多行选择整列单击列标选择一列；用鼠标在列标上拖动选择连续多列；按下Ctrl键单击列标选择不相邻多列选择一个区域 在起始单元格中单击鼠标，按下左键不放拖动鼠标选择一个区域； 按住Shift的同时按箭头键以扩展选定区域； 单击该区域中的第一个单元格，然后在按住Shift的同

15、时单击该区域中的最后一个单元格选择不相邻区域先选择一个单元格或区域，然后按下Ctrl键不放选择其他不相邻区域选择整个表格单击表格左上角的“全选”按钮 ，或者在空白区域中按下Ctrl+A组合键选择有数据的区域 按Ctrl+箭头键可移动光标到工作表中当前数据区域的边缘； 按Shift+箭头键可将单元格的选定范围向指定方向扩大一个单元格； 在数据区域中按下Ctrl+A或者Ctrl+Shift+*组合键，选择当前连续的数据区域； 按Ctrl+Shift+箭头键可将单元格的选定范围扩展到活动单元格所在列或行中的最后一个非空单元格，或者如果下一个单元格为空，则将选定范围扩展到下一个非空单元格表 3.1 选

16、择单元格及区域的方法第 3 章 通 过 E X C E L 创 建 并 处 理 电 子 表 格3.1 Excel 制表基础223.1.2 对表格进行基本整理和修饰2. 行列操作行列操作调整行高、列宽插入行或列删除行或列移动行或列隐藏行或列其基本方法如表 3.2 中所列。行列操作可以通过鼠标，也可以通过菜单实现。提示：以上各项功能（除移动行列外）还可以通过鼠标右键快捷菜单实现：在单元格或行列上单击右键，从弹出的快捷菜单中选择相应的命令即可。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础23行列操作行列操作基本方法基本方法调整行高用鼠标拖动行号的下边线；或者依次选择“开始”选

17、项卡“单元格”组中的“格式”下拉列表“行高”命令，在对话框中输入精确值。调整列宽用鼠标拖动列标的右边线；或者依次选择“开始”选项卡“单元格”组中的“格式”下拉列表“列宽”命令，在对话框中输入精确值。隐藏行用鼠标拖动行号的下边线与上边线重合；或者或者依次选择“开始”选项卡“单元格”组中的“格式”下拉列表“隐藏和取消隐藏”“隐藏行”命令隐藏列用鼠标拖动列标的右边线与左边线重合；或者依次选择“开始”选项卡“单元格”组中的“格式”下拉列表“隐藏和取消隐藏”“隐藏列”命令插入行依次选择“开始”选项卡“单元格”组中的“插入”下拉列表“插入工作表行”命令，将在当前行上方插入一个空行插入列依次选择“开始”选项

18、卡“单元格”组中的“插入”下拉列表“插入工作表列”命令，将在当前列左侧插入一个空行删除行或列选择要删除的行或列，在“开始”选项卡的“单元格”组中单击“删除”按钮移动行列选择要移动的行或列，将鼠标光标指向所选行或列的边线，当光标变为 状时，按下左键拖动鼠标即可实现行或列的移动。表 3.2 行列操作方法第 3 章 通 过 E X C E L 创 建 并 处 理 电 子 表 格3.1 Excel 制表基础243.1.2 对表格进行基本整理和修饰3. 设置字体及对齐方式设置字体及对齐方式设置字体与字号： 单击“文件”选项卡上的“字体”组中相应按钮。 单击“数字”右侧的对话框启动器，打开的“设置单元格格

19、式”对话框的“字体”选项卡，进行详细设置。设置对齐方式 单击“文件”选项卡上的“对齐方式”组中相应按钮。 单击“对齐方式”右侧的对话框启动器，打开“设置单元格格式”对话框的“对齐”选项卡，进行详细设置。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础253.1.2 对表格进行基本整理和修饰4. 设置数字格式设置数字格式数字格式是指表格中数字的外观形式。通常情况下，输入单元格中的数据是未经格式化的，尽管Excel会尽量将其显示为最接近的格式，但并不能满足所有需求。通常来说，需要对数据进行数字格式设置，这样不仅美观，而且更便于阅读，或者使其显示的精度更高。例如，当试图在单元格中

20、输入一个人的18位身份证号位身份证号时，你可能会发现直接输入一串数字后结果是不对的，这时就需要通过数字格式的设置才能正确显示。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础263.1.2 对表格进行基本整理和修饰1）可供）可供选择的数字格式选择的数字格式：常 规：默认格式。数 值货 币会计专用日 期时 间百分比分 数科学记数：用指数符号(E)显示数字: 2.00E+05=200 000文本：主要用于设置那些表面看来是数字，但实际是文本的数据。例如序号001、002，就需要设置为文本格式才能正确显示出前面的零。特殊：包括邮政编码、中文小写数字和中文大写数字。自定义第3章 通

21、过EXCEL创建并处理电子表格3.1 Excel 制表基础273.1.2 对表格进行基本整理和修饰2）设置数字格式的基本方法）设置数字格式的基本方法通过“文件”选项卡上“数字”组中的相应按钮快速设置。单击“数字”组右侧的对话框启动器，在“设置单元格格式”对话框的“数字”选项卡中，进行更加详细的设置或自定义格式。提示：如果一个单元格显示出一连串的#标记，这通常意味着单元格宽度不够，无法显示全部数据长度，这时可以加宽该列或改变数字格式。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础283.1.2 对表格进行基本整理和修饰5. 添加边框和底纹添加边框和底纹默认情况下，工作表中的

22、网格线只用于显示，不会被打印。改变边框和底纹的基本方法：在“文件”选项卡上的“字体”组中，单击“边框”按钮，选择边框；单击“填充颜色”按钮，设置背景底纹。“文件”选项卡“单元格”组“格式”按钮“设置单元格格式”命令“设置单元格格式”对话框， “边框”选项卡或“填充”选项卡。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础293.1.2 对表格进行基本整理和修饰6. 完成案例的基础格式设置完成案例的基础格式设置打开案例工作簿文档“3.1家庭收支流水账（3）”。调整行高列宽插入及隐藏行列完成数据的输入设置字体及对齐方式设置数字格式设置边框和底线第3章 通过EXCEL创建并处理电

23、子表格3.1 Excel 制表基础303.1.3 格式化工作表高级技巧除了手动进行各种格式化操作外，除了手动进行各种格式化操作外，Excel还提供有各种自动格式化的高级功能，还提供有各种自动格式化的高级功能，以方便大家快速进行格式化操作以方便大家快速进行格式化操作1. 自动套用格式自动套用格式 Excel本身提供大量预置好的表格格式，可自动实现包括字体大小、填充图案和对齐方式等单元格格式集合的应用，从而快速实现报表格式化。1）指定单元格样式）指定单元格样式该功能只对某个指定的单元格设定预置格式“开始”选项卡“样式”组“单元格样式”按钮从打开预置样式列表中选择某一个预定样式。自定义样式：单击样式

24、列表下方的“新建单元格样式”命令动手操作：打开案例工作簿文档“3.1家庭收支流水账（4）”，试为该表格的标题“家庭收支流水账”所在的单元格套用不同的单元格样式。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础313.1.3 格式化工作表高级技巧2）套用表格格式）套用表格格式 自动套用表格格式，将把格式集合应用到整个数据区域。注意，自动套用格式只能应用于不含合并单元格的数据列表中。“开始”选项卡“样式”组“套用格式”按钮 从预置格式列表中选择某一个预定样式。自定义快速格式：单击预置格式列表下方的“新建表样式”命令。取消套用格式： “表格工具 设计”选项卡“表格样式”组右下角的

25、“其他”箭头 最下方的“清除”命令。打开案例工作簿文档“3.1家庭收支流水账（4）”，试为该表格的数据区域B3:G19套用不同的表格样式，演示不同格式组合效果。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础323.1.3 格式化工作表高级技巧2. 设定与使用主题设定与使用主题 主题是一组格式集合，其中包括主题颜色、主题字体（包括标题字体和正文字体）和主题效果（包括线条和填充效果）等。通过应用文档主题，可以快速设定文档格式基调并使具看起来更加美观且专业。1）使用主题 “页面布局”选项卡“主题”组“主题”按钮选择主题类型。2）自定义主题 “页面布局”选项卡“主题”组“颜色”按

26、钮自行设定颜色组合。 “页面布局”选项卡“主题”组 “字体”按钮自行设定字体组合。 “页面布局”选项卡“主题”组“效果”按钮选择一组主题效果。 保存自定义主题： “页面布局”选项卡“主题”组“主题”按钮从主题列表最下方选择“保存当前主题”命令输入主题名称。提示：新建主题将会显示在主题列表最上面的“自定义”区域以供选用。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础333.1.3 格式化工作表高级技巧3. 条件格式条件格式条件格式将会基于设定的条件来自动更改单元格区域的外观，可以突出显示所关注的单元格或单元格区域、强调异常值、使用数据条、颜色刻度和图标集来直观地显示数据。例

27、如：一份成绩表中谁的成绩最好，谁的成绩最差？不论这份成绩单中有多少人，利用条件格式都可以快速找到并以特殊格式标示出这些特定数据所在的单元格。1）利用预置条件实现快速格式化）利用预置条件实现快速格式化“开始”选项卡“样式”组“条件格式”按钮 选择规则 选择预置的条件格式。第3章 通过EXCEL创建并处理电子表格3.1 Excel 制表基础343.1.3 格式化工作表高级技巧各项条件规则的功能说明：各项条件规则的功能说明： 突出显示单元格规则：通过比较运算符限定数据范围，例如，在一份工资表中，例如，在一份工资表中，将所有大于将所有大于10000元的工资数用红色字体突出显示。元的工资数用红色字体突出

28、显示。 项目选取规则：可以设定前若干个最高值或后若干个最低值、高于或低于该区域平均值的单元格特殊格式。例如，在一份学生成绩单中，用绿色字体标示某科目例如，在一份学生成绩单中，用绿色字体标示某科目排在后排在后5名的分数。名的分数。 数据条：帮助查看某个单元格相对于其他单元格的值。数据条越长，表示值越高。在观察大量数据（如节假日销售报表中最畅销和最滞销的玩具）中的较高值和较在观察大量数据（如节假日销售报表中最畅销和最滞销的玩具）中的较高值和较低值时，数据条尤其有用。低值时，数据条尤其有用。 色阶：通过使用两种或三种颜色的渐变效果来比较单元格区域中数据，一般情况下，颜色的深浅表示值的高低。例如，在绿色和黄色的双色色价中，可以指定数例如，在绿色和黄色的双色色价中，可以指定数值越大的单元格的颜色越绿，而数值越小的单元格的颜色越黄。值越大的单元格的颜色越绿，而数值越小的单元格的颜色越黄。 图标集：使用图标集对数据进行注释，每个图标代表一个值的范围。例如，在三例如，在三色交通灯图标集中，绿色的圆圈代表较高值，黄色的圆圈代表中间值，红色的圆色交通灯图标集中，绿色的圆圈代表较高值，黄色的圆圈代表中间值，红色的圆圈代表