注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

碧海的博客

不要看远方模糊的东西,真真切切善待眼前的这一切!

 
 
 

日志

 
 

Excel 函数使用大全(中)  

2007-11-11 17:27:50|  分类: 学习经验探讨 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

四、一组用于求数据集的满足不同要求的数值的函数

1
、求数据集的最大值MAX与最小值MIN
这两个函数MAXMIN就是用来求解数据集的极值(即最大值、最小值)。函数的用法非常简单。语法形式为函数(number1,number2,...),其中Number1,number2,... 为需要找出最大数值的 1 30 个数值。如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。因此如果逻辑值和文本不能忽略,请使用带A的函数MAXA或者MINA 来代替。

2
、求数据集中第K个最大值LARGE与第k个最小值SMALL
这两个函数LARGESMALLMAXMIN非常想像,区别在于它们返回的不是极值,而是第K个值。语法形式为:函数(array,k),其中Array为需要找到第 k 个最小值的数组或数字型数据区域。K为返回的数据在数组或数据区域里的位置(如果是LARGE为从大到小排,若为SMALL函数则从小到大排)。
说到这,大家可以想得到吧。如果K=1或者K=n(假定数据集中有n个数据)的时候,是不是就可以返回数据集的最大值或者最小值了呢。

3
、 求数据集中的中位数MEDIAN
MEDIAN
函数返回给定数值集合的中位数。所谓中位数是指在一组数据中居于中间的数,换句话说,在这组数据中,有一半的数据比它大,有一半的数据比它小。
语法形式为MEDIAN(number1,number2, ...)其中Number1, number2,...是需要找出中位数的 1 30 个数字参数。如果数组或引用参数中包含有文字、逻辑值或空白单元格,则忽略这些值,但是其值为零的单元格会计算在内。
需要注意的是,如果参数集合中包含有偶数个数字,函数 MEDIAN 将返回位于中间的两个数的平均值。

4
、 求数据集中出现频率最多的数MODE
MODE
函数用来返回在某一数组或数据区域中出现频率最多的数值。跟 MEDIAN 一样,MODE 也是一个位置测量函数。
语法形式为MODE(number1,number2, ...)其中Number1, number2, ... 是用于众数(众数指在一组数值中出现频率最高的数值)计算的 1 30 个参数,也可以使用单一数组(即对数组区域的引用)来代替由逗号分隔的参数。

5
、 以上函数的示例
以某单位年终奖金分配表为例说明。在示例中,我们将利用这些函数求解该单位年终奖金分配中的最高金额、最低金额、平均金额、中间金额、众数金额以及第二高金额等。
详细的公式写法可从图中清楚的看出,在此不再赘述。

 
4

五、用来排位的函数RANKPERCENTRANK

1
、一个数值在一组数值中的排位的函数RANK
数值的排位是与数据清单中其他数值的相对大小,当然如果数据清单已经排过序了,则数值的排位就是它当前的位置。数据清单的排序可以使用Excel提供的排序功能完成。
语法形式为RANK(number,ref,order) 其中Number为需要找到排位的数字;Ref 为包含一组数字的数组或引用。Order为一数字用来指明排位的方式。
如果 order 0 或省略,则Excel ref 当作按降序排列的数据清单进行排位。
如果 order 不为零,Microsoft Excel ref 当作按升序排列的数据清单进行排位。
需要说明的是,函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。嗯,这就好像并列第几的概念啊。例如,在一列整数里,如果整数 10 出现两次,其排位为 5,则 11 的排位为 7(没有排位为 6 的数值)。

2
、求特定数值在一个数据集中的百分比排位的函数PERCENTRANK
PERCENTRANK函数可用于查看特定数据在数据集中所处的位置。例如,可以使用函数 PERCENTRANK 计算某个特定的能力测试得分在所有的能力测试得分中的位置。
语法形式为PERCENTRANK(array,x,significance) 其中Array为彼此间相对位置确定的数字数组或数字区域。X为数组中需要得到其排位的值。Significance为可选项,表示返回的百分数值的有效位数。如果省略,函数 PERCENTRANK 保留 3 位小数。

3
、与排名有关的示例
仍以某单位的年终奖金分配为例说明,这里以员工Annie的排名为例说明公式的写法。
奖金排名的公式写法为:
=RANK(C3,$C$3:$C$12)
百分比排名的公式写法为:
=PERCENTRANK($C$3:$C$12,C3)

 
5

以上我们介绍了Excel统计函数中比较常用的几种函数,更多的涉及专业领域的统计函数可以参看附表以及各种相关的统计学书籍。

附表:

函数名称

函数说明

语法形式

AVEDEV

返回一组数据与其均值的绝对偏差的平均值,即离散度。

AVEDEV(number1,number2, ...)

