ERP現金收支表Excel函數補上月份 - 會計

Eden avatar
By Eden
at 2020-04-22T07:48

Table of Contents



ERP現金收支表沒有月份?MID函數再搭配VLOOKUP補上,或者MATCH與INDEX雙劍合璧

網誌圖文版:

https://www.b88104069.com/archives/4478

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

銀行出納的ERP現金收支表沒有月份欄位

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

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


MID、VLOOKUP、MATCH、INDEX函數實務應用

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

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

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

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

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

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

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

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

VLOOKUP函數與INDEX、MATCH組合兩者比較

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



延伸閱讀:

Indirect、Match、Row這三個Excel函數組合,竟然足以搜尋任何關鍵字重組報表

https://www.b88104069.com/archives/4411

報表並不難,聰明使用Excel,瞬間搞定工作大小事

https://www.b88104069.com/archives/4408

Excel如何以切片器快速切換直條圖分析對象

https://www.b88104069.com/archives/4397

--


周末,我們繼續Excel:精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室: https://www.facebook.com/acctexcel


--
Tags: 會計

All Comments

Ivy avatar
By Ivy
at 2020-04-26T15:50
謝謝分享!

會研所及會計師的考試準備

Yedda avatar
By Yedda
at 2020-04-22T00:22
各位版友好,小弟目前是國立科大會計系大二生,已有挑戰會研所的打算,目前補習班課程是排2年研究所課程+1年CPA 上網爬了些文,大多數人好像都是去補研究所課程,然後在畢業那年順便考過CPA的中+成+審,這樣是蠻可行的,因為聽說研所的考題難於CPA。 那小弟有個疑問,在研所考完到CPA考試有大概半年的空檔,若利用 ...

樞紐分析是否能做出此結果

Lauren avatar
By Lauren
at 2020-04-21T18:18
Database是上半部表格,想用樞紐分析成下面的結果是否有方法?因爲欄標籤同時放Step 1和Step2, 會顯示step1 tool分類再顯示step2 tool, 找不到方法可以分開顯示,還是這表格不適 合做樞紐?謝謝 Orz(Google 關鍵字不知道怎麼打 https://i.imgur.com/b ...

稀釋eps股票股利問題

Iris avatar
By Iris
at 2020-04-21T15:03
請問為什麼在算稀釋eps的時候 若遇到股票股利發放,是要全年度流通股數做調整呢 例如: 7/1發放股票股利 不是應該只有半年流通股數變多嗎 即1/2的時間需要調整流通股數? 為什麼呢 不懂邏輯 - ...

109年中會 鄭泓函授課程

Vanessa avatar
By Vanessa
at 2020-04-21T14:58
想徵求109年中會 鄭泓函授課程轉讓,亦可接受合購 請來信報價,謝謝。 - ...

有限公司出資轉讓

Olive avatar
By Olive
at 2020-04-21T14:43
請教會計師版的各位大大 公司法 第110條(出資轉讓) 股東非得其他股東表決權過半數之同意, 不得以其出資之全部或一部,轉讓於他人。 董事非得其他股東表決權三分之二以上之同意, 不得以其出資之全部或一部,轉讓於他人。 這部分的1/2、2/3是硬性規定嗎? 章程能不能訂更高? 例如其他股東100 ...