Excel中的绝对引用是什么

Excel中的绝对引用是什么

想知道 Excel 中的绝对引用是什么？立即阅读本必备 Excel 教程，了解有关绝对引用的所有知识。

您可以引用单元格和单元格区域来执行动态计算，而不是将值硬编码到公式中。当您将公式复制到另一个单元格、向下填充列或向右填充行时，这些引用会自动更新。

但您可能不希望公式的某些单元格引用自动更新。您可能希望一个单元格范围保持不变，而其他单元格范围根据新网格位置的列和行进行更新。Microsoft Excel 包含了一种绝对引用技术，可让您对公式引用进行如此出色的控制。

读完本 Excel 指南并按照练习来控制准确的单元格范围引用。

推荐：YITH WooCommerce Frequently Bought Together

Excel 中的绝对引用是什么？

在 Excel 中，绝对引用是一种固定单元格或单元格区域的方法，这样当您将公式复制到另一个单元格时，它们不会发生变化。此外，如果您将公式填充到列的下方或行的右侧，引用也不会发生变化。

假设您在单元格中有一个公式，用于将单元格和A1中的数字相加。如果您将此公式复制到单元格，您会希望它将和 中的数字相加，对吗？但是使用绝对引用，您可以确保它始终将和 中的数字相加，无论您将公式复制到哪里。B1C1A2B2C2B1C1

以下是一个例子

假设单元格中有以下公式A1。

=B1+C1

以上是Excel中的相对引用。 如果您将此公式复制到A2没有绝对引用的单元格中，它将变为以下内容：

=B2+C2

但是，如果使用$在列字母和行号前带有符号的绝对引用，则公式在您复制或填充的任何地方都保持不变，如下所示：

=B$1+C$1

以下是 Excel 中绝对引用的一些重要用途：

• 创建模板，其中某些值应始终保持不变。
• 根据固定值计算百分比或比率。
• 在保持某些引用不变的情况下分析多个工作表中的数据。

何时在 Excel 中使用绝对引用？

绝对引用非常方便的一种情况是，当您处理涉及固定值或常量的公式时。通过使用绝对引用，您可以确保公式始终引用目标单元格，这对于准确计算至关重要。例如，当根据固定税率计算销售税时，您需要对包含税率的单元格使用绝对引用。

此外，绝对引用有利于减少存储空间的使用并简化公式。您无需在公式中或跨不同单元格多次重复相同的值，只需使用绝对引用引用一次即可。

假设您需要计算员工添加奖金部分后的净工资。

一种情况是您上面看到的数据集。您可以为奖金部分创建一个列，并使用相对公式将B 列和C列的单元格相加。如果每个员工的奖金金额不同，这是可以接受的。

但是，如果所有员工的奖金部分都相同，则创建第二列没有任何意义。

您通常会为奖金部分创建一个单元格，然后将工资单元格和奖金金额单元格添加到净工资单元格中以获得最终工资。例如，G3+ J3。

当您复制此公式或将其填入所有其他员工的表格时，您将得到不准确的结果。因为，由于 Excel 的默认相对引用系统，只要您将公式复制到原始单元格下方的单元格，Excel 就会更改单元格引用。

例如，G4+J4单元格中包含工资H4，且为空。因此，您会得到错误的结果。G4J4

在这里，您必须包含J3包含奖金金额的 作为绝对引用，如上图所示。现在，如果您填写H 列，Excel 将H4添加J3、H5等等J3。

使用附加列来对固定值使用相对引用是非常低效的存储。如果您的工作簿包含数百万个数据点，您就会亲眼见证这一点。

例如，如果一列中的一个单元格使用 20 个字符，并且该列包含 1,048,576 个数据单元格，则您的 Excel 工作簿的大小将增加 20 MB。通过对奖励组件使用绝对引用系统，您可以将此存储空间节省在 PC 驱动器或云服务器上。

推荐：Divi Shop Builder For WooCommerce

如何在 Excel 中应用绝对引用

您可以$在列字母或行号前使用符号在 Excel 中应用绝对单元格或单元格范围引用。

