基于VBA的福建省道路客運需求分布預(yù)測

林玉英，陳 誠，劉娜翠，邱榮祖

(福建農(nóng)林大學交通與土木工程學院，福建福州350002)

0 引言

隨著經(jīng)濟的發(fā)展，近年來福建省加快推進了交通基礎(chǔ)設(shè)施建設(shè)，初步建立了由公路、水路、鐵路、民航、管道等5種運輸方式構(gòu)成的綜合交通運輸體系，運輸網(wǎng)絡(luò)結(jié)構(gòu)進一步得到完善。道路旅客運輸是綜合交通運輸體系的主要組成部分，加強對道路客運市場的調(diào)查和研究，對道路客運需求量進行分析與預(yù)測，可為相關(guān)部門進行資源合理配置提供參考依據(jù)，促進全省運輸業(yè)和國民經(jīng)濟的發(fā)展。

客運需求預(yù)測的方法主要分為兩類，即定性預(yù)測和定量預(yù)測。定性方法主要有專家分析法、德爾菲法等;定量方法主要有灰色系統(tǒng)法、指數(shù)平滑法、回歸分析法、四階段預(yù)測法等。其中，四階段法作為交通需求預(yù)測的傳統(tǒng)方法，其邏輯關(guān)系明確、步驟分明，在城市交通規(guī)劃領(lǐng)域已被廣泛應(yīng)用，理論和技術(shù)上已趨于成熟［1］。但隨著道路網(wǎng)絡(luò)的不斷完善，運輸網(wǎng)絡(luò)結(jié)構(gòu)越來越復(fù)雜，交通小區(qū)數(shù)量不斷增多，OD分布矩陣數(shù)據(jù)量更加龐大，分布預(yù)測求解時需要多次迭代，計算復(fù)雜，費時費力，該方法的應(yīng)用受到了很大的局限。Excel VBA具有高效處理大型計算及重復(fù)性任務(wù)，定制程序界面，開發(fā)受保護的專業(yè)程序等強大功能［2］，加上用戶對Excel的界面和操作熟悉等優(yōu)勢，已廣泛應(yīng)用于人事管理、教學管理、決策分析、數(shù)學模型求解計算等［3-6］。因此筆者利用VBA對Excel進行二次開發(fā)，實現(xiàn)道路客運需求分布預(yù)測的快速計算，提高預(yù)測精度和效率，為相關(guān)人員進行道路客運需求分析和運力規(guī)劃提供輔助手段。

1 預(yù)測模型與方法

四階段預(yù)測法包括交通生成預(yù)測、交通分布預(yù)測、交通方式劃分預(yù)測、交通分配預(yù)測。交通分布預(yù)測是交通規(guī)劃四階段法的主要步驟之一，是利用現(xiàn)狀(基準年)的交通發(fā)生與吸引量來推求目標年的交通發(fā)生與吸引量［7］。交通分布預(yù)測的方法很多，其中增長系數(shù)法，模型簡單，計算結(jié)果能滿足客運需求分布預(yù)測的精度要求。

筆者采用增長系數(shù)法中的底特律法對客運需求分布進行預(yù)測。該方法考慮的因素比較全面，收斂速度較快。假設(shè)交通分布模式現(xiàn)在和將來變化不大，適用于小區(qū)或小區(qū)間的出行不太受空間阻撓因素的影響，而只受地區(qū)間產(chǎn)生及吸引特性影響的空間分布形態(tài)。

OD表是描述各交通小區(qū)的發(fā)生與吸引交通量表格，是交通分布預(yù)測乃至交通規(guī)劃步驟中的重要數(shù)據(jù)［8］。應(yīng)用底特律法進行客運需求分布預(yù)測，首先要準備基準年OD調(diào)查表數(shù)據(jù)、客運生成預(yù)測的目標年發(fā)生和吸引量，然后計算相應(yīng)的比率，進行迭代運算。通過反復(fù)計算分布量、發(fā)生量、吸引量和增長比率，直到比率達到需求的預(yù)測精度，則輸出預(yù)測的最終結(jié)果。其運算流程如圖1。

圖1 底特律法運算流程Fig.1 Operation flow diagram of Detroit method

1)令迭代次數(shù)m=0。

3)求出各小區(qū)的發(fā)生與吸引交通量的增長率FmOi，，增長率函數(shù)為f()：

5)收斂判別。

若滿足上述條件，則結(jié)束計算;反之，令 m=m+1，返回到第②步。

2 基于Excel VBA的預(yù)測系統(tǒng)設(shè)計

2.1 系統(tǒng)分析與設(shè)計

由于福建省道路客運分布預(yù)測涉及的數(shù)據(jù)量大，其預(yù)測結(jié)果直接影響到全省客運規(guī)劃、運力投放、設(shè)施建設(shè)等，用戶進行客運分布預(yù)測操作時，主要的需求是界面友好，操作簡單，運行速度快，結(jié)果準確，數(shù)據(jù)保密和安全。另外，由于每個區(qū)域的交通小區(qū)的數(shù)目是不同的，系統(tǒng)要有一定的靈活性和適應(yīng)性。根據(jù)用戶的需求，系統(tǒng)應(yīng)具備以下功能，如圖2。

