企業馬爾可夫預測的Ｅｘｃｅｌ ＶＢＡ實現

[摘 要]分析了馬爾可夫預測法的步驟，用Excel VBA自定義了一個求矩陣任意次冪的函數，并利用這個函數由一步轉移概率矩陣求得n步轉移概率矩陣，簡化了馬爾可夫預測的過程。

[關鍵詞]馬爾可夫預測;VBA;Excel;自定義函數

doi:10.3969/j.issn.1673-0194.2009.14.015

[中圖分類號]F275[文獻標識碼]A[文章編號]1673-0194(2009)14-0039-02

馬爾可夫預測法是利用狀態轉移概率來研究某一事物在預測時期發生的可能程度的一種預測方法，它應用馬爾可夫鏈的基本原理和方法來預測事物未來的變化趨勢。至今它的理論已發展得較為系統和深入，在自然科學、工程技術及經濟管理領域中有著廣泛的應用[1] 。

Excel作為企業經營管理的常用軟件，本身就有著強大的函數庫和數據分析功能。再利用內嵌于其中的VBA語言編程，可以實現函數和過程的定制，使完成工作任務更加高效和自動化。

一、馬爾可夫預測法簡介

馬爾可夫預測法主要用于市場占有率和銷售期望利潤的預測。其重要特征是無后效性，即事物第n次出現的狀態，只與它第(n-1)次的狀態有關，而與此前的狀態無關。

設變量有N個狀態，它從任一狀態i經一步轉移至任一狀態j都有發生的可能，于是稱Pij為一步轉移概率。將所有的一步轉移概率排列起來構成的矩陣稱為一步轉移概率矩陣。其表達式為:

P=

如果系統在t0時刻處于狀態i，經過n步轉移，在tn時刻處于狀態j，則將這種轉移的可能性數量指標稱為n步轉移概率，記為P(xn=j|x0=i)=Pij(n)。同樣可以得到n步轉移概率矩陣，其表達式為:

P(n)=

其中P與P(n)具有關系式:P(n)=Pn。

轉移矩陣還具有下列與概率相關的性質:

(1)矩陣的每個元素均非負，即P(n)ij≥0;i，j=1，2，…，N。

(2)矩陣每行元素之和等于1，即

∑P(n)ij=1，i=1，2，…，N。

利用馬爾可夫預測法進行企業經營狀況分析的基本思路是:找出過去事物變動的規律性，以此來推測未來事物的變動趨勢，從而預測出企業經營的盈虧概率。

二、用馬爾可夫預測法進行企業經營狀況預測的步驟[2]

用馬爾可夫預測法預測企業經營狀況的關鍵步驟有兩步:

(1)由企業過去的經營狀況確定一步轉移概率矩陣;

(2)求出n步轉移概率矩陣，完成n個時段后企業經營狀況的預測。

其中步驟(1)又可分為兩步，首先要確定各時段的企業經營狀況，其次是根據其狀態轉移規律確定一步轉移概率矩陣中各元素的值。

假設某企業的經營狀況分為盈利、持平、虧本3種狀態，分別記為E1、E2、E3。該企業在過去40個月即2004年1月至2007年4月的經營狀況變化情況如表1所示。

表1 某企業過去40個月經營狀況

表2 某企業過去40個月經營狀態的轉移情況

表3 一步轉移概率矩陣

從表1中可見，在15個從E1出發的狀態中，有3個轉移到了E1，7個轉移到了E2，5個轉移到了E3。相應得出從E2和E3出發的狀態的轉移情況。總的狀態轉移情況如表2所示。

其中行方向上的“盈利、持平、虧本”表示出發的狀態，列方向上的“盈利、持平、虧本”表示一步轉移到的狀態?！昂嫌嫛绷斜硎緩摹坝?、“持平”、“虧本”狀態出發的總的次數。接下來以頻率近似代替概率，便可得到其一步轉移概率矩陣，如表3所示。其中概率值可用Excel的公式計算完成。其數學公式如:從盈利到盈利的一步轉移概率

P11=P(E1→E1)=P(E1|E1)=315=0.200 0;

從持平到虧本的一步轉移概率

P23=P(E2→E3)=P(E3|E2)=413=0.307 7

，其余可相應得出。

由上述，預測的第一步完全可在Excel中利用其公式功能來完成，但第二步需要借助VBA定義一個函數，用于求n步轉移概率矩陣。

三、用VBA自定義求矩陣n次冪的函數[3]

若在本月經營狀況已知的情況下，預測n個月后企業的經營狀況，則僅有一步轉移概率矩陣是不夠的，必須得出n步轉移概率矩陣。因為一步轉移矩陣P和n步轉移矩陣P(n)具有關系P(n)=Pn，故只要求出一步轉移矩陣的n次方即可。盡管Excel中內置了一個求兩個矩陣乘積的函數Mmult()，但用來求某個方陣的任意次方卻很不方便。為此用VBA自定義一個求方陣任意次冪的函數。做法是:在Excel界面下按Alt+F11鍵，進入VBE編輯器，選擇“插入”菜單中的“模塊”，然后在編輯區錄入下列代碼:

Function mmultn(myrange As Range， n As Integer)

′myrange為欲求其次冪的方陣，n為次冪

With myrange

If .Rows.Count <> .Columns.Count Then

MsgBox \"矩陣的行數和列數必須相等!\"

Exit Function

End If

End With

′利用求矩陣乘積的mmult函數實現遞歸調用

If n <= 1 Then

mmultn = myrange

Else

mmultn = WorksheetFunction.mmult(myrange， mmultn(myrange， n - 1))

End If

End Function

上述代碼用遞歸的方式定義了一個函數mmultn(myrange，n)，作用是求myrange區域所示方陣的n次冪。定義之后，這個函數便會出現在Excel內置函數的“用戶定義”類別之中，也就是說，今后可以在Excel界面下用數組公式來使用它。當然，也可以用VBA代碼來調用這個函數過程。

四、用n步轉移概率矩陣完成馬爾可夫預測

例如:若要預測5個月后企業的經營狀況，則可先利用Mmultn函數求出其5步轉移矩陣。方法是:在一步轉移矩陣下方選擇與其同樣大小的一塊空白區域，點擊插入菜單的“函數”，選“用戶定義”類別，選取Mmultn函數，在Myrange參數框內寫入一步轉移矩陣的地址，如“C24:E26”，在N參數框內寫入數字“5”，按Ctrl+Shift+Enter組合鍵，便可在所選區域內得到5步轉移矩陣。結果如圖1所示。

圖1 5步轉移矩陣

如果本月的經營狀況是“持平”，則可預測得出:5個月后企業盈利、持平、虧本的概率分別是0.367 7、0.351 0、0.281 3，即5步轉移矩陣第二行(“持平”所在行)的3個元素。若本月狀況是“盈利”或“虧本”，則5個月后各種狀況的概率分別是第一行和第三行的元素。

五、結束語

利用VBA定制函數，使得求n步轉移概率矩陣的步驟變得簡單高效，從而簡化了馬爾可夫預測的過程。本文中自定義函數的代碼具有通用性，可以推廣到其他領域的馬爾可夫預測過程中。

主要參考文獻

[1] 林元烈.應用隨機過程[M] .北京:清華大學出版社，2002.

[2] 神龍工作室.Excel高效辦公——VBA范例應用[M] .北京:人民郵電出版社，2002.

[3] 伍云輝. Excel VBA辦公應用開發詳解[M] .北京:電子工業出版社，2008.