AVERAGE

返回参数算术平均值。

AVERAGE(number1,number2, ...)

AVERAGEA

计算参数清单中数值的平均值(算数平均值)。不仅数字,而且文本和逻辑值(如TRUE FALSE)也将计算在内。

AVERAGEA(value1,value2,...)

BETADIST

返回 Beta 分布累积函数的函数值。Beta 分布累积函数通常用于研究样本集合中某些事物的发生和变化情况。

BETADIST(x,alpha,beta,A,B)

BETAINV

返回 beta 分布累积函数的逆函数值。即,如果 probability = BETADIST(x,...),则 BETAINV(probability,...) = xbeta 分布累积函数可用于项目设计,在给定期望的完成时间和变化参数后,模拟可能的完成时间。

BETAINV(probability,alpha,beta,A,B)

BINOMDIST

返回一元二项式分布的概率值。

BINOMDIST(number_s,trials,probability_s,cumulative)

CHIDIST

返回 γ2 分布的单尾概率。γ2 分布与 γ2 检验相关。使用 γ2 检验可以比较观察值和期望值。

CHIDIST(x,degrees_freedom)

CHIINV

返回 γ2 分布单尾概率的逆函数。

CHIINV(probability,degrees_freedom)

CHITEST

返回独立性检验值。函数 CHITEST 返回 γ2 分布的统计值及相应的自由度。

CHITEST(actual_range,expected_range)

CONFIDENCE

返回总体平均值的置信区间。置信区间是样本平均值任意一侧的区域。

CONFIDENCE(alpha,standard_dev,size)

CORREL

返回单元格区域 array1 array2 之间的相关系数。使用相关系数可以确定两种属性之间的关系。

CORREL(array1,array2)

COUNT

返回参数的个数。利用函数 COUNT 可以计算数组或单元格区域中数字项的个数。

COUNT(value1,value2, ...)

COUNTA

返回参数组中非空值的数目。利用函数COUNTA 可以计算数组或单元格区域中数据项的个数。

COUNTA(value1,value2, ...)

COVAR

返回协方差,即每对数据点的偏差乘积的平均数,利用协方差可以决定两个数据集之间的关系。

COVAR(array1,array2)

CRITBINOM

返回使累积二项式分布大于等于临界值的最小值。此函数可以用于质量检验。

CRITBINOM(trials,probability_s,alpha)

DEVSQ

返回数据点与各自样本均值偏差的平方和。

DEVSQ(number1,number2,...)

EXPONDIST

返回指数分布。使用函数 EXPONDIST 可以建立事件之间的时间间隔模型。

EXPONDIST(x,lambda,cumulative)

FDIST

返回 F 概率分布。使用此函数可以确定两个数据系列是否存在变化程度上的不同。

FDIST(x,degrees_freedom1,degrees_freedom2)

FINV

返回 F 概率分布的逆函数值。

FINV(probability,degrees_freedom1,degrees_freedom2)

FISHER

返回点 x Fisher 变换。该变换生成一个近似正态分布而非偏斜的函数。

FISHER(x)

FISHERINV

返回 Fisher 变换的逆函数值。使用此变换可以分析数据区域或数组之间的相关性。

FISHERINV(y)

FORECAST

根据给定的数据计算或预测未来值。

FORECAST(x,known_y's,known_x's)

FREQUENCY

以一列垂直数组返回某个区域中数据的频率分布。

FREQUENCY(data_array,bins_array)

FTEST

返回 F 检验的结果。F 检验返回的是当数组 1 和数组 2 的方差无明显差异时的单尾概率。可以使用此函数来判断两个样本的方差是否不同。

FTEST(array1,array2)

GAMMADIST

返回伽玛分布。可以使用此函数来研究具有偏态分布的变量。伽玛分布通常用于排队分析。

GAMMADIST(x,alpha,beta,cumulative)

GAMMAINV

返回伽玛分布的累积函数的逆函数。

GAMMAINV(probability,alpha,beta)

GAMMALN

返回伽玛函数的自然对数,Γ(x)

GAMMALN(x)

GEOMEAN

返回正数数组或数据区域的几何平均值。

GEOMEAN(number1,number2, ...)

GROWTH

根据给定的数据预测指数增长值。

