EXCEL里快捷的把1000自动转换为壹万元整
答案:5 悬赏:50 手机版
解决时间 2021-11-16 11:13
- 提问者网友：欺烟
- 2021-11-15 12:34
EXCEL里快捷的把1000自动转换为壹万元整
最佳答案
- 五星知识达人网友：山有枢
- 2021-11-15 13:10
选中你需要结果的单元格，点击：设置单元格格式——自定义——输入文字以下 （[DBNum2]G/通用格式"元""整"） 输入文字不包括小括号
全部回答
- 1楼网友：怀裏藏嬌
- 2021-11-15 18:07
操作步骤如下：
Alt + F11---------工具栏---------插入模块---------输入以下代码
Function CaRmb(NumberArg As Double, Optional O As Boolean = True) As String 'RR '转为大写金额,最大处理到万亿共13位
Const STRSZ = "壹贰叁肆伍陆柒捌玖零"
Const STRDW = "万仟佰拾亿仟佰拾万仟佰拾元角分整"
Dim caTMP As String
Dim NumberInt As String
Dim L, N, i As Integer
Dim strN
If Int(NumberArg * 100) = 0 Then
caTMP = "零"
ElseIf Abs(NumberArg) < 10 ^ 13 Then
caTMP = IIf(NumberArg < 0, "负", "")
NumberInt = Abs(Format(NumberArg * 100, "#"))
L = Len(NumberInt)
For i = 1 To L
strN = Mid(NumberInt, i, 1)
If strN <> 0 Then
If O And (L - i + 1 = 2 Or L - i + 1 = 6 Or L - i + 1 = 10) And i > 1 Then
caTMP = IIf(Mid(NumberInt, i - 1, 1) = "0", caTMP & "零", caTMP)
End If
caTMP = caTMP + Mid(STRSZ, strN, 1) + Mid(STRDW, 15 - L + i, 1)
Else '零的处理
Select Case L - i - 1 '正在处理的位数,0为角，-1为分
Case Is > 9, 6 To 8, 2 To 4
caTMP = IIf(Mid(NumberInt, i + 1, 1) = "0", caTMP, caTMP + Right(STRSZ, 1))
Case 9 '亿位
caTMP = caTMP + Mid(STRDW, 15 - L + i, 1)
Case 1 '元位
caTMP = caTMP + Mid(STRDW, 15 - L + i, 1)
Case 5 '万位
If L >= 11 Then '上亿
caTMP = IIf(Mid(NumberInt, L - 9, 3) = "000", caTMP, caTMP + Mid(STRDW, 15 - L + i, 1))
Else
caTMP = caTMP + Mid(STRDW, 15 - L + i, 1)
End If
Case 0 '如果有无分则加“整”字
caTMP = IIf(Mid(NumberInt, i + 1, 1) = "0", caTMP + Right(STRDW, 1), caTMP + Mid(STRSZ, 10, 1))
Case -1
caTMP = IIf(Mid(NumberInt, i - 1, 1) <> "0", caTMP & "整", caTMP)
End Select
End If
Next
Else
MsgBox "转换数 " + Format(NumberArg, "#,###") + " 的绝对值太大，几乎不能是金额！" + _
Chr(13) + Chr(13) + "请检查您的数据是否正确。", vbExclamation + vbOKOnly, "转换大写金额"
caTMP = ""
End If
CaRmb = caTMP
End Function
显示 大写 数额 的 单元格 输入 公式=CaRmb(有小写数值的单元格)
Alt + F11---------工具栏---------插入模块---------输入以下代码
Function CaRmb(NumberArg As Double, Optional O As Boolean = True) As String 'RR '转为大写金额,最大处理到万亿共13位
Const STRSZ = "壹贰叁肆伍陆柒捌玖零"
Const STRDW = "万仟佰拾亿仟佰拾万仟佰拾元角分整"
Dim caTMP As String
Dim NumberInt As String
Dim L, N, i As Integer
Dim strN
If Int(NumberArg * 100) = 0 Then
caTMP = "零"
ElseIf Abs(NumberArg) < 10 ^ 13 Then
caTMP = IIf(NumberArg < 0, "负", "")
NumberInt = Abs(Format(NumberArg * 100, "#"))
L = Len(NumberInt)
For i = 1 To L
strN = Mid(NumberInt, i, 1)
If strN <> 0 Then
If O And (L - i + 1 = 2 Or L - i + 1 = 6 Or L - i + 1 = 10) And i > 1 Then
caTMP = IIf(Mid(NumberInt, i - 1, 1) = "0", caTMP & "零", caTMP)
End If
caTMP = caTMP + Mid(STRSZ, strN, 1) + Mid(STRDW, 15 - L + i, 1)
Else '零的处理
Select Case L - i - 1 '正在处理的位数,0为角，-1为分
Case Is > 9, 6 To 8, 2 To 4
caTMP = IIf(Mid(NumberInt, i + 1, 1) = "0", caTMP, caTMP + Right(STRSZ, 1))
Case 9 '亿位
caTMP = caTMP + Mid(STRDW, 15 - L + i, 1)
Case 1 '元位
caTMP = caTMP + Mid(STRDW, 15 - L + i, 1)
Case 5 '万位
If L >= 11 Then '上亿
caTMP = IIf(Mid(NumberInt, L - 9, 3) = "000", caTMP, caTMP + Mid(STRDW, 15 - L + i, 1))
Else
caTMP = caTMP + Mid(STRDW, 15 - L + i, 1)
End If
Case 0 '如果有无分则加“整”字
caTMP = IIf(Mid(NumberInt, i + 1, 1) = "0", caTMP + Right(STRDW, 1), caTMP + Mid(STRSZ, 10, 1))
Case -1
caTMP = IIf(Mid(NumberInt, i - 1, 1) <> "0", caTMP & "整", caTMP)
End Select
End If
Next
Else
MsgBox "转换数 " + Format(NumberArg, "#,###") + " 的绝对值太大，几乎不能是金额！" + _
Chr(13) + Chr(13) + "请检查您的数据是否正确。", vbExclamation + vbOKOnly, "转换大写金额"
caTMP = ""
End If
CaRmb = caTMP
End Function
显示 大写 数额 的 单元格 输入 公式=CaRmb(有小写数值的单元格)
- 2楼网友：不甚了了
- 2021-11-15 16:30
直接用替换，把10000替换成壹万元整追问怎么替换？追答菜单，编辑，替换，上面输入10000，下面输入壹万元整
- 3楼网友：底特律间谍
- 2021-11-15 15:13
10000所在的单元格中，右击－储存单元格－数值－自订，在自订中可以看到类型，你自己输入壹万元整 按确定，就变成壹万元整，其他的用格式刷，刷一下就好了，试一下。
有问题我在截图给你。。。。。追问是这样的追答那你要自订里将壹万捌仟壹百捌拾元伍角整输进去，其实你的数值会变你将壹到十的大写都写进去。
有问题我在截图给你。。。。。追问是这样的追答那你要自订里将壹万捌仟壹百捌拾元伍角整输进去，其实你的数值会变你将壹到十的大写都写进去。
- 4楼网友：西风乍起
- 2021-11-15 14:45
转成壹仟圆整。
假设：1000在A1,任意单元格输入
=IF(A1=0,"",IF(ABS(A1)<1,"",TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元")&IF(RIGHt(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整"),IF(ABS(A1)<0.1,"",TEXT(RIGHt(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHt(TRUNC(A1*10))*1=0,"","角")&IF(RIGHt(TRUNC(A1*100))*1=0,"整",TEXT(RIGHt(TRUNC(A1*100)),"[dbnum2]")&"分")))
ctrl+shift+enter结束
假设：1000在A1,任意单元格输入
=IF(A1=0,"",IF(ABS(A1)<1,"",TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元")&IF(RIGHt(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整"),IF(ABS(A1)<0.1,"",TEXT(RIGHt(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHt(TRUNC(A1*10))*1=0,"","角")&IF(RIGHt(TRUNC(A1*100))*1=0,"整",TEXT(RIGHt(TRUNC(A1*100)),"[dbnum2]")&"分")))
ctrl+shift+enter结束
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