第十讲 Excel2010数据管理与图表操作 参考教材第4章 网络信息中心教研室 谢华成 大学计算机基础 网络信息中心教研室

内容提纲 8.1 使用公式与函数 8.2 数据管理 8.3 数据图表 8.4 打印工作表 重点 难点 难点 大学计算机基础 8.1 使用公式与函数 重点 8.2 数据管理 难点 8.3 数据图表 难点 8.4 打印工作表 大学计算机基础 网络信息中心教研室

8.1 使用公式与函数 重点 1 单元格的引用 2 使用公式 3 使用函数 4 常用函数介绍 大学计算机基础 网络信息中心教研室

绝对单元格引用是引用特定位置处的单元格，在公式复制或移动的过程中，单元格的地址不发生变化。 8.1.1 单元格的引用 相对引用 相对单元格引用是引用相对于公式所在单元格位于某一位置处的单元格，当复制或移动公式到新的位置时，公式中引用的单元格地址相对的发生变化。 绝对引用 绝对单元格引用是引用特定位置处的单元格，在公式复制或移动的过程中，单元格的地址不发生变化。 （表示方法:在所需单元格列号和行号之前加“$”符号） 三维引用 若引用同一工作簿的其它工作表中的单元格，在工作表名与单元格引用之间用感叹号（!）分开。 大学计算机基础 网络信息中心教研室

=（B4+25）/ SUM（D5:F5） 1. 公式的语法 单元格区域范围 公式特定符号 运算操作符号 单元格地址 数值型常量 8.1.2 使用公式 1. 公式的语法 公式是由运算符和参与计算的元素(常量、单元格或单元格区域以及函数等)组成的有意义的式子，在输入公式时必须以符号“=”或“+”开始 。 单元格区域范围 公式特定符号 运算操作符号 单元格地址 简单例子：=25 =B4 =3+5-2 =16/2+3 公式是在工作表中对数据进行计算和分析的等式，它可以对工作表数据进行加、减、乘、除、等运算。公式是Excel的核心。函数通常是公式的重要成分，它是预先定义好的内置公式。Excel提供了10大类200多个函数，常用的有日期和时间函数、财务函数、统计函数数学与三角函数、数据库操作函数等类型，他们构成了功能强大的数据统计与分析系统。灵活、准确地使用公式和函数是用好Excel的关键。 =（B4+25）/ SUM（D5:F5） Excel函数 数值型常量 单元格地址 大学计算机基础 网络信息中心教研室 5