假设，我需要纠正上述数据集中C 列单元格中的公式，以便它始终引用E11而不是相对地更改为E12、C12等等。E13C13

我将选择单元格C11并按下F2以进入编辑模式。

然后，我将光标移到前面，通过添加符号将对E 列E11的相对引用更改为绝对引用。$

再次，我将光标放在11参考行号之前E11并添加$符号。

我将通过按下来计算单元格Enter。

要将此公式应用于C 列的其余单元格，我将使用填充柄。

按照上述方法将相对引用转换为绝对引用时，您可能会遇到挑战。

使用以下秘密技巧可以轻松循环遍历不同类型的绝对引用：

• 选择参考，如E11，然后F4按应用完整绝对参考。
• 突出显示一个单元格范围，如E11，然后按F4两次以应用混合绝对引用，其中列是相对的，但行是绝对的。
• 选择一个单元格或单元格范围地址，如E11，然后按F4三次以应用混合绝对引用，其中列是绝对的，但行是相对的。
• 选择一个单元格或单元格范围后按F4四次以删除所有类型的绝对引用。

如何在 Excel 中使用绝对引用

以下是绝对参考使用的不同组合的多种场景：

使用绝对列和行引用

假设您想在员工月薪中添加奖金部分。在您创建的数据集中，月薪位于G 列。

您已在单元格中输入了奖金金额J3。

现在，您想要计算H 列中的净工资。

在H 列中，选择第一位员工的单元格并输入以下公式：

=G3+$J$3

在上面的公式中，对于单元格引用J3，列和行都是绝对的。

现在，点击Enter计算净工资。

将填充柄从第一个计算单元格向下拖至H 列，以根据需要将公式应用于其余单元格。

使用绝对列和相对行引用

当您想要跨多行但在固定列范围内执行计算或分析时，通常会使用绝对列引用和相对行引用。这种设置通常适用于您在各列之间水平排列数据，并且想要对每一行应用一致的计算或分析的情况。

例如，在上面的数据集中，您想要找出给定设备的欧元和加元价格。

要将美元兑换成上述任何一种货币，您需要当前汇率。您已将这些数据排列在行中2，将美元兑换成欧元排列在C 列中，将美元兑换成加元排列在D 列中，等等。

要计算第一台设备的欧元价格，请选择C2并输入以下公式：

=$B5*C$2

上式中，第一个单元格地址的列数固定，行数可变；第二个单元格引用的列数可变，行数固定。

按下Enter即可获取iPhone 15 的欧元价格。

当您将此公式复制到右侧C2或将其填充到右侧时，Excel 会自动调整公式以引用单元格中的美元兑加元汇率，D2同时保持产品价格参考不变，即B5。

总之，如果一组参考值排列在一行中，另一组参考值排列在列中，并且您想要从左到右复制或填充，则必须使用绝对列和相对行引用。

此示例还向您展示了在引用行中的汇率时使用绝对行和相对列组合的绝对引用2。

推荐：使用Virtual Box软件在Windows电脑上运行macOS

使用 VBA 在 Excel 中自动执行绝对引用

在 Excel 中使用绝对引用约定时，您可能会遇到许多瓶颈。以下是一些有用的VBA 宏，它们可以帮助您解决这些障碍。

突出显示包含绝对引用的单元格

这个简单的 VBA 脚本使您能够找到选定单元格范围内包含绝对引用的所有公式单元格：

Sub HighlightAbsoluteReferences()
    Dim rng As Range
    Dim cell As Range
    Dim formula As String
    Dim i As Integer

    ' Prompt the user to select a range
    On Error Resume Next
    Set rng = Application.InputBox("Select a range", Type:=8)
    On Error GoTo 0

    ' If no range is selected, exit the subroutine
    If rng Is Nothing Then Exit Sub

    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell contains a formula
        If cell.HasFormula Then
            formula = cell.formula
            ' Check if the formula contains an absolute reference
            If InStr(formula, "$") > 0 Then
                ' Highlight the cell
                cell.Interior.Color = RGB(255, 151, 151)
            End If
        End If
    Next cell
End Sub

