☰

编辑1

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
编辑1

我有一个文档，我在其中创建了一个表示日期的新工作表（必须这样）。每个工作表都引用了工作表中表示前一天的同一单元格。因此，工作表“星期二”从工作表“星期一”中提取一些值，依此类推。公式为：

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33

我看到的结果如下Saldo from 1 June 2016, Amount $55.00

如果我创建一个新工作表并命名它Day05并粘贴工作表的内容Day04，是否有办法更新引用的公式Day03？Day04目前，内容仍然从工作表中提取数据Day03。

简而言之，当我复制 sheet 的内容时Day04，我希望看到公式更新为：

            Description                              Amount

="Saldo from "&TEXT($'Day04'.C4,"DD.MM.YYYY.")        =$'Day04'.F33

目前，工作表名称引用不会更新，并且我的Day05工作表仍然从中提取数据Day03

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33 //<-- WRONG is 03 ref!

答案1

问题是创建一个新的工作表，复制现有工作表的内容，并将单元格的引用更改为第二个现有工作表。萨蒂亚·米什拉说，可以通过在复制到新工作表后更改'Day03'!为来手动完成。'Day04'!Day05

为了在从上一个工作表复制到新工作表时自动进行更改，让我们重新陈述问题：创建一个新工作表并复制上一个工作表的内容。使用新工作表的名称来引用上一个命名的工作表。

这是当前工作表中的公式，它从上一个命名工作表中获取单元格的内容F33。例如，如果当前工作表是Day05，则公式F33从工作表中获取Day04。

=INDIRECT(CONCATENATE("'日",TEXT(MID(CELL("文件名",$A$1),FIND("]",CELL("文件名",$A$1))+4,255)-1,"0#"),"'!","F33"))

公式如何运作

该公式用于INDIRECT从上一张工作表中获取单元格值。如果当前工作表为 ，Day05并且我们想要单元格F33，则需要一个引用工作表中INDIRECT单元格的字符串。我们需要的字符串是。F33Day04'Day04'!F33

  1. 获取工作表名称当前工作表。
Day05 =MID(CELL("文件名",$A$1),FIND("]",CELL("文件名",$A$1))+1,255)
  1. 通过将 +1 改为 +4，修改公式以获取工作表名称末尾的数字。这样会跳过 3 个字符“Day”。
05 =MID(CELL("文件名",$A$1),FIND("]",CELL("文件名",$A$1))+4,255)
  1. 减去 1 即可得到上一张表的数字部分。
4 =MID(CELL("文件名",$A$1),FIND("]",CELL("文件名",$A$1))+4,255)-1
  1. 用于TEXT在数字前填充前导零。
04 =TEXT(MID(CELL("文件名",$A$1),FIND("]",CELL("文件名",$A$1))+4,255)-1,"0#")
  1. 使用CONCATENATE对单元格的引用来创建新的工作表名称F33。
'Day04'!F33 =CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33")

使用公式

无论您想要上一张表中的单元格值，都可以使用公式代替单元格引用，并将公式中的最后一个字符串更改为单元格引用。例如，要使用单元格C4显示说明（请注意C4公式中的说明）：

=“Saldo 来自“＆TEXT（INDIRECT（CONCATENATE（”'Day“，TEXT（MID（CELL（”filename“，$A$1），FIND（”]“，CELL（”filename“，$A$1））+4,255）-1，“0#”），“'！”，“C4”）），“DD.MM.YYYY。”）

结果是Saldo from 01.06.2016.

条件和注意事项

  • 每个工作表名称必须以两个数字结尾，例如，，，Day02等等Day03。
  • 除非新工作表具有正确的名称，否则公式将不起作用。错误的工作表名称会导致#VALUE!错误。

答案2

我还没有找到任何方法可以自动完成此操作。但“搜索和替换”（Ctrl+H）似乎有效。

答案3

感谢 Scott Craner 的评论，我找到了解决方案。我警告你，这将是一个很长的公式。

无论你在哪里得到“DayXX”和 XX 号码，你都可以将 XX 更改为

&MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),FIND("y";MID(CE("filename",A1),FIND("]";CEL("filename";A1))+1,255))+1,2)-1

这将查找文件名并从 day 中搜索 y，然后从文件名中提取数字并从中减去 1。如果您愿意，我可以进一步解释，只需添加评论即可。

答案4

当我们更改工作表的名称时，Excel 会调整对工作表的引用。所有引用都会自动重命名。让我们尝试使用此功能解决问题：我们不需要重命名引用，而是重命名工作表。

  1. 打开包含每日工作表的工作簿。
  2. 像之前一样，使用新一天的数据（例如“Day05”）创建工作表。最后，从 Day04 工作表中复制公式（无论它们在复制后是否仍引用 Day03），然后保存。不要关闭工作簿！
  3. 打开另一个工作簿（可能是空的）以保存临时引用，然后移动 Day05 表（为此，右键单击 Day05，选择“移动或复制...”并选择其他工作簿作为目标）。
  4. 删除工作表“Day04”并将工作表“Day03”的名称更改为“Day04”。这是最棘手的部分！

现在您可以看到，由于重命名，新工作簿中的“Day05”工作表的引用将设置为 Day04（来自第一个工作簿），而不是 Day03。接下来，让我们将此工作表移回原始工作簿。

  1. 关闭原始工作簿，放弃上次保存后所做的更改。
  2. 重新打开工作簿并移回临时工作簿中的 Day05 工作表。引用仍指向 Day04（来自当前工作簿），因为我们放弃了更改，而不是重新命名工作表。

瞧！这是让 Excel 为我们重命名引用的技巧。

乍一看，这可能并不容易，但我认为它比使用公式或 VBA 更容易，因为您无需编程知识即可控制步骤。此外，它比重命名每个引用更容易。

希望能帮助到你！

编辑1

当然，您可以使用此活动录制宏，甚至对其进行调整，以便能够更快地调用它。

相关内容