专题8Excel函数计算数据.docx
《专题8Excel函数计算数据.docx》由会员分享，可在线阅读，更多相关《专题8Excel函数计算数据.docx（36页珍藏版）》请在冰豆网上搜索。
专题8Excel函数计算数据
专题8：
Excel函数计算数据
目录
1.公式和函数输入语法1
【案例】：
设计10年年利率分别为1%、3%、5%、7%、9%的期末折现系数表2
2.财务函数3
【案例】：
名义利率与实际利率的换算4
【案例】：
投资项目的内部收益率IRR6
【案例】：
用双倍余额递减法设计折旧表7
【案例】：
用年数总和法设计折旧表8
3.日期与时间函数9
【案例】：
计算应收账款账龄10
4.数学与三角函数10
【案例】：
标准化考室及申领试卷数量的计算12
【案例】：
矩阵的四则运算、转置及求逆运算13
【案例】：
随机排序分配座位编号14
5.统计函数15
6.查找与引用函数17
【案例】：
按关键字自动向数据表输入其他信息18
7.数据库函数19
8.文本和数据函数20
【案例】：
自动提取身份证号码中的性别和出生日期信息20
9.逻辑函数21
【案例】：
根据长短位数不同的身份证号码自动判别性别并提取出生日期22
10.信息函数22
11.工程函数23
12.外部函数24
Excel虽然在编辑格式上是一张大表格，但它并非仅仅是表格而已。
用Excel的表格来显示数据，也并非仅仅是美观，更重要的是，Excel强大的计算和分析功能。
Excel提供丰富的函数，以帮助解决各种计算和分析问题。
1.公式和函数输入语法
在Excel单元格中输入公式，一般语法为“＝公式表达式”
函数其实也是一种公式。
在Excel单元格中输入函数，一般语法为“＝函数名称（参数1，参数2,…）”。
公式的一般四则混合运算符号，与一般数学运算符号相似，如“＋”（加）、“－”（减）、“*”（乘）、“/”（除）、“^”（幂），括号只有“（）”（小括号），但可以叠加多重括号。
函数实际上是部分特殊运算的快捷运算方式。
而Excel中的函数要在加载了分析工具库后才会显示完全。
如图1.1，在“工具”菜单中选择“加载宏”，选择“分析工具库”，确定即可。
图1.1
【案例】：
设计10年年利率分别为1%、3%、5%、7%、9%的期末折现系数表
在财务、金融或精算理论中，n年期折现系数
，根据不同的利率情况，列表显示各年的折现系数。
如图1.2，在第1行造成1%到9%的序列（利用快捷输入方法，根据1%和3%的规律拖曳自动生成），在A列生成1到10的序列（同样的快捷方法）。
图1.2
如图1.3，在B2单元格中输入公式“=1/（1+B$1）^$A2”，然后将公式用鼠标向下拖曳到B11单元格。
再将整列的公式统一向右拖曳复制到F列。
因为是先向下拖曳，再向右拖曳，所以用对利率用“B$1”只对行号“1”作绝对引用，便于向右拖时列号“B”逐一变到“F”，对幂指数用“$A2”只对列号“A”作绝对引用，便于向下拖曳时，幂由“1”变到“10”。
如图1.4，鼠标置于E10单元格，检查其公式引用正确无误。
图1.3
图1.4
【案例扩展】折现系数表经常用在投资分析中，各个不同时期的资金是不可直接比较的，必须将各期资金统一折现或者计算终值，在同一个时点上，才可以对其进行比较。
2.财务函数
Excel提供了详细的财务函数，用于金融、财务、投资、精算等计算和分析。
详细函数参见表2.1，对各种函数的具体使用过程可以参见Excel帮助文件中的示例。
表2.1 Excel财务函数表
ACCRINT
返回定期付息有价证券的应计利息
ACCRINTM
返回到期一次性付息有价证券的应计利息
AMORDEGRC
返回使用折旧系数的每个结算期间的折旧值
AMORLINC
返回每个结算期间的折旧值
COUPDAYBS
返回当前付息期内截止到成交日的天数
COUPDAYS
返回包含成交日所在的付息期的天数
COUPDAYSNC
返回从成交日到下一付息日之间的天数
COUPNCD
返回成交日之后的下一个付息日
COUPNUM
返回成交日和到期日之间的付息次数
COUPPCD
返回成交日之前的前一个的付息日
CUMIPMT
返回两个期间内累计支付的利息
CUMPRINC
返回一笔贷款在两个期间内累计偿还的本金数额
DB
使用固定余额递减法，返回一笔资产在给定期间内的折旧值
DDB
使用双倍余额递减法或其他指定方法，返回一笔资产在给定期间内的折旧值
DISC
返回有价证券的贴现率
DOLLARDE
将按分数表示的价格转换为按小数表示的价格
DOLLARFR
将按小数表示的价格转换为按分数表示的价格
DURATION
返回定期付息有价证券的每年期限
EFFECT
返回实际年利率
FV
返回一笔投资的未来值
FVSCHEDULE
返回应用一系列复利率后的本金的未来值
INTRATE
返回一次性付息有价证券的利率
IPMT
返回一笔投资在给定期间内支付的利息
IRR
返回一系列现金流的内部收益率
ISPMT
计算特定投资期内要支付的利息
MDURATION
返回假设面值$100的有价证券的Macauley修正期限
MIRR
返回正和负现金流以不同利率进行计算的内部收益率
NOMINAL
返回年名义利率
NPER
返回投资期间的数量
NPV
返回基于一系列定期现金流和贴现率计算的投资的现净值
ODDFPRICE
返回首期付息日不固定的面值$100的有价证券价格
ODDFYIELD
返回首期付息日不固定的有价证券的收益率
ODDLPRICE
返回末期付息日不固定的面值$100的有价证券价格
ODDLYIELD
返回末期付息日不固定的有价证券的收益率
PMT
返回年金的定期支付金额
PPMT
返回一笔投资在给定期间内偿还的本金
PRICE
返回定期付息的面值$100的有价证券的价格
PRICEDISC
返回折价发行的面值$100的有价证券的价格
PRICEMAT
返回到期付息的面值$100的有价证券的价格
PV
返回投资的现值
RATE
返回年金的各期利率
RECEIVED
返回一次性付息的有价证券到期收回的金额
SLN
返回一笔资产在某个期间内的线性折旧值
SYD
返回一笔资产按年限总和折旧法计算的指定期间的折旧值
TBILLEQ
返回国库券的等效收益率
TBILLPRICE
返回面值$100的国库券的价格
TBILLYIELD
返回国库券的收益率
VDB
使用余额递减法，返回一笔资产在给定期间或部分期间内的折旧值
XIRR
返回一组不定期发生的现金流的内部收益率
XNPV
返回一组不定期发生的现金流的净现值
YIELD
返回定期付息有价证券的收益率
YIELDDISC
返回折价发行的有价证券（如国库券）的年收益率
YIELDMAT
返回到期付息的有价证券的年收益率
【案例】：
名义利率与实际利率的换算
一般地，如果一期结算m次，那么称
期的实际利率的m倍为该期的名义利率，记为i（m）。
期的实际利率为
。
名义利率与实际利率换算的公式为
。
如果要计算与年实际利率8%等价的每年计算2次、4次、8次、……的年名义利率，以及与年名义利率8%等价的每年计算2次、4次、8次、……的年实际利率，可以用到Excel中提供的Nominal（）和Effect（）函数。
计算名义利率函数的语法为Nominal（effect_rate,npery），其中Effect_rate为实际利率，Npery为每年的复利期数。
Npery将被自动地截尾取整。
如果Effect_rate≤0或Npery<1，函数将返回错误值#NUM!
。
要利用Nominal（）计算名义利率，参见图2.1，在B3单元格中输入公式“=NOMINAL（$B$1,A3）”，回车确认后用鼠标将单元格公式向下拖曳到B20单元格。
实际利率单元格B1用绝对引用。
图2.1
计算实际利率函数的语法为Effect（nominal_rate,npery），其中Nominal_rate为名义利率，Npery为每年的复利期数。
Npery将被自动地截尾取整。
如果Nominal_rate≤0或Npery<1，函数将返回错误值#NUM!
。
该函数的各参数的计算公式如下，这与前面所介绍的换算公式也是一致的。
要利用Effect（）计算名义利率，如图2.2，在B3单元格中输入公式“=EFFECT（$B$1,A3）”，回车确认后用鼠标将单元格公式向下拖曳到B20单元格。
名义利率单元格B1用绝对引用。
图2.2
【案例】：
投资项目的内部收益率IRR
投资项目资金净流入现值记为：
，该项目内部收益率即是方程
的根。
一个20年期的投资项目，投资者在第1年初投入30000元，第2年初投入20000元，第3年初投入10000元，以后每年初承担2000元的维持费。
该项目预期从第3年末开始，每年末提供投资回报，从3000元开始，以后第一年比上一年增加1000元，该项目的内部收益率是多少呢。
Excel提供IRR（）函数计算一系列现金流的内部收益率，这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。
内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。
其语法为IRR（values,guess）。
Values为数组或单元格的引用，包含用来计算返回的内部收益率的数字，Values必须包含至少一个正值和一个负值，以计算返回的内部收益率。
函数IRR根据数值的顺序来解释现金流的顺序。
故应确定按需要的顺序输入了支付和收入的数值。
Guess为对函数IRR计算结果的估计值。
MicrosoftExcel使用迭代法计算函数IRR。
从guess开始，函数IRR进行循环计算，直至结果的精度达到0.00001%。
如果函数IRR经过20次迭代，仍未找到结果，则返回错误值#NUM!
。
在大多数情况下，并不需要为函数IRR的计算提供guess值。
如果省略guess，假设它为0.1（10%）。
如果函数IRR返回错误值#NUM!
，或结果没有靠近期望值，可用另一个guess值再试一次。
如图2.3，第一步，设定投资项目资金流量表，仅需要计算每期的资金净流入。
第二步，在B2中输入公式“=IRR（D3:
D23）”，计算出该投资项目的内部报酬率为8.535852%。
图2.3
【案例】：
用双倍余额递减法设计折旧表
双倍余额递减法的计算公式为：
年折旧额
，即上年末账面净值与折旧率乘积，年折旧率
。
一台设备价值12,000元，预计使用10年，预计使用残值为1,000元。
计算双倍余额递减法下各年折旧情况。
Excel中提供的年数总和法的函数命令DDB，可以方便地计算各年的折旧额。
第一步，如图2.4，建立基本信息，用函数就可以不用计算每年的折旧率。
图2.4
第二步，在C6单元格中插入函数，选择“财务”函数中的DDB函数，在弹出的对话框中设置“Cost”为A2单元格（即A），“Salvage”为C2单元格（即S），“Life”为B2单元格（即n），“Period”为A6单元格（即k=1），其余省略，除A6单元格表示年份数要变化外，其余都用绝对引用（按F4可快速切换）。
然后确定。
第三步，用鼠标将公式向下拖曳至C13单元格。
需要注意的是，DDB函数提供参数“Factor”，省略则默认值为2，也即是双倍余额递减法，如果用其他值，就是另一种加速效果了。
【案例】：
用年数总和法设计折旧表
固定资产折旧计算中的符号约定
A—
固定资产原值
S—
固定资产残将
n—
预计使用年数
Dk—
第k期的折旧额
dk—
第k期的折旧率
年数总和法的计算公式为：
年折旧额
，年折旧率
，也即是
。
一台设备价值12,000元，预计使用10年，预计使用残值为1,000元。
计算年数总和法下各年折旧情况。
Excel中提供的年数总和法的函数命令SYD，可以方便地计算第k年的折旧额。
第一步，如图2.5，建立基本信息，用函数就可以不用计算每年的折旧率。
图2.5
第二步，在C6单元格中插入公式，选择“财务”函数中的SYD函数，在弹出的对话框中设置“Cost”为A2单元格（即A），“Salvage”为C2单元格（即S），“Life”为B2单元格（即n），“Per”为A6单元格（即k=1），除A6单元格表示年份数要变化外，其余都用绝对引用（按F4可快速切换）。
然后确定。
第三步，用鼠标将公式向下拖曳至C15单元格。
3.日期与时间函数
Excel提供了的日期与时间函数，用于编辑排版以及金融、财务、投资、精算等计算和分析的需要。
详细函数参见表3.1，对各种函数的具体使用过程可以参见Excel帮助文件中的示例。
表3.1 Excel日期与时间函数表
DATE
返回特定日期的序列号
DATEVALUE
将文本格式的日期转换为序列号
DAY
将序列号转换为月的日期
DAYS360
计算基于一年360天的两个日期间的天数
EDATE
返回用于表示开始日期之前或之后月数的日期的序列号
EOMONTH
返回指定个数月之前或之后的月的末日的序列号
HOUR
将序列号转换为小时
MINUTE
将序列号转换为分钟
MONTH
将序列号转换为月
NETWORKDAYS
返回两个日期之间的所有工作日个数
NOW
返回当前日期和时间的序列号
SECOND
将序列号转换为秒
TIME
返回特定时间的序列号
TIMEVALUE
将文本格式的时间转换为序列号
TODAY
返回今天日期的序列号
WEEKDAY
将序列号转换为一星期的某天
WEEKNUM
将序列号转换为代表该星期为一年中的第几周的数字
WORKDAY
返回指定个数工作日之前或之后日期的序列号
YEAR
将序列号转换为年
YEARFRAC
返回代表start_date和end_date之间的天数的年分数
在Excel编辑排版设计表格时，往往需要表示打印日期或制作时期，此时只需要在对应单元格中输入公式“=today（）”，则会显示出当前计算机日期来，而且每次打开文档时都会自动更新。
在财务上将票据向银行贴现或者计算利息时，往往要计算两个日期日间的天数，以便计算帖息或利息，用Days360（）函数，可以迅速得到结果，而不必翻日历或者手动计算天数。
【案例】：
计算应收账款账龄
在财务会计和资产评估中，正确对应收账款计价非常重要。
财务制度规定，对3年以上的应收账款可以全额计提坏账准备金。
如图3.1，在这个应收账款清单中，在C3单元格输入公式“=DAYS360（A3,$B$1）”，计算出第1笔应收账款到截止日期的天数（以1年360天计）。
将公式向下拖曳到最后一笔。
然后筛选出所有账龄大于或等于3×360＝
天，计算其总和。
图3.1
【案例扩展】还可以进一步筛选出账龄在1年内，在1年到2年之间的应收账款，计算其各占比例，以便分析。
4.数学与三角函数
Excel提供了丰富的数学与三角函数，用于科学计算、工程计算、矩阵运算、精算等需要。
详细函数参见表4.1，对各种函数的具体使用过程可以参见Excel帮助文件中的示例。
表4.1 Excel数学与三角函数表
ABS
返回数字的绝对值
ACOS
返回数字的反余弦值
ACOSH
返回数字的反双曲余弦值
ASIN
返回数字的反正弦值
ASINH
返回数字的反双曲正弦值
ATAN
返回数字的反正切值
ATAN2
返回X和Y坐标的反正切值
ATANH
返回数字的反双曲正切值
CEILING
返回大于等于数值的最小整数
COMBIN
返回给定数目对象的组合数
COS
返回数字的余弦
COSH
返回数字的双曲余弦值
DEGREES
将弧度转换为度
EVEN
将数字向上舍入为最接近的偶数
EXP
返回e的给定数字次幂
FACT
返回数字的阶乘
FACTDOUBLE
返回数字的双倍阶乘
FLOOR
向绝对值减小的方向舍入数字
GCD
返回最大公约数
INT
将数字向下舍入到最接近的整数
LCM
返回最小公倍数
LN
返回数字的自然对数
LOG
按所指定的底数，返回数字的对数
LOG10
返回数字的以10为底的对数
MDETERM
返回数组的矩阵行列式的值
MINVERSE
返回数组的逆矩阵
MMULT
返回两个数组的矩阵乘积
MOD
返回除法的余数
MROUND
返回舍入到指定倍数的数值
MULTINOMIAL
返回一组数字的多项式
ODD
将数字向上舍入为最接近的奇数
PI
返回pi的值
POWER
返回给定数字次幂的结果
PRODUCT
将其参数相乘
QUOTIENT
返回除法的整数部分
RADIANS
将角度转换为弧度
RAND
返回0和1之间的一个随机数
RANDBETWEEN
返回指定数字间的随机数
ROMAN
将阿拉伯数字转换为文本形式的罗马数字
ROUND
将数字按指定位数舍入
ROUNDDOWN
向绝对值减小的方向舍入数字
ROUNDUP
向绝对值增大的方向舍入数字
SERIESSUM
返回基于以下公式的幂级数之和
SIGN
返回数字的符号
SIN
返回给定角度的正弦值
SINH
返回数字的双曲正弦值
SQRT
返回正平方根
SQRTPI
返回某数与pi的乘积的平方根
SUBTOTAL
返回列表或数据库中的分类汇总
SUM
将其参数相加
SUMIF
按给定条件对若干单元格求和
SUMPRODUCT
返回对应的数组元素的乘积和
SUMSQ
返回参数的平方和
SUMX2MY2
返回两个数组中对应值的平方差之和
SUMX2PY2
返回两个数组中对应值的平方和之和
SUMXMY2
返回两数组中对应值的平方差的之和
TAN
返回数字的正切
TANH
返回数字的双曲正切值
TRUNC
将数字截尾取整
【案例】：
标准化考室及申领试卷数量的计算
某大型集团公司，在省内各地都有分支机构，该公司拟对各地区分支机构各部门员工进行业务技能测试，要求每个标准化考室30人，试题印制就按每袋30份包装，如果某地考试人数刚好比标准化考室容纳的人数多1人，也要申请一整袋试卷。
如何迅速计算出各地申请领取的试卷呢？
如图4.1，在B3和M15区域输入各地区各部门报名考试人数。
在第16行和N列求和，即是选中B3到N16，刚好比数据区域多一行一列，点击“常用工具栏”上的“自动求和”按钮即可。
在B17单元格输入公式“=B16/30”，计算各地标准化考室的数学意义上的数量，并向右拖曳复制公式到M17。
在B18输入公式“=CEILING（B17,1）”，计算出实际意义上的考室数量，并向右拖曳复制公式到M18。
在N18单元格对该行求和，即是全公司总的试卷袋数，也即是要求印刷厂印制数量的基础。
图4.1
【案例扩展】在B18中，还可以输入公式“=TRUNC（B17）+1”，即是将数字截尾取整，再加1，但这有个不足，如果数学意义上的数量刚好与实际意义上的数量相同，则计算出的后者要多于实际需要量。
当然在B18中，还可以输入公式“=ROUNDUP（B17,0）”，即向绝对值增大的方向舍入。
在此案例中，不能直接用四舍五入函数Round（），除非加上对小数点后面数字的逻辑判断。
【案例】：
矩阵的四则运算、转置及求逆运算
在表格中列示的数据，其实也就体现出一种矩阵数据的思维。
Excel还可以进行一些简单的矩阵运算。
如图4.2，对矩阵A和B，要计算（A＋B），则在I2单元格中输入公式“=A2+E2”，向下拖曳到I4，再整体拖曳到K列即可。
同理还可以计算（A－B）
如图4.2，要计算（A×B），则选中一个空白的3×3区域，如I7到K9区域，输入公式“=MMULT（A2:
C4,E2:
G4）”，或者在“插入”菜单中选择“函数”（在“常用公式栏”上点击“自动求和”按钮的下拉菜单中的“其他函数”也可），选择“数学与三角函数”，选择Mmult（）函数，数组“Array1”设置为“A2:
C4”（矩阵A），数组“Array2”设置为“E2:
G4”（矩阵B），点击F2键，再按“Ctrl+Shift+Enter”组合键，即计算出（A×B）。
图4.2
如图4.3，要计算A的转置矩阵AT，先复制A2到C10区域，即是矩阵A，鼠标置于A7，在“编辑”菜单中选择“选择性粘贴”，在“转置”复选框前面打勾，确定即可。
图4.3
如图4.4，要计算A的逆矩阵A－1，先选定一个3×3的空白区域，如E7到G9区域，输入公式“=MINVERSE（A2:
C4）”，或者在“插入”菜单中选择“函数”（在“常用公式栏”上点击“自动求和”按钮的下拉菜单中的“其他函数”也可），选择“数学与三角函数”，选择Minverse（）函数，数组“Array”设置为“A2:
C4”（矩阵A），再按“Ctrl+Shift+Enter”组合键，即计算出A－1。
图4.4
【案例扩展】如果要计算A/B，即是A×B－1。
【案例】：
随机排序分配座位编号
在考试考务组织过程中，考生的准考证号码按一定顺序分配的，但往往要求其座位是随机打乱的，这如何做到呢？
如图4.5，在这个考生名单中，考试座位是按“001”、“002”的顺序生成的序列，但之前姓名的排序如何做到随机呢？
在E列生成随机序列，在E2单元格中输入公式“=RAND（）”，向下拖曳到E17。
将数据表按E列排序，升降序都无关紧要。
这时姓名排序就已经是随机的了。
然后在D2中输入“’001”，用单引号表示输入成文本，否则系统会显示成“1”，用鼠标右键向处拖曳按序列填充到D17。
图4.5
【案例扩展】也可以在E2中输入公式“=RANDBETWEEN（1,17）”来产生随机序列，括号中的数值可随意设置，只是不要太小，否则会产生很多的相同数。
5.统计函数
Excel提供了丰富的统计函数，用于统计、计量、概率、精算分析等的需要。
详细函数参见表5.1，对各种函数的具体使用过程可以参见Excel帮助文件中的示例。
表5.1 Excel统计函数表
AVEDEV
返回数据点与它们的平均值的绝对偏差平均值
AVERAGE
返回其参数的平均值
AVERAGEA
返回其参数的平均值，包括数字、文本和逻辑值
BETADIST
返回Beta累积分布函数
BETAINV
返回指定Beta分布的累积分布函数的反函数
BINOMDIST
返回一元二项式分布的概率值
CHIDIST
返回γ2分布的单尾概率
CHIINV
返回γ2分布的单尾概率的反函数
CHITEST
返回独立性检验值
CONFIDENCE
返回总体平均值的置信区间
CORREL
返回两个数据集之间的相关系数
COUNT
计算参数列表中的数字个数
COUNTA
计算参数列表中的数值个数
COUNTBLANK
计算区间内的空白单元格个数
COUNTIF
计算满足给定条件的区间内的非空单元格个数
COVAR
返回协方差，成对偏差乘积的平均值
CRITBINOM
返回使累积二项式分布小于或等于临界值的最小值
DEVSQ
返回偏差的平方和
EXPONDIST
返回指数分布
FDIST
返回F概率分布
FINV
返回F概率分布的反函数值
FISHER
返回F
升级会员 