Excel公式与函数公开课一等奖省优质课大赛获奖课件

Excel电子表格

——公式与函数一、单元格引用本此课内容：二、惯用函数介绍一、单元格引用引子显然，这种使用原数据直接参加运算方法不方便！改用单元格地址形式试试看！=25+128=b3+c3地址引用假如参加运算参数个数较多时，能够采取区域地址形式=sum(b2:I2)标识工作表上单元格或单元格区域，指明公式中所用数据位置引用作用Excel公式与函数中参数，能够是单元格地址或区域地址，以代表对应单元格或区域中内容。当地址中内容改变时，公式结果会自动随之改变Excel中地址概念，类似于数学中变量概念，能给运算带来方便在公式中输入单元格（区域）地址最准确方法是使用指针选定。引用概念单元格引用三种类型——相对引用，绝对引用，混合引用类型地址例子相对引用相对地址=B3+E5绝对引用绝对地址=$B$3+$E$5混合引用混合地址=$B3+E$5相对地址格式：采取区域地址常规写法如：=C2*D2-F2,=average(a1:a10)特点：在复制该公式到其它单元格后，公式中相对地址会发生对应改变。

移动该公式到其它单元格时，相对地址仍保持原样，不会自行改变。注意绝对地址格式：列、行号前面都加上符号【$】如：=$B$2,=sum($A$1:$A$10)特点：公式中引用绝对地址，不论是复制或移动该公式到其它单元格，其中绝对地址都保持原样，不会自行调整(地址被锁定了)。混合地址特点：在复制或移动包含混合地址公式时，“绝正确部分”不变，“相正确部分”会变。格式：地址引用中固定行不固定列或固定列不固定行。相对列号绝对行号例B$6,A$4,C$5:F$8绝对列号相对行号例$B6,$A4,$C5:$F8在输入或编辑公式时，能够利用【F4】键变换单元格地址引用方式。首先选中公式中某单元格地址，然后每按一次【F4】键，其地址就按相对、绝对、混合方式循环改变一次怎样在三种引用之间中切换实例格式：工作表名！单元格地址比如：在“销售1”工作表B18单元格求出第一、二季总销售量

=B14+销售2!B14假如要引用不一样工作簿单元格呢?怎样引用其它工作表中单元格注意事项当用户插入或删除若干单元格(或行/列)，会对包含了地址引用公式产生一定影响。即公式中地址会作出对应改变，以保持原公式有效性。当公式犯错时，系统会给出错误信息，常见犯错类型及其原因见书本(P126,表4.5）练习A、=C3+$D5B、=D7+$E9C、=E7+$D9D、=E7+$D51、假如B3单元格中公式为“=C3+$D5”复制到同一工作表D7单元格，该单元格公式为_______。CA、=AVERAGE（C1：F6)B、=AVERAGE（B1：E6)C、=AVERAGE（B1：F6)D、=AVERAGE（C1：G6)

2、已知A1单元格中公式为：=AVERAGE(B1:F6)将B列删除之后，A1公式将调整为_______B使用函数函数组成是：=函数名(参数1，参数2，……，参数n)参数个数能够多达30个参数形式：数值常量字符串常量(以一对半角双引号括住)逻辑值常量(TRUE与FALSE，或1与0)单元格或区域地址表示式或函数(即函数嵌套)有些函数是不带参数（即无参函数）。实例Excel提供了数量众多、功效强大函数来满足运算要求，这些函数有：Excel函数两种输入方法：（一）直接输入法；（二）粘贴函数法。数学与三角函数、日期与时间函数、统计函数、逻辑函数、文本函数、数据库函数、财务函数、查找与引用函数、信息函数、工程函数等1、统计函数函数名称功效SUM(number1,number2,……)Average(number1,number2,……)Max(number1,number2,……)Min(number1,number2,……)Count(number1,number2,……)相同函数还有？？？Countif(Range,Criteria)Sumif(Range,Criteria,Sum_range)着重讨论下面几个统计函数：COUNT(number1,number2,……)功效：统计参数组中数值单元格个数。COUNTA(number1,number2,……)功效：统计参数组中非空白单元格个数。COUNTBLANK(number1,number2,……)功效：统计参数组中空白单元格个数。（1）计数函数实例COUNTIF(Range,Criteria)功能：统计Range区域中满足指定条件Criteria单元格个数。说明：range——被统计数据所在区域。criteria——统计条件。其形式可认为数字、表达式或字符，例如，条件可以表示为32、“>=32”、“32”、“优异”。还可以是条件所在单元格地址（2）条件计数函数=COUNTIF(I3:I22,">=80")统计区域统计条件注意：此处双引号必须用半角状态符号实例SUMIF(Range,Criteria,Sum_range)

