EXCEL表格汇总时货币怎样自然生成大写元角分整?
发布网友
发布时间：2022-04-26 20:45
共4个回答
热心网友
时间：2023-10-31 05:38
用 VBA 写个函数
这是我在网上找到的
Function RMB_DaXie(ByVal Num As String) As String
Num = Application.WorksheetFunction.Round(Num, 2)
RMB_DaXie = Application.WorksheetFunction.Text(Num, "[DBNum2]")
If Num - Int(Num) = 0 Then
RMB_DaXie = RMB_DaXie & "元整"
Else
If Abs(Round(Num, 2)) - Int(Abs(Round(Num, 2))) <= 0.09 Then
RMB_DaXie = Replace(RMB_DaXie, ".", "元") & "分"
Else
If Abs(Round(Num, 2) * 10) - Int(Abs(Round(Num, 2) * 10)) = 0 Then
RMB_DaXie = Replace(RMB_DaXie, ".", "元") & "角整"
Else
RMB_DaXie = Replace(RMB_DaXie, ".", "元")
RMB_DaXie = Left(RMB_DaXie, Len(RMB_DaXie) - 1) & "角" & Right(RMB_DaXie, 1) & "分"
End If
End If
End If
If Abs(Num) < 1 Then RMB_DaXie = Replace(RMB_DaXie, "零元", "")
If Abs(Num) < 0.1 Then RMB_DaXie = Replace(RMB_DaXie, "零", "")
If Left(RMB_DaXie, 1) = "-" Then RMB_DaXie = Replace(RMB_DaXie, "-", "（负）")
If Num = 0 Then RMB_DaXie = "零元"
End Function
这是另一个VBA函数（有点繁）
Public Function DaXie2(ByVal Num As Double, Optional JinWei As Boolean = True)
'**********************************************
'* 人民币中文大写函数 *
'**********************************************
Dim Str_Wei1, Str_Wei2, Str_Num As String
Dim Str_Yuan, Str_Jiao, Str_Feng As String
Dim Str_RetYuan, Str_RetJiaoFeng As String
Dim Str_Ret1, Str_Ret2 As String
Dim Num_Yuan As Double
Dim Num_Jiao, Num_Feng As Single
Dim Num_i, I As Integer
Str_Wei1 = "拾佰仟"
Str_Wei2 = "万亿万亿万亿"
Str_Num = "壹贰叁肆伍陆柒捌玖"
'范围检测
If Num >= 10 ^ 14 Or Num <= -10 ^ 14 Then
DaXie2 = "＃数字超出转换范围！！！＃"
Exit Function
End If
'符号检测
If Num < 0 Then
Str_RetYuan = "（负）"
Else
Str_RetYuan = ""
End If
Num = Abs(Num)
'是否“四舍五入”
If JinWei Then
Num = Round(Num, 2) '“四舍五入”
Num_Yuan = Int(Num)
Num_Jiao = Int((Num - Num_Yuan) * 10)
Num_Feng = (Num - Num_Yuan) * 100 - Num_Jiao * 10
Else
Num_Yuan = Int(Num) '舍去最末位
Num_Jiao = Int((Num - Num_Yuan) * 10)
Num_Feng = Int((Num - Num_Yuan) * 100) - Num_Jiao * 10
End If
' ***** 整数部分转换
Str_Yuan = Trim(Str(Format(Num_Yuan, "###0")))
For I = Len(Str_Yuan) To 1 Step -1
Num_i = Val(Mid(Str_Yuan, Len(Str_Yuan) - I + 1, 1)) '第“I”位数
If Num_i <> 0 Then
'非“零”数字转换
Str_RetYuan = Str_RetYuan & Mid(Str_Num, Num_i, 1) ' “I”位数字转换
If (I - 1) Mod 4 <> 0 Then ' “I”位数位转换
Str_RetYuan = Str_RetYuan & Mid(Str_Wei1, (I - 1) Mod 4, 1)
Else
If (I - 1) \ 4 <> 0 Then
Str_RetYuan = Str_RetYuan & Mid(Str_Wei2, (I - 1) \ 4, 1)
End If
End If
Else
'“零”数字转换
If Right(Str_RetYuan, 1) <> "零" Then
'前一位非“零”：本位是“万亿”位加“万、亿”，否则其他加“零”
If (I - 1) Mod 4 <> 0 Then
Str_RetYuan = Str_RetYuan & "零"
Else
If (I - 1) \ 4 <> 0 Then
Str_RetYuan = Str_RetYuan & Mid(Str_Wei2, (I - 1) \ 4, 1)
End If
End If
Else
'前一位也为“零”：本位是“万亿”位加“万、亿”（并删除前面的“零”）；其他跳过此位
If (I - 1) Mod 4 = 0 Then
Str_RetYuan = Left(Str_RetYuan, Len(Str_RetYuan) - 1)
If (I - 1) \ 4 <> 0 Then
Str_RetYuan = Str_RetYuan & Mid(Str_Wei2, (I - 1) \ 4, 1)
End If
End If
End If
End If
Next I
If Num_Yuan = 0 Then
Str_RetYuan = ""
Else
Str_RetYuan = Str_RetYuan & "元"
End If
' ***** 小数部分转换
If Num_Jiao <> 0 Then
Str_RetJiaoFeng = Mid(Str_Num, Num_Jiao, 1) & "角"
If Num_Feng <> 0 Then
Str_RetJiaoFeng = Str_RetJiaoFeng & Mid(Str_Num, Num_Feng, 1) & "分"
Else
Str_RetJiaoFeng = Str_RetJiaoFeng & "整"
End If
Else
If Num_Feng <> 0 Then
If Num_Yuan <> 0 Then
Str_RetJiaoFeng = "零" & Mid(Str_Num, Num_Feng, 1) & "分"
Else
Str_RetJiaoFeng = Mid(Str_Num, Num_Feng, 1) & "分"
End If
Else
Str_RetJiaoFeng = Str_RetJiaoFeng & "整"
End If
End If
If Num < 0 Then '返回（包括符号）
DaXie2 = "（负）" & Str_RetYuan & Str_RetJiaoFeng
Else
DaXie2 = Str_RetYuan & Str_RetJiaoFeng
End If
End Function
热心网友
时间：2023-10-31 05:39
ctrl+1之后，打开对话框，数字／特殊／中文大小写数字，确定．你再重新输入一个数字，成功了吧？
热心网友
时间：2023-10-31 05:39
=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,,"零")),"零分","整")
自定义单元格格式为
[DBNum2][$-804]yyyy"年"mm"月"dd"日"
热心网友
时间：2023-10-31 05:40
如果你是用智能ABC输入法的话，我可以告诉你一个快速输入法：
在中文输入状态按住shift后输入I,然后在后面输入你想要的数字如1234567，空格就出现下面的数字！
壹贰叁肆伍陆柒捌玖
哈哈，一般人我不告诉他！
热心网友
时间：2023-10-31 05:38
用 VBA 写个函数
这是我在网上找到的
Function RMB_DaXie(ByVal Num As String) As String
Num = Application.WorksheetFunction.Round(Num, 2)
RMB_DaXie = Application.WorksheetFunction.Text(Num, "[DBNum2]")
If Num - Int(Num) = 0 Then
RMB_DaXie = RMB_DaXie & "元整"
Else
If Abs(Round(Num, 2)) - Int(Abs(Round(Num, 2))) <= 0.09 Then
RMB_DaXie = Replace(RMB_DaXie, ".", "元") & "分"
Else
If Abs(Round(Num, 2) * 10) - Int(Abs(Round(Num, 2) * 10)) = 0 Then
RMB_DaXie = Replace(RMB_DaXie, ".", "元") & "角整"
Else
RMB_DaXie = Replace(RMB_DaXie, ".", "元")
RMB_DaXie = Left(RMB_DaXie, Len(RMB_DaXie) - 1) & "角" & Right(RMB_DaXie, 1) & "分"
End If
End If
End If
If Abs(Num) < 1 Then RMB_DaXie = Replace(RMB_DaXie, "零元", "")
If Abs(Num) < 0.1 Then RMB_DaXie = Replace(RMB_DaXie, "零", "")
If Left(RMB_DaXie, 1) = "-" Then RMB_DaXie = Replace(RMB_DaXie, "-", "（负）")
If Num = 0 Then RMB_DaXie = "零元"
End Function
这是另一个VBA函数（有点繁）
Public Function DaXie2(ByVal Num As Double, Optional JinWei As Boolean = True)
'**********************************************
'* 人民币中文大写函数 *
'**********************************************
Dim Str_Wei1, Str_Wei2, Str_Num As String
Dim Str_Yuan, Str_Jiao, Str_Feng As String
Dim Str_RetYuan, Str_RetJiaoFeng As String
Dim Str_Ret1, Str_Ret2 As String
Dim Num_Yuan As Double
Dim Num_Jiao, Num_Feng As Single
Dim Num_i, I As Integer
Str_Wei1 = "拾佰仟"
Str_Wei2 = "万亿万亿万亿"
Str_Num = "壹贰叁肆伍陆柒捌玖"
'范围检测
If Num >= 10 ^ 14 Or Num <= -10 ^ 14 Then
DaXie2 = "＃数字超出转换范围！！！＃"
Exit Function
End If
'符号检测
If Num < 0 Then
Str_RetYuan = "（负）"
Else
Str_RetYuan = ""
End If
Num = Abs(Num)
'是否“四舍五入”
If JinWei Then
Num = Round(Num, 2) '“四舍五入”
Num_Yuan = Int(Num)
Num_Jiao = Int((Num - Num_Yuan) * 10)
Num_Feng = (Num - Num_Yuan) * 100 - Num_Jiao * 10
Else
Num_Yuan = Int(Num) '舍去最末位
Num_Jiao = Int((Num - Num_Yuan) * 10)
Num_Feng = Int((Num - Num_Yuan) * 100) - Num_Jiao * 10
End If
' ***** 整数部分转换
Str_Yuan = Trim(Str(Format(Num_Yuan, "###0")))
For I = Len(Str_Yuan) To 1 Step -1
Num_i = Val(Mid(Str_Yuan, Len(Str_Yuan) - I + 1, 1)) '第“I”位数
If Num_i <> 0 Then
'非“零”数字转换
Str_RetYuan = Str_RetYuan & Mid(Str_Num, Num_i, 1) ' “I”位数字转换
If (I - 1) Mod 4 <> 0 Then ' “I”位数位转换
Str_RetYuan = Str_RetYuan & Mid(Str_Wei1, (I - 1) Mod 4, 1)
Else
If (I - 1) \ 4 <> 0 Then
Str_RetYuan = Str_RetYuan & Mid(Str_Wei2, (I - 1) \ 4, 1)
End If
End If
Else
'“零”数字转换
If Right(Str_RetYuan, 1) <> "零" Then
'前一位非“零”：本位是“万亿”位加“万、亿”，否则其他加“零”
If (I - 1) Mod 4 <> 0 Then
Str_RetYuan = Str_RetYuan & "零"
Else
If (I - 1) \ 4 <> 0 Then
Str_RetYuan = Str_RetYuan & Mid(Str_Wei2, (I - 1) \ 4, 1)
End If
End If
Else
'前一位也为“零”：本位是“万亿”位加“万、亿”（并删除前面的“零”）；其他跳过此位
If (I - 1) Mod 4 = 0 Then
Str_RetYuan = Left(Str_RetYuan, Len(Str_RetYuan) - 1)
If (I - 1) \ 4 <> 0 Then
Str_RetYuan = Str_RetYuan & Mid(Str_Wei2, (I - 1) \ 4, 1)
End If
End If
End If
End If
Next I
If Num_Yuan = 0 Then
Str_RetYuan = ""
Else
Str_RetYuan = Str_RetYuan & "元"
End If
' ***** 小数部分转换
If Num_Jiao <> 0 Then
Str_RetJiaoFeng = Mid(Str_Num, Num_Jiao, 1) & "角"
If Num_Feng <> 0 Then
Str_RetJiaoFeng = Str_RetJiaoFeng & Mid(Str_Num, Num_Feng, 1) & "分"
Else
Str_RetJiaoFeng = Str_RetJiaoFeng & "整"
End If
Else
If Num_Feng <> 0 Then
If Num_Yuan <> 0 Then
Str_RetJiaoFeng = "零" & Mid(Str_Num, Num_Feng, 1) & "分"
Else
Str_RetJiaoFeng = Mid(Str_Num, Num_Feng, 1) & "分"
End If
Else
Str_RetJiaoFeng = Str_RetJiaoFeng & "整"
End If
End If
If Num < 0 Then '返回（包括符号）
DaXie2 = "（负）" & Str_RetYuan & Str_RetJiaoFeng
Else
DaXie2 = Str_RetYuan & Str_RetJiaoFeng
End If
End Function
热心网友
时间：2023-10-31 05:39
ctrl+1之后，打开对话框，数字／特殊／中文大小写数字，确定．你再重新输入一个数字，成功了吧？
热心网友
时间：2023-10-31 05:39
=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,,"零")),"零分","整")
自定义单元格格式为
[DBNum2][$-804]yyyy"年"mm"月"dd"日"
热心网友
时间：2023-10-31 05:40
如果你是用智能ABC输入法的话，我可以告诉你一个快速输入法：
在中文输入状态按住shift后输入I,然后在后面输入你想要的数字如1234567，空格就出现下面的数字！
壹贰叁肆伍陆柒捌玖
哈哈，一般人我不告诉他！
