如何在文件之间复制带有公式的Excel工作表? [英] How to copy an Excel sheet with formulas between files?

问题描述

我有两个Excel文件:带有两个工作表(Sheet1和Sheet2)的file1和带有Sheet3的file2.
表格1和3具有相同的结构，但数据却不同.
Sheet2包含一些引用Sheet1的公式，例如"= Sheet1！A1" .

I have two Excel files: file1 with two Sheets (Sheet1 and Sheet2) and file2 with Sheet3.
Sheets 1 and 3 have the same structure but different data.
Sheet2 contains some formulas referring to Sheet1 like for example "=Sheet1!A1".

我想使用与Sheet3相同的公式将Sheet2复制到file2，但是当我尝试将其复制时，公式从"= Sheet1！A1" 更改为"= [file1 .xls] Sheet1！A1".

I want to copy Sheet2 to file2 with the same formulas referring to Sheet3, but when I try to copy it, formulas change from "=Sheet1!A1" to "=[file1.xls]Sheet1!A1".

如何使用相同的公式复制整个工作表?

How can I copy the whole sheet with the same formulas?

推荐答案

我不确定是否有明确的方法来执行此操作，但是通过一些查找/替换"操作，您可以解决此问题.

I am not sure there is an explicit way to do this, however with some Find/Replace action you can work around this.

将新公式粘贴到File2中之后，进行查找替换( CTRL + F 并选择替换").将文件引用[file1.xls]sheet1!替换为sheet3!，这将删除该链接.

After you paste your new formulas into File2, do a find replace (CTRL+Fand select Replace). Replace the file reference [file1.xls]sheet1! with sheet3! and this should remove that link.

虽然不是您要求的100％，但复制精确公式(不更改文件或工作表引用)的一种方法是:

While not 100% what you're asking, one way to copy exact formulas over (without changing the file or sheet references) is this:

或者，您也可以将源文件和目标文件都置于公式视图模式(转到功能区上的公式"->显示公式"或按 CTRL + `(键，看起来像是向后的撇号))，然后将公式从第一个文件复制并粘贴到第二个文件，然后退出公式查看模式.

Alternatively, you can put both the source and destination files into formula view mode (go to Formula on the ribbon --> Show Formulas or press CTRL+` (key that looks like backwards apostrophe)), then copy and paste the formulas from the first file to the second file, and exit formula view mode.

对于您的问题，如果使用此替代方法，则仍需要用Sheet3查找/替换Sheet1.

For your question, if you used this alternate method, you would still need to find/replace Sheet1 with Sheet3.

这篇关于如何在文件之间复制带有公式的Excel工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！