功效：依据Range区域中指定条件Criteria，则对Sum_range对应单元格求和。

说明：range——用于条件判断区域。criteria——判定条件，能够是数字、表示式或字符形式。sum_range——进行求和运算实际区域。（2）条件求和函数实例函数名称功效Left(text,number)它和LeftB区分是什么？(对汉字字符处理上不一样)Right(text,number)Mid(text,number1,number2,……)Len(text)Replace(old_text,start_num,num_chars,new_text)在字符串中替换字符Search(text1,text2,num)搜索text1在text2中首次出现位置,它与find区分是??Lower(text)与此相反有Upper(text)2、字符函数LEFT(text,number)

功效：返回字符串text左边number个字符。比如：=LEFT("middleschool",4)结果是middRIGHT(text,number)

功效：返回字符串text右边number个字符。

例：=RIGHT("middleschool",6)结果是schoolMID(text,num1,num2)功效：返回字符串text中，从第num1个字符开始num2个字符。例：=MID("middleschool",3,4)结果是ddleLEN(text)功效：返回字符串text字符个数。例：=LEN("school")结果是6实例3、逻辑函数函数名称功效例子IF(Logical_test,v1,v2)当Logical条件式取值为True时，返回V1结果，为False时，返回V2结果=if(G5>=80,”A”,”B’）AND（Logical1,Logical2,….)当全部参数值都为TRUE时，返回TRUE；不然返回FALSE=and(1+5>4,10<2)OR（Logical1,Logical2,….)当全部参数值都为FALSE时，返回FALSE;不然返回TRUE=or(1+5<4,10<2)NOT（Logical)对逻辑值求反。=not(False)IF(,,)IF函数带三个参数此处普通放公式假如公式运算结果为真假如公式运算结果为假IF(logical_test,value_if_true,value_if_false)

功效：logical_test为“TRUE”时存放value_if_true值；当逻辑表示式logical_test为“FALSE”时存放value_if_false值。

注意：

IF函数可嵌套，能完成复杂条件测试。实例使用IF函数时需要注意：1、IF函数参数个数为三个，缺一不可。2、当IF函数参数是字符型时，用半角双引号括起来。3、在IF函数嵌套使用时，注意左括号和右括号搭配使用。if函数例子

=if(true,"abc","xxx")运算结果是abc

=if(false,"abc","xxx")运算结果是xxx=if(3>2,"abc","xxx")运算结果是abc=if(4+3=5,"abc","xxx")运算结果是xxx

=if(3>=3,"abc","xxx")运算结果是abc=if(2-3>2+3,"abc","xxx")运算结果是xxx自学材料if函数例子

=if(1,0,1)运算结果是0

=if(0,1,0)运算结果是0

=if(0,1,2)运算结果是?

=if(1,2,3)运算结果是?

=if(3,6,9)运算结果是?=if(0,if(1,2,3),if(4,5,6))结果是?思索：=if(1,if(2,if(3,4,5),if(6,7,8)),9)呢？自学材料IF(,,)IF函数带三个参数此处普通放公式假如公式运算结果为真假如公式运算结果为假IF(logical_test,value_if_true,value_if_false)

