第三节 公式与函数的应用.docx
- 文档编号:14555682
- 上传时间:2023-06-24
- 格式:DOCX
- 页数:10
- 大小:59.19KB
第三节 公式与函数的应用.docx
《第三节 公式与函数的应用.docx》由会员分享,可在线阅读,更多相关《第三节 公式与函数的应用.docx(10页珍藏版)》请在冰点文库上搜索。
第三节公式与函数的应用
第三节 公式与函数的应用
一、公式的应用(★★★)
(一)公式的概念及其构成
例如:
=68+B2*3-SUM(C1:
C5)
包含:
=、运算体、运算符(公式总是以等号“=”开始)
1.运算体是指能够运算的数据或者数据所在单元格的地址名称、函数等。
上例中68、B2、SUM(C1:
C5)
2.运算符是使Excel自动执行特定运算的符号。
Excel中,运算符主要有四种类型:
算术运算符、比较运算符、文本运算符、引用运算符。
(1)算术运算符:
可以完成基本的数学运算,包括:
“+”(加)、“-”(减)、“*”(乘)、“/”(除)、“^”(乘方)等,运算的结果为数值。
(2)比较运算符:
可以比较两个同类型的数据(都是数值或都是字符或都是日期),包括:
“=”(等于)、“>”(大于)、“<”(小于)、“>=”(大于等于)、“<=”(小于等于)、“<>”(不等于),运算的结果为逻辑值TRUE或FALSE。
(3)文本运算符:
“&”(连接运算符),用于把前后两个字符串连接在一起,生成一个字符串。
算术运算符和文本运算符优于比较运算。
(4)引用运算符:
是Excel特有的运算符,用于单元格引用,可以将单元格区域合并运算,包括:
①区域运算符“:
”(冒号):
产生对包括在两个引用之间的所有单元格的引用,上例中SUM(C1:
C5)。
②联合运算符“,”(逗号):
将多个引用合并为一个引用,即取多个区域的并集如SUM(A1:
A5,C1:
C5)。
③交叉运算符“”(空格):
产生对多个引用共有的单元格的引用,即多个区域的交集,如
SUM(A1:
B5A4:
D9),相当于SUM(A4:
B5)。
(二)公式的创建与修改
1.公式的创建
手动输入、移动点击输入
编辑栏显示公式单元格显示计算结果
【链接】报表管理模块中公式的录入,需要在“显示公式”或“格式”状态下。
2.公式的编辑
快捷键:
F2
(三)公式的运算次序
1.优先级次相同的运算符:
从左至右运算
2.多个优先级次不同的运算符:
从高至低运算
比如小括号、乘号等优先级较高。
(四)公式运算结果的显示
1.查看公式中某步骤的运算结果
快捷键:
F9(查看)、“Esc或Ctrl+Z”(恢复)
【提示】先按F2进入公式编辑状态,再按F9查看。
2.公式默认显示方式的改变
快捷键:
Ctrl+`(数字1左边的按键)
功能区:
公式→显示公式
3.将公式运算结果转换为数值
复制→选择性粘贴
【例题•单选题】下列属于区域运算符的是( )。
A.“/”
B.“&”
C.“:
”
D.“,”
『正确答案』C
『答案解析』选项A为算术运算符;选项B为文本运算符;选项D为联合运算符。
二、单元格的引用(★★★)
例如:
=68+B2*3-SUM(C1:
C5)
(一)引用的类型
1.相对引用:
Excel默认使用的单元格引用
2.绝对引用
例如:
=68+$B$2*3-SUM(C1:
C5)
3.混合引用
例如:
=68+$B2*3-SUM(C1:
C5)
【例题·实务操作题】如图所示,计算学生的总成绩。
(二)输入单元格引用
1.在列标和行标前直接输入“$”符号。
2.输入完单元格地址以后,重复按“F4”键选择合适的引用类型。
【例题·实务操作题】把图中D2的单元格公式改为“=$B$2+$C$2”,然后将公式复制到单元格D3中,D3中的值与D2相同,引用地址没有改变。
(三)跨工作表单元格引用
工作表名!
数据源所在单元格地址
例如:
=68+Sheet1!
$B$2*3-SUM(C1:
C5)
(四)跨工作簿单元格引用
[工作簿名]工作表名!
数据源所在单元格地址
例如:
=68+[Book2]Sheet1!
$B$2*3-SUM(C1:
C5)
三、函数的应用(★★★)
基本格式:
函数名(参数序列)
函数只能出现在公式中。
除中文外都必须使用英文半角字符,参数无大小写之分。
【提示】重点把握参数的含义和运用。
(一)常用函数
1.统计函数
(1)MAX
MAX(number1,number2,……)用于返回数值参数中的最大值,忽略参数中的逻辑值和文本。
(2)MIN
MIN(number1,number2,……)用于返回数值参数中的最小值,忽略参数中的逻辑值和文本。
(3)SUM
SUM(number1,number2,……)用于计算单元格区域中所有数值的和。
(4)SUMIF
SUMIF(range,criteria,sum_range)用于对满足条件的单元格求和。
Range代表要进行计算的的单元格区域(包括条件判断区)
Criteria数字、表达式或文本形式定义的条件,条件要用双引号引起来
sum_range用于求和计算的实际单元格,如果省略,则使用Range定义的区域
(5)AVERAGE
AVERAGE(number1,number2,……)用于返回参数的算术平均值。
(6)AVERAGEIF
AVERAGEIF(range,criteria,sum_range)用于返回某个区域内满足给定条件的所有单元格的算术平均值。
(7)COUNT
COUNT(va1ue1,va1ue2,……)用于计算包含数字的单元格以及参数列表中数字的个数。
(8)COUNTIF
COUNTIF(range,criteria)用于对区域中满足单个指定条件的单元格进行计数,条件表达式同样要用引号引起来。
2、文本函数
(1)LEN
LEN(text)用于返回文本字符串中的字符数。
(2)RIGHT
RIGHT(text,num_chars)用于从文本字符串中最后一个字符开始返回指定个数的字符。
(3)MID
MID(text,start_num,num_chars)用于返回文本字符串中从指定位置开始的指定数目的字符。
(4)LEFT
LEFT(text,num_chars)用于返回文本字符串中第一个字符开始至指定个数的字符。
3、逻辑函数IF
IF(1ogica1_test,va1ue_if_true,va1ue_if_fa1se)用于判断“1ogica1_test”的内容是否为真,如果为真则返回“va1ue_if_true”,如果为假则返回“va1ue_if_fa1se”的内容。
4、查找与引用函数
(1)LOOKUP
LOOKUP函数可返回一行或一列区域中或者数组中的某个值,LOOKUP函数具有矢量和数组两种语法形式:
矢量形式的LOOKUP
矢量形式的LOOKUP在一行或一列区域(称为矢量)中查找值,然后返回另一行或一列区域中相同位置处的值。
矢量形式的语法
LOOKUP(lookup_value,lookup_vector,result_vector)
Lookup_value是LOOKUP在第一个矢量中指定要搜索的值(或值的仅似值)。
Lookup_value可以是数字、文本、逻辑值,也可以是代表某个值的名称或引用。
Lookupvector是一个仅包含一行或一列的区域,这一区域实际上就是前面指定Lookup_value的搜索范围。
lookupvector中的值可以是文本、数字或逻辑值。
result_vector是与Lookupvector区域相对应的行或列,这两个区域的单元格是相互对应的,也就是说,在Lookupvector区域中找到一个值,在result_vector区中相对位置上就有一值与之对应(这个值就是我们公式运行的结果)
如果在Lookupvector区域中找不到lookup_value值,系统会匹配lookupvector中小于或等于lookup_value的最大值。
如果lookupvector单元中的所有数据都大于lookup_value给定值,则LOOKUP会返回#N/A错误值。
重要说明:
首先,lookupvector中的值必须按升序排列,否则,LOOKUP返回的值可能不正确,大写和小写文本是等效的。
其次,Result_vector是一个仅包含一行或一列的区域。
它的大小必须与lookupvector相同。
再次,lookupvector区域不能全为大于lookup_value指定值的单元。
数组形式的LOOKUP
数组形式的LOOKUP在数组的第一行或列中查找指定值,然后返回该数组的最后一行或列中相同位置处的值。
如果要匹配的值位于数组的第一行或列中,请使用这种形式的LOOKUP。
数组形式的语法
LOOKUP(lookup_value,array)
Lookup_value是LOOKUP需要在第一行或第一列中搜索的值。
Lookup_value可以是数字、文本、逻辑值,也可以是代表某个值的名称或引用。
如果LOOKUP找不到lookup_value,它会使用该数组第一行或第一列中小于或等于lookup_value的最大值。
如果lookup_value小于第一行或列(取决于数组维度)中的最小值,则LOOKUP会返回#N/A错误值。
Array是一个单元格区域,其中包含要与lookup_value进行比较的文本、数字或逻辑值。
如果array所覆盖区域的宽度大于高度(列多于行),则LOOKUP会在第一行中搜索lookup_value。
如果array所覆盖的区域是正方形或者高度大于宽度(行多于列),则LOOKUP会在第一列中进行搜索。
也就是说,LOOKUP在查找给定值是时,总是以数据最多的向方找,行列数相等时,以列为查找依据。
array中的值必须按升序顺序排列。
(2)INDEX
INDEX(array,row_num,co1umn_num)用于返回表格或数组中的元素值,此元素由行序号和列序号的索引值给定。
Array代表单元格区域或数组常量;
row_num表示指定的行序号;
co1umn_num表示指定的列序号;
注意:
此处的行序号参数和列序号参数是相对于所引用的单元格区域而言的,不是Excel工作表中的行或列序号。
(3)MATCH
MATCH(1ookup_va1ue,1ookup_array,match_type)用于在单元格区域中(一般为行或列)搜索指定项,然后返回该项在单元格区域中的相对位置。
1ookup_va1ue代表需要在数据表中查找的数值;
1ookup_array表示可能包含所要查找的数值的连续单元格区域;
match_type表示查找方式的值(-1,0,1),。
如果match_type为-1,表示查找大于或等于1ookup_va1ue的最小值,这时1ookup_array区域中的数值必须按降序排列。
如果match_type为0,表示查找等于1ookup_va1ue的第一个数值,这时1ookup_array区域中的数值可按任意顺序排列。
如果match_type为1,表示查找小于或等于1ookup_va1ue的最大值,这时1ookup_array区域中的数值必须按升序排列。
5.日期与时间函数
(1)YEAR
YEAR(seria1_number)用于返回某日期对应的年份。
(2)MONTH
MONTH(seria1_number)用于返回某日期对应的月份,介于1到12之间。
(3)DAY
DAY(seria1_number)用于返回某日期对应的天数,介于1到31之间。
(4)NOW
NOW()用于返回当前电脑系统的日期和时间,按F9可刷新当前值,该函数不需要参数。
(二)基本财务函数
1、SLN
SLN(cost,sa1vage,1ife)用于返回某项资产以直线法(比如平均年限法、工作量法等)计提的每一期的折旧值。
cost指固定资产原值,sa1vage指固定资产的残值,1ife指固定资产的折旧期数(也称固定资产的使用寿命)。
2、DDB
DDB(cost,sa1vage,1ife,period,factor)用于使用双倍余额递减法计算一项固定资产在给定期间内的折旧值。
cost指固定资产原值,sa1vage指固定资产的残值,1ife指固定资产的折旧期数(即固定资产寿命,可以是年,也可以是月),period指需要计算折旧值的期间(第几年或第几个月),period必须使用与1ife相同的单位(同为年或同为月),factor是可选参数,指余额递减速率,如果factor被省略,则默认为2,即使用双倍余额递减法。
3、SYD
SYD(cost,sa1vage,1ife,per)用于返回某项资产按年数总和折旧法计算的在第“per”期的折旧值。
cost指固定资产原值,sa1vage指固定资产的残值,1ife指固定资产的折旧期数,per指第几期,其单位必须与1ife相同。
(三)补充函数(非大纲要求)
(1)ROUND(四舍五入函数)
Round(number,num_digits)用于返回给定数值的四舍五入后的值。
(2)INT(取整函数)
Int(number)用于返回给定数值的整数部分。
(3)VLOOKUP(水平查找函数)
VLOOKUP(1ookup_va1ue,Table_array,Col_index_num,Range_lookup)用于在数据表的首列查找指定的数值,并由此返回数据表当前行中指定列处的数值。
1ookup_va1ue代表需要查找的数值
Table_array代表需要在其中查找数据的单元格区域
Col_index_num为在Table_array区域中待返回的匹配值的列序号。
Range_lookup为一逻辑值,如果为TURE或省略,则返回精确匹配值,如果为FALSE,则返回近似匹配值。
=VLOOKUP(B3,职员档案!
$A$1:
$E$20,3,FALSE)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第三节 公式与函数的应用 三节 公式 函数 应用