表格处理软件excel-2.ppt

1,Excel 2003 -2,2,4.5 公式和函数的使用,1 单元格引用 2 公式和函数的创建 3 公式中的错误信息 4 常用函数使用,3,单元格地址 (1) 相对地址：列标+行号，如A1，B2，C3等。公式复制时，若引用公式的单元格地址发生变动，公式中的相对地址会随之变动 (2) 绝对地址： $列标+ $行号，如$A$1，$B$2等。公式复制时，公式中的绝对地址保持不变 (3) 混合地址： 公式中既有相对引用又有绝对引用，如B6-B6*$C$2。公式复制时，公式中相对引用部分会随引用公式的单元格地址变动而变动，而绝对引用部分保持不变,1 单元格引用,4,引用同一工作簿中其他工作表中的单元格 在引用的单元格名字前面加上工作表名和“!” 如：表1！D4 引用其他工作簿中工作表上的单元格 引用方法：工作簿名称工作表名称!单元格名称 如：工作簿1.xlssh2!A2 当公式中引用的单元格数据发生变动时，公式所在单元格的值也会随之变动； 当公式中引用的是相对地址时，目标地址改变时公式中的相对地址将随之改变。,1 单元格引用,5,公式是用运算符将数据、单元格地址、函数等连接在一起的式子，以等号“=”开头 =5*10-20 常数运算 =A6+B1 对单元格A6和B1中的值相加 =SQRT(10+A2) 使用SQRT函数，求开方根 运算符 算术运算符、文本运算符和关系运算符,2 公式和函数的创建,6,=83*25% 表示8的立方再乘以0.25，结果为128 =B2/4 若单元格B2的值是12，则结果为3 =“计算机“ & “电脑“ 运算结果是“计算机电脑“ = A1 & A2 把单元格A1和A2中的文本进行连接 而生成一个新的文本 = B242 比较结果为TRUE (设B2的值是12) = B2=32 比较结果为FALSE = B212 比较结果为FALSE = A3+80 假设A3为日期型数据，则A3+80 表示日期A3加上80天,公式举例,7,公式的输入和复制,例1 计算出学生的总评成绩(=机试40%+笔试60%) (1) 单击总评成绩第一个单元格E2，使之成为活动单元格 (2) 键入公式“=C2*0.4+D2*0.6”后按回车键。 (3) 再次单击单元格E2使之成为活动单元格；拖动填充柄至E6单元格完成公式复制。,8,输入函数可以通过单击工具栏中图标“ ”或“ ”菜单命令“插入函数”完成。,“插入函数”对话框,函数的输入,9,3 出错信息,当公式(或函数)表达不正确时，系统将显示出错信息,10,格式： =(, ,) 举例： = SUM(C2:C6) = AVERAGE(A1:A3,C2:C6) SUM 求和数 AVERAGE 求平方值 MAX 求最大值 MIN 求最小值 COUNT 求数值数据的个数 COUNTIF 条件求数值数据的个数 FREQUENCY 频率分布统计 IF 条件选择,4 函数的使用,11,函数应用举例,(1) 单击单元格D8，使之成为活动单元格 (2) 键入公式“=SUM(D2:D7)”，并按回车键 例2 计算扣款总数，将结果分别存放在单元格E8中 (1) 选定E2:E8区域 (2) 单击“自动求和”按钮，即可对该列求和，和数存放在该区域的最后一个单元格上,12,例3 计算基本工资的平均值，并将结果存放在D9中 例4 利用MAX求基本工资的最大值，并将结果存入单元格D10中 例5 假设单元格A1A4的内容分别为1，2，空，“ABC”，则COUNT(A1:A4)的值为2。COUNT( )只计算数值单元格的个数。 例6 若要求出基本工资数大于等于3000的职工人数，可采用： = COUNTIF(D2:D7, “=3000“) 例7 在工作表(A1:D9)中，要求出班号为21的学生人数，可采用： = COUNTIF(D2:D9, “21“) 或 = COUNTIF(D2:D9, D3),函数应用举例,13,频率分布统计函数FREQUENCY,用于统计一组分段数据在某个数据样本区间的分布情况 格式：FREQUENCY(data_array,bins_array) 功能：计算一组数(data_array)分布在指定各区间(由bins_array确定)的个数 例8 在成绩表A1:D9中，统计出成绩60，60成绩70，70成绩80，80成绩90，成绩90的学生数各有多少 (1) 在区域(如F4:F7)中建立统计的间距数组(59,69,79,89) (2) 选定作为统计结果的数组输出区域G4:G8 (3) 键入函数“=FREQUENCY(C2:C9，F4:F7)” (4) 按下Ctrl+Shift+Enter组合键,14,例8的执行结果,15,格式：IF(条件, 值1, 值2) 功能：对条件进行测试，如果条件成立，则取值1，否则取值2 举例 已知单元格C2中存放考试分数，要根据分数判断是否及格 =IF(C2=80,“优良“,IF(C2=60,“中“,“不及格“),条件选择函数IF(),16,例9 在工资表中，按级别计算“补贴”，教授补贴1500元，副教授补贴1200元，讲师1000元，助教及其他人员补贴800元，然后计算每个人的实发数。操作步骤如下： (1) 计算第一个记录的“补贴”。在F2单元格中输入函数： =IF(C2=“教授“, 1500, IF(C2=“副教授“, 1200, IF(C2=“讲师“, 1000, 800) 按Enter键后，该单元格显示1500。 注意：上面函数不能写成“=IF(C2=”教授“,”1500“,”，即不能用双引号把数字（数值型数据）括起来。只是用到文本数据时才需要用双引号括起来，如“教授”。 (2) 计算第一个记录的“实发数”。 在G2单元格中输入函数： = D2 + F2 - E2 按Enter键后，该单元格显示5600 (3) 用复制公式的方法，可以把单 元格F2和G2中的公式复制到其他单 元格中，以计算出其他教师的“补贴”和“实发数”。,4.6 图表,4.6.1 图表的创建,4.6.2 图表的编辑及修饰,Excel图表可以直观表示数据结果。在美国总统大选历史上，就曾流传过“一张图表胜过一千句话”的说法。,选中要建立图表的单元格区域，单击工具栏上“ ”图标，或单击“插入图表”命令，可以打开“图表向导”对话框，快速建立相应数据的图表。 下图给出了“实用电数”图表。,4.6.1 图表的创建,(1) 图表的基本知识 可以生成嵌入式图表和独立的图表工作表 可以按行、或者按列，产生数据系列，进而产生图表。 例：依据工作表区域A2:D6中的数据绘制的一个图表,柱形图图表。有3个数据系列，分别对应于数据源中的3 个列：B2:B6(计算机系)，C2:C6(电子系)，D2:D6(信管 系)。每个数据系列中都有4个数据点，因而构成4 个数据 分类: 99级、00级、01级和02级,(2) 图表的基本元素,Excel提供了多种图表类型，有平面图表，也有立体图表。常用的图表类型主要有柱形图、折线图、饼图和XY散点图等 柱形图：柱形图是用柱形块表示数据的图表，通常用于反映数据之间的相对差异 饼图: 饼图用于表示部分在整体中所占的百分比，能显示出部分与整体的关系 折线图: 折线图是用点以及点与点之间的连成的折线表示数据的图表，它可以描述数值数据的变化趋势 XY散点图: 散点图中的点一般不连续，每一点代表了两个变量的数值，适用于分析两个变量之间是否相关,(3) 图表的类型,可以随时修改工作表中的图表源数据，还可以对图表标题、图例、数据系列、背景、坐标轴、图表区、绘图区等进行设置。进行修改及修饰的方法主要有： （1）右击图表中元素，可打开相应元素的快捷菜单； （2）双击图表中元素，可打开相应元素的格式设置对话框。,原图表,修饰后的图表,4.6.2 图表的修改、修饰,4.7 打印输出,4.7.1 打印预览,4.7.2 页面设置,4.7.3 工作表打印,单击“视图|分页预览”，可将视图切换到“分页预览”界面，预览实际打印效果。,分页预览视图中，鼠标位于分页符上时，可以拖动鼠标，直接调整每页打印文字的多少。,4.7.1 打印预览,主要介绍Excel的两个特有设置。单击“文件页面设置”，打开“页面设置”对话框： 1设置缩放比例：在“页面设置”对话框的“页面”选项卡中,通过此项，可直接将整个工作表在一页中输出。,4.7.2 页面设置,2设置打印区域及页表头：在“页面设置”对话框的“工作表”选项卡中,通过此项，可在每页输出工作表的行列标题。,通过此项，可选择工作表的输出是否带网格线，行号等。,4.7.2 页面设置,通过此项设置打印区域,单击“文件打印”，可设置工作表的打印参数。,通过此对话框，可选择打印整个工作簿，或指定页,4.7.3 工作表打印,4.8 数据管理,书上P109,29,4.9 数据分析,1数据的排序,2数据的分类汇总,3数据的筛选,Excel提供了快捷的数据处理功能，单击“数据”菜单中的“筛选”、“排序”、“分类汇总”命令可以快速实现数据表的相关操作。,30,单击“数据排序”，可对指定数据区进行指定排序。,“排序”对话框中， “主要关键字” 选定要排序的列标题，“次要关键字”可进一步确定当“主要”值相等时次要排序的列标题。“次要关键字”和“第三关键字”均为可选项。这里“总评成绩”为主要关键字，“期末笔试”为次要关键字，“期中考试”为第三关键字。,1 数据的排序,31,数据的分类汇总是指将数据按不同类别各自进行汇总的数据处理。要注意的是：数据在进行分类汇总前一定要先进行排序处理，以便将同类数据集中在一起。,2 数据的分类汇总,32,先要对数据清单按分类的关键字进行排序，再执行“数据”的“分类汇总”命令。 例 在成绩表中，按“班号”字段分类汇总“成绩”的平均分(即统计出各个班的平均分),所谓数据“筛选”，是指按条件显示数据表中数据。主要分两种：“自动筛选”和“高级筛选”。,3 数据的筛选,34,自动筛选,自动筛选采用简单条件来快速筛选记录，将不满足条件的记录暂时隐藏起来，而将满足条件的记录显示在工作表上. 单击“数据筛选自动筛选”。 例:在上述成绩表中，筛选出成绩大于等于80分的记录 选定数据清单，选择“数据”菜单中的“筛选”命令 从“筛选”级联菜单中选择“自动筛选”选项，此时在标题栏中每个字段名右边出现了一个下拉箭头 单击“成绩”字段名的下拉箭头，从下拉列表单击“自定义”选项，打开“自定义自动筛选方式”对话框 本题筛选条件：成绩=80。设置如下,35,例 在上述成绩表中，查找成绩在7090分之间的记录 本例题筛选条件是：成绩=70 AND 成绩=90，因此在“自定义自动筛选方式”对话框中设置如下,36,高级筛选 多个条件筛选时，可应用“高级筛选”设置。在“高级筛选”对话框中，“数据区域”指定要进行筛选的区域，“条件区域”指定要进行筛选的条件区域；“复制到”指定筛选结果位置；“选择不重复的记录”指筛选时滤掉重复记录。,37,例 在成绩表中，查找11班及21班中成绩不及格的所有记录。 筛选条件为： (成绩60 AND 班号=11）OR（成绩60 AND 班号=21) 执行“数据”的“筛选”/“高级筛选”命令。,条件区域（F1:G3）,筛选结果 (E6:H6),38,条件区域的规定,条件区域：条件标记和具体条件 条件标记行在上方，逐一列示； 具体条件行在下方： 同行并列，表示“逻辑与”； 错行列示，表示“逻辑或”。 同一行的条件为“与”(AND)的关系，表示要求筛选出同时满足这些条件的记录； 错行的条件为“或”(Or)的关系，表示要求筛选出只要满足其中一个条件的记录； 例 查找成绩大于等于80分，且班号为“11”的所有记录 筛选条件：成绩=80 AND 班号=11，条件区域表示： 补充例 查找计算机90或英语90分的所有记录 筛选条件：计算机=90 OR 英语=90，条件区域表示：,39,不同条件行的条件互为“或”(OR)的关系，表示筛选出满足任何一个条件的记录 例 查找英语和计算机课程中至少有一科不及格的记录 英语60 OR 计算机60，条件区域表示： 对相同的列(字段)指定一个以上的条件，或条件为一个数据范围，则应重复列标题 例 查找成绩60分，并且90分的姓“林”的记录,40,判断(条件区域) 班11及班21的所有不及格学生 A. (成绩60 AND 班号=11) OR (成绩60 AN