圖2 系統(tǒng)的主要功能Fig.2 Main functions of the system

2.1.1 權(quán)限認證

用于驗證用戶是否為指定操作人員。如果未輸入或者輸入的不是預(yù)設(shè)的用戶或者密碼不正確，系統(tǒng)都會有相應(yīng)的提示，并且等待用戶繼續(xù)輸入。如果輸入的用戶名和密碼都正確，則進入初始OD矩陣所在的工作表。

2.1.2 操作幫助

給操作人員具體的步驟提示，例如提示用戶按照給定的模版在初始表輸入數(shù)據(jù)，從而為程序運行提供初始數(shù)據(jù)。這個步驟十分關(guān)鍵，如果用戶輸入有誤，將直接影響到后面的迭代運算結(jié)果。系統(tǒng)應(yīng)設(shè)計智能著色，只要選定某個預(yù)備輸入數(shù)據(jù)的單元格，則該單元格對應(yīng)的整行和整列都有鮮艷的背景顏色，以防止用戶在輸入大量數(shù)據(jù)的過程中，因看錯行或列所造成的數(shù)據(jù)錯位。

2.1.3 數(shù)據(jù)錄入

福建省各個城市、各個縣城，各個城鎮(zhèn)的相關(guān)部門在進行交通分布預(yù)測時，考慮到的交通小區(qū)的數(shù)目是不同的。為了提高系統(tǒng)的適用性，必須根據(jù)用戶需求的交通小區(qū)數(shù)目來進行預(yù)測運算。

2.1.4 需求預(yù)測

通過控件工具箱，設(shè)計表單控件中的命令按鈕，實現(xiàn)單擊命令按鈕則進行自動運算，此后無需人工操作，用戶只需等待結(jié)果。

2.1.5 結(jié)果輸出

當交通小區(qū)的數(shù)目多時，可能要進行無數(shù)次迭代，程序運行結(jié)束時，工作簿中迭代的工作表數(shù)目很多，這時需要有運行結(jié)果的通知，并且直接跳到最終的OD矩陣工作表，以便用戶查看計算結(jié)果，從而輔助決策。

2.2 系統(tǒng)功能實現(xiàn)

系統(tǒng)要實現(xiàn)以上功能，首先建立Excel工作簿，命名為“客運需求分布預(yù)測系統(tǒng)”，在工作簿中要設(shè)計初始迭代模板的工作表，將其命名為“0次”。以福州、廈門、泉州3個交通小區(qū)為例，設(shè)計輸入模板，以及智能背景著色，方便用戶輸入現(xiàn)狀OD矩陣、發(fā)生量、吸引量等數(shù)據(jù)，如圖3。

設(shè)計系統(tǒng)權(quán)限認證界面，編程實現(xiàn)用戶登錄。當用戶雙擊工作簿時，跳出權(quán)限認證窗口，在窗口文本框填入正確的用戶名和密碼之后，允許工作簿打開，激活初始迭代的“0次”工作表，并用信息框顯示操作幫助信息。當用戶按照模板輸入數(shù)據(jù)之后，點擊“開始計算”按鈕時，系統(tǒng)跳出輸入框，提供給用戶輸入交通小區(qū)數(shù)目，默認為3。如果用戶誤輸入了非數(shù)值，則提示“無效的數(shù)字”，如果用戶輸入正確的交通小區(qū)數(shù)目，點擊輸入框的“確定”按鈕，系統(tǒng)即進行迭代運算，幾秒之后可看到運行結(jié)果通知信息和最終結(jié)果工作表，如圖4。

圖3 初始數(shù)據(jù)工作表的輸入模板Fig.3 Input template of the initial data worksheet

圖4 運行結(jié)果工作表Fig.4 Worksheet of the running results

3 系統(tǒng)應(yīng)用

3.1 交通小區(qū)劃分

以福建省省內(nèi)班線道路客運為例，應(yīng)用該系統(tǒng)進行客運需求分布預(yù)測。根據(jù)福建省的實際情況，同時保證全面反映交通流特征和調(diào)查數(shù)據(jù)易于統(tǒng)計的前提下，按照行政區(qū)域設(shè)區(qū)市為單位，將其劃分為9個交通小區(qū)，分別是福州、莆田、泉州、廈門、漳州、龍巖、三明、南平、寧德。

3.2 OD 調(diào)查表

通過調(diào)查我省9地市出站率數(shù)據(jù)，經(jīng)整理得出2010年、2011年各個交通小區(qū)客運量OD調(diào)查表，如表1、表2。

表1 2010年OD調(diào)查Table 1 OD questionnaire of 2010

表2 2011年OD調(diào)查Table 2 OD questionnaire of 2011

3.3 系統(tǒng)驗證

