如何在 Excel 中使用相对、绝对和混合引用
在 Excel 中创建公式时可以节省时间并引用正确的单元格。
要点
- Excel 单元格引用可以是相对、绝对或混合引用,以在公式中实现特定结果。
- 当复制到另一个单元格时,相对引用会发生变化,而绝对引用保持不变。
- 混合引用锁定公式中的列或行。
在 Excel 中处理大量数据时,您可以使用相对、绝对或混合引用来节省重复的手动工作,确保公式正确运行,并通过几个快速操作捕获大量数据。让我们探讨如何使用这些 Excel 引用类型。
什么是 Excel 单元格引用?
当您想要捕获另一个单元格或单元格区域中包含的信息时,可以使用单元格引用。例如,以下公式引用单元格 A1 和 A2,并将它们的内容加在一起以生成结果。
=SUM(A1+A2)
您可以使用以下类型的单元格引用来实现特定结果:
- 默认情况下,Excel 中的引用是相对,即单元格的相对位置。如果您在单元格 A1 中键入涉及单元格 A2 的公式,则您引用的单元格是您所键入单元格的下一个单元格。如果您要将公式复制到另一个单元格中,引用将自动执行与其先前位置相同的相对操作。
- 您还可以使用绝对引用,如果将公式复制到另一个单元格中,该引用不会改变。如果您创建使用绝对引用引用单元格的公式,则无论使用该公式,都将引用同一单元格。
- 最后,您可以使用混合引用,它是两者的组合。
让我们更详细地研究一下这些内容。
如何在 Excel 中使用相对引用
在此处显示的示例中,您可以查看此人每月在每个项目上花费的金额。他们现在想做的是计算出每个月的总支出。
首先,单击要进行第一个计算的单元格(在本例中为 H2),然后键入公式以创建此计算。在此示例中,您将使用以下公式:
=SUM(E2+E12+E22)
现在在计算中引用了单元格 E2、E12 和 E22,并将它们加在一起。这是相对引用 - 单元格 H2 中的公式引用了左侧三列 (E2)、左侧三列向下十行 (E12) 以及左侧三列向下二十行的单元格 ( E22),分别。
现在,如果您要将相同的公式复制并粘贴到其他地方,它会选取与粘贴位置相同的相对位置的值。您还可以使用 Excel 的自动填充功能使用相同的相对引用来完成其余数据。
正如您在此处看到的,单元格 H11 中的公式为:
=SUM(E11+E21+E31)
这是因为相对引用放置在比原来位置低九行的位置。现在,您不必键入每个月的公式,只需键入一次,然后在 Excel 工作表上的任意位置应用相同的相对引用。
如何在 Excel 中使用绝对引用
在此示例中,公司会计希望根据员工的工作小时数和固定的小时费率计算出向员工支付的工资。
首先单击要计算第一个总计的单元格(在本例中为 E2),然后开始键入适当的公式。在此示例中,会计希望将单元格 A2 中的小时费率乘以 Jacinda 在 D2 中的总工作时间。
=SUM(A2
但是,如果我们在这里使用相对引用,当我们将公式应用于其他员工的总计时,该公式将引用单元格 A3(代表 Raul)、A4(代表 Lucy),依此类推,这意味着您将无法获得所需的结果。
因此,我们需要确保 Excel 继续引用正确的单元格,在本例中为 A2。为此,您需要在参考文献的每个部分之前插入美元符号:
=SUM($A$2
现在这告诉 Excel,如果我们在其他地方使用相同的公式,A2 将始终被引用。
输入单元格引用后,不要手动输入美元符号,而是按 F4(您可能需要 了解功能键的作用 ,具体取决于您的键盘),并且美元符号将自动出现。
您现在可以完成您的公式。在这种情况下,它将如下所示:
=SUM($A$2*D2)
请注意公式 (D2) 的第二部分不使用绝对引用。这是因为我们要将单元格 A2 乘以每个员工各自的工作时间,因此需要与 D 列中每个人的工作时间相关。
现在,您可以通过复制并粘贴公式或使用 Excel 的自动填充功能将公式应用到其他单元格,因为单元格 A2 是绝对引用的。
如何在 Excel 中使用混合引用
有时您可能需要锁定公式中的行而不是列,反之亦然。例如,在本例中,会计师想要根据每个员工的个人税率计算出每个员工需要缴纳多少税款。
因此,我们需要锁定 E 列(税率)中的值,而不是行,因为它们对于每个员工来说都是不同的,并且不需要锁定年份的引用,因为它们需要具有适应性。
单击要进行第一次计算的单元格(在本例中为单元格 G2)并输入公式。在上面的示例中,会计师想要首先计算 Arjun 2021 年的应缴税款,因此他们将输入以下公式:
=SUM(B2*E2)
我们现在需要锁定E列。如果我们在不修改公式的情况下向右自动填充,则无法正确计算Arjun的2022年应纳税额;虽然它会准确地获取正确的年收入总额(因为我们没有锁定单元格 B2),但该公式还会引用单元格 F2。为此,请在公式中对 E 列的引用之前添加一个美元符号:
=SUM(B2*$E2)
我们不想在这里使用绝对参考,因为这意味着,当我们计算其他员工的应纳税额时,将使用 Arjun 的税率,而不是每个员工的个人税率。本质上,只锁定E栏意味着我们将保持税率不变,但可以相对调整以适应每个人的不同税率。
现在,我们可以使用自动填充函数来完成 Arjun 的应纳税总额,因为我们知道 E 列将在我们的计算中保持锁定状态,而每年的总额将相对于公式所在的位置移动。
因为我们已经锁定了 E 列,但在公式中保留了相关行,所以我们可以自信地根据剩余员工的个人税率完成他们的总计。请记住,您可以单击结果中的任何单元格并查看编辑栏中的公式以检查引用了正确的单元格。
如何在 Excel 中的不同类型的单元格引用之间切换
如果您想在 Excel 工作表中轻松地在相对、绝对和混合引用之间切换,则无需每次都键入美元符号。相反,单击要修改的单元格,然后在编辑栏中单击要切换的公式部分(直接在引用之前、引用中间或直接在引用之后单击都可以)。
在本例中,我们想要在单元格 G11 的单元格引用类型之间切换,因此我们需要确保单击该单元格引用上或旁边的某处。
然后,按F4,您将看到单元格引用类型发生变化。
它将首先切换到绝对引用:
$G$11
然后是锁定行的混合引用:
G$11
然后是锁定列的混合引用:
$G11
最后,它将恢复为默认值,即相对引用:
G11
如果您想同时切换同一公式中的多个引用,只需突出显示公式中的相关引用并按照相同的步骤操作即可。
你就完成了!谁知道仅仅存在美元符号就可以帮助您快速准确地组织 Excel 中的单元格引用?