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

徐欣源

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

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

中圖分類號：TP319 文獻標(biāo)志碼：B 文章編號：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)粘９ぷ餍枰玫奖砀?，但并不一定知道表格也可以編程。學(xué)會Excel編程可以讓我們體會到Excel的功能比我們想象的要強大。利用Excel編程可以提高日常工作效率。本文以資產(chǎn)記賬管理為例探討Excel編程的應(yīng)用。

1 思路

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

圖1 電表的基本流程

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

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

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

⑴ 統(tǒng)計盤點表格；

⑵ 進貨清單；

⑶ 出貨清單。

功能：

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

⑵ 在進貨單和出貨單上反映盤點表格中的當(dāng)前庫存，以便核實。

2 功能實現(xiàn)

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

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

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

<？php

Private Sub Worksheet_Selectionchange（ByVal Target As Range）

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

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ù)值時

End If

End Sub

配合公式：

=IF（ISBLANK（$A），""，OFFSET（新表庫實時總存量！$B，MATCH（$A，

品種代碼，0），8））

完成這一工作。

2.2 輸出需要打印的審批單

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

實現(xiàn)過程是：

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

代碼舉例如下：

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

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

遇到的一個難題是：廠編號有時是字母開頭加數(shù)字，這就不可以簡單地使用公式生成終止廠編號。而需要先用公式

=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等于終止廠號，然后提取字母：

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

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

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

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

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

2.4 制作界面頁

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

圖2 表庫記賬界面

BOOK模塊：

<？

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

Dim Bar As CommandBar

For Each Bar In Application.CommandBars

On Error Resume Next

Bar.Visible=False

Next

Application.DisplayFormulaBar=False

Sheets（"主頁面"）.Select '實現(xiàn)打開工作簿時總是出現(xiàn)主頁面

Range（"A1"）.Select

End Sub

增加保存退出按鈕：

<？

Private Sub CommandButton1_Click（）

ActiveWorkbook.Save

Application.Quit

End Sub

月底初始化程序：

<？

Sub 初始化（）

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

vbExclamation， "危險操作"

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

Dim t As String

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

口令（請勿輸錯）："）

a=123456

If Val（t）=a Then

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

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

ElseIf t <> "" Then

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

vbExclamation， "警告"

End If

End Sub

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

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

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

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

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

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

2.6 表格的運行說明

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

3 結(jié)束語

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

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

參考文獻：

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

社，2009.

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

[3] 具體案例：材料進出存明細表，來源：表格之家中文論壇.

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