☰

如何让一个 Excel 表中的值在递增的同时传播到其他表？

tag-icon • tag-icon microsoft-excel worksheet-function vba automation
如何让一个 Excel 表中的值在递增的同时传播到其他表？

我有一个 Excel 工作簿，其中有 100 张工作表，除每张工作表中的 A10 外，所有工作表都相同。

在 Sheet 1 的 A10 中有一个值 101，是我手动输入的，其他所有 Sheet 的 A10 都是空白的。现在我想要做的是，当我在 Sheet1 的 A10 中输入 101 时，我希望 Sheet2 的 A10 中自动输入 102，Sheet3 的 A10 中自动输入 103，依此类推，直到 Sheet 100；

这个怎么做？

答案1

我假设 A10 是一个变量，否则您可以对其进行硬编码，并且每个连续的工作表都会增加 1，而不管工作表 1 中的值如何。在工作表 2 的 A10 中，您可以输入以下内容：

=IF(ISBLANK(SHEET1!A10),"",SHEET1!A10+1)

在每个连续的工作表上，更改工作表引用以反映上一个工作表编号。ISBLANK 函数测试上一个工作表上的 A10 是否为空白，如果是则显示空白（空双引号）。否则，它会显示比上一个工作表高 1 的数字。

正如 Prasanna 在评论中指出的那样，这似乎需要在 100 张工作表上手动输入公式。但是，有一种方法可以在一个步骤中完成结果。它结合了两件事。首先，可以一次在每个工作表上输入相同的公式。右键单击底部的工作表选项卡之一，然后单击“选择所有选项卡”。如果您随后在当前工作表的 A10 中粘贴公式，则每个工作表都会通过该操作获得相同的公式。这将包括 Sheet1，因此您必须返回第一张工作表并将 A10 替换为您的值。

那么，您可能会问，如何在每张工作表上使用完全相同的公式。很高兴您问了这个问题。Excel 有一个 CELL 函数，可返回有关单元格的元信息。您可以请求的参数之一是 FILENAME，它返回完整路径、文件名和工作表名称。可以解析它以提取当前工作表的名称，然后您可以使用算术来更改工作表引用，然后使用 INDIRECT 函数使用生成的文本实际引用另一张工作表上的单元格。要使 CELL 函数正常工作，您必须先保存电子表格，以便它实际获得文件名。

举例来说，要引用上一张工作表中的 A10，您需要用如下内容替换硬编码表达式SHEETn!A10（其中是上一张工作表的编号）：n

INDIRECT("sheet"&VALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,3))-1&"!a10")

我相信您可以在 CELL 函数中引用工作表上的任何单元格（我不认为它需要包含任何内容），因此我使用单元格 A1 来避免循环引用。这将通过查找右括号（文件名的结尾）的位置来提取工作表编号，并为单词“sheet”添加字符数以确定工作表编号的起始位置。我使用长度 3，因为最大的工作表编号将包含 3 位数字（如果少于 3 位数字，MID 函数只会采用其中的数字）。

我明确地将结果转换为数字（VALUE 函数）以进行减法运算，从而得到上一个工作表编号。Excel 实际上对此很宽容，无需进行 VALUE 转换即可进行计算，但对于使用其他电子表格的人来说，这可能是必需的。

然后，通过使用 & 将文本连接到工作表编号周围，构建完整的工作表名称和单元格引用。INDIRECT 函数将此文本字符串转换为实际的单元格引用。

因此，如果您在原始公式中替换此表达式，则每个工作表都会获得完全相同的公式（无需在任何工作表上编辑它），并且它将在每个工作表上计算所需的工作表引用。

答案2

此宏是特定于工作表的，必须放入工作表 vba 模块中。

右键单击excel底部的工作表名称，并选择查看代码。

将此代码粘贴到那里。

关闭 vba 模块并更改第一张表上的 A10。

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
For Count = 1 To ActiveWorkbook.Sheets.Count
    Sheets(Count).Range("A10").Value = Sheets(1).Range("A10").Value + Count - 1
Next
Application.EnableEvents = True
End Sub

相关内容