☰

Excel 对其他工作表的相对引用

tag-icon • tag-icon microsoft-excel
Excel 对其他工作表的相对引用

假设我有一本工作簿，其中每张工作表代表一个城市。每个城市的工作表中都有大量数据，顶部有一些汇总字段。假设每个城市的工作表中，单元格 A1 是城市名称，A2 是人口，A3 是房屋数量。例如，假设第一张工作表是 Anytown。它的前 3 个单元格（A1-A3）可能是：

Anytown   25,000    8,000

（注意：城市名称除了位于单元格 A1 中之外，也是工作表的名称）

我将为其他城市准备更多类似的表格。

现在，我想创建一个“摘要”工作表，自动将其他所有城市工作表中的相同 3 个字段提取到列中。例如，其 A、B 和 C 列将如下所示：

Anytown   25,000    8,000
Busytown  35,000    9,000
Tinytown  5,000     1,000
etc...

到目前为止，我可以为第一行创建单元格引用，引用第一张表中的固定单元格，如下所示：

Anytown!$A$1   Anytown!$A$2    Anytown!$A$3

这样就得到了第一行，但我有许多工作表需要引用，我不想为每一行都输入工作表名称。我希望该行自动重复，从所有其他城市的工作表中获取 A1-A3 单元格引用。

我尝试拖动第一行单元格的右下角并向下拖动，但这只会创建一堆对 Anytown 工作表的引用。因此，看来我对工作表名称的引用是绝对的。有没有办法让它们变成相对的，这样就可以了？

我还能怎样实现这一目标？

答案1

借助 Excel 最近推出的新字符串/数组函数之一，TOCOL现在这变得简单了：

=TOCOL(Sheet2:Sheet4!A1,,)

函数中的参数是一个范围，该范围位于引用的工作表部分，并且每个工作表上的单元格始终为 A1。

电子表格通常会由一组连续的工作表组成，您可以用它们来执行所需的操作。在这种情况下，引用很容易创建：只需输入公式并单击工作表组中的第一个工作表，然后单击单元格（在本例中为 A1），然后导航到工作表组中的最后一个工作表并单击同一单元格。当然，也可以直接输入公式。

只要工作表保持连续，这就是您所需要的。如果您在两个“书挡”工作表内插入或删除工作表，该操作将更新所有包含的工作表的 A1 单元格列表。

当然，B1和C1也可以用同样的方式完成。

它的输出对于该SORT功能来说是可行的，因此按字母顺序排列的列表不需要工作表按字母顺序排列。

我没有试验过，但它应该可以接受任何可构建的单元格集。这可能会允许使用不连续的工作表，尽管显然设置起来会更困难。如果可以使用这样的范围，那么肯定也可以使用页面中的任何单元格，而不仅仅是每个页面上的相同单元格。

更具包容性的公式：

    =TOCOL(Sheet2:Sheet4!A1:C1,,)

导致单列列表，如 Sheet2 的三个单元格，然后是 Sheet3 的，依此类推。这可能适合使用类似INDEX和巧妙的方法进行提取和重新定位。

（姊妹函数的TOROW工作原理类似，产生如上所述的一维数组。）

真正的精华可能是MAKEARRY需要适当的LAMBDA重新定位。我还没有用过这个，但它肯定是你最好的选择，可以在一个单元格中使用一个公式填充整个列表。

答案2

免责声明：我从这个答案中得到了启发：如何通过索引而不是名称引用 Excel 工作表？

您可以结合使用自定义函数（该函数根据其索引返回工作表的名称）和“INDIRECT”函数，如下所示：

打开 VBA 编辑器，插入新模块并将以下函数复制到其中：

Function sheetNameFromIndex(ByVal sheetIndex As Integer) As String
    sheetNameFromIndex = Worksheets(sheetIndex).Name
End Function

然后创建摘要工作表并将此函数放在 A1 中：

=INDEX(INDIRECT("'" & sheetNameFromIndex(ROW()) & "'" & "!1:1048576"),1,COLUMN())

水平扩展此函数将从第一个工作表中提取值，垂直扩展它将从后续工作表中提取相同的值（每个工作表都有自己的行）

答案3

我无法发表评论，但是当发帖人使用早期的解决方案时，出现了一个重要问题。

下面的办法可以解决问题，当引用单元格之一为空白时返回“0”：

=INDEX(IF(TOCOL(Sheet2:Sheet4!A1:C1)="","",TOCOL(Sheet2:Sheet4!A1:C1)),SEQUENCE(3,3),)

看起来IF属于TOCOL，但那样做不行。但这样效果很好。基本上，它会检查TOCOL返回结果中是否有空格，如果是，则在其返回结果中传递一个空格，否则传递正常返回结果。因此，在将“0”传递给 之前INDEX。

（“1/0/xxx” 是 Excel 显示零的方式。）

此外，事实证明，它不仅解决了将 的单列重新INDEX转换为三列的额外问题，而且还有另一种用途。它能够直接使用的输出作为动态范围，而其他几个函数则需要中间的另一个公式供它们引用；它们不能直接将输出用作全范围。TOCOLTOCOL

并且，INDEX可以根据SORT感兴趣的内容对 的输出进行排序。

相关内容