模块2：公式与函数 - Excel学习平台

公式入门 —— 从"算总价"开始认识公式

▼

📖 知识讲解

在 Excel 里，"计算"不是用计算器按出来的，而是写一条公式，让 Excel 帮我们算。公式写一次，以后数据变了，结果会自动跟着变——这就是 Excel 最迷人的地方。

🔹 第一条规矩：公式必须以 = 开头

所有公式、所有函数，前面都要加一个英文等号 =。不加等号，Excel 会把它当成普通文字。比如想算 3+5，要写 =3+5，而不是 3+5。

🔹 五种算术运算符

运算符	含义	例子	结果
+	加	`=2+3`	5
-	减	`=10-4`	6
*	乘（星号）	`=6*7`	42
/	除（斜杠）	`=20/4`	5
^	乘方（幂）	`=2^3`	8
%	百分比	`=50%`	0.5

🔹 运算优先级（先算谁后算谁）

和数学一样，Excel 也讲"先乘除后加减"。规则是：

括号 ( ) > 乘方 ^ > 乘除 * / > 加减 + -

=2+3*4 先算 3×4=12，再加 2，结果是 14
=(2+3)*4 先算括号里 2+3=5，再 ×4，结果是 20

💡 拿不准先算谁的时候，一律加括号！括号能让你的意图清清楚楚，也最不容易出错。

🔹 关键技巧：公式里用"单元格地址"，别用数字

这是新手最容易踩的坑。假设 B2 是单价、C2 是数量，正确写法是 =B2*C2，而不是 =12.9*2。

用地址：以后你把 B2 的单价改成 15，金额会自动重算。
用死数字：改了单价，公式还是 25.8，永远不会变——这就失去了 Excel 的意义。

🔹 编辑栏与 F2：查看和编辑公式

单元格里默认只显示计算结果，真正的公式藏在编辑栏（顶部 fx 那一行）。

单击单元格 → 编辑栏里看到完整公式。
双击单元格，或按 F2，可以直接在单元格里修改公式。

🔹 一键看穿所有公式：Ctrl+~

按 Ctrl+~（波浪号，数字 1 左边那个键），整张表会从"显示结果"切换成"显示公式"，方便你检查哪里写错了。再按一次恢复原样。也可以用公式选项卡 → 显示公式。

🔹 公式的复制与自动填充

写好第一个公式后，不用一个个重写。选中含公式的单元格，拖动右下角的填充柄（小绿十字）往下拉，Excel 会把公式"智能"地复制到每一行，地址自动顺延（这叫相对引用，下节课细讲）。

🔹 自动求和快捷键：Alt+=

想对一列数求和？选中要放结果的空白单元格，按 Alt+=，Excel 会自动帮你写 =SUM(...) 并猜出要求和的区域，回车即可。

🔹 三个最常见的出错值

⚠️ 公式算不出来，单元格里出现这些奇怪的字，先别慌：

#VALUE! —— 把文字和数字硬算在一起，比如 ="苹果"+5
#DIV/0! —— 除以了 0，比如 =10/0 或分母单元格是空的
#NAME? —— Excel 看不懂你写的名字，通常是函数名拼错（=SUMMA(...)）或忘写引号

🔧 操作步骤：用公式算出超市小票的金额

我们用一份 20 行的"超市购物小票"来练手，金额列先留空，由你用公式填出来。

下载下面的 CSV，把数据整理成表格：A列序号、B列商品名称、C列单价、D列数量、E列金额

单击 E2，输入 =C2*D2（单价 × 数量），按 Enter，应看到第 1 行金额

选中 E2，把鼠标移到单元格右下角，当出现小绿十字（填充柄）时，按住左键往下拖到 E21

观察：每一行的金额都自动算出来了，且公式从 =C2*D2 变成了 =C3*D3、=C4*D4……

单击 E2，看编辑栏，确认里面是 =C2*D2 而不是死数字

按 Ctrl+~ 切换成"显示公式"模式，检查每一行的公式是否正确；再按一次切回结果

选中 E22（金额下方空单元格），按 Alt+=，Excel 自动生成 =SUM(E2:E21)，回车得到总价

把 C2 的单价随便改一个数，看 E 列和总价是不是自动跟着变——这就对了！

📥 练习数据：超市购物小票（20 行生活用品）

下面是 20 行生活用品的小票，金额列留空，请下载后用公式 =C2*D2 把每一行的金额算出来，最后用 Alt+= 求总价。

序号	商品名称	单价	数量
1	抽纸(心相印)	12.9	2
2	矿泉水(农夫山泉)	2	6
3	方便面(红烧牛肉)	4.5	3
4	牙膏(云南白药)	19.9	1
5	洗发水(海飞丝)	39.9	1

显示前5条，共20条数据 ↓ 点击下载查看全部

💡 技巧提示

公式里出现 #DIV/0!，多半是分母单元格还是空的——先把分母数据填上
双击填充柄可以一键填充到底（只要左边/上边有连续数据），比拖拽更快
想看某个公式引用了哪些单元格？单击它，Excel 会用彩色框把引用的区域圈出来
按 F2 进入编辑后，用方向键在公式里移动光标，比用鼠标方便
写错公式想重来？选中单元格按 Delete 清空，再重新输入即可

📐 操作规范（养成好习惯）

公式一律引用单元格，不要直接写死数字，方便后续修改和复用
表头在第 1 行，金额等"算出来的列"也要有表头（如"金额""总价"）
数据连续存放，行与行之间不要留空行，否则自动填充和求和会断掉
金额列保持为数值格式，不要手打成"￥25.8"这样的文字
重要的计算表另存备份（如"小票计算_v2.xlsx"），公式改坏了还能找回

⚠️ 常见错误

❌ 忘记写开头的 = → 写 C2*D2 变成普通文字，不会计算
❌ 把乘号写成 × 或字母 x → Excel 只认 *
❌ 公式里直接写死数字 =12.9*2 → 改了单价结果不变，白忙一场
❌ 填充时只拖了公式却没覆盖全部行 → 部分行金额空白，求和少算
❌ 把 / 写成中文顿号或反斜杠 → 报 #NAME? 错误

