Excel数据处理软件的应用（二） 清华大学电教中心 杜 婧 dujing@mail.tsinghua.edu.cn

内容提要 课堂测验，给出成绩作为考核 Excel公式和函数 Excel 图表操作 Excel 数据管理 数据库 数据排序 数据筛选 分类汇总 创建图表 修改图表 如何创建公式 如何引用单元格 函数应用实例 课堂测验，给出成绩作为考核

知识回顾 工作簿打印 Excel一秒钟快照 设置快照工具 拍照 特点 目的 操作：自定义工具栏->命令->左侧类别选“工具”->右侧命令选“照相机”->按左键拖动到工具栏中 拍照 操作：选中照相范围->点击“照相机”按钮->出现虚线框->在目标位置单击左键，即可 特点 原始数据变化，快照也会变化 目的 不同的、不连续的工作表区域打印到同一页面上

Excel 公式和函数 什么是公式？ 公式是指使用运算符和函数，对工作表数据以及普通常量进行运算的方程式。 Fx=280*2+C2*5-AVERAGE(C8:C15) 创建公式 在编辑栏中输入：“=”开始进行公式编辑 使用行号和列表的组合来引用单元格 自动填充公式

Excel 公式和函数 公式运算符 （一） 算数运算符 完成基本的数学运算 算术运算符 含义 示例 +(加号) 加 1+1 -(减号) 减/负号 2-1，-1 *(星号) 乘 2*2 /(斜杠) 除 6/3 %(百分号) 百分比 10% ^(脱字符) 乘幂 4^3(与4*4*4相同)

Excel 公式和函数 公式运算符 （二） 比较运算符 可以比较两个数值并产生逻辑值TRUE或FALSE 比较运算符 含义 示例 =(等号) 等于 A1=B1 >(大于号) 大于 A1>B1 <(小于号) 小于 A1<B1 >=(大于等于号) 大于等于 A1>=B1 <=(小于等于号) 小于等于 A1<=B1 <>(不等于号) 不等于 A1<>B1

Excel 公式和函数 公式运算符（三） 文本运算符 文本运算符“&”可以将一个或多个文本连接为一个组合文本。 文本运算符 含义 示例 &(连字符) 将两个文本值连接或串起来产生一个连续的文本值 “north”&”west”产生“northwest”

Excel 公式和函数 公式运算符 引用运算符 可以将单元格区域合并计算。 引用运算符 含义 示例 ：(冒号) 区域运算符，对两个引用之间，包括两个引用在内的单元格进行引用 A1:A5 ，(逗号) 联合运算符，将多个引用合并为一个引用 SUM(A5:A15,B5:B15)

Excel 公式和函数 公式返回错误值及其原因 #DIV/0 除数为零，当公式被空单元格除时，也会出现这个错误 #N/A #####！ 公式计算的结果太长，单元格容纳不下，增加单元格的列宽可以解决这个问题 #DIV/0 除数为零，当公式被空单元格除时，也会出现这个错误 #N/A 公式中无可用的数值或者缺少函数参数 #NAME？ 公式中引用了一个无法识别的名称，当删除一个公式正在使用的名称或者在使用文本时有不相称的引用，也会返回这种错误 #NULL！ 使用了不正确的区域运算或者不正确的单元格引用 #NUM！ 在需要数字参数的函数中使用了不能接受的参数，或者公式计算结果的数字太大或者太小，无法表示 #RFF! 公式引用了一个无效的单元格，如果单元格从工作表中被删除就会出现这一错误 #VALUE! 公式中含有一个错误类型的参数或者操作数，操作数是公式中用来计算结果的数值或者单元格引用

Excel 公式和函数 什么是引用单元格 按地址引用单元格 方法：采用行号和列标来表示、引用一个单元格 在列A和行6种的单元格 A6 如果要引用 请使用 在列A和行6种的单元格 A6 属于列A和行5到行10中的单元格区域 A5:A10 属于行10和列B到列E中的单元格区域 B10:E10 行10中的所有单元格 10:10 从行5到行10中的所有单元格 5:10 列B中的所有单元格 B:B 从列B到列D中的所有单元格 B:D B2、B3、B4、C6四个单元格 B2:B4,C6

