第4章 Excel2007

主要内容 基本操作及制作表格 1 应用公式、函数 2 数据的图表化 3 数据分析和管理 4

重点及难点 表的操作与表的格式化，掌握不同类型数据的输入规则，以及行、列、单元格的修饰 表格处理 数据计算 正确使用公式/函数，解决生产生活中的应用问题 数据分析 与管理 了解数据库的相关操作，掌握分析、管理数据的方法和技巧，能够对数据表进行排序、筛选、分类汇总等操作

关于Excel2007 电子表格软件EXCEL 2007能够方便地制作各种表格，有效地进行数据处理、统计分析和预测决策等。其主要特点是： 增加了工作薄中可显示的颜色数（从原来的256色到现在的43亿(32位色)) 格式化的“现场预览” 极大的改进了图表，专业的“图表样式” 丰富的条件格式，引入了一些新颖的功能，如色阶、图标集和数据条 一个新的视图——“页面布局”视图，增加了普遍需要的、与打印相关的内 容 Office主题可以更改这个文档的总体设计，包括颜色、字体和效果变化

Excel2007基本操作 Excel2007工作环境及基本概念 Excel 2007的工作窗口 Office按钮（可以完成新建、打开、保存、关闭工作薄的操作，以及退出Excel、“Excel”选项设置等） 功能区 编辑栏 快速访问工具栏 状态栏

Excel2007基本操作 工作簿、工作表及单元格的基本概念及相互关系 工作薄是一个Excel文件，其文件扩展名为.xlsx,启动Excel会自动建立名为“Book1”的工作薄，一个工作薄最多含有255个工作表，一个新工作薄默认有3个工作表，分别命名为Sheet1、Sheet2和Sheet3。 工作表由单元格、行号、列标、工作表标签等组成，列标的表示范围为A~Z、……、IA~IV，行号范围为1~65536。 单元格的地址由“行号”和“列标”组成，如A1、IV65536

Excel2007基本操作 工作薄操作（新建、打开、保存、关闭） 工作表操作 (设计表格、数据输入/编辑、自定义序列) 单元格的定位及单元格区域的命名 使用工作表 使用单元格 查看和编辑工作薄属性

数据输入与编辑 多个不连续单元格输入相同内容（用组合键Ctrl+Enter） 数据类型（文本型、数值型和日期时间型） 默认情况下，数据的对齐方式为：文本型数据左对齐，数值型数据右对齐。 日期时间的输入有多种格式，年、月、日之间合法的分隔符是“-”和“/” 输入当前日期用快捷键【Ctrl+；】，输入当前时间用快捷键【Ctrl+Shift+；】 特别注意 数字信息（文本型）如商品编号、电话号码、身份证号、银行账户等的输入

Excel2007基本操作 格式化工作表 1、设置单元格格式 数字格式、对齐方式和字体、单元格边框、单元格图案、绘制斜线表头 2、设置行高和列宽（用鼠标粗略设置或者用命令精确设置） 3、应用样式（设置条件格式、套用表格格式、单元格样式） 4、更改文档主题 5、图形和艺术字

案例4——工作表格式化 知识要点 1、合并单元格 2、设置边框和底纹 3、使用条件格式 4、文本换行【Alt+Enter】 5、应用中文货币形式

Excel2007基本操作 页面设置、打印工作表 案例5

数据计算 使用公式 （1）公式的形式：=<表达式> （2）常用运算符 （3）公式的应用 （4）单元格地址的引用 注：表达式可以是算术表达式、关系表达式和字符串表达式等，其组成可以是运算符、常量、单元格地址、函数及圆括号等，但不能含有空格 （2）常用运算符 （3）公式的应用 （4）单元格地址的引用 相对地址：D3、A8 绝对地址：$D$3、$A$8 混合地址：$D3、A$8 跨工作表的单元格地址引用： [工作薄文件名]工作表名!单元格地址

数据计算 常用函数 常用函数： COUNTIF、SUMIF、VLOOKUP 函数嵌套： SUM 、AVERAGE、MAX、MIN、COUNT、 COUNTIF、SUMIF、VLOOKUP 函数嵌套： =IF(d3>=90,”优秀”,IF(d3>=80,”良好”,if(d3>=60,”及格”,”不及格”))) 成绩 ≥90 80～90 60～80 ＜60 等级 优秀 良好 及格 不及格

数据计算 案例7、8——工资管理

数据计算 关于错误信息 错误信息 原因 ##### 表示列宽不足以显示结果，或者使用了负数的日期和时间 #DIV/O! 表示除数为0（零） #N/A 表示函数或公式中的某些数值不可用 #NAME? 表示单元格内出现了Excel无法识别的文本 #NULL! 表示交集运算符指定了两个并不相交的区域的交点 #NUM！ 表示公式和函数中使用了无效的数值 #REF! 表示公式和函数中使用了无效的单元格 #VALUE! 表示使用的参数或操作数的数据类型不正确

数据的图表化 图表的基本概念 创建图表的过程——演示 图表的编辑和修改 图表的修饰 案例制作

图表的定义：图表泛指在屏幕中显示的，可直观展示统计信息属性（时间性、数量性等），对知识挖掘和信息直观生动感受起关键作用的图形结构，是一种很好的将对象属性数据直观、形象地“可视化”的手段。 图表的分类 条形图、柱状图、折线图和饼图是图表中四种最常用的基本类型。按照Microsoft Excel对图表类型的分类，图表类型还包括散点图、面积图、圆环图、雷达图,气泡图,股价图等。此外，可以通图表间的相互叠加来形成复合图表类型。

