📌 阶段四 · 进阶函数 · 第17讲 数学函数 · 约 40 分钟

让账目分毫不差：ROUND 把金额锁定到分，SUMPRODUCT 一步算出总销售额与库存价值

学习目标

  • 分清 ROUND/ROUNDUP/ROUNDDOWN/INT 各自的舍入脾气
  • 会用 MOD 求余数，判断奇偶、拆装箱零头
  • 会用 ROUND 把金额统一锁定到分，杜绝合计对不上
  • 会用 SUMPRODUCT 一步算出总销售额与库存总值
  • 认识 ROW/COLUMN 与「按位置找规律」的引用套路

知识点讲解

ROUND 一家：四舍五入、强制进位、强制舍去

=ROUND(数字,位数)：参数 1 是对谁舍入，参数 2 是舍入到几位——大于 0 舍入到指定小数位，等于 0 舍入到整数，小于 0 舍入到小数点左边（十位、百位）。=ROUND(2.15,1)=2.2，=ROUND(21.5,-1)=20。

=ROUNDUP 无条件向上进位：=ROUNDUP(3.2,0)=4；对负数是往大的值进位，=ROUNDUP(-3.249,1)=-3.2。

=ROUNDDOWN 无条件舍去：=ROUNDDOWN(3.14159,3)=3.141，=ROUNDDOWN(31415.9,-2)=31400。

进销存的核心用法一句话：金额=数量×单价后必须 =ROUND(...,2) 锁定到分。

=ROUND(G2*H2,2)

数量乘单价后四舍五入保留 2 位——每一笔金额都分毫不差，合计才能对得上。

=ROUNDUP(3.2,0)

无条件进位到整数得 4：算「至少要几个包装箱」时用。

=ROUNDDOWN(3.14159,3)

保留 3 位小数、多余的直接舍去，得 3.141。

💡 提示:

  • 把单元格格式设成两位小数只是「看起来」舍了，真舍入必须用 ROUND——这是对账差错的高频元凶
  • 位数写负数处理整数位：=ROUND(21.5,-1)=20

INT：最简单的取整

=INT(数字) 把数值向下取整到最接近的整数，只有一个参数、不能指定位数。它不是四舍五入，而是舍尾：INT(3.9)=3。

处理正数时和 ROUNDDOWN(数字,0) 效果相同；但注意负数：INT(-8.9)=-9——它始终朝更小的方向取整，比看起来会「更负」，这是很多人踩的坑。

进销存用法：按箱规算整箱数 =INT(数量/24)，734 包就是 30 个整箱，零头留给下一节的 MOD。

=INT(3.9)

向下取整得 3：只舍不入。

=INT(G2/24)

入库数量除以每箱 24 包后取整，得到整箱数。

💡 提示:

  • INT(-8.9)=-9：负数方向别想当然
  • 要指定位数的舍去用 ROUNDDOWN，纯粹去尾取整用 INT

MOD：求余数的小函数，大用处

=MOD(被除数,除数) 返回两数相除的余数，余数可以带小数；结果的符号跟随除数（第 2 个参数）：MOD(-3,2)=1，MOD(3,-2)=-1。

两个高频用法：① 判奇偶——MOD(x,2)=1 就是奇数，配上身份证第 17 位就能判性别：=IF(MOD(RIGHT(LEFT(B2,17),1),2)=1,"男","女")；② 配合 INT 做以 0.5 为单位的特殊舍入：=IF(MOD(C2,1)>=0.5,INT(C2)+0.5,INT(C2))。

进销存场景：装箱零头 =MOD(G2,24)——734 包是 30 箱零 14 包。

=MOD(G2,24)

数量除以箱规 24 的余数：整箱之外的零头。

=IF(MOD(RIGHT(LEFT(B2,17),1),2)=1,"男","女")

取身份证第 17 位除 2 判奇偶——多层嵌套从最里层读起：LEFT→RIGHT→MOD→IF。

=IF(MOD(C2,1)>=0.5,INT(C2)+0.5,INT(C2))

小数部分满 0.5 就凑成 .5，不足就舍尾——以 0.5 为基本单位的舍入。

💡 提示:

  • MOD 的余数符号跟除数一致，处理负数数据时留意

ROW/COLUMN：按位置找规律的引用套路

=ROW([引用]) 求行号，=COLUMN([引用]) 求列号；不带参数就是当前单元格的位置。

它们让公式跟着位置「走」：讲义三例——竖排数据变横排：=INDEX($A:$A,COLUMN()-2)（从 C2 写起时 COLUMN()=3，右拖自动取 A 列下一行）；隔几天取一个数：=INDEX($E:$E,ROW()*5-17)；一列数据分 3 列：=INDEX($A$1:$A$15,ROW()*3+COLUMN()-10)。

