Excel2003的工作表6.ppt

《Excel2003的工作表6.ppt》由会员分享，可在线阅读，更多相关《Excel2003的工作表6.ppt（36页珍藏版）》请在三一文库上搜索。

1、第6章 Excel 2003的数据计算,6.1 Excel 2003公式的使用,6.1.1 公式的概念 6.1.2 公式的输入 6.1.3 公式的修改 6.1.4 公式的填充,6.1 Excel 2003公式的使用,6.1.1 公式的概念,Excel 2003中的公式是对表格中的数据进行计算的一个运算式，参加运算的数据可以是常量，也可以是代表单元格中数据的单元格地址，还可以是系统提供的一个函数（称作内部函数）。每个公式都能根据参加运算的数据计算出一个结果。 一、常量 常量是一个固定的值，从字面上就能知道该值是什么或它的大小是多少。公式中的常量有数值型常量、文本型常量和逻辑常量。 二、运算符 运

2、算符则指定了对数据进行的某种运算处理。Excel中，运算符根据参与运算的性质分为算术运算符、比较运算符、文字连接符等3类。,6.1 Excel 2003公式的使用,6.1.1 公式的概念,（1）算术运算符 算术运算符用来对数值进行算术运算，结果还是数值。Excel中的算术运算符及其含义如下表。,6.1 Excel 2003公式的使用,6.1.1 公式的概念,（2）比较运算符 比较运算符用来比较两个文本、数值、日期、时间的大小，结果是一个逻辑值（TRUE或FALSE）。比较运算的优先级比算术运算的低。比较运算符及其含义见下表。（排序比较规则）,6.1 Excel 2003公式的使用,6.1.1

3、公式的概念,（3）文字连接符 文字连接符只有一个“&”，是双目运算符，用来连接文本或数值，结果是文本类型。文字连接的优先级比算术运算符的低，但比比较运算符的高。例如： “计算机“ & “应用“，其结果是“计算机应用“。 12&34，其结果是“1234“。 “总成绩是“ & 543，其结果是“总成绩是543“。 “总分是“ & 87+88+89，其结果是“总分是264“。,6.1 Excel 2003公式的使用,6.1.2 公式的输入,在Excel 2003中，可直接输入公式，直接输入公式的过程与单元格内容编辑的过程大致相同，不同之处如下： （1）公式必须以英文等于号“=”开始，然后再输入公式。

4、 （2）输入完公式后，单元格中显示的是公式的计算结果。 输入公式时，应特别注意以下问题。 （1）常量、单元格引用、函数名、运算符等必须是英文符号。 （2）公式中只允许使用小括号“( )”，且必须是英文的小括号；括号必须成对出现，并且配对正确。 错误代码（P115）,6.1 Excel 2003公式的使用,6.1.3 公式的修改,通常，在单元格中用户只能看到公式的计算结果，单击相应的单元格，在编辑框内就可以看到相应的公式 。 双击存放公式的单元格，插入点光标位于该单元格内，单元格和编辑框内都可看到相应的公式，可直接在单元格内修改其中的公式 。,6.1 Excel 2003公式的使用,6.1.4

5、公式的填充,填充公式与填充单元格数据的方法大致相同。不同的是，若填充的公式中含有单元格地址（即使用单元格中的数据）时，系统会根据目标单元格与原始单元格的位移，自动调整原始公式中的单元格地址，并在填充公式后，填充的单元格或单元格区域中显示公式的计算结果。,6.1 Excel 2003公式的使用,实训,产品成本计算 家电商场商品销售表,6.3 Excel 2003的单元格引用,6.3.1 相对引用,在相对引用方式中，所引用的单元格地址是按“列标+行号”格式表示的。例如，A1、B5等。 在相对引用中，如果将公式（或函数）复制或填充到其他单元格中，系统会根据目标单元格与原始单元格的位移，自动调整原始公

