💰 一、业务场景：工资零钞准备系统
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)
步骤2：处理浮点数精度
+ 1%%
步骤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:BA$1,0,1):OFFSET($AB2 * VLOOKUP($A2, 门店配置表!$A$2:$B$10, 2, FALSE) - SUMPRODUCT($A$1:B$1, $A2:B2) + 1%%) / C$1)
10.2 活动经费分配
// 考虑最小面额限制（如：只发整元） =IF(C$1>=1, INT(($B2 - SUMPRODUCT($A$1:B$1, $A2:B2) + 1%%) / C$1), 0)
10.3 外币兑换计算
// 添加汇率转换 =INT(($B2 * 汇率!$B$2 - SUMPRODUCT($A$1:B$1, $A2:B2) + 1%%) / C$1)
🎯 十一、最佳实践建议
11.1 模板维护
定期检查：每月更新前检查公式引用
面额更新：根据央行发行新币及时调整
备份保存：保留历史记录以便审计
11.2 效率优化
使用表格：将区域转为Excel表格（Ctrl+T）
名称管理：为常用区域定义名称
避免易失函数：减少OFFSET、INDIRECT使用
📝 十二、总结
关键技巧回顾：
SUMPRODUCT的妙用：动态计算已分配金额
浮点数精度处理：+1%%解决计算误差
混合引用技巧：正确使用$符号
INT函数应用：确保张数为整数
业务价值：
✅ 提高效率：自动计算，减少人工错误 ✅ 方便取款：银行取款时一目了然 ✅ 便于对账：清晰的面额分布记录 ✅ 灵活扩展：可适配各种面额体系
学习收获：
掌握财务计算中的取整技巧
理解混合引用在实际中的应用
学会处理浮点数精度问题
能够创建专业级的财务工具
