EXCEL公式及函数应用技巧.docx
- 文档编号:26859849
- 上传时间:2023-06-23
- 格式:DOCX
- 页数:40
- 大小:28KB
EXCEL公式及函数应用技巧.docx
《EXCEL公式及函数应用技巧.docx》由会员分享,可在线阅读,更多相关《EXCEL公式及函数应用技巧.docx(40页珍藏版)》请在冰豆网上搜索。
EXCEL公式及函数应用技巧
EXCEL公式及函数的高级应用
公式和函数是Excel最基本、最重要的应用工具,是Excel的核
心,所以,应付公式和函数娴熟掌握,才能在实质应用中驾轻就熟。
数组公式及其应用
数组公式就是能够同时进行多重计算并返回一种或多种结果的公
式。
在数组公式中使用两组或多组数据称为数组参数,数组参数能够
是一个数据地区,也能够是数组常量。
数组公式中的每个数组参数必
须有相同数目的行和列。
数组公式的输入、编写及删除
一.数组公式的输入
数组公式的输入步骤以下:
(1)选定单元格或单元格地区。
假如数组公式将返回一个结果,
单击需要输入数组公式的单元格;假如数组公式将返回多个结果,则
要选定需要输入数组公式的单元格地区。
(2)输入数组公式。
(3)同时按“Crtl+Shift+Enter”组合键,则Excel自动在公式
的两边加上大括号{}。
特别要注意的是,第(3)步相当重要,只有输入公式后同时按
“Crtl+Shift+Enter”组合键,系统才会把公式视为一个数组公式。
不然,假如只按Enter键,则输入的不过一个简单的公式,也只在选
中的单元格地区的第1个单元格显示出一个计算结果。
1
在数组公式中,往常都使用单元格地区引用,但也能够直接键入
数值数组,这样键入的数值数组被称为数组常量。
当不想在工作表中
按单元格逐一输入数值时,能够使用这类方法。
假如要生成数组常量,
一定按以下操作:
(1)直接在公式中输入数值,并用大括号“{}”括起来。
(2)不一样列的数值用逗号“,”分开。
(3)不一样行的数值用分号“;”分开。
★输入数组常量的方法:
比如,要在单元格A1:
D1中分别输入10,20,30和40这4个数值,
则可采纳下述的步骤:
(1)选用单元格地区A1:
D1,如图2-1所示。
图1-1选用单元格地区A1:
D1
(2)在公式编写栏中输入数组公式“={10,20,30,40}”,如图2-2
所示。
2
图1-2在编写栏中输入数组公式
(3)同时按Ctrl+Shift+Enter组合键,即可在单元格A1、B1、C1、
D1中分别输入了10、20、30、40,如图2-3所示。
倘若要在单元格A1、B1、C1、D1、A2、B2、C2、D2中分别输入10、
20、30、40、50、60、70、80,则能够采纳下述的方法:
图1-3同时按Ctrl+Shift+Enter组合键,获得数组常量
(1)选用单元格地区A1:
D2,如图2-4所示。
3
图1-4选用单元格地区A1:
D2
(2)在编写栏中输入公式“={10,20,30,40;50,60,70,80}”,如图
2-5所示。
图1-5在编写栏中输入数组公式
(3)按Ctrl+Shift+Enter组合键,就在单元格A1、B1、C1、D1、
A2、B2、C2、D2中分别输入了10、20、30、40和50、60、70、80,
如图2-6所示。
4
图1-6同时按Ctrl+Shift+Enter组合键,获得数组常量
★输入公式数组的方法
比如,在单元格A3:
D3中均有相同的计算公式,它们分别为单元
格A1:
D1与单元格A2:
D2中数据的和,即单元格A3中的公式为“=A1+A2”,单元格B3中的公式为“=B1+B2”,,则能够采纳数组公式的方法输入公式,方法以下:
(1)选用单元格地区A3:
D3,如图2-7所示。
(2)在公式编写栏中输入数组公式“=A1:
D1+A2:
D2”,如图2-8所
示。
5
图1-7选用单元格地区A3:
D3
图1-8在编写栏中输入数组公式
(3)同时按Ctrl+Shift+Enter组合键,即可在单元格A3:
D3中得
到数组公式“=A1:
D1+A2:
D2”,如图2-9所示。
图1-9同时按Ctrl+Shift+Enter组合键,获得数组公式
二.编写数组公式
数组公式的特点之一就是不可以独自编写、消除或挪动数组公式所波及的单元格地区中的某一个单元格。
若在数组公式输入完成后发现错误
6
需要改正,则需要按以下步骤进行:
(1)在数组地区中单击任一单元格。
(2)单击公式编写栏,当编写栏被激活时,大括号“{}”在数组公式中消逝。
(3)编写数组公式内容。
(4)改正完成后,按“Crtl+Shift+Enter”组合键。
要特别注意不要忘掉这一步。
三.删除数组公式
删除数组公式的步骤是:
第一选定寄存数组公式的全部单元格,而后
按Delete键。
数组公式的应用
一.用数组公式计算两个数据地区的乘积
【例2-1】如图2-10所示,已经知道12个月的销售量和产品单价,则能够利用数组公式计算每个月的销售额,步骤以下:
图1-10用数组公式计算销售额
7
(1)选用单元格地区B4:
M4。
(2)输入公式“=B2:
M2*B3:
M3”。
(3)按“Crtl+Shift+Enter”组合键。
假如需要计算12个月的月均匀销售额,可在单元格B5中输入公
式“=AVERAGE(B2:
M2*B3:
M3)”,而后按“Crtl+Shift+Enter”组合键
即可,如图2-10所示。
在数组公式中,也能够将某一常量与数组公式进行加、减、乘、
除,也能够对数组公式进行乘幂、开方等运算。
比如在图2-10中,每
月的单价相同,故我们也能够在单元格B4:
M4中输入公式
“=B2:
M2*28”,而后按“Crtl+Shift+Enter”组合键;在单元格B5
中输入公式“=AVERAGE(B2:
M2*28)”,而后按“Crtl+Shift+Enter”组合键。
在使用数组公式计算时,最好将不一样的单元格地区定义不一样的名
称,如在图2-10中,将单元格地区B2:
M2定义名称为“销售量”,单元格地区B3:
M3定义名称为“单价”,则各月的销售额计算公式为“=销售量*单价”,月均匀销售额计算公式为“=AVERAGE(销售量*单价)”,这样不简单犯错。
二.用数组公式计算多个数据地区的和
假如需要把多个对应的行或列数据进行相加或相减的运算,并得
出与之对应的一行或一列数据时,也能够使用数组公式来达成。
【例2-2】某公司2002年销售的3种产品的相关资料如图2-11
所示,则能够利用数组公式计算该公司2002年的总销售额,方法以下:
8
图1-11某公司的月销售总数计算
(1)选用单元格地区C8:
N8。
(2)输入公式“=C2:
N2*C3:
N3+C4:
N4*C5:
N5+C6:
N6*C7:
N7”。
(3)按“Crtl+Shift+Enter”组合键。
三.用数组公式同时对多个数据地区进行相同的计算
【例2-3】某公司对现有三种商品实行降价销售,产品原价如图2-12所示,降价幅度为20%,则能够利用数组公式进行计算,步骤以下:
9
图1-12产品降价计算
(1)选用单元格地区G3:
I8。
(2)输入公式“=B3:
D8*(1-20%)”。
(3)按Crtl+Shift+Enter组合键。
别的,当对构造相同的不一样工作表数据进行归并汇总办理时,利
用上述方法也将是特别方便的。
相关不一样工作表单元格的引用可参阅
第1章的相关内容,对于数据的归并计算可参阅本章节的内容。
常用函数及其应用
在第1节中介绍了一些相关函数的基本知识,本节对在财务管理中常用的一般函数应用进行说明,其余相关的特意财务函数将在此后的相关章节中分别予以介绍。
1.2.1SUM函数、SUMIF函数和SUMPRODUCT函数
在财务管理中,应用最多的是乞降函数。
乞降函数有三个:
无条
件乞降SUM函数、条件乞降SUMIF函数和多组数据相乘乞降
SUMPRODUCT函数。
一.无条件乞降SUM函数
该函数是求30个之内参数的和。
公式为=SUM(参数1,参数
2,,参数N)
当对某一行或某一列的连续数据进行乞降时,还能够使用工具栏
10
中的自动乞降按钮。
比如,在例2-1中,求整年的销售量,则能够单击单元格N2,然
后再单击乞降按钮,按回车键即可,如
图1-13所示。
11
图1-13自动乞降
二.条件乞降SUMIF函数
SUMIF函数的功能是依据指定条件对若干单元格乞降,公式:
=SUMIF(range,criteria,sum_range)
式中range—用于条件判断的单元格地区;
criteria—确立哪些单元格将被相加乞降的条件,其形式能够
为数字、表达式或文本;
sum_range—需要乞降的实质单元格。
只有当range中的相应单元格知足条件时,才对sum_range中的单元格乞降。
若省略sum_range,则直接对range中的单元格乞降。
利用这个函数进行分类汇老是很实用的。
【例1-4】某商场2月份销售的家电流水记录如图1-14所示,则
在单元格I3中输入公式“=SUMIF(C3:
C10,211,F3:
F10)”,单元格I4
中输入公式“=SUMIF(C3:
C10,215,F3:
F10)”,在单元格I5中输入公式
“=SUMIF(C3:
C10,212,F3:
F10)”,单元格I6中输入公式“=SUMIF(C3:
C10,220,F3:
F10)”,即可获得分类销售额汇总表。
12
图1-14商品销售额分类汇总
SUMIF函数的对话框如图1-15所示。
图1-15SUMIF函数对话框
当需要分类汇总的数据很大时,利用SUMIF函数是很方便的。
三.SUMPRODUCT函数
SUMPRODUCT函数的功能是在给定的几组数组中,将数组间对应的
元素相乘,并返回乘积之和。
公式为=SUMPRODUCT(array1,array2,array3,)
式中,array1,array2,array3,...为1至30个数组。
13
需注意的是,数组参数一定拥有相同的维数,不然,函数SUMPRODUCT将返回错误值#VALUE!
。
对于非数值型的数组元素将作为
1办理。
比如,在例1-2中,要计算2002年产品A的销售总数,可在任一单元格(比方O2)中输入公式“=SUMPRODUCT(C2:
N2,C3:
N3)”即可。
1.2.2AVERAGE函数
AVERAGE函数的功能是计算给定参数的算术均匀值。
公式为=AVERAGE(参数1,参数2,,参数N)
函数中的参数能够是数字,或许是波及数字的名称、数组或引用。
假如数组或单元格引用参数中有文字、逻辑值或空单元格,则忽视其值。
可是,假如单元格包括零值则计算在内。
AVERAGE函数的使用方法与SUM函数相同,此处不再介绍。
1.2.3MIN函数和MAX函数
MIN函数的功能是给定参数表中的最小值,MAX函数的功能是给定
参数表中的最大值。
公式为
=MIN(参数1,参数2,,参数N)
=MAX(参数1,参数2,,参数N)
函数中的参数能够是数字、空白单元格、逻辑值或表示数值的文字串。
比如,MIN(3,5,12,32)=3;MAX(3,5,12,32)=32。
1.2.4COUNT函数和COUNTIF函数
COUNT函数的功能是计算给定地区内数值型参数的数目。
公式为:
=COUNT(参数1,参数2,,参数N)
14
COUNTIF函数的功能是计算给定地区内知足特定条件的单元格的数
目。
公式为:
=COUNTIF(range,criteria)
式中range—需要计算此中知足条件的单元格数目的单元格区
域;
criteria—确立哪些单元格将被计算在内的条件,其形式
能够为数字、表达式或文本。
COUNT函数和COUNTIF函数在数据汇总统计剖析中是特别实用的
函数。
1.2.5IF函数
IF函数也称条件函数,它依据参数条件的真假,返回不一样的结果。
在实践中,常常使用函数IF对数值和公式进行条件检测。
公式为=IF(logical_test,value_if_true,value_if_false)
式中logical_test—条件表达式,其结果要么为TRUE,要么为
FALSE,它可使用任何比较运算符;
value_if_true—logical_test为TRUE时返回的值;
value_if_false—logical_test为FALSE时返回的值。
IF函数在财务管理中拥有特别宽泛的应用。
【例2-5】比如,某公司对各个销售部门的销售业绩进行评论,
评论标准及各个销售部门在2002年的销售业绩汇总如图1-16所示,
评论计算步骤以下:
15
图1-16销售部门业绩评论
(1)选定单元格地区C3:
C12。
(2)直接输入以下公式:
“=IF(B3:
B12<100000,"差
",IF(B3:
B12<200000,"一般",IF(B3:
B12<300000,"好
",IF(B3:
B12<400000,"较好","很好"))))”。
(3)按“Crtl+Shift+Enter”组合键。
则各个销售部门的销售业绩评论结果就显示在单元格域C3:
C12中。
也能够直接在单元格C3中输入公式“=IF(B3<100000,"差
",IF(B3<200000,"一般",IF(B3<300000,"好",IF(B3<400000,"较好
","很好"))))”后,将其向下填补复制到C4~C12单元格中。
1.2.6AND函数、OR函数和NOT函数
这3个函数的用法以下:
=AND(条件1,条件2,,条件N)
=OR(条件1,条件2,,条件N)
=NOT(条件)
AND函数表示逻辑与,当全部条件都知足时(即全部参数的逻辑
16
值都为真时),AND函数返回TRUE,不然,只需有一个条件不知足即返
回FALSE。
OR函数表示逻辑或,只需有一个条件知足时,OR函数返回TRUE,
只有当全部条件都不知足时才返回FALSE。
NOT函数只有一个逻辑参数,它能够计算出TRUE或FALSE的逻辑
值或逻辑表达式。
假如逻辑值为FALSE,函数NOT返回TRUE;假如
逻辑值为TRUE,函数NOT返回FALSE。
这3个函数一般与IF函数联合使用。
【例2-6】某公司依据各销售部门的销售额及销售花费确立奖金
提成比率及提取额,若销售额大于300000元且销售花费占销售额的比
例不超出1%,则奖金提取比率为15%,不然为10%,则计算过程以下
(如图2-17所示):
(1)在单元格D3中输入公式
“=IF(AND(B3>300000,C3/B3<1%),15%,10%)”,将其向下填补复制到
D4~C10单元格中。
(2)选用单元格地区E3:
E10,输入公式“=B3:
B10*D3:
D10”,按
“Crtl+Shift+Enter”组合键。
则各销售部门的销售奖金提成比率及奖金提取额如图
1-17所示。
17
图1-17奖金提成比率及提取额的计算
1.2.7LOOKUP函数、VLOOKUP函数和HLOOKUP函数
一.LOOKUP函数
LOOKUP函数的功能是返回向量(单行地区或单列地区)或数组中
的数值。
函数LOOKUP有两种语法形式:
向量和数组。
函数LOOKUP的
向量形式是在单行地区或单列地区(向量)中查找数值,而后返回第
二个单行地区或单列地区中相同地点的数值;函数LOOKUP的数组形
式在数组的第一行或第一列查找指定的数值,而后返回数组的最后一
行或最后一列中相同地点的数值。
(1)向量形式:
公式为=
LOOKUP(lookup_value,lookup_vector,result_vector)
式中lookup_value—函数LOOKUP在第一个向量中所要查找的
数值,它能够为数字、文本、逻辑值或包括数值的名称或引用;
lookup_vector—只包括一行或一列的地区lookup_vector的数
值能够为文本、数字或逻辑值;
result_vector—为只包含一行或一列的区域其大小必须与
18
lookup_vector相同。
(2)数组形式:
公式为=LOOKUP(lookup_value,array)
式中array—包括文本、数字或逻辑值的单元格地区或数组它的
值用于与lookup_value进行比较。
比如:
LOOKUP(5.2,{4.2,5,7,9,10})=5。
注意:
lookup_vector的数值一定按升序摆列,不然函数LOOKUP
不可以返回正确的结果。
文本不划分大小写。
假如函数LOOKUP找不到
lookup_value,则查找lookup_vector中小于或等于lookup_value
的最大数值。
假如lookup_value小于lookup_vector中的最小值,函
数LOOKUP返回错误值#N/A。
二.VLOOKUP函数
VLOOKUP函数的功能是在表格或数值数组的首列查找指定的数值,并
由此返回表格或数组目前行中指定列处的数值。
公式为:
=VLOOKUP(lookup_value,table_array,col_index_num,
range_lookup)
式中lookup_value—需要在数据表第一列中查找的数值,
lookup_value能够为数值、引用或文字串;
table_array—需要在此中查找数据的数据表,能够使用对地区或地区
名称的引用,比如数据库或数据清单;
假如range_lookup为TRUE,则table_array的第一列中的数值
一定按升序摆列,不然函数VLOOKUP不可以返回正确的数值,假如
range_lookup为FALSE,table_array不用进行排序。
table_array
的第一列中的数值能够为文本、数字或逻辑值,且不划分文本的大小
19
写;
col_index_num—table_array中待返回的般配值的列序号;
col_index_num为1时,返回table_array第一列中的数值;
col_index_num为2时,返回table_array第二列中的数值,以此类推。
假如col_index_num小于1,函数VLOOKUP返回错误值#VALUE!
;假如col_index_num大于table_array的列数,函数VLOOKUP返回错误值#REF!
。
range_lookup—逻辑值,指明函数VLOOKUP返回时是精准般配仍是近
似般配。
假如其为TRUE或省略,则返回近似般配值,也就是说,假如找不到精准般配值,则返回小于lookup_value的最大数值;假如range_value为FALSE,函数VLOOKUP将返回精准般配值。
假如找不到,则返回错误值#N/A。
VLOOKUP函数在财务管理与剖析中是一个常常用到的函数,所以熟习
它将会带来很大便利。
在此后的相关章节中会常常用到它。
比如,假定单元格A1:
A4中的数据分别为1、30、80和90,单元格B1:
B4
中的数据分别为400、500、600和700,则有:
VLOOKUP(5,A1:
B4,2)=400,
VLOOKUP(30,A1:
B4,2)=500,VLOOKUP(79,A1:
B4,2)=500,
VLOOKUP(92,A1:
B4,2)=700。
三.HLOOKUP函数
HLOOKUP函数的功能是从表格或数值数组的首行查找指定的数
值,并由此返回表格或数组目前列中指定行处的数值。
公式为:
=(lookup_value,table_array,row_index_num,range_lookup)
20
式中row_index_num—table_array中待返回的般配值的行序号。
row_index_num为1时,返回table_array第一行的数值,
row_index_num为2时,返回table_array第二行的数值,以此类推。
假如row_index_num小于1,函数HLOOKUP返回错误值#VALUE!
;如
果row_index_num大于table_array的行数,函数HLOOKUP返回错误值#REF
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 公式 函数 应用 技巧
![提示](https://static.bdocx.com/images/bang_tan.gif)