基于VBA的包裝桶管理系統的實現
作者：靳建水，王衛華，趙慶云
【摘要】包裝桶的信息管理繁瑣，針對物流管理難點，在原有的EXCEL 表格基礎上用VBA 進行開發，制作了包裝桶信息管理系統，實現了對5000 多個包裝桶的流動跟蹤，統計檢索。可方便查出外在滯留桶信息等，大大提高了工作效率，確保了統計的準確率，快速性，提高了公司資產的自動化管理水平。
【關鍵詞】VBA；EXCEL；包裝桶；管理系統
1 引言
EXCEL 表格處理數據功能強大，應用廣泛。在EXCEL 里處理數據操作直觀，修改方便，還可以處理較復雜的數學運算及邏輯運算。利用VBA 可以在EXCEL 里進行二次開發，可實現復雜的數據統計[1]。用戶對EXCEL 的熟練運用也利于系統應用。系統的應用改變了數據查詢，錄入信息等手動方式，使包裝桶的跟蹤管理變得不再雜亂繁瑣。
2 系統背景
隨著公司快速發展，電解液產量增長較快，從而電解液包裝桶也在短短幾年從幾百個迅速增長至5 000 多個，每天發貨桶發給誰，有多少個，發貨日期，空桶從哪里回來，回桶數是否一致等都需要準確統計。而包裝桶在不同客戶的滯留期也不相同，有的1 個月即可回桶，有的3 個月還不能回來。這又要求對外在桶進行統計，看看超過3 個月的有哪些桶，都在哪家客戶手里。手動統計在桶數量較少時還能應付，桶數量龐大時便力不從心，效率低下，漏洞百出。不銹鋼桶的成本較高，一個2 000 多元，如果丟失嚴重，對公司資產造成很大損失，因而公司對包裝桶自動化管理的需求越來越迫切。
3 系統分析
原有的包裝桶手動錄入的表格是EXCEL 表格，員工將每個桶對應唯一編號，每500 個桶占一頁，按日期對每個桶的發貨、回桶進行記錄。
從原始記錄看，桶的記錄比較雜亂，存在的問題主要有：
(1)同一客戶的名稱錄入不一樣，例如“哈爾濱光宇”有時省略為“哈光宇”；
(2)很多桶存在信息丟失，例如1# 桶在11.5.30 發給哈爾濱光宇后，回桶記錄丟失，后來又在11.5.30 發給東莞正德邦；
(3)存在已經丟失的桶，例如10# 桶和12# 桶沒有任何記錄，視為丟失；
(4)發貨的客戶名稱和回桶的客戶名稱應該一致，可很多回桶的客戶名稱是物流公司。從原始表格提取多個表，組成數據庫，從而實現自動檢索難度較大，主要困難有：
1)將原始表分解為多個表單，例如分解為發貨信息表、回桶信息表等，在這些表中還要包含桶的最后狀態，即是否在公司外，在哪家客戶手中，滯留了多長時間，某個桶周轉了幾次，某家客戶手中現有那些桶等，操作起來非常復雜。
2)建立起多個表后，還要將5 000 多個桶的信息分別手動錄入多個表中，工作量大。
3)很多桶在流通當中存在信息丟失，信息錯誤等，需要進行核實、修改以符合數據庫要求，如表單主鍵不能為空等[2]。
綜合以上情況，決定在原有的EXCEL 表格的基礎上進行VBA 開發，不改變現有的表格格式，既符合員工的使用習慣，又減少了開發的工作量，將統計結果顯示在指定的頁中，便于員工方便地進行打印及二次處理。
4 系統設計
4.1 功能設計
根據用戶需求，系統主要實現以下功能：
（1）按桶號檢索，即檢索出某個桶的所有流通信息。
（2）按客名稱檢索，即檢索出某個客戶曾經使用過哪些桶。
（3）按日期檢索，即某天發出哪些桶，發給誰；收回哪些桶。
（4）外在包裝桶檢索，即檢索出所有外在桶的信息，具體分為某家客戶現滯留哪些桶，滯留多久；也可按日期查出，某天發出去的桶，有哪些還沒有回來，都在哪個客戶手中。在檢索結果中也可統計出哪些桶丟失。
（5）發貨桶錄入功能。
（6）返回桶錄入功能。
在主界面中“包裝桶檢索”比較直觀，選中檢索條件，點擊“檢索按鈕”即可在相應頁面中顯示檢索結果。“外在包裝桶檢索”中分為“一次檢索”和“條件檢索”，由于原始數據量大，為了提高檢索效率，先用“一次檢索”統計出所有外在桶的信息，在此基礎上再進行條件檢索，可大大縮短檢索時間，只要原始表格沒有改動，“一次檢索”使用一次即可。
以前手動錄入數據相當麻煩，比如錄入0008# 桶的發貨信息，需要先打開“0001-0500”頁面，找到0008# 桶所在行，再順著該行找到記錄末尾的空格，核實該空格處在“發貨日期”列，無誤后輸入發貨日期，客戶名稱。錄入回桶信息和錄入發貨信息一樣，費時費力，很容易看錯行，看錯列，錄入錯誤時，再一一檢查修改，耗費精力。實現自動錄入后，只需要在指定區域輸入錄入信息，點擊“輸入”按鈕，即可將錄入信息自動分配到各個桶號位置。在錄入前還增加“檢查”功能，即核實錄入信息是否有誤，并在輸入區域中自動顯示出最近一次的回桶客戶名稱。例如錄入0001# 桶發貨信息，當該桶已經發出，最后不是回桶記錄，不應該再有發貨信息，則這時會提示錯誤，并自動跳轉到相應頁面，例如0001# 桶最后信息是發貨信息，于11.10.12 發給哈爾濱光宇，這時再錄入發貨信息，則會提示錯誤。
在錄入回桶信息時也同樣增加了“檢查”功能，并顯示出最近一次發貨客戶名稱。當回桶客戶名稱與發貨客戶名稱不一樣時，能夠很方便的看出。0001# 桶的回桶客戶名稱是“福斯特（岳陽）”，而上一次發貨客戶名稱是“哈爾濱光宇”，這樣可以很快發現，回桶信息有誤。
4.2 程序設計
4.2.1 檢索程序設計。
程序設計是在VB 編輯環境下進行，現以“外在檢索”功能為例進行說明。“一次檢索”先從原始數據中提取出基礎數據，即對每個桶進行判斷，最后是發貨信息還是回桶信息，如果是“發貨信息”說明桶還沒有回來，將該桶號和最后一次發貨信息提取出來；如果是“回桶信息”則說明桶已經收回，跳過判斷下一桶號。由于原始數據是每500 個桶一個頁面,系統預留到6 000 個桶，共有12 個頁面，針對每一個頁面做一個子程序，共有12 個子程序。在主程序中調用各個子程序。
Call 外在桶檢索1
Call 外在桶檢索2
…
Call 外在桶檢索12
由圖1 的原始表單可以看出，頁面的第1 列是“桶號”，第2 列是“回桶日期”，第3 列是“客戶名稱”，第4 列是“發貨日期”，第5 列是“客戶名稱”，以此類推，從中可發現，從第4 列開始，每4 列是一個發收循環，而且“發貨日期”和發貨的“客戶名稱”是同時出現的，不會只有“發貨日期”而沒有發貨的“客戶名稱”這種情況，同理“回桶日期”和回桶的“客戶名稱”也是同時存在。由此建立如下算法，設某桶號所在行的最后數據單元格的列數為n，令z=(n-3)/4，若z<0 則桶丟失，若z 為正整數包括0 則桶在公司內，若z 為正小數則桶在公司外。EXCEL 表格中最大列數是256 列，設某桶號所在的行是x，定義m=256，調用如下語句：
While (IsEmpty(Cells(x, m)))
m = m - 1
Wend
先判斷cells(x，256)單元格是否為空，若為空判斷cells(x，255) 單元格是否為空，以此類推直到對應的單元格不為空為止，取該單元格的列數值，即為n，再運用上述算法進行判斷桶的狀態，算法語句如下：
Cells(i + 2, n1 + 4) = (Cells(i + 2, n1 + 3) - 3) / 4
If Cells(i + 2, n1 + 4) < 0 Then
Cells(i + 2, n1 + 5) =“丟失”
ElseIf (Int(Cells(i + 2, n1 + 4)) < Cells(i + 2, n1 + 4)) Then
Cells(i + 2, n1 + 5) =“在外”
Else
Cells(i + 2, n1 + 5) =“在內”
End If
程序中將各個中間變量及結果用數組進行存儲，然后再輸出到“外在桶檢索結果”頁面中指定的單元格中，作為條件檢索的基礎數據。程序中Cells(i + 2, n1 + 3)即為“外在桶檢索結果”中存儲列數n 的單元格。Cells(i + 2, n1 + 4)用來存儲計算結果，Cells(i + 2, n1 + 5)用來顯示桶的狀態。檢索子程序流程圖如圖1 所示。
條件檢索的程序設計主要在初次檢索的結果中進行篩選，根據客戶要求，按“客戶名稱”檢索時要能夠實現模糊檢索，比如查詢“哈爾濱光宇”滯留了哪些桶，則只需要輸入“光宇”即可檢索出發貨客戶名稱中包含“光宇”字樣的所有信息。這里調用InStr()函數[3]來實現，具體語句如下：
While Not (IsEmpty(Cells(n, 1)))
If InStr(Cells(n, 2), c1) <> 0 Then
a(i) = Cells(n, 1)
b(i) = Cells(n, 2)
c(i) = Cells(n, 3)
i = i + 1
End If
n = n + 1
Wend
當InStr(Cells(n, 2), c1) <> 0 時，說明包含關鍵字，將桶的信息存入數組中。
4.2.2 錄入程序設計。錄入程序分為發貨信息錄入程序和回桶信息錄入程序，設計思路基本一致，這里以發貨信息錄入為例進行說明。在指定區域輸入要錄入的信息后，先檢查信息是否有誤，在檢查程序里邊需要判斷桶的當前狀態，判斷方法和查詢外在桶方法一樣，用同樣的算法語句實現。檢查無誤，即可啟動錄入程序，程序先將各個桶的發貨信息存儲在數組中，然后根據桶號轉到相應的表單中，如桶號為3008#，則轉入“3001-3500”頁面內，在該頁面內，找到桶號為“3008”的那一行，接著找出該行最后不為空的單元格，在該單元格后填入對應的發貨信息。程序流程圖如圖2 所示。
5 結論
本系統已投入實際應用3 個月，運行穩定，使用效果良好，對于自動化管理的效果，員工感受頗深。公司的資產管理有很多值得挖潛、提升的地方，加強自動化管理可發揮出強大的作用，既提高了工作效率，節約了人力物力，也增強了公司的競爭力，因而自動化管理值得大家去細化、研究。
[參考文獻]
[1]劉利國.基于Excel 的管道工程控制測量計算[J].中國科技博覽,2012,(15):128-129.
[2]高陽.數據庫技術與應用[M].北京：電子工業出版社，2008.
[3]化柏林.用VBA 實現文獻計量分析研究中的數據預處理技術[J].現代圖書情報技術,2007,（4）:70-74.
