如何在 Excel 中插入或刪除列時，始終取得上方儲存格的值？

作者Xiaoyang修改日期2025-07-24

在 Excel 中，參照正上方儲存格的值是常見需求，適用於建立累計總和、進行逐月比較，或單純在工作表演進過程中維持資料一致性。通常您可使用如 =D5 這類直接公式來參照上方儲存格，但此方法有其限制：當您插入或刪除列時，公式可能無法如預期般持續參照新的「上方」儲存格。如下圖所示，在上方插入新列可能導致連續性中斷，或使參照仍連結至原始儲存格，而非自動調整至新位置。

螢幕截圖顯示在 Excel 中插入列後，對上方儲存格的參照中斷

為解決此問題，確保公式在插入或刪除列後仍能穩定取得緊鄰上方儲存格的值，可採用多種方法。各方法各有優劣，取決於您的工作表複雜度、是否需要自動更新或手動調整公式，以及您對 VBA／巨集的熟悉程度。

目錄：

在插入或刪除列時，使用公式始終取得上方儲存格的值
透過 VBA 事件驅動巨集自動從上方儲存格更新儲存格值（始終保持動態）

在插入或刪除列時，使用公式始終取得上方儲存格的值

若想以無需巨集或複雜設定的簡便方式解決此問題，可使用能動態參照上方儲存格的公式，無論列如何變動皆不受影響。此公式結合 Excel 的 INDIRECT 與 ADDRESS 函數，確保參照始終「追蹤」上方儲存格，即使因插入或刪除導致列位移亦然。此方法特別適用於經常調整列結構的工作表，例如在清單頂端或中間新增資料！

直接在欲始終取得上方值的儲存格中輸入下列公式（例如在 B6 中參照 B5）：

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

輸入公式後按下 Enter，目前儲存格將立即顯示其正上方儲存格的值，如下圖所示：

螢幕截圖顯示在 Excel 中使用 INDIRECT 與 ADDRESS 函數來參照上方儲存格的公式

現在，只要在公式儲存格上方任意位置插入新列，公式就會自動重新計算，並即時反映最新上方儲存格的值。無論您插入或刪除列，公式始終保持最新狀態，確保資料永遠準確無誤。請參閱下圖作為參考：

螢幕截圖顯示在 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

參數說明：請將 "B2:B100" 替換為實際需要套用此行為的儲存格範圍（您也可以設定為整欄，例如）"B:B"，但縮小範圍有助提升效能並避免意外覆寫）。

3. 關閉 VBA 編輯器。現在，只要在限定區域內變更、插入儲存格或更新工作表，在該操作完成後，Excel 就會自動將這些儲存格更新為反映其新位置正上方儲存格的值。例如，若您在第 5 列插入新列，則從該位置起監控範圍內的所有 B 欄儲存格都會取得其新上方儲存格的值。

請謹慎：此程式碼會覆寫監控範圍內的手動輸入值。若您有公式儲存格或希望保留原始輸入內容，請小心使用。
若您日後希望繼續在活頁簿中使用此 VBA 巨集，需將檔案另存為啟用巨集的活頁簿（.xlsm）。
事件程式碼僅在貼上巨集的工作表模組中生效（除非每張工作表模組都加入該程式碼，否則不會套用至所有工作表）。
若您希望在選取儲存格時立即觸發更新，而非等到變更值才執行，可改用 Worksheet_SelectionChange 並搭配類似邏輯。

疑難排解與摘要建議：
若複製 VBA 指令碼後似乎無效，請確認活頁簿已啟用巨集，且程式碼已貼至正確的工作表模組（非標準模組）。若出現錯誤或 Excel 當機，請再次確認在自動變更儲存格前已將 Application.EnableEvents 設為 False，並在變更後重設為 True，以避免遞迴迴圈。若需其他進階行為或更精細的控制，請根據您的資料結構考慮自訂指令碼。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多