例如输入金额456,转换成大写时对应的万位、千位为“*”,角、分为“零”,求解,期待中……
在A2里输入=TEXT(IF(ROUNDDOWN(J2,-6)>0,(ROUNDDOWN(J2,-6)-ROUNDDOWN(J2,-7))/1000000,""),"[dbnum2]G/通用格式;;")
在B2里输入=TEXT(IF(ROUNDDOWN(J2,-5)>0,(ROUNDDOWN(J2,-5)-ROUNDDOWN(J2,-6))/100000,""),"[dbnum2]G/通用格式;;")
在C2里输入=TEXT(IF(ROUNDDOWN(J2,-4)>0,(ROUNDDOWN(J2,-4)-ROUNDDOWN(J2,-5)/10000,""),"[dbnum2]G/通用格式;;")
在D2里输入=TEXT(IF(ROUNDDOWN(J2,-3)>0,(ROUNDDOWN(J2,-3)-ROUNDDOWN(J2,-4)/1000,""),"[dbnum2]G/通用格式;;")
在E2里输入=TEXT(IF(ROUNDDOWN(J2,-2)>0,(ROUNDDOWN(J2,-2)-ROUNDDOWN(J2,-3)/100,""),"[dbnum2]G/通用格式;;")
在F2里输入=TEXT(IF(ROUNDDOWN(J2,-1)>0,(ROUNDDOWN(J2,-1)-ROUNDDOWN(J2,-2)/10,""),"[dbnum2]G/通用格式;;")
在G2里输入=TEXT(IF(ROUNDDOWN(J2,0)>0,(ROUNDDOWN(J2,0)-ROUNDDOWN(J2,-1),""),"[dbnum2]G/通用格式;;")
在H2里输入=TEXT((ROUNDDOWN(J2,1)-ROUNDDOWN(J2,0))*10,"[dbnum2]0")
在I2里输入=TEXT((ROUNDDOWN(J2,2)-ROUNDDOWN(J2,1))*100,"[dbnum2]0")
随后可以把A2的公式下拉,A列(A3、A4等)就自动填写了有效公式。对B2、C2、D2、E2、F2、G2、H2、I2进行同样的下拉操作。
在B2里输入=TEXT(IF(ROUNDDOWN(J2,-5)>0,(ROUNDDOWN(J2,-5)-ROUNDDOWN(J2,-6))/100000,""),"[dbnum2]G/通用格式;;")
在C2里输入=TEXT(IF(ROUNDDOWN(J2,-4)>0,(ROUNDDOWN(J2,-4)-ROUNDDOWN(J2,-5)/10000,""),"[dbnum2]G/通用格式;;")
在D2里输入=TEXT(IF(ROUNDDOWN(J2,-3)>0,(ROUNDDOWN(J2,-3)-ROUNDDOWN(J2,-4)/1000,""),"[dbnum2]G/通用格式;;")
在E2里输入=TEXT(IF(ROUNDDOWN(J2,-2)>0,(ROUNDDOWN(J2,-2)-ROUNDDOWN(J2,-3)/100,""),"[dbnum2]G/通用格式;;")
在F2里输入=TEXT(IF(ROUNDDOWN(J2,-1)>0,(ROUNDDOWN(J2,-1)-ROUNDDOWN(J2,-2)/10,""),"[dbnum2]G/通用格式;;")
在G2里输入=TEXT(IF(ROUNDDOWN(J2,0)>0,(ROUNDDOWN(J2,0)-ROUNDDOWN(J2,-1),""),"[dbnum2]G/通用格式;;")
在H2里输入=TEXT((ROUNDDOWN(J2,1)-ROUNDDOWN(J2,0))*10,"[dbnum2]0")
在I2里输入=TEXT((ROUNDDOWN(J2,2)-ROUNDDOWN(J2,1))*100,"[dbnum2]0")
随后可以把A2的公式下拉,A列(A3、A4等)就自动填写了有效公式。对B2、C2、D2、E2、F2、G2、H2、I2进行同样的下拉操作。
温馨提示:答案为网友推荐,仅供参考
第1个回答 2012-09-12
利用查找替换
第2个回答 2012-09-12
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
cr = Target.Row
cc = Target.Column
If cr < 2 Or cc <> 10 Then End
dx = Array("零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖")
sz = Int(Cells(cr - 1, cc).Value * 100)
Target.Value = ""
For i = 1 To 9
ys = sz Mod 10
If sz = 0 Then
vv = "*"
Else
vv = dx(ys)
End If
Cells(cr - 1, 10 - i).Value = vv
sz = Int(sz / 10)
Next
End Sub
cr = Target.Row
cc = Target.Column
If cr < 2 Or cc <> 10 Then End
dx = Array("零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖")
sz = Int(Cells(cr - 1, cc).Value * 100)
Target.Value = ""
For i = 1 To 9
ys = sz Mod 10
If sz = 0 Then
vv = "*"
Else
vv = dx(ys)
End If
Cells(cr - 1, 10 - i).Value = vv
sz = Int(sz / 10)
Next
End Sub