提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何復制公式而不更改它在Excel中的單元格引用?

通常Excel調整單元格引用如果您將公式複製到您的工作表中的另一個位置。 您將不得不用一個美元符號($)修復所有的單元格引用或按F4鍵來切換相對絕對引用,以防止自動調整公式中的單元格引用。 如果您需要復制一系列公式,這些方法將非常繁瑣和耗時。 如果您想快速輕鬆地複制公式而不更改單元格引用,請嘗試以下方法:

精確地/靜態地複制公式,而無需更改Excel中的單元格引用

Kutools for Excel 精確複製 實用程序可以幫助您輕鬆地複制多個公式,而無需更改Excel中的單元格引用,防止相對單元格引用自動更新 全功能免費試用30天!
廣告精確的複制公式3

箭頭藍色右泡 複製公式而不更改其單元格引用 通過替換功能

在Excel中,可以使用Replace函數複製公式而不更改其單元格引用,步驟如下:

1。 選擇要復制的公式單元格,然後單擊 主頁 > 查找和選擇 > 更換,或按快捷鍵 CTRL + H 打開 查找和選擇 對話框。

2。 點擊 更換 按鈕,在 查找內容 框輸入“=“,並在 更換 框輸入“#“或其他任何與您的公式不同的標誌,然後點擊 “全部替換” 按鈕。
基本上,這將停止參考引用。 例如, ”= A1 * B1“成為”#A1 * B1“,並且可以移動它,而不會自動更改當前工作表中的單元格引用。

3。 而現在所有的“=“在選定的公式被替換為”#“。 並出現一個對話框,顯示已經做了多少替換。 請關閉它。 看上面的截圖:
而范圍中的公式將更改為文本字符串。 查看截圖:

4。 將公式複制並粘貼到當前工作表所需的位置。

5。 選擇兩個更改的範圍,然後顛倒步驟2。 點擊 主頁> 查找和選擇 >更換… 或按快捷鍵 CTRL + H,但這次輸入“#“在”查找內容“框中,以及”=“在”替換為“框中,然後單擊 “全部替換”。 然後公式已被複製並粘貼到另一個位置,而不更改單元格引用。 看截圖:


箭頭藍色右泡 通過將公式轉換為文本來複製公式而不更改其單元格引用

以上方法是將公式更改為文本,將=替換為#。 實際上,Excel的Kutools提供了這樣的工具 將公式轉換為文本 - 將文本轉換為公式。 您可以將公式轉換為文本並將它們複製到其他位置,然後將這些文本恢復為公式。

1。 選擇要復制的公式單元格,然後單擊 Kutools > 內容 > 將公式轉換為文本。 看截圖:

2。 現在選定的公式被轉換為文本。 請複制它們並粘貼到您的目的地範圍。

3。 然後,您可以通過選擇文本字符串並單擊來將文本字符串恢復為公式 Kutools > 內容 > 將文本轉換為公式。 看截圖:

Kutools for Excel -包括多個用於Excel的300便捷工具。 全功能免費試用30天,無需信用卡! 立即行動吧


箭頭藍色右泡 通過轉換為絕對引用來複製公式而不更改其單元格引用

作為相對引用的結果,複製後的公式會發生更改。 因此,我們可以將Kutools應用於Excel 轉換指令 實用程序將單元格引用更改為絕對,以防止在Excel中復制後更改。

1。 選擇要復制的公式單元格,然後單擊 Kutools > 轉換指令.

2。 在打開轉換公式引用對話框中,請檢查 絕對 選項,然後單擊 Ok 按鈕。 看截圖:

3。 複製公式並粘貼到您的目標範圍。

備註:如有必要,可以通過重用公式將單元格的引用恢復為相對 轉換指令 實用程序。

Kutools for Excel -包括多個用於Excel的300便捷工具。 全功能免費試用30天,無需信用卡! 立即行動吧


箭頭藍色右泡 通過Kutools for Excel複製公式而不更改其單元格引用

有沒有一種更簡單的方法來複製公式,而不用改變它的單元格引用這個快速和舒適? 實際上, Kutools for Excel 可以幫助您複製公式而無需快速更改其單元格引用。

1。 選擇要復制的公式單元格,然後單擊 Kutools > 精確的副本.

2。 在第一個 精確的公式拷貝 對話框,請點擊 OK。 在第二個精確公式對話框中,請指定目標範圍的第一個單元格,然後單擊 OK 按鈕。 看截圖:

小技巧: :複製格式選項將粘貼範圍後保持所有單元格格式,如果選項已被選中。
並且所有選定的公式都被粘貼到指定的單元格中,而不改變單元格的引用。 看截圖:

Kutools for Excel -包括多個用於Excel的300便捷工具。 全功能免費試用30天,無需信用卡! 立即行動吧


箭頭藍色右泡演示:複製公式而不更改Excel中的單元格引用

