讓Excel函數(shù)成為財務(wù)工作的好助手

摘 要：Excel有其獨特的優(yōu)勢，但由于會計人員對Excel函數(shù)知識的掌握程度不同，有很多人運用得并不是十分靈活，文章通過具體案例分析，探討如何發(fā)揮Excel的優(yōu)勢，提高工作質(zhì)量和工作效率。

關(guān)鍵詞：Excel 財務(wù)工作 優(yōu)勢

中圖分類號：F275 文獻標(biāo)識碼：A

文章編號：1004-4914（2013）07-103-02

財務(wù)工作除了日常的記賬，編制報表外，更重要的是根據(jù)計算出來的數(shù)據(jù)進行分類匯總整理，對數(shù)據(jù)進行分析，以實現(xiàn)財務(wù)決策。

現(xiàn)有的財務(wù)軟件只能解決基礎(chǔ)數(shù)據(jù)的編制及簡單的分析，要進行數(shù)據(jù)的進一步加工處理，跨年的數(shù)據(jù)整理與分析匯總，就有點力不從心了，Excel正好能解決這個難題。

Excel是一種功能強大、使用方便，并且以“表格”形式進行數(shù)據(jù)綜合管理與分析的電子表格軟件，具有電子表格處理、圖形處理和數(shù)據(jù)庫管理三大功能。能較好地幫助用戶編輯和管理各種類型的電子表格以及自動處理，其快捷的制表功能、強大的函數(shù)運算功能和簡便的操作方法是會計日常工作的好幫手，操作者無需高深的電腦專業(yè)知識，只要熟悉本專業(yè)知識，通過靈活運用，就可以解決很多工作難題，提高工作質(zhì)量和工作效率。

Excel有其獨特的優(yōu)勢，但由于會計人員對Excel函數(shù)知識的掌握程度不同，有很多人運用得并不是十分靈活，下面筆者就舉幾個具體例子，淺談一下自己膚淺的心得，以此拋磚引玉，和同仁們共同探索共同學(xué)習(xí)。

一、根據(jù)身份證號碼提取出生年月、性別、年齡

首先我們來分析一下身份證號碼的組成：身份證號碼一般由18位數(shù)字組成，前6位是地址碼，第7至14位為出生日期碼，第15至17位為順序碼，第18位為校驗碼。其中第17位代表性別，如果是單數(shù)為男性，雙數(shù)為女性。

我們用mid函數(shù)來解決出生年月的問題，mid函數(shù)是從文本字符串中的指定位置起返回指定長度的字符。

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

text為準(zhǔn)備從中提取字符串的字符串；

atart_num為準(zhǔn)備提取的第一個字符的位置，text中第一個字符為1；

num_chars為指定所要提取的字符串長度。

先取一下出生年月，第一個參數(shù)選擇身份證號碼，第二個參數(shù)為7，第三個參數(shù)為要取的長度，出生年月共8位，所以為8，公式為：

=MID（“身份證號碼”，7，8）

下面我們一起來取一下性別，性別在身份證號碼的第17位，判斷單數(shù)或雙數(shù)我們用除以2取余數(shù)的方法，余數(shù)為1是單數(shù)，余數(shù)為0是雙數(shù)，取余數(shù)的函數(shù)是mod，然后再嵌套IF函數(shù)就能實現(xiàn)。我們一起寫一下這個公式：

=IF（MOD（MID（“身份證號碼”，17，1），2）=1，“男”，“女”）

取年齡就要用到日期函數(shù)TODAY、DATE和DATEDIF。TODAY函數(shù)是返回日期格式的當(dāng)前日期。DATE函數(shù)是返回代表特定日期的序列號。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。

格式：DATE（year，month，day）

DATEDIF函數(shù)是計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。

格式：DATEDIF（start_date，end_date，unit）

Start_date代表時間段內(nèi)的起始日期。

End_date代表時間段內(nèi)的結(jié)束日期。

Unit為所需信息的返回類型，“Y”時間段中的整年數(shù)，“M”時間段中的整月數(shù)，“D”時間段中的天數(shù)，“YM”兩個日期中月數(shù)的差，“YD”兩個日期中天數(shù)的差，本題用“Y”。

有了上述這些函數(shù)，我們就可以從身份證號碼中取出年齡了，公式為：

=DATEDIF（DATE（MID（“身份證號碼”，7，4），MID（“身份證號碼”，11，2），MID（“身份證號碼”，13，2）），TODAY（），“y”）

二、根據(jù)出庫單流水?dāng)?shù)據(jù)統(tǒng)計相應(yīng)類別的出庫金額

大家都知道求和函數(shù)SUM，如果要根據(jù)指定條件求和用SUM函數(shù)就比校麻煩，還需要和IF函數(shù)嵌套，我們可以用SUMIF、SUMPRODUCT函數(shù)來解決這個問題。

例如：已知1月的出庫單流水文件，物資編碼的1-2位表示物資大類，3-4位表示大類下的二級子類如下表：

1.求02大類的出庫金額，這是單條件求和，用SUMIF函數(shù)：

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

Range用于條件判斷的單元區(qū)域；

Criteria確定哪些單元格將被相加求和的條件；

sum-range 需要求和的實際單元格。

02大類是物資編碼的前兩位，可以用LEFT函數(shù)取出前兩位，做個輔助列，然后用SUMIF函數(shù)。更簡便的方法是使用通配符，公式為=SUMIF（“選擇物資編碼列”，02”*”，“選擇金額列”），計算出來02大類的出庫金額為28538.29元。

