ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合

Excel可以善用函數公式補足公司ERP系統報表的不足。本文以現金收支表為例,先以MID函數擷取單據編號,VLOOKUP函數帶出月份,接著介紹MATCH與INDEX函數的經典組合。

已經導入ERP的企業,通常系統單據會有一定的編碼原則,很常見的會分成兩個部份:「單頭」+「單身」,單頭為單據性質,慣例至少會有一碼區分是哪個模塊所屬的單據,所以「CA」可能表示是現金及銀行模組的單據,單身為單據編號,慣例至少會有幾碼區分是哪個期間所屬的單據,所以「1801」應該就表示是18年1月份的單據。

整理原始資料時,瞭解編碼原則是相當基本的工作,唯有確認了ERP系統、存貨、固定資產、客戶地區等編碼原則,才能依照種種條件進行統計分析。本節即以現金收支表中的單據編號,分享如何將其中的月份抽離出來成為單獨欄位,順便介紹如何以MATCH與INDEX函數的組合,執行會計工作上非常實用的VLOOKUP查找功能。

一、現金收支明細表調整

現金收支明細表,單據編號「CA-180101001」中的「1801」為開單當時的年月期間,系統跑出來的報表並沒有月份的欄位,現在基於資料統計的需要,希望藉助Excel工具,新增一個獨立的月份欄位。

ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 1

二、MID函數提取中間字元

首先以MID文字函數將單據編號中的月份提取出來:「=MID(A2,6,2)」,意思是從儲存格A2內容中的第六個字元開始,取出兩個字元,計算結果即為「01」。

ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 3

三、月份中文英文對照表

雖然上個步驟的「01」到「12」已經表達出月份了,希望報表更加豐富一點,特別編製了一個月份英文簡稱的對照表,從「Jan」到「Dec」,所以現在任務變成是,如何從「01」轉換成「Jan」、「12」轉換成「Dec」。

ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 5

四、第一個想到VLOOKUP

像這樣的任務,熟悉Excel的讀者應該都會想到VLOOKUP:「=VLOOKUP(E2,’3′!A:B,2,0)」,有興趣讀者可以參考《會計人的Excel小教室》第三章「VLOOKUP函數應用」,有一整章五個小節完整介紹VLOOKUP函數在實務工作上的應用。

ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 7

五、MATCH函數確定位置

接下來進行這一節的重點,首先設計函數公式:「=MATCH(E2,’3′!A:A,1)」,表示以MATCH函數得到「01」在第三步驟月份對照表A欄的順序,第三個引數「1」和VLOOKUP函數第四個引數作用類似,這裡「1」表示條件值必須完全相同。

如果是第一次使用MTACH函數,對於這函數有些陌生,可以將滑鼠游標移到資料編輯列該函數的位置,點擊左邊的「fx」,即會跳出「函數引數」視窗,有關於此函數及其引數的簡要說明,甚至可以點選最左下角的「函數說明」,可以超連結到微軟官方的支援中心,會有更加詳細的解說。

ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 9

六、INDEX函數傳回對應值

在MTACH函數的基礎上,設計函數公式:「=INDEX(‘3’!B:B,G2)」,表示以MATCH公式所得到的月份順序,再傳回相對應的月份英文簡稱。

ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 11

七、函數公式巢狀合併使用

將月份英文簡稱的對照表貼到此工作表上,如此在參照引用時較為直覺,再將MATCH和INDEX的公式合併:「=INDEX(L:L,MATCH(E2,K:K,1))」。

ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 13

VLOOKUP與INDEX、MATCH組合比較

通常會以VLOOKUP函數依照條件傳回相對應位置的儲存格內容,不過在Excel還可以MATCH和INDEX函數搭配運用,也能完成和VLOOKUP同樣的任務。VLOOKUP是以一整個表格作為查找範圍,INDEX(MATCH())是直接以某一陣列範圍作為查找對象,因此VLOOKUP較為直覺容易理解,但是Excel計算處理可能較花時間,INDEX(MATCH())的函數公式複雜一些,但是執行時較有效率,而且靈活性較高,工作中可依照實際狀況選擇合適方案。

筆者在《讓上班族狠賺的EXCEL財報分析術》一書上,有一大部份是介紹如何以 VBA取得財報資料,再以Excel函數公式自動化整理,在整個模型裡面,MATCH和INDEX超到核心關鍵的查找作用,確實是比VLOOKUP較為靈活自由,有興趣讀者可以參考看看。

每天學習,每天充電:Excel函數文章合輯

YouTube video
ERP現金收支表新增月份欄位:VLOOKUP與MATCH、INDEX組合 15

最新文章: