☰

如何跨列和行复制公式，调整单元格引用和工作表名称

tag-icon • tag-icon microsoft-excel
如何跨列和行复制公式，调整单元格引用和工作表名称

我不知道如何使用绝对和相对引用以及工作表名称引用的混合来复制公式。

我有一张表，它是书中其他表的摘要，每个组件表代表一年中的一个月。我需要通过复制引用其他表的单元格来填充摘要表。公式列需要反映每张表固定行中的相关列，而公式排需要反映工作表选择。工作表名称基于月份名称。

工作簿中每个月都有一张工作表，一月、二月等。每张工作表都是相同的，我从每个月工作表的月度汇总行（第 4 行）中提取值。汇总行包含从 E 列开始的连续列中不同会计类别的月度总计。

摘要表的每一行都包含来自相关月份表的摘要行。

换句话说，我有这个：单元格E5是=IF(Jan!E4>0,Jan!E4," ")，并且我希望它下面的单元格（E6）是=IF(Feb!E4>0,Feb!E4," ")。

那么单元格“F5”（的右侧E5）将是=IF(Jan!F4>0,Jan!F4," ")。

我附上了摘要页面和其中一张月度报表的截图。

一月表

摘要表

答案1

Rey Juna 的回答解释了如何使用查找表来实现这一点。下面是另一种不使用查找表来导出引用地址的方法。公式的总体结构类似。其中大部分是通过将公式位置转换为单元格引用来决定的（请注意，我的公式中的位置转换现在可能与 Rey 的公式略有不同，因为问题中的描述略有变化）。

  • 您希望公式第 5 行引用一月（月份 1），因此我们需要从公式行中减去 4。
  • 您希望逐列提取数据，从 E 列开始，但始终从目标表的第 4 行开始。

问题中的描述发生了变化，因此我将使用略有不同的 INDIRECT 方法。INDIRECT 具有允许引用具有所谓 R1C1 格式的单元格的功能，这对于此类要求非常方便。您可以轻松指定行号和列号，并进行相对寻址。

INDIRECT 有一个可选的最后一个参数，用于指示单元格引用的样式。如果它为 FALSE 或0，则表示 R1C1 样式寻址。在 INDIRECT 字符串中，R4C[0]引用第 4 行和与公式相同的列（零偏移）。

否则，这里的主要区别是如何通过公式而不是查找来得出工作表名称。

关键在于这个公式：

TEXT((ROW()-4)*28,"mmm")

上面解释了行减 4，将公式位置转换为月份数。我们需要将月份数转换为该月份内的日期（可以是任何年份，我们只需要一年中的某一天）。乘以 28 即可。非闰年除二月外的所有月份都有超过 28 天，但这足以保证结果的日期数在正确的月份。

（请注意，此技巧适用于将 1-12 转换为 Jan-Dec，但如果您的起始月份不是 1 月或您有连续多个年份，则需要进行调整；您不能只调整行偏移。请参阅下面的附录。）

TEXT 函数将结果格式化为三个字母的月份缩写。

综合起来，实际公式是：

=IF(INDIRECT(TEXT((ROW()-4)*28,"mmm")&"!R4C[0]",0)>0,INDIRECT(TEXT((ROW()-4)*28,"mmm")&"!R4C[0]",0),"")

您可以将此公式复制并粘贴到单元格 E5 中，然后只需复制并粘贴或拖动即可填写矩阵。除非工作表布局发生变化，否则您不需要调整公式。

附录：如果您的月份不是一月至十二月（例如财政年度），并且/或者您有连续多个年份，那么这里还有另一个技巧，即使用上面显示的月份数到月份缩写的转换。

  • 进行第一行调整，将行号转换为起始月份号。假设您的第一个公式行为 5，起始月份为 10 月，那么您可以使用 ROW()+5。
  • 将其与 MOD 函数组合，得到除以 12 后的余数（十二月将为零，但仍然有效）：。MOD(ROW()+5,12)结果是每一行都指向正确的月份数字。
  • 在 TEXT 函数中使用它：TEXT(MOD(ROW()+5,12)*28,"mmm")。

答案2

这种方法需要一个缩写月份名称列表，我将其放入我命名为 的工作表的单元格A1中，如下所示： A12ListMo

|    |  A  |
|---:|:---:|
|  1 | Jan |
|  2 | Feb |
|  3 | Mar |
|  4 | Apr |
|  5 | May |
|  6 | Jun |
|  7 | Jul |
|  8 | Aug |
|  9 | Sep |
| 10 | Oct |
| 11 | Nov |
| 12 | Dec |

以下是输入到您的单元格中的公式E7：

=IF(INDIRECT(INDEX(ListMo!$A$1:$A$12,ROW()-6,) & "!E" & COLUMN()-1)>0,INDIRECT(INDEX(ListMo!$A$1:$A$12,ROW()-6,) & "!E" & COLUMN()-1)," ")

解释

INDEX(ListMo!$A$1:$A$12,ROW()-6,)

Index允许您从数组/列表中选择一个元素。 Row()仅返回公式所在的行号，以及-6使第 7 行等于列表中第一个元素“Jan”所需的偏移量。

COLUMN()-1

就像Row()，Column()将返回公式所在的列号，是使列或 5 等于 中的 4-1所需的偏移量。EE4

Indirect允许您将所有文本放在一起&，然后将其作为单元格引用读取。

对于单元格来说，E7它将像这样解析：

INDIRECT(INDEX(ListMo!$A$1:$A$12,1) & "!E" & 4)那么就等于Jan!E4。

该Indirect公式必须执行两次，因为该单元格引用在公式中使用了两次。

所有这些允许您向右拖动公式，同时使单元格引用向下移动，这不是在 Excel 中拖动公式时的默认行为。现在向下拖动公式会选择下一个工作表名称，并且默认拖动不会增加工作表名称。

答案3

单元格地址中的 $ 符号固定单元格或行引用。例如，如果您要引用第 4 行中的单元格，并复制该公式，那么您可能会引用单元格 E$4。当将其复制到列下方时，它始终会给出 E$4。将其复制到行上方时，将给出 E$4、F$4、G$4。

如果您要构建一个参考月份列的东西（图表之外），您可能会引用类似（如果 $A1=E$4 ...）的内容。向下复制时，这会将 so 更改1为指向包含月份的 A2、A3 ...。E4 将继续充当测试变量，但在下一列中（复制该列时），它将是 F$4，指向新的测试。

相关内容