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单元格内把上面的公式复制进去，然后下拉公式就可以了