Home Articles

删除和替换工作表时如何保留公式对工作表的引用?

Asked
Viewed 1253 times
2

我是一个应用程序的作者,在打开Excel工作簿之前,删除并重新创建一些工作表,例如下面示例中的“Sheet1” .

同一工作簿中的其他工作表(比如Sheet2)可能具有引用替换工作表的公式,如下所示:

=IF('Sheet1'!A9="","",'Sheet1'!A9)

不幸的是,当更换纸张时,上述公式中对Sheet1的引用被破坏了

=IF(#Ref!A9="","",#REF!A9)

任何人都可以想到一种编码这个公式的方法,这样它就不会失败吗?例如,它可以从Sheet2中的隐藏单元格中获取工作表的名称或其他内容?

我对Excel公式知之甚少,不知道可能性是什么 .

TIA

1 Answer

  • 4

    使用间接寻址和命名范围

    1)使用填充了文本“SheetN!”的幻像细胞(即Z1 ... Zn) . 对于要删除的每个工作表 .

    2)为这些表中的所有外部可寻址单元格和范围定义范围名称

    3)在公式中用“间接”和命名范围的LITERAL替换引用 .

    Example:

    更换

    =Sum(Sheet1!A:A)
    

    =SUM(INDIRECT(Z1&"caca"))
    

    其中“caca”是在Sheet1中定义为A:A的命名范围,Z1包含“Sheet1!”

    Another Example:

    更换

    =Sheet1!A1+Sheet1!A2
    

    =INDIRECT(Z1&"NamedRangeForA1")+INDIRECT(Z1&"NamedRangeForA2")
    

    现在您可以删除Sheet1而不会引用参考问题 .

    添加新Sheet1时,应重新创建(在VBA中)命名范围 .

    Edit: Answering your comment about Sheets with blanks in name

    在这种情况下你需要额外的fu :)

    用这个:

    =INDIRECT("_'_" & Z1& "_'_!_" & "caca")
    

    应该删除“_”,因为我将它们插入那里只是为了便于单引号和双引号的可视化 .

    在单元格Z1中输入Sheet1的名称,不带引号且没有感叹号(因为它已经在公式中) .

    HTH!

Related