GROWTH(known_y's,known_x's,new_x's,const)

HARMEAN

返回数据集合的调和平均值。调和平均值与倒数的算术平均值互为倒数。

HARMEAN(number1,number2, ...)

HYPGEOMDIST

返回超几何分布。

HYPGEOMDIST(sample_s,number_sample,
population_s,number_population)

INTERCEPT

利用已知的 x 值与 y 值计算直线与 y 轴的截距。

INTERCEPT(known_y's,known_x's)

KURT

返回数据集的峰值。

KURT(number1,number2, ...)

LARGE

返回数据集里第 k 个最大值。使用此函数可以根据相对标准来选择数值。

LARGE(array,k)

LINEST

使用最小二乘法计算对已知数据进行最佳直线拟合,并返回描述此直线的数组。

LINEST(known_y's,known_x's,const,stats)

LOGEST

在回归分析中,计算最符合观测数据组的指数回归拟合曲线,并返回描述该曲线的数组。

LOGEST(known_y's,known_x's,const,stats)

LOGINV

返回 x 的对数正态分布累积函数的逆函数。

LOGINV(probability,mean,standard_dev)

LOGNORMDIST

返回 x 的对数正态分布的累积函数。

LOGNORMDIST(x,mean,standard_dev)

MAX

返回数据集中的最大数值。

MAX(number1,number2,...)

MAXA

返回参数清单中的最大数值。

MAXA(value1,value2,...)

MEDIAN

返回给定数值集合的中位数。中位数是在一组数据中居于中间的数。

MEDIAN(number1,number2, ...)

MIN

返回给定参数表中的最小值。

MIN(number1,number2, ...)

MINA

返回参数清单中的最小数值。

MINA(value1,value2,...)

MODE

返回在某一数组或数据区域中出现频率最多的数值。

MODE(number1,number2, ...)

NEGBINOMDIST

返回负二项式分布。

NEGBINOMDIST(number_f,number_s,probability_s)

NORMDIST

返回给定平均值和标准偏差的正态分布的累积函数。

NORMDIST(x,mean,standard_dev,cumulative)

NORMINV

返回给定平均值和标准偏差的正态分布的累积函数的逆函数。

NORMINV(probability,mean,standard_dev)

NORMSDIST

返回标准正态分布的累积函数,该分布的平均值为 0,标准偏差为 1

NORMSDIST(z)

NORMSINV

返回标准正态分布累积函数的逆函数。该分布的平均值为 0,标准偏差为 1

NORMSINV(probability)

PEARSON

返回 Pearson(皮尔生)乘积矩相关系数,r,这是一个范围在 -1.0 1.0 之间(包括 -1.0 1.0 在内)的无量纲指数,反映了两个数据集合之间的线性相关程度。

PEARSON(array1,array2)

PERCENTILE

返回数值区域的 K 百分比数值点。可以使用此函数来建立接受阀值。例如,可以确定得分排名在 90 个百分点以上的检测侯选人。

PERCENTILE(array,k)

PERCENTRANK

返回特定数值在一个数据集中的百分比排位。此函数可用于查看特定数据在数据集中所处的位置。例如,可以使用函数 PERCENTRANK 计算某个特定的能力测试得分在所有的能力测试得分中的位置。

PERCENTRANK(array,x,significance)

PERMUT

返回从给定数目的对象集合中选取的若干对象的排列数。排列可以为有内部顺序的对象或为事件的任意集合或子集。排列与组合不同,组合的内部顺序无意义。此函数可用于彩票计算中的概率。

PERMUT(number,number_chosen)

POISSON

返回泊松分布。泊松分布通常用于预测一段时间内事件发生的次数,比如一分钟内通过收费站的轿车的数量。

POISSON(x,mean,cumulative)

PROB

返回一概率事件组中落在指定区域内的事件所对应的概率之和。如果没有给出 upper_limit,则返回 x _range 内值等于 lower_limit 的概率。

PROB(x_range,prob_range,lower_limit,upper_limit)

QUARTILE

返回数据集的四分位数。四分位数通常用于在销售额和测量值数据集中对总体进行分组。例如,可以使用函数 QUARTILE 求得总体中前 25% 的收入值。

QUARTILE(array,quart)

RANK

返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小(如果数据清单已经排过序了,则数值的排位就是它当前的位置)。

RANK(number,ref,order)

RSQ

返回根据 known_y's known_x's 中数据点计算得出的 Pearson 乘积矩相关系数的平方。有关详细信息,请参阅函数 REARSONR 平方值可以解释为 y 方差与 x 方差的比例。

RSQ(known_y's,known_x's)

SKEW

返回分布的偏斜度。偏斜度反映以平均值为中心的分布的不对称程度。正偏斜度表示不对称边的分布更趋向正值。负偏斜度表示不对称边的分布更趋向负值。

SKEW(number1,number2,...)

SLOPE

返回根据 known_y's known_x's 中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的重直距离与水平距离的比值,也就是回归直线的变化率。

SLOPE(known_y's,known_x's)

SMALL

返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置上的数值。

SMALL(array,k)

STANDARDIZE

返回以 mean 为平均值,以 standard-dev 为标准偏差的分布的正态化数值。

STANDARDIZE(x,mean,standard_dev)

STDEV

估算样本的标准偏差。标准偏差反映相对于平均值(mean)的离散程度。

STDEV(number1,number2,...)

STDEVA

估算基于给定样本的标准偏差。标准偏差反映数值相对于平均值(mean)的离散程度。文本值和逻辑值(如 TRUE FALSE)也将计算在内。

STDEVA(value1,value2,...)

STDEVP

返回以参数形式给出的整个样本总体的标准偏差。标准偏差反映相对于平均值(mean)的离散程度。

STDEVP(number1,number2,...)

STDEVPA

计算样本总体的标准偏差。标准偏差反映数值相对于平均值(mean)的离散程度。

STDEVPA(value1,value2,...)

STEYX

返回通过线性回归法计算 y 预测值时所产生的标准误差。标准误差用来度量根据单个 x 变量计算出的 y 预测值的误差量。

STEYX(known_y's,known_x's)

TDIST

返回学生 t- 分布的百分点(概率),t 分布中数值 (x) t 的计算值(将计算其百分点)。t 分布用于小样本数据集合的假设检验。使用此函数可以代替 t 分布的临界值表。

TDIST(x,degrees_freedom,tails)

TINV

返回作为概率和自由度函数的学生 t 分布的 t 值。

TINV(probability,degrees_freedom)

TREND

返回一条线性回归拟合线的一组纵坐标值(y 值)。即找到适合给定的数组 known_y's known_x's 的直线(用最小二乘法),并返回指定数组 new_x's 值在直线上对应的 y 值。

TREND(known_y's,known_x's,new_x's,const)

TRIMMEAN

返回数据集的内部平均值。函数 TRIMMEAN 先从数据集的头部和尾部除去一定百分比的数据点,然后再求平均值。当希望在分析中剔除一部分数据的计算时,可以使用此函数。

TRIMMEAN(array,percent)

TTEST

返回与学生氏- t 检验相关的概率。可以使用函数 TTEST 判断两个样本是否可能来自两个具有相同均值的总体。

TTEST(array1,array2,tails,type)

VAR

估算样本方差。

VAR(number1,number2,...)

VARA

估算基于给定样本的方差。不仅数字,文本值和逻辑值(如 TRUE FALSE)也将计算在内。

VARA(value1,value2,...)

VARP

计算样本总体的方差。

VARP(number1,number2,...)

VARPA

计算样本总体的方差。不仅数字,文本值和逻辑值(如 TRUE FALSE)也将计算在内。

VARPA(value1,value2,...)

WEIBULL

返回韦伯分布。使用此函数可以进行可靠性分析,比如计算设备的平均故障时间。

WEIBULL(x,alpha,beta,cumulative)

ZTEST

返回 z 检验的双尾 P 值。Z 检验根据数据集或数组生成 x 的标准得分,并返回正态分布的双尾概率。可以使用此函数返回从某总体中抽取特定观测值的似然估计。

ZTEST(array,x,sigma)

 

Excel的工程函数与统计函数类似,都是属于比较专业范畴的函数。因此,在文中笔者也仅介绍几种比较常用的工程函数,更多的请参考Excel帮助和专业的书籍。顾名思义,工程工作表函数就是用于工程分析的函数。Excel中一共提供了近40个工程函数。工程工作表函数由"分析工具库"提供。如果您找不到此类函数的话,可能需要安装"分析工具库"

一、"分析工具库"的安装
如图所示

 
1

1)在"工具"菜单中,单击"加载宏"命令。

2)如果"加载宏"对话框中没有"分析工具库",请单击"浏览"按钮,定位到"分析工具库"加载宏文件"Analys32.xll"所在的驱动器和文件夹(通常位于"Microsoft Office\Office\Library\Analysis"文件夹中);如果没有找到该文件,应运行"安装"程序。
3) 选中"分析工具库"复选框。