6、式中的单元格地址的行号与列标。,6.3 Excel 2003的单元格引用,6.3.2 绝对引用,在绝对引用方式中，所引用的单元格地址是按“$列标+$行号”格式表示的。例如，$A$1、$B$5等。 与相对引用相反，若将采用相对引用的公式（或函数）复制或填充到其他单元格中，其中的单元格引用地址不会随着移动的位置而自动产生相应的变化，是“完全”复制。,6.3 Excel 2003的单元格引用,6.3.3 混合引用,在混合引用方式中，所引用的单元格地址是按“$列标+行号”或“列标+$行号”的格式表示的。例如，$A1、B$5等。 与相对引用和绝对引用相比，若将采用混合引用的公式（或函数）复制或填充到其他

7、单元格中，前面带有“$”号的列号或行号的部分不会随着移动的位置而自动产生相应的变化，不带有“$”号的列号或行号的部分会随着移动的位置而自动产生相应的变化。,6.3 Excel 2003的单元格引用,6.3.4 区域引用,单元格区域地址也叫单元格区域引用。区域引用常见的格式是通过如下的区域运算符将所要引用的单元格区域表示出来。 （1）冒号“：”运算符 冒号“：”运算符按“左上角单元格引用:右下角单元格引用”的形式表示一个矩形的单元格区域。 例如，“A1:C3”，表示以A1为左上角C3为右下角的矩形区域内的全部单元格。 （2）逗号“，”运算符 逗号“，”运算符用于将指定的多个引用合并为一个引用。例

8、如“A1:B2,C1:C3”，表示A1:B2和C1:C3两个单元格区域。 （3）空格“ ”运算符 空格“ ”运算符表示对两个引用的单元格区域的重叠部分的引用。例如“A1:C3 B2:D4”，即B2:C3。,6.3 Excel 2003的单元格引用,引用 的分类,一维引用：A1:A10、C2:G2 二维引用：A1:C15 三维引用：Sheet1:Sheet3!A1,6.3 Excel 2003的单元格引用,数组的概念,由文本、数值、日期、逻辑、错误值等元素组成的集合 常量数组和内存数组,数组的分类,一维数组：行数组（垂直数组）和列数组（水平数组）例如1,2,3,4或“A”;”B”;”C” 二维数

9、组：多行多列数组 0，“差”；60，“中”；90，“优”,6.2 Excel 2003函数的使用,6.2.1 函数的概念,在Excel中，函数是系统预先设置的用于执行数学运算、文本处理或者逻辑计算的一系列计算公式或计算过程，用户无需了解这些计算公式或计算过程是如何实现的，只要掌握函数的功能和如何使用方法即可。 一、函数的格式 函数名（参数1，参数2，） 二、函数的参数 三、函数的返回值,6.2 Excel 2003函数的使用,6.2.2 常用函数,1、SUM()函数 2、AVERAGE()函数 3、COUNT()/countA()函数 4、MAX()和MIN()函数 5、LEFT()和RIGH

10、T()函数 5、IF()函数 6、MOD(),6.2 Excel 2003函数的使用,6.2.3 函数的输入,一、直接输入 单击存放函数返回值的单元格，使其成为活动单元格；依次输入等号、函数名、左括号、具体参数、右括号；按回车键确认函数的输入，此时单元格中会显示该函数的计算结果。 二、利用【粘贴函数】 由于Excel中提供了大量的函数，并且许多函数不经常使用，用户很难记住它们的参数，因此系统提供了【粘贴函数】方法，按照给出的提示逐步选择需要的函数及其相应的参数 。,6.2 Excel 2003函数的使用,常用引用函数,一、OFFSET函数 将源引用根据指定的行、列偏移来产生新的单元格引用 语法

11、：OFFSET(基点;行数,列数,高度,宽度) 特征： “引用基点”只能是单元格引用。而不能是数组 高度，宽度用于指定目标的单元格区域大小，如果不指定，则目标引用尺寸与源引用相同 高度，宽度允许用户使用负数作为参数,OFFSET(reference,rows,cols,height,width) Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。 Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 ref

12、erence 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。 Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。 Height 高度，即所要返回的引用区域的行数。Height 必须为正数。 Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。,6.2 Excel 2003函数的使用,常用引用函数,OFFSET函数一个疑问,如果统计表格中最后五天产品的平均销量

