9299.net
大学生考试网 让学习变简单
当前位置:首页 >> 数学 >>

Excel公式与函数_图文

Excel公式与函数_图文

Excel电子表格
教学内容:Excel公式与函数 学习重点:公式与函数的复制、常用函数 学习方法:看书、上机操作、课后练习 授课时数:4 学时

应用公式 公式编辑 使用函数 数学函数

统计函数
文本函数 日期与时间函数 逻辑函数 财务函数 其他函数


? 公式介绍 ? 公式类型 ? 数值公式 ? 字符公式 ? 逻辑公式 ? 引用运算







? 公式中各运算符的优先级

公式介绍
?

与数学公式类似,对数据进行各种四则运算。 ? 以“=”开头,后面接各种表达式


例:求计算机 、英语和数学三门功课的总分

?

输入方法: – 在单元格中输入,“回车”确认 – 在编辑栏内输入并确认

公式类型
数值公式:对数字或单元格内的数字进行 计算。(包括日期运算) ? 字符公式:对字符或单元格内的字符进行 合并运算。 ? 逻辑公式:进行比较运算,用于构造条件。
?

数值公式
算术运算符:+,-,*,/,^,% ? 例:求总分
?

? 总分=平时成绩*30%+期末成绩

*70% ? 求给定数(如:2)的立方
注意:EXCEL中使用的标点符号都是英 文状态的标点符号

字符公式
文本运算符:用“&”进行字符的连接运算。 ? 例: – 产生1月份~12月份 ? 说明: – 对于字符常量要加双引号,单元格或区 域内的数值数据可直接引用。
?

逻辑公式
比较运算符:>,<,>=,<=,=,<> ? 例: – 比较两门功课考试分数的高低。 ? 说明: – 逻辑公式均用在条件判断式中,产生结 果TRUE或FALSE。
?

引用运算
引用运算符:区域运算符(:),交叉运算符 (空格),联合运算符(,)。 ? 例: count() – A1:C1 – A1,C1 – A1:D4 D4:F7
?

各运算符的优先级
公式处理复杂数据时,用到的多个运算符 的优先级是不同的。 ? 数学运算符>字符串运算符>比较运算符 ? 默认:根据公式中运算符的特定顺序从左到 右计算公式。 ? Q:如何更改求值的顺序?
?









? 公式的复制和移动

? 单元格地址引用
? 不同引用在公式复制时的变化

? 确定公式的引用形式
? 使用公式出错信息及原因

公式的复制和移动介绍
公式通常引用单元格计算数据,当对公式进 行复制时就不是一般数据的简单复制。 ? 例:求出所有同学的总分。 ? 例:求出所有女同学的总分。 ? 方法一:填充句柄 ? 方法二:粘贴 ? 方法三:选择性粘贴
?

单元格地址引用介绍
?

?

?

EXCEL 中单元格地址有不同的表示方法,可以直 接用相应的地址表示。 单元格地址引用类型(三大地址) – 相对地址:如A3,A4:A8等形式; – 绝对地址:如$A$3,$a$4:$A$4等形式; – 混合地址:如$A3,A$3等形式。 – 引用转换:F4 不同的引用方式只有在公式复制时有不同的作用。

不同引用在公式复制时的变化
公式单元格(A1) A1= B1+ C1 A1= $B$1+ $C$1 A1= $B1+ C$1 目的单元格(C10) C10=D10+E10 C10=$B$1+ $C$1 C10=$B10+E$1

确定公式的引用形式
例:利用公式复制产生九九乘法表 ? 准备:
?
– –

在B35:J35中输入了9个数1~9 在A36:A44中输入了9个数1~9

?

要求:在B2单元格中输入一个公式,然后 将该公式复制产生九九乘法表

使用公式出错信息及原因
出错信息 #DIV/0! 说明 公式中出现0作除数,可能是空单元格作除数引起的。

#NAME?
#NULL! #NUM! #REF!

引用了Excel不能识别的文本,输入错误或使用了未定义的名 称
在不相交的区域中指定了一个交集 在公式或函数中使用了不适当的数字,如不在函数定义域中 的数字 单元格引用无效,如引用的单元格不存在

#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 ? 使用数学函数计算:
?

sin(x? ) cos( ? / 12) ? 1









?average()函数
?count

()

?countif

()函数

average()函数
功能:返回参数平均值(算术平均) ?语 法:average(number1,number2,…,num ber30) ? 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 可以为数字、文本,如 “ 15Apr -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-iffalse) 例:判断考试成绩是否及格 条件:考试成绩>=60为及格,否则为不及格。

嵌套IF函数
?

在某些情况下,可能需要将某函数作为另一函数 的参数使用。

?
?

例:判断成绩的等级 条件: – 考试成绩>=85为优秀, – 84>考试成绩>=60为中等, – 考试成绩<60为差

AND()函数
?

?

功能:所有参数的逻辑值为真时返回 TRUE;只要 一个参数的逻辑值为假即返回 FALSE。 语法: AND(logical1,logical2, ...) – Logical1, logical2, ... 待检测的 1~30 个条 件值,各条件值或为 TRUE,或为 FALSE。 例:判断成绩是否及格 条件:机试和笔试都>=60为及格,否则为不及格。

? ?

OR()函数
?

?

功能:在其参数组中,任何一个参数逻辑值为 TRUE,即返回 TRUE,全为FALSE,才返回 FALSE。 语法:OR(logical1,logical2,...) – Logical1,logical2,... 为需要进行检验的 1 到 30 个条件,分别为 TRUE 或 FALSE。 例:判断成绩是否及格 条件:机试和笔试任意<60为不及格,否则为及格。

? ?

AND、OR、NOT综合比较
N1 TRUE FALSE TRUE FALSE N2 TRUE TRUE AND OR NOT(N1)

TRUE TRUE FALSE FALSE TRUE TRUE

FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE

财 务 函 数

? 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) 例如:求各位同学计算机成绩在整个班级中的 名次。

公式与函数综合练习题
1.

2.

3.

根据A列中的学生成绩,在B列中对应单元格中 计算学生的成绩绩点,绩点的确定方法如下:成绩 60分时绩点为1,成绩每增加1分绩点增加0.1; 成绩小于60分时绩点为0。 用公式在区域D2:D101中填入相应信息:当所在 行的C列单元格中的数值为整数时,其结果取值 为1,否则为0。 用公式在区域G2:G101的对应单元格中根据区域 E2:F101中的数据填入相应信息:如果性别为男, 则填入某先生,如果性别为女,则填入某小姐。 如:“熊先生,许小姐……”。


网站首页 | 网站地图 | 学霸百科 | 新词新语
All rights reserved Powered by 大学生考试网 9299.net
文档资料库内容来自网络,如有侵犯请联系客服。zhit325@qq.com