複製沒有鏈接移位的公式

問題

假設我們有一個像這樣的簡單表格，其中計算了兩個城市每個月的金額，然後按照黃色單元格 J2 中的匯率將總額轉換為歐元。

複製沒有鏈接移位的公式

問題是，如果您將 D2:D8 範圍內的公式複製到工作表的其他位置，Microsoft Excel 將自動更正這些公式中的鏈接，將它們移動到新位置並停止計數：

複製沒有鏈接移位的公式

任務：用公式複制範圍，使公式不變，保持不變，保持計算結果。

方法一、絕對鏈接

從上圖可以看出，Excel 只移動相對鏈接。 對黃色單元格 $J$2 的絕對引用（帶有 $ 符號）沒有移動。 因此，為了精確複製公式，您可以暫時將所有公式中的所有引用轉換為絕對引用。 您將需要在公式欄中選擇每個公式並按 鍵 F4:
複製沒有鏈接移位的公式
對於大量單元格，這個選項當然會消失——太費力了。

方法 2：暫時禁用公式

為防止公式在復制時發生更改，您需要（暫時）確保 Excel 停止將它們視為公式。 這可以通過將等號 (=) 替換為公式中通常不存在的任何其他字符來完成，例如用於復制時間的井號 (#) 或與符號對 (&&)。 為了這：

  1. 使用公式選擇範圍（在我們的示例中為 D2:D8）
  2. 點擊 Ctrl + H 在鍵盤或選項卡上 主頁 – 查找和選擇 – 替換 （首頁—查找&選擇—替換）

    複製沒有鏈接移位的公式

  3. 在出現的對話框中，輸入我們要查找的內容和替換的內容，然後在 參數 （選項） 不要忘記澄清 搜索範圍 - 公式. 我們按 全部替換 （全部替換）.
  4. 將帶有已停用公式的結果範圍複製到正確的位置：

    複製沒有鏈接移位的公式

  5. 更換 # on = 使用相同的窗口返回，將功能返回給公式。

方法3：通過記事本複制

這種方法更快更容易。

按鍵盤快捷鍵 Ctrl+Ё 或按鈕 顯示公式 選項卡 式 （公式 - 顯示公式）, 打開公式檢查模式——單元格將顯示計算它們的公式，而不是結果：

複製沒有鏈接移位的公式

複製我們的範圍 D2:D8 並將其粘貼到標準中 筆記本:

複製沒有鏈接移位的公式

現在選擇粘貼的所有內容（Ctrl + A），再次將其複製到剪貼板（Ctrl + C）並將其粘貼到工作表上您需要的位置：

複製沒有鏈接移位的公式

它只剩下按下按鈕 顯示公式 （顯示公式）將 Excel 返回到正常模式。

注意：此方法有時在具有合併單元格的複雜表格上會失敗，但在絕大多數情況下它可以正常工作。

方法 4. 宏

如果您經常需要在不移動引用的情況下複製公式，那麼為此使用宏是有意義的。 按鍵盤快捷鍵 Alt + F11鍵 或按鈕 Visual Basic中 選項卡 開發人員 （開發商）, 通過菜單插入一個新模塊 插入 - 模塊  並在此處複製此宏的文本：

Sub Copy_Formulas() Dim copyRange As Range，pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("選擇帶有要復制的公式的單元格。", _ "精確複製公式"，默認值：=Selection.Address，類型：= 8) 如果 copyRange 為空，則 Exit Sub Set PasteRange = Application.InputBox("現在選擇粘貼範圍。" & vbCrLf & vbCrLf & _ "該範圍的大小必須等於原始 " & vbCrLf & _ " 單元格範圍複製。" , "精確複製公式", _ 默認值：=Selection.Address，類型：=8) 如果pasteRange.Cells.Count <> copyRange.Cells.Count 那麼MsgBox "複製和粘貼範圍的大小不同！", vbExclamation，“複製錯誤” Exit Sub End If If PastRange Is Nothing then Exit Sub Else patchRange.Formula = copyRange.Formula End If End Sub

您可以使用該按鈕運行宏。 宏 選項卡 開發人員 （開發者——宏） 或鍵盤快捷鍵 Alt + F8鍵. 運行宏後，它會要求您選擇原始公式的範圍和插入範圍，並自動複製公式：

複製沒有鏈接移位的公式

  • 方便的同時查看公式和結果
  • 為什麼 Excel 公式中需要 R1C1 引用樣式
  • 如何快速找到所有帶有公式的單元格
  • 從 PLEX 插件複製精確公式的工具

 

發表評論