ExcelVBA在腳踏車製造行業鋼管採購最佳化決論文
摘 要：在腳踏車製造行業鋼管採購決策中結合鋼管裁減工藝，應用Excel VBA尋找鋼管採購的最優長度組合，達到減少鋼管裁減浪費的目的。以某腳踏車企業為例項闡述Excel VBA的應用過程，以及應用前後該企業鋼管裁減浪費情況對比來證明Excel VBA在採購最佳化決策中應用的價值。
關鍵詞：Excel VBA；採購；鋼管長度；浪費金額
1 引言
企業進行生產經營活動，就不可避免地要採購原材料。採購作為物流的第一個環節，其成本高低對於整個生產的總成本有著舉足輕重的作用。採購成本主要包括三部分：材料成本、採購管理成本、儲存成本。材料成本是採購成本中最基礎的，其採購數量的大小將直接影響到儲存成本。如何確定最佳採購數量一直是學術界研究的熱點問題。現有確定採購量的方法都是從庫存控制角度考慮採購數量，沒有結合實際生產工藝，然而現實中很多企業在使用這些方法時都必須考慮其加工工藝。例如腳踏車製造行業，由於企業是按一定規格長度採購鋼管的，而多個腳踏車零部件要共用這些鋼管，若不考慮零部件的尺寸和加工工藝就會造成鋼管浪費。
鋼管是腳踏車製造行業必不可少的物料，其採購是按長度計算。鋼管浪費現象主要是因為採購的鋼管的長度不合理引起的。理論上來講，如果按照各種車型量身採購各種型號鋼管，則可以完全消除鋼管的浪費，不過這樣做將會引起管理費用大量增加。因此對於該行業，在改進過程中，如何在不增加鋼管的採購種類數量，僅透過改變鋼管採購的長度來減少鋼管浪費的研究還很少，也沒有一套適用的工具。對這方面的研究之所以較少，是因為腳踏車各個零部件加工工藝有差異，對多品種企業其不同產品之間加工工藝也有所不同，這些都給研究帶來了困難。
目前中國大多數腳踏車企業根據經驗確定鋼管採購長度，由於沒有結合加工工藝，加工過程中鋼管裁減的浪費是巨大的。為了減少這種物料浪費，除了改善加工工藝還可以在採購數量中增加加工工藝的考慮來減少浪費。本文就應用Excel VBA研究腳踏車製造行業鋼管採購，應用科學的方法替代經驗管理的方式減少鋼管裁減所造成的浪費。
2 問題描述
在腳踏車企業鋼管裁減現場，可以非常直觀地看出鋼管在使用過程中存在著較嚴重的浪費。鋼管的裁減餘料只能作為廢品回收處理，在MRP系統中，對鋼管的管理相當薄弱，鋼管的單位為根，即使只擷取很短的一段，在系統中也是認為使用一根，而現場又是按照長度來進行管理的。這樣造成的'後果就是鋼管的各種管理缺陷被掩蓋。本文主要研究在加工工藝不變的前提下，如何透過科學的計算來確定鋼管的最優採購長度，以達到減少裁減餘料浪費的目的。各符號的含義及計算公式如下所示：
計算某種鋼管部件在裁減時每根鋼管浪費的長度Wi = Li - li×Ni,其中：Ni = ［Li/li］。Li表示第i種車型現用鋼管型號長度；li表示第i種車型鋼管現截長度；Ni表示第i種車型每根鋼管可截數量。［…］ 在此處的含義為向下取整。例如：［3.8］ = 3。
計算該種鋼管部件在一定時期內浪費的總長度TWi = Wi×Si×ni/Ni。Si表示第i種車型的銷售量；ni 為一輛腳踏車使用該類部件的數量。對於ni這一常數，因為一輛腳踏車需要使用2根後上叉和2根後下叉。因此在計算該種管材時，需要數量應為車輛銷售量的2倍。
統計橫截面型別相同的鋼管浪費的總長度TTW = ∑TWi。公式的含義即統計所有使用該型別鋼管的部件，並將所有部件在裁減過程中浪費的總長度相加，即可得到該型別鋼管的浪費的總長度。
統計橫截面型別相同的鋼管浪費的總金額V=TTW×p。p表示所用鋼管單位長度的價格。
本文的目的就是要尋找在加工工藝不變即l不變的情況下使得鋼管浪費總金額V最小時的L值。
3 Excel VBA
Excel中使用的程式語言叫做Visual Basic for Applications,簡稱為VBA。其基本形態是Excel的宏語言透過特定語法（VBA）來控制Excel內建物件，來完成一些自動化過程，而這些過程通常都用來完成一些重複性的工作。Excel中的宏可以稱為Excel應用程式程式設計的起點，它可以展示基本的Excel VBA程式工作的步驟。
應用Excel VBA的目的是在給定條件下尋求總浪費金額最小值所對應的鋼管長度，這樣得到的鋼管長度即是我們鋼管採購的最優長度。尋優過程的原理：由於採購時精度可以達到10mm，所以適合使用計算機窮舉所有采購組合。根據採購時最短長度要求和集裝箱長度限制，讓計算機在最短長度和最長長度之間的資料中遍歷各種組合，以獲得浪費最小的組合，即Min_i，Min_j。尋求Min_i，Min_j的計算步驟如下：
（1）鋼管總浪費長度Waste，因為是要尋求總浪費長度最小值，所以Waste的初始值應當是無窮大的。可以根據公司具體情況設定Waste的初始值。
（2）鋼管長度i,j。鋼管長度種類越多管理費用越高，公司為節約管理費用一般採購兩種長度型別即可。初始化i值，j=i+10，鋼管長度取i,j時的總浪費長度TTW與初始化Waste相比較。Waste
=TTW時將TTW的值賦予Waste並且Min_i=i，Min_j=j。
（3）應用巢狀迴圈：i值不變，j以10mm增加繼續步驟2直到j值達到上限跳出該迴圈。
（4）i值增加10mm，j=i+10，繼續步驟3。
（5）i值達到上限程式結束，所得到的Min_i，Min_j就是要尋找的鋼管長度最優組合。
4 例項說明
本文以某自行車廠的鋼管採購長度最佳化為例來闡述Excel VBA在腳踏車製造行業鋼管採購最佳化決策中的應用過程。腳踏車的鋼管主要分為上管、下管、中管、上叉、下叉。該廠現採購的主要的鋼管型號如表1所示：
由於在鋼管的裁減過程中，並不區分鋼管的用途，只確認鋼管的型號和裁減長度。因此將鋼管的資料分類進行調整，如表2所示。
從表中可以看出當採購長度為4660與6070時鋼管的浪費達到最小。改善前浪費長度為293824，浪費金額為758.94739元，改善後浪費長度為36726.13元，浪費金額為94.86元。改善後浪費金額僅為原有的12.50%。浪費程度減少了87.50%。可見改善效果是相當明顯的。
改善後基本上都可以減少70%-95%的浪費，至少可以減少50%以上的浪費。浪費金額也從改善前的15萬多減少到現有的4萬多。改善總幅度達到73.06%。改善效果是比較令人滿意的。
對鋼管的管理最佳化過程中，由於改進過程所用方法簡單，易於標準化。管理鋼管的工作人員有章可詢，不需要工作人員根據經驗自行計算，既避免了經驗和計算過程中可能出現的各種錯誤，又提高了工作效率。並且即使遭遇人員流失，新來的員工能對鋼管的管理迅速上手。
5 結論
電子表格並不能提供所有問題的解決方案，使用Excel VBA程式來處理自動重複計算任務是很有效的。根據此特點本文就嘗試將Excel VBA應用在腳踏車製造行業鋼管採購最佳化決策中，並用實際的案例說明了其可行性以及應用的價值。本文提出的方法對於腳踏車製造行業具有普適性，所有腳踏車企業均可應用此方法決策鋼管最優採購長度。
參考文獻
［1］@郝淵曉,王茜草等.現代物流採購管理［M］.中山大學出版社，2003，（5）.
［2］@馬士華等.供應鏈管理［M］.機械工業出版社，2000.
［3］@傑克遜,斯湯頓著.基於Excel和VBA的高階金融建模［M］.中國人民大學出版社，2006.
［4］@黃睿,馬然.Excel VBA應用程式專業設計實用指南［M］.電子工業出版社，2006.
［5］@閆宏印等.VB程式設計［M］.電子工業出版社,2001.