当中间有空行时如何复制公式而不丢失链接工作表的顺序？

Question

我有一个包含两张表的 Excel 文件。

• 概括
• 原始数据

我的摘要表包含类似这样的数据，其中每个数据行之间都有几行间隙

现在的问题是，如果我有一张包含 1000 多行的表格，并且我想更改其中的任何内容，我必须手动编辑每个单元格引用，因为它不会继续引用 RawData 表格，而是将单元格编号作为摘要表格的引用。

在所附的示例图片中，如果我复制并粘贴公式，我会得到单元格编号 2、5、8、11 等，具体取决于行距，而我需要复制手动公式列中显示的公式，如 B2、B3、B4、B5 顺序，而不管当前工作表的行号是多少。尝试了很多方法，但根本找不到任何解决方案。有什么帮助吗？

为了避免混淆，从我的工作表中创建更现实的示例，并附带 RawData 选项卡的屏幕截图。

原始数据表：- 原始数据表

这是链接表，我将从 RawData 表中链接数据 产品说明书摘要

如您所见，如果您复制粘贴公式，它将显示测试产品 1、测试产品 6 和测试产品 11，而这应该是测试产品 1、2 和 3。

以下是示例 Excel 文件链接https://we.tl/t-3F3mJra7pe

Answer 1

让我首先描述一下这个问题背后的原因，

• 在摘要表在单元格中，F3公式=Rawdata!A2返回值Test Product 1。
• 你一复制摘要表数据来自 B3:F7，B8Excel读取6 行之间F3:F8并返回 来自的Test Product 7单元格B8原始数据床单。

这破坏了整个演习。

解决方案：

• 你需要辅助栏在原始数据床单。

• 重写单元B3格概括带有 简单产品 1。
• F3在单元格中输入此公式概括床单。

=VLOOKUP(B3,RawData!A$2:E$12,2,FALSE)

你明白了。

• 现在，复制范围B3:F7和粘贴在 单元格B8。

你明白了。

• 重写单元B8格简单产品 2，现在你得到了正确的序列。

• 重复复制粘贴并进行纠正简单产品顺序，你就会得到期望的结果。

您可以根据需要调整公式中的单元格引用。

Answer 2

在这些情况下，我认为更简单但更强大的解决方案是使用 OFFSET() 函数。Offset 返回对单个单元格或单元格范围的引用。考虑到您的情况，如果第一个公式复制和粘贴应引用 RawData Product1，而第二个公式复制和粘贴应引用 Product2，则无论在这些复制和粘贴中跳过多少行，我都会按如下方式实现此 OFFSET：

A B               C      D        E       F
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B6);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B11);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B16);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

请注意，当您复制公式“=OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)”时，对“Summary!$B$1:B1”的引用将自动更改，并产生您不希望出现的行为；但是，一旦在 COUNTA() 公式中使用该引用，它就会奏效，您不必自己更改数千个引用。这样做后，COUNTA() 将计算自列开始以来“简单产品”出现的次数，并将使用此内部偏移量引用序数位置中的相应产品。所有这些都无需创建辅助列。结果如下：

A B               C      D        E       F
  Simple Product  FALSE  TRUE             TestProduct1
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct2
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct3
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct4
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large