📌 阶段三 · 函数核心 · 第7讲 认识函数与公式 · 约 40 分钟

让出入库单金额自动计算，弄懂相对引用与绝对引用这两把钥匙

学习目标

  • 记住公式必须以 = 开头，会用算术运算符和 & 连字符
  • 理解比较运算返回 TRUE/FALSE，且在运算中当作 1 和 0
  • 分清相对引用、绝对引用、混合引用，会按 F4 加美元符号
  • 认识 SUM、AVERAGE、MAX、MIN、COUNT、COUNTA、RANK 七个函数
  • 会用「定位空值 + 自动求和」和 Ctrl+Enter 做批量填充

知识点讲解

公式的起点：一切从「=」开始

在单元格里输入的第一个字符是等号 =，Excel 就把这格当成公式来算。比如 =G2*H2 就是把 G2 和 H2 两个单元格的值相乘——注意算的是「单元格里的值」，不是格子本身，所以源头一变，结果自动跟着变，这就是公式和手算的区别。

常用算术运算符：+ 加、- 减、* 乘、/ 除、% 百分号（表示除以 100）、^ 乘方，还有一个文本专用的 &（下一节讲）。运算优先级和数学一样：先乘除后加减，括号最优先，拿不准就加括号。

💡 提示:

  • 公式写完按回车，修改时双击单元格或按 F2 进入编辑

比较运算符：公式也会下结论

比较运算符有六个：=（等于）、>（大于）、<（小于）、>=（大于或等于）、<=（小于或等于）、<>（不等于）。

比较运算的结果不是数字，而是逻辑值：输入 =1+3>2 得 TRUE，输入 =1+8<6 得 FALSE。TRUE 和 FALSE 参与数学运算时分别当 1 和 0 用。

这个特性很实用，比如讲义的经典例子「本地考生加 30 分」：=(D6="本地")*30+F6——如果 D6 是本地，比较得 TRUE 当 1，乘 30 得 30，加上 F6 的原始分。进销存里同理：=(E2="月结30天")*1 可以把文本条件变成 1/0 参与统计。

另外注意：公式里出现的文本必须用英文双引号括起来，如 =A1="办公文具"，不写引号 Excel 会当成名字去找，直接报错。

💡 提示:

  • TRUE 当 1、FALSE 当 0，是第 8 讲 IF 函数和第 18 讲数组公式的地基

& 连字符：文本拼接与一个求和大坑

& 用来把多段文本连接成一个：A1 里是商品名称、A2 里是规格，A3 写 =A1&A2 就拼成完整品名。进销存常用它做「联合标签」，比如 =A2&"（"&C2&"）" 把编号和名称拼在一起显示。

再记一个坑：对文本类型的数字用 SUM 求和，结果是 0；但用 + - * / 去算文本数字是可以的，而且运算后的结果就能被 SUM 求和了。这就是第 2 讲「选择性粘贴乘 1 强制转数值」的原理——乘 1 就是让文本数字过一遍算术运算，露出数值本质。

💡 提示:

  • 拼接出来的长编号（如 =A2&B2）是文本，拿去和真编号比对时要小心格式

相对引用、绝对引用、混合引用

这是全课程最重要的概念之一，务必吃透。

相对引用 A1：公式里的引用会随公式位置变化。金额列 I2 写 =G2H2，下拉到 I3 自动变 =G3H3——正因为是相对引用，双击填充柄整列公式一秒完成。

绝对引用 $A$11：加了美元符号（可按 F4 快速添加），行和列都被锁死，公式拖到哪它都不变。适合锁定税率、固定的合计数、单价表的位置。

混合引用 $A1（锁列不锁行）、A$1（锁行不锁列）：既要横向拖又要纵向拖的时候用，经典案例是九九乘法表。进销存里暂时用得少，知道有这回事，第 10 讲 SUMIF、第 19 讲 INDIRECT 会再遇到。

💡 提示:

  • F4 键循环切换：A1 → $A$1 → A$1 → $A1
  • 下拉填充只在乎「行」变不变，横向拖只在乎「列」变不变

认识函数：等号、函数名、括号、参数

函数就是 Excel 预制好的公式，结构固定：等号开头、函数名在中间、括号结尾、括号中间写参数，参数之间用逗号隔开。例如 =SUM(D5:G5)。

