☰

Excel 中的跨工作表脚本

tag-icon • tag-icon microsoft-excel-2013
Excel 中的跨工作表脚本

是否可以通过工作簿其余部分中相同的单元格来更新第一张工作表上的行号？

我有一张索引表 ( Index)，其中包含指向 53 个其他工作表的链接，涵盖周数，Wk 1直到Wk 53。我需要从Index:A2到Wk1:C19、Index:A3到Wk2:C19、Index:A4到Wk3:C19等中提取数据。

我尝试过各种方法来Index!$A(wk1!sheet())返回行引用，但是都没有成功。

唯一似乎有效的方法是在C19所有 53Wk张纸上的每一张都写上绝对引用。

有一个更好的方法吗？

答案1

您需要使用INDIRECT()它来引用不断变化的单元格位置，并使用 CELL(“filename”) 来获取工作表名称，以便您知道单元格需要引用哪一周/哪一行。

=INDIRECT("Index!A"
    &RIGHT(
        CELL("filename")
        ,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2)
    +1)

让我们详细分析一下：

  1. 让我们确定您每周的工作表。转到 Wk1 选项卡上的单元格 C19。输入=CELL("filename")。此步骤要求您先保存工作簿，否则 Excel 无法识别文件名。

    你应该得到如下结果：

    C:\Users\yourname\Documents\[yourworkbook.xlsx]Wk1

  2. 现在我们需要删除大部分内容，以便只保留相关信息，即周数。因为周数始终是字符串的最后一部分，所以我们可以使用函数RIGHT()来获取它。

    如果您直接输入，=RIGHT(CELL("filename"),1)您将获得第 1 周，但这仅在周数少于 10 周时才有效。所以我们需要让函数更灵活，并计算出我们想要保留多少位数字。1 还是 2？

    我们可以使用LEN()来确定 中的字符总数。然后，我们可以从总长度CELL("filename")中减去 之前的字符串长度，从而确定工作表名称中的字符数，如下所示：]

    =LEN(CELL("filename"))-SEARCH("]",CELL("filename"))

    这会给出 3 或 4 的值，但是我们不想包含组成工作表名称“Wk”部分的两个字符，因此让我们减去 2，以便我们也去掉“Wk”：

    =LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2

    因此，将其添加到我们的RIGHT()函数中，我们有：

    RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2))

    最后一件事 - 在您的索引表上，第 1 周从第 2 行开始，因此让我们添加 1 以确保输出正确的行号：

    RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2))+1

  3. INDIRECT()允许我们在单元格引用中使用计算。我们将行号添加到对“Index!A”的静态引用中，并使用 连接两者&。

综上所述，这就是：

=INDIRECT("Index!A"
    &RIGHT(
        CELL("filename")
        ,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2)
    +1)

相关内容