AOA之Excel.docx
- 文档编号:5487007
- 上传时间:2022-12-17
- 格式:DOCX
- 页数:40
- 大小:1.23MB
AOA之Excel.docx
《AOA之Excel.docx》由会员分享,可在线阅读,更多相关《AOA之Excel.docx(40页珍藏版)》请在冰豆网上搜索。
AOA之Excel
Excel高级应用篇
第9章公式与函数
9.3数组公式
1、什么是数组公式
数组是具有某种联系的多个元素的组合,其实就是一组同类型的数据,可以作为一个整体来处理。
数组公式是一种专门用于数组的公式类型,是可以在数组的一项或多项上执行多个计算的公式。
数组公式是相对于普通公式而言的,普通公式(如“=SUM(B2:
D2),=B2+C2+D2”等),只占用一个单元格,只返回一个结果。
而数组公式可以占用一个单元格,也可以占用多个单元格。
它对一组数或多组数进行多重计算,并返回一个或多个结果(每个结果显示在一个单元格区域中)。
在Excel中数组公式的显示是用大括号对“{}”来括住以区分普通Excel公式。
如下图:
输入数组公式:
用Ctrl+Shift+Enter结束公式输入
图9-3-1数组公式输入
在对数组公式有了一个简单的了解之后,下面我们将通过简单的例子来进一步认识数组公式在Excel中的使用。
问题:
现有商品销售工作表如图9-3-2所示,要求在D2:
D4求出商品的销售金额。
图9-3-2商品销售工作表
操作步骤如下:
步骤1:
选定需要输入公式的单元格或单元格区域,本例选中“D2:
D4”。
步骤2:
输入公式“=B2:
B4*C2:
C4”,但要注意的是输完后不要按
Ctrl+Shift+Enter。
如下图9-3-3所示:
D2:
D4被当作整个单元格来进行处理,所以不能对D2到D4中的任意一个单元格作任何单独处理,必须针对整个数组进行处理,不然会出现如图9-3-4所示的错误。
图9-3-3输完数组公式后的商品销售工作表
图9-3-4试图修改数组中单元格D3后的错误信息
数组公式具有以下优点:
●一致性如果单击上例中区域D2:
D4中的任意单元格,您将看到相同的公式。
这种一致性有助于确保更高的准确性。
●安全性不能随便修改多单元格数组公式的组成部分,例如单击单元格D3并按Delete。
您必须选择整个单元格区域(D2到D4),然后更改整个数组的公式,否则只能让数组保留原样。
作为一种附加安全措施,必须按Ctrl+Shift+Enter确认对公式的更改。
●节省空间和时间通常可以使用单个数组公式,而不必用多个中间公式。
另外,数组可以存储在内存中,因此使用数组公式可以加快公式的执行时间。
●一些运算用传统公式无法实现而可以用数组实现。
后面有例子会讲到。
使用数组公式的缺点:
数组公式看起来似乎功能很神奇,但它们也存在某些缺点:
●可能有时会忘记按Ctrl+Shift+Enter。
请记住每当输入或编辑数组公式时都要按此组合键。
●其他用户可能不理解您的公式。
数组公式相对复杂,因此如果其他人需要修改您的工作簿,您应避免使用数组公式或者确信这些用户知道如何更改您的公式。
●大型数组公式可能会降低计算速度,具体取决于计算机的处理速度和内存。
2、数组常量及使用
数组常量是数组公式的组成部分。
可以通过输入一系列数值然后手动用大括号“{}”将该系列项括起来创建数组常量,类似于:
“={1,2,3,4,5}”。
数组中使用的常量可以是数字、文本、逻辑值(“TRUE”或“FALSE”)和错误值等。
数组有整数型、小数型和科学计数型。
文本则必须使用引用引起来,例如“星期一”。
在同一个数组常量中可以使用不同类型的值。
数组常量中的值必须是常量,不可以是公式。
数组常量不能含有货币符号、括号或百分比符号。
所输入的数组常量不得含有不同长度的行或列。
数组常量有一维数组,二维数组,三维数组,四维数组……,在公式里,我们更多接触到的只是一维数组和二维数组。
一维数组包括垂直和水平数组。
水平数组我们可以简单地看成是一行的单元格数据集合,比如A1:
F1(垂直数组则可以看成是一列)。
水平一维数组的各个元素间用英文的逗号“,”隔开(如果是垂直数组,则用英文分号“;”隔开)。
如下图9-3-5:
图9-3-5一维水平数组
“={1,2,3,4,5,6}”,这就是一个有6个元素的一维数组,或者说,只有一行的数组。
数组的各个元素间用逗号“,”分隔。
如果想把这个数组输入到工作表的单元格里,要同时选中同一行里相邻的六个单元格,输入:
“={1,2,3,4,5,6}”后,以组合键Ctrl+Shift+Enter结束公式,就可以看到这个一维数组被输入到工作表的单元格里了。
二维数组则可以看成是一个多行多列的单元各数据集合,也可以看成是多个一维数组的组合。
如单元格A1:
D3,就是一个三行四列的二维数组。
我们可以把它看成是A1:
D1、A2:
D2与A3:
D3这三个一维数组的组合。
二维数组里同行的元素间用逗号“,”分隔,不同的行用分号“;”分隔。
我们可以用上面的方法,在A1:
D3区域输入数据,并引用地址,按F9来查看。
如图9-3-6所示:
图9-3-6二维数组
可以看到在数组里,换行的时候,元素间的分隔符是“;”,所以,要判断一个数组是几行几列的数组,只需要看里面的逗号和分号就知道了。
如果需要把数把数组返回到单元格区域里,首先得看数组是几行几列,然后再选择相应的单元格区域,输入数组,以组合键Ctrl+Shift+Enter结束。
注意:
(1)一维数组是单独的一行或一列。
二维数组是多行多列。
(2)数组里的元素,同一行内的各元素用英文逗号“,”分开,用英文分号“;”将各行分开。
(3)二维数组的元素按先行后列的顺序排列。
总是这样:
{第一行的第一个,第一行的第二个,第一行的第三个……;第二行的第一个,第二行的第二个,第二行的第三个……;第三个的第一个……}
●命名数组常量
使用数组常量的最佳方式是对它们进行命名。
命名的数组常量更易于使用,并且对于初学者来说,它们可以降低数组公式的复杂性。
要命名数组常量并在公式中使用它们,请执行以下操作步骤:
步骤1:
在“插入”菜单的“名称”子菜单中选择“定义”命令。
显示“定义名称”对话框。
如图9-3-7所示:
步骤2:
在“名称”框中,键入“第一季度”。
步骤3:
在“引用位置”框中,输入下面的常量(记住要手动键入大括号):
“={"一月","二月","三月"}”对话框中的内容应类似如图9-3-7所示:
单击“确定”。
步骤4:
在工作表中,选择一行中的三个空单元格。
步骤5:
键入公式,“=第1季度”然后按组合键Ctrl+Shift+Enter。
将得到以下结果:
图9-3-7定义数组名称
图9-3-8数组名称的使用
将命名常量用作数组公式时,切记要输入等号。
如果未输入等号,Excel会将该数组解释为文本字符串。
最后,请记住可以使用文本和数字的组合。
3、编辑数组公式
一个数组包含数个单元格,这些单元格形成一个整体,所以,数组中的单元格不能单独进行编辑、清除和移动,也不能插入或删除单元格,在对数组进行各种编辑操作之前,必须先选取整个数组,然后进行相应的操作。
如果要编辑数组,可以执行如下的操作步骤:
步骤1:
选定要编辑的数组。
步骤2:
再将鼠标移动到编辑栏上直接单击鼠标左键,或直接按下F2键,使得表示数组公式的括号消失。
步骤3:
在代表数组的括号消失后,对公式进行编辑,在编辑完成后,按下组合键Ctrl+Shift+Ente,完成数组公式的编辑。
如果要删除数组,可以选定要删除的数组,按下组合键Ctrl+Delete或选择“编辑”菜单中的“清除”命令即可完成。
4、数组公式的应用
在上面的例子中我们举了一个多单元格(区域D2:
D4)数组公式的实例,下面我们再举一个单个单元格的数组公式的实例来说明数组公式在实际工作中的应用。
问题:
现有商品销售工作表如图9-3-9所示,要求在F1求出全部商品的销售总金额。
这一题如果用普通公式又怎么解决呢?
一般会想可能有两种方法:
A、插入辅助列,先求出各商品的销售额,然后再求总和。
B、直接在F1输入公式“=SUM(B2*C2,B3*C3,B4*C4)”,这样看上去不错,可是,如果有100行数据,一千行号数据呢?
先不考虑单元格能容纳多少字符的问题,就光输入公式,累也得把你累趴下,显然是行不通的。
这时候就需要用数组公式来完成了。
图9-3-9求商品销售总金额
操作方法:
选中F1单元格,输入公式“=SUM(B2:
B4*C2:
C4)”,按下组合键Ctrl+Shift+Ente确认输入即可。
如下图所示:
图9-3-10数组公式的应用
这是一个单个单元格的数组公式,B2:
B4*C2:
C4是两个一维数组相乘,返回一个新的一维数组,最后用SUM函数对返回的数组进行了求和。
这里,用一个数组公式代替了多个公式的方式来完成了数据的计算。
对该问题总结一下,什么时候会用到数组公式?
是的,当运算中存在着一些只有通过复杂的中间运算过程才会等到结果的时候,就需要使用数组公式了。
9.4Excel常用函数的使用
在9.2函数概述这一节中我们简单介绍了函数的定义、使用方法以及10类函数的基本功能。
在本节我们将结合实例对其中一些比较重要的函数加以较详细的介绍。
9.4.1 财务函数
财务函数可以进行一般的财务计算,如确定贷款的支付额、投资的未来值或净现值,以及债券或息票的价值。
这些财务函数大体上可分为四类:
投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。
财务函数中常见的参数:
●未来值(fv)--在所有付款发生后的投资或贷款的价值。
●期间数(nper)--为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。
●付款(pmt)--对于一项投资或贷款的定期支付数额。
其数值在整个年金期间保持不变。
通常pmt包括本金和利息,但不包括其他费用及税款。
●现值(pv)--在投资期初的投资或贷款的价值。
例如,贷款的现值为所借入的本金数额。
●利率(rate)--投资或贷款的利率或贴现率。
●类型(type)--付款期间内进行支付的间隔,如在月初或月末,用0或1表示。
●日计数基准类型(basis)--为日计数基准类型。
Basis为0或省略代表US(NASD)30/360,为1代表实际天数/实际天数,为2代表实际天数/360,为3代表实际天数/365,为4代表欧洲30/360。
一、投资计算函数
投资计算函数可分为与未来值fv有关,与付款pmt有关,与现值pv有关,与复利计算有关及与期间数有关几类函数。
1、与未来值fv有关的函数--FV、FVSCHEDULE
2、与付款pmt有关的函数--IPMT、ISPMT、PMT、PPMT
3、与现值pv有关的函数--NPV、PV、XNPV
4、与复利计算有关的函数--EFFECT、NOMINAL
5、与期间数有关的函数--NPER
这里我们重点介绍FV、PMT和PV函数。
(一)求某项投资的未来值FV
FV函数是基于固定利率及等额分期付款方式,返回某项投资的未来值。
语法形式为:
FV(rate,nper,pmt,pv,type)
其中rate为各期利率,是一固定值,nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为各期所应付给(或得到)的金额,其数值在整个年金期间(或投资期内)保持不变,通常Pv包括本金和利息,但不包括其它费用及税款,pv为现值,或一系列未来付款当前值的累积和,也称为本金,如果省略pv,则假设其值为零,type为数字0或1,用以指定各期的付款时间是在期初还是期末,如果省略t,则假设其值为零。
例如:
假如某人两年后需要一笔比较大的学习费用支出,计划从现在起每月初存入2000元,如果按年利2.25%,按月计息(月利为2.25%/12),那么两年以后该账户的存款额会是多少呢?
公式写为:
FV(2.25%/12,24,-2000,0,1)
各参数含义见下图9-4-1所示:
图9-4-1 FV函数及说明
(二)求贷款分期偿还额PMT
PMT函数基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额。
PMT函数可以计算为偿还一笔贷款,要求在一定周期内支付完时,每次需要支付的偿还额,也就是我们平时所说的"分期付款"。
比如借购房贷款或其它贷款时,可以计算每期的偿还额。
其语法形式为:
PMT(rate,nper,pv,fv,type)
其中,rate为各期利率,是一固定值,nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为现值,或一系列未来付款当前值的累积和,也称为本金,fv为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv,则假设其值为零(例如,一笔贷款的未来值即为零),type为0或1,用以指定各期的付款时间是在期初还是期末。
如果省略type,则假设其值为零。
例如,需要10个月付清的年利率为8%的¥10,000贷款的月支额为:
PMT(8%/12,10,10000) 计算结果为:
-¥1,037.03。
(三)求某项投资的现值PV
PV函数用来计算某项投资的现值。
年金现值就是未来各期年金现在的价值的总和。
如果投资回收的当前价值大于投资的价值,则这项投资是有收益的。
其语法形式为:
PV(rate,nper,pmt,fv,type)
其中Rate为各期利率。
Nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。
Pmt为各期所应支付的金额,其数值在整个年金期间保持不变。
通常pmt包括本金和利息,但不包括其他费用及税款。
Fv为未来值,或在最后一次支付后希望得到的现金余额,如果省略fv,则假设其值为零(一笔贷款的未来值即为零)。
Type用以指定各期的付款时间是在期初还是期末。
例如,假设要购买一项保险年金,该保险可以在今后二十年内于每月末回报¥600。
此项年金的购买成本为80,000,假定投资回报率为8%。
那么该项年金的现值为:
PV(0.08/12,12*20,600,0) 计算结果为:
¥-71,732.58。
负值表示这是一笔付款,也就是支出现金流。
年金(¥-71,732.58)的现值小于实际支付的(¥80,000)。
因此,这不是一项合算的投资。
各参数说明见下图9-4-2所示:
图9-4-2 PV函数及说明
二、折旧计算函数
折旧计算函数主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。
这些函数都是用来计算资产折旧的,只是采用了不同的计算方法。
这里,对于具体的计算公式不再赘述,具体选用哪种折旧方法,则须视各单位情况而定。
我们仅以SLN函数为例举例介绍:
●SLN函数计算的是资料原值;语法格式如下:
SLN(cost,salvage,life)
其中:
cost表示的产资产原值;salvage表示的是资产在折旧期末的价值,即资产残值life表示的是折旧期限,即资产的使用寿命。
例:
现有固定资产情况表如下图9-4-3所示:
现要分别计算“每天折旧值、每月折旧值和每年折旧值”,填入折旧值情况表中,具体操作步骤如下:
图9-4-3 SLN函数的应用
步骤1:
选中单位格E2,输入公式:
“=SLN(B2,B3,B4*365)”,按回车键。
步骤2:
选中单位格E2,输入公式:
“=SLN(B2,B3,B4*12)”,按回车键。
步骤3:
选中单位格E2,输入公式:
“=SLN(B2,B3,B4)”,按回车键。
三、偿还率计算函数
偿还率计算函数主要用以计算内部收益率,包括IRR、MIRR、RATE和XIRR几个函数。
四、债券及其他金融函数
债券及其他金融函数又可分为计算本金、利息的函数,与利息支付时间有关的函数、与利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。
1、计算本金、利息的函数--CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM
2、与利息支付时间有关的函数--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD
3、与利率收益率有关的函数--INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT
4、与修正期限有关的函数--DURATION、MDURATION
5、与有价证券有关的函数--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED
6、与证券价格表示有关的函数--DOLLARDE、DOLLARFR
9.4.2 日期与时间函数
1、取出当前系统时间/日期信息的函数
用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。
语法形式均为:
函数名()
例如:
NOW() 结果返回现在的日期和时间
TODAY() 结果返回现在的日期
2、取得日期/时间的部分字段值函数
如果需要单独的年份、月份、日数或小时的数据时,可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。
例如,当前工作表的E5单元格中内容为:
2010-8-3012:
30PM,现在返回E5的年份、月份、日数及小时数,可以分别采用相应函数实现。
YEAR(E5) 结果为:
2010
MONTH(E5) 结果为:
8
DAY(E5) 结果为:
30
HOUR(E5) 结果为:
12
MINUTE(E5) 结果为:
30
例:
现有工作表内容如图9-4-4所示:
要求分别求出其中的年龄和工龄字段的值。
操作步骤如下:
步骤1:
单击C2单元格输入公式:
“=YEAR(TODAY())-YEAR(B2)”
步骤2:
双击C2单元格的填充柄。
步骤1:
单击E2单元格输入公式:
“=YEAR(TODAY())-YEAR(D2)”
步骤2:
双击E2单元格的填充柄。
图9-4-4 日期函数的应用
9.4.3 数学与三角函数
1、SUM函数
返回某一单元格区域中所有数字之和。
语法格式:
SUM(number1,number2,...)
Number1,number2,... 为1到30个需要求和的参数。
即被求和的单元格或单元格区域不能超过30个。
2、SUMIF函数
SUMIF函数可对满足某一条件的单元格区域求和,该条件可以是数值、文本或表达式,可以应用在人事、工资和成绩统计中。
其语法格式:
SUMIF(range,criteria,sum_range)
●range 为用于条件判断的单元格区域。
●criteria 为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式或文本。
例如,条件可以表示为32、"32"、">32"或"apples"。
●sum_range 是需要求和的实际单元格区域
例:
现有工作表数据如下图9-4-5所示:
现要统计各种商品的采购总量和采购总金额。
具体操作步骤如下:
步骤1:
选中J12单元格,输入公式:
“=SUMIF($A$11:
$A$43,I12,$B$11:
$B$43)”。
(即求区域A11:
A43中内容是I12的值即“衣服”的单元格对应B列内容数值的和。
其中区域:
B11:
B43是实际求和的区域。
)
步骤2:
双击J12单元格的填充柄。
步骤3:
选中K12单元格,输入公式:
“=SUMIF($A$11:
$A$43,I12,$F$11:
$F$43)”。
步骤2:
双击K12单元格的填充柄。
图9-4-5 SUMIF函数的应用
3、舍入函数:
在实际工作的数学运算中,特别是财务计算中常常遇到四舍五入的问题。
虽然,excel的单元格格式中允许你定义小数位数,但是在实际操作中,我们发现,其实数字本身并没有真正的四舍五入,只是显示结果似乎四舍五入了。
这在财务运算中是不允许的。
那是否有简单可行的方法来进行真正的四舍五入呢?
有,那就要借助于函数了。
Excel的舍入函数很多,详见表9-4-1所示。
这里我们着重介绍两个常用的舍入函数:
(1)ROUND函数
它的功能就是根据指定的位数,将数字四舍五入。
其语法格式:
ROUND(number,num_digits)
其中number就是将要进行四舍五入的数字;num_digits则是希望得到的数字的小数点后的位数。
例如图9-4-6所示:
单元格B2中为初始数据0.123456,B3的初始数据为0.234567,将要对它们进行四舍五入。
在单元格C2中输入“=ROUND(B2,2)”,小数点后保留两位有效数字,得到0.12、0.23。
在单元格D2中输入“=ROUND(B2,4)”,则小数点保留四位有效数字,得到0.1235、0.2346。
图9-4-6对数字进行四舍五入
(2)INT函数
对于数字进行四舍五入,还可以使用INT(取整函数),但由于这个函数的定义是返回实数舍入后的整数值。
因此,用INT函数进行四舍五入还是需要一些技巧的,也就是要加上0.5,才能达到取整的目的。
仍然以上图9-4-6为例,如果采用INT函数,则C2公式应写成:
“=INT(B2*100+0.5)/100”。
表9-4-1常用舍入函数说明
CEILING函数
将参数Number沿绝对值增大的方向,舍入为最接近的整数或基数
EVEN函数
返回沿绝对值增大方向取整后最接近的偶数
FLOOR函数
将参数Number沿绝对值减小的方向去尾舍入,使其等于最接近的significance的倍数
INT函数
返回实数舍入后的整数值
ODD函数
返回对指定数值进行舍入后的奇数
ROUND函数
返回某个数字按指定位数舍入后的数字
ROUNDDOWN函数
靠近零值,向下(绝对值减小的方向)舍入数字
ROUNDUP函数
远离零值,向上(绝对值增大的方向)舍入数字
TRUNC函数
将数字的小数部分截去,返回整数
9.4.4 统计函数
Excel的统计工作表函数用于对数据区域进行统计分析。
1、AVERAGE函数
求参数的算术平均值函数。
AVERAGE语法形式为:
AVERAGE(number1,number2,...)
其中Number1,number2,...为要计算平均值的1~30个参数。
这些参数可以是数字,或者是涉及数字的名称、数组或引用。
如果数组或单元格引用参数中有文字、逻辑值或空单元格,则忽略其值。
但是,如果单元格包含零值则计算在内。
2、COUNT函数
COUNT函数用于返回数字参数的个数,即统计数组或单元格区域中含有数值类型的单元格个数,语法格式为:
COUNT(value1,value2,...)
其中Value1,value2,...为包含或引用各种类型数据的参数(1~30个),但只有数字类型的数据才被计数。
函数COUNT在计数时,将把数字、空值、逻辑值、日期或以文字代表的数计算进去;但是错误值或其他无法转化成数字的文字则被忽略。
如果参数是一个数组或引用,那么只统计数组或引用中的数字;数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。
如果要统计逻辑值、文字或错误值,应当使用函数COUNTA。
3、COUNTBLANK函数
统计某个单元格区域中空白单元格的数目,语法格式为:
COUNTBLANK(range)
参数range表示的是需要计算其中空白单元格数目的区域。
4、COUNTIF函数
计算区域中满足给定条件的单元格的个数,其语法格式为:
COUNTIF(range,criteria)
其中参数range表示的是需要计算其中满足条件的单元格数目的单元格区域,参数criteria表示的是确定哪些单元格将被计算在内
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- AOA Excel