2. 常见的运算符 称为逻辑值 算术运算符：加号(+)、减号(-)、乘号()、除号(/)、百分比(%)、幂(∧)。 比较运算符(比较结果为：TRUE真 或 FALSE假）： 等号(=)、大于号(>)、小于号(<)、 大于等于号(>=)、小于等于号(<=)、 不等于号(<>)。 称为逻辑值 大学计算机基础 网络信息中心教研室

文本连接符:（&） 其功能是将两个文本连接成一个整体。 例：=B2&C2 结果为B2和C2内的文本相连接 ：语文数学 引用运算符： （：）区域运算符， 例：=SUM（D5：F5） （，）连接多个单元格区域。 例：=SUM（D5：F5，B2：B5） D5到F5之间的单元格区域 D5到F5之间和B2到B5之间的单元格区域 大学计算机基础 网络信息中心教研室

3. 公式的输入 例1：计算：历史=英语成绩/6X7-8 例：=E3/6*7-8 （1）选择要输入公式的单元格; （2）在编辑框的输入框或者当前选中的单元格中，输入一个等号(=)，然后键入公式表达式; （3）单击“确认”按钮，或按回车键。 例1：计算：历史=英语成绩/6X7-8 例：=E3/6*7-8 单元格地址 大学计算机基础 网络信息中心教研室

4. 公式的复制 拖动含有公式的单元格的右下角控点(又称填充柄——指针 变成“” )，可以将公式按序列方式填充到其它单元格。 填充柄 大学计算机基础 网络信息中心教研室

5. 公式错误说明 （4）错误值 #VALUE！ 表明在公式中引用了错误的参数。 （5）错误值 ##### （1）错误值 #DIV/0！ 表明公式中含有除法运算，而且作为除数的单元格中的数值为零。 （2）错误值 #NAME? 表明公式中出现了不能识别的名称。 （3）错误值 #REF！ 表明在公式中引用了无效的单元格。 （4）错误值 #VALUE！ 表明在公式中引用了错误的参数。 （5）错误值 ##### 表示公式产生的结果较长，单元格容纳不下。 大学计算机基础 网络信息中心教研室

8.1.3 使用函数 函数名（参数1，参数2，…） 1. 函数的一般格式 参数由用户选定或录入 具有一定含义的英文缩写字母， 表示函数功能 圆括号是必须的 函数的参数，使用逗号(,)分隔，······表示与上一参数种类相同的参数可以有多个。不同类型的函数要求给定不同类型的参数.有的函数无参数。参数由用户选定或录入，根据这些参数和函数名，函数将返回处理结果。 说明：◆函数总是返回一个处理结果； ◆参数之间用逗号隔开； ◆既使无参数，圆括号也不能省略。 大学计算机基础 网络信息中心教研室 11

2. 函数的输入 “公式”“插入函数” 或者地址栏上的插入函数图标( ) 大学计算机基础 网络信息中心教研室

函数格式 函数功能及参数说明 引用的数据 参数说明 计算结果 大学计算机基础 网络信息中心教研室

8.1.4 常用函数介绍 (1)统计函数 count、countif、 average、Max、Min、Rank等 (2)算术函数 sum、sumif、ABS、exp、log、sqrt、int、round、mod等 (3)日期函数 now、month、today、year等 (4)逻辑函数 and、or、not、if、false、true等 大学计算机基础 网络信息中心教研室

1. 统计函数 COUNT(x1, x2, …) 求包含数值的单元格个数 COUNTIF(<范围>,<条件>)求满足条件的单元格个数 AVERAGE(x1, x2, …) 求平均值 MAX(x1, x2, …) 求最大值 MIN(x1, x2, …) 求最小值 RANK(x1,x2,x3) 求名次 大学计算机基础 网络信息中心教研室 15

COUNTIF函数详细说明 语法： COUNTIF(<范围>,<条件>) 说明: <范围> 单元格区域 <条件> 指定计数的条件，其形式可以为数字、表达式或文本。 大学计算机基础 网络信息中心教研室

返回 =COUNTIF(E3:E9,”>=70”) - COUNTIF(E3:E9,”>=80”) =E13/COUNT(E3:E9) 返回 大学计算机基础 网络信息中心教研室

RANK 函数详细说明 语法： RANK(<数值>,<范围>,<方式>) 说明: 返回指定数字在一列数字中的排位 <数值> 指定的数字； <范围> 指一组数； <方式> 指排位方式，如果为“零”或省略，降序；非零值，升序。 示例: 在总分后面添加一列，给出每位学生的总分名次。 （按降序） =RANK(H3,H$3:H$9) 大学计算机基础 网络信息中心教研室

大学计算机基础 网络信息中心教研室

2. 算术函数 sum(x1,x2，…) 求和 MOD(x1,x2) 求两数相除后的余数 ABS(x) 求绝对值 EXP(x) 求ex 的值 SQRT(x) 求x 的平方根 LOG(x,y) 求logyx 的值 SIN(x) 求正弦值 COS(x) 求余弦值 大学计算机基础 网络信息中心教研室 20

3. 日期时间函数 TODAY( ) 返回当前机器的日期序列数。 NOW( ) 返回当前机器的日期与时间序列数。 DATE(<年>,<月>,<日>)，根据<年>,<月>,<日>产生日期序列数。 DAY(<日期序列数>) 返回指定日期是当月的“几”号。 YEAR(<日期序列数>) 返回指日期的年份。 MONTH(<日期序列数>) 返回指定日期的月份。 WEEKDAY (<日期序列数>，<返回类型>) 返回指定日期是星期几。 大学计算机基础 网络信息中心教研室 21

Excel日期的内部表示 Excel 将日期存储为一系列连续的序列数，时间存储为小数。通过将单元格的格式设置为“常规”格式，可以查看以系列值显示的日期和以小数值显示的时间。 Excel 支持两种日期系统：1900 年和 1904 年日期系统。默认的日期系统是 1900 年日期系统。欲更改：“文件”->“选项”“高级”命令，勾选“使用1904日期系统” 。 注意： 当输入的年份为2位数字时, Excel 将如下解释年份： 00 到 29 之间，解释为 2000 到 2029 年； 30 到 99 之间，解释为 1930 到 1999 年。 大学计算机基础 网络信息中心教研室

年龄计算的方法 与当前日期（动态日期）比较： 1、=(today( )-B3) /365 日期数据单元格 当前日期 日期数据单元格 2、=year(today( )) – year(B3) 与某个单元格内的日期比较： 1、=($B$10-B3) /365 2、=year($B$10) – year(B3) 或=datedif (B3,today( ),"y") 或=datedif (B3,B$10,"y") 固定日期单元格B10 大学计算机基础 网络信息中心教研室 23

4. 逻辑函数 IF (逻辑测试表达式，逻辑值为真时的返回值，逻辑值为假时的返回值） 作用：执行真假值判断，根据逻辑的真假值，返回不同的结果。 例1：=IF(A1>=20,”20岁以上 ”,”小于20岁”) 例2：=IF( MOD(E3, 2)=0, ”二班” , ”一班”） 例1，例2为实验二中的要求。 大学计算机基础 网络信息中心教研室 24

日期时间函数及IF函数的综合应用 TODAY( ) 返回当前机器的日期序列数。 MONTH(<日期序列数>) 返回指定日期的月份。 例1：添加一列，当“出生日期”为3月时，写入“生日快乐！”。 =IF(MONTH(C2)=3,”生日快乐!”，””） 例2：添加一列，当“出生日期”为当前月时，写入“生日快乐!”。 =IF(MONTH(C2)=MONTH(TODAY()),”生日快乐!”，””） 空 返回 大学计算机基础 网络信息中心教研室 25

8.2 数据管理 难点 1 数据排序 2 分类汇总 3 数据透视表 3 数据筛选 大学计算机基础 网络信息中心教研室

1. 简单排序 8.2.1 数据排序 ①把光标移至排序依据列； ② “数据”功能区上的“降序( )”按钮 （或“升序( )”按钮）。 ② “数据”功能区上的“降序( )”按钮 （或“升序( )”按钮）。 大学计算机基础 网络信息中心教研室

2. 自定义排序 选取需排序的单元格区域； “数据”“排序”命令 大学计算机基础 网络信息中心教研室

创建/删除分类汇总 8.2.2 分类汇总 ①选择分类汇总数据所在区域； ②对所选区域的数据，按分类要求进行排序； ③单击“数据”“分类汇总”，打开“分类汇总”对话框； ④按需要进行选择后，单击“确定”。 大学计算机基础 网络信息中心教研室

分级显示分类汇总表 分级显示分类汇总表 二级分类汇总 大学计算机基础 网络信息中心教研室

“数据透视表”是“分类汇总”另一种方法。这种方法不需要提前对数据进行分类(排序)，并允许把汇总结果存入另一表格。 8.2.3 数据透视表 “数据透视表”是“分类汇总”另一种方法。这种方法不需要提前对数据进行分类(排序)，并允许把汇总结果存入另一表格。 (1) 选中要分类汇总的区域 (2) “插入”“数据透视表” 大学计算机基础 网络信息中心教研室

大学计算机基础 网络信息中心教研室

8.2.4 数据筛选 1. 自动筛选 “数据”“筛选” 大学计算机基础 网络信息中心教研室

2. 高级筛选 在工作表的任一空白区域，输入筛选条件； 例2：语文大于80或数学大于70 例1：语文大于80而且数学大于70。 语文 数学 条件单元格区域 语文 数学 >80 >70 语文 数学 >80 >70 注意：同行条件是“与”的关系，不同行的条件是“或”的关系. 大学计算机基础 网络信息中心教研室

“数据” “高级筛选” 大学计算机基础 网络信息中心教研室

大学计算机基础 网络信息中心教研室

8.3 数据图表 难点 1 图表概述 2 图表的建立 3 图表格式与编辑 大学计算机基础 网络信息中心教研室

以统计图的方式显示工作表数据。Excel中图表类型有11种 8.3.1 图表概述 以统计图的方式显示工作表数据。Excel中图表类型有11种 通过一个图表的样例，首先让同学们了解并熟悉图表中的元素： 数据标记：一个数据标记对应于工作表中一个单元格中的具体数值，它在图表中的表现形式可以有：柱形、折线、扇形等等。 数据系列：绘制在图表中的一组相关数据标记，来源于工作表中的一行或一列数值数据。图表中的每一组数据系列都以相同的形状和图案颜色表示。通常在一个图表中可以绘制多个数据系列，但是在饼图中只能有一个数据系列。 坐标轴：通常由类型轴（X轴） 和值轴（Y轴） 构成。 图例：每个数据系列的名字都将出现在图例区域中，成为图例中的一个标题内容，对应数据工作表则为这组数据的行标题或列标题。只有通过图表中图例和类别名称才能正确识别数据标记对应的数值数据所在的单元格位置。 绘图区：它含有坐标轴、网格线和数据系列。 图表区：它含有构成图表的全部对象，可理解为一块画布。 教材参考6.8.1节内容。 大学计算机基础 网络信息中心教研室 38

数据标签 标题 值轴（Y） 数据序列 数据标签 值轴（Y） 图例 大学计算机基础 网络信息中心教研室

8.3.2 图表的建立 1. 快速建立图表(默认柱形图) (1)选中产生图表的数据区域； (2) 按F11键。 图表被放入一个新建名为“Chart1”的工作表。 注意：选择图表数据时，最好同时选中系列名(图例名)。允许连选(Shift)和跳选(Ctrl)。 大学计算机基础 网络信息中心教研室

2. 建立自定义图表 (1) 选中产生图表的数据区域(连选或跳选); (2) “插入”“图表” ……选择合适的类型。 大学计算机基础 网络信息中心教研室

调整图表位置和大小 8.3.3 图表格式与编辑 图表的移动： 选中(单击)要移动的图表，用鼠标拖动它到一个新的位置，再松开鼠标即可。 改变大小： 先选中要改变大小的图表，然后将鼠标指针移至图表的四周的任一控点上，当指针变成双箭头时，拖动鼠标，直至图表变成满意的大小后松开鼠标。 大学计算机基础 网络信息中心教研室

图表标题/坐标轴/图例/数据标签/网格线 更改图表类型/切换行列/选择数据 图表标题/坐标轴/图例/数据标签/网格线 修改格式技巧：单击、单击、…、双击！ 大学计算机基础 网络信息中心教研室

8.4 打印工作表 1 页面设置 2 页眉页脚 3 打印 大学计算机基础 网络信息中心教研室

8.4.1 页面设置 设置打印区域 页面设置 大学计算机基础 网络信息中心教研室

8.4.2 页眉页脚 1. 页面设置 方法一 工作表标签 页码 文件名 总页数 大学计算机基础 网络信息中心教研室 46

在页面视图进行页眉页脚设置 方法二 (1) “视图”“页面布局” (2) 插入点(光标)移至页眉/页脚编辑框 (3) 通过“页眉和页脚工具”栏进行设置 大学计算机基础 网络信息中心教研室

8.4.3 打印 预览效果 大学计算机基础 网络信息中心教研室

小结 公式与函数 排序 筛选 分类汇总 数据透视表 数据图表 打印区域 页眉页脚 页面设置 大学计算机基础 网络信息中心教研室