📥 练习任务

下载"超市购物小票"CSV，建立表格，用 =C2*D2 算第 1 行金额
用填充柄把金额公式复制到全部 20 行
按 Ctrl+~ 切换到"显示公式"，截图检查 5 行公式
在金额下方用 Alt+= 求出所有商品的总价
把第 1 行抽纸单价改成 15，确认金额和总价自动更新
新增一列"是否满 20 元"（金额≥20 写"是"，否则写"否"），用公式 =IF(E2>=20,"是","否") 实现（IF 下节课学，这里先试一试）

🏆 挑战任务（选做）

增加一列"折扣价"，按"金额打 9 折"计算（提示：单价×数量×0.9，可以用 =C2*D2*0.9）
用括号写一条公式：先算 (单价+2) 再乘以数量，体会括号改变优先级
挑战：用一条公式直接算出"所有商品金额的平均值"（提示：=AVERAGE(E2:E21)，下节课会讲）

📝 理论测试

完成以下10道题，检验你对公式入门的掌握：

单元格引用 —— 相对引用、绝对引用与 $ 符号

▼

📖 知识讲解

"引用"就是公式里写的那个单元格地址（如 A1）。Excel 在复制公式时，会根据引用方式自动调整或固定地址，这就是相对、绝对、混合三种引用的区别。

🔹 相对引用 A1（默认）

不加 $ 的就是相对引用。公式往下复制时，行号会跟着变；往右复制时，列字母会跟着变。

例子：在 E2 写 =C2*D2，向下填充

E2: =C2 * D2

E3: =C3 * D3 （行号自动 +1）

↳ 适合"每一行都用自己的数据算"的场景

🔹 绝对引用 $A$1（锁死不动）

在列字母和行号前都加 $，公式复制到哪，这个地址永远不变。常见于"折扣率""汇率"这种全表共用的固定值。

例子：折扣率锁在 $B$1，折后价 = 原价 × 数量 × 折扣率

F2: =C2 * D2 * $B$1

F3: =C3 * D3 * $B$1 （只有 C、D 变，$B$1 不动）

↳ 想改全场折扣？只改 B1 一个格子，整列折后价全变

🔹 混合引用 $A1 与 A$1

只锁一半：$A1 锁列不锁行（往右复制列不变，往下复制行变）；A$1 锁行不锁列（往下复制行不变，往右复制列变）。做乘法表时特别好用。

🔹 F4：一键循环切换四种引用

编辑公式时，把光标放在地址上，连按 F4 会在四种状态间循环：

A1→ $A$1→ A$1→ $A1→ A1

🔹 三种引用对比

类型	写法	向下复制	典型用途
相对引用	`A1`	行号变	每行算自己的数据
绝对引用	`$A$1`	不变	折扣率、税率、单价表
混合引用	`$A1` / `A$1`	锁一半	乘法表、交叉表

🔹 跨工作表引用：Sheet1!A1

公式要用另一个工作表的数据，写法是 表名!单元格。比如 Sheet2 的 B2 想引用 Sheet1 的 A1：

=Sheet1!A1 * 2

表名有空格或中文时要加单引号：='销售数据'!A1。

🔹 跨工作簿引用：[文件名.xlsx]Sheet1!A1

引用另一个 Excel 文件里的单元格，写法是 [工作簿名]表名!单元格：

=[价格表.xlsx]Sheet1!B2

⚠️ 跨工作簿引用要求两个文件都打开，且源文件移动/改名后引用会断。正式交付建议把数据整合进同一个文件。

🔹 引用失效 #REF!

当你删除了公式所引用的行/列/工作表，Excel 找不到原来的位置，就会显示 #REF!。它一旦出现，原始引用就丢了，比 #N/A 更难修，所以删行前先检查有没有公式依赖它。

🔧 操作步骤：用绝对引用算全场折扣

一份 30 行电商商品表，折扣率放在固定单元格 $B$1，请算出每件商品的折后价。

下载 CSV，整理成表格：A列商品、B列原价、C列数量；在 B1 单元格输入折扣率，例如 0.9（表示 9 折）

单击 D2（折后价），输入 =B2*C2*B1

把光标放在公式里的 B1 上，按 F4 一次，把它变成 $B$1（绝对引用），回车

选中 D2，拖动填充柄到 D31，观察：C、B 列地址在变，但 $B$1 始终不变

把 B1 的折扣率改成 0.8，整列折后价应全部自动重算

在 E2 写 =D2-C2*C2 算"让利金额"（折后价 - 原价×数量），填充到 E31

📥 练习数据：商品折扣计算表（30 行电商商品）

下面是 30 行电商商品，折扣率请自己放在 B1 单元格（如 0.9 表示九折），用 =B2*C2*$B$1 算出折后价。

商品	原价	数量
手机壳-硅胶透明	9.9	120
充电线-TypeC	12.9	80
蓝牙耳机-入门	39.9	45
手机支架-桌面	15.9	60
钢化膜-高清	8.9	150

显示前5条，共30条数据 ↓ 点击下载查看全部

💡 技巧提示

不确定该不该加 $？问自己：复制后这个地址要不要跟着变？ 不变的就锁
编辑公式时按 F4 循环切换，比手敲 $ 快且不易错
跨表引用时，直接用鼠标点目标单元格，Excel 会自动补全 Sheet名!地址
想引用整列？写 B:B（绝对列引用），但求和时要避开结果所在列防循环
偶尔需要"半锁定"做九九表，就靠混合引用 $A1 / A$1

📐 操作规范（养成好习惯）

共用参数单独放一格（折扣率、税率、汇率），并用绝对引用锁住，方便统一调整
参数单元格要有清晰标注，如旁边写"折扣率(0.9=九折)"，别让同事猜
跨表/跨簿引用越少越好，正式报表尽量合并到同一工作簿
删除行/列前，先检查是否有公式引用它，避免产生 #REF!
工作表命名含中文或空格时，跨表引用要加单引号

⚠️ 常见错误

