☰

跨工作表更改链接单元格公式

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
跨工作表更改链接单元格公式

我有一个名为“stu”的工作簿，它有 1 到 30 个工作表。在单元格中 H4我链接了一个公式：

=[sub.xlsx]Sheet1!$B$2

在工作表1：单元格 H4 单元格中的公式H4应=[sub.xlsx]Sheet1!$B$2
为表2：单元格 H4 单元格中的公式H4应=[sub.xlsx]Sheet1!$B$3
为表3：单元格 H4 单元格中的公式H4应=[sub.xlsx]Sheet1!$B$4
为表4：单元格 H4 单元格中的公式H4应=[sub.xlsx]Sheet1!$B$5
为表5：单元格 H4 单元格中的公式H4应为=[sub.xlsx]Sheet1!$B$6
..等等..

...........................

因此，在每张表中，单元格编号应自动更改。我不必一次又一次地按 = 符号并输入。

答案1

在 Excel 中，键入Alt+F11打开 Visual Basic 窗口。在左侧，右键单击电子表格的名称，然后Insert→ Module。Module1 - Code右侧将打开一个子窗口。在该窗口中插入以下内容：

Function SHEET_NUM()
    SHEET_NUM = Application.Caller.Parent.Index
End Function

现在回到电子表格。为了好玩，为了看看它是如何工作的，请=SHEET_NUM() 在任意单元格中输入。它应该显示它所在的工作表的编号。因此，输入

=INDIRECT("[sub.xlsx]Sheet1!$B$" & (SHEET_NUM()+1))

H4到每张表的单元格中。SHEET_NUM()+1子表达式将计算为2on Sheet1、3onSheet2等……——换句话说，就是您想要引用的行号。  &是连接运算符；它将常量字符串"[sub.xlsx]Sheet1!$B$" 与计算出的行号组合在一起，从而得到您想要的引用。该INDIRECT函数采用看起来像引用的字符串并取消引用它；即，它获取引用的值。

一些警告：

  • 如果您正在处理一个工作簿 ( stu.xlsx) 引用另一个工作簿 ( sub.xlsx)，则在使用第一个工作簿时，您需要打开第二个工作簿——尽管 Excel 可以引用已关闭工作簿中的数据，但该INDIRECT函数不能。如果这是一个问题，您可以执行复制和粘贴值操作。
  • 您需要将工作簿保存为.xlsm文件，并在每次打开时启用宏。（或者执行前面提到的复制和粘贴值操作。）
  • 据我所知，工作表在创建时会分配一个编号，并且很难更改。特别是，如果你移动工作表，它们将保留其原有的原来的数字，这会让人感到困惑。（但另一方面，你可以重命名它们而不必担心更改它们的数字。）

答案2

以下步骤应该可以精确获得您正在寻找的公式，无需 VBA：

  1. 选择您希望公式出现的所有工作表（您可以通过按住“Ctrl”并单击其选项卡将每个工作表添加到选择中）
  2. 在另一个空白单元格中输入以下公式，可能是 H3：=IF(LEFT(RIGHT(CELL("filename",$A$1),2),1)="t",RIGHT(CELL("filename",$A$1),1),RIGHT(CELL("filename",$A$1),2))
  3. 在单元格 H4 中输入以下公式（用 H3 替换您在步骤 2 中使用的单元格）：="=[sub.xlsx]Sheet1!$B$"&H3+1
  4. 选择单元格 H4，复制并粘贴值
  5. 清除步骤 2 中创建的公式
  6. 在整个工作簿中查找并将“=”替换为“=”（Ctrl+H 进行查找和替换）
  7. 取消对其他工作表的选择（您可以通过按住“Ctrl”并单击其选项卡来从选择中删除每个工作表）

除非您知道如何处理选定的多张工作表，否则请不要在步骤 1 到步骤 7 之间执行任何其他操作。

答案3

最好的办法可能是打开 sub.xlsx，将 stu 中的所有 30 张工作表分组，插入一个版本的公式（例如，stu 的 sheet1 的 H4 中的 =[sub.xlsx]Sheet1!$B$2），取消分组，然后手动调整 stu 每张工作表中的 H4（输入公式的工作表除外，例如，在 sheet2 中将 2 替换为 3 等）。

相关内容