企業(yè)管理中Ｅｘｃｅｌ函數(shù)之應(yīng)用

汪洪祥

摘要：Excel是Office辦公室軟件中非常重要的組成部分，很多企事單位都是依靠Excel進(jìn)行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進(jìn)行圖形分析，其更強(qiáng)大的功能體現(xiàn)在對數(shù)據(jù)的自動處理和計(jì)算，然而很多缺少理工科背景或是對Excel強(qiáng)大數(shù)據(jù)處理功能不了解的人卻難以進(jìn)一步深入應(yīng)用。

關(guān)鍵詞：企業(yè)管理Excel函數(shù)決策

1Excel中的函數(shù)類型

Excel中函數(shù)，就是根據(jù)某些數(shù)據(jù)統(tǒng)計(jì)處理的實(shí)際需要，事先定制好的一個計(jì)算函數(shù)式。它們通過對一些被稱為參數(shù)的指定數(shù)值按照一定順序或結(jié)構(gòu)進(jìn)行運(yùn)算后，返回預(yù)定的結(jié)果。

函數(shù)的類型Excel函數(shù)一共有11類三百余個函數(shù)。分別是數(shù)據(jù)庫函數(shù)、日期與時間函數(shù)、……文本函數(shù)以及用戶自定義函數(shù)。

1.1數(shù)據(jù)庫函數(shù)當(dāng)需要分析數(shù)據(jù)清單中的數(shù)值是否符合特定條件時，可以使用數(shù)據(jù)庫工作表函數(shù)。例如，在一個包含銷售信息的數(shù)據(jù)清單中，可以計(jì)算出所有銷售數(shù)值大于2000且小于2,500的行或記錄的總數(shù)。Microsoft Excel共有12個工作表函數(shù)用于對存儲在數(shù)據(jù)清單或數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行分析，這些函數(shù)的統(tǒng)一名稱為Dfunctions，也稱為D函數(shù)，每個函數(shù)均有三個相同的參數(shù)：database、field和criteria。這些參數(shù)指向數(shù)據(jù)庫函數(shù)所使用的工作表區(qū)域。其中參數(shù)database為工作表上包含數(shù)據(jù)清單的區(qū)域。參數(shù)field為需要匯總的列的標(biāo)志。參數(shù)criteria為工作表上包含指定條件的區(qū)域。

1.2日期與時間函數(shù)通過日期與時間函數(shù)，可以在公式中分析和處理日期值和時間值。

1.3工程函數(shù)工程工作表函數(shù)用于工程分析。這類函數(shù)中的大多數(shù)可分為三種類型：對復(fù)數(shù)進(jìn)行處理的函數(shù)、在不同的數(shù)字系統(tǒng)(如十進(jìn)制系統(tǒng)、十六進(jìn)制系統(tǒng)、八進(jìn)制系統(tǒng)和二進(jìn)制系統(tǒng))間進(jìn)行數(shù)值轉(zhuǎn)換的函數(shù)、在不同的度量系統(tǒng)中進(jìn)行數(shù)值轉(zhuǎn)換的函數(shù)。

1.4財務(wù)函數(shù)財務(wù)函數(shù)可以進(jìn)行一般的財務(wù)計(jì)算，如確定貸款的支付額、投資的未來值或凈現(xiàn)值，以及債券或息票的價值。

1.5信息函數(shù)可以使用信息工作表函數(shù)確定存儲在單元格中的數(shù)據(jù)的類型。信息函數(shù)包含一組稱為IS的工作表函數(shù)，在單元格滿足條件時返回TRUE。例如，如果單元格包含一個偶數(shù)值，ISEV-EN工作表函數(shù)返回TRUE。如果需要確定某個單元格區(qū)域中是否存在空白單元格，可以使用COUNTBLANK工作表函數(shù)對單元格區(qū)域中的空白單元格進(jìn)行計(jì)數(shù)，或者使用JSBLANK工作表函數(shù)確定區(qū)域中的某個單元格是否為空。

1.6邏輯函數(shù)使用邏輯函數(shù)可以進(jìn)行真假值判斷，或者進(jìn)行復(fù)合檢驗(yàn)。例如，可以使用IF函數(shù)確定條件為真還是假，并由此返回不同的數(shù)值。

1.7查詢和引用函數(shù)當(dāng)需要在數(shù)據(jù)清單或表格中查找特定數(shù)值，或者需要查找某一單元格的引用時，可以使用查詢和引用工作表函數(shù)。例如，如果需要在表格中查找與第一列中的值相匹配的數(shù)值，可以使用VLOOKUP工作表函數(shù)。如果需要確定數(shù)據(jù)清單中數(shù)值的位置，可以使用MATCH工作表函數(shù)。

1.8數(shù)學(xué)和三角函數(shù)通過數(shù)學(xué)和三角函數(shù)，可以處理簡單的計(jì)算，例如對數(shù)字取整、計(jì)算單元格區(qū)域中的數(shù)值總和或復(fù)雜計(jì)算。

1.9統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)工作表函數(shù)用于對數(shù)據(jù)區(qū)域進(jìn)行統(tǒng)計(jì)分析。例如，統(tǒng)計(jì)工作表函數(shù)可以提供由一組給定值繪制出的直線的相關(guān)信息，如直線的斜率和y軸截距，或構(gòu)成直線的實(shí)際點(diǎn)數(shù)值。

1.10文本函數(shù)通過文本函數(shù)，可以在公式中處理文字串。例如，可以改變大小寫或確定文字串的長度?？梢詫⑷掌诓迦胛淖执蜻B接在文字串上。下面的公式為一個示例，借以說明如何使用函數(shù)TODAY和函數(shù)TEXT來創(chuàng)建一條信息，該信息包含著當(dāng)前日期并將日期以“dd-mm-yy”的格式表示。

