当前位置：首页 > news >正文

Excel财务实战：零钞估算备用表制作全攻略

news 2026/3/26 22:54:05

💰 一、业务场景：工资零钞准备系统

1.1 实际问题

假设您是一名财务人员，每月需要为员工发放现金工资。银行取款时需要准确知道每种面额的钞票各需要多少张，以便快速清点并减少找零麻烦。

1.2 数据模板

🧮 二、核心公式深度解析

2.1 核心计算公式

=INT(($B2 - SUMPRODUCT($A$1:B$1, $A2:B2) + 1%%) / C$1)

2.2 分步拆解

步骤1：计算剩余金额

$B2 - SUMPRODUCT($A$1:B$1, $A2:B2)

$B2：当前员工的工资总额
SUMPRODUCT($A$1:B$1, $A2:B2)：计算已分配的面额总金额
$A$1:B$1：面额标签行（100元、50元...）
$A2:B2：已计算的各面额张数

步骤2：处理浮点数精度

+ 1%%

1%%= 0.0001（万分之一）
目的：避免因浮点数计算误差导致的向下取整错误
示例：实际9.9999元，不加修正会算成9张，实际需要10张

步骤3：计算张数

/ C$1

C$1：当前面额值（100、50、20...）
除法计算该面额需要多少张

步骤4：取整处理

INT(...)

向下取整，得到整数张数
不能出现"半张"钞票

📊 三、完整实现步骤

3.1 第一步：设置表头

A1: 姓名
B1: 工资
C1: 100
D1: 50
E1: 20
F1: 10
G1: 5
H1: 1
I1: 0.5
J1: 0.2
K1: 0.1

3.2 第二步：输入核心公式

在C2单元格输入：

=INT(($B2 - SUMPRODUCT($A$1:B$1, $A2:B2) + 1%%) / C$1)

3.3 第三步：填充公式

向右填充：从C2拖到K2
向下填充：从第2行拖到第14行

填充技巧：

使用Ctrl+R向右填充
使用Ctrl+D向下填充
或双击填充柄自动填充

3.4 第四步：计算总计

在C15单元格输入：

=SUM(C2:C14)

向右填充到K15

🔍 四、运行原理演示

4.1 示例计算：西门吹雪 8285.9元

计算过程：

1. 100元面额：INT(8285.9/100) = 82张，剩余285.9元
2. 50元面额：INT(285.9/50) = 5张，剩余35.9元
3. 20元面额：INT(35.9/20) = 1张，剩余15.9元
4. 10元面额：INT(15.9/10) = 1张，剩余5.9元
5. 5元面额：INT(5.9/5) = 1张，剩余0.9元
6. 1元面额：INT(0.9/1) = 0张，剩余0.9元
7. 0.5元面额：INT(0.9/0.5) = 1张，剩余0.4元
8. 0.2元面额：INT(0.4/0.2) = 2张，剩余0元
9. 0.1元面额：INT(0/0.1) = 0张

验证：

82×100 + 5×50 + 1×20 + 1×10 + 1×5 + 1×0.5 + 2×0.2 = 8285.9 ✓

🎨 五、表格美化与优化

5.1 条件格式设置

// 高亮非零单元格
选择区域：C2:K14
条件格式 → 新建规则 → 使用公式
=AND(C2>0, NOT(ISBLANK($B2)))
设置填充色：浅绿色
// 总计行特殊格式
选择C15:K15
设置：加粗、双下边框、浅蓝色填充

5.2 添加数据验证

// B列工资输入验证
数据验证 → 自定义
公式：=AND(B2>=0, B2<=99999.99)
错误提示："请输入0-99999.99之间的金额"

⚡ 六、高级扩展功能

6.1 动态面额系统

// 在L1:M1添加可配置面额
L1: "自定义面额"
M1: 2（可以修改为其他面值）
// 在L2输入公式
=IF($M$1>0, INT(($B2 - SUMPRODUCT($A$1:K$1, $A2:K2) + 1%%) / $M$1), "")

6.2 金额验证功能

// 在L2添加验证公式
=IF(ABS($B2 - SUMPRODUCT($C$1:K$1, C2:K2)) < 0.01, "✓", "✗ 误差：" & TEXT($B2 - SUMPRODUCT($C$1:K$1, C2:K2), "0.00"))

6.3 自动生成银行取款单

// 汇总表
=LET(
denominations, C1:K1,
counts, C15:K15,
total, SUMPRODUCT(denominations, counts),
"需准备现金：" & TEXT(total, "#,##0.00") & "元" & CHAR(10) &
TEXTJOIN("、", TRUE,
FILTER(denominations & "元×" & counts & "张", counts>0)
)
)

🛠️ 七、常见问题解决

问题1：浮点数精度误差

症状：最后一分钱对不上
解决：公式中的+1%%就是为此设计

问题2：面额顺序错误

// 正确顺序：从大到小排列
// 如果顺序错误，在C1:K1重新排列
// 建议顺序：100, 50, 20, 10, 5, 1, 0.5, 0.2, 0.1

问题3：公式引用错误

// 检查绝对引用和相对引用
$B2 // 列绝对，行相对
C$1 // 列相对，行绝对
$A$1:B$1 // 混合引用

📱 八、移动端适配方案

8.1 简化版本（适合手机查看）

// 创建汇总视图
=LET(
name, A2,
salary, B2,
breakdown, MAP({100,50,20,10,5,1,0.5,0.2,0.1},
LAMBDA(d, INT((salary - SUMPRODUCT(
OFFSET($A$1,0,1):OFFSET($A$1,0,COLUMN()-2),
OFFSET(A2,0,1):OFFSET(A2,0,COLUMN()-2)
) + 0.0001) / d))),
name & "：" & salary & "元 = " &
TEXTJOIN("+", TRUE, FILTER({100,50,20,10,5,1,0.5,0.2,0.1} & "×" & breakdown, breakdown>0))
)

🔄 九、VBA自动化方案

9.1 一键生成零钞表

Sub GenerateChangeTable()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("零钞表")

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

' 应用公式
ws.Range("C2:K" & lastRow).FormulaR1C1 = _
"=INT((RC[-1]-SUMPRODUCT(R1C1:RC[-1],R[-1]C1:RC[-1])+1%%)/R1C)"

' 计算总计
ws.Range("C" & lastRow + 1 & ":K" & lastRow + 1).FormulaR1C1 = _
"=SUM(R[-" & lastRow - 1 & "]C:R[-1]C)"

' 美化
With ws.Range("C" & lastRow + 1 & ":K" & lastRow + 1)
.Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlDouble
End With
End Sub