❌ 折扣率没锁 $ → 向下填充后变成 B2、B3、B4…，只有第一行算对
❌ 把折扣率写死在公式里 =B2*C2*0.9 → 想改全场折扣得改 30 个公式
❌ 跨表引用忘了表名或写错 → 报 #NAME? 或 #REF!
❌ 删除了被引用的行 → 公式显示 #REF!，且原始引用无法自动恢复
❌ 跨工作簿引用时源文件已关闭 → 显示 #REF! 或旧值

📥 练习任务

在 B1 输入折扣率 0.85（八五折），用 =B2*C2*$B$1 算 D2 折后价
用 F4 把 B1 锁成 $B$1，再把公式填充到全部 30 行
新增"折后总价"列 = 折后价 × 数量，观察是否也引用了 $B$1
把 B1 改成 1（不打折），确认所有折后价回到原价
在另一个工作表 Sheet2 的 A1，用跨表引用 =Sheet1!D2 取第 1 个折后价
把 B1 改成 0.7，截图折后价前 5 行，确认全部自动更新

🏆 挑战任务（选做）

做一个 9×9 乘法表：行标题 1~9、列标题 1~9，用混合引用 $A2*B$1 一拖到底
给不同商品设置不同折扣：在表格里加"折扣率"列，每行用自己的折扣率（相对引用），再算折后价
挑战：用跨工作簿引用，把价格表.xlsx 里的成本价引过来算毛利（注意两个文件都要打开）

📝 理论测试

完成以下10道题，检验你对单元格引用的掌握：

求和与统计函数 —— SUM、AVERAGE、MAX、MIN、COUNT

▼

📖 知识讲解

函数就是 Excel 自带的"现成算法"。写法和公式一样以 = 开头，格式固定为 =函数名(参数1, 参数2, ...)。参数之间用英文逗号 , 隔开。

💡 参数可以是具体数字（如 =SUM(1,2,3)）、单元格（如 =SUM(A1)），或区域（如 =SUM(A1:A10)）。最常用的是"区域"。

🔹 五大统计函数一览

SUM

=SUM(A1:A10)

求和，把所有数字加起来

AVERAGE

=AVERAGE(A1:A10)

求平均值

MAX

=MAX(A1:A10)

找最大值

MIN

=MIN(A1:A10)

找最小值

COUNT

=COUNT(A1:A10)

数一数有几个数字

🔹 SUM —— 求和

最常用。除了手动写，还可以用功能区开始选项卡里的 Σ 自动求和 按钮，或快捷键 Alt+=。

SUM(C2:C41) 求 C2 到 C41 的总和

🔹 AVERAGE —— 平均值

AVERAGE(C2:C41) 相当于 =SUM(C2:C41)/COUNT(C2:C41)

🔹 MAX / MIN —— 最大 / 最小

用来找"最高分""最低销量"等。比如 =MAX(D2:D41) 找出数学最高分。

🔹 COUNT 家族：三个"数数"函数别搞混

函数	数什么	例子
`COUNT`	只数数字单元格	=COUNT(A1:A10) → 数字个数
`COUNTA`	数非空单元格（含文字）	=COUNTA(A1:A10) → 已填个数
`COUNTBLANK`	数空白单元格	=COUNTBLANK(A1:A10) → 空单元格数

⚠️ COUNT 只认数字！如果一列是"文本型数字"（带绿三角的那种），COUNT 会把它漏掉。要数全部内容用 COUNTA。

🔹 连续区域 vs 不连续区域

一次求多个不相邻区域，用逗号隔开：

SUM(A1:A5, C1:C5) 把 A 列和 C 列两段分别求和后再相加

🔹 状态栏：不用写公式也能看统计

用鼠标选中一片数据，看 Excel 窗口最右下角的状态栏，会自动显示：平均值、计数、求和。右键状态栏还能切换显示哪些指标——临时看一眼数据特别快。

🔹 函数嵌套的雏形

函数里可以套函数。例如"求平均"其实就是"总和÷个数"：

SUM(A1:A5) / COUNT(A1:A5) 和 AVERAGE 等价

这就是"嵌套"思想的起点，后面的 IF、SUMIF 都是在这个基础上叠加的。

🔧 操作步骤：算一份班级成绩单的总分、平均分与极值

40 名同学，有语文/数学/英语/专业课四科成绩，总分和平均分留空，由你用函数算。

下载 CSV，整理成表格：A学号、B姓名、C语文、D数学、E英语、F专业课、G总分、H平均分

单击 G2（总分），输入 =SUM(C2:F2)，回车，得到第 1 名同学四科总分

选中 G2，双击填充柄自动填充到 G41（因为左边 B 列有连续姓名）

单击 H2（平均分），输入 =AVERAGE(C2:F2)，回车并填充到 H41

在成绩区下方空白格，用 =MAX(G2:G41) 找最高总分，=MIN(G2:G41) 找最低总分

用 =COUNT(C2:C41) 数一数语文成绩共几条，再用 =COUNTA(B2:B41) 数人数，确认一致

选中 C2:F41 任意区域，看状态栏显示的平均值/计数/求和，和你的函数结果对照

📥 练习数据：班级期末成绩单（40 行）

下面是 40 名同学的成绩，总分、平均分两列留空，请用 SUM、AVERAGE 算出后填入，再用 MAX/MIN/COUNT 做整体分析。

学号	姓名	语文	数学	英语	专业课
20250101	张伟	88	92	76	95
20250102	王芳	75	68	82	80
20250103	李娜	91	85	90	88
20250104	刘洋	60	55	63	70
20250105	陈静	83	79	88	91

显示前5条，共40条数据 ↓ 点击下载查看全部

💡 技巧提示

