欢迎来到铁虎网-专注为站长!

Excel基础

当前位置:铁虎网 > Excel之家 > Excel基础 >

五种人民币大写转换

时间:2019-05-11|栏目:Excel基础|点击:

NUMBERSTRING函数
=IF(A2=0,"零",IF(AND(A2<1,A2>0.09,A2*10-INT(A2*10)=0),NUMBERSTRING((INT(A2*10)/10-INT(A2))*10,2)&"角整",IF(AND(A2<1,A2>0.09,A2*10-INT(A2*10)<>0),NUMBERSTRING((INT(A2*10)/10-INT(A2))*10,2)&"角"&NUMBERSTRING((INT(A2*100)/100-INT(A2*10)/10)*100,2)&"分",IF(A2<=0.09,NUMBERSTRING((INT(A2*100)/100-INT(A2*10)/10)*100,2)&"分",IF(A2-INT(A2)=0,NUMBERSTRING(A2,2)&"元整",IF(AND(A2-INT(A2*10)/10=0,A2>1),NUMBERSTRING(INT(A2),2)&"元"&NUMBERSTRING((INT(A2*10)/10-INT(A2))*10,2)&"角整",)))))&IF(A2<0,"无效数值",IF(AND(A2-INT(A2*10)/10>0,INT(A2*10)-INT(A2)*10>0,A2>1),NUMBERSTRING(INT(A2),2)&"元"&NUMBERSTRING((INT(A2*10)/10-INT(A2))*10,2)&"角"&NUMBERSTRING((INT(A2*100)/100-INT(A2*10)/10)*100,2)&"分",IF(AND(A2-INT(A2*10)/10>0,INT(A2*10)-INT(A2)*10=0,A2>1),NUMBERSTRING(INT(A2),2)&"元"&"零"&NUMBERSTRING((INT(A2*100)/100-INT(A2*10)/10)*100,2)&"分",""))))

TEXT函数及[dbnum2]格式
1.=IF(ROUND(A2,2)<0,"无效数值",IF(ROUND(A2,2)=0,"零",IF(ROUND(A2,2)<1,"",TEXT(INT(ROUND(A2,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A2,2)*10)-INT(ROUND(A2,2))*10=0,IF(INT(ROUND(A2,2))*(INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A2,2)*10)-INT(ROUND(A2,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10)=0,"整",TEXT((INT(ROUND(A2,2)*100)-INT(ROUND(A2,2)*10)*10),"[dbnum2]")&"分")))

SUBSTITUTE、TEXT函数及[dbnum2]格式
=SUBSTITUTE(SUBSTITUTE(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]")&"分",IF(ROUND(A2,2)=0,"","整")),"零元零",""),"零元","零")

rmbdx自定义函数
用法: =rmbdx(A2)
Function rmbdx(value, Optional m = 0)
'支持负数,支持小数点后的第三位数是否进行四舍五入处理
'默认参数为0,即不将小数点后的第三位数进行四舍五入处理
'2002-10-11--2002-10-13
On Error Resume Next
Dim a
Dim jf As String   '定义角分位
Dim j '定义角位
Dim f '定义分位
If value < 0 Then '处理正负数的情况
a = "负"
Else
a = ""
End If

If IsNumeric(value) = False Then '判断待转换的value是否为数值
rmbdx = "需转换的内容非数值"
Else
value = Abs(CCur(value))
'当参数m不输入(默认为0)或为0时,小数点后的第三数不进行四舍五入处理
'当参数m为1或其它数值时,小数点后的第三数进行四舍五入处理
If m = 0 Then
   jf = Fix((value - Fix(value)) * 100)
   value = Fix(value) + jf / 100
   Else '厘位进行四舍五入实践很少用到,但还是要照顾到
     value = Application.WorksheetFunction.Round(value, 2) '-->这句最关键!只用round有bug
     jf = Round((value - Fix(value)) * 100, 0)
End If
If value = 0 Or value = "" Then '当待转换数值为0或空时,不进行转换
rmbdx = ""
Else
strrmbdx = Application.WorksheetFunction.Text(Int(value), "[DBNum2]") & "元" '转换整数位
   If Int(value) = 0 Then
   strrmbdx = ""
   End If
If Int(value) <> value Then
   If jf > 9 Then '判断小数位
     j = Left(jf, 1)
     f = Right(jf, 1)
     Else
     j = 0
     f = jf
   End If
  If j <> 0 And f <> 0 Then '角分位都有时
    jf = Application.WorksheetFunction.Text(j, "[DBNum2]") & "角" _
    & Application.WorksheetFunction.Text(f, "[DBNum2]") & "分"
     Else
      '处理出现零几分的情况
      If Int(value) = 0 And j = 0 And f <> 0 Then
         jf = Application.WorksheetFunction.Text(f, "[DBNum2]") & "分"
         Else
           If j = 0 Then '有分无角时
            jf = "零" & Application.WorksheetFunction.Text(f, "[DBNum2]") & "分"
              Else
               If f = 0 Then '有角无分时
                jf = Application.WorksheetFunction.Text(j, "[DBNum2]") & "角整"
               End If
           End If
      End If
  End If
  strrmbdx = strrmbdx & jf '组装
Else
     strrmbdx = strrmbdx & "整"
End If
rmbdx = a & strrmbdx '最后成型了,各位MM满意了吧
End If
End If
End Function

dx自定义函数
用法: =dx(A2)
Function DX(n)
    DX = Replace(Application.Text(Round(n + 0.00000001, 2), "[DBnum2]"), ".", "元")
    DX = IIf(Left(Right(DX, 3), 1) = "元", Left(DX, Len(DX) - 1) & "角" & Right(DX, 1) & "分", IIf(Left(Right(DX, 2), 1) = "元", DX & "角整", IIf(DX = "零", "", DX & "元整")))
    DX = Replace(Replace(Replace(Replace(DX, "零元零角", ""), "零元", ""), "零角", "零"), "-", "负")
End Function

栏    目:Excel基础

上一篇:用Excel批量替换文本列

下一篇:excel弹出“不能清除剪贴板”的提示框,解决方法

本文标题:五种人民币大写转换

本文地址:http://www.tiehoo.com//Excel/jichu/2019/0511/2230.html

您可能感兴趣的文章

关于我们 | 寻求报道 | 投稿须知 | 商务合作 | 版权申明 | 联系我们

重要申明:本站所有的文章、图片、评论等,均由网友发表或上传并维护或收集自网络,属个人行为,与本站立场无关。

如果侵犯了您的权利,请与我们联系,我们将在24小时内进行处理、任何非本站因素导致的法律后果,本站均不负任何责任。

联系QQ:149650349 | 邮箱:wx#tiehoo.com(#换成@)

Copyright © 2008-2019 铁虎网(模站无忧) 版权所有

陕ICP备16002307号-2