当您运行此宏时，Excel 会要求您选择用于绝对引用检测的输入单元格范围。

一旦您提供单元格范围，Excel 将以RGB(255, 151, 151)突出显示单元格。

阅读此快速 Excel 教程，了解使用上述脚本设置 VBA 宏的技术：

在公式中创建绝对引用

以下脚本将指导您在所选公式中将相对引用转换为绝对引用：

Sub ConvertToAbsolute()
    Dim rng As Range
    Dim formula As String
    Dim cols As String
    Dim rows As String
    Dim colArray() As String
    Dim rowArray() As String
    Dim i As Integer

    ' Prompt user to select a cell with a formula
    On Error Resume Next
    Set rng = Application.InputBox("Select a cell with a formula", Type:=8)
    On Error GoTo 0

    If rng Is Nothing Then Exit Sub

    formula = rng.formula

    ' Prompt user to enter column letters to be made absolute
    cols = Application.InputBox("Enter column letters to be made absolute, separated by commas. Current formula: " & formula, Type:=2)
    colArray = Split(cols, ",")

    ' Prompt user to enter row numbers to be made absolute
    rows = Application.InputBox("Enter row numbers to be made absolute, separated by commas. Current formula: " & formula, Type:=2)
    rowArray = Split(rows, ",")

    ' Convert specified column letters and row numbers to absolute references
    For i = LBound(colArray) To UBound(colArray)
        formula = Replace(formula, colArray(i), "$" & colArray(i), , , vbTextCompare)
    Next i

    For i = LBound(rowArray) To UBound(rowArray)
        formula = Replace(formula, rowArray(i), "$" & rowArray(i))
    Next i

    ' Update the formula in the selected cell
    rng.formula = formula

    ' Show the updated formula in an input box
    MsgBox "The updated formula is: " & formula
End Sub

脚本将以文本格式显示一个输入框，其中包含所选公式。您可以告诉 Excel 您要将哪些列修改为以逗号分隔的绝对引用。

类似地，在下一个提示中，您可以输入要更改为绝对引用的行号。

完成后，脚本将使用新公式更新选定的单元格并将其显示在另一个输入框中。

创建命名范围以用作绝对引用

您无需使用符号修改单元格引用$，只需将其标记为命名范围即可。然后，在创建公式时，键入首字母，以便 Excel 会显示与您输入的前几个字母匹配的适当命名的范围列表。

以下 VBA 脚本允许您从任何单元格或单元格范围直观地创建命名范围，而无需记住基于名称管理器的过程。

Sub CreateNamedRange()
    Dim rng As Range
    Dim strName As String

    ' Show an input box to select the cell or cell range
    On Error Resume Next
    Set rng = Application.InputBox(Prompt:="Please select the cell or cell range", Type:=8)
    On Error GoTo 0

    ' If no range is selected, exit the subroutine
    If rng Is Nothing Then
        MsgBox "No range selected. Exiting..."
        Exit Sub
    End If

    ' Show another input box to write the name of the named range
    strName = Application.InputBox(Prompt:="Please enter the name for the named range", Type:=2)

    ' If no name is entered, exit the subroutine
    If strName = "" Then
        MsgBox "No name entered. Exiting..."
        Exit Sub
    End If

    ' Create the named range
    ThisWorkbook.Names.Add Name:=strName, RefersTo:=rng

    ' Show a confirmation message
    MsgBox "Named range '" & strName & "' has been created successfully."
End Sub

您可以运行上述脚本，Excel 将以视觉方式指导您通过两个简单的步骤创建命名范围。

第一步，选择单元格或单元格区域并命名以完成该过程。

⚠️警告：在使用上述任何 VBA 脚本之前，请先备份原始工作簿。因为如果您运行 VBA 宏，您将无法使用Excel 撤消功能恢复到以前的状态。

结论

现在您知道了 Excel 中的绝对引用是什么。您还了解了何时使用此类引用约定。此外，您还了解了如何通过几次单击创建不同类型的绝对引用。

通过本 Excel 教程，我还解释了 VBA 如何帮助您准确使用绝对参考系统。

推荐：如何在Excel中添加趋势线