写函数时按 Tab 键可以自动补全函数名，避免拼错
输入 =SUM( 后，直接用鼠标框选要求和的区域，比手敲地址准
区域用整列引用 =SUM(C:C) 最省事，但务必保证该列没有杂数据
状态栏是"免公式快速预览"神器，临时判断数据量级超好用
COUNT 和 COUNTA 结果不一致？多半是混进了文本，检查一下数据格式

📐 操作规范（养成好习惯）

表头清晰：总分、平均分等计算列都要有表头，别让看表的人猜
统计区域要选全且不含表头（表头是文字，会被 COUNT 漏掉但 SUM 会当 0）
人数/科数这类"计数"优先用 COUNTA，避免文本型数字被 COUNT 漏计
函数参数用英文逗号；如果系统是中文标点，记得切换中英文
关键统计结果（最高/最低/平均）可加粗或变色突出，但全表颜色不超过 4 种

⚠️ 常见错误

❌ 求和区域把表头也圈进去了 → 表头是文字，SUM 当 0 处理虽不影响，但 COUNT 会少算
❌ 把 COUNT 当"数人数"用 → 文本姓名它一个都不数，应用 COUNTA
❌ 函数名拼错或中文括号 → 报 #NAME?
❌ 参数用英文分号 ; 而非逗号 , → 某些地区设置下才用分号，默认用逗号
❌ 平均分写成 =SUM/4 而非 =AVERAGE，且没锁定列 → 填充后列错乱

📥 练习任务

用 =SUM(C2:F2) 算第 1 名同学总分，填充到全班 40 行
用 =AVERAGE(C2:F2) 算平均分，填充到全班
在表下方用 =MAX(G2:G41) 和 =MIN(G2:G41) 找总分极值
用 =COUNT(C2:C41) 数语文成绩条数，=COUNTA(B2:B41) 数人数
用 =SUM(C2:C41)/COUNT(C2:C41) 验证语文平均分，和 AVERAGE 结果对照
选中任意成绩区域，看状态栏的平均值/计数/求和，截图留存

🏆 挑战任务（选做）

新增"是否及格"列：四科平均分 ≥60 写"及格"，否则"不及格"（提示：=IF(H2>=60,"及格","不及格")，IF 下节课学）
用 =SUM(C2:C41,D2:D41,E2:E41,F2:F41) 一次算出全班的"四科总得分"（不连续区域求和）
挑战：算出"专业课"超过 90 分的人数（提示：配合下节课的 COUNTIF，=COUNTIF(F2:F41,">90")）

📝 理论测试

完成以下10道题，检验你对求和统计函数的掌握：

逻辑函数 —— IF 让 Excel 会"判断"

▼

📖 知识讲解

前面的函数只会"算数"，而 IF 函数让 Excel 学会"判断对错再做决定"——这正是从"计算器"升级到"小助手"的关键一步。

🔹 先认识：比较运算符

IF 的"条件"靠比较运算符写出，结果是 TRUE（真）或 FALSE（假）：

运算符	含义	例子	结果
`=`	等于	`=5=5`	TRUE
`>`	大于	`=8>3`	TRUE
`<`	小于	`=2<1`	FALSE
`>=`	大于等于	`=6>=6`	TRUE
`<=`	小于等于	`=4<=3`	FALSE
`<>`	不等于	`=5<>3`	TRUE

🔹 IF 的三段式

IF(条件, 成立时返回, 不成立时返回)

例如"金额≥99 包邮，否则不包邮"：

IF(C2>=99, "包邮", "不包邮")

💡 返回的文字必须用英文双引号包起来；返回数字则不用引号。这是 IF 最常写错的地方。

🔹 TRUE / FALSE 是什么

比较表达式本身就会返回一个逻辑值。=5>3 显示 TRUE，=5>8 显示 FALSE。IF 正是拿这个真假来决定走哪条路。

🔹 AND / OR / NOT：把多个条件揉在一起

AND

=AND(条件1,条件2)

全部为真才真（"而且"）

OR

=OR(条件1,条件2)

有一个真就真（"或者"）

NOT

=NOT(条件)

真假颠倒

例子：金额≥99 且地区不是"偏远"才包邮：

IF(AND(C2>=99, D2<>"偏远"), "包邮", "不包邮")

🔹 IF 嵌套：做多级判断（成绩等级）

等级评定"优/良/中/及格/不及格"要一层套一层，从最高档往下写：

成绩 → 等级

IF(A1>=90,"优",

IF(A1>=80,"良",

IF(A1>=70,"中",

IF(A1>=60,"及格","不及格"))))

↳ 每多一级就多包一层 IF，括号数量要配对

🔹 IFERROR：给公式加"安全气囊"

当公式可能报错（比如除以 0、查不到数据）时，用 IFERROR 指定"出错时显示什么"，避免满屏 #DIV/0!：

IFERROR(公式, "出错时显示的内容")

例：=IFERROR(B2/C2,"无数据")，C2 为空时显示"无数据"而不是错误值。

🔹 电商里的 IF 业务判断

是否包邮：订单金额≥包邮门槛 → "包邮"，否则"不包邮"
是否达标：销售额≥目标 → "达标"，否则"未达标"
是否超预算：实际支出>预算 → "超预算"，否则"正常"

🔧 操作步骤：判定电商订单是否包邮

一份 35 行订单表，含金额和收货地区，是否包邮、判定结果留空，用 IF 判定。

下载 CSV，整理成：A订单号、B客户、C金额、D收货地区、E是否包邮、F发货时效要求、G判定结果

单击 E2，输入 =IF(C2>=99,"包邮","不包邮")（假设 99 元包邮），回车

选中 E2，填充到 E36，观察金额≥99 的显示"包邮"，其余"不包邮"

单击 G2，写复合判断：金额≥99 且地区≠"偏远" 才"正常发货"，否则"需补运费"：
=IF(AND(C2>=99,D2<>"偏远"),"正常发货","需补运费")

把 G2 填充到 G36，抽查几条：偏远地区即使金额高也应"需补运费"

在旁边空列试 IFERROR：=IFERROR(C2/COUNT(...),"—") 体会容错写法

📥 练习数据：电商订单发货判定表（35 行）

下面是 35 笔订单，是否包邮、判定结果两列留空，请用 IF / AND 判定（包邮门槛自定，如金额≥99 包邮，偏远地区不包邮）。

订单号	客户	金额	收货地区	发货时效要求
DD2025001	晓梅	128	江浙沪	24小时
DD2025002	建国	56	江浙沪	48小时
DD2025003	丽娟	210	偏远地区	72小时
DD2025004	志强	89	华南	24小时
DD2025005	春燕	305	华北	24小时

显示前5条，共35条数据 ↓ 点击下载查看全部

💡 技巧提示

IF 嵌套容易括号不配对，写完用 F2 检查，Excel 会用颜色配对括号
文字比较大小写吗？默认不区分，但建议统一写法（如都用"包邮"两字）
AND/OR 里可以继续套 AND/OR，做"三条件""四条件"都不在话下
不确定条件对不对？先在空白格写 =C2>=99 看返回 TRUE/FALSE，调对了再塞进 IF
IFERROR 包裹查找函数（下节课 VLOOKUP）是黄金搭档，几乎必用

📐 操作规范（养成好习惯）

判断阈值写清楚：公式旁注明"99 元包邮""60 分及格"，方便他人理解
返回的文字结果用统一措辞（如全用"包邮/不包邮"），别一会儿"是"一会儿"Y"
IF 嵌套层级别超过 3~4 层，太深难维护，可用下节课的 IFS（365 版）或多列分步
重要业务判断（是否达标）建议加一列原始数值留底，方便复核
涉及金额的判定，阈值单元格单独放并绝对引用（复习上节课）

⚠️ 常见错误

❌ 文字没加引号 =IF(C2>=99,包邮,不包邮) → 报 #NAME?，应写 "包邮"
❌ 用了中文引号 "" → Excel 不认，必须用英文双引号
❌ IF 嵌套括号数量不对 → 公式报错或不完整，注意左括号=右括号
❌ 把 > 写成 >= 导致边界错判（如 99 元到底包不包邮）
❌ AND/OR 参数写成一个整体 =AND(C2>=99,D2<>"偏远") 写成 =AND(C2>=99 AND D2<>"偏远") → 语法错

📥 练习任务

用 =IF(C2>=99,"包邮","不包邮") 填"是否包邮"列，填充到 35 行
用 AND 写判定结果：金额≥99 且非偏远 →"正常发货"，否则"需补运费"，填充
把包邮门槛改成 199，确认"是否包邮"结果随之更新
在旁边列用 =IF(C2>=200,"急单","普通") 标记"是否急单"
用 IF 嵌套把金额分成"高(>300)/中(100~300)/低(<100)"三档
抽查 5 条：偏远地区订单即便金额高，也应判为"需补运费"，确认无误

🏆 挑战任务（选做）

做一个"成绩等级评定表"：用 IF 嵌套把 0~100 分映射成优/良/中/及格/不及格五档
综合判定"客户价值"：金额≥300 且非偏远 → "VIP"，否则"普通"，用 AND 实现
挑战：用 IFERROR 包裹一个会除零的公式，让出错时显示"—"而非错误值

📝 理论测试

完成以下10道题，检验你对逻辑函数 IF 的掌握：

条件统计函数 —— SUMIF、COUNTIF、SUMIFS、COUNTIFS

▼

📖 知识讲解

前面的 SUM/COUNT 是"全算"，而这节课的带 IF 的兄弟们是"只算满足条件的那部分"——电商日报、月报里 80% 的统计都靠它们。

🔹 SUMIF —— 按条件求和

SUMIF(条件区域, 条件, 求和区域)

在"条件区域"里找符合条件的行，把对应"求和区域"的值加起来。例：求"店铺A"的总销售额：

SUMIF(C:C, "店铺A", H:H)

💡 当"条件区域"和"求和区域"是同一列时，求和区域可省略，写成 =SUMIF(C:C,"店铺A")。

🔹 COUNTIF —— 按条件计数

COUNTIF(区域, 条件)

统计区域里满足某条件的单元格个数。例：统计"已发货"状态有多少单：

COUNTIF(I:I, "已发货")

🔹 条件怎么写（重点！）

想要的条件	写法	说明
等于某值	`"手机"`	直接写文本
大于 1000	`">1000"`	大于号放引号内
不等于"已取消"	`"<>已取消"`	不等于用 <>
包含"手机"	`"手机"`	★ 任意多个字符
单字符匹配	`"手?"`	? 代表单个字符
大于某单元格	`">="&B1`	符号+&连接单元格

⚠️ 带比较符号（> < >=）或连接单元格（&B1）时，必须用 & 把符号和引用拼起来，且符号本身在引号里。

🔹 通配符 * 与 ?

*（星号）= 任意多个字符："*手机*" 能命中"华为手机""手机壳"
?（问号）= 任意一个字符："手机?" 命中"手机1"但不命中"手机壳"

🔹 SUMIFS / COUNTIFS —— 多条件（求和区域在前！）

多个条件同时满足时才统计。注意参数顺序和 SUMIF 相反：求和/计数区域排在最前：

SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2)

