让Excel函数成为财务工作的好助手

开篇：润墨网以专业的文秘视角，为您筛选了一篇让Excel函数成为财务工作的好助手范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！

摘 要：excel有其独特的优势，但由于会计人员对Excel函数知识的掌握程度不同，有很多人运用得并不是十分灵活，文章通过具体案例分析，探讨如何发挥Excel的优势，提高工作质量和工作效率。

关键词：Excel 财务工作 优势

中图分类号：F275 文献标识码：A

文章编号：1004-4914（2013）07-103-02

财务工作除了日常的记账，编制报表外，更重要的是根据计算出来的数据进行分类汇总整理，对数据进行分析，以实现财务决策。

现有的财务软件只能解决基础数据的编制及简单的分析，要进行数据的进一步加工处理，跨年的数据整理与分析汇总，就有点力不从心了，Excel正好能解决这个难题。

Excel是一种功能强大、使用方便，并且以“表格”形式进行数据综合管理与分析的电子表格软件，具有电子表格处理、图形处理和数据库管理三大功能。能较好地帮助用户编辑和管理各种类型的电子表格以及自动处理，其快捷的制表功能、强大的函数运算功能和简便的操作方法是会计日常工作的好帮手，操作者无需高深的电脑专业知识，只要熟悉本专业知识，通过灵活运用，就可以解决很多工作难题，提高工作质量和工作效率。

Excel有其独特的优势，但由于会计人员对Excel函数知识的掌握程度不同，有很多人运用得并不是十分灵活，下面笔者就举几个具体例子，浅谈一下自己肤浅的心得，以此抛砖引玉，和同仁们共同探索共同学习。

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

首先我们来分析一下身份证号码的组成：身份证号码一般由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”）

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

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

例如：已知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函数是最常用的条件求和函数，会计人员要掌握好这两个函数并灵活运用，会使日常工作更加方便快捷。

三、利用财务函数作投资决策

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

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

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

Rate各期利率；

Nper总投资期；

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

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

Type 0代表期末支付；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：计算本金数额的期次

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）

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

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

Excel的函数有几百个，上述只举了一些常用的简单的函数，我们可以看出Excel具有灵活、简便而功能强大的数据分析功能，而且Excel数据库便于维护和更新，财务人员通过简单的操作，就可以从各个维度、范围对财务数据进行深入的分析。当然，Excel还具备更多的数据分析潜能，需要财务人员在工作中发挥主动性和创造力，从而提高财务分析的效率和作用，让Excel函数真正成为我们财务工作的好助手。

参考文献：

1.赵琳.浅谈用EXCEL做财务报表分析.山东英才学院学报，2012（6）

2.陈伟.EXCEL构建财务分析数据库.会计之友（下旬刊），2009（7）

3.刘义福.EXCEL2000在会计工作中的运用.金融会计，2000（9）

（作者单位：西山煤电（集团）有限责任公司财务处 山西太原 030053）

（责编：贾伟）