《第4章中文Excel2003.ppt》由会员分享，可在线阅读，更多相关《第4章中文Excel2003.ppt（47页珍藏版）》请在三一办公上搜索。
1、中文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
2、工作表函数,工作表函数（也称为内部函数）是Excel系统所提供的能在工作表中运用的计算工具。,1.函数及其在公式中的使用,（1）一般格式：函数名(参数1，参数2，.)函数名:特定的名称，用来说明函数要执行的运算。例如：SUM，COUNT，AVERAGE等。参数:是函数用以生成新值或完成运算的常量或单元格。函数值:函数返回的结果称函数值。,1.函数及其在公式中的使用,（2）函数的使用说明:参数必须出现在函数名右边的括号里面,没有参数时也必须有一对括号。各参数之间用逗号分隔。参数可以是常数、公式或函数。如果一个函数的参数本身也是一个函数,则称函数的嵌套。Excel中最多可嵌套7层函数。例如：=SU
3、M（3，MAX（A3：A12）。,4、常用函数,Excel函数分类：大致分为十类：财务函数、日期和时间函数、数学与三角函数、统计函数、查找与引用函数、数据库函数、文本函数、逻辑函数、信息函数、工程函数,INT(number)向下求整函数【功能】取不大于参数,且又最接近参数的整数 例题：=INT(3.99)结果是3=INT(-4.05)结果是-5,数学与三角函数,Excel 2000 的工作表函数,2.MOD(number,divisor)求余函数【功能】返回 number 除以 divisor 所得余数，结果的正负号与 divisor 相同。例题：MOD(7,3)=1 MOD(7,-3)=-2
4、 MOD(-7,3)=2 MOD的确切含义是：MOD（n,d）=n-d*INT(n/d),数学与三角函数,Excel 2000 的工作表函数,数学与三角函数,3.随机数函数 RAND【格式】RAND()【功能】返回一个01之间的随机数。【说明】无参数。返回ab的随机整数：=a+INT(RAND()*(b-a+1)【例】=20+INT(RAND()*81)返回一个20100 之间的随机整数。,Excel 2000 的工作表函数,4.ROUND 四舍五入【格式】ROUND(number,num_digits)【功能】返回 number 四舍五入后保留 num_digits 位小数的值。【例】=RO
5、UND(34.546,2)的值为34.55,数学与三角函数,Excel 2000 的工作表函数,Excel 2000 的工作表函数,5.求和函数 SUM,【格式】SUM(number1,number2,)【功能】返回 number1，number2，.的和。【说明】由数字构成的字符串常量将被转换成相应的数值，逻辑常量 FALSE 被转换成数值0，TRUE 被转换成数值1。而当参数为单元或区域坐标时，对其中上述值不予转换，一律作0处理。,数学与三角函数,Excel 2000 的工作表函数,【格式】SUMIF(range,criteria,sum_range)【功能】返回区域 range 内满足条
6、件 criteria 的单元格所顺序对应的区域 sum_range 内的单元格中的数值之和。【说明】range，用于进行条件测试的单元格区域。criteria，为用双引号括起来的比较条件式，确定哪些单元格将被相加求和的条件。sum_range，为实际进行求和的区域，必须与区域range的规格相同。它为可选参数，其默认值为 range。,6.条件求和函数 SUMIF,数学与三角函数,6.条件求和函数 SUMIF,criteria（比较条件式）用于描述一个条件，其一般形式为关系运算符后面跟一个常量数据构成。例如：教授、=男。关系运算符“=”可以省略。字符型常量可用“*”表示一串任意字符、用“?”表
7、示一个任意字符。例如：姓陈的人名可以表示为“陈*”。,题目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(te
8、xt,num_chars)【功能】返回字符串 text 左边 num_chars 个字符的子字符串。【说明】text 是用于截取的字符串，num_chars 为任意非负整数，用以指定所要截取的字符串长度，它的默认值为 1。【例】取字符串“Sale Price”的最左边4个字符=LEFT(“Sale Price”,4)的结果是”Sale”,文本函数,Excel 2000 的工作表函数,2.右截取子串函数 RIGHT【格式】RIGHT(text,num_chars)【功能】返回字符串 text 右边 num_chars 个字符的子字符串。【说明】同 LEFT 函数。【例】取字符串“Sale Pri
9、ce”的最右边5个字符=RIGHT(“Sale Price”,5)的结果是“Price”,文本函数,Excel 2000 的工作表函数,3.任意截取子串函数 MID【格式】MID(text,start_num,num_chars)【功能】返回字符串 text 从 start_num 起 num_chars 个字符的子字符串。【说明】start_num 用以指定从 text 字符串中的第几个字符开始截取，text 中的第一个字符的位置为 1。【例】从字符串“The Sale Price”的第5个字 开始取其中4个字符：=MID(“The Sale Price”,5,4)的结果是“Sale”,文本
10、函数,Excel 2000 的工作表函数,1.系统日期函数 TODAY【格式】TODAY()【功能】返回计算机系统内部时钟现在日期的序列数或是显示当前的日期。(取决于对单元格格式的设置),Excel 2000 的工作表函数,日期与时间函数,2.系统日期和时间函数 NOW【格式】NOW()【功能】返回计算机系统内部时钟现在的日期和时间（或者对应的序列数）。【说明】设置其“单元格格式”能改变其显示的格式。,Excel 2000 的工作表函数,日期与时间函数,Excel 2000 的工作表函数,逻辑函数,1.逻辑“与”函数 AND【格式】AND(logical1,logical2,)【功能】当所有参
11、数的值都是 TRUE 时，返回 TRUE；否则返回 FALSE。【说明】Logical1，logical2，是 1 到 30 个结果为 TRUE 或 FALSE 的表达式、逻辑值、数组或者引用，多以关系运算表达式的形式出现。,Excel 2000 的工作表函数,逻辑函数,1.逻辑“与”函数 AND【例】=AND(25)的值为 TRUE=AND(TRUE,FALSE)的值为 FLASE=AND(-12)的值为 TRUE,Excel 2000 的工作表函数,逻辑函数,2.逻辑“或”函数 OR【格式】OR(logical1,logical2,)【功能】当所有参数的值都是 FALSE 时，返回 FAL
12、SE；否则返回 TRUE。【说明】同 AND 函数。【例】=OR(1+1=1,2+2=5)的值为 FALSE=OR(TRUE,FALSE)的值为 TRUE,Excel 2000 的工作表函数,逻辑函数,3.条件选择函数 IF(重点掌握)这是一个非常重要的函数。所有“根据不同情况选择不同表达式进行计算”的操作都是由 IF 函数完成。【格式】IF(logical_test,value_if_true,value_if_false)【功能】,logical_test,返回 value_if_true 的结果,返回 value_if_ false 的结果,=TRUE,=FALSE,Excel 2000
13、 的工作表函数,逻辑函数,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”),Excel 2000 的工作表函数,逻辑函数,3.条件选择函数 IF,一个 IF 函数可实现“二者选其一”的运算，若要实现“多者选其一”的运算，则要用嵌套的 IF函数来完成。即参数 valu
14、e_if_true 或 value_if_false 本身又是一个 IF 函数。IF 函数的嵌套最多只能有 7 层。,Excel 2000 的工作表函数,逻辑函数,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“),Excel 2000
15、 的工作表函数,统计函数,1.计数函数 1 COUNTA【格式】COUNTA(value1,value2,)【功能】返回参数组中所有参数的个数，对于区域参数则返回其中非空白单元的数目。【说明】所要计数的可以是任何类型的数据，参数一般用区域的形式。,2.计数函数 2 COUNT【格式】COUNT(value1,value2,)【功能】返回参数组中数值的个数，对于区域参数则返回其中数据为数值型的单元数目。【说明】参数一般用区域的形式，除非执意要计算区域中数据为数值型的单元数目时使用，一般情况下都使用 COUNTA。,Excel 2000 的工作表函数,统计函数,3.条件计数函数 COUNTIF【格
16、式】COUNTIF(range,criteria)【功能】返回区域 range 中数据取值符合条件 criteria 的单元格数目。【说明】range 为任意区域，在此区域中进行条件测试并计数；criteria 与 SUMIF 函数中的要求相同。【例】见工作表函数.xls的count工作表。,Excel 2000 的工作表函数,统计函数,4.求平均值函数 AVERAGE【格式】AVERAGE(number1,number2,)【功能】返回诸参数的平均值（算术平均值）。【说明】参数一般用区域的形式，其中含文字、逻辑值的单元格和空白单元格不被计入。【例】用公式计算教师工资.XLS中所有教师的平均基
17、本工资，教师工资在H3:H32区域。=AVERAGE(H3:H32),Excel 2000 的工作表函数,统计函数,5.求最大值函数 MAX【格式】MAX(number1,number2,)【功能】返回参数表中的最大值。【说明】参数一般用区域的形式。【例】用公式计算教师工资.XLS中的教师最高基本工资：=MAX(H3:H32),Excel 2000 的工作表函数,统计函数,6.求最小值函数 MIN【格式】MIN(number1,number2,)【功能】返回参数表中的最小值。【说明】参数一般用区域的形式。【例】用公式计算表教师工资.XLS的教师最低基本工资：=MIN(H3:H32),Excel
18、 2000 的工作表函数,统计函数,Excel 2003 的工作表函数,统计函数,7.频率分布函数 FREQUENCY（难点）所谓频率分布问题是对于一组数据和一组数值分段，求该组数据出现在各个分段区间的频率或个数。【格式】FREQUENCY(data_array,bins_array)【功能】以垂直数组的方式返回一个频率分布。,Excel 2003 的工作表函数,统计函数,7.频率分布函数 FREQUENCY,【说明】data_array 为要计算频率的数据所在区域；bins_array 为间隔点数据所在区域；(注意)假设 BINS_array 中的一串数为 a、b、c、d代表间隔点，且满足a
19、 b c d，则分为如下五个区间：（-,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复合键确认。,1 修改数组公式步骤：1）选定数组公式应用区域的
20、任一单元格 2）执行“编辑/定位”，单击“定位条件”按钮 3）在“定位条件”对话框中选择“当前数组”单选钮，单击“确定”4）按F2，为了去掉“”,5）修改完成后，然后再按Ctrl+Shift+Enter复合键2 删除数组公式的步骤：选定当前数组DELETE,8.求第K个求最大值函数LARGE【格式】LARGE(array，k)【功能】返回某一数据集中的某个最大值。【说明】Array为需要从中查询第k个最大值的数组或数据区域，K为返回值在数组或数据单元格区域里的位置(即名次)。【例】【例】如果B1=59、B2=70、B3=80、B4=90、B5=89、B6=84、B7=92，则公式=LARGE(
21、B1:B7，2)返回90。,9.求第K个求最小值函数SMALL【格式】SMALL(array，k)【功能】返回某一数据集中的某个最小值。【说明】Array为需要从中查询第k个最小值的数组或数据区域，K为返回值在数组或数据单元格区域里的位置(即名次)。【例】【例】如果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是需要
22、计算其排位的一个数字；Ref是包含一组数字的数组或引用(其中的非数值型参数将被忽略)；order为一数字，指明排位的方式。如果order为0或省略，则按降序排列的数据清单进行排位。如果order不为零，ref当作按升序排列的数据清单进行排位。,（1）VLOOKUP【格式】VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)【功能】在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。,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 不必进行排序。,
