第4章+中文Excel+2003(2)

中文Excel2000授课老师：曾莉4.4公式与函数•4.4.1公式中坐标的引用•4.4.2工作表函数4.4.1.公式中单元格的引用一、概念•相对引用：公式中引用的单元格地址在公式复制、移动时自行调整。•绝对引用：公式中引用的单元格地址在公式复制、移动时不会改变。•混合引用：公式中引用的单元格地址在公式复制、移动时部分发生改变。二、表示(1)相对引用：公式中,用行、列地址作单元格引用的名称，例如A2,E8。(2)绝对引用：公式中,用行、列地址前加$作为单元格引用的名称,例如$B$3,$F$4。(3)混合引用：公式中有绝对引用部分，又有相对引用部分,例如A$2,$E8。4.4.2工作表函数•工作表函数（也称为内部函数）是Excel系统所提供的能在工作表中运用的计算工具。1.函数及其在公式中的使用（1）一般格式：函数名(参数1，参数2，...)函数名:特定的名称，用来说明函数要执行的运算。例如：SUM，COUNT，AVERAGE等。参数:是函数用以生成新值或完成运算的常量或单元格。函数值:函数返回的结果称函数值。1.函数及其在公式中的使用（2）函数的使用说明:参数必须出现在函数名右边的括号里面,没有参数时也必须有一对括号。各参数之间用逗号分隔。参数可以是常数、公式或函数。如果一个函数的参数本身也是一个函数,则称函数的嵌套。Excel中最多可嵌套7层函数。例如：=SUM（3，MAX（A3：A12））。4、常用函数•Excel函数分类：•大致分为十类：财务函数、日期和时间函数、数学与三角函数、统计函数、查找与引用函数、数据库函数、文本函数、逻辑函数、信息函数、工程函数1.INT(number)向下求整函数【功能】取不大于参数,且又最接近参数的整数例题：=INT(3.99)结果是3=INT(-4.05)结果是-5数学与三角函数Excel2000的工作表函数2.MOD(number,divisor)求余函数【功能】返回number除以divisor所得余数，结果的正负号与divisor相同。例题：MOD(7,3)=1MOD(7,-3)=-2MOD(-7,3)=2MOD的确切含义是：MOD（n,d）=n-d*INT(n/d)数学与三角函数Excel2000的工作表函数数学与三角函数3.随机数函数—RAND【格式】RAND()【功能】返回一个0～1之间的随机数。【说明】无参数。返回a~b的随机整数：=a+INT(RAND()*(b-a+1))【例】=20+INT(RAND()*81)返回一个20～100之间的随机整数。Excel2000的工作表函数4.ROUND四舍五入【格式】ROUND(number,num_digits)【功能】返回number四舍五入后保留num_digits位小数的值。【例】=ROUND(34.546,2)的值为34.55数学与三角函数Excel2000的工作表函数Excel2000的工作表函数5.求和函数—SUM【格式】SUM(number1,number2,…)【功能】返回number1，number2，….的和。【说明】由数字构成的字符串常量将被转换成相应的数值，逻辑常量FALSE被转换成数值0，TRUE被转换成数值1。而当参数为单元或区域坐标时，对其中上述值不予转换，一律作0处理。数学与三角函数Excel2000的工作表函数【格式】SUMIF(range,criteria,sum_range)【功能】返回区域range内满足条件criteria的单元格所顺序对应的区域sum_range内的单元格中的数值之和。【说明】range，用于进行条件测试的单元格区域。criteria，为用双引号括起来的比较条件式，确定哪些单元格将被相加求和的条件。sum_range，为实际进行求和的区域，必须与区域range的规格相同。它为可选参数，其默认值为range。6.条件求和函数—SUMIF数学与三角函数6.条件求和函数—SUMIFcriteria（比较条件式）用于描述一个条件，其一般形式为关系运算符后面跟一个常量数据构成。例如：=100、教授、=男。①关系运算符“=”可以省略。②字符型常量可用“*”表示一串任意字符、用“?”表示一个任意字符。例如：姓陈的人名可以表示为“陈*”。题目1：在student.XLS的成绩信息工作表的C13输入SUMIF函数，求姓李的学生的英语总分。=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【格式】LEFT(text,num_chars)【功能】返回字符串text左边num_chars个字符的子字符串。【说明】text是用于截取的字符串，num_chars为任意非负整数，用以指定所要截取的字符串长度，它的默认值为1。【例】取字符串“SalePrice”的最左边4个字符=LEFT(“SalePrice”,4)的结果是”Sale”文本函数Excel2000的工作表函数2.右截取子串函数—RIGHT【格式】RIGHT(text,num_chars)【功能】返回字符串text右边num_chars个字符的子字符串。【说明】同LEFT函数。【例】取字符串“SalePrice”的最右边5个字符=RIGHT(“SalePrice”,5)的结果是“Price”文本函数Excel2000的工作表函数3.任意截取子串函数—MID【格式】MID(text,start_num,num_chars)【功能】返回字符串text从start_num起num_chars个字符的子字符串。【说明】start_num用以指定从text字符串中的第几个字符开始截取，text中的第一个字符的位置为1。【例】从字符串“TheSalePrice”的第5个字符开始取其中4个字符：=MID(“TheSalePrice”,5,4)的结果是“Sale”文本函数Excel2000的工作表函数1.系统日期函数—TODAY【格式】TODAY()【功能】返回计算机系统内部时钟现在日期的序列数或是显示当前的日期。(取决于对单元格格式的设置)Excel2000的工作表函数日期与时间函数2.系统日期和时间函数—NOW【格式】NOW()【功能】返回计算机系统内部时钟现在的日期和时间（或者对应的序列数）。【说明】设置其“单元格格式”能改变其显示的格式。Excel2000的工作表函数日期与时间函数Excel2000的工作表函数逻辑函数1.逻辑“与”函数—AND【格式】AND(logical1,logical2,…)【功能】当所有参数的值都是TRUE时，返回TRUE；否则返回FALSE。【说明】Logical1，logical2，…，是1到30个结果为TRUE或FALSE的表达式、逻辑值、数组或者引用，多以关系运算表达式的形式出现。Excel2000的工作表函数逻辑函数1.逻辑“与”函数—AND【例】=AND(24,75)的值为TRUE=AND(TRUE,FALSE)的值为FLASE=AND(-12)的值为TRUEExcel2000的工作表函数逻辑函数2.逻辑“或”函数—OR【格式】OR(logical1,logical2,…)【功能】当所有参数的值都是FALSE时，返回FALSE；否则返回TRUE。【说明】同AND函数。【例】=OR(1+1=1,2+2=5)的值为FALSE=OR(TRUE,FALSE)的值为TRUEExcel2000的工作表函数逻辑函数3.条件选择函数—IF(重点掌握)这是一个非常重要的函数。所有“根据不同情况选择不同表达式进行计算”的操作都是由IF函数完成。【格式】IF(logical_test,value_if_true,value_if_false)【功能】logical_test返回value_if_true的结果返回value_if_false的结果Excel2000的工作表函数逻辑函数3.条件选择函数—IF(重点掌握)【例】1.在综合成绩.xls的sheet1工作表中H2:H11输入公式，如果大学英语的分数大于等于60，在H列显示“Pass”，否则显示“Fail“。=IF(G2=60,“Pass”,“Fail“)2.根据当前时间值来判断是“白天”还是“晚上”；=IF(AND(HOUR(NOW())=6,HOUR(NOW())=18),“day”,“night”)Excel2000的工作表函数逻辑函数3.条件选择函数—IF一个IF函数可实现“二者选其一”的运算，若要实现“多者选其一”的运算，则要用嵌套的IF函数来完成。即参数value_if_true或value_if_false本身又是一个IF函数。IF函数的嵌套最多只能有7层。Excel2000的工作表函数逻辑函数3.条件选择函数—IF【例】假定在B3中存放着一个考试成绩。若B3中的数大于89，返回A；若B3中的数在80至89之间，返回B；若B3中的数在70至79之间，返回C；若B3中的数在60至69之间，返回D；若B3中的数小于60，返回F。可以用嵌套的IF函数：=IF(B289,A“,IF(B279,B“,IF(B269,C“,IF(B259,D“,F“))))Excel2000的工作表函数统计函数1.计数函数1—COUNTA【格式】COUNTA(value1,value2,…)【功能】返回参数组中所有参数的个数，对于区域参数则返回其中非空白单元的数目。【说明】所要计数的可以是任何类型的数据，参数一般用区域的形式。2.计数函数2—COUNT【格式】COUNT(value1,value2,…)【功能】返回参数组中数值的个数，对于区域参数则返回其中数据为数值型的单元数目。【说明】参数一般用区域的形式，除非执意要计算区域中数据为数值型的单元数目时使用，一般情况下都使用COUNTA。Excel2000的工作表函数统计函数3.条件计数函数—COUNTIF【格式】COUNTIF(range,criteria)【功能】返回区域range中数据取值符合条件criteria的单元格数目。【说明】range为任意区域，在此区域中进行条件测试并计数；criteria与SUMIF函数中的要求相同。【例】见工作表函数.xls的count工作表。Excel2000的工作表函数统计函数4.求平均值函数—AVERAGE【格式】AVERAGE(number1,number2,…)【功能】返回诸参数的平均值（算术平均值）。【说明】参数一般用区域的形式，其中含文字、逻辑值的单元格和空白单元格不被计入。【例】用公式计算教师工资.XLS中所有教师的平均基本工资，教师工资在H3:H32区域。=AVERAGE(H3:H32)Excel2000的工作表函数统计函数5.求最大值函数—MAX【格式】MAX(number1,number2,…)【功能】返回参数表中的最大值。【说明】参数一般用区域的形式。【例】用公式计算教师工资.XLS中的教师最高基本工资：=MAX(H3:H32)Excel2000的工作表函数统计函数6.求最小值函数—MIN【格式】MIN(number1,number2,…)【功能】返回参数表中的最小值。【说明】参数一般用区域的形式。【例】用公式计算表教师工资.XLS的教师最低基本工资：=MIN(H3:H32)Excel2000的工作表函数统计函数7.频率分布函数—FREQUENCY（难点）所谓频率分布问题是对于一组数据和一组数值分段，求该组数据出现在各个分段区间的频率或个数。【格式】FREQUENCY(data