【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式




Excel設計函數公式時要留意文字和數值是不同格式,無法直接計算或核對。本文以成本會計更新ERP系統分攤率為例,介紹如何以VALUE和TEXT轉換格式進而執行VLOOKUP。

公司難免變更部門組織架構,對於苦命的成本會計而言,這意味著又要同步變更系統成本分攤率的設定了。實務上如果已經導入ERP成本計算,直接在系統修改相當麻煩且不切實際。這時候可能是會計部門和資訊部門討論之後,打算由資訊人員先從系統匯出一個Excel範本,它是系統可讀取的格式,內容就是原來的分攤設置,會計人員必須依照匯出來的格式修改好,再交給資訊匯入系統,如此有效率地更新系統的分攤設置。

會計在作業中也許會遇到困難,匯出來的Excel範本只有部門編號,而且是文字格式,為了方便討論和設置分攤率,想要將部門名稱以VLOOKUP函數依照部門編號引用過來,可是卻沒辦法順利完成任務,以下具體說明狀況和處置方式:

一、成本分攤表

資訊匯出來的格式範本,有年度、月份、部門編號、會計科目等欄位。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第1張

二、部門代碼表

範本裡只有部門編號,要依照部門重新分配成本分攤率,為了作業方便,想把部門名稱帶出來,先整理一份所有部門的代碼名稱對照表。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第2張

三、VLOOKUP函數

像這種狀況,最適合函數無非VLOOKUP莫屬,拉好公式:「=VLOOKUP(D2,部門!$A$2:$C$6,2,0)」,卻發現無效,明明部門編號相符,可是卻顯示:「#N/A」,意思是Not Available,Excel表示找不到。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第3張

四、錯誤說明

仔細看,儲存格左上角有個綠色三角形,將滑鼠點到儲存格,出現驚嘆號,有一行浮窗說明:「此儲存格內的數字其格式為文字或開頭為單引號」,可以再點一下「關於這個錯誤的說明」。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第4張

五、Excel說明

跳出Excel線上說明課程,瞭解錯誤原因,才能因應修正。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第5張

六、轉換成數字

最直接方案是將文字改成數字,選取好範圍,點擊驚嘆號,整批「轉換成數字」。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第6張

七、成功VLOOKUP

轉換成數字之後,Excel終於醒過來了,VLOOKUP發揮作用,果然是文字格式在作怪。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第7張

八、VALUE函數

瞭解了錯誤原因,不但能因應修正,還可以觸類旁通。由於這個要匯入系統的檔案,最好不要將原來的文字格式改為數值,而且要選取整欄的部門編號範圍,也是麻煩。在不修改格式前提下,聰明作法是套個value函數:「=VLOOKUP(VALUE(D2),部門!$A$2:$C$6,2,0)」,如此一來,方便省事。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第8張

九、TEXT函數

另外一個對策,既然參考值為文字格式,那乾脆把搜尋表格(部門代碼名稱對照表)也改成文字格式,輸入函數公式「=TEXT(A2,”@”)」,意思是轉換成文字格式。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第9張

十、文字格式的VLOOKUP

成功在不改變原報表的前提下,順利執行VLOOKUP帶出部門名稱,接下來可以好好看看成本分攤讓怎麼改了。

【Excel成本會計】ERP分攤表VLOOKUP查找不到部門?VALUE與TEXT轉換文字數值格式 成本會計 第10張

ERP系統編號名稱與Excel實務應用

資訊時代只要涉及到系統分類,都會有編號和名稱的區別。編號是系統識別和數位儲存的唯一身份證號碼,一旦確認決定無法修改。名稱是穿在編號上的衣服,用意讓編號更加好看和容易理解。衣服可以換件穿,名稱也可以事後修改,編號不會因此受影響。

編號是系統唯一的識別碼,所以系統的報表欄位通常都會有編號,但不一定有名稱,因為名稱一目了然容易理解,像這個情形,如果能把編號相對應的名稱帶出來,才是一份資訊充足的報表。很多Excel實務應用都和編號和名稱有關,這篇文章介紹的是成本會計的一個案例,從中可以瞭解到VLOOKUP經典應用以及文字數值間如何轉換,是一個非常好的Excel實務範例。

本文內容取自《會計人的Excel小教室(增訂版)》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

https://www.books.com.tw/products/0010817518




當前文章延伸閱讀:
成本會計