2.求采一隊領(lǐng)用02大類的出庫金額，這是兩個條件的求和，用SUMIF函數(shù)就不行了，要用多條件求和函數(shù)SUMPRODUCT。

SUMPRODUCT函數(shù)最初的涵義是在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘并返回乘積之和。

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

注意：數(shù)組必須具有相同的維數(shù)。

用于多條件統(tǒng)計是SUMPRODUCT的特殊用法，條件之間用“*”，一方面表示“且”的關(guān)系，另一方面也起一個乘的作用，即將邏輯值運算成數(shù)值；邏輯值在數(shù)值運算中FLASE相當(dāng)于0，TRUE相當(dāng)于1，F(xiàn)LASE*TRUE=0，F(xiàn)LASE*FLASE=0，TRUE* TRUE=1。

先做了輔助列，取物資編碼的前兩位，用LEFT函數(shù)，假定在第I列做。

物資編碼，I2：I18=“02”，用數(shù)組公式，返回一組邏輯值

（B2：B18=“02”）*（C2：C18=“采一隊”），數(shù)組公式，返回一組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的值與出庫金額相乘，就求出我們要的結(jié)果了。

公式：SUMPRODUCT（（B2：B18=“02”）*（C2：C18=“采一隊”）*（H2：H18）），計算出來采一隊領(lǐng)用02大類的出庫金額為7515.66元。

本題是兩個條件的求和，如果是多條件的，可以繼續(xù)加，只要把所有的條件都括起來相乘就可以了。

SUMIF、SUMPRODUCT函數(shù)是最常用的條件求和函數(shù)，會計人員要掌握好這兩個函數(shù)并靈活運用，會使日常工作更加方便快捷。

三、利用財務(wù)函數(shù)作投資決策

EXCEL中的財務(wù)函數(shù)，主要有PV現(xiàn)值函數(shù)、FV終值函數(shù)、基于固定利率及等額分期付款方式的一組函數(shù)：PMT還款額、 PPMT本金部分IPMT利息部分，NPV凈現(xiàn)值函數(shù)、IRR內(nèi)含報酬率函數(shù)等。

1.在連續(xù)5年中，每年年初存入銀行1000元，存款利率為8%，計算5年末年金終值。要求終值就用到FV函數(shù)，

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

Rate各期利率；

Nper總投資期；

Pmt年金，計算復(fù)利終值時可忽略；

Pv現(xiàn)值，計算年金終值時可忽略；

Type 0代表期末支付；1代表期初支付。

注意：pmt或pv在該函數(shù)中應(yīng)用負(fù)數(shù)表示。

在本題中，利率是8%，總投資期是5年，每年年初存入1000元，說明是年金形式的，pmt為1000，現(xiàn)值沒有可忽略，年初存入說明type是1，所以公式為：

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

計算出來的結(jié)果是5年后可得到6335.93元。

2.某公司每年年末償還借款12000元，借款期為10年，銀行存款利率為10%，則該公司目前銀行存款至少為多少元。求現(xiàn)在時點的金額，用現(xiàn)值函數(shù)PV，

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

格式參數(shù)與FV基本相同，利率是10%，總投資期10年，年金12000元，終值沒有可忽略，期末付款type是0，當(dāng)為0時也可忽略不寫。公式為：

=PV（0.1，10，-12000）

計算出來該公司目前的銀行存款至少要有73734.81元，才能滿足還款條件。

3.某企業(yè)租用一固定資產(chǎn)，租金共計36000元，分五年等額支付，年利率為8%，每年年末支付，計算各期支付本金及利息。這道題用PMT、PPMT、IPMT這一組等額函數(shù)。

PMT函數(shù)是基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。

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

PPMT函數(shù)是基于固定利率及等額分期付款方式，返回投資在某一給定期次內(nèi)的本金。

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

其中：per：計算本金數(shù)額的期次

IPMT函數(shù)是基于固定利率及等額分期付款方式，返回投資在某一給定期次內(nèi)的利息。

格式：=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）

在做模型時，參數(shù)不要用數(shù)據(jù)表示，要用選擇單元格的方式，上面表中的數(shù)據(jù)：租金總額、付款期、年利率都是不變的，用絕對引用單元格。引用年份時用相對引用單元格。

這樣，當(dāng)租金總額、付款期、年利率有一個或幾個數(shù)據(jù)變化時，只需在上面的表格中更改，不用動下面的公式，便可輕松得到想要的結(jié)果。

Excel的函數(shù)有幾百個，上述只舉了一些常用的簡單的函數(shù)，我們可以看出Excel具有靈活、簡便而功能強大的數(shù)據(jù)分析功能，而且Excel數(shù)據(jù)庫便于維護和更新，財務(wù)人員通過簡單的操作，就可以從各個維度、范圍對財務(wù)數(shù)據(jù)進行深入的分析。當(dāng)然，Excel還具備更多的數(shù)據(jù)分析潛能，需要財務(wù)人員在工作中發(fā)揮主動性和創(chuàng)造力，從而提高財務(wù)分析的效率和作用，讓Excel函數(shù)真正成為我們財務(wù)工作的好助手。

參考文獻：

1.趙琳.淺談用EXCEL做財務(wù)報表分析.山東英才學(xué)院學(xué)報，2012（6）

2.陳偉.EXCEL構(gòu)建財務(wù)分析數(shù)據(jù)庫.會計之友（下旬刊），2009（7）

3.劉義福.EXCEL2000在會計工作中的運用.金融會計，2000（9）

（作者單位：西山煤電（集團）有限責(zé)任公司財務(wù)處 山西太原 030053）

（責(zé)編：賈偉）