本讲先认识 7 个最基础的：

SUM 求和：=SUM(I2:I201)；

AVERAGE 求平均：=AVERAGE(I2:I201)；

MAX / MIN 求最大/最小：找出单笔最大入库金额；

COUNT 计数：只数数字格的个数；COUNTA 计数：数所有非空格（文本也算）——统计「录了几行单」用 COUNTA；

RANK 排名：=RANK(H5,$H$5:$H$11)，第一个参数是当前数，第二个参数是所有数所在区域，参数用逗号隔开，区域必须用绝对引用（否则下拉时排名区域跟着缩，结果全错）。

💡 提示:

  • 忘了函数名可以在输入 = 后点编辑栏左侧的 fx 插入函数搜索

定位空值 + 自动求和：跳跃式汇总

流水表里隔几行就有个小计行，一段段求和太累。跳跃式求和两步走：选中整个数据区域（含所有空着的小计格），【查找和选择-定位条件-空值】，然后点「公式」选项卡里的自动求和（Σ）——所有空位一次性填好各自的段和。

同款思路的变体：定位空值后直接输入公式，按 Ctrl+Enter（而不是回车），公式会带着相对引用自动适配每一个空位，一次全填完。第 3 讲的定位、本讲的公式，在这里合流了。

💡 提示:

  • 自动求和快捷键：Alt+= ，选中单元格按一下自动生成 SUM

实战演练

系统正式进入自动化阶段。本讲实战：让入库单、出库单的金额列自动计算（金额=数量×单价），加合计行；库存台账的期初库存列引用商品档案；再用 RANK 给 12 个月销售额排名。这批公式是台账自动化的第一块砖，期末成品里金额列就是这么算的。

  1. 入库单金额列公式 — 在入库单 I2 输入 =G2*H2（数量×单价，别写反），回车后选中 I2，双击右下角填充柄，整列公式瞬间完成。抽查三行手工验证：数量 100 × 单价 12.5 = 金额 1250。
  2. 入库单合计行 — 在流水下方空一行的 A 列输入「合计」，I 列对应位置输入 =SUM(I2:I201)（行数按你的数据来；或选中合计格直接按 Alt+= 让 Excel 自动圈定求和范围）。再用 =AVERAGE(I2:I201) 算单笔平均、=MAX(I2:I201) 找最大单，感受三个兄弟函数。
  3. 出库单同款处理 — 切到出库单，重复前两步：金额列 =G2*H2 双击填充，合计行 =SUM(...)。提醒：出库单的单价用的是商品售价，公式结构完全一样。
  4. 台账期初库存引用档案 — 切到库存台账，E2（期初库存）输入 =商品档案!H2，回车后下拉填充。观察：相对引用让每一行各自引用档案对应行的期初库存——这要求台账商品顺序和档案一致（我们的设计正是如此）。点 E2 按 F4 试试给 H2 加上美元符号，感受三种引用形态切换。
  5. 绝对引用算金额占比 — 在入库单合计行旁（比如 J2）算第一笔金额占总金额的比例：=I2/$I$202（合计格用 F4 锁死），下拉填充，观察分母一动不动、分子逐行变化——这就是绝对引用的意义。算完把这一列删掉，它只是练习。
  6. RANK 给月度销售排名 — 切到出库月报（第 6 讲的透视表），在旁边空白列对 12 个月的「销售金额」排名：C2 输入 =RANK(B2,$B$2:$B$13)，下拉。重点检查区域参数是否锁了 $——忘了锁的话下拉后排名会越来越离谱，正好体会这个坑。

在线练习

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

表格加载中…

验收清单

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

  • ☐ 能说出公式必须以 = 开头，公式里的文本要加英文双引号
  • ☐ 入库单、出库单金额列整列公式，双击填充一次完成
  • ☐ 合计行 SUM 结果与「分类汇总/透视表」算出的总额一致
  • ☐ 会按 F4 在 A1、$A$1、A$1、$A1 之间切换
  • ☐ RANK 的排名区域用了绝对引用，12 个月排名无重复错乱
  • ☐ 台账期初库存列已用跨表相对引用连接商品档案