SUMIFS(H:H, C:C, "店铺A", I:I, "已发货") 店铺A且已发货的销售额

COUNTIFS 同理：=COUNTIFS(C:C,"店铺A",I:I,"已发货")

🔹 AVERAGEIF / AVERAGEIFS

按条件求平均，结构和 SUMIF 一致：=AVERAGEIF(C:C,"店铺A",H:H)。

🔹 IF 与 SUMIF 的分工

✅ 一句话分清：IF 是"对每一行逐行判断返回不同结果"；SUMIF/COUNTIF 是"跨很多行汇总出一个总数/总次数"。要逐行打标签用 IF，要算合计用 SUMIF。

🔧 操作步骤：用条件统计做电商订单分析

一份 60 行订单明细，含店铺、类目、金额、状态、客服。我们用 SUMIF/COUNTIF 算出各维度汇总。

下载 CSV，整理成：A订单号、B订单日期、C店铺、D商品名称、E类目、F数量、G单价、H订单金额、I订单状态、J客服

在空白处写 =SUMIF(C:C,"店铺A",H:H) 求"店铺A"总销售额

把"店铺A"换成"店铺B""店铺C"，或用单元格引用，算出各店铺销售额

写 =COUNTIF(I:I,"已取消") 统计"已取消"订单数；用 <>已取消 统计有效订单数

