2022/09/25

使用TEXT+DBNUM2(+SUBSTITUTE)將數字變成大寫中文字

      , , ,      No comments   

如果有到銀行辦過事的就知道，大部分金融相關要填寫的單據、票據，都需要使用中文的大寫數字，也就是「壹、貳、參、肆、伍、陸、柒、捌、玖、零」，在 Microsoft  Excel 中只要直接使用TEXT搭配DBNUM2這個格式代碼就可以達成，但這個方式在 Google Sheet 中僅能顯示數詞單位，數字本身並沒有辦法調整為大寫國字，因此這邊額外加上函數SUBSTITUTE，強制取代掉阿拉伯數字，目前看起來是最好的方式了，可以試試看唷。

    基本說明

    TEXT+DBNUM2 函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用，但僅在 Microsoft Excel 中能將數字順利顯示成全部大寫，Google Sheet 僅會加上數詞單位，數字仍會是阿拉伯數字顯示，因此需要再搭配SUBSTITUTE函數公式，將數字取代為大寫國字。
    為方便辨識，儲存格標示為  黃底  的是帶有函式資料的儲存格；儲存格標示為  綠底  的為可修改資料，且會影響公式結果的儲存格；標示為  淺灰底  的為黃底儲存格的公式文字。



    函數實際操作

    此篇的函數公式結果顯示如下(  黃底  內容)：
      《 步驟 》
    1. 調整文字格式(B12)
      使用TEXT函數，先帶入要調整的儲存格位置，格式加上「"」雙引號，帶入[DBNUM2]，後面加入0與大寫數字單位。
      =TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元")

    2. 調整數字1 (B15)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字1，用「"」雙引號框起，取代的文字為大寫的「壹」。
      =SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹")

    3. 調整數字2 (B18)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字2，用「"」雙引號框起，取代的文字為大寫的「貳」。
      =SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳")

    4. 調整數字3 (B21)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字3，用「"」雙引號框起，取代的文字為大寫的「參」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參")

    5. 調整數字4 (B24)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字4，用「"」雙引號框起，取代的文字為大寫的「肆」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆")

    6. 調整數字5 (B27)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字5，用「"」雙引號框起，取代的文字為大寫的「伍」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍")

    7. 調整數字6 (B30)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字6，用「"」雙引號框起，取代的文字為大寫的「陸」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸")

    8. 調整數字7 (B33)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字7，用「"」雙引號框起，取代的文字為大寫的「柒」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒")

    9. 調整數字8 (B36)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字8，用「"」雙引號框起，取代的文字為大寫的「捌」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒"),"8","捌")

    10. 調整數字9 (B39)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字9，用「"」雙引號框起，取代的文字為大寫的「玖」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒"),"8","捌"),"9","玖")

    11. 調整數字0 (B42)
      使用SUBSTITUTE將剛剛完成的公式帶入，用逗點分隔要取代的資料，為文字0，用「"」雙引號框起，取代的文字為大寫的「零」。
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒"),"8","捌"),"9","玖"),"0","零")


    12. 完成~~~~~

    EXCEL公式小工具

    使用方式
    1. 選擇使用的系統 (Microsoft和Google兩個的方式不一樣喔~)
    2. 輸入儲存格位置，也可以直接輸入數值轉換
    3. 按下 Enter 按鈕
    4. 點擊輸出的函數公式就可以直接複製
    5. 將它貼在想要顯示的儲存格內即可
    選擇使用系統

    儲存格位置
    Copied!

    0 Comments:

    張貼留言