如何将Excel中的多个单元格的相对引用替换为绝对引用

需求场景

在Excel表中，每周更新最新的发布情况，插入到固定位置。因此，需要保证插入新行后，表中的公式使用绝对引用。

现在，表中有多个单元格使用的是相对引用，需要全部替换为绝对引用

解决方案

1、逐一修改

选中要调整的单元格，光标移到引用的位置上，按键盘F4可调整引用方式。

2、VBA宏整体替换

• 按下 Alt + F11 打开VBA编辑器。
• 在VBA编辑器中，插入一个新的模块（右键点击任意项目，选择"插入" -> "模块"）。

• 在新模块中粘贴以下VBA代码：

相对引用→绝对引用（始终引用该单元格的数据）

Sub ConvertToAbsoluteReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

• 关闭VBA编辑器。
• 选中包含公式的多个单元格或单元格区域。
• 运行刚才创建的宏：可以按下 Alt + F8，选择"ConvertToAbsoluteReferences"，然后点击"运行"。

这个VBA宏会遍历选中的单元格，对包含公式的单元格进行转换，将公式中的相对索引改为绝对索引。

---

补充：其他情况下的VBA代码

相对引用替换

相对引用→行绝对引用（始终引用一行的数据）

Sub ConvertToRowLockedReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRowAbsolute)
        End If
    Next cell
End Sub

相对引用→列绝对引用（始终引用一列的数据）

Sub ConvertToColumnLockedReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlColumnAbsolute)
        End If
    Next cell
End Sub

 相对引用→绝对引用（始终引用该单元格的数据）

Sub ConvertToAbsoluteReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

行绝对引用替换

行绝对引用→相对引用

Sub ConvertRowAbsoluteToRelative()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRelative)
        End If
    Next cell
End Sub

行绝对引用→列绝对引用

Sub ConvertRowAbsoluteToColumnAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsoluteRowRelativeColumn)
        End If
    Next cell
End Sub

行绝对引用→绝对引用

Sub ConvertRowAbsoluteToAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

列绝对引用替换

列绝对引用→相对引用

Sub ConvertColumnAbsoluteToRelative()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRelativeColumnAbsoluteRow)
        End If
    Next cell
End Sub

列绝对引用→行绝对引用

Sub ConvertColumnAbsoluteToRowAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRowAbsoluteColumnRelative)
        End If
    Next cell
End Sub

列绝对引用→绝对引用

Sub ConvertColumnAbsoluteToAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

绝对引用替换

绝对引用→相对引用

Sub ConvertAbsoluteToRelative()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRelativeRowRelativeColumn)
        End If
    Next cell
End Sub

绝对引用→行绝对引用

Sub ConvertAbsoluteToRowAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRowAbsolute)
        End If
    Next cell
End Sub

绝对引用→列绝对引用

Sub ConvertAbsoluteToColumnAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlColumnAbsolute)
        End If
    Next cell
End Sub