袁俊毅

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

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

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

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

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

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

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

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

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

num_chars為指定所要提取的字符串長(zhǎng)度。

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

=MID（“身份證號(hào)碼”，7，8）

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

=IF（MOD（MID（“身份證號(hào)碼”，17，1），2）

=1，“男”，“女”）

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

格式：DATE（year，month，day）

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

格式：DATEDIF（start_date，end_date，unit）

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

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

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

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

=DATEDIF（DATE（MID（“身份證號(hào)碼”，7，4），

MID（“身份證號(hào)碼”，11，2），MID（“身份證號(hào)碼”，13，2）），TODAY（），“y”）

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

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

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

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

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

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

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

sum-range需要求和的實(shí)際單元格。

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

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

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

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

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

用于多條件統(tǒng)計(jì)是SUMPRODUCT的特殊用法，條件之間用“*”，一方面表示“且”的關(guān)系，另一方面也起一個(gè)乘的作用，即將邏輯值運(yùn)算成數(shù)值；邏輯值在數(shù)值運(yùn)算中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=“采一隊(duì)”），

數(shù)組公式，返回一組0、1表示的值；

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

=“采一隊(duì)”））

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

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

然后一組0、1的值與出庫(kù)金額相乘，就求出我們要的結(jié)果了。

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

*（C2：C18=“采一隊(duì)”）*（H2：H18）），計(jì)算出來采一隊(duì)領(lǐng)用02大類的出庫(kù)金額為7515.66元。

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

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

3.利用財(cái)務(wù)函數(shù)作投資決策

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

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

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

Rate各期利率；

Nper總投資期；

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

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

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

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

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

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

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

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

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

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

=PV（0.1，10，-12000）

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

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

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

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

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

其中：per：計(jì)算本金數(shù)（下轉(zhuǎn)第131頁(yè)）（上接第129頁(yè)）額的期次

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

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

我們可以利用Excel表格建一個(gè)模型，

每期還款額的公式為：

=PMT（0.08，5，-36000）

第一年償還本金的公式為：

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

第一年償還利息的公式為：

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

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

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

4.結(jié)語(yǔ)

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

參考文獻(xiàn)：

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

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