Excel函数在财务工作中的应用

【摘 要】Excel作为一款试算表软件，是办公套装软件的重要组成部分，其具有诸多的优点，在管理、统计财经和金融等众多领域有所应用。本文结合实际案例，分析了Excel软件的具体优势，重点探讨了Excel函数在财务工作的应用，旨在提高工作的质量及效率，以供参考。

【关键词】Excel函数；数据统计；投资决策；财务工作

随着社会经济建设的快速发展，各种类型的企业数量日益增加。财务工作作为企业发展的重要内容，关系着企业整体的经济效益及未来的发展前景。目前，财务工作除了一些日常的记账和编制报表外，最主要的是对计算出来的经济数据进行分析，以实现高效的财务决策。Excel是一种以表格形式进行数据综合管理与分析的电子表格软件，具有数据处理、图形处理和数据分析等优点，可以帮助用户建立、编辑和管理各种类型的电子表格，并且它强大的函数功能和简单的操作方法为会计领域的财务工作带来了许多的便利。但由于许多会计人员对Excel函数知识的认识不足，在操作过程中并不是十分灵活，影响到财务工作质量。因此，本文通过探讨了Excel函数在财务工作中的应用，以提高企业的财务决策质量。

1.根据身份证号码提取出生年月、性别、年龄

首先我们来分析一下身份证号码的组成：身份证号码一般由18位数字组成，前6位是地址码，第7至14位为出生日期码，第15至17位为顺序码，第18位为校验码。其中第17位代表性别，如果是单数为男性，双数为女性。

我们用mid函数来解决出生年月的问题，mid函数是从文本字符串中的指定位置起返回指定长度的字符。

格式：=MID（text，start_num，num_chars）

text为准备从中提取字符串的字符串；

atart_num为准备提取的第一个字符的位置，text中第一个字符为1；

num_chars为指定所要提取的字符串长度。

先取一下出生年月，第一个参数选择身份证号码，第二个参数为7，第三个参数为要取的长度，出生年月共8位，所以为8，公式为：

=MID（“身份证号码”，7，8）

下面我们一起来取一下性别，性别在身份证号码的第17位，判断单数或双数我们用除以2取余数的方法，余数为1是单数，余数为0是双数，取余数的函数是mod，然后再嵌套IF函数就能实现。我们一起写一下这个公式：

=IF（MOD（MID（“身份证号码”，17，1），2）

=1，“男”，“女”）

取年龄就要用到日期函数TODAY、DATE和DATEDIF。TODAY函数是返回日期格式的当前日期。DATE函数是返回代表特定日期的序列号。如果在入函数前，单元格的格式为“常规”，则结果将设为日期格式。

格式：DATE（year，month，day）

DATEDIF函数是计算两个日期之间的天数、月数或年数。

格式：DATEDIF（start_date，end_date，unit）

Start_date代表时间段内的起始日期。

End_date代表时间段内的结束日期。

Unit为所需信息的返回类型，“Y”时间段中的整年数，“M”时间段中的整月数，“D”时间段中的天数，“YM”两个日期中月数的差，“YD”两个日期中天数的差，本题用“Y”。

有了上述这些函数，我们就可以从身份证号码中取出年龄了，公式为：

=DATEDIF（DATE（MID（“身份证号码”，7，4），

MID（“身份证号码”，11，2），MID（“身份证号码”，13，2）），TODAY（），“y”）

2.根据出库单流水数据统计相应类别的出库金额

大家都知道求和函数SUM，如果要根据指定条件求和用SUM函数就比校麻烦，还需要和IF函数嵌套，我们可以用SUMIF、SUMPRODU函数来解决这个问题。

例如：已知1月的出库单流水文件，物资编码的1-2位表示物资大类，3-4位表示大类下的二级子类如下表；

（1）求02大类的出库金额，这是单条件求和，用SUMIF函数：

格式：=SUMIF（range，criteria，sum_range）

Range用于条件判断的单元区域；

Criteria确定哪些单元格将被相加求和的条件；

sum-range需要求和的实际单元格。

02大类是物资编码的前两位，可以用LEFT函数取出前两位，做个辅助列，然后用SUMIF函数。更简便的方法是使用通配符，公式为=SUMIF（“选择物资编码列”，02&”*”，“选择金额列”），计算出来02大类的出库金额为28538.29元。

（2）求采一队领用02大类的出库金额，这是两个条件的求和，用SUMIF函数就不行了，要用多条件求和函数SUMPRODUCT。

SUMPRODUCT函数最初的涵义是在给定的几组数组中，将数组间对应的元素相乘并返回乘积之和。

格式：=SUMPRODUCT（array1，array2，array3，…）

注意：数组必须具有相同的维数。

