Excel函數(shù)在財務(wù)工作中的應(yīng)用

袁俊毅

【摘 要】Excel作為一款試算表軟件，是辦公套裝軟件的重要組成部分，其具有諸多的優(yōu)點，在管理、統(tǒng)計財經(jīng)和金融等眾多領(lǐng)域有所應(yīng)用。本文結(jié)合實際案例，分析了Excel軟件的具體優(yōu)勢，重點探討了Excel函數(shù)在財務(wù)工作的應(yīng)用，旨在提高工作的質(zhì)量及效率，以供參考。

【關(guān)鍵詞】Excel函數(shù)；數(shù)據(jù)統(tǒng)計；投資決策；財務(wù)工作

隨著社會經(jīng)濟建設(shè)的快速發(fā)展，各種類型的企業(yè)數(shù)量日益增加。財務(wù)工作作為企業(yè)發(fā)展的重要內(nèi)容，關(guān)系著企業(yè)整體的經(jīng)濟效益及未來的發(fā)展前景。目前，財務(wù)工作除了一些日常的記賬和編制報表外，最主要的是對計算出來的經(jīng)濟數(shù)據(jù)進行分析，以實現(xiàn)高效的財務(wù)決策。Excel是一種以表格形式進行數(shù)據(jù)綜合管理與分析的電子表格軟件，具有數(shù)據(jù)處理、圖形處理和數(shù)據(jù)分析等優(yōu)點，可以幫助用戶建立、編輯和管理各種類型的電子表格，并且它強大的函數(shù)功能和簡單的操作方法為會計領(lǐng)域的財務(wù)工作帶來了許多的便利。但由于許多會計人員對Excel函數(shù)知識的認識不足，在操作過程中并不是十分靈活，影響到財務(wù)工作質(zhì)量。因此，本文通過探討了Excel函數(shù)在財務(wù)工作中的應(yīng)用，以提高企業(yè)的財務(wù)決策質(zhì)量。

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

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

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

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

text為準備從中提取字符串的字符串；

atart_num為準備提取的第一個字符的位置，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”）

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

大家都知道求和函數(shù)SUM，如果要根據(jù)指定條件求和用SUM函數(shù)就比校麻煩，還需要和IF函數(shù)嵌套，我們可以用SUMIF、SUMPRODU函數(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ù)并靈活運用，會使日常工作更加方便快捷。

3.利用財務(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)值，計算年金終值時可忽略；

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

注意：pmt或pv在該函數(shù)中應(yīng)用負數(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ù)（下轉(zhuǎn)第131頁）（上接第129頁）額的期次

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é)果。

4.結(jié)語

綜上所述，通過上述列舉的一些常用Excel函數(shù)可知，Excel具有靈活簡單、功能強大的數(shù)據(jù)處理分析功能，并且Excel的數(shù)據(jù)庫也比較容易維護和更新。為提高財務(wù)工作的決策質(zhì)量，這就需要提高會計人員對于Excel函數(shù)的認識，并從各個維度、范圍對財務(wù)數(shù)據(jù)進行深入的分析，以提高財務(wù)分析的效率，使Excel軟件更好地應(yīng)用于財務(wù)工作中。

參考文獻：

[1]李星月.Excel電子表格在財務(wù)管理中的應(yīng)用[J].金融經(jīng)濟，2013，14.

[2]葉青梅.EXCEL在財務(wù)工作中的運用[J].經(jīng)濟師，2012，06.