在Excel中,財務人員常常會遇到製作發票模版的活兒。
如上圖紅框處的金額填寫和金額大寫的填寫,可以通過簡單的函數來自動實現。
需要用到的函數:
1、Len函數,返回單元格內字符串的字符數。
2、Rept函數,重複指定次數的文本。
3、Mid函數,從文本字符串中,指定開始位置返回指定長度的字符。
語法:Mid(字符串,從第幾個字符開始,長度為多少字符)
=MID(A1,3,1) :從字符"12345"第3個字符開始,提取1個長度的字符,返回3。
=MID(A2,2,3):從字符"小李飛刀"第2個字符開始,提取3個長度的字符,返回李飛刀。
4、Column函數,返回列號(數字)
5、NumberString函數是Excel裡的隱藏函數,它將阿拉伯數字返回中文。其第二參數有1、2、3三個,分別返回不同形式的中文漢字。
它不能識別小數:
下面先介紹製作金額的填寫:數值格式都為兩位小數,最大數值不超過百萬位,也就是字符數最大為9位。
1、去除小數點,數值*100
2、用Len函數統計每個數值的字符數。
3、通過運算,使用Rept重複指定數量的空格,與數值用 "&" 鏈接在一起,補足數值的字符數為9位。空格補在數值的前面,重複的數量根據9-LEN($A2*100) 運算得出的數值變化而變化。
4、使用Mid函數配合Column函數提取補足9位字符的各個對應的字符(1個長度)。
Column函數右拉形成1、2、3、4.....9的"數列"來確定字符串的起始位置。
合併公式:
=MID(REPT(" ",9-LEN($A2*100)) &$A2*100,COLUMN(A1),1)
公式右拉,完成自動填寫。
至此,金額的填寫製作完成。當然方法還有更多更簡單的。
這裡希望通過介紹簡單的小例子,弱基礎的朋友可以掌握上面的函數,組合起來學習和實踐。
補充部分:人民幣的金額大寫,使用NumberString將相當簡單。
直接使用NumberString(單元格,2)套用公式即可。
當NumberString函數發生錯誤時,使用IFERROR函數排錯,返回"零"。
=IFERROR(NUMBERSTRING(C5,2),"零"),依次在單元格套用函數即可。
如果版本不支持IFERROR,可以換成下面公式:
=IF(ISERR(NUMBERSTRING(C5,2)),"零",NUMBERSTRING(C5,2))
閱讀更多 套路Excel 的文章