Excel在資產記賬管理中的應用實踐

徐欣源

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

關鍵詞： 表格； 資產； 管理； 打印； 宏

中圖分類號：TP319 文獻標志碼：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 引言

人們日常工作需要用到表格，但并不一定知道表格也可以編程。學會Excel編程可以讓我們體會到Excel的功能比我們想象的要強大。利用Excel編程可以提高日常工作效率。本文以資產記賬管理為例探討Excel編程的應用。

1 思路

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

圖1 電表的基本流程

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

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

有三方面的內容：

⑴ 統計盤點表格；

⑵ 進貨清單；

⑶ 出貨清單。

功能：

⑴ 表格根據進貨出貨自動完成統計（用公式實現）；

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

2 功能實現

2.1 輸出統計表格

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

需要用到VGA編程——調用事件：

<？php

Private Sub Worksheet_Selectionchange（ByVal Target As Range）

'當前單元格的行數大于凍結窗口所在行時

If ActiveCell.Row>ActiveWindow.SplitRow Then

'將A1格的值設定為當前單元格所在行第2列的單元格的值

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

'當C1格的值可以轉化為數值時

End If

End Sub

配合公式：

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

品種代碼，0），8））

完成這一工作。

2.2 輸出需要打印的審批單

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

實現過程是：

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

代碼舉例如下：

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

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

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

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

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

提取數字，把該數字加電表數量減1等于終止廠號，然后提取字母：

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

最后，把字母與結果數字加起來輸出：

=CONCATENATE（F8，F9）

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

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

2.4 制作界面頁

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

圖2 表庫記賬界面

BOOK模塊：

<？

Sub auto_open（） '實現打開工作簿后自動關閉所有工具條

Dim Bar As CommandBar

For Each Bar In Application.CommandBars

On Error Resume Next

Bar.Visible=False

Next

Application.DisplayFormulaBar=False

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

Range（"A1"）.Select

End Sub

增加保存退出按鈕：

<？

Private Sub CommandButton1_Click（）

ActiveWorkbook.Save

Application.Quit

End Sub

月底初始化程序：

<？

Sub 初始化（）

MsgBox "本操作只有系統管理員可以執行"，

vbExclamation， "危險操作"

MsgBox "備份了嗎？"， vbYesNo， "請確認"

Dim t As String

t=InputBox（Chr（13） & Chr（15） & "請輸入執行該操作的權限

口令（請勿輸錯）："）

a=123456

If Val（t）=a Then

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

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

ElseIf t <> "" Then

MsgBox "你無權執行該項操作，請與系統管理員聯系！"，

vbExclamation， "警告"

End If

End Sub

2.5 其他相關設置

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

⑵ 定義區域，并應用區域內容，以便規范輸入文字。

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

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

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

2.6 表格的運行說明

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

3 結束語

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

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

參考文獻：

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

社，2009.

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

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

[4] 參考相關函數來源：English forum：http：//www.excelforum.com/