excel貨幣數字轉漢字金額

㈠ 在Excel使用中，如何將貨幣的數字變成中文的大寫啊

在需要輸出格式中輸入公式=IF(A2<0,"負","")&TEXT(TRUNC(ABS(ROUND(A2,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A2,2))),"",TEXT(RIGHT(TRUNC(ROUND(A2,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A2,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A2,2),3))=".",TEXT(RIGHT(ROUND(A2,2)),"[DBNum2]")&"分","整")，A2為貨幣數字格

㈡ excel中怎麼將表示金額的阿拉伯數字轉換成中文大寫

在單元格格式里有，只是只能把整數轉換，小數部分不可以的。設置見下圖：

㈢ 如何把excel表格中的數字變成中文大寫數字金額

B1=SUBSTITUTE(IF(A1,IF(A1<0,"負",)&TEXT(TRUNC(A1),"[dbnum2]g/通用格式元;[dbnum2]g/通用格式元;")&TEXT(RIGHT(RMB(A1),2),"[>9][dbnum2]0角0分;[>0][dbnum2]0角0分;整"),"零元"),"零分",)

㈣ 怎麼用Excel將小寫金額轉換成人民幣的大寫金額

可以先將小寫金額所在的單元格選中，然後設置單元格格式為中文大寫數字。

1、打開Excel文檔，用滑鼠將所有小寫金額所在的單元格選中，然後右擊被選中的單元格，點擊「從下拉列表中選擇...」按鈕：

㈤ 如果將EXCEL中的數字用函數轉換成中文金額大寫

在EXCEL創建如下vba函數，然後可以把JEZH當內部函數使用。
Function
JEZH(X
As
Range)
If
X
>=
1
Then
If
Int(X)
=
X
Or
Round(X,
2)
=
Int(X)
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元整"
ElseIf
Int(X
*
10)
=
X
*
10
Or
Int(X
*
10)
=
Round(X,
2)
*
10
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"角"
ElseIf
Right(Int(X
*
10),
1)
<>
0
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
ElseIf
X
=
0
Then
JEZH
=
Application.WorksheetFunction.Text(Int(X),
"[DBNUM2]")
&
"元"
ElseIf
X
<
1
And
X
>
0
Then
If
Int(X
*
10)
=
X
*
10
Then
JEZH
=
Application.WorksheetFunction.Text(Right(X,
1),
"[DBNUM2]")
&
"角整"
ElseIf
Right(Int(X
*
10),
1)
<>
0
Then
JEZH
=
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
ElseIf
X
<=
-1
Then
If
Int(X)
=
X
Or
Round(Abs(X),
2)
=
Int(Abs(X))
Then
JEZH
=
"負"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元整"
ElseIf
Int(X
*
10)
=
X
*
10
Then
JEZH
=
"負"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Right(X,
1),
"[DBNUM2]")
&
"角"
ElseIf
Right(Int(X
*
10),
1)
<>
1
Then
JEZH
=
"負"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
"負"
&
Application.WorksheetFunction.Text(Int(Abs(X)),
"[DBNUM2]")
&
"元"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
ElseIf
0
>
X
>
-1
Then
ElseIf
Int(X
*
10)
=
X
*
10
Then
JEZH
=
"負"
&
Application.WorksheetFunction.Text(Right(X,
1),
"[DBNUM2]")
&
"角整"
ElseIf
Right(Int(X
*
10),
1)
<>
1
Then
JEZH
=
"負"
&
Application.WorksheetFunction.Text(Left(Right(Round(X,
2),
2),
1),
"[DBNUM2]")
&
"角"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
Else
JEZH
=
"負"
&
Application.WorksheetFunction.Text(Right(Round(X,
2),
1),
"[DBNUM2]")
&
"分"
End
If
End
Function

㈥ EXCEL表格，將小寫金額數值轉換為大寫中文數字（美元），在轉換的同時會四捨五入

剛剛回答了一樣的問題，不會是同一個人吧。

如果只有兩種貨幣。
=IF(A3="HKD",IF(ROUND(A1,2)<0,"金額為負無效",IF(ROUND(A1,2)=0,"零元",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分"))),IF(A1<0,"金額為負無效",IF(OR(A1=0,A1=""),"零美元",IF(A1-TRUNC(A1)=0,NUMBERSTRING(TRUNC(ROUND(A1,1),0),2)&"美元整",NUMBERSTRING(TRUNC(A1,0),2)&"美元"&TEXT(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2))*100,"[dbnum2]")&"美分"))))

如果只是以美分為單位：
=IF(A3="HKD",IF(ROUND(A1,2)<0,"金額為負無效",IF(ROUND(A1,2)=0,"零元",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分"))),IF(A1<0,"金額為負無效",IF(OR(A1=0,A1=""),"零美元",NUMBERSTRING(ROUND(A1,2)*100,2)&"美分")))

㈦ Excel中如何把數字轉換成中文大寫金額

可以通過自定義數據格式將中文轉換成 大寫。如果需要完整金額，可以參考以下：
=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1)),"[>0][dbnum2];[<0]負[dbnum2];;")&TEXT(RIGHT(FIXED(A1),2),"元[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"元整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")

㈧ Excel 怎麼將數字自轉換成中文大寫貨幣

假如數據在A1欄，在你需要的單元格輸入：=IF(A1=INT(A1),NUMBERSTRING(INT(A1),2)&"元整",IF(A1*10=INT(A1*10),NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(INT(A1*10)-INT(A1)*10,2)&"角整",IF(ROUNDDOWN(A1*10-INT(A1)*10,0)=0,NUMBERSTRING(INT(A1),2)&"元零"&NUMBERSTRING(ROUND(A1*100-INT(A1*10)*10,0),2)&"分",NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(INT(A1*10)-INT(A1)*10,2)&"角"&NUMBERSTRING(ROUND(A1*100-INT(A1*10)*10,0),2)&"分")))
或輸入：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(A1),"[dbnum2];; ")&TEXT(MOD(A1*100,100),"[>9][dbnum2]元0角0分;[=0]元整;[dbnum2]元零0分"),"零分","整")," 元零",)," 元",)希望對你有幫助

㈨ excel中怎樣才能把數字轉換成中文大寫金額並在中文大寫加上「人民幣」

比如金額在A1中,B1輸入公式
="人民幣 "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(a1<0,"負",)&TEXT(INT(ABS(a1)),"[dbnum2];; ")&TEXT(MOD(ABS(a1)*100,100),"[>9][dbnum2]圓0角0分;[=0]圓整;[dbnum2]圓零0分"),"零分","整")," 圓零",)," 圓",)

㈩ excel小寫金額轉換成中文大寫。。

=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"負","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","零")

假設你的小寫金額數放在A列，在A1後面新插入1列，在B1單元格內把上面的公式復制進去，然後下拉公式就可以了