方法论记住讲义的三步：明确需求→找规律→代入调试。系数完全取决于公式起始位置，挪了地方要重新调偏移。

=INDEX($A:$A,COLUMN()-2)

列号当行号用：公式右拖时 COLUMN 递增，把竖排一列数据「摊」成横排。

=INDEX($A$1:$A$15,ROW()*3+COLUMN()-10)

行号列号一起参与运算，把一列数据重排成 3 列的矩阵。

💡 提示:

  • 先在草稿格算出 ROW()/COLUMN() 的值，再倒推偏移系数，别硬凑

SUMPRODUCT：一步算完「单价×数量」的总账

SUMPRODUCT 的本职是「对应位置相乘，再求和」：=SUMPRODUCT(数量列,单价列) 直接得出总金额，既不用金额辅助列，也不用任何组合键。

进销存三大用法：① 全年总销售额 =SUMPRODUCT(出库单!G2:G301,出库单!H2:H301)；② 库存总值 =SUMPRODUCT(库存台账!H2:H31,商品档案!E2:E31)——当前库存逐行乘进价再总加；③ 它还天然兼容数组条件运算，下一讲展开。

用它和「金额列求和」互相验算，是查账的黄金搭档。

=SUMPRODUCT(出库单!G2:G301,出库单!H2:H301)

数量列×单价列逐行相乘后总加——总销售额一条公式，中间不用金额列。

=SUMPRODUCT(库存台账!H2:H31,商品档案!E2:E31)

每个商品的当前库存乘档案进价再求和——仓库里压了多少钱的货，一眼见底。

💡 提示:

  • 两个区域行数必须一样多，且不要引用整列（行数错位会算错，整列会拖慢表格）
  • 区间末行按你工作簿的实际数据行数写，示例按出库单 300 行、台账 30 行

实战演练

账算得准不准，全看这一讲。任务三件：给出库单金额列正式装上 ROUND，把每一笔锁定到分；用 SUMPRODUCT 一步算出全年总销售额和库存总值，并与金额列合计互相验算；顺手用 INT+MOD 把入库数量按「整箱+零头」拆开。这节课结束后，期末系统的金额口径就全部统一了。可先在右侧练习数据里试手感，再回到进销存工作簿正式建造（公式中的区间末行按你的实际数据行数调整）。

  1. 金额列升级：锁定到分 — 出库单 I2 改为 =ROUND(G2H2,2)，下拉到全部流水行。随便找一行把单价临时改成 2.335，对比 =G2H2 与 =ROUND(G2*H2,2) 的差异，体会「显示两位小数」和「真舍入」的区别，然后改回。
  2. 合计验算：两种算法必须分毫不差 — 出库单底部写合计 =SUM(I2:I301)；旁边空格写 =SUMPRODUCT(G2:G301,H2:H301)。两个结果应完全相等——差了几分钱，就说明有金额行没套 ROUND。
  3. 总销售额记入仪表盘素材区 — 把 =SUMPRODUCT(出库单!G2:G301,出库单!H2:H301) 的结果记在仪表盘工作表的 KPI 区（这一讲先落地数据，期末统一美化），它就是「总销售额」指标的数据源。
  4. 库存台账：库存金额列 — 台账 K1 写表头「库存金额」，K2 输入 =ROUND(H2*VLOOKUP(A2,商品档案!A:E,5,FALSE),2)，下拉 30 行——每行的当前库存乘档案进价；K32 写合计 =SUM(K2:K31)，再在旁边用 =SUMPRODUCT(库存台账!H2:H31,商品档案!E2:E31) 交叉验算，两值相等才收工。
  5. 整箱与零头 — 在入库单旁空列试 =INT(G2/24) 与 =MOD(G2,24)，再验证：整箱数×24+零头=原数量。按 24 包一箱的规格，734 包应拆成 30 箱零 14 包。
  6. 选做：0.5 单位舍入体验 — 在空格输入一个小数（如 4.72），旁边用讲义公式 =IF(MOD(C2,1)>=0.5,INT(C2)+0.5,INT(C2)) 观察结果——小数满 0.5 凑成 .5，不满则舍尾。感受用 INT+MOD 自定义舍入规则的思路。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 出库单 I 列全部是 =ROUND(G*H,2)，合计与 SUMPRODUCT 验算完全一致
  • ☐ 台账 K 列无错误值，K 列合计与 SUMPRODUCT 交叉验算相等
  • ☐ 仪表盘素材区已记录总销售额数值
  • ☐ 能说出 ROUND 与「格式改两位小数」的区别（真假舍入）
  • ☐ INT+MOD 拆箱验算通过：整箱×24+零头=原数量