用通配符 =COUNTIF(E:E,"*手机*") 统计类目中含"手机"的订单数

用多条件 =SUMIFS(H:H,C:C,"店铺A",I:I,"已发货") 算"店铺A且已发货"销售额

用 =COUNTIFS(J:J,"小张",I:I,"已发货") 统计客服"小张"的已发货业绩单数

📥 练习数据：电商订单明细（60 行）

下面是 60 行真实风格的订单明细，用于统计各店铺销售额、各状态订单数、各客服业绩。所有数据已给全，直接用条件统计函数分析。

订单号	订单日期	店铺	商品名称	类目	数量	单价	订单金额	订单状态	客服
ORD001	2025/3/1	店铺A	蓝牙耳机	数码	2	99	198	已发货	小张
ORD002	2025/3/1	店铺B	纯棉T恤	服饰	5	39	195	已付款	小李
ORD003	2025/3/2	店铺A	手机壳	数码	10	15	150	已发货	小张
ORD004	2025/3/2	店铺C	坚果礼盒	食品	3	88	264	已取消	小王
ORD005	2025/3/3	店铺B	运动鞋	服饰	1	199	199	已发货	小李

显示前5条，共60条数据 ↓ 点击下载查看全部

💡 技巧提示

条件区域、求和区域要一样长（如都是 2~61 行），否则会算错或漏算
把条件（如"店铺A"）写进单独单元格，公式里用 C:C,"店铺A" 改成 C:C,$M$1，改条件不用改公式
记不住 SUMIFS 参数顺序？口诀："先求和区域，再成对条件"
统计金额区间可用两个 COUNTIF 相减：=COUNTIF(H:H,">100")-COUNTIF(H:H,">500")
通配符统计时要注意 * 会匹配"任意内容"，空单元格也会被 * 命中

📐 操作规范（养成好习惯）

汇总表的条件值单独成格（店铺名、状态名），用绝对引用，避免硬编码
区域尽量用整列引用 C:C 省事，但要确保该列没有无关的合计行/表头混入
统计口径写清楚：是"已发货"还是"已付款"？口径不同结果差很多，报表要标注
多条件统计优先用 SUMIFS/COUNTIFS，别用 SUMIF 硬套多个导致逻辑错
重要汇总结果加合计校验：所有店铺销售额之和应等于总销售额

⚠️ 常见错误

❌ SUMIFS 把求和区域放错位置（放在条件后面）→ 结果 0 或报错，应放最前
❌ 大于号没加引号 >1000 写成裸符号 → 应写 ">1000"
❌ 引用单元格时漏了 &：">"B1 应为 ">="&B1
❌ 条件区域和求和区域行数不一致 → 统计结果错位
❌ 用 COUNTIF 统计"订单金额>1000 的数量"却把区域选成文本列 → 永远 0

📥 练习任务

用 SUMIF 分别算出店铺A、店铺B、店铺C 的总销售额
用 COUNTIF 统计"已取消"订单数，再用 "<>已取消" 统计有效订单数
用 "*手机*" 通配符统计类目含"手机"的订单数
用 SUMIFS 算"店铺A 且已发货"的销售额
用 COUNTIFS 统计客服"小张"的"已发货"业绩单数
用 AVERAGEIF 算"店铺B"的平均订单金额，与总平均对照

🏆 挑战任务（选做）

统计"金额在 100~500 之间"的订单数（用两个 COUNTIF 相减）
做一个"各客服业绩看板"：每个客服的已发货订单数 + 总销售额（SUMIFS+COUNTIFS）
挑战：用 SUMIFS 配合日期条件 ">="&某单元格 算"3月1日之后"的销售额

📝 理论测试

完成以下10道题，检验你对条件统计函数的掌握：

查找与引用 —— VLOOKUP 与它的伙伴们

▼

📖 知识讲解

日常工作中，"按编码查价格""按姓名查部门"是高频需求。VLOOKUP 就是干这个的：给一个"钥匙"，去表里竖着查找并返回对应信息。

🔹 VLOOKUP 四参数

VLOOKUP(查找值, 查找区域, 返回第几列, 0)

查找值：你要拿去匹配的"钥匙"，如商品编码
查找区域：去哪张表里找，必须包含"钥匙列"和"要返回的列"
返回第几列：从查找区域最左列算起，第几列是你要的结果（1 就是钥匙本身那列）
第四参数 0：固定写 0，表示精确匹配（找不到就报错）。写 1 是模糊匹配，新手别用

例：按商品编码查单价

VLOOKUP(A2, $E$2:$G$31, 3, 0)

↳ A2=要查的编码；E:G 是价格表（E编码/F名称/G单价），单价在第3列；0=精确匹配

🔹 铁律 1：查找值必须在查找区域的第一列

VLOOKUP 只能往右找。所以你的查找区域，最左边必须是"钥匙列"（如商品编码）。如果钥匙在右边，要么调整列顺序，要么改用下面的 INDEX+MATCH。

🔹 铁律 2：查找区域要用 $ 锁定

因为要往下填充，查找区域必须绝对引用 $E$2:$G$31，否则下拉后区域跑偏，查出一堆错。

🔹 铁律 3：第四参数用 0

绝大多数业务查询都要精确匹配，第四参数写 0（也可写 FALSE）。写 1/TRUE 会做"近似匹配"，极易查错。

🔹 #N/A 的五种常见原因

① 查找值在表里根本不存在（编码输错、多了空格）
② 查找值有多余空格（"A001 "≠"A001"），用 TRIM 处理
③ 查找区域没锁 $，下拉后区域错位
④ 返回的列号超出了查找区域范围
⑤ 数据类型不一致：一边是文本"001"、一边是数字 1，永远对不上

🔹 用 IFERROR 包一层，优雅处理查不到

IFERROR(VLOOKUP(A2,$E$2:$G$31,3,0), "未找到")

查不到时显示"未找到"而不是刺眼的 #N/A。