二、工程函数的分类
Excel帮助系统中将工程函数大体可分为三种类型,即:
1)对复数进行处理的函数
2)在不同的数字系统(如十进制系统、十六进制系统、八进制系统和二进制系统)间进行数值转换的函数
3)在不同的度量系统中进行数值转换的函数
在文中为了对函数的解释更清晰,笔者把工程函数分为如下的六种类型,即:
1)贝赛尔(Bessel)函数
2)在不同的数字系统间进行数值转换的函数
3)用于筛选数据的函数
4)度量衡转换函数
5)与积分运算有关的函数
6)对复数进行处理的函数
下面逐一的对于这些工程函数进行介绍。

1
、贝赛尔(Bessel)函数
贝赛尔(Bessel)函数是特殊函数中应用最广泛的一种函数,在理论物理研究、应用数学、大气科学以及无线电等工程领域都有广泛的应用。在Excel中一共提供了四个函数,即:BESSELIBESSELJ BESSELKBESSELY
语法形式为:函数(x,n) 其中,X为参数值,N为函数的阶数。如果 n非整数,则截尾取整。需说明的是,如果 x 为非数值型,则贝赛尔(Bessel)函数返回错误值 #VALUE!。如果 n 为非数值型,则贝赛尔(Bessel)函数返回错误值 #VALUE!。如果 n 0,则贝赛尔(Bessel)函数返回错误值 #NUM!

