如何在Excel中記住或保存已更改單元格的先前單元格值？

網友投稿 1205 2025-03-31

如何在Excel中記住或保存已更改單元格的先前單元格值？

通常，當用新內容更新單元格時，會覆蓋以前的值，除非在 Excel 中撤消操作。但是，如果您想保留先前的值以與更新的值進行比較，將先前的單元格值保存到另一個單元格或單元格注釋中將是一個不錯的選擇。本文中的方法將幫助您實現它。

在Excel中使用VBA代碼保存以前的單元格值

驚人的！在 Excel 中使用高效的選項卡，如 Chrome、Firefox 和 Safari！ 每天節省50％的時間，并減少數千次鼠標單擊！

假設您有一個表格，如下所示。如果C列中的任何單元格發生更改，則要將其先前的值保存到G列的相應單元格中，或自動保存在注釋中。請按照以下步驟進行操作。

1.在工作表中包含更新時將保存的值，右鍵單擊工作表選項卡，然后選擇 查看代碼 從右鍵單擊菜單中。看截圖：

2.在開幕 Microsoft Visual Basic應用程序 窗口，將下面的VBA代碼復制到“代碼”窗口中。

以下VBA代碼可幫助您將指定列的先前單元格值保存到另一列中。

VBA代碼：將先前的單元格值保存到另一個列單元格中

Dim xRg As Range Dim xChangeRg As Range Dim xDependRg As Range Dim xDic As New Dictionary Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Long Dim xCell As Range Dim xDCell As Range Dim xHeader As String Dim xCommText As String On Error Resume Next Application.ScreenUpdating = False Application.EnableEvents = False xHeader = "Previous value :" x = xDic.Keys For I = 0 To UBound(xDic.Keys) Set xCell = Range(xDic.Keys(I)) Set xDCell = Cells(xCell.Row, 7) xDCell.Value = "" xDCell.Value = xDic.Items(I) Next Application.EnableEvents = True Application.ScreenUpdating = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim I, J As Long Dim xRgArea As Range On Error GoTo Label1 If Target.Count > 1 Then Exit Sub Application.EnableEvents = False Set xDependRg = Target.Dependents If xDependRg Is Nothing Then GoTo Label1 If Not xDependRg Is Nothing Then Set xDependRg = Intersect(xDependRg, Range("C:C")) End If Label1: Set xRg = Intersect(Target, Range("C:C")) If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = Union(xRg, xDependRg) ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = xDependRg ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then Set xChangeRg = xRg Else Application.EnableEvents = True Exit Sub End If xDic.RemoveAll For I = 1 To xChangeRg.Areas.Count Set xRgArea = xChangeRg.Areas(I) For J = 1 To xRgArea.Count xDic.Add xRgArea(J).Address, xRgArea(J).Formula Next Next Set xChangeRg = Nothing Set xRg = Nothing Set xDependRg = Nothing Application.EnableEvents = True End Sub

Copy

要在注釋中保存以前的單元格值，請應用以下VBA代碼

VBA代碼：在注釋中保存以前的單元格值

Dim xRg As Range Dim xChangeRg As Range Dim xDependRg As Range Dim xDic As New Dictionary Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Long Dim xCell As Range Dim xHeader As String Dim xCommText As String On Error Resume Next Application.ScreenUpdating = False Application.EnableEvents = False xHeader = "Previous value :" For I = 0 To UBound(xDic.Keys) Set xCell = Range(xDic.Keys(I)) If Not xCell.Comment Is Nothing Then xCell.Comment.Delete With xCell .AddComment .Comment.Visible = False .Comment.Text xHeader & vbCrLf & xDic.Items(I) End With Next Application.EnableEvents = True Application.ScreenUpdating = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim I, J As Long Dim xRgArea As Range On Error GoTo Label1 If Target.Count > 1 Then Exit Sub Application.EnableEvents = False Set xDependRg = Target.Dependents If xDependRg Is Nothing Then GoTo Label1 If Not xDependRg Is Nothing Then Set xDependRg = Intersect(xDependRg, Range("C:C")) End If Label1: Set xRg = Intersect(Target, Range("C:C")) If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = Union(xRg, xDependRg) ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then Set xChangeRg = xDependRg ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then Set xChangeRg = xRg Else Application.EnableEvents = True Exit Sub End If xDic.RemoveAll For I = 1 To xChangeRg.Areas.Count Set xRgArea = xChangeRg.Areas(I) For J = 1 To xRgArea.Count xDic.Add xRgArea(J).Address, xRgArea(J).Text Next Next Set xChangeRg = Nothing Set xRg = Nothing Set xDependRg = Nothing Application.EnableEvents = True End Sub

Copy

備注：在代碼中，數字7表示將先前的單元格保存到的G列，而C：C是將先前的單元格值保存的列。請根據您的需要進行更改。

3。點擊工具 > 參考資料 打開 裁判– VBAProject 對話框，檢查 Microsoft腳本運行時 框，最后單擊 OK 按鈕。看截圖：

4。按其他 + Q 關閉鍵 Microsoft Visual Basic應用程序 窗口。

從現在開始，當C列中的單元格值更新時，該單元格的先前值將保存到G列中的相應單元格中，或保存在注釋中，如下面的屏幕快照所示。

將以前的單元格值保存在其他單元格中：

在注釋中保存以前的單元格值：

最佳辦公生產力工具

將小時轉化為分鐘 Kutools for Excel!

準備好增強您的 Excel 任務了嗎？利用的力量 Kutools for Excel - 您終極的節省時間的工具。簡化復雜的任務并像專業人士一樣瀏覽數據。以閃電般的速度體驗 Excel！

為什么需要 Kutools for Excel

??? 超過 300 項強大功能: Kutools 包含 300 多項高級功能，可簡化您在 1500 多種場景中的工作。

?? 卓越的數據處理能力：合并單元格、刪除重復項并執行高級數據轉換 - 所有這些都不費吹灰之力！