excel如何算元角

1.excel带元角分的计算

如果你要转换的金额在A1，则在A2输入：=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，，"零"）），"零分"，"整"） 另外还有一个完美一点的公式，考虑了很多特殊情况的，花了我一个小时去想去试的哦：=IF(A1=0,"",IF(MOD(A1,1),IF(A1>=1,NUMBERSTRING(INT(A1),2）&"元"，""）&IF(RIGHT(FIXED(A1,2),1)="0",NUMBERSTRING(MOD(A1*10,10),2）&"角"，NUMBERSTRING(MOD(INT(A1*10),10),2)&IF(MOD(INT(A1*10),10)=0，""，"角"）&NUMBERSTRING(MOD(A1*100,10),2）&"分"），NUMBERSTRING(INT(A1),2）&"元整"））。

2.excel带元角分的计算

如果你要转换的金额在A1，则在A2输入：

=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，，"零"）），"零分"，"整"）

另外还有一个完美一点的公式，考虑了很多特殊情况的，花了我一个小时去想去试的哦：

=IF(A1=0,"",IF(MOD(A1,1),IF(A1>=1,NUMBERSTRING(INT(A1),2）&"元"，""）&IF(RIGHT(FIXED(A1,2),1)="0",NUMBERSTRING(MOD(A1*10,10),2）&"角"，NUMBERSTRING(MOD(INT(A1*10),10),2)&IF(MOD(INT(A1*10),10)=0，""，"角"）&NUMBERSTRING(MOD(A1*100,10),2）&"分"），NUMBERSTRING(INT(A1),2）&"元整"））

3.在Office Excel中人民币大写无元角分如何解决

a、单击“工具”--“宏”--“Visual Basic 编辑器” b、单击“Visual Basic 编辑器”菜单栏“运行”--“运行宏” c、输入宏名，如 "zh" ，单击“创建” d、键入以下代码： 'a1为数字小写单元格，a2为中文大写单元格 Const strN = "零壹贰叁肆伍陆柒捌玖" Const strG = "拾佰仟万亿" Const intN = "0123456789" Dim Zero_Count As Long '读零计数 Private Function GetN(ByVal N As Long) As String GetN = Mid(strN, N + 1, 1) End Function Private Function GetG(ByVal G As Long) As String Select Case G Case 1 GetG = "" Case 2, 6 GetG = Mid(strG, 1, 1) Case 3, 7 GetG = Mid(strG, 2, 1) Case 4, 8 GetG = Mid(strG, 3, 1) Case 5 GetG = Mid(strG, 4, 1) Case 9 GetG = Mid(strG, 5, 1) End Select End Function Private Function ReadLongNumber(ByVal LongX As String) As String Dim numberx As String Dim l As Long '长度 Dim m As Long '多余位数 Dim c As Long '循环次数 Dim i As Long, j As Long '标志 Dim CurN As String numberx = LongX l = Len(numberx) Do Until l < 9 m = l Mod 8 If m = 0 Then m = 8 CurN = Left(numberx, m) If ReadIntNumber(CurN) <> "零" Then ReadLongNumber = ReadLongNumber & ReadIntNumber(CurN) & "亿" Else ReadLongNumber = ReadLongNumber & "亿" End If numberx = Right(numberx, Len(numberx) - m) l = Len(numberx) Loop ReadLongNumber = ReadLongNumber & ReadIntNumber(numberx) If Len(ReadLongNumber) > 2 And Right(ReadLongNumber, 1) = "零" Then '去尾 零 ReadLongNumber = Left(ReadLongNumber, Len(ReadLongNumber) - 1) End If If Mid(ReadLongNumber, 1, 2) = "壹拾" Then '掐头 壹拾 ReadLongNumber = Right(ReadLongNumber, Len(ReadLongNumber) - 1) Mid(ReadLongNumber, 1, 1) = "拾" End If Zero_Count = 0 End Function Private Function ReadIntNumber(ByVal numberx As String) As String Dim l As Long '长度 Dim m As Long '多余位数 Dim c As Long '循环次数 Dim i As Long, j As Long '标志 Dim CurN As String If Val(numberx) = 0 Then ReadIntNumber = GetN(0): Exit Function l = Len(numberx) If l > 8 Then Exit Function m = l Mod 9 CurN = Right(numberx, m) For i = Len(CurN) To 1 Step -1 If GetN(Int(Mid(CurN, i, 1)）） = "零" And Zero_Count = 1 Then If GetG(Len(CurN) - i + 1） = "万" Then If (Not (Val(Left(CurN, Len(CurN) - 5)) = 0)) Then ReadIntNumber = GetG(Len(CurN) - i + 1) & ReadIntNumber End If End If Else If GetN(Int(Mid(CurN, i, 1)）） = "零" Then ReadIntNumber = GetN(Int(Mid(CurN, i, 1))) & ReadIntNumber If GetG(Len(CurN) - i + 1） = "万" Then If (Not (Val(Left(CurN, Len(CurN) - 5)) = 0)) Then ReadIntNumber = GetG(Len(CurN) - i + 1) & ReadIntNumber End If Zero_Count = 1 Else ReadIntNumber = GetG(Len(CurN) - i + 1) & ReadIntNumber ReadIntNumber = GetN(Int(Mid(CurN, i, 1))) & ReadIntNumber Zero_Count = 0 End If End If Next i 'Loop If Len(ReadIntNumber) > 2 And Right(ReadIntNumber, 1) = "零" Then '去尾 零 ReadIntNumber = Left(ReadIntNumber, Len(ReadIntNumber) - 1) End If If Mid(ReadIntNumber, 1, 2) = "壹拾" Then '掐头 壹拾 ReadIntNumber = Right(ReadIntNumber, Len(ReadIntNumber) - 1) Mid(ReadIntNumber, 1, 1) = "拾" End If End Function Public Function ReadNumber(ByVal numberx As String) As String Dim LongX As String Dim PointX As String Dim LongLong As Long Dim bFS As Boolean '负数 If Not IsNumeric(numberx) Then ReadNumber = "" Exit Function End If If CDbl(numberx) < 0 Then numberx = -numberx bFS = True End If numberx = CStr(Format(numberx, "General Number")) LongLong = InStr(1, numberx, ".") If LongLong <> 0 Then ReadNumber = ReadLongNumber(Left(numberx, LongLong - 1)) ReadNumber = ReadNumber & "点" & ReadSmallNumber(Right(numberx, Len(numberx) - LongLong)) Else ReadNumber = ReadLongNumber(numberx) End If If bFS = True Then ReadNumber = "负" & ReadNumber End If End Function Private Function ReadSmallNumber(SmallNumber As String) As String Dim i As Long For i = 1 To Len(SmallNumber) ReadSmallNumber = ReadSmallNumber & GetN(Mid(SmallNumber, i, 1)) Next i End Function Private Function ReadSmallNumberToRMB(SmallNumber As String) As String ReadSmallNumberToRMB = GetN(Mid(SmallNumber, 1, 1)） & "角" & GetN(Mid(SmallNumber, 2, 1)） & "分" End Function Public Function ReadNumberToRMB(ByVal numberx As String) As String Dim LongX As String Dim PointX As String Dim LongLong As Long Dim bFS As Boolean '负数 If Not IsNumeric(numberx) Then ReadNumberToRMB = "" Exit Function End If If CDbl(numberx) < 0 Then numberx = -numberx bFS = True End If numberx = CStr(For。