Excel 公式和函数 什么是引用单元格 引用操作 方法一：直接在公式中输入单元格的标识 跨表引用 同一工作簿，不同工作表： 方法二：书用鼠标框选单元格 跨表引用 同一工作簿，不同工作表： “工作表名称！单元格地址”如，“sheet 2！A1：B4” 不同工作表： “[工作簿名称]工作表名称！单元格地址” 如，“[期末成绩表]学生名单！A1：B4”

Excel 公式和函数 什么是引用单元格 相对、绝对、混合引用 相对引用： 所引用的单元格位置随着计算单元格位置的变化而变化，表示方式：E6 绝对引用： 所引用的单元格位置始终不变，跟计算单元格位置无关，表示方式：$E$6 混合引用： 上面两者的结合，表示方式：E$6,$E6

Excel公式和函数 什么是函数？ 常用函数 在公式中使用函数 函数是预定义的内置公式，是设置好的计算关系，可将参数按特定的顺序或结构进行计算。 Fx=函数名（参数1，参数2，…，参数n） 常用函数 SUM与SUMIF函数 AVERAGE函数 IF函数 COUNT与COUNTIF函数 LOOKUP函数 ROUND函数 在公式中使用函数

Excel公式和函数 SUM，求和函数 SUM(number1,number2, ...) 说明 直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。 如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 如果参数为错误值或为不能转换成数字的文本，将会导致错误。

Excel公式和函数 SUMIF，求满足条件的单元格数值之和 SUMIF(range,criteria,sum_range) Criteria    为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32" 或 "apples" Sum_range    是需要求和的实际单元格。 说明 只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和。 如果忽略了 sum_range，则对所选区域中的单元格求和。

Excel公式和函数 AVERAGE，求平均值函数 AVERAGE(number1,number2,...) 说明 参数可以是数字，或者是包含数字的名称、数组或引用。 如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。

Excel公式和函数 IF，执行真假值判断，根据逻辑计算的真假值，返回不同结果。 IF(logical_test,value_if_true,value_if_false) Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如，A10=100 就是一个逻辑表达式，如果单元格 A10 中的值等于 100，表达式即为 TRUE，否则为 FALSE。本参数可使用任何比较运算符。 Value_if_true logical_test 为 TRUE 时返回的值。例如，如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE，则 IF 函数将显示文本“预算内”。如果 logical_test 为 TRUE 而 value_if_true 为空，则本参数返回 0（零）。如果要显示 TRUE，则请为本参数使用逻辑值 TRUE。Value_if_true 也可以是其他公式。 Value_if_false logical_test 为 FALSE 时返回的值。例如，如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE，则 IF 函数将显示文本“超出预算”。如果 logical_test 为 FALSE 且忽略了 Value_if_false（即 value_if_true 后没有逗号），则会返回逻辑值 FALSE。如果 logical_test 为 FALSE 且 Value_if_false 为空（即 value_if_true 后有逗号，并紧跟着右括号），则本参数返回 0（零）。Value_if_false 也可以是其他公式。

Excel公式和函数 COUNT函数，返回包含数字以及包含参数列表中的数字的单元格的个数。 COUNT(value1,value2,...) 说明 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值，请使用函数 COUNTA。

Excel公式和函数 COUNTIF函数，计算区域中满足给定条件的单元格的个数。 COUNTIF(range,criteria) Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32" 或 "apples"。 说明 Microsoft Excel 提供其他函数，可用来基于条件分析数据。例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作表函数。若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用 IF 工作表函数。

