VLOOKUP函數與矩陣法風險分類

引言：如何分析一家銀行的經營狀況？或者專業一點，如何估算一個銀行的價值？股權的估值。銀行業務主要是存款貸款，簡單一點，貸款減去存款等於股權資本。一個銀行的價值等於貸款價值減去存款價值。存款是欠儲戶的，賬麵價值和實際價值一般不會偏差，做做函證（對賬）即可查清。貸款呢？賬麵價值和實際價值是否偏差就不好說了。

衡量貸款質量，一般是按風險分類。按照監管規定，銀行會披露貸款風險分類情況，五級分類。但凡分析銀行，一個基礎性的工作就是要看貸款的風險分類準確性。貸款風險分類不準確，損失準備（撥備）計提就不足，利潤就會虛增，不良率、撥備覆蓋率、資本充足率、資產收益率等高大上的指標都會出誤差，指標分析變成瞎扯淡。

在對一家銀行的財務審計過程中，貸款（當然還有其他非信貸資產）的風險分類是重頭戲。由於信貸業務的量往往很大，不可能一筆一筆詳查，評估現金流，要簡化處理。

《中國銀監會關於印發<貸款風險分類指引>的通知》（銀監發[2007]54號）

第八條 對零售貸款如自然人和小企業貸款主要採取脫期法，依據貸款逾期時間長短直接劃分風險類別。對農戶、農村微型企業貸款可同時結合信用等級、擔保情況等進行風險分類。

《中國銀監會關於印發<小企業貸款風險分類辦法（試行）>的通知》（銀監發〔2007〕63號）

第五條 銀行業金融機構可根據貸款逾期時間，同時考慮借款人的風險特徵和擔保因素，參照小企業貸款逾期天數風險分類矩陣（見附件）對小企業貸款進行分類。

也就是說，零售貸款通常採用脫期法，農戶貸款、小企業貸款通常採用矩陣法。

二、監管規定

脫期法，比如逾期1天進關注，逾期90天進次級，逾期180天進可疑等等。

例如，《農村合作金融機構信貸資產風險分類指引》（銀監發[2006]23號） 中規定：

脫期法，相當於特殊的矩陣，只有逾期天數一個維度。

矩陣法分類，要結合擔保等因素，例如逾期90天的保證擔保貸款是次級類，而逾期90天的抵質押貸款可能僅僅是關注類。

監管文件中出現過兩套矩陣，一是《小企業貸款風險分類辦法》附件。考慮了擔保因素，信用、保證、抵押、質押四種情況，就是4×6的二維矩陣。

《農村合作金融機構信貸資產風險分類指引》（銀監發[2006]23號） ，考慮了信用等級、擔保因素，相當於三維矩陣（多一重因素就多一個維度），拆為三個二維矩陣。

這些監管上的矩陣，科學性不評論，下面從實務角度說下操作。

三、實務操作

脫期法，一維區間查詢，用VLOOKUP，區間匹配，模糊查詢，自不用說。

二維呢？三維呢？複雜的工具或者寫程序不論，單用Excel公式還是可以做，只是要多次查詢，把多維轉為一維。

以小企業矩陣為例，當我們導出貸款餘額表，含逾期天數、擔保方式信息，如何快速的批量分類？矩陣法，聽起來挺玄乎的，操作中只需要兩個公式即可，VLOOKUP與HLOOKUP.

LOOKUP，查找，V為Vertical，垂直之意，按列查找；H為Horizontal，水平的意思，按行查找。

先建立分類矩陣表，2-5行代表不同擔保方式：信用、保證、抵押、質押四類，C-G列為不同逾期天數，交叉處即為分類結果。

再看貸款餘額表，L列代表逾期天數，M列代表擔保方式，O列是分類結果

公司1，保證擔保，逾期16天，查詢矩陣，第3行，C列，分類為正常。這是手工查詢，如何自動查詢？

手工查詢分兩步，一是查到保證對應的行號，然後查16天對應的列區間。行是精確匹配，列是匹配區間。總之，要查詢兩次，有必要存儲一次查詢結果。

自動查詢，我們先查逾期天數對應的列號，存起來，第二次通過擔保方式查詢要調用，即到底返回第幾列的結果。

首先，建立輔助列N，設定公式，N2=HLOOKUP(L2,分類矩陣!$B$6:$G$7,2,1)

這樣，不同的逾期天數就會「查找」其在分類矩陣表中不同的列

HLOOKUP，按行查找，函數的語法規則如下：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

lookup_value：要查找的值

table_array：要查找的數據表區域

row_index_num：返回數據在區域的第幾行數

range_lookup：模糊匹配/精確匹配

精確查詢是指按照指定條件進行精確查詢,查詢不到結果直接返回錯誤值.模糊查找是指按照指定條件,首先進行精確查詢,當查詢不到結果,模糊匹配小於查找值的最大值

例如，逾期16天，N2=HLOOKUP(L2,分類矩陣!$B$6:$G$7,2,1)，由於分類矩陣B6：G6，第6行沒有16，查詢不到，匹配小於查找值的最大值，即1，1對應的是3，所以返回3，N2=3，依次類推。

最後，O2=VLOOKUP(M2,分類矩陣!A:G,N2,0)

保證擔保貸款，逾期天數在第3列的對應的是正常

抵押擔保貸款，逾期天數在第4列的對應的是關注

這就是最終分類。

向下拖動公式，全部客戶都自動分類完畢。

當然，監管部門的矩陣是基礎，還可以用更精細的矩陣，更多維度的矩陣，做更優化的分類。

以上。

感覺有用就賞個贊吧。