复制/粘贴后如何禁用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://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2 ：

$ b
1. 将Excel置于公式视图模式。最简单的方法是按 Ctrl + `（该字符是向后撇号，通常位于带有〜（波浪号）的同一键上


2. 选择要复制的范围。


3. 按 Ctrl + C


4. 启动Windows记事本


5. 按 Ctrl + V 将复制的数据粘贴到记事本中


6. 在记事本中，按 Ctrl + A ，然后按 Ctrl + C 复制文本


7. 激活Excel并激活要粘贴公式的左上角单元格，并确保要复制到的工作表处于公式视图模式。


8. 按 Ctrl + V 粘贴。


9. 按 Ctrl + < kbd>`切换到公式查看模式。

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可以尝试通过记住您上次解析数据的方式来提供帮助。您需要启动将文本转换为列向导。选择定界选项，然后单击下一步。清除除制表符之外的所有分隔符选项标记。

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屋！