Excel电子表格讲解

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel,电子表格,教学内容,:,Excel,公式与函数,学习重点,:,公式与函数的复制、常用函数,学习方法,:,看书、上机操作、课后练习,授课时数,:,4,学时,应用公式,逻辑函数,公式编辑,Excel公式与函数,使用函数,综合练习,数学函数,统计函数,日期与时间函数,文本函数,财务函数,其他函数,应用公式,公式介绍,公式类型,数值公式,字符公式,逻辑公式,引用运算,公式中各运算符的优先级,公式介绍,与数学公式类似，对,数据,进行各种四则运算。,以“,=”,开头，后面接各种,表达式,例,:,求计算机 、英语和数学三门功课的总分,输入方法,:,在单元格中输入，“回车”确认,在编辑栏内输入并确认,公式类型,数值公式,:,对数字或单元格内的数字进行计算。,(,包括日期运算,),字符公式,:,对字符或单元格内的字符进行合并运算。,逻辑公式,:,进行比较运算，用于构造条件。,数值公式,算术运算符,:+,，,*,，,/,，,，,%,例,:,求总分,总分,平时成绩,*30%+,期末成绩,*,70%,求给定数,(,如,:2),的立方,注意：,EXCEL,中使用的标点符号都是英文状态的标点符号,字符公式,文本运算符,:,用“,&”,进行字符的连接运算。,例,:,产生,1,月份,12,月份,说明,:,对于,字符常量,要加,双引号,，单元格或区域内的数值数据可直接引用。,逻辑公式,比较运算符,:,=,字符串运算符,比较运算符,默认,:,根据公式中运算符的,特定顺序,从左到右计算公式。,Q:,如何更改求值的顺序？,公式编辑,公式的复制和移动,单元格地址引用,不同引用在公式复制时的变化,确定公式的引用形式,使用公式出错信息及原因,公式的复制和移动介绍,公式通常,引用,单元格计算数据，当对公式进行复制时就不是一般数据的简单复制。,例,:,求出所有同学的总分。,例,:,求出所有女同学的总分。,方法一,:,填充句柄,方法二,:,粘贴,方法三,:,选择性粘贴,单元格地址引用介绍,EXCEL,中单元格地址有不同的表示方法，可以直接用相应的地址表示。,单元格地址引用类型,(,三大地址,),相对地址,:,如,A3,A4:A8,等形式；,绝对地址,:,如,$A$3,$a$4:$A$4,等形式；,混合地址,:,如,$A3,A$3等形式。,引用转换,:F4,不同的引用方式只有在,公式复制,时有不同的作用。,不同引用在公式复制时的变化,公式单元格,(A1),目的单元格,(C10),A1= B1+ C1,C10=D10+E10,A1= $B$1+ $C$1,C10=$B$1+ $C$1,A1= $B1+ C$1,C10=$B10+E$1,确定公式的引用形式,例,:,利用公式复制产生九九乘法表,准备,:,在,B35:J35,中输入了,9,个数,19,在,A36:A44,中输入了,9,个数,19,要求,:,在,B2,单元格中输入一个公式，然后将该公式复制产生九九乘法表,使用公式出错信息及原因,出错信息,说明,#DIV/0!,公式中出现,0,作除数，可能是空单元格作除数引起的。,#NAME?,引用了,Excel,不能识别的文本，输入错误或使用了未定义的名称,#NULL!,在不相交的区域中指定了一个交集,#NUM!,在公式或函数中使用了不适当的数字，如不在函数定义域中的数字,#REF!,单元格引用无效，如引用的单元格不存在,#VALUE!,错误的参数或运算对象，如数据的类型出错,#,数值长度超过了单元格列宽,使用函数,函数基础,使用函数举例,函数的结构,函数基础,函数是一些预定义的公式，方便用户使用。,函数运算以“,=”,开头。,函数输入有两种方法,:,手工直接输入法,粘贴函数法,函数的结构,等号表示要输入一个公式或函数进行计算,函数名,左右括号表示函数的开始和结束,参数,SUM (a1,25,”,25,”,b5:d7),说明,:,参数的个数可以是,0,个或多个,不管函数是否有参数，其括号,(),不能少,参数可以是具体值、含有值的单元格地址或区域地址形式等。,使用函数举例,例,:,求三门功课的总分,利用求和函数,:SUM(),输入函数,:,直接输入法,:,SUM(B31:D31),利用函数向导,(,粘贴函数法,),数学与三角函数,ABS(),函数,INT(),函数,PI(),函数,ROUND(),函数,MOD(),函数,RAND(),函数,SQRT(),函数,SIN(),函数,综合应用,ABS(),函数,功能,:,返回参数的绝对值。,语法,:ABS(number,)Number,需要计算其绝对值的实数,Q:,如何用,IF,函数,代替？,INT(),函数,功能,:,返回接近于实数的最大整数。,语法,INT(number,),number :,需要进行取整处理的实数。,例如,:int(8.9)=? int(-8.9)=?,Q:,如何用公式返回,单元格中正实数的小数部分？,PI(),函数,功能,:,返回圆周率,的值,:,3.14159265358979,语法,:PI(),说明,:,此函数无须参数，但函数名后面的括号不能少。,ROUND(),函数,功能,:,对指定数值进行四舍五入并保留指定小数位数。,语法,:ROUND(number,，,num_digits,),number:,需要进行舍入的数字。,Num_digits,:,指定保留的小数位数。,例,:round(88.56,1)=88.6,round (88.56,-1)=90,MOD(),函数,功能,:,返回两数相除的余数。,语法,:MOD(number,，,divisor),Number,为被除数,、,divisor,为除数。,除数为,0,，则返回错误值,#div/0!,Q:,1222211,除以,111,？,RAND(),函数,功能,:,返回一个,0,1),均匀分布的,随机数,。,语法,:RAND(),无参数,如何将公式永久性地改为随机数？,保持编辑状态，然后按,F9,生成,a,，,b,之间的随机整数,:,INT(RAND()*(b,a,1),a),Q:,如何产生银行帐号的初始密码,(,六位,),SQRT(),函数,功能,:,返回给定,正数,的平方根。,语法,:SQRT(number,)number:,为负则函数返回错误值,:,#Num,！,Q:,如何用简单的公式代替？,A1,(1/2),SIN(),函数,功能,:,返回给定,角度,的正弦值。,语法,:SIN(number,),注意,:Number,以,弧度,表示。,Q:,角度如何转化成弧度？,SUM,函数,功能,:,返回参数表中所有参数之和。,语法,:SUM(number1,number2,，,),注意,:,如参数为逻辑值，则,TRUE,和,FALSE,分别转换为,1,和,0,；文本型数字转换为数值型数字；而引用的单元格中出现空白单元格、逻辑值、文本型数字该参数将被忽略。,SUMIF,函数,功能,:,根据指定条件对若干单元格求和。,语法,:SUMIF(range,criteria,sum_range,),例如,:,求所有工程师基本工资之和。,综合应用,例,:x=46.057,使用数学函数计算,:,统计函数,average(),函数,count (),countif,(),函数,average(),函数,功能,:,返回参数平均值,(,算术平均,),语法,:average(number1,number2,number30),Max() :,返回给定参数表中的最大值。,Min():,返回给定参数表中的最小值,例,:,求学生平均成绩、最高分和最低分。,count (),函数,功能,:,返回参数中,数值型数据,的个数。,语法,:count(value1,value30),Value1,:,是包含或引用各种类型数据的参数。,只有数字类型的数据才被计算。,Counta,:,返回参数中非空单元格个数,例,:,统计学生人数,countif,(),函数,功能,:,计算区域中,满足给定条件,的单元格的个数。,语法,: COUNTIF(range,criteria,),Range,为需要计算其中满足条件的单元格数目的单元格区域。,Criteria,为确定哪些单元格将被计算在内的条件。,例,:,统计男学生人数,文本函数,UPPER(),函数,LEFT(),函数,MID(),函数,LEN(),函数综合应用,UPPER(),函数,功能,:,将文本转化成大写字母。,语法,:UPPER(text,),text:,要转化为小写字母的文本串。,函数并不改变文本串中的非文本字符。,例,:,Upper(“Exercise1”,),结果,:EXERCISE1,LOWER( text ),函数,:,将文本转化成小写字母,LEFT(),函数,功能,:,返回字符串最左端的子字符串。,语法,:(text,num_chars,),text:,包含要提取,字符,的文字串。,Num_chars,:,指定函数所要返回的字符串长度,说明,:,省略,num-chars,则假设其值等于,1,。,例,:,取字符串,A1:”,信息,information”,left(A1),为信，,left(A1,5),为 信息,inf,RIGHT:,返回某一文本串中最右侧的子字符串。,MID(),函数,功能,:,从文本串的某一指定位置开始，返回指定长度的子字符串。,语法,:MID(text,start_num,num_chars,),text:,用于从中提取字符的文本串。,Start_num,:,要从文本串中提取字符的起始位置。,Num_chars,:,指定要返回字符的个数。,例,:,取字符串,A1:”,信息,information”,mid(A1,4,2),结果,:fo,LEN(),函数,功能,:,返回文本字符串中的字符数。,LENB,返回文本字符串中用于代表字符的字节数。,语法,: LEN(text,),Text,是要查找其长度的文本,(,包含空格,),。,例,:,取字符串,A1:”,信息,information”,=LEN(A1),结果,:13,=LENB(A1),结果,:15,说明,:,汉字占两个字节，英文字母占一个字节,综合应用,使用文本函数时常用到文本的连接符号“,&”,。,例,:,用公式将字符串左数第三个字符取出来。,例,:,用公式将字符串右数第三个字符取出来。,日期与时间函数,日期与时间函数介绍,date,与,time,函数,today,与,now,函数,year,、,month,与,day,函数,综合应用,日期与时间函数介绍,日期,/,时间数据本质上是数值型数据，该数字称为,序列数,。,序列数,:,小数点左边的数字表示日期，从,1900,年,1,月,1,日到所输入日期之间的总天数。,小数点右边的数字表示时间。,例如，序列数,367.5,表示,1901,年,1,月,1,日晚,12,时。,作用,:,常作为日期时间函数的返回值或参数。,date,与,time,函数,功能,:,返回某一特定,日期,的序列数。,语法,:DATE(year,month,day,),Year,介于,1900,到,9999,之间的一个数字。,Month,代表月份的数字。,Day,代表在该月份中第几天的数字。,例,:,date(2006,04,10),结果为日期,:2006/04/10,time(),:,返回某一特定,时间,的序列数，为小数,today,与,now,函数,功能,:,返回系统,当天日期,的序列数。,语法,:TODAY( ),无参数,例,:,today(),结果为日期,:2006/11/27,插入静态的日期和时间,:,当前日期,:Ctrl+;,当前时间,:Ctrl+Shift,+,；,当前日期和时间按,Ctrl+;,，然后按空格键，最后按,Ctrl+Shift,+;,now(),:,返回系统,当前日期和时间,的序列数。,year,、,month,与,day,函数,功能,:,返回指定日期的年数、月份数和天数。,语法,:YEAR(serial_number,),Serial_number,可以为数字、文本，如 “,15-Apr -1993”,或 “,4-15-1993”,。,例,1:,计算年龄,(,虚岁,),例,2:,用公式得到月份加,1,的日期,年龄的计算方法主要有：,虚岁年龄。中国在习惯上常用的年龄计算方法，按出生后所经历的日历年头计算,即生下来就算,1,岁,以后每过一次新年便增加,1,岁。一般按农历新年算，也有按公历算的。,日期时间函数综合应用,例,:,计算周岁年龄。,什么是周岁？,周岁年龄。又称实足年龄，指从出生到计算时为止，共经历的周年数或生日数。例如，,1990,年,7,月,1,日零时进行人口普查登记，一个,1989,年,12,月,15,日出生的婴儿，按虚岁计算是,2,岁，实际刚刚,6,个多月，还未过一次生日，按周岁计算应为不满,1,周岁，即,0,岁。周岁年龄比虚岁年龄常常小,1,2,岁,它能正确反映人们的实际生存年岁,是人口统计中最常用的年龄计算方法。,逻辑函数,IF(),函数,函数的嵌套,AND(),函数,OR(),函数,AND,、,OR,、,NOT,综合比较,IF(),函数,功能,:,对,比较条件式,Logical-test,进行测试，如果条件为逻辑值,TRUE,，则取,value-if-true,的值；否则取,value-if-false,的值。,语法,:IF(logical,-test,，,value-if-true,，,value-if-false),例,:,判断考试成绩是否及格,条件,:,考试成绩,=60,为及格，否则为不及格。,嵌套,IF,函数,在某些情况下，可能需要将某函数作为另一函数的,参数,使用。,例,:,判断成绩的等级,条件,:,考试成绩,=85,为优秀，,84,考试成绩,=60,为中等，,考试成绩,=60,为及格,否则为不及格。,OR(),函数,功能,:,在其参数组中，任何一个参数逻辑值为,TRUE,，即返回,TRUE,，全为,FALSE,，才返回,FALSE,。,语法,:OR(logical1,logical2,.),Logical1,logical2,.,为需要进行检验的,1,到,30,个条件，分别为,TRUE,或,FALSE,。,例,:,判断成绩是否及格,条件,:,机试和笔试任意,60,为不及格,否则为及格。,AND,、,OR,、,NOT,综合比较,财务函数,PV,函数,PMT,函数,PV,函数,功能：用于计算固定偿还能力下的可贷款总数。,语法：,PV(rate,nper,pmt,fv,type,),例如：某用户想商业贷款购房，月偿还能力为,3500,元,/,月，目前月利率为千分之,5,。,PMT,函数,功能：用于计算贷款后每期需偿还的金额。,语法：,PMT(rate,nper,pv,fv,type,),其他函数,FREQUENCY,函数,RANK,函数,FREQUENCY,函数,功能：将区域,range1,中的数据按垂直区域,range2,（分段点）进行频率分布的统计，统计结果放在,range2,右边列的对应位置。,语法,(range1,range2),例如：统计计算机成绩在,0,59,，,60,69,，,70,79,，,80,89,，,90,100,各区间中的人数,RANK,函数,功能：返回单元格,Number,在一个垂直区域,range,中的排位名次，,rank-way,是排位的方式。,语法：,RANK(number,range,rank,-way),例如：求各位同学计算机成绩在整个班级中的名次。,公式与函数综合练习题,根据,A,列中的学生成绩，在,B,列中对应单元格中计算学生的成绩绩点，绩点的确定方法如下,:,成绩,60,分时绩点为,1,，成绩每增加,1,分绩点增加,0.1,；成绩小于,60,分时绩点为,0,。,用公式在区域,D2:D101,中填入相应信息,:,当所在行的,C,列单元格中的数值为整数时，其结果取值为,1,，否则为,0,。,用公式在区域,G2:G101,的对应单元格中根据区域,E2:F101,中的数据填入相应信息,:,如果性别为男，则填入某先生，如果性别为女，则填入某小姐。如,:“,熊先生，许小姐,”,。,