1.11用戶自定義函數(shù)如果要在公式或計(jì)算中使用特別復(fù)雜的計(jì)算，而工作表函數(shù)又無法滿足需要，則需要創(chuàng)建用戶自定義函數(shù)。這些函數(shù)，稱為用戶自定義函數(shù)，可以通過使用Visual Basic forApplications來創(chuàng)建。

2Excel函數(shù)基本用法

2.1單擊需要插入函數(shù)的單元格。

2.2單擊編輯欄中“編輯公式”按鈕fx，將會彈出“插入函數(shù)”窗口，選擇所需要的函數(shù)類別后，就可以在其下的“選擇函數(shù)”中選擇所需的函數(shù)了。

2.3當(dāng)選中所需的函數(shù)后，Excel將打開“函數(shù)參數(shù)”窗口。用戶可以輸入函數(shù)的參數(shù)，當(dāng)輸入完參數(shù)后，在“函數(shù)參數(shù)”中可顯示函數(shù)計(jì)算的結(jié)果。

2.4單擊“確定”按鈕，即可完成函數(shù)的輸入：

3自定義函數(shù)的方法

以定義梯形的面積計(jì)算函數(shù)為例，執(zhí)行Excel[工具]－[宏]－[Visual Basic編輯器]命令，打開內(nèi)置的Visual Basic編輯窗口。

在[工程資源管理器]窗口中，點(diǎn)擊“插入”菜單，選擇“模塊”命令，在下側(cè)的編輯區(qū)域輸入下述代碼。

Function ST(a，b,h)

ST=(a+b)*h／2

Eed Function

關(guān)閉Visual Basic編輯器窗口，至此，自定義函數(shù)完成。

自定義函數(shù)完成后，會自動增加“用戶自定義”函數(shù)類型。

4Excel函數(shù)在企業(yè)決策中的運(yùn)用

函數(shù)在Excel中占有相當(dāng)重要的地位，在企業(yè)管理中被多方面應(yīng)用，下面以資產(chǎn)折舊和投資期數(shù)計(jì)算來說明其用法。

4.1計(jì)算固定資產(chǎn)的折舊固定資產(chǎn)的折舊計(jì)算方法中常用的有兩種，即直線法和年數(shù)總和法。

4.1.1直線法(年限平均法)直線法使用的Excel函數(shù)為SLN()，函數(shù)格式為SLN(cost,salvage，life)，其中：Cost為資產(chǎn)原值；Sal－vage為資產(chǎn)在折舊期末的價值(也稱為資產(chǎn)殘值)；Life為折舊期限(有時也稱作資產(chǎn)的使用壽命)。

例如：某廠購置一臺重型機(jī)械，原始價值120000元，預(yù)計(jì)凈殘值2000元，預(yù)計(jì)使用年限5年。采用直線法提取折舊時，該車床的年折舊額為：SLN(120000，2000，5)=23600(元)

4.1.2年數(shù)總和法使用的Excel函數(shù)為SYD()，函數(shù)格式SYD(cost,salvage，life,per)，其中參數(shù)Cost為資產(chǎn)原值；Salvage為資產(chǎn)在折舊期末的價值(也稱為資產(chǎn)殘值)；Life為折舊期限(有時也稱作資產(chǎn)的使用壽命)：Per為期間，其單位與life相同。函數(shù)SYD()計(jì)算公式如下：

SYD=((cost-salvage)*(life-per+1)*2)／((lie)*(life+1))

還是以上例來計(jì)算各年的折舊額分別為：

第一年：SYD(120000，2000，5，1)=3933333(元)

第二年：SYD(120000，2000，5，2)=31466.67(元)

第三年：SYD(120000，2000，5，3)=23600.00(元)

第四年：SYD(120000，2000，5，4)=15733.33(元)

第五年：SYD(120000，2000，5，5)＝7866.67(元)

4.2投資期數(shù)的計(jì)算計(jì)算項(xiàng)目的投資期，使用NPER()函數(shù)，該函數(shù)是基于固定利率及等額分期付款方式，返回某項(xiàng)投資的總期數(shù)。

函數(shù)格式：NPER(rate，pmt，pv，fv，type)，其中參數(shù)Rate為各期利率，是一固定值；Pmt為各期所應(yīng)支付的金額，其數(shù)值在整個年金期間保持不變。通常，pmt包括本金和利息，但不包括其他的費(fèi)用及稅款；Pv為現(xiàn)值，即從該項(xiàng)投資開始計(jì)算時已經(jīng)入帳的款項(xiàng)，或一系列未來付款的當(dāng)前值的累積和，也稱為本金；

Fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額。如果省略fv，則假設(shè)其值為零；Type數(shù)字0或1，用以指定各期的付款時間是在期初還是期末。

例如：兩個公司A和B，A公司從B公司購買一臺120萬元的設(shè)備，B公司有兩種付款方式供A公司選擇，一是一次性付清全部貨款，二是分若干年每年年初付22萬，假設(shè)資金利率為12％，如果A公司選擇第二種付款方式，簽訂合同時B公司可接受的收款次數(shù)至少為多少次，其收入才不低于一次性收取貨款的收入。

由于A和8兩家公司一個是付款一個是收款，pmt和pv必須有個為負(fù)值。

計(jì)算方法：NPER(12％，-220000，1200000，0，1)=7.75次

因收款付款次數(shù)不為小數(shù)，所以至少為8次，才能保證B公司的收入不受損。