🔹 更灵活的 INDEX + MATCH

VLOOKUP 只能向右找，INDEX+MATCH 可以左右随便找：

INDEX(返回列, MATCH(查找值, 钥匙列, 0))

MATCH 负责"钥匙在第几行"，INDEX 负责"取那行某列的值"，组合起来不受方向限制。

🔹 新版 XLOOKUP（Microsoft 365）

Excel 365 / 2021 提供了更简单的 XLOOKUP，不用数第几列、不用锁区域、能向左查：

XLOOKUP(查找值, 钥匙列, 返回列)

如果学校电脑还是旧版 Excel，先掌握 VLOOKUP，XLOOKUP 作为了解即可。

🔧 操作步骤：订单表按商品编码取价格

有两张表：商品价格表（编码/名称/类目/单价/成本）和 订单表（订单号/编码/数量/单价待填/金额）。我们要在订单表里按编码自动带出单价。

同时打开两份 CSV（或放进同一个工作簿的两个 Sheet）

在订单表 D2（单价）输入 =VLOOKUP(B2,价格表!$A$2:$E$31,4,0) —— 第 4 列是单价

回车，应看到第 1 行订单对应的单价自动出现

选中 D2，填充到全部 40 行；抽查几条，确认价格带对了

在 E2 写 =C2*D2 算金额（数量×单价），填充

把其中一行订单的编码故意改错（如 A001→A00X），看是否出现 #N/A

用 IFERROR 包住 VLOOKUP，让查不到时显示"未找到"，再改回正确编码

📥 练习数据：商品价格表（30 行）+ 订单表（40 行）

两份表配套使用：先在订单表的"单价"列用 VLOOKUP 按商品编码去价格表取单价，再算金额。价格表如下：

商品编码	商品名称	类目	单价	成本
A001	蓝牙耳机	数码	99	55
A002	纯棉T恤	服饰	39	18
A003	手机壳	数码	15	6
A004	坚果礼盒	食品	88	50
A005	运动鞋	服饰	199	120

价格表显示前5条，共30条 ↓ 点击下载查看全部

订单表（单价、金额留空，等你用 VLOOKUP + 乘法填）：

订单号	商品编码	数量
SO001	A001	2
SO002	A003	10
SO003	A005	1
SO004	A002	5
SO005	A004	3

订单表显示前5条，共40条 ↓ 点击下载查看全部

💡 技巧提示

返回列号数错是最常见的错，写公式前先在查找区域里从左往右数一遍
查不到先别慌：用 =TRIM(查找值) 清掉空格，或检查两边数据类型是否一致
想避免数错列，配合 表（Ctrl+T） 可用列名，但基础阶段先练手工数
IFERROR 包 VLOOKUP 是标准姿势，正式报表几乎必加
365 用户直接用 XLOOKUP，省去"锁区域+数第几列"两苦恼

📐 操作规范（养成好习惯）

价格表、参数表这类基准数据单独放一个 Sheet，命名为"价格表""参数表"，别和明细混在一起
VLOOKUP 的查找区域务必加 $ 绝对引用，否则填充后全盘错
编码、ID 等"钥匙"列统一格式（都用文本或都用数字），防止对不上
第四参数固定写 0（精确匹配），除非你明确要做区间匹配
交付前抽查 3~5 条查找结果，确认带出的信息正确

⚠️ 常见错误

❌ 查找值不在查找区域第一列 → 永远查不到，应调整列序或换 INDEX+MATCH
❌ 查找区域没加 $ → 下拉后区域漂移，结果乱套
❌ 第四参数写成 1（模糊匹配）→ 查出不相关的值还以为对了
❌ 返回列号超出范围（如区域只有 3 列却写 4）→ 报 #REF!
❌ 两边数据类型不一致（文本 vs 数字）→ 满屏 #N/A

📥 练习任务

在订单表 D2 用 =VLOOKUP(B2,价格表!$A$2:$E$31,4,0) 取单价，填充 40 行
在 E2 用 =C2*D2 算金额，填充
抽查 5 条，确认单价、金额与价格表一致
把某一行编码改成错误值（如 A00X），观察 #N/A，再用 IFERROR 包成"未找到"
新增"毛利"列 = 金额 - 数量×成本（成本用 VLOOKUP 取第 5 列）
把价格表某商品单价改一下，确认订单表金额随之更新（引用生效）

🏆 挑战任务（选做）

用 INDEX+MATCH 实现"按商品名称（非首列）查单价"，体会它比 VLOOKUP 灵活
若你用的是 365，把 VLOOKUP 改写成 XLOOKUP 完成同样的取价
挑战：用 VLOOKUP 从价格表同时带出"类目"和"单价"两列，做商品明细看板

📝 理论测试

完成以下10道题，检验你对 VLOOKUP 的掌握：

文本与日期函数 —— 把数据"拆开再拼起来"

▼

📖 知识讲解

真实数据常常"挤"在一个单元格里（如"浙江省杭州市西湖区"），或者格式乱七八糟。文本与日期函数就是帮你把它拆开、整理、再拼好的工具箱。

🔹 截取文本：LEFT / RIGHT / MID / LEN

LEFT

=LEFT(文本, 3)

从左边取 3 个字符

RIGHT

=RIGHT(文本, 4)

从右边取 4 个字符

MID

=MID(文本, 2, 3)

从第 2 位起取 3 个

LEN

=LEN(文本)

数一数共几个字符

例：手机号 13812345678，取前 3 位运营商号段：=LEFT(A2,3) → 138。

🔹 找位置：FIND / SEARCH

想知道"省""市"在哪个位置，用 FIND（区分大小写）/ SEARCH（不区分）：

FIND("省", A2) 返回"省"字在文本中的位置

配合 MID 就能"按关键字切分"，比如从"浙江省杭州市"取出"浙江"。

🔹 拼接：& 连接符 / CONCAT / TEXTJOIN