2
、在不同的数字系统间进行数值转换的函数
Excel
工程函数中提供二进制、八进制、十进制与十六进制之间的数值转换函数。
这类工程函数名称非常容易记忆,只要记住二进制为BIN,八进制为OCT,十进制为DEC,十六进制为HEX。再记住函数名称中间有个数字2就可以容易的记住这些数值转换函数了。比如,如果需要将二进制数转换为十进制,应用的函数为前面BIN,中间加个2,后面为DEC,合起来这个函数就是BIN2DEC
简单列表为:

 
2

此类数值转换函数的语法形式也很容易记忆。
比如,将不同进制的数值转为十进制的语法形式为:函数(number),其中Number为待转换的某种进制数。
又如,将不同进制转换为其他进制的数值的语法形式为:函数(number,places)其中Number为待转换的数。Places为所要使用的字符数。当需要在返回的数值前置零时 places 尤其有用。

3
、用于筛选数据的函数DELTAGESTEP
1)用以测试两个数值是否相等的函数DELTA
DELTA
用以测试两个数值是否相等。如果 number1=number2,则返回 1,否则返回 0。可用此函数筛选一组数据,例如,通过对几个 DELTA 函数求和,可以计算相等数据对的数目。该函数也称为 Kronecker Delta 函数。
语法形式为DELTA(number1,number2) 其中Number1为第一个参数,Number2为第二个参数。如果省略,假设 Number2 值为零。如果number1或者number2为非数值型,则函数 DELTA 返回错误值 #VALUE!
2)可筛选数据的函数GESTEP
使用GESTEP函数可筛选数据。如果 Number 大于等于 step,返回 1,否则返回 0。例如,通过计算多个函数 GESTEP 的返回值,可以检测出数据集中超过某个临界值的数据个数。
语法形式为:GESTEP(number,step) 其中Number为待测试的数值。Step称阀值。如果省略 step,则函数 GESTEP 假设其为零。需注意的是,如果任一参数非数值,则函数 GESTEP 返回错误值 #VALUE!
3)以考试成绩统计为例说明函数的用法
例:某院校举行数学模拟考试,正在进行成绩排定。提出的评定方案为求出成绩超过90分的考生人数有哪些人。
在这里我们采用GEStep函数来完成统计,首先会为每位考生的成绩做标记。超过90分的标记为1,否则为0,然后对所有考生的标记进行汇总,即可求出有多少人超过90分。

 
3

1Annie的成绩为例,成绩为98分,超90分。具体公式为:
=GESTEP(C4,90)

4
、度量衡转换函数CONVERT
CONVERT
函数可以将数字从一个度量系统转换到另一个度量系统中。
语法形式为CONVERT(number,from_unit,to_unit) 其中Number为以 from_units 为单位的需要进行转换的数值。From_unit为数值 number 的单位。To_unit为结果的单位。
函数 CONVERT from_unit to_unit的参数接受的附表的文本值。

重量和质量

From_unit to_unit

能量

From_unit to_unit

"g"

焦耳

"J"

斯勒格

"sg"

尔格

"e"

磅(常衡制)

"lbm"

热力学卡

"c"

U(原子质量单位)

"u"

IT

"cal"

盎司(常衡制)

"ozm"

电子伏

"eV"

距离

From_unit to_unit

马力-小时

"HPh"

"m"

瓦特-小时

"Wh"

法定哩

"mi"

英尺磅

"flb"

海里

"Nmi"

BTU

"BTU"

英寸

"in"

功率

From_unit to_unit

英尺

"ft"

马力

"HP"

