如何通过公式自动将一张表中的数据放入另一张表中？

Question 1

不确定我的理解是否正确，你可以尝试这个公式：

=INDIRECT("'"&CELL("filename")&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),"1","")&ROW(A1))

Answer

不确定我的理解是否正确，你可以尝试这个公式：

=INDIRECT("'"&CELL("filename")&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),"1","")&ROW(A1))

Question 2

你正在做的是链接克隆工作表。

我认为“拖动”公式的最大问题实际上不是拖动，而是插入和删除或添加新行时无法很好地克隆。在上面的答案中，我详细说明了如何使用 INDEX 来维护克隆，即使您在主表中插入或删除行也是如此。而且由于您为空白单元格返回“”，因此您可以轻松地根据需要将公式扩展到尽可能远的范围，并且它将自动填充新数据。

话虽如此，Excel 365提供“带溢出的动态数组”。这个拗口的意思是，您可以在一个单元格中输入多单元格公式或范围，答案会自动溢出到相邻的单元格中。因此，您可以通过在 Sheet2!A1 中输入单个公式来克隆整个工作表

=Sheet1!$A:$XFD

然而，这是一个非常糟糕的想法，会降低电子表格的速度，因为它会为整个 sheet1（最大行数 x 最大列数）中的每个空单元格返回 0。为了仅克隆正在使用的内容（例如，最大边界由第 1 行长度 x 第 1 列长度定义）：

=Sheet1!$A$1:INDEX(Sheet1!$A:$XFD,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

这样会好很多，但即使引用最多 XFD 列也可能会导致问题。也许将其限制为 $IV（Excel 2003 及更早版本中的最大列数）。此外，您仍会在 Sheet1 有空白的地方看到大量零，因此请改为：

=LET(usedrange, Sheet1!$A$1:INDEX(Sheet1!$A:$IV,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)), IF(ISBLANK(usedrange), "", usedrange))

Answer

你正在做的是链接克隆工作表。

我认为“拖动”公式的最大问题实际上不是拖动，而是插入和删除或添加新行时无法很好地克隆。在上面的答案中，我详细说明了如何使用 INDEX 来维护克隆，即使您在主表中插入或删除行也是如此。而且由于您为空白单元格返回“”，因此您可以轻松地根据需要将公式扩展到尽可能远的范围，并且它将自动填充新数据。

话虽如此，Excel 365提供“带溢出的动态数组”。这个拗口的意思是，您可以在一个单元格中输入多单元格公式或范围，答案会自动溢出到相邻的单元格中。因此，您可以通过在 Sheet2!A1 中输入单个公式来克隆整个工作表

=Sheet1!$A:$XFD

然而，这是一个非常糟糕的想法，会降低电子表格的速度，因为它会为整个 sheet1（最大行数 x 最大列数）中的每个空单元格返回 0。为了仅克隆正在使用的内容（例如，最大边界由第 1 行长度 x 第 1 列长度定义）：

=Sheet1!$A$1:INDEX(Sheet1!$A:$XFD,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

这样会好很多，但即使引用最多 XFD 列也可能会导致问题。也许将其限制为 $IV（Excel 2003 及更早版本中的最大列数）。此外，您仍会在 Sheet1 有空白的地方看到大量零，因此请改为：

=LET(usedrange, Sheet1!$A$1:INDEX(Sheet1!$A:$IV,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)), IF(ISBLANK(usedrange), "", usedrange))

Question 3

下面的代码可以很好地工作，并且具有仅返回 A 列中包含某些内容的行的功能：

=FILTER(Sheet1!A2:A31,  Sheet1!A2:A31<>"")

由于您只要求 A 列...请根据需要编辑 A31 以适合您所需的行，此外，如果您确实需要比 A 更多的列，请也编辑列标签部分。

问题出在终点。假设最后使用的行（目前）是第 20,000 行。如果您愿意，您可以简单地将其用于行号编辑，它将很好地处理行插入和删除，因为 Excel 会跟踪这些影响。但它不会处理某人简单地在第 20,001 行中输入下一个条目，依此类推。

可以通过指定某个适当较大的值（例如 A25000）来克服这个问题，以便在再次出现问题之前允许大量出现这种情况。当然，越往下走，到达目标所需的时间就越长，而且越有可能忘记这一点……或者记得在晋升时将其传递给接任者。

在下一部分之前，我应该提到，经常提到的函数在这种问题中可以以两种方式使用。一种是用它来构建一个字符串，即您需要的范围地址，然后将其提供给它以使其成为INDIRECT()一个真正的男孩。考虑到现代机器的内存和速度，这现在不是问题，但是将其堆积在大量单元格中，并且每次发生任何变化时它都会重新计算，您会看到一些实际的减速，即使在今天。100,000 行和 30 列使我陷入明显的秒左右滞后，但在 20 列时，没有什么明显的滞后。不过，还是要考虑。另一种方法是像INDEX()asMobus一样在函数中使用它。INDEX()不是易失性的，所以它只在 Excel 认为需要时才重新计算。

FILTER()很高兴有提供您需要的范围。所以您可能更喜欢INDEX()。

或者，使用我最后推荐的方法来避免这两种情况。

选择范围终点的方法似乎是使用类似的东西COUNTA()来为您提供一个动态且准确的数字，甚至向其中添加一些金额以适应任何 Excel 上的“有趣事情”。但是……如果可能存在空白单元格，您会立即开始看到底部的行未显示在输出表中。因此，行 2-2,001 有条目并COUNTA()给出 2,000。并且您的公式立即只返回范围 A2:A2000 中的内容，而不是 A2:A2001。同样，向其中添加一些适当（无论如何看起来）的高值会暂时解决您的问题，但最终会出现问题。

（当然，如果不能填空也没关系。）

Excel 中没有函数可以返回行或列中最后使用的单元格。有很多解决方法，但没有一种是万无一失的。添加列会使这个问题复杂化。MAX(COUNTA(one column after another, then MAX choosing the highest value returned))如果涉及空白，即使“轮询”所涉及的列也不能保证成功：空白不必都在同一行。因此，A1:B5 可以在 A 列中a, blank, d, blank, g显示 3，而 B 列可以blank, a, blank, blank, blank显示 1。两者都不是正确的值，因为需要 5。诸如“文本连接”一行中的所有单元格并呈现它之类的方法会COUNTA()遇到 Excel 公式中内部数组长度限制的问题（可能不是行文本连接，而是当所有这些行的数组放在一起进行COUNTA()计数时）。

就我个人而言，我只会给出FILTER()一个适当更高的数字所使用的范围，甚至可能有点花哨，只需将其结果乘以某个因子，比如 1.25，或者任何适合您手头项目需求的因子。（ROUND(xxx,0)当然，得到一个整数！）

要真正解决您的问题，您可以使用黄金标准：VBA。要么是一个功能齐全的、可执行大量/所有类型的宏，要么只是一个使用 VBA 来查找真正的 LastCell、为其和已知起始单元格创建一个范围并将其提供给公式的 UDF。VBA 几乎总是比 Excel 公式快得多，如果您在 VBA 中设计整个公式，则只需要 UDF。虽然它必须是易变的才能在发生变化时更新，因此 VBA 代码的运行次数将比公式计算的次数多得多……可能非常“愚蠢”……

Answer