讓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）

（責編：賈偉）