《第4章中文Excel2003》由会员分享，可在线阅读，更多相关《第4章中文Excel2003（47页珍藏版）》请在金锄头文库上搜索。
1、中文中文Excel2000Excel2000授课老师：曾莉授课老师：曾莉4.4 公式与函数4.4.1 公式中坐标的引用4.4.2 工作表函数4.4.1.公式中单元格的引用公式中单元格的引用一、一、 概念概念相对引用相对引用：公式中引用的单元格地址在：公式中引用的单元格地址在公式复制、移动时自行调整。公式复制、移动时自行调整。绝对引用绝对引用：公式中引用的单元格地址在：公式中引用的单元格地址在公式复制、移动时不会改变。公式复制、移动时不会改变。混合引用混合引用：公式中引用的单元格地址在：公式中引用的单元格地址在公式复制、移动时部分发生改变。公式复制、移动时部分发生改变。二、表示二、表示(1)(1
2、)相对引用：公式中相对引用：公式中, ,用用行、列地址行、列地址作作单元格引用的名称，例如单元格引用的名称，例如 A2,E8A2,E8。(2)(2)绝对引用：公式中绝对引用：公式中, ,用用行、列地址前行、列地址前加加$ $作为单元格引用的名称作为单元格引用的名称, ,例如例如 $B$3,$F$4$B$3,$F$4。(3)(3)混合引用：公式中有绝对引用部分，混合引用：公式中有绝对引用部分，又有相对引用部分又有相对引用部分, ,例如例如 A$2, $E8A$2, $E8。4.4.2 工作表函数工作表函数（也称为内部函数）是工作表函数（也称为内部函数）是Excel系统所提供的能在工作表中运用的计
3、算系统所提供的能在工作表中运用的计算工具。工具。1.1.函数及其在公式中的使用函数及其在公式中的使用（1）一般格式一般格式：函数名函数名函数名函数名( (参数参数参数参数1 1，参数，参数，参数，参数2 2，.).) 函数名函数名: :特定的名称，用来说明函数要执行的运算。特定的名称，用来说明函数要执行的运算。例如：例如：SUMSUM，COUNTCOUNT，AVERAGEAVERAGE等。等。 参数参数: :是函数用以生成新值或完成运算的常量或单是函数用以生成新值或完成运算的常量或单元格。元格。 函数值函数值: :函数返回的结果称函数值。函数返回的结果称函数值。1.1.函数及其在公式中的使用函
4、数及其在公式中的使用（2 2）函数的使用说明）函数的使用说明: : 参数参数参数参数必须出现在函数名右边的括号里面必须出现在函数名右边的括号里面, ,没有参没有参数时也必须有数时也必须有一对括号一对括号一对括号一对括号。 各参数之间用各参数之间用逗号逗号逗号逗号分隔。分隔。 参数参数参数参数可以是可以是常数、公式或函数常数、公式或函数常数、公式或函数常数、公式或函数。如果一个函数的。如果一个函数的参数本身也是一个函数参数本身也是一个函数, ,则称则称函数的嵌套函数的嵌套函数的嵌套函数的嵌套。ExcelExcel中中最多可嵌套最多可嵌套7 7层函数。层函数。 例如：例如：=SUM=SUM（3 3
5、，MAXMAX（A3A3：A12A12）。）。）。）。4、常用函数Excel函数分类：大致分为十类：财务函数、日期和时大致分为十类：财务函数、日期和时间函数、数学与三角函数、统计函数、间函数、数学与三角函数、统计函数、查找与引用函数、数据库函数、文本查找与引用函数、数据库函数、文本函数、逻辑函数、信息函数、工程函函数、逻辑函数、信息函数、工程函数数1.INT(number) 向下求整函数向下求整函数 【功能】取不大于参数取不大于参数取不大于参数取不大于参数, , , ,且又且又且又且又最最最最接近参数的整数接近参数的整数接近参数的整数接近参数的整数 例题：=INT(3.99)结果是3 =INT
6、(-4.05)结果是-5数学与三角函数Excel 2000 Excel 2000 的工作表函数的工作表函数 2. MOD(number,divisor) 求余函数求余函数 【功能功能】返回返回返回返回 number number number number 除以除以除以除以 divisor divisor divisor divisor 所得所得所得所得余数余数余数余数，结果的正负号与结果的正负号与结果的正负号与结果的正负号与 divisor divisor divisor divisor 相同。相同。相同。相同。 例题：例题：MOD(7,3)=1 MOD(7,-3)=-2 MOD(-7,3)
7、=2 MOD MOD的确切含义是：的确切含义是： MOD（n,d）=n-d*INT(n/d)数学与三角函数数学与三角函数Excel 2000 Excel 2000 的工作表函数的工作表函数 数学与三角函数3. 随机数函数随机数函数 RAND【格式格式】RAND( )【功能功能】返回一个返回一个01之间的随机数。之间的随机数。【说明说明】无参数。无参数。 返回返回ab的随机整数：的随机整数： =a+INT(RAND()*(b-a+1)【例例】= 20 + INT( RAND( ) * 81 ) 返回一个返回一个20100 之间的随机整数。之间的随机整数。Excel 2000 Excel 2000
8、 的工作表函数的工作表函数 4.4. ROUND 四舍五入四舍五入【格式格式】ROUND( number , num_digits )【功功能能】返返回回 number 四四舍舍五五入入后后保保留留 num_digits 位小数的值。位小数的值。 【例例】=ROUND(34.546,2)的值为的值为34.55数学与三角函数数学与三角函数Excel 2000 Excel 2000 的工作表函数的工作表函数 Excel 2000 Excel 2000 的工作表函数的工作表函数 5. 求和函数求和函数 SUM【格式格式】SUM( number1,number2, )【功能功能】返回返回 number
9、1，number2，. 的和。的和。【说说明明】由由数数字字构构成成的的字字符符串串常常量量将将被被转转换换成成相相应应的的数数值值，逻逻辑辑常常量量 FALSE 被被转转换换成成数数值值0，TRUE 被被转转换换成成数数值值1。而而当当参参数数为为单单元元或或区区域域坐标时，对其中上述值不予转换，一律作坐标时，对其中上述值不予转换，一律作0处理。处理。数学与三角函数Excel 2000 Excel 2000 的工作表函数的工作表函数 【格式格式】SUMIF( range , criteria , sum_range )【功功能能】返返回回区区域域 range 内内满满足足条条件件 crite
10、ria 的的单单元元格格所所顺顺序序对对应应的的区区域域 sum_range 内内的的单单元元格格中中的的数数值值之和。之和。【说说明明】range，用用于于进进行行条条件件测测试试的的单单元元格格区区域域。criteria，为为用用双双引引号号括括起起来来的的比比较较条条件件式式，确确定定哪哪些些单单元元格格将将被被相相加加求求和和的的条条件件。sum_range ，为为实实际际进进行求和的区域，必须与区域行求和的区域，必须与区域range的规格相同。的规格相同。 它为可选参数，其默认值为它为可选参数，其默认值为 range 。 6. 条件求和函数条件求和函数 SUMIF数学与三角函数6.
11、条件求和函数条件求和函数 SUMIF criteria（比比较较条条件件式式）用用于于描描述述一一个个条条件件，其其一一般般形形式式为为关关系系运运算算符符后后面面跟跟一一个个常常量量数数据据构构成成。例如：例如：=100、教授、教授、=男。男。 关系运算符关系运算符 “=” 可以可以省略省略。 字字符符型型常常量量可可用用“*”表表示示一一串串任任意意字字符符、用用“?”表表示示一一个个任任意意字字符符。例例如如：姓姓陈陈的的人人名名可可以以表示为表示为“陈陈*”。题目题目1：在：在student.XLS的成绩信息工作表的的成绩信息工作表的C13输输入入SUMIF函数，求姓李的学生的英语总分
12、。函数，求姓李的学生的英语总分。 = SUMIF(B2:B10,“=李*”, C2:C10 )题目题目2：打开教师工资：打开教师工资.XLS的的Sheet1工作表，完成工作表，完成下面的题目。下面的题目。【例】计算讲师基本工资的总和。计算讲师基本工资的总和。 = SUMIF(G3:G32,“讲师”, H3:H32)如果G5单元中的值为“讲师”，也可以使用： = SUMIF(G3:G32,G5, H3:H32)【例】计算年龄超过计算年龄超过40岁的教师的基本工资总和。岁的教师的基本工资总和。 =SUMIF(D3:D32,”40”, H3:H32)1. 左截取子串函数左截取子串函数 LEFT【格式
13、格式】LEFT( text , num_chars )【功功能能】返返回回字字符符串串 text 左左边边 num_chars 个个字字符符的的子子字符串。字符串。【说说明明】text 是是用用于于截截取取的的字字符符串串，num_chars 为为任任意意非非负负整整数数，用用以以指指定定所所要要截截取取的的字字符符串串长长度度，它它的的默认值为默认值为 1。 【例例】取字符串取字符串“Sale Price”的最左边的最左边4个字符个字符 = LEFT( “Sale Price”, 4 )的结果是的结果是”Sale”文本函数Excel 2000 Excel 2000 的工作表函数的工作表函数
14、2. 右截取子串函数右截取子串函数 RIGHT【格式格式】RIGHT( text , num_chars )【功功能能】返返回回字字符符串串 text 右右边边 num_chars 个个字字符的子字符串。符的子字符串。【说明说明】同同 LEFT 函数。函数。【例例】取字符串取字符串“Sale Price”的最右边的最右边5个字符个字符 =RIGHT( “Sale Price”, 5 )的结果是的结果是“Price”文本函数Excel 2000 Excel 2000 的工作表函数的工作表函数 3. 任意截取子串函数任意截取子串函数 MID【格式格式】MID( text , start_num ,
15、 num_chars )【 功功 能能 】 返返 回回 字字 符符 串串 text 从从 start_num 起起 num_chars 个字符的子字符串。个字符的子字符串。【说说明明】start_num 用用以以指指定定从从 text 字字符符串串中中的的第第几几个个字字符符开开始始截截取取，text 中中的的第第一一个个字字符符的的位位置置为为 1。 【例例】从从字字符符串串 “The Sale Price”的的第第5个个字字 开开始始取取其其中中4个个字字符符： = MID( “The Sale Price”, 5, 4 )的结果是的结果是 “ Sale”文本函数Excel 2000 Ex
16、cel 2000 的工作表函数的工作表函数 1. 系统日期函数系统日期函数 TODAY【格式格式】TODAY( )【功功能能】返返回回计计算算机机系系统统内内部部时时钟钟现现在在日日期期的的序序列列数数或或是是显显示示当当前前的的日日期期。 ( 取取决决于于对对单单元格格式的设置元格格式的设置)Excel 2000 Excel 2000 的工作表函数的工作表函数 日期与时间函数2. 系统日期和时间函数系统日期和时间函数 NOW【格式格式】NOW( )【功功能能】返返回回计计算算机机系系统统内内部部时时钟钟现现在在的的日日期期和时间（或者对应的序列数）。和时间（或者对应的序列数）。【说说明明】设
17、设置置其其“单单元元格格格格式式”能能改改变变其其显显示示的格式。的格式。Excel 2000 Excel 2000 的工作表函数的工作表函数 日期与时间函数Excel 2000 Excel 2000 的工作表函数的工作表函数 逻辑函数1. 逻辑逻辑“与与”函数函数 AND【格式格式】AND( logical1 , logical2 , )【功功能能】当当所所有有参参数数的的值值都都是是 TRUE 时时，返返回回 TRUETRUE ；否则返回；否则返回 FALSEFALSE 。【说说明明】Logical1，logical2，是是 1 到到 30 个个结结果果为为 TRUE 或或 FALSE 的
18、的表表达达式式、逻逻辑辑值值、数数组组或者引用或者引用，多以，多以关系运算表达式关系运算表达式关系运算表达式关系运算表达式的形式出现。的形式出现。Excel 2000 的工作表函数的工作表函数 逻辑函数1. 逻辑逻辑“与与”函数函数 AND【例例】= AND( 25 ) 的值为的值为 TRUE = AND( TRUE, FALSE ) 的值为的值为 FLASE = AND( -12 ) 的值为的值为 TRUEExcel 2000 Excel 2000 的工作表函数的工作表函数 逻辑函数2. 逻辑逻辑“或或”函数函数 OR【格式格式】OR( logical1 , logical2 , )【功功能
19、能】当当所所有有参参数数的的值值都都是是 FALSE 时时，返返回回 FALSE；否则返回；否则返回 TRUE。【说明说明】同同 AND 函数。函数。【例】= OR( 1+1=1, 2+2=5 ) 的值为的值为 FALSE = OR( TRUE, FALSE ) 的值为的值为 TRUEExcel 2000 Excel 2000 的工作表函数的工作表函数 逻辑函数3. 条件选择函数条件选择函数 IF( 重点掌握重点掌握) 这这是是一一个个非非常常重重要要的的函函数数。所所有有“根根据据不不同同情情况况选选择择不不同同表表达达式式进进行行计计算算”的的操操作作都都是是由由 IF 函数完成函数完成。
20、【格式】IF(logical_test, value_if_true , value_if_false)【功能】logical_logical_test返回返回返回返回 value_if_truevalue_if_true 的结果的结果的结果的结果返回返回返回返回 value_ifvalue_if_ false _ false 的结果的结果的结果的结果=TRUETRUE=FALSE=FALSEExcel 2000 Excel 2000 的工作表函数的工作表函数 逻辑函数3. 条件选择函数条件选择函数 IF( 重点掌握重点掌握) 【例例】1.在在综综合合成成绩绩.xls的的sheet1工工作作表
21、表中中H2:H11输输入入公公式式，如如果果大大学学英英语语的的分分数数大大于于等等于于60，在在H列显示列显示“Pass” ，否则显示，否则显示“Fail“ 。 = IF( G2 = 60 , “Pass” , “Fail“ ) 2.根根据据当当前前时时间间值值来来判判断断是是 “白白天天” 还还是是 “晚晚上上”； =IF(AND(HOUR(NOW()=6, HOUR(NOW()89 ,A“, IF( B279 ,B“, IF( B269 ,C“, IF( B259 ,D“ , F“ )Excel 2000 Excel 2000 的工作表函数的工作表函数 统计函数1. 计数函数计数函数 1
22、 COUNTA【格式格式】COUNTA( value1 , value2 , )【功功能能】返返回回参参数数组组中中所所有有参参数数的的个个数数，对对于于区域参数则返回其中区域参数则返回其中非空白单元非空白单元的数目。的数目。【说说明明】所所要要计计数数的的可可以以是是任任何何类类型型的的数数据据，参数一般用区域的形式。参数一般用区域的形式。2. 计数函数计数函数 2 COUNT【格式格式】COUNT( value1 , value2 , )【功功能能】返返回回参参数数组组中中数数值值的的个个数数，对对于于区区域域参数则返回其中数据为参数则返回其中数据为数值型数值型的单元数目。的单元数目。【说
23、说明明】参参数数一一般般用用区区域域的的形形式式，除除非非执执意意要要计计算算区区域域中中数数据据为为数数值值型型的的单单元元数数目目时时使使用用，一般情况下都使用一般情况下都使用 COUNTA 。Excel 2000 Excel 2000 的工作表函数的工作表函数 统计函数3. 条件计数函数条件计数函数 COUNTIF【格式格式】COUNTIF( range , criteria )【功功能能】返返回回区区域域 range 中中数数据据取取值值符符合合条条件件 criteria 的单元格数目。的单元格数目。【说说明明】range 为为任任意意区区域域，在在此此区区域域中中进进行行条条件件测测
24、试试并并计计数数；criteria 与与 SUMIF 函函数数中中的的要要求求相相同。同。【例例】见工作表函数见工作表函数.xls的的count工作表。工作表。Excel 2000 Excel 2000 的工作表函数的工作表函数 统计函数4. 求平均值函数求平均值函数 AVERAGE【格式格式】AVERAGE( number1 , number2 , )【功能功能】返回诸参数的平均值（算术平均值）。返回诸参数的平均值（算术平均值）。【说说明明】参参数数一一般般用用区区域域的的形形式式，其其中中含含文文字字、逻辑值的单元格和空白单元格不被计入逻辑值的单元格和空白单元格不被计入。【例例】用用公公式
25、式计计算算教教师师工工资资.XLS中中所所有有教教师师的的平平均基本工资，教师工资在均基本工资，教师工资在H3:H32区域。区域。 = AVERAGE( H3:H32 )Excel 2000 Excel 2000 的工作表函数的工作表函数 统计函数5. 求最大值函数求最大值函数 MAX【格式格式】MAX( number1 , number2 , )【功能功能】返回参数表中的最大值。返回参数表中的最大值。【说明说明】参数一般用区域的形式。参数一般用区域的形式。【例例】用用公公式式计计算算教教师师工工资资.XLS中中的的教教师师最最高高基基本工资：本工资： = MAX( H3:H32 )Excel
26、 2000 Excel 2000 的工作表函数的工作表函数 统计函数6. 求最小值函数求最小值函数 MIN【格式格式】MIN( number1 , number2 , )【功能功能】返回参数表中的最小值。返回参数表中的最小值。【说明说明】参数一般用区域的形式。参数一般用区域的形式。【例例】用用公公式式计计算算表表教教师师工工资资.XLS的的教教师师最最低低基基本工资：本工资： = MIN( H3:H32)Excel 2000 Excel 2000 的工作表函数的工作表函数 统计函数Excel 2003 Excel 2003 的工作表函数的工作表函数 统计函数7. 频率分布频率分布函数 FREQ
27、UENCY（难点）难点） 所谓频频率率分分布布问题是对于一一组组数数据据和一一组组数数值值分分段段，求该组数据出现在各个分段区间的频率或个数。【格式】FREQUENCY( data_array, bins_array )【功能】以垂直数组垂直数组的方式返回一个频率分布。Excel 2003 Excel 2003 的工作表函数的工作表函数 统计函数7. 频率分布函数 FREQUENCY【说明】data_array 为要计算频率的数据所在区域； bins_array 为间隔点数据所在区域； (注注意意) 假设 BINS_array 中的一串数为 a、b、c、d代表间间隔隔点点，且满足a b c d
28、，则分为如下五个区间： （-,a、（a,b、（b,c、（c,d、（d,+） 而函数FREQUENCY就是返回data_array 在这五个区间所出现的次数。【例】统计教师工资年龄不超过不超过 25 岁、超过超过 25 但但不超过不超过 35 岁、超过超过 35 但不超过但不超过 50 岁及 50 岁岁以上以上各年龄段的人数。 在区域E12:E14中依次输入25、35、50 ； 选定区域F12:F15，输入公式： = FREQUENCY( E2:E9 , E12:E14 )或直接输入公式 ： = FREQUENCY( D3:D32 , 25,35,50 ) 按下Ctrl+Shift+Enter复
29、合键确认。1 修改数组公式步骤修改数组公式步骤： 1）选定数组公式应用区域的任一单元格 2）执行“编辑/定位”，单击“定位条件”按钮 3）在“定位条件”对话框中选择“当前数组”单选钮，单击“确定” 4）按F2 ，为了去掉“”, 5）修改完成后，然后再按Ctrl+Shift+Enter复合键2 删除数组公式的步骤：删除数组公式的步骤：选定当前数组DELETE8.求第K个求最大值函数LARGE【格式格式】LARGE(array，k)【功能功能】返回某一数据集中的某个最大值。返回某一数据集中的某个最大值。【说说明明】Array为为需需要要从从中中查查询询第第k个个最最大大值值的的数数组组或或数数据据
30、区区域域，K为为返返回回值值在在数数组组或或数数据据单元格区域里的位置单元格区域里的位置(即名次即名次)。【例例】 【例例】如如果果B1=59、B2=70、B3=80、B4=90、 B5=89、 B6=84、 B7=92， 则则 公公 式式=LARGE(B1:B7，2)返回返回90。9.求第K个求最小值函数SMALL【格式格式】SMALL(array，k)【功能功能】返回某一数据集中的某个最返回某一数据集中的某个最小小值。值。【说说明明】Array为为需需要要从从中中查查询询第第k个个最最小小值值的的数数组组或或数数据据区区域域，K为为返返回回值值在在数数组组或或数数据据单元格区域里的位置单元
31、格区域里的位置(即名次即名次)。【例例】 【例例】如如果果B1=59、B2=70、B3=80、B4=90、 B5=89、 B6=84、 B7=92， 则则 公公 式式=SMALL(B1:B7，2)返回返回70。10. 返回排位函数返回排位函数RANK【格式】【格式】RANK(number，ref，order)【功功能能】 返返回回一一个个数数值值在在一一组组数数值值中中的的排排位位(如如果果数数据据清清单单已已经经排排过过序序了了，则则数数值值的的排排位位就就是是它当前的位置它当前的位置)。【说说明明】number是是需需要要计计算算其其排排位位的的一一个个数数字字；Ref是是包包含含一一组组
32、数数字字的的数数组组或或引引用用(其其中中的的非非数数值值型型参参数数将将被被忽忽略略)；order为为一一数数字字，指指明明排排位位的的方方式式。如如果果order为为0或或省省略略，则则按按降降序序排排列列的的数数据据清清单单进进行行排排位位。如如果果order不不为为零零，ref当作按升序排列的数据清单进行排位。当作按升序排列的数据清单进行排位。（1）VLOOKUP【格式格式】VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)【功能功能】在表格或数值数组的首列查找指在表格或数值数组的首列查找指定的数值，并由此返
33、回表格或数组当前行定的数值，并由此返回表格或数组当前行中指定列处的数值。中指定列处的数值。Excel 2000 Excel 2000 的工作表函数的工作表函数 查找与引用函数【说明说明】lookup_value为需要在数据表第一列中查找的为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。数值，它可以是数值、引用或文字串。table_array为需为需要在其中查找数据的数据表，可以使用对区域或区域要在其中查找数据的数据表，可以使用对区域或区域名称的引用。名称的引用。col_index_num为为table_array中待返回的匹中待返回的匹配值的列序号。配值的列序号。如果如果 range_lookup 为为 TRUE，则，则 table_array 的第一列中的数值必须按升序排列：的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否；否则，函数则，函数 VLOOKUP 不能返回正确的数值。如果不能返回正确的数值。如果 range_lookup 为为 FALSE，table_array 不必进行排不必进行排序。序。