功效：logical_test为“TRUE”时存放value_if_true值；当逻辑表示式logical_test为“FALSE”时存放value_if_false值。

注意：

IF函数可嵌套，能完成复杂条件测试。实例使用IF函数时需要注意：1、IF函数参数个数为三个，缺一不可。2、当IF函数参数是字符型时，用半角双引号括起来。3、在IF函数嵌套使用时，注意左括号和右括号搭配使用。if函数例子

=if(true,"abc","xxx")运算结果是abc

=if(false,"abc","xxx")运算结果是xxx=if(3>2,"abc","xxx")运算结果是abc=if(4+3=5,"abc","xxx")运算结果是xxx

=if(3>=3,"abc","xxx")运算结果是abc=if(2-3>2+3,"abc","xxx")运算结果是xxx自学材料if函数例子

=if(1,0,1)运算结果是0

=if(0,1,0)运算结果是0

=if(0,1,2)运算结果是?

=if(1,2,3)运算结果是?

=if(3,6,9)运算结果是?=if(0,if(1,2,3),if(4,5,6))结果是?思索：=if(1,if(2,if(3,4,5),if(6,7,8)),9)呢？自学材料数学函数名称功效应用举例ABS(number)返拒绝对值=ABS(-24.65)PRODUCT(number1,number2,……)各参数相乘=PRODUCT(1,2,3,4)SQRT(number)返回正平方根值=SQRT(ABS(-16))MOD(number1,number2)返回两数相除后余数=MOD(26,4)RAND()返回[0,1)范围内随机数=RAND()PI()返回π值=3*3*PI()LOG10(number)返回以10为底对数值=LOG10(1000)4、数学函数ROUND(number1,number2)

功效：把数值number1四舍五入到指定小数位数(number2)。例：=ROUND(35.78,1)结果是35.8INT(number)

功效：取数值number整数部分(返回结果是一个小于number最大整数)。例：=INT(15.63)结果是15=INT(-15.63)结果是-16(参数必须是数值型)函数名称功效例子YEAR(date)计算序列数date年份值=YEAE(37509)

MONTH(date)计算序列数date月份值=MONTH(37509)9DAY（(date)计算序列数date当月日期=DAY(37509)10DATE(year,month,day)计算某一日期序列数=DATE(,9,10)37509TODAY()返回系统当前日期=TODAY()NOW()返回系统当前日期和时间=NOW()5.日期函数YEAR(date)功效：返回指定日期date年份。例：=YEAR("1998-4-23")结果是1998。=YEAR(35908)结果是1998；=YEAR(D12)结果是1998；参数是日期或日期所对应序列数，或单元格地址1998-4-23D12MONTH(date)功效：返回指定日期date月份。例：=MONTH("1998-4-23")结果是4。DAY(date)功效：返回指定日期date日子。例：=DAY("1998-4-23")结果是23。NOW()功效：无参数函数，返回系统日期及时间。例：=NOW()结果是-10-139:17。TODAY()功效：无参数函数，返回系统日期。例：=TODAY()结果是-10-13。年纪计算设定：某人出生日期置于D3单元格，截止统计日期置于B9单元格。虚岁计算：=year(b9)-year(d3)足岁计算：=year(b9)-year(d3)

+if(b9>=date(year(b9),month(d3),day(d3)),0,-1)注函数date(year(b9),month(d3),day(d3)作用是：结构一个特殊日期----年份与统计年份相同，但月份及日子却与出生月份、日子相同。这个特殊日期与统计日期b9比较（年份不需作比较），即可得出，到统计日期为止，某人是否已过了生日（是否已过了出生月份或出生日子）月龄与周龄计算设定：某人出生日期置于D3单元格，截止统计日期置于B9单元格。月龄计算：=(year(b9)-year(d3))*12+