`

瓦特

"W"

"ang"

From_unit to_unit

皮卡(1/72 英寸)

"Pica"

特斯拉

"T"

时间

From_unit to_unit

高斯

"ga"

"yr"

温度

From_unit to_unit

"day"

摄氏度

"C"

小时

"hr"

华氏度

"F"

分钟

"mn"

开尔文度

"K"

"sec"

液体度量

From_unit to_unit

压强

From_unit to_unit

茶匙

"tsp"

帕斯卡

"Pa"

汤匙

"tbs"

大气压

"atm"

液量盎司

"oz"

毫米汞柱

"mmHg"

"cup"

From_unit to_unit

U.S. 品脱

"pt"

牛顿

"N"

U.K. 品脱

"uk_pt"

达因

"dyn"

夸脱

"qt"

磅力

"lbf"

加仑

"gal"

 

 

"l"


5
、与积分运算有关的函数ERFERFC
ERF
为返回误差函数在上下限之间的积分。
其语法形式为:ERF(lower_limit,upper_limit) 其中,Lower_limitERF函数的积分下限。Upper_limitERF函数的积分上限。如果省略,默认为零。
ERFC
为返回从 x (无穷)积分的 ERF 函数的余误差函数。其语法形式为:
ERFC(x)
其中XERF函数积分的下限。
6
、与复数运算有关的函数
还记得中学时代学过的复数吗?是不是还记得当时求复数的模等计算的繁复?Excel的工程函数中提供的多种与复数运算有关的函数,你可以用它来验证自己的运算结果的正确性啊。关于有哪些函数与复数运算有关,可以察看所附的表格。这里将以简单的事例说明函数的使用方法。注意到在工程函数中有一些前缀为im的函数了吗?这些就是与复数运算有关的函数。
举例,已知复数5+12i,请用函数求解该复数的共轭复数、实系数、虚系数、模等。

 
4

附表:Excel的工程函数

函数名

函数说明

语法形式

BESSELI

返回修正 Bessel 函数值,它与用纯虚数参数运算时的 Bessel 函数值相等。

BESSELI(x,n)

BESSELJ

返回 Bessel 函数值。

BESSELJ(x,n)

BESSELK

返回修正 Bessel 函数值,它与用纯虚数参数运算时的 Bessel 函数值相等。

BESSELK(x,n)

BESSELY

返回 Bessel 函数值,也称为 Weber 函数或 Neumann 函数。

BESSELY(x,n)

BIN2DEC

将二进制数转换为十进制数。

BIN2DEC(number)

BIN2HEX

将二进制数转换为十六进制数。

BIN2HEX(number,places)

BIN2OCT

将二进制数转换为八进制数。

BIN2OCT(number,places)

COMPLEX

将实系数及虚系数转换为 x+yi x+yj 形式的复数。

COMPLEX(real_num,i_num,suffix)

CONVERT

将数字从一个度量系统转换到另一个度量系统中。

CONVERT(number,from_unit,to_unit)

DEC2BIN

将十进制数转换为二进制数。

DEC2BIN(number,places)

DEC2HEX

将十进制数转换为十六进制数。

DEC2HEX(number,places)

DEC2OCT

将十进制数转换为八进制数。

DEC2OCT(number,places)

DELTA

测试两个数值是否相等。如果 number1=number2,则返回 1,否则返回 0

DELTA(number1,number2)

ERF

返回误差函数在上下限之间的积分。

ERF(lower_limit,upper_limit)

ERFC

返回从 x (无穷)积分的 ERF 函数的余误差函数

ERFC(x)

GESTEP

如果 Number 大于等于 step,返回 1,否则返回 0。使用该函数可筛选数据。

GESTEP(number,step)

HEX2BIN

将十六进制数转换为二进制数。

HEX2BIN(number,places)

HEX2DEC

将十六进制数转换为十进制数。

HEX2DEC(number)

HEX2OCT

将十六进制数转换为八进制数。

HEX2OCT(number,places)

IMABS

返回以 x+yi x+yj 文本格式表示的复数的绝对值(模)。

IMABS(inumber)

IMAGINARY

返回以 x+yi x+yj 文本格式表示的复数的虚系数。

IMAGINARY(inumber)

IMARGUMENT

返回以弧度表示的角

IMARGUMENT(inumber)

IMCONJUGATE

返回以 x+yi x+yj 文本格式表示的复数的共轭复数。

IMCONJUGATE(inumber)

IMCOS

返回以 x+yi x+yj 文本格式表示的复数的余弦。

IMCOS(inumber)

IMDIV

返回以 x+yi x+yj 文本格式表示的两个复数的商。

IMDIV(inumber1,inumber2)

IMEXP

返回以 x+yi x+yj 文本格式表示的复数的指数。

IMEXP(inumber)

IMLN

返回以 x+yi x+yj 文本格式表示的复数的自然对数。

IMLN(inumber)

IMLOG10

返回以 x+yi x+yj 文本格式表示的复数的常用对数(以 10 为底数)。

IMLOG10(inumber)

IMLOG2

返回以 x+yi x+yj 文本格式表示的复数的以 2 为底数的对数。

IMLOG2(inumber)

IMPOWER

返回以 x+yi x+yj 文本格式表示的复数的 n 次幂。

IMPOWER(inumber,number)

IMPRODUCT

返回以 x+yi x+yj 文本格式表示的 2 29 个复数的乘积。

IMPRODUCT(inumber1,inumber2,...)

IMREAL

返回以 x+yi x+yj 文本格式表示的复数的实系数。

IMREAL(inumber)

IMSIN

返回以 x+yi x+yj 文本格式表示的复数的正弦值。

IMSIN(inumber)

IMSQRT

返回以 x+yi x+yj 文本格式表示的复数的平方根。

IMSQRT(inumber)

IMSUB

返回以 x+yi x+yj 文本格式表示的两个复数的差。

IMSUB(inumber1,inumber2)

IMSUM

返回以 x+yi x+yj 文本格式表示的两个或多个复数的和。

IMSUM(inumber1,inumber2,...)

OCT2BIN

将八进制数转换为二进制数。

OCT2BIN(number,places)

OCT2DEC

将八进制数转换为十进制数。

OCT2DEC(number)

OCT2HEX

将八进制数转换为十六进制数。

OCT2HEX(number,places)

 

 

像统计函数、工程函数一样,在Excel中还提供了许多财务函数。财务函数可以进行一般的财务计算,如确定贷款的支付额、投资的未来值或净现值,以及债券或息票的价值。这些财务函数大体上可分为四类:投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。它们为财务分析提供了极大的便利。使用这些函数不必理解高级财务知识,只要填写变量值就可以了。在下文中,凡是投资的金额都以负数形式表示,收益以正数形式表示。

在介绍具体的财务函数之前,我们首先来了解一下财务函数中常见的参数:

未来值 (fv)--在所有付款发生后的投资或贷款的价值。

期间数 (nper)--为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。

付款 (pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常 pmt 包括本金和利息,但不包括其他费用及税款。

现值 (pv)--在投资期初的投资或贷款的价值。例如,贷款的现值为所借入的本金数额。

利率 (rate)--投资或贷款的利率或贴现率。

类型 (type)--付款期间内进行支付的间隔,如在月初或月末,用01表示。

日计数基准类型(basis--为日计数基准类型。Basis0 或省略代表US (NASD) 30/360 ,为1代表实际天数/实际天数,为2代表实际天数/360 ,为3代表实际天数/365 ,为4代表欧洲30/360

接下来,我们将分别举例说明各种不同的财务函数的应用。在本文中主要介绍各类型的典型财务函数,更多的财务函数请参看附表及相关书籍。如果下文中所介绍的函数不可用,返回错误值 #NAME?,请安装并加载"分析工具库"加载宏。操作方法为:

1
、在"工具"菜单上,单击"加载宏"

2
、在"可用加载宏"列表中,选中"分析工具库"框,再单击"确定"

一、投资计算函数

投资计算函数可分为与未来值fv有关,与付款pmt有关,与现值pv有关,与复利计算有关及与期间数有关几类函数。

1
、与未来值fv有关的函数--FVFVSCHEDULE

2
、与付款pmt有关的函数--IPMTISPMTPMTPPMT

3
、与现值pv有关的函数--NPVPVXNPV

4
、与复利计算有关的函数--EFFECTNOMINAL

5
、与期间数有关的函数--NPER

在投资计算函数中,笔者将重点介绍FVNPVPMTPV函数。

(一) 求某项投资的未来值FV

在日常工作与生活中,我们经常会遇到要计算某项投资的未来值的情况,此时利用Excel函数FV进行计算后,可以帮助我们进行一些有计划、有目的、有效益的投资。FV函数基于固定利率及等额分期付款方式,返回某项投资的未来值。

语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率,是一固定值,nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为各期所应付给(或得到)的金额,其数值在整个年金期间(或投资期内)保持不变,通常Pv包括本金和利息,但不包括其它费用及税款,pv为现值,或一系列未来付款当前值的累积和,也称为本金,如果省略pv,则假设其值为零,type为数字01,用以指定各期的付款时间是在期初还是期末,如果省略t,则假设其值为零。

例如:假如某人两年后需要一笔比较大的学习费用支出,计划从现在起每月初存入2000元,如果按年利2.25%,按月计息(月利为2.25%/12),那么两年以后该账户的存款额会是多少呢?

公式写为:FV(2.25%/12, 24,-2000,0,1)

 
1

(二) 求投资的净现值NPV

NPV
函数基于一系列现金流和固定的各期贴现率,返回一项投资的净现值。投资的净现值是指未来各期支出(负值)和收入(正值)的当前值的总和。

语法形式为:NPV(rate,value1,value2, ...) 其中,rate为各期贴现率,是一固定值;value1,value2,...代表129笔支出及收入的参数值,value1,value2,...所属各期间的长度必须相等,而且支付及收入的时间都发生在期末。需要注意的是:NPV按次序使用value1,value2,来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式,则都会计算在内;如果参数是错误值或不能转化为数值的文字,则被忽略,如果参数是一个数组或引用,只有其中的数值部分计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。

例如,假设开一家电器经销店。初期投资¥200,000,而希望未来五年中各年的收入分别为¥20,000、¥40,000、¥50,000、¥80,000和¥120,000。假定每年的贴现率是8%(相当于通贷膨胀率或竞争投资的利率),则投资的净现值的公式是:

=NPV(A2, A4:A8)+A3

在该例中,一开始投资的¥200,000并不包含在v参数中,因为此项付款发生在第一期的期初。假设该电器店的营业到第六年时,要重新装修门面,估计要付出¥40,000,则六年后书店投资的净现值为:

=NPV(A2, A4:A8, A9)+A3

如果期初投资的付款发生在期末,则 投资的净现值的公式是:

=NPV(A2, A3:A8)

 
2

(三) 求贷款分期偿还额PMT

PMT
函数基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款,要求在一定周期内支付完时,每次需要支付的偿还额,也就是我们平时所说的"分期付款"。比如借购房贷款或其它贷款时,可以计算每期的偿还额。

其语法形式为:PMT(rate,nper,pv,fv,type) 其中,rate为各期利率,是一固定值,nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为现值,或一系列未来付款当前值的累积和,也称为本金,fv为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv,则假设其值为零(例如,一笔贷款的未来值即为零),type01,用以指定各期的付款时间是在期初还是期末。如果省略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)。因此,这不是一项合算的投资。

 
3

二、 折旧计算函数

折旧计算函数主要包括AMORDEGRCAMORLINCDBDDBSLNSYDVDB。这些函数都是用来计算资产折旧的,只是采用了不同的计算方法。这里,对于具体的计算公式不再赘述,具体选用哪种折旧方法,则须视各单位情况而定。

三、偿还率计算函数

偿还率计算函数主要用以计算内部收益率,包括IRRMIRRRATEXIRR几个函数。

(一) 返回内部收益率的函数--IRR

IRR
函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的,但作为年金,它们必须按固定的间隔发生,如按月或按年。内部收益率为投资的回收利率,其中包含定期支付(负值)和收入(正值)。

其语法形式为IRR(values,guess) 其中values为数组或单元格的引用,包含用来计算内部收益率的数字,values必须包含至少一个正值和一个负值,以计算内部收益率,函数IRR根据数值的顺序来解释现金流的顺序,故应确定按需要的顺序输入了支付和收入的数值,如果数组或引用包含文本、逻辑值或空白单元格,这些数值将被忽略;guess为对函数IRR计算结果的估计值,excel使用迭代法计算函数IRRguess开始,函数IRR不断修正收益率,直至结果的精度达到0.00001%,如果函数IRR经过20次迭代,仍未找到结果,则返回错误值#NUM!,在大多数情况下,并不需要为函数IRR的计算提供guess值,如果省略guess,假设它为0.110%)。如果函数IRR返回错误值#NUM!,或结果没有靠近期望值,可以给guess换一个值再试一下。

例如,如果要开办一家服装商店,预计投资为¥110,000,并预期为今后五年的净收益为:¥15,000、¥21,000、¥28,000、¥36,000和¥45,000。分别求出投资两年、四年以及五年后的内部收益率。

 
4

在工作表的B1B6输入数据"函数.xls"所示,计算此项投资四年后的内部收益率IRRB1B5)为-3.27%;计算此项投资五年后的内部收益率IRRB1B6)为8.35%;计算两年后的内部收益率时必须在函数中包含guess,即IRRB1B3-10%)为-48.96%

(二) 用RATE函数计算某项投资的实际赢利

在经济生活中,经常要评估当前某项投资的运作情况,或某个新企业的现状。例如某承包人建议你贷给他30000元,用作公共工程建设资金,并同意每年付给你9000元,共付五年,以此作为这笔贷款的最低回报。那么你如何去决策这笔投资?如何知道这项投资的回报率呢?对于这种周期性偿付或是一次偿付完的投资,用RATE函数可以很快地计算出实际的赢利。其语法形式为RATE(nper,pmt,pv,fv,type,guess)

具体操作步骤如下:

1
、选取存放数据的单元格,并按上述相似的方法把此单元格指定为"百分数"的格式。

2
、插入函数RATE,打开"粘贴函数"对话框。

3
、在"粘贴函数"对话框中,在"Nper"中输入偿还周期5(),"Pmt"中输入7000(每年的回报额),在"Pv"中输入-30000(投资金额)。即公式为=RATE(5,9000,-30000)

4
、确定后计算结果为15.24%。这就是本项投资的每年实际赢利,你可以根据这个值判断这个赢利是否满意,或是决定投资其它项目,或是重新谈判每年的回报。


  评论这张
 
阅读(5988)| 评论(1)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017