Excel公式中单元格引用锁定是什么？
在Excel中，我们创建公式时经常会引用其他单元格的值。这些引用可以是相对于公式所在位置的（相对引用），也可以是固定不变的（绝对引用），或者固定行但列可变，或者固定列但行可变（混合引用）。所谓“锁定公式”，实际上是指锁定公式中使用的单元格引用，使其在公式被复制或填充到其他位置时不会自动改变。这是通过在单元格引用的前面加上美元符号（$）来实现的。
默认情况下，Excel使用相对引用。例如，如果你在C1单元格输入公式 =A1+B1，然后将这个公式向下拖动复制到C2，Excel会自动将公式调整为 =A2+B2。这是因为Excel理解公式为“将当前行左边两列的单元格相加”，当公式移动到下一行时，这个相对关系依然成立，引用也会跟着下一行改变。
然而，很多时候我们需要公式中的某些引用固定不变，比如一个税率、一个总金额或者一个查找表范围。这时就需要“锁定”这些引用，让它们变成绝对引用或混合引用。
为什么要锁定Excel公式中的单元格引用？
锁定单元格引用最重要的原因是为了提高效率和确保准确性。
- 复制公式时的便利性： 当你需要将同一个公式应用于大量数据，而公式中的某些参数（如费率、总额、固定值）来源于表格中的一个特定单元格时，如果不对这个特定单元格的引用进行锁定，复制公式会导致这个引用也跟着变化，结果就会出错。通过锁定，你可以一步到位地将公式复制到任意位置，而无需手动修改每一个复制出来的公式。
- 引用固定值： 很多计算需要引用一个固定的数值，比如税率、折扣率、汇率等。将这些值放在一个单独的单元格中并在公式中引用并锁定，比直接将数值写死在公式里要好得多。因为如果这个数值需要修改，你只需要改动那个单独的单元格，所有引用它的公式都会自动更新，而不需要逐个修改公式。
- 处理数据区域： 在使用一些函数（如VLOOKUP、SUMIFS、COUNTIFS、AVERAGEIFS）时，函数参数中会包含一个数据查找或计算的区域。通常，这个区域是固定不变的。锁定这个区域的引用，可以确保无论你将公式复制到哪里，它总是作用于正确的、不变的数据范围。
- 构建复杂的表格和模型： 在创建财务模型、统计报表等复杂表格时，锁定引用是基础操作。它可以帮助你灵活地引用各种参数、中间结果和原始数据，构建出结构清晰、易于维护和扩展的公式体系。
Excel公式中可以在哪里锁定单元格引用？
你可以在Excel公式中任何需要固定不变的单元格引用上应用锁定。这通常包括：
- 引用单个固定数值的单元格： 如税率、折扣率、某个常数等。
- 引用计算结果的单元格： 如一个总和、平均值等，后续计算需要引用这个总和或平均值来计算比例或差值。
- 函数中的数据区域： 例如VLOOKUP函数的第二个参数（查找区域）、SUMIFS函数的第一个参数（求和区域）和第二个参数（条件区域）等。
- 需要固定行或列的混合引用场景： 例如，制作乘法表、九九乘法表，或者计算不同产品在不同地区的销售额时，可能需要固定引用产品的行或地区的列。
- 条件格式或数据验证的公式中： 这些公式通常需要引用特定的单元格或区域，锁定引用可以确保规则正确应用。
记住，你锁定的是单元格引用（如A1, B$2, $C3），而不是函数名（如SUM, VLOOKUP）或运算符（如+, -, *, /）。
Excel公式中单元格引用锁定有多少种类型？
Excel的单元格引用锁定（通过美元符号$）共有四种状态，可以理解为三种锁定类型加上一种默认的不锁定状态：
- 相对引用 (Relative Reference): 例如 A1。这是默认状态。当公式被复制或填充时，行号和列标都会根据公式的新位置相对改变。
- 绝对引用 (Absolute Reference): 例如 $A$1。在列标和行号前都加上了美元符号。当公式被复制或填充到任何位置时，这个引用始终指向固定的单元格 A1。行和列都被锁定了。
- 混合引用 (Mixed Reference) – 列绝对，行相对: 例如 $A1。只有列标前有美元符号。当公式被复制或填充时，列标（A）保持固定不变，但行号（1）会根据公式的新位置相对改变。列被锁定了，行是相对的。
- 混合引用 (Mixed Reference) – 列相对，行绝对: 例如 A$1。只有行号前有美元符号。当公式被复制或填充时，行号（1）保持固定不变，但列标（A）会根据公式的新位置相对改变。行被锁定了，列是相对的。
通过在引用中添加或不添加美元符号，并选择放置在列标前还是行号前，你就可以创建出这四种不同的引用类型，满足不同的复制需求。
如何对Excel公式中的单元格引用进行锁定？
锁定单元格引用主要有两种方法：手动输入美元符号或使用快捷键F4。
方法一：手动输入美元符号 ($)
在你编辑公式时，直接在需要锁定的单元格引用的列标或行号前输入美元符号（$）。
例如：
- 输入 $A$1 来创建绝对引用。
- 输入 $A1 来创建列绝对、行相对的混合引用。
- 输入 A$1 来创建列相对、行绝对的混合引用。
这种方法适用于你对锁定类型非常确定，并且引用数量不多的情况。
方法二：使用快捷键 F4 (推荐)
这是最常用、最方便的方法。在编辑公式时：
- 选中公式中你想要锁定（或改变锁定类型）的单元格引用部分。例如，在一个公式 `=C2*$D$1` 中，如果你想改变 `$D$1` 的锁定状态，就选中 `D$1`。
- 按下键盘上的 F4 键。
-
每次按下F4键，选中的引用都会在四种状态之间循环切换：
- 第一次按F4：从 A1 (相对) 变为 $A$1 (绝对)
- 第二次按F4：从 $A$1 变为 A$1 (行绝对)
- 第三次按F4：从 A$1 变为 $A1 (列绝对)
- 第四次按F4：从 $A1 变回 A1 (相对)
- 重复按F4，直到引用变为你需要的锁定类型。
- 完成公式编辑后，按Enter键确认。
使用F4键的好处是你可以快速尝试和切换不同的锁定类型，特别是当你不太确定应该锁定行还是列时。
Excel公式中如何使用（应用）单元格引用锁定？（具体场景举例）
通过具体的例子来理解如何应用单元格引用锁定是最好的方式。
场景一：计算占总销售额的百分比
假设你在A列有各种商品的销售额，B1单元格计算了总销售额，你需要在B列计算每种商品的销售额占总销售额的百分比。
- 数据：
A1: 商品A销售额 (例如 100)
A2: 商品B销售额 (例如 150)
…
A5: 商品E销售额 (例如 200)
B1: 总销售额 (公式 `=SUM(A1:A5)`, 结果 650) - 目标： 在B2、B3…B6单元格分别计算 A1/B1, A2/B1, …, A5/B1 的百分比。
- 问题： 如果在B2输入 `=A1/B1` 然后向下复制，B3会变成 `=A2/B2`，B4会变成 `=A3/B3`，引用B1的总销售额会跟着变化，这显然是错误的。
- 解决方法： 引用A列的销售额需要是相对的（A1, A2, …），而引用总销售额的B1则需要是绝对的。在B2单元格输入公式：=A1/$B$1。
在这里，A1 是相对引用，当公式向下复制时会变为A2, A3等。
$B$1 是绝对引用，无论公式复制到哪里，都固定引用B1单元格的值。 - 应用： 输入公式后，将B2单元格的公式向下拖动到B6。你会看到公式正确地变成了 `=A2/$B$1`, `=A3/$B$1` 等等，所有商品的销售额都正确地除以了B1中的总销售额。然后将B列单元格格式设置为百分比即可。
场景二：根据固定税率计算税金
假设A列是收入金额，C1单元格存放税率（例如 0.15，即15%），你需要在B列计算对应的税金。
- 数据：
A1: 收入1 (例如 1000)
A2: 收入2 (例如 1500)
…
C1: 税率 (例如 0.15) - 目标： 在B1、B2等单元格分别计算 A1*C1, A2*C1 等。
- 问题： 如果在B1输入 `=A1*C1` 然后向下复制，B2会变成 `=A2*C2`，B3会变成 `=A3*C3`，引用C1的税率会向下偏移，找不到税率导致错误。
- 解决方法： 引用A列的收入需要是相对的（A1, A2, …），而引用税率的C1则需要是绝对的。在B1单元格输入公式：=A1*$C$1。
在这里，A1 是相对引用，向下复制时变为A2, A3等。
$C$1 是绝对引用，向下复制时始终引用C1的税率。 - 应用： 输入公式后，将B1单元格的公式向下拖动，即可快速计算出所有收入对应的税金。
场景三：使用VLOOKUP进行查找
VLOOKUP函数常用于在一个数据表中查找匹配值。其基本语法是 `VLOOKUP(查找值, 查找区域, 返回列序号, [匹配类型])`。查找区域通常是固定不变的。
- 数据：
A列: 商品ID
B列: 需要查找的商品名称
E列和F列构成一个查找表：E列是商品ID，F列是对应的商品名称。 - 目标： 根据A列的商品ID，在E:F区域查找对应的商品名称，并填入B列。
- 问题： 在B1输入公式查找A1的商品名称，如果查找区域不锁定，向下复制时查找区域会偏移，导致查找失败。
- 解决方法： 查找值A1需要是相对的，而查找区域E:F需要是绝对的。在B1单元格输入公式：=VLOOKUP(A1, $E$1:$F$100, 2, FALSE)。
在这里，A1 是相对引用，向下复制时变为A2, A3等，查找下一个商品ID。
$E$1:$F$100 是绝对引用，无论公式复制到哪里，查找范围始终固定在E1到F100这个区域。 - 应用： 输入公式后，将B1单元格的公式向下拖动，即可快速填充所有商品ID对应的名称。
场景四：制作乘法表（混合引用应用）
假设你在A列有数字1-10，第一行（B1到K1）也有数字1-10，你想在B2到K11区域制作一个乘法表，即每个单元格的值是其对应行标和列标数字的乘积。
- 数据：
A2:A11: 1, 2, …, 10
B1:K1: 1, 2, …, 10 - 目标： 在B2单元格输入一个公式，然后向右向下填充，使得B2=A2*B1, B3=A3*B1, C2=A2*C1, C3=A3*C1, …, K11=A11*K1。
- 解决方法：
在B2单元格输入公式：=$A2*B$1。分析这个公式：
我们正在计算A列的数字（例如A2）与第一行的数字（例如B1）的乘积。
当我们向右复制时，希望A2的引用保持在A列不变，但B1的引用变为C1, D1等。所以A列的引用（$A2）需要锁定列（$A），行号（2）保持相对。
当我们向下复制时，希望B1的引用保持在第一行不变，但A2的引用变为A3, A4等。所以第一行的引用（B$1）需要锁定行（$1），列标（B）保持相对。
因此，我们需要一个列绝对行相对的引用（$A2）来引用A列的数字，和一个列相对行绝对的引用（B$1）来引用第一行的数字。 - 应用： 在B2输入 =$A2*B$1 后，先将公式向右拖动到K2，再将B2:K2选中的公式向下拖动到B11:K11。整个乘法表区域将被正确填充。
总结
理解和熟练运用单元格引用锁定（相对引用、绝对引用、混合引用）是高效使用Excel公式的关键。通过在单元格引用前添加美元符号（$）并结合F4快捷键，你可以精确控制公式复制时的引用变化行为，从而轻松处理各种需要引用固定单元格或区域的计算任务，极大地提高了工作效率和公式的准确性。在构建任何需要复制或填充的复杂公式之前，花时间思考哪些引用需要锁定是一个非常重要的习惯。