以表1的數(shù)據(jù)為基準，結(jié)合近5年各交通小區(qū)人口分布和GDP增長，應(yīng)用該系統(tǒng)計算獲得2011年預(yù)測表，見表3。

表3 2011年OD預(yù)測結(jié)果Table 3 OD forecasting result table for 2011

將2011年預(yù)測OD表(表3)和2011實際調(diào)查OD表(表2)中的數(shù)據(jù)進行對比分析，求出兩個表對應(yīng)數(shù)據(jù)的相對誤差，獲得平均相對誤差率為13.1%，由此可知預(yù)測表中大部分的流量流向數(shù)據(jù)與調(diào)查表數(shù)據(jù)相吻合，表明應(yīng)用該系統(tǒng)進行福建省道路客運需求分布預(yù)測是可行的。

3.4 客運需求分布預(yù)測

現(xiàn)以2011年為基準年(以表2為基準表)，綜合各地區(qū)人口和經(jīng)濟發(fā)展趨勢，預(yù)測2015年的客運分布量。進入“客運分布預(yù)測系統(tǒng)”工作簿，按照“0次”工作表的模版，將表2的數(shù)據(jù)輸入，點擊“開始計算”按鈕，按照提示輸入交通小區(qū)數(shù)目“9”，得出2015年客運分布預(yù)測的最終結(jié)果，如表4。

表4 2015年OD預(yù)測結(jié)果Table 4 OD forecasting result table for 2015

3.5 預(yù)測結(jié)果分析

4 結(jié)語

福建Y省客運需求旺盛，數(shù)據(jù)量較大，客運需求分布預(yù)測工作顯得十分繁瑣。Excel作為優(yōu)秀的Office組件之一，功能強大，運用廣泛，簡單易操作，且為用戶所熟悉。對于福建省的道路客運需求分布預(yù)測，結(jié)合增長系數(shù)法中的底特律方法，運用 Excel VBA開發(fā)“客運分布預(yù)測系統(tǒng)”，能夠?qū)τ脩舻臋?quán)限進行驗證，起到保護數(shù)據(jù)的作用，而且可以根據(jù)用戶的需求，將其輸入的不同的交通小區(qū)數(shù)代入程序進行迭代運算，最終得出目標年的客運分布量。初步應(yīng)用研究表明，系統(tǒng)的用戶界面友好，可擴展性強，運行效率高，在用戶滿意的精度下，通常都是5 s以內(nèi)完成數(shù)次迭代。另外，得出的最終結(jié)果表與用戶輸入的初始數(shù)據(jù)表結(jié)構(gòu)是一致的，便于用戶在Excel進行其他數(shù)據(jù)處理操作，使工作效率大大提高。但分布預(yù)測僅是構(gòu)成整個4階段預(yù)測的重要部分，尚有客運生成預(yù)測，交通方式劃分，交通分配等，系統(tǒng)功能有待于進一步完善。

［1］邱榮祖.公路交通運輸系統(tǒng)工程［M］.廈門：廈門大學出版社，2001.Qiu Rongzu.Road Transportation Systems Engineering［M］.Xiamen：Xiamen University Press，2001.

［2］羅剛君.Excel VBA程序開發(fā)自學寶典［M］.2版.北京：電子工業(yè)出版社，2011：10-11.Luo Gangjun.Excel VBA Program Development Self-Study Book［M］.2nded.Beijing：Publishing House of Electronics Industry，2011.

［3］韋慶進，徐丹.基于VBA的人事管理交互系統(tǒng)［J］.高師理科學刊，2011(6)：38-41.Wei Qingjin，Xu Dan.Personnel management interactive systems based on the VBA［J］.Journal of Science of Teachers’College and University，2011(6)：38-41.

［4］Kelvin W W W，John P B.Teaching Excel VBA as a problem solving tool for chemical engineering core course［J］.Education for Chemical Engineers，2010，5(4)：72-77.

［5］Hyde K M，Maier H R.Distance-based and stochastic uncertainty analysis for multi-criteria decision analysis in Excel using visual basic for application［J］.Environmental Modeling＆ Software，2006，21(12)：1695-1710.

［6］譚緒泉，谷建才，孫榮喜，等.基于Excel VBA實現(xiàn)林分空間結(jié)構(gòu)參數(shù)的自動計算［J］.東北林業(yè)大學學報，2011，39(6)：116-118.Tan Xuquan，Gu Jiancai，Sun Rongxi，et al.Auto-calculation of spatial structure parameters for forest stand based on excel VBA［J］.Journal of Northeast Forestry University，2011，39(6)：116-118.

［7］邱榮祖.現(xiàn)代物流運輸系統(tǒng)工程［M］.北京：人民交通出版社，2011.Qiu Rongzu.Modern Logistics Transportation Systems Engineering［M］.Beijing：China Communications Press，2011.

［8］王煒，陳學武.交通規(guī)劃［M］.北京：人民交通出版社，2007.Wang Wei，Chen Xuewu.Transportation Planning［M］.Beijing：China Communications Press，2007.