图表的基本构成：标题、刻度、图例和主体等。 图表的表达特性: 具有表达的准确性，对所示事物的内容、性质或数量等处的表达应该准确无误。 信息表达的可读性，即在图表认识中应该通俗易懂，尤其是用于大众传达的图表。 图表设计的艺术性，图表是通过视觉的传递来完成，必须考虑到人们的欣赏习惯和审美情趣，这也是区别于文字表达的艺术特性。

创建图表 选择数据区域（可以是连续的，也可以是不连续的） 确定图表类型（使用“图表工具”的“设计”、“布局”、“格式”选项卡确定图表样式和格式） 编辑图表（图表标题、图表区、绘图区、图例项、数据系列、坐标轴等）

应用举例1 利用散点图（带平滑线的散点图）制作正弦和余弦函数的图像

应用举例2 利用饼图（三维饼图）显示销量的增长速度

数据分析与管理 建立数据清单 排序 数据筛选 自动筛选 高级筛选 分类汇总 合并计算 数据透视表和数据透视图

数据清单是遵循数据库规则建立的Excel表格 数据表的第一行为字段名（文本型数据），其余行是一片连续的数据区域。 数据表的行数据称为记录（即一行就是一条记录），列数据称为字段值，每一列数据具有相同的数据类型。 例如，公司的客户名录中，每一条客户信息就是个记录，所有记录的同一字段存放相似的信息。

数据排序 Excel允许对字符、数字等数据按大小顺序进行升序或降序排列，要进行排序的数据称之为关键字。不同类型的关键字的排序规则如下： 数值：按数值的大小。 字母：按字母先后顺序。 日期：按日期的先后。 汉字：按汉语拼音的顺序或按笔画顺序。 逻辑值：升序时FALSE排在TRUE前面，降序时相反。 空格：总是排在最后。

工作表中数据的排序 简单排序 复杂排序 自定义排序 自定义排序方法 建立自定义序列 单击数据区中要进行排序的任意单元格，单击【数据】菜单，选择【排序】项，在弹出的【排序】对话框中单击【选项】按钮，系统弹出【排序选项】对话框 在【自定义排序次序】的下拉列表中，选择前面建立的自定义序列，然后单击【确定】按钮，即可对数据进行自定义排序。

筛选工作表中的数据 自动筛选 记录单检索数据每次只能显示一个数据行，当查询的数据较多，或要把查询的结果汇总成表时，就需要使用筛选工具了。自动筛选提供了快速检索数据清单或数据库的方法，通过简单的操作，就能筛选出需要的数据。

筛选工作表中的数据 高级筛选 建立条件区域，构造条件建立条件区域. “或” 关系的条件不能出现在同一行 “与”关系的条件必须出现在同一行 选择工作表的数据清单区域 单击“数据→“排序和筛选”→“高级”

说明：高级筛选结果可以显示在数据清单的原有区域中，也可以复制到工作表的其他空白单元格区域，系统默认的方式是在数据清单的原有区域中显示结果。

数据分类汇总 在对数据进行分析时，常常需要将相同类型的数据统计出来，这就是数据的分类汇总

将数据表按照主要关键字“品牌”，次要关键字“商品”进行排序 以“品牌”为分类字段，求和汇总“数量”和“金额” 进行第二次分类汇总，以“商品”为分类字段，求和汇总“数量”和“金额” 查看汇总结果

特别注意： 在对数据进行汇总之前，首先必须对要汇总的关键字进行排序。 在第一次自动分类汇总的结果上，还可以再进行分类汇总，两次分类汇总的关键字可以相同，也可以不同

合并计算 通过合并计算，可以把来自一个或多个来自源区域的数据进行汇总，并建立合并计算表。这些源区域与合并数据表可以在同一个工作表中，也可以在同一个工作薄的不同工作表中，还可以在不同工作薄中。

数据透视表和数据透视图 数据透视表是一个交互式表格，它可以快速地组合和合并数据。它不仅具有转换行和列来查看源数据的不同汇总结果、显示不同页面来筛选数据、根据需要显示区域中的细节数据和设置报告格式等功能，还具有链接图表的功能。 数据透视图是数据透视表的更深一层次应用，它可将数据以图形的形式表示出来。

数据透视表的用途 以多种友好方式查询大量数据； 对数值数据进行分类汇总，按分类和子分类对数据进行汇总，创建自定义计算和公式； 展开或折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细； 将行移动到列或将列移动到行，以查看源数据的不同汇总； 对最有用和最关注的数据子集进行筛选、排序、分组 和有条件地设置格式。

数据透视表的创建 第1步 打开“数据透视表”设置框 第2步 选择摆放位置 第3步 指定一个起始单元格 第4步 确认

建立数据透视图 单击“数据透视表工具”→“选项”→“工具” → “数据透视图” 选择透视图 生成透视图

课后练习 学生成绩管理 1、新建工作薄“学生成绩.xlsx”，包括六张工作表，分别为“班级通讯录”，“班级课程表”， “学生平时成绩”，“学生考试成绩”，“总评成绩”，“奖学金评定” 2、工作表的修饰自定义，要求字体大小适中、颜色搭配协调、单元格设置合理 3、课程成绩列区域设置数据的有效范围为0～100（输入信息中提示成绩满分为100；进行出错警告），不及格的单元格用红色字体显示，并加上25%的灰色底纹；缺考的单元格用淡蓝色底纹。 4、根据“学生平时成绩”和“学生考试成绩”计算“总评成绩”（包括总分、平均分、名次；统计“平均分”各分数段的学生人数，并以此为数据源建立图表） 5、评定奖学金