如何在 Excel 中插入或刪除行時始終獲取上方單元格的值？

Author Xiaoyang Last modified 2025-08-23

在 Excel 中，引用正上方單元格的值是創建累計總數、月度比較或僅僅隨著工作表的發展保持數據一致性的一個常見需求。通常，您可以使用像 =D5 這樣簡單的公式來引用上方單元格，但這種解決方案有其局限性：如果您插入或刪除行，您的公式可能不會如您預期地繼續引用新的“上方”單元格。如下圖所示，插入新行可能會破壞連續性，或使引用仍然連結到原始單元格而不是調整到新位置。

A screenshot showing how the reference to the above cell breaks after inserting a row in Excel

要解決這個問題並確保您的公式即使在插入或刪除行後仍然可以獲取正上方單元格的值，您可以採取幾種方法。每種方法都根據您的工作表複雜性、是否希望自動更新還是手動公式以及是否熟悉使用 VBA/宏而有不同的權衡。

目錄：

插入或刪除行時始終獲取上方單元格的值（使用公式）
使用 VBA 事件驅動的宏來自動更新單元格的值（始終動態）

插入或刪除行時始終獲取上方單元格的值（使用公式）

要以一種不需要宏或複雜設置的簡便方式解決這個問題，您可以使用一個動態引用正上方單元格的公式，無論您的行如何變化。該公式使用 Excel 的 INDIRECT 和 ADDRESS 函數，使得引用始終“追蹤”正上方的單元格，即使由於插入或刪除行而導致行移動。這對於經常修改行結構的工作表特別有用，例如在列表的頂部或中間添加新數據。

將以下公式直接輸入到您希望始終獲取上方單元格值的單元格中（例如，如果要在 B6 單元格中引用 B5，則輸入以下公式）：

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

輸入公式後按 Enter 鍵。當前單元格將立即顯示正上方單元格的值，如下所示：

A screenshot showing the formula to reference the above cell in Excel using INDIRECT and ADDRESS

現在，如果在引用公式的單元格上方插入新行，公式將重新計算並始終顯示正上方單元格的新值。這樣，您的公式無論插入或刪除行都能保持最新。以下是供參考的截圖：

A screenshot showing the correct cell reference maintained after inserting a row in Excel

參數解釋和提示：

此公式將提取正上方單元格的值——因此在 B6 使用它將始終反映 B5 的值，即使在上方插入或刪除行也一樣。
如果您在數據的第一行（例如 A1）中使用此公式，它可能會嘗試從不存在的行中提取數據並返回 #REF! 錯誤。您可通過添加錯誤處理來避免這種情況，例如使用 =IF(ROW()=1,"",INDIRECT(ADDRESS(ROW()-1,COLUMN()))) 在第一行顯示空白。
請記住，INDIRECT 是一個易變函數，因此在極大的工作表中，過度使用會減慢計算速度。
當您希望保持與行位置的嚴格關係時，無論工作表結構如何改變，使用此公式都很有效。

故障排除和總結建議：
如果在插入或刪除行後公式未按預期更新，請仔細檢查是否已將其輸入到目標單元格中。此外，確保您未使用絕對單元格引用（例如 $A$1），因為它們是靜態的。如果遇到 #REF! 第一行中的錯誤，考慮使用前面提到的條件公式。對於高級自動化，或者如果您需要複製值而不只是引用，請參閱下面的 VBA 事件驅動宏解決方案，以獲得動態且基於代碼的方法。

使用 VBA 事件驅動的宏自動更新單元格的值（始終動態）

如果您需要一種解決方案，無論插入或刪除行，單元格值總是能自動更新以匹配上方單元格的值——並且希望這種行為無需複製公式即可管理——使用事件驅動的 VBA 宏非常有效。此方法適合熟悉啟用宏的用戶，並允許動態更新：每當您選擇特定單元格或範圍，或者更改值時，宏都可以即時將目標單元格設置為始終等於上方的值，從而使數據輸入一致，無論工作表如何改變。這對於模板尤其有用，在模板中，您希望每一新行都有固定的模式或默認值設置。

以下是使用 Worksheet_Change 事件設置此功能的方法：

1. 右鍵單擊您希望此功能所在的表格標籤，然後選擇查看代碼。Microsoft Visual Basic for Applications 編輯器將打開正確的工作表模塊。

2. 將以下 VBA 代碼複製並粘貼到工作表模塊窗口中：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    On Error Resume Next
    ' Set the range you want to monitor (for example, B2:B100)
    Set WatchRange = Intersect(Target, Me.Range("B2:B100"))
    
    If Not WatchRange Is Nothing Then
        Application.EnableEvents = False
        Dim cell As Range
        
        For Each cell In WatchRange
            ' Avoid the first row, or adjust as needed
            If cell.Row > 1 Then
                cell.Value = Me.Cells(cell.Row - 1, cell.Column).Value
            End If
        Next cell
        
        Application.EnableEvents = True
    End If
End Sub

參數注意：將 Me.Range("B2:B100") 中的 "B2:B100" 替換為您希望此行為實際發生的範圍（您可以將其設置為整個列，例如 "B:B"，但縮小範圍可提高性能並避免意外覆蓋）。

3. 關閉 VBA 編輯器。現在，只要在指定範圍內更改、插入單元格或更新工作表，Excel 將自動更新這些單元格以反映上方的值。例如，如果在第 5 行插入一行，那麼從該點開始監控的 B 列所有單元格將獲取其新位置正上方單元格的值。

請謹慎：此代碼將覆蓋監控範圍內手動輸入的值。如果您有公式單元格或希望保留原始條目，請小心使用。
如果您希望今後在此工作簿中繼續使用此 VBA 宏，則需要將文件保存為啟用宏的工作簿 (.xlsm)。
事件代碼只會在粘貼宏的工作表模塊上起作用（除非在每個工作表模塊中添加代碼，否則不會跨所有工作表生效）。
如果您希望在選擇單元格時而不是在更改值時進行更新，可以使用 Worksheet_SelectionChange 並使用類似的邏輯。

故障排除和總結建議：
如果在複製 VBA 腳本後似乎不起作用，請確保您的工作簿中已啟用宏，並且您已將代碼粘貼到正確的工作表模塊中（而非標準模塊）。如果出現錯誤或 Excel 卡住，請仔細檢查 Application.EnableEvents 是否在自動單元格更改之前設置為 False，之後重置為 True，以避免遞歸循環。對於其他高級行為或更精確的控制，請根據您的數據結構考慮自定義腳本。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多