13、？ 函数公式： =AVERAGE(OFFSET($B$1,COUNTA(B:B)-5,2,5) 或公式： =AVERAGE(OFFSET($D$1,COUNTA(B:B)-1,-5),6.2 Excel 2003函数的使用,常用引用函数,二、INDEX函数 Index(数据表,行号,列号,区域序号) 数据表为引用时，结果返回为单元格引用 当数据表使用联合区域时，还可以通过区域序号返回子区域-少用 Index(数组,行号,列号) 数据表为数组时，结果返回为数组中的具体值 注意事项 通常情况下，行号，列号参数不支持使用数组,当函数 INDEX 的第一个参数为数组常量时，使用数组形式。 INDEX(

14、array,row_num,column_num) Array 为单元格区域或数组常量。 如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选参数。 如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。 Row_num 数组中某行的行号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。 Column_num 数组中某列的列标，函数从该列返回数值。如果省略 column_num，则必须有 row_num。,说明 如果同时使用参数 row_num 和

15、 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格中的值。 如果将 row_num 或 column_num 设置为 0，函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式 （数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号 ( ) 中。按 Ctrl+Shift+Enter 可以输入数组公式。）形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。 Row_num

16、 和 column_num 必须指向数组中的一个单元格；否则，函数 INDEX 返回错误值 #REF!。,返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一选定区域。 INDEX(reference,row_num,column_num,area_num) Reference 对一个或多个单元格区域的引用。 如果为引用输入一个不连续的区域，必须将其用括号括起来。 如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,column_num)。 R

17、ow_num 引用中某行的行号，函数从该行返回一个引用。 Column_num 引用中某列的列标，函数从该列返回一个引用。 Area_num 选择引用中的一个区域，返回该区域中 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，则函数 INDEX 使用区域 1。,例如，如果引用描述的单元格为 (A1:B4,D1:E4,G1:H4)，则 area_num 1 为区域 A1:B4，area_num 2 为区域 D1:E4，而 area_num 3 为区域 G1:H4。 说明 reference 和 area_

18、num 选择了特定的区域后，row_num 和 column_num 将进一步选择特定的单元格：row_num 1 为区域的首行，column_num 1 为首列，以此类推。函数 INDEX 返回的引用即为 row_num 和 column_num 的交叉区域。 如果将 row_num 或 column_num 设置为 0，函数 INDEX 分别返回对整列或整行的引用。 Row_num、column_num 和 area_num 必须指向 reference 中的单元格；否则，函数 INDEX 返回错误值 #REF!。如果省略 row_num 和 column_num，函数 INDEX 返回由

19、 area_num 所指定的引用中的区域。 函数 INDEX 的结果为一个引用，且在其他公式中也被解释为引用。,6.2 Excel 2003函数的使用,制作工资条,二、INDEX函数-实例 制作工资条,小结,Offset函数 函数语法 函数应用：数据排版、生成工资条、动态引用、动态下拉菜单等 Index函数 数据范围为支持数组 行、列参数不支持数组来生成内存数组,6.2 Excel 2003函数的使用,常用引用函数,三、LOOKUP函数 语法：LOOKUP(查找值,查找范围,目标范围) 通常情况下查找范围的首列或首行要求升序排列 四、VLOOKUP函数 语法：VLOOKUP(查找值,数据表,列

20、序号,匹配条件) 匹配条件 true or false（1 or 0）0代表精确查找，1代表近似查找 支持通配符进行查找,LOOKUP和VLOOKUP的常规用法,员工信息查询 利用LOOKUP进行查找 利用VLOOKUP函数进行查找 多条件区间判断取值,两个疑问,Lookup查找范围，必须升序排列吗？ 使用Vlookup实现逆向（从右向左）查询？,定位函数-MATCH函数,Match（查找值，查找范围（一维），匹配条件） 匹配条件：使用-1，0，1来确定查找模式（降序,无序,升序） 结果返回为查找值在范围中的位置序号 用法提醒 通常情况下，match函数和index组合应用实现类似VLOOKU

21、P函数的应用，使用更加灵活。 思考 Match函数能否实现逆向查找(从右向左)？,MATCH(lookup_value,lookup_array,match_type) Lookup_value 为需要在数据表中查找的数值。 Lookup_value 为需要在 Lookup_array 中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。 Lookup_value 可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。 Lookup_array 可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。 Match_type 为数字 -1、0 或 1。Match_type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。,查找引用函数的综合应用,产品规格价格查询 二级动态下拉菜单,