Kutools for Excel 包含多個用於Excel的300便捷工具,可以在30天內不受限制地免費試用。 立即下載並免費試用!

Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jihane · 1 years ago
    thank you so much, brilliant idea!!
  • To post as a guest, your comment is unpublished.
    Greg · 2 years ago
    Great tip, saved me hours of work. Thanks
  • To post as a guest, your comment is unpublished.
    Chris Wright · 2 years ago
    Ah! This reminds me of my days programming a mainframe computer, in the mid 1980s! All changes to text had to be done by "find/replace". There was no such thing as putting the cursor where you wanted, , replace text. The find/replace functions in editors are very powerful tools, if you use them properly. Thank you for the tip, which helped me solve my problem!
  • To post as a guest, your comment is unpublished.
    Aggeliki · 2 years ago
    Thank you so much!!
    Your "Copy formula without changing its cell references by Replace feature" tip worked perfectly and did the job in like 10 seconds.
    Brilliant!!
  • To post as a guest, your comment is unpublished.
    SHIVAM SHUKLA · 3 years ago
    SUPERB IDEA thnks for help me i am very happy...
  • To post as a guest, your comment is unpublished.
    Patrick · 4 years ago
    Wow, works like a charm. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Alessandro · 4 years ago
    astonished this is what i was looking for. you are smart
  • To post as a guest, your comment is unpublished.
    SANKAR KS · 4 years ago
    THANK YOU FOR U R IDEA
  • To post as a guest, your comment is unpublished.
    Madhu · 4 years ago
    Great website, wonderful solutions. Saving lot of time.
  • To post as a guest, your comment is unpublished.
    sifalio · 4 years ago
    Anoher way to do it is:
    ctrl+' will display all formulas
    copy the whole area you need.
    open notepad
    paste it there
    copy from notepad
    paste in the desired area.
    done :)

    cheers
  • To post as a guest, your comment is unpublished.
    JeffDDD · 5 years ago
    This was the answer. You rock.
  • To post as a guest, your comment is unpublished.
    Hellooooo · 5 years ago
    Just go to Formula Tab, select Show Formula, Formulas will now display, copy them, paste into notepad, copy again from notepad and paste back into excel...
  • To post as a guest, your comment is unpublished.
    Sultan · 5 years ago
    Thanks a lot , great job :D
  • To post as a guest, your comment is unpublished.
    Cokedie · 5 years ago
    Great tip, this is the only trick... People who dont appreciate its just because they dont understand why we need this trick. TQ.
  • To post as a guest, your comment is unpublished.
    Anwar · 5 years ago
    Thanks for very good tip! :lol:
  • To post as a guest, your comment is unpublished.
    JH · 5 years ago
    Just copy the whole sheet (right click sheet name tab -> Move or Copy... -> Create a copy)
  • To post as a guest, your comment is unpublished.
    Dayna · 5 years ago
    THANK YOU thank you thank you. Sometimes the easiest fix is the one that escapes me the most. :lol:
  • To post as a guest, your comment is unpublished.
    Russell · 5 years ago
    Great Tip. What is actually happening when you do this, is you are de-activating the formula by making it no longer a recognised formula with the 1st replace (so excel won't change it). Then after you've moved/deleted/ made your changes, the 2nd replace re-activates the formula, by making it a valid/recognised formula again. This was handy when someone had entered a basic formula for a running total, but then we had to delete an item from the total. Excel would try to update the formula and break the running total
  • To post as a guest, your comment is unpublished.
    ricky · 5 years ago
    its not good enough..........................................................................................
  • To post as a guest, your comment is unpublished.
    Viktor · 5 years ago
    Isn't it easier to just drag the area with the formulas to the new location? That won't change the cell references. (Mark the area with the formulas you want to move. Place the cursor on the edge of the marked area so that the symbol with four arrows (N, S, E, W) is displayed. Click and hold left mouse button and drag the area to desired location.) Good luck!
  • To post as a guest, your comment is unpublished.
    Peter · 5 years ago
    the # tip is super!! good job
  • To post as a guest, your comment is unpublished.
    Yare · 5 years ago
    Awesome stuff!! Thanks a lot!
  • To post as a guest, your comment is unpublished.
    BJ · 5 years ago
    Thanks very fucking much
  • To post as a guest, your comment is unpublished.
    tj · 5 years ago
    Just press F2, copy the whole formula, go to the new cell. F2. Paste the formula.
    • To post as a guest, your comment is unpublished.
      John McNutt · 5 years ago
      [quote name="tj"]Just press F2, copy the whole formula, go to the new cell. F2. Paste the formula.[/quote]
      This is a better option for myself and it works great; I don't need anymore add-ins.
      • To post as a guest, your comment is unpublished.
        VR · 5 years ago
        This isn't easy if you want to copy a range of cells and paste it. F2 is useful to copy just one cell and paste.
  • To post as a guest, your comment is unpublished.
    Adrian Busuttil · 5 years ago
    Very helpful - Thanks
  • To post as a guest, your comment is unpublished.
    Mayank · 5 years ago
    brilliant tip... simple and very effective!
  • To post as a guest, your comment is unpublished.
    davidjoneslocker · 5 years ago
    That's really clever.
  • To post as a guest, your comment is unpublished.
    pbash · 6 years ago
    Thank you. That helped.
  • To post as a guest, your comment is unpublished.
    name · 6 years ago
    Great tip, thanks for the help!
  • To post as a guest, your comment is unpublished.
    jack corvin · 6 years ago
    :-x great website
    :lol: [quote]fly like a butterfly sting like a bee [/quote]