在Excel中利用宏和函數(shù)提高計量物資庫存查詢效率

李煥秋

(國網(wǎng)冀北唐山市豐南區(qū)供電公司,河北 唐山063300)

供電公司計量班組在庫房管理工作中，出入庫物資的種類及數(shù)量較多，查詢庫存量的工作較為煩瑣，工作效率不高。庫房管理人員做的出入庫記錄大多在Excel或WPS電子表格中完成，若查詢一兩種物資的庫存量工作量不大，但同時查詢幾十種或上百種物資的庫存量，給庫房盤點工作帶來不便。根據(jù)遇到的實際問題，通過利用電子表格中的宏和函數(shù)，編制一個小程序，來解決以上問題，使計量庫房管理更加準確、高效。下面以在Excel中利用宏和函數(shù)的方法為例，說明該程序的編制過程。

1 程序結(jié)構(gòu)設(shè)計

在Excel中新建工作薄，命名為“計量物資出入庫記錄”，插入4個工作表，分別命名為“入庫”“出庫”“庫存”“sheet1”。按照日常出入庫記錄中的格式，設(shè)計出合理的表格版式，“sheet1”為空表即可?！叭霂臁惫ぷ鞅碛脕碛涗浉鞣N物資的入庫情況，“出庫”工作表用來記錄各種物資的出庫情況，“庫存”工作表可以查看全部物資的入庫、出庫及庫存情況、“sheet1”為錄制宏的時候使用。在“庫存”工作表中點擊“開發(fā)工具”，點擊“插入控件”，插入“ActiveX控件”中的“命令按鈕”，加入一個按鈕，命名為“數(shù)據(jù)刷新”。此時，該程序的結(jié)構(gòu)設(shè)計基本完成，較為簡單，點擊“數(shù)據(jù)刷新”按鈕，即可查看全部物資的出入庫及庫存情況。

2 編制方法

2.1 錄制宏

在“入庫”工作表中錄制一個新的宏，命名為“數(shù)據(jù)更新”，下面是錄制宏的內(nèi)容。選擇“入庫”工作表中的“C”到“G”列復(fù)制，粘貼到“sheet1”工作表中。在“sheet1”工作表中，選中“A”到“E”列，打開“數(shù)據(jù)”菜單，選擇“刪除重復(fù)項”按鈕，選中“型號”選項，點擊“確定”，刪除重復(fù)值，保留唯一值，如圖1所示。

圖1 刪除重復(fù)項

點擊右鍵，對唯一值進行復(fù)制，粘貼到“庫存”工作表中對應(yīng)列，點擊“停止錄制”按鈕，宏錄制完成。

2.2 插入函數(shù)

在“庫存”工作表中，“入庫數(shù)量”一列的“F2”單元格輸入公式“=SUMIF(入庫!$D:$D,$B2,入庫!$H:$H)”，下拉復(fù)制大約100行（根據(jù)物資種類數(shù)量確定），在“出庫數(shù)量”一列的“G2”單元格輸入公式“=SUMIF(出庫!$D:$D,$B2,出庫!$H:$H)”，下拉復(fù)制大約100行即可，在“庫存數(shù)量”列“H2”單元格輸入公式“=F2-G2”，下拉復(fù)制大約100行即可。

在“庫存”工作表中點擊已經(jīng)插入的“數(shù)據(jù)刷新”按鈕，雙擊進入VBA設(shè)計界面，在Click事件中輸入代碼“Call數(shù)據(jù)更新”，可調(diào)用宏“數(shù)據(jù)更新”，完成庫存量的查詢工作。

目前，程序已經(jīng)編制完成，點擊“數(shù)據(jù)刷新”按鈕，程序未出現(xiàn)中斷，運行成功，全部物資的入庫、出庫及庫存數(shù)量，全部查詢出來。充分證明了VBA技術(shù)、宏、函數(shù)在Excel或WPS電子表格中強大的生命力，能夠化繁為簡，提高工作效率。