Excel公式和函数 LOOKUP函数，返回向量（单行区域或单列区域）或数组中的数值。 LOOKUP(lookup_value,lookup_vector,result_vector) Lookup_value    为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。 Lookup_vector    为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。 要点  Lookup_vector 的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。 Result_vector    只包含一行或一列的区域，其大小必须与 lookup_vector 相同。 说明 如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。 如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。

Excel公式和函数 ROUND函数，返回某个数字按指定位数取整后的数字。 ROUND(number,num_digits) 说明 如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。

Excel公式和函数 动手练习——成绩管理.xls Step1：自创公式计算综合成绩 Step2：使用SUM函数和公式计算综合成绩 Step3：使用IF函数判断及格与否

Excel 图表操作 创建图表 修改图表 选中数据区域 插入图表 选择图标类型 填写图表信息 完成 单击要修改的图表部分 进行格式修改 对数据进行修改

Excel 图表操作 动手练习——成绩管理.xls Step1：将各学生期末成绩用柱状图进行横向对比

Excel 数据管理 数据库 数据库是一个以相同表结构存放在存储设备中的数据集合。合乎数据规范的数据表格能进行一系列的数据管理工作。

Excel 数据管理 数据库规范 避免在一个工作表上建立多个数据清单 在工作表的数据清单与其它数据间至少留出一个空白列和空白行 避免在数据清单中放置空白行和列 避免将关键数据放到数据清单的左右两侧 数据清单的第一行里创建列标志 列标字体格式应与数据清单分开 单元格的开始处不要插入多余的空格 不要用空白行将列标与数据分开

Excel 数据管理 数据排序 数据筛选 选中数据区域，或数据区域的某一单元格 数据菜单中，排序 选择排序的列、排序方式，确定 数据菜单中，筛选，自动筛选 各列标右侧出现下拉菜单按钮，即可筛选

Excel 数据管理 数据分类汇总 选中数据区域，或数据区域的某一单元格 对数据排序 数据菜单中，分类汇总 选择分类字段 选择汇总方式 选择汇总字段 确定完成

Excel 数据管理 动手练习——项目管理.xls Step1：对表格进行排序 Step2：筛选需要的项目 Step3：撤销筛选

融会贯通小技巧 同时打开多个文件 同时编辑多个不连续的单元格 插入分数的方法 在excel工具栏中，找“打开”按钮 在相应的目录中，使用键盘ctrl键同时选中多个文件 点击对话框中“打开”按钮即可 同时编辑多个不连续的单元格 在一个工作表中，用键盘ctrl键同时选中几个单元格 在最后选中的单元格中编辑内容 键盘中按ctrl键+Enter键，即可 插入分数的方法 在单元格中，输入0空格2/3，即可变为分数2/3

融会贯通小技巧 将行数据转换为列数据 如何复制公式计算结果的数值 如何显示多行文本 选中已经编辑好公式的单元格 选择要转换的数据，复制 在目标单元格内单击鼠标右键，点击“选择性粘贴” 在打开的对话框中勾选“转置”，确定 如何复制公式计算结果的数值 选中已经编辑好公式的单元格 将鼠标移在编辑栏中，按下键盘F9键，即将公式转变为数值 复制粘贴即可 如何显示多行文本 设置单元格格式->对齐->自动换行 在目标单元格的编辑栏中需要换行的位置，输入Alt+Enter键

融会贯通小技巧 用word和excel批量打印信封 用WORD+EXCEL批量打印信封,应该用邮件合并功能. 完整做法应该是: 2、存盘，退出，注意文件名不要存成BOOK1.XLS之类的，可以存成ADD.XLS。否则会出错。 3、打开WORD，点工具->信函与邮件->邮件合并->信封->下一步启动文档->选取收件人 4、点“获取数据”->打开数据源->ADD.XLS 5、下一步选取信封->地址块，将格式中的选项与excel中字段进行匹配 6、预览信封，调整格式 7、打印 邮政编码 收信人地址 收件人姓名 收件人职务 寄信人地址 404000 某某某某某 某某某 某某