Excel在資產(chǎn)記賬管理中的應(yīng)用實(shí)踐

徐欣源

摘 要： Excel作為辦公自動(dòng)化軟件擅長(zhǎng)處理各種電子表格，利用Excel提供的宏操作、表格函數(shù)等功能可以生成多種風(fēng)格的統(tǒng)計(jì)報(bào)表，且能夠顯示數(shù)據(jù)走向和預(yù)測(cè)工作進(jìn)度，這些功能可在一個(gè)工作簿中實(shí)現(xiàn)。

關(guān)鍵詞： 表格； 資產(chǎn)； 管理； 打印； 宏

中圖分類(lèi)號(hào)：TP319 文獻(xiàn)標(biāo)志碼：B 文章編號(hào)：1006-8228（2013）06-72-02

Application of Excel in accounting management

Xu Xinyuan

（Suzhou Power Supply Company Metrology Center， Suzhou， Jiangsu 215000， China）

Abstract： Excel， as office automation software， can deal with all kinds of electronic forms. VBA and form function， provided by the Excel， are used to generate various styles of statistical forms and graphs. Moreover， it can display the data trend and predict the work schedule. These functions can be realized just in one sheet.

Key words： Excel； asset； management； print； VBA

0 引言

人們?nèi)粘９ぷ餍枰玫奖砀瘢⒉灰欢ㄖ辣砀褚部梢跃幊獭W(xué)會(huì)Excel編程可以讓我們體會(huì)到Excel的功能比我們想象的要強(qiáng)大。利用Excel編程可以提高日常工作效率。本文以資產(chǎn)記賬管理為例探討Excel編程的應(yīng)用。

1 思路

根據(jù)開(kāi)源軟件社區(qū)的思想，設(shè)計(jì)一個(gè)軟件，先讓他的基本功能運(yùn)行起來(lái)，也就是先設(shè)計(jì)一個(gè)實(shí)用模型圖，然后根據(jù)實(shí)際需要，邊使用邊完善其功能。電表廠物資的基本流程如圖1所示。

圖1 電表的基本流程

記賬管理所用表單，主要是用于從廠方進(jìn)貨到校驗(yàn)班出貨（根據(jù)表庫(kù)的配送計(jì)劃）的批次與盤(pán)點(diǎn)管理。

首先設(shè)計(jì)表庫(kù)（包括物供科的進(jìn)貨、出貨流水賬）。

有三方面的內(nèi)容：

⑴ 統(tǒng)計(jì)盤(pán)點(diǎn)表格；

⑵ 進(jìn)貨清單；

⑶ 出貨清單。

功能：

⑴ 表格根據(jù)進(jìn)貨出貨自動(dòng)完成統(tǒng)計(jì)（用公式實(shí)現(xiàn)）；

⑵ 在進(jìn)貨單和出貨單上反映盤(pán)點(diǎn)表格中的當(dāng)前庫(kù)存，以便核實(shí)。

2 功能實(shí)現(xiàn)

2.1 輸出統(tǒng)計(jì)表格

在統(tǒng)計(jì)表格中，用SUMIF語(yǔ)句搜索進(jìn)貨、出貨單中符合條件的規(guī)格表，對(duì)其進(jìn)行累加。

需要用到VGA編程——調(diào)用事件：

<？php

Private Sub Worksheet_Selectionchange（ByVal Target As Range）

'當(dāng)前單元格的行數(shù)大于凍結(jié)窗口所在行時(shí)

If ActiveCell.Row>ActiveWindow.SplitRow Then

'將A1格的值設(shè)定為當(dāng)前單元格所在行第2列的單元格的值

Range（"A1"）.Value=Cells（ActiveCell.Row， 2）.Value

'當(dāng)C1格的值可以轉(zhuǎn)化為數(shù)值時(shí)

End If

End Sub

配合公式：

=IF（ISBLANK（$A），""，OFFSET（新表庫(kù)實(shí)時(shí)總存量！$B，MATCH（$A，

品種代碼，0），8））

完成這一工作。

2.2 輸出需要打印的審批單

根據(jù)管理工作所要求的格式，我們需要為每一批進(jìn)表填寫(xiě)入庫(kù)審批單。

實(shí)現(xiàn)過(guò)程是：

新建一張表格作為數(shù)據(jù)庫(kù)表單，就是“批次”表單。紀(jì)錄每一批表的進(jìn)貨和出貨及貨物地址，以及貨物審批單的內(nèi)容，例如：型號(hào)、廠家、常數(shù)、表精度、使用周期等。然后，通過(guò)審批表單打印輸出每一批內(nèi)容。

代碼舉例如下：

=OFFSET（批次！$C，MATCH（審批表打印！B4，批次！C：C，0）-2，4）

搜索與審批表打印單的B4（審批號(hào)）相符的批次表的一行輸出打印該批次內(nèi)容。

遇到的一個(gè)難題是：廠編號(hào)有時(shí)是字母開(kāi)頭加數(shù)字，這就不可以簡(jiǎn)單地使用公式生成終止廠編號(hào)。而需要先用公式

=LOOKUP（9E+307，--MID（C8，MIN（FIND（{0；1；2；3；4；5；6；7；8；9}，

C8&1234567890）），ROW（INDIRECT（"1："&LEN（C8）））））

提取數(shù)字，把該數(shù)字加電表數(shù)量減1等于終止廠號(hào)，然后提取字母：

