Excel公式与引用基础
本章概览
Excel的核心能力在于计算和数据处理，而公式是实现这些功能的基础。本章将系统讲解Excel公式的基本语法、单元格引用方式以及常用运算符，帮助你建立扎实的公式基础。
学习目标
- 掌握Excel公式的基本语法和输入方法
- 理解相对引用、绝对引用和混合引用的区别
- 熟练运用各类运算符进行计算
- 学会使用名称管理器提升公式可读性
- 掌握公式审核和错误处理技巧
17.1 公式基础语法
17.1.1 公式的基本结构
公式组成
= 函数名(参数1, 参数2, ...)
核心要素
- 等号开头：所有公式必须以
=开始 - 函数名称：Excel内置函数或自定义函数
- 参数列表：用括号包围，多个参数用逗号分隔
- 单元格引用：A1、B2等形式引用数据
示例
=SUM(A1:A10) # 求和函数
=AVERAGE(B1:B5) # 平均值
=A1+B1 # 简单加法
=IF(C1>60,"及格","不及格") # 条件判断
17.1.2 公式输入方法
方法一：直接输入
- 选中目标单元格
- 输入等号
= - 输入公式内容
- 按Enter确认
方法二：点击引用
- 输入
= - 用鼠标点击需要引用的单元格
- Excel自动插入单元格地址
- 继续输入其他部分
方法三：使用公式生成器
- 点击"公式"选项卡
- 选择"插入函数"
- 在对话框中选择函数并填写参数
快捷技巧
F2：编辑当前单元格公式- `Ctrl + ``（反引号）：显示/隐藏公式
F9：计算选中的公式部分Ctrl + Shift + Enter：输入数组公式
17.1.3 公式的显示与隐藏
显示所有公式
- 按`Ctrl + ``（反引号键，通常在Tab键上方）
- 或：公式选项卡 → 公式审核 → 显示公式
查看单个公式
- 选中单元格，在编辑栏查看
- 双击单元格进入编辑模式
- 按F2键编辑
17.2 单元格引用方式
17.2.1 相对引用
定义 引用会随公式位置变化而自动调整
语法
A1 # 列和行都相对
特点
- 复制公式时，引用自动调整
- 最常用的引用方式
- 适用于有规律的批量计算
示例
A1单元格：=B1*C1
复制到A2：=B2*C2 # 自动调整
复制到A3：=B3*C3
应用场景
A B C D
1 商品 单价 数量 金额
2 苹果 5 10 =B2*C2
3 香蕉 3 15 =B3*C3
4 橙子 4 20 =B4*C4
D2的公式复制到D3、D4时自动调整行号。
17.2.2 绝对引用
定义 引用固定不变，无论公式复制到哪里
语法
$A$1 # 列和行都绝对
特点
- 用
$符号固定列号和行号 - 复制公式时引用不变
- 适用于引用固定常量
快捷键
选中单元格引用后按F4循环切换引用类型
示例
A B C
1 税率 0.13
2 单价 金额 含税金额
3 100 =B3*(1+$B$1)
4 200 =B4*(1+$B$1)
5 300 =B5*(1+$B$1)
所有公式都引用固定的B1单元格（税率）。
17.2.3 混合引用
定义 列绝对行相对，或列相对行绝对
语法
$A1 # 列绝对，行相对
A$1 # 列相对，行绝对
应用场景：九九乘法表
A B C D E
1 1 2 3 4
2 1 =$A2*B$1 =$A2*C$1 =$A2*D$1
3 2 =$A3*B$1 =$A3*C$1 =$A3*D$1
4 3 =$A4*B$1 =$A4*C$1 =$A4*D$1
B2单元格公式：=$A2*B$1
$A：向右复制时列不变2：向下复制时行变化B：向右复制时列变化$1：向下复制时行不变
F4键循环
- 第一次按F4：
$A$1（绝对引用） - 第二次按F4：
A$1（行绝对） - 第三次按F4：
$A1（列绝对） - 第四次按F4：
A1（相对引用）
17.2.4 跨工作表引用
同一工作簿
=Sheet2!A1 # 引用Sheet2的A1
=Sheet2!A1:B10 # 引用Sheet2的区域
=SUM(Sheet2:Sheet4!A1) # 引用多个工作表
不同工作簿
=[工作簿名.xlsx]Sheet1!A1
='C:\Documents\[销售数据.xlsx]一月'!A1
技巧
- 输入
=后切换到目标工作表点击单元格 - Excel自动生成引用路径
- 工作表名含空格或特殊字符时自动加单引号
17.2.5 三维引用
定义 引用多个工作表的相同位置
语法
=SUM(Sheet1:Sheet12!A1) # 求和Sheet1到Sheet12的A1
应用场景
# 12个月度销售表，汇总全年数据
=SUM(一月:十二月!B2)
注意事项
- 工作表必须连续
- 中间不能插入或删除工作表
- 所有工作表结构应一致
17.3 运算符详解
17.3.1 算术运算符
| 运算符 | 功能 | 示例 | 结果 |
|---|---|---|---|
+ | 加法 | =10+5 | 15 |
- | 减法 | =10-5 | 5 |
* | 乘法 | =10*5 | 50 |
/ | 除法 | =10/5 | 2 |
% | 百分比 | =50% | 0.5 |
^ | 乘方 | =2^3 | 8 |
应用示例
=A1+B1*C1 # 先乘后加
=(A1+B1)*C1 # 括号优先
=A1^2 # 平方
=A1/B1*100&"%" # 计算百分比并添加符号
17.3.2 比较运算符
| 运算符 | 功能 | 示例 | 结果 |
|---|---|---|---|
= | 等于 | =A1=B1 | TRUE/FALSE |
> | 大于 | =A1>60 | TRUE/FALSE |
< | 小于 | =A1<100 | TRUE/FALSE |
>= | 大于等于 | =A1>=60 | TRUE/FALSE |
<= | 小于等于 | =A1<=100 | TRUE/FALSE |
<> | 不等于 | =A1<>B1 | TRUE/FALSE |
应用示例
=IF(A1>=60,"及格","不及格")
=COUNTIF(A:A,">100") # 统计大于100的数量
=SUMIF(B:B,">=60",C:C) # 条件求和
17.3.3 文本运算符
连接符：&
=A1&B1 # 连接两个单元格
="总计："&SUM(A:A) # 连接文本和数值
=A1&" "&B1 # 中间加空格
=TEXT(A1,"0.00")&"元" # 格式化后连接
应用场景
# 姓名组合
=B2&C2 # 张三 + 丰 = 张三丰
# 地址组合
=A1&B1&C1&D1 # 省 + 市 + 区 + 详细地址
# 编号生成
="NO."&TEXT(ROW(),"0000") # NO.0001, NO.0002...
17.3.4 引用运算符
冒号：区域引用
=SUM(A1:A10) # A1到A10的区域
=AVERAGE(B2:D2) # B2到D2的区域
逗号：联合引用
=SUM(A1:A10,C1:C10) # 两个不连续区域
=COUNT(A1,A3,A5,A7) # 多个单独单元格
空格：交叉引用
=SUM(A1:C3 B2:D4) # 取交集区域B2:C3
17.3.5 运算优先级
优先级顺序（从高到低）
:区域引用- 空格（交叉）、
,联合 -（负数）%百分比^乘方*/乘除+-加减&文本连接=<><=>=<>比较
示例
=10+5*2 # 结果20（先乘后加）
=(10+5)*2 # 结果30（括号优先）
=A1+B1&"元" # 先算加法，再连接文本
="总计："&A1+B1 # 错误！应加括号
="总计："&(A1+B1) # 正确
17.4 名称管理器
17.4.1 定义名称
方法一：名称框定义
- 选中单元格或区域
- 点击左上角名称框
- 输入名称（如"税率"）
- 按Enter确认
方法二：定义名称对话框
- 选择"公式"选项卡
- 点击"定义名称"
- 填写名称和引用位置
- 点击确定
命名规则
- 必须以字母或下划线开头
- 不能包含空格（用下划线替代）
- 不能与单元格地址相同（如A1、B2）
- 区分大小写
- 最长255个字符
示例
# 定义名称
税率 = $B$1
单价列 = $B$2:$B$100
销售总额 = SUM(金额列)
# 使用名称
=单价*数量*(1+税率)
=VLOOKUP(A2,产品表,2,0)
17.4.2 名称的优势
提高可读性
# 使用单元格引用（难以理解）
=B2*C2*(1+$E$1)
# 使用名称（清晰明了）
=单价*数量*(1+税率)
便于维护
# 修改引用位置时，只需在名称管理器中修改一次
# 所有使用该名称的公式自动更新
跨工作表使用
# 定义工作簿级别名称后，所有工作表都可使用
=销售总额/12 # 计算月均销售额
17.4.3 管理名称
打开名称管理器
- 公式选项卡 → 名称管理器
- 快捷键：
Ctrl + F3
管理操作
- 新建：创建新名称
- 编辑：修改名称或引用范围
- 删除：删除不再使用的名称
- 筛选：按类别查看名称
批量创建名称
- 选中包含标题和数据的区域
- 公式 → 根据所选内容创建
- 选择首行/首列作为名称
- 点击确定
应用场景
A B C D
1 姓名 语文 数学 英语
2 张三 85 90 88
3 李四 92 87 95
# 选中A1:D3，根据首行创建名称
# 自动创建：语文、数学、英语三个名称
=AVERAGE(语文) # 计算语文平均分
17.5 公式审核与调试
17.5.1 追踪引用关系
追踪引用单元格
- 选中包含公式的单元格
- 公式选项卡 → 追踪引用单元格
- 出现蓝色箭头，指向被引用的单元格
追踪从属单元格
- 选中数据单元格
- 公式选项卡 → 追踪从属单元格
- 显示哪些公式引用了该单元格
移去箭头
- 公式 → 移去箭头
应用场景
- 检查公式是否引用了正确的单元格
- 查找循环引用
- 理解复杂工作表的数据流向
17.5.2 公式求值
使用方法
- 选中包含公式的单元格
- 公式 → 公式求值
- 点击"求值"按钮逐步查看计算过程
- 观察每一步的结果
示例
公式：=IF(SUM(A1:A3)>100,A1*0.9,A1*0.8)
求值过程：
1. =IF(SUM(A1:A3)>100,A1*0.9,A1*0.8)
2. =IF(150>100,A1*0.9,A1*0.8)
3. =IF(TRUE,A1*0.9,A1*0.8)
4. =A1*0.9
5. =50*0.9
6. =45
适用场景
- 复杂嵌套公式调试
- 查找公式错误原因
- 理解公式逻辑
17.5.3 错误检查
常见错误类型
| 错误值 | 含义 | 常见原因 | 解决方法 |
|---|---|---|---|
#DIV/0! | 除数为零 | 分母为0或空单元格 | 用IF检查：=IF(B1=0,"",A1/B1) |
#N/A | 值不可用 | VLOOKUP找不到值 | 用IFERROR包装 |
#VALUE! | 值错误 | 数据类型不匹配 | 检查数据类型 |
#REF! | 引用无效 | 删除了被引用的单元格 | 恢复引用或修改公式 |
#NAME? | 名称错误 | 函数名或名称拼写错误 | 检查拼写 |
#NUM! | 数值错误 | 数值计算超出范围 | 检查参数有效性 |
#NULL! | 空值错误 | 区域交集为空 | 检查引用范围 |
###### | 列宽不够 | 数值或日期过长 | 调整列宽 |
错误处理函数
# IFERROR：捕获所有错误
=IFERROR(A1/B1,"除数不能为零")
# IFNA：只处理#N/A错误
=IFNA(VLOOKUP(A1,表格,2,0),"未找到")
# ISERROR：判断是否错误
=IF(ISERROR(A1/B1),"错误",A1/B1)
17.5.4 循环引用检测
什么是循环引用 公式直接或间接引用自身，形成循环
示例
A1: =B1+10
B1: =A1+20 # 循环引用！
检测方法
- Excel自动提示循环引用警告
- 公式 → 错误检查 → 循环引用
- 状态栏显示循环引用位置
解决方法
- 检查公式逻辑，打破循环链
- 使用迭代计算（文件 → 选项 → 公式）
- 重新设计计算流程
17.5.5 显示公式
快捷键 `Ctrl + `` （反引号键）
效果
- 所有单元格显示公式而非结果
- 自动调整列宽以显示完整公式
- 再按一次恢复正常显示
应用场景
- 检查整个工作表的公式
- 打印公式文档
- 教学演示
17.6 实战案例
案例1：工资表计算
A B C D E F G
1 姓名 基本工资 绩效 补贴 应发工资 个税 实发工资
2 张三 8000 2000 500 =SUM(B2:D2) =IF(E2>5000,(E2-5000)*0.03,0) =E2-F2
3 李四 10000 3000 800 =SUM(B3:D3) =IF(E3>5000,(E3-5000)*0.03,0) =E3-F3
名称定义
- 起征点 = 5000
- 税率 = 0.03
优化后的公式
F2: =IF(E2>起征点,(E2-起征点)*税率,0)
案例2：销售提成计算
需求：销售额不同，提成比例不同
- 0-10000：3%
- 10000-50000：5%
- 50000以上：8%
=IF(A2<=10000, A2*0.03,
IF(A2<=50000, 10000*0.03+(A2-10000)*0.05,
10000*0.03+40000*0.05+(A2-50000)*0.08))
优化方案：使用VLOOKUP
# 建立提成比例表
销售额下限 提成比例
0 3%
10000 5%
50000 8%
# 公式
=VLOOKUP(A2,提成表,2,1)*A2
案例3：考勤统计
A B C D E F
1 日期 上班 下班 工时 加班 备注
2 1/1 9:00 18:00 =C2-B2 =IF(E2>8,E2-8,0) =IF(F2>0,"有加班","")
注意：时间格式需设置为"hh:mm"
案例4：九九乘法表
B C D E F G H I J K
1 1 2 3 4 5 6 7 8 9
2 1 =$A2*B$1 =$A2*C$1 ...
3 2 =$A3*B$1 =$A3*C$1 ...
...
B2单元格公式：=$A2*B$1，向右下填充即可。
17.7 最佳实践建议
17.7.1 公式编写规范
保持简洁
- 避免过长的公式（超过3层嵌套考虑拆分）
- 复杂计算分步骤进行
- 使用辅助列存储中间结果
使用名称
- 重要常量定义为名称
- 关键区域使用名称引用
- 提高公式可读性
添加注释
- 在相邻单元格添加公式说明
- 使用插入 → 批注功能
- 重要公式记录计算逻辑
17.7.2 性能优化
避免易失性函数
NOW(),TODAY(),RAND()等函数每次重算都会变化- 导致工作簿频繁重新计算
- 仅在必要时使用
减少数组公式
- 数组公式计算量大
- 能用普通公式就不用数组公式
- 考虑使用辅助列替代
限定计算区域
- 避免整列引用：
A:A - 使用具体区域：
A1:A1000 - 减少不必要的计算范围
17.7.3 数据验证
使用数据有效性
- 限制输入范围防止错误
- 设置下拉列表规范输入
- 添加输入提示
公式保护
- 锁定公式单元格
- 保护工作表防止误删
- 只开放数据输入区域
本章小结
本章系统学习了Excel公式的基础知识：
核心要点
- 公式语法：以
=开头，遵循运算优先级 - 引用方式：相对引用、绝对引用、混合引用的应用场景
- 运算符：算术、比较、文本、引用运算符的使用
- 名称管理器：提高公式可读性和维护性
- 审核调试：追踪引用、公式求值、错误处理
学习建议
- 多练习F4键切换引用类型
- 养成使用名称的习惯
- 掌握IFERROR等错误处理函数
- 学会使用公式审核工具排查问题
下一步学习
- 第20章：Excel常用函数详解
- 第21章：Excel数据清洗与整理
- 第23章：Excel高级函数应用
思考练习
- 创建一个个人消费记录表，使用公式自动计算总支出、分类支出占比
- 制作学生成绩表，用公式计算总分、平均分、排名
- 设计一个简单的进销存系统，使用公式计算库存余额
- 尝试用混合引用制作一个汇率换算表
- 练习使用名称管理器优化现有工作表的公式
练习提示
- 注意引用类型的选择
- 合理使用绝对引用固定常量
- 使用名称提高公式可读性
- 添加错误处理避免显示错误值