如何在复制/粘贴后禁用 Excel 的自动单元格引用更改? [英] How to disable Excel's automatic cell reference change after copy/paste?

问题描述

我正在处理一个庞大的 Excel 2003 电子表格.有很多非常大的公式，有很多单元格引用.这是一个简单的例子.

I've got a massive Excel 2003 spreadsheet I'm working on. There are a lot of very large formulas with a lot of cell references. Here's a simple example.

='Sheet'!AC69+'Sheet'!AC52+'Sheet'!AC53)*$D$3+'Sheet'!AC49

它们中的大多数都比这更复杂，但这使我对我正在处理的内容有了很好的了解.这些单元格引用很少是绝对的($s).我希望能够将这些单元格复制到不同的位置，而不会更改单元格引用.我知道我可以简单地使用 f4 使引用成为绝对引用，但是有很多数据，我可能需要稍后使用 Fill.有什么方法可以暂时禁用在复制粘贴/填充时更改单元格引用而不使引用成为绝对引用?

Most of them are more complicated than that, but this gives a good idea of what I'm working with. Few of these cell references are absolute ($s). I'd like to be able to copy these cells to a different location without the cell references changing. I know I can simply use f4 to make the references absolute, but there is a lot of data and I may need to use Fill later. Is there any way to temporarily disable the cell reference changing on copy-paste/fill without making the references absolute?

我刚刚发现您可以通过将单元格内容复制为文本而不是公式来使用 VBA 执行此操作.我不想这样做，因为我想一次复制整行/列.我缺少一个简单的解决方案吗?

I just found out that you can do this with VBA by copying the cell contents as text instead of a formula. I'd like to not have to do this though because I want to copy whole rows/columns at once. Is there a simple solution I am missing?

推荐答案

来自 http://电子表格page.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:

1. 将 Excel 置于公式视图模式.最简单的方法是按 Ctrl+`(该字符是向后撇号"，通常位于具有 ~(波浪号)的同一个键上.
2. 选择要复制的范围.
3. 按Ctrl+C
4. 启动 Windows 记事本
5. 按Ctrl+V将复制的数据粘贴到记事本中
6. 在记事本中，按 Ctrl+A 然后按 Ctrl+C 复制文本
7. 激活 Excel 并激活要粘贴公式的左上角单元格.并且，确保您要复制到的工作表处于公式视图模式.
8. 按 Ctrl+V 粘贴.
9. 按 Ctrl+` 可退出公式查看模式.

1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
2. Select the range to copy.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.

注意:如果粘贴回 Excel 的操作无法正常工作，则可能是您最近使用了 Excel 的文本到列"功能，并且 Excel 正试图通过记住您上次解析数据的方式来提供帮助.您需要启动将文本转换为列向导.选择分隔选项并单击下一步.清除除 Tab 之外的所有分隔符选项复选标记.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.

或者，来自 http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/:

If you're a VBA programmer, you can simply execute the following code: 
With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

这篇关于如何在复制/粘贴后禁用 Excel 的自动单元格引用更改?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！