& 连接符：=A2&"-"&B2 把两格用短横拼起来
CONCAT：=CONCAT(A2,B2) 直接拼接多个文本
TEXTJOIN：=TEXTJOIN("-",,A2:C2) 用"-"把 A~C 拼起来，还能跳过空单元格

🔹 格式化显示：TEXT

把数字/日期按指定"格式代码"显示成文本：

TEXT(A1, "0000") 数字补零成 4 位，如 12 → "0012"

TEXT(A1, "yyyy年m月") 日期 → "2025年3月"

🔹 清洗：TRIM / CLEAN

TRIM：去掉首尾多余空格、把中间多个空格合并成一个
CLEAN：去掉不可打印的换行/控制字符（从系统导出的脏数据常有）

🔹 大小写：UPPER / LOWER / PROPER

UPPER 全大写、LOWER 全小写、PROPER 首字母大写（如英文名规范化）

🔹 日期函数家族

函数	作用	例子
`TODAY()`	今天日期	=TODAY()
`NOW()`	现在日期+时间	=NOW()
`YEAR/MONTH/DAY`	取年/月/日	=YEAR(A2)
`WEEKDAY`	星期几(1~7)	=WEEKDAY(A2,2)
`DATEDIF`	两日期差	=DATEDIF(开始,结束,"d")
`EDATE`	加减月数	=EDATE(A2,12)
`EOMONTH`	某月最后一天	=EOMONTH(A2,0)

💡 DATEDIF 的第三参数："y"年差、"m"月差、"d"天差。它是隐藏函数，输的时候不会自动提示，但确实能用。

🔹 文本型数字 ⇄ 数值

从系统导出的数字常是"文本"（带绿三角），不能参与计算。互转小技巧：

文本转数值：--A2 或 A2*1（两个减号把文本强转成数）
数值转文本：A2&""（连接空文本，变成文本）

🔧 操作步骤：从客户信息表里"拆"出有用字段

一份 40 行客户信息表，含手机号、所在省市区、注册日期等。所属省份、注册年月、入会天数、会员到期日留空，由你用文本/日期函数算。

下载 CSV，整理成：A客户编号、B姓名、C手机号、D所在省市区、E注册日期、F最近下单时间、G客户等级（及待填列）

取手机号前 3 位：在空列写 =LEFT(C2,3)，填充 40 行

取所属省份：=LEFT(D2, FIND("省",D2))（假设都是"X省…"格式），填充

取注册年月：=TEXT(E2,"yyyy年m月")，填充

算入会天数：=DATEDIF(E2, TODAY(),"d")，填充（今天减去注册日）

算会员到期日：=EDATE(E2,12)（注册日加 12 个月），填充

把"省"字用 =SUBSTITUTE(省份单元格,"省","") 去掉，得到纯省名

📥 练习数据：客户信息表（40 行）

下面是 40 位客户的信息，所属省份、注册年月、入会天数、会员到期日四列留空，请用 LEFT/FIND/TEXT/DATEDIF/EDATE 算出来。

客户编号	姓名	手机号	所在省市区	注册日期	最近下单时间	客户等级
KH001	周敏	13812345678	浙江省杭州市西湖区	2024/3/15	2025/3/1	黄金
KH002	黄磊	13912345679	广东省深圳市南山区	2024/5/20	2025/2/18	白银
KH003	徐丽	13712345680	江苏省南京市鼓楼区	2023/11/8	2025/1/25	黄金
KH004	马涛	15812345681	四川省成都市武侯区	2024/8/2	2025/3/5	青铜
KH005	林芳	13612345682	北京市朝阳区	2024/1/12	2025/2/9	黄金

显示前5条，共40条数据 ↓ 点击下载查看全部

💡 技巧提示

FIND 找不到会报 #VALUE!，配合 IFERROR 更稳；不区分大小写时用 SEARCH
处理"省/市/区"拆分，先用 FIND 定位关键字位置，再用 MID 截取中间段
TEXT 之后结果是文本，不能直接当数字算，需要再 -- 转回
DATEDIF 算年龄、会员天数、账期差都很顺手，记住第三个参数是"y/m/d"
大量脏数据先 TRIM+CLEAN 洗一遍，再去重、查找，效率翻倍

📐 操作规范（养成好习惯）

拆分出的"省份""年月"等派生列放在原列右侧，并加表头，保持表格整洁
日期统一用 年/月/日 真实日期格式，别手打成文字，否则无法计算天数
从系统导出数据后，先清洗（TRIM/CLEAN/转数值）再分析，避免脏数据坑人
手机号、身份证等保留为文本，防止前导 0 丢失或变科学计数法
派生列可标为"系统计算"用浅色底，和人工录入列区分

⚠️ 常见错误

❌ MID 起算位置搞错：Excel 字符从 1 开始数，不是 0
❌ 把日期当文本截：LEFT(日期,4) 截到的是序列号，应先 TEXT 再截
❌ FIND 找不到关键字报 #VALUE! → 用 IFERROR 兜底或先确认数据格式
❌ TEXT 结果当数字用 → 求和得到 0，记得 -- 转回数值
❌ 文本型数字直接相加 → 结果 0，用 -- 或 *1 转数值

📥 练习任务

用 =LEFT(C2,3) 提取手机号前 3 位（运营商号段），填充 40 行
用 =LEFT(D2, FIND("省",D2)) 提取所属省份，填充
用 =TEXT(E2,"yyyy年m月") 生成"注册年月"，填充
用 =DATEDIF(E2,TODAY(),"d") 算"入会天数"，填充
用 =EDATE(E2,12) 算"会员到期日"（注册日+12个月），填充
把省份里的"省"字用 SUBSTITUTE 去掉，得到纯省名（如"浙江""广东"）

🏆 挑战任务（选做）

用 FIND+MID 从"浙江省杭州市西湖区"中单独取出"杭州市"（定位"省"和"市"之间）
用 =WEEKDAY(注册日期,2) 判断客户都在星期几注册，统计各星期注册人数
挑战：把"客户编号+姓名"用 TEXTJOIN 拼成"KH001-周敏"格式的会员卡号

📝 理论测试

完成以下10道题，检验你对文本与日期函数的掌握：