=LEFT（C8，（FINDB（TEXT（F7，"@"），C8）-1））

最后，把字母與結(jié)果數(shù)字加起來(lái)輸出：

=CONCATENATE（F8，F(xiàn)9）

2.3 隨著數(shù)據(jù)量的增大，表格的打開(kāi)速度變慢的處理方法

我們發(fā)現(xiàn)批次表上重復(fù)信息很多，如果能建立品種數(shù)據(jù)表單進(jìn)行調(diào)用就能有效避免重復(fù)調(diào)用，于是建立了“品種”表單，大大地簡(jiǎn)化了數(shù)據(jù)。需對(duì)“審批表打印”單也做出相應(yīng)的修改，添加嵌套的OFFSET語(yǔ)句。

2.4 制作界面頁(yè)

綜合以上兩個(gè)方面的內(nèi)容基本上就是一個(gè)簡(jiǎn)單的實(shí)用模型。在此基礎(chǔ)上，為了使界面更加大方、友好，又作了一些頁(yè)面設(shè)計(jì)，如圖2所示。

圖2 表庫(kù)記賬界面

BOOK模塊：

<？

Sub auto_open（） '實(shí)現(xiàn)打開(kāi)工作簿后自動(dòng)關(guān)閉所有工具條

Dim Bar As CommandBar

For Each Bar In Application.CommandBars

On Error Resume Next

Bar.Visible=False

Next

Application.DisplayFormulaBar=False

Sheets（"主頁(yè)面"）.Select '實(shí)現(xiàn)打開(kāi)工作簿時(shí)總是出現(xiàn)主頁(yè)面

Range（"A1"）.Select

End Sub

增加保存退出按鈕：

<？

Private Sub CommandButton1_Click（）

ActiveWorkbook.Save

Application.Quit

End Sub

月底初始化程序：

<？

Sub 初始化（）

MsgBox "本操作只有系統(tǒng)管理員可以執(zhí)行"，

vbExclamation， "危險(xiǎn)操作"

MsgBox "備份了嗎？"， vbYesNo， "請(qǐng)確認(rèn)"

Dim t As String

t=InputBox（Chr（13） & Chr（15） & "請(qǐng)輸入執(zhí)行該操作的權(quán)限

口令（請(qǐng)勿輸錯(cuò)）："）

a=123456

If Val（t）=a Then

月底還原，已錄制“月底還原”宏的引用

MsgBox "已成功初始化"， vbInformation， "信息"

ElseIf t <> "" Then

MsgBox "你無(wú)權(quán)執(zhí)行該項(xiàng)操作，請(qǐng)與系統(tǒng)管理員聯(lián)系！"，

vbExclamation， "警告"

End If

End Sub

2.5 其他相關(guān)設(shè)置

⑴ 把兩張表合并為一張表，建立記賬主頁(yè)面，美化表格界面。

⑵ 定義區(qū)域，并應(yīng)用區(qū)域內(nèi)容，以便規(guī)范輸入文字。

⑶ 利用相對(duì)引用，根據(jù)批次表的庫(kù)存信息，變化該條信息顏色。

⑷ 增加篩選用"宏"按鈕，以便方便操作。

⑸ 增加常用庫(kù)存分析表，以便隨時(shí)掌握當(dāng)前庫(kù)存信息，得知進(jìn)表需求信息和當(dāng)前工作進(jìn)度。

2.6 表格的運(yùn)行說(shuō)明

表格在使用之前，必須通過(guò)Excel軟件的工具選項(xiàng)的安全性菜單，把安全性級(jí)別降到“中”或“低”，這樣才能使表格的程序以“宏”的方式正常運(yùn)行。如果不運(yùn)行“宏”，表格的許多功能就不會(huì)加載。

3 結(jié)束語(yǔ)

本文所述Excel工作簿的應(yīng)用，經(jīng)過(guò)改進(jìn)，最后達(dá)到了比較完美的效果，提高了日常工作效率，節(jié)省了大量的時(shí)間。

作為個(gè)人的統(tǒng)計(jì)工具，Excel足夠了，但是它也有自身的缺點(diǎn)：Excel版本的更新帶來(lái)的在一些配置比較低的機(jī)器上運(yùn)行緩慢（這個(gè)主要是因?yàn)閿?shù)據(jù)在Excel打開(kāi)是全部需要調(diào)入內(nèi)存的原因）；數(shù)據(jù)獨(dú)占模式，也決定了它只適合個(gè)人單獨(dú)使用，所謂的共享工作簿其實(shí)也只是記錄了每個(gè)人的操作步驟，以便及時(shí)還原數(shù)據(jù)，并不是真正的多人操作。還有數(shù)據(jù)量增大和公式嵌套增多時(shí)出現(xiàn)運(yùn)行緩慢的問(wèn)題不可避免。

參考文獻(xiàn)：

[1] 韓小良.EXCELVBA高效辦公實(shí)用寶典（第一版）[M].中國(guó)鐵道出版

社，2009.

[2] 趙志東.EXCELVBA技巧應(yīng)用（第一版）[M].人民郵電出版社，2007.

[3] 具體案例：材料進(jìn)出存明細(xì)表，來(lái)源：表格之家中文論壇.

[4] 參考相關(guān)函數(shù)來(lái)源：English forum：http：//www.excelforum.com/