EXCEL里增加人民币大写函数

在财务工作中，可能会碰到把数字转换成人民币大写金额的情况，比如拨款单的填写。EXCEL并没有现成的转换函数，用函数组合也可以实现转换，就是公式比较长，不太方便使用：

=LET(_n,ROUND(ABS(A1), 2),

_neg,IF(A1 < 0, "负", ""),

_int,INT(_n),

_frac, ROUND((_n - _int) * 100, 0),

_j,INT(_frac / 10),

_f,MOD(_frac, 10),

_iStr, IF(_int = 0, "零", TEXT(_int, "[DBNum2]")),

_jStr, IF(_j = 0, IF(_f > 0, "零", ""), TEXT(_j, "[DBNum2]") & "角"),

_fStr, IF(_f = 0, "", TEXT(_f, "[DBNum2]") & "分"),

_tail, IF(AND(_j = 0, _f = 0), "整", ""),

_neg & _iStr & "元" & _jStr & _fStr & _tail)

可以通过用VBA公开函数包装，实现全局可用，跟 Excel 内置函数一模一样的使用体验，任何文件打开就能用：

第一步：在加载宏中写 VBA

Alt + F11 → 在你的加载宏项目中插入 → 模块，粘贴：

Function RMBDX(num As Double) As String

Dim DX

DX = Array("零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖")

Dim DW1

DW1 = Array("", "拾", "佰", "仟")

Dim DW2

DW2 = Array("", "万", "亿")

Dim n As Double

Dim sInt As String, sDec As String

Dim r As String

Dim i As Long, d As Long, pos As Long, seg As Long

Dim zero As Boolean

Dim jiao As Long, fen As Long

If num < 0 Then

RMBDX = "负" & RMBDX(Abs(num))

Exit Function

End If

If num = 0 Then

RMBDX = "零元整"

Exit Function

End If

n = Round(num, 2)

sInt = CStr(Int(n))

sDec = Format((n - Int(n)) * 100, "00")

r = ""

zero = False

Dim totalLen As Long

totalLen = Len(sInt)

For i = 1 To totalLen

d = CLng(Mid(sInt, i, 1))

Dim rightLen As Long

rightLen = totalLen - i

pos = rightLen Mod 4

seg = rightLen \ 4

If d = 0 Then

zero = True

If pos = 0 And seg > 0 Then

r = r & DW2(seg)

zero = False

End If

Else

If zero Then r = r & "零"

zero = False

r = r & DX(d) & DW1(pos)

If pos = 0 And seg > 0 Then r = r & DW2(seg)

End If

Next i

r = r & "元"

jiao = CLng(Left(sDec, 1))

fen = CLng(Right(sDec, 1))

If jiao = 0 And fen = 0 Then

r = r & "整"

ElseIf jiao = 0 Then

r = r & "零" & DX(fen) & "分"

ElseIf fen = 0 Then

r = r & DX(jiao) & "角"' ← 修正：去掉"整"

Else

r = r & DX(jiao) & "角" & DX(fen) & "分"

End If

RMBDX = r

End Function

第二步：保存并设置加载宏

1. 在 VBA 编辑器中关闭代码窗口

2. 文件 → 另存为 → 选择「Excel加载宏 (*.xlam)」

保存位置保持默认的 AddIns 文件夹（C:\Users\你的用户名\AppData\Roaming\Microsoft\AddIns）

3. 文件 → 选项 → 加载宏 → 转到 → 勾选你的加载宏 → 确定

第三步：任意文件中直接使用

=RMBDX(A1)

=RMBDX(1234.56)

跟 SUM、XLOOKUP 一样直接调用。