用于多条件统计是SUMPRODUCT的特殊用法，条件之间用“*”，一方面表示“且”的关系，另一方面也起一个乘的作用，即将逻辑值运算成数值；逻辑值在数值运算中FLASE相当于0，TRUE相当于1，FLASE*TRUE=0，FLASE*FLASE=0，TRUE*TRUE=1。

先做了辅助列，取物资编码的前两位，用LEFT函数，假定在第I列做。

物资编码，I2：I18=“02”，用数组公式，返回一组逻辑值

（B2：B18=“02”）*（C2：C18=“采一队”），

数组公式，返回一组0、1表示的值；

SUMPRODUCT（（B2：B18=“02”）*（C2：C18

=“采一队”））

=SUMPRODUCT（{0，0，0，0，1，1，1，0，0，0，

0，0，0，1，1，1，0}）

然后一组0、1的值与出库金额相乘，就求出我们要的结果了。

公式：SUMPRODUCT（（B2：B18=“02”）

*（C2：C18=“采一队”）*（H2：H18）），计算出来采一队领用02大类的出库金额为7515.66元。

本题是两个条件的求和，如果是多条件的，可以继续加，只要把所有的条件都括起来相乘就可以了。

SUMIF、SUMPRODUCT函数是最常用的条件求和函数，会计人员要掌握好这两个函数并灵活运用，会使日常工作更加方便快捷。

3.利用财务函数作投资决策

EXCEL中的财务函数，主要有PV现值函数、FV终值函数、基于固定利率及等额分期付款方式的一组函数：PMT还款额、PPMT本金部分IPMT利息部分，NPV净现值函数、IRR内含报酬率函数等。

（1）在连续5年中，每年年初存入银行1000元，存款利率为8%，计算5年末年金终值。要求终值就用到FV函数，

格式：=FV（rate，nper，pmt，pv，type）

Rate各期利率；

Nper总投资期；

Pmt年金，计算复利终值时可忽略；

Pv现值，计算年金终值时可忽略；

Type0代表期末支付；1代表期初支付。

注意：pmt或pv在该函数中应用负数表示。

在本题中，利率是8%，总投资期是5年，每年年初存入1000元，说明是年金形式的，pmt为1000，现值没有可忽略，年初存入说明type是1，所以公式为：

=FV（0.08，5，-1000，1）

计算出来的结果是5年后可得到6335.93元。

（2）某公司每年年末偿还借款12000元，借款期为10年，银行存款利率为10%，则该公司目前银行存款至少为多少元。求现在时点的金额，用现值函数PV，

=PV（rate，nper，pmt，fv，type）

格式参数与FV基本相同，利率是10%，总投资期10年，年金12000元，终值没有可忽略，期末付款type是0，当为0时也可忽略不写。公式为：

=PV（0.1，10，-12000）

计算出来该公司目前的银行存款至少要有73734.81元，才能满足还款条件。

（3）某企业租用一固定资产，租金共计36000元，分五年等额支付，年利率为8%，每年年末支付，计算各期支付本金及利息。这道题用PMT、PPMT、IPMT这一组等额函数。

PMT函数是基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。格式：

=PMT（rate，nper，pv，fv，type）PPMT函数是基于固定利率及等额分期付款方式，返回投资在某一给定期次内的本金。

格式：=PPMT（rate，per，nper，pv，fv，type）

其中：per：计算本金数（下转第131页）（上接第129页）额的期次

IPMT函数是基于固定利率及等额分期付款方式，返回投资在某一给定期次内的利息。

格式：=IPMT（rate，per，nper，pv，fv，type）

我们可以利用Excel表格建一个模型，

每期还款额的公式为：

=PMT（0.08，5，-36000）

第一年偿还本金的公式为：

=PPMT（0.08，1，5，-36000）

第一年偿还利息的公式为：

=IPMT（0.08，1，5，-36000）

在做模型时，参数不要用数据表示，要用选择单元格的方式，上面表中的数据：租金总额、付款期、年利率都是不变的，用绝对引用单元格。引用年份时用相对引用单元格。

这样，当租金总额、付款期、年利率有一个或几个数据变化时，只需在上面的表格中更改，不用动下面的公式，便可轻松得到想要的结果。

4.结语

综上所述，通过上述列举的一些常用Excel函数可知，Excel具有灵活简单、功能强大的数据处理分析功能，并且Excel的数据库也比较容易维护和更新。为提高财务工作的决策质量，这就需要提高会计人员对于Excel函数的认识，并从各个维度、范围对财务数据进行深入的分析，以提高财务分析的效率，使Excel软件更好地应用于财务工作中。

参考文献：

[1]李星月.Excel电子表格在财务管理中的应用[J].金融经济，2013，14.

[2]叶青梅.EXCEL在财务工作中的运用[J].经济师，2012，06.