复制/粘贴后禁用Excel的自动单元格参考更改 [英] Disable Excel's Automatic Cell Reference Change After Copy/Paste

问题描述

我有一个很大的Excel 2003电子表格，我正在努力。有很多非常大的公式，有很多细胞参考。这是一个简单的例子。

  ='Sheet'！AC69 +'Sheet'！AC52 +'Sheet'！AC53）* $ D $ 3 +'Sheet'！AC49 
  

大多数比这更复杂，但这是一个好主意我正在使用什么这些单元格引用中的很少是绝对的（$ s）。我希望能够将这些单元格复制到不同的位置，而不改变单元格参考。我知道我可以简单地使用f4来使引用绝对，但是有很多数据，我可能需要使用Fill。有没有办法临时禁用复制粘贴/填充的单元格引用更改，而不使引用绝对？

编辑：我刚刚发现，你可以这样做通过将单元格内容复制为文本而不是公式来进行VBA。我不需要这样做，因为我想要一次复制整个行/列。有没有一个简单的解决方案我缺少？

解决方案

从 http://spreadsheetpage.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 +`切换到公式视图模式。

注意：如果粘贴操作回到Excel不工作正常，最近你已经使用了Excel的文本到列的功能，Excel通过记住你最后一次解析数据的方式来帮助你，你需要启动将文本转换为列向导。选择分隔符选项 并单击下一步。清除除Tab之外的所有分隔符选项复选标记。

或者，从 http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/ ：

 如果你是一个VBA程序员，你可以简单地执行下面的代码：
带表格（Sheet1）
 .Range（A11：D20）。Formula = .Range（A1：D10）。 
结束
  

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

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?

EDIT: 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?

解决方案

From http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:

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.

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.

Or, from 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屋！