在Excel表格中自动递增行号

Question

我在单元格B5中写下了以下公式。

=SUMPRODUCT(--(COLORINDEXOFRANGE(Data!D10:HO10,FALSE,1)=COLORINDEXOFONECELL(Data!$F$3,FALSE,1)))

当我从B5向下拖动时，公式中的单元格会改变为COLORINDEXOFRANGE(Data!D11:HO11,FALSE,1)。

但是我希望列数保持不变，只想改变行数，例如对于单元格C5，应为COLORINDEXOFRANGE(Data!E10:HO10,FALSE,1)，对于单元格D5，应为COLORINDEXOFRANGE(Data!F10:HO10,FALSE,1)，以此类推。

COLORINDEXOFRANGE是我在网上找到的内置函数。

请帮忙解决。谢谢。

- user1455116

---

我感到困惑。您想要的行为不是内置的默认行为吗？您无法通过在公式中使用“$”来实现您想要的吗？ - Brad

@Brad 我尝试使用 $ 符号，但是当我们向下拖动时，行编号会增加，例如D10变为D11而不是E10。正如Sean在下面建议的那样，我需要使用OFFSET，但是我无法确定在公式中应该写什么来使用OFFSET。 - user1455116

你想反转递增吗？垂直拖动通常会增加行属性，但你想保留行而增加列。水平拖动则相反，想要垂直偏移吗？就好像你已经转置一样？ - Brad

Answer 1

如果范围的结尾保持不变，则可以使用INDEX仅更改范围的起始位置，即将Data!D10:HO10替换为： =INDEX(Data!D$10:HO$10,ROWS(B$5:B5)):Data!HO$10 这种类型的INDEX公式只是半挥发性的（打开工作表时重新计算），这可能比完全挥发性的OFFSET和INDIRECT函数更可取。另外，ROWS函数比ROW更好，因为它能够处理插入（或删除）在公式上方的行而不改变结果。

- barry houdini

Answer 2

有几种方法可以避免将公式拖放到固定引用单元格中：

1. 直接回答您的问题，您可以使用ROW()引用当前行号，因此ROW() - 1给出前一行的行号。
2. 但是，在您的情况下，这种方法不起作用，因为您正在引用一个范围。另一个函数是INDIRECT()，请查看文档，但可以这样使用：INDIRECT("R5C" & COLUMN(), FALSE)
3. 由于选项#2往往会使公式变得非常冗长和（几乎）难以阅读，因此有时可以使用named range来解决问题。

- klonq

Answer 3

为了从水平参考转换为垂直参考，我使用了OFFSET函数，在另一个工作表中引用值，使用公式=OFFSET('Forecast'!$I$17,COLUMN()-3,0)，这样横向的列参考就变成了纵向的行参考。

- SeanC

---

嗨，我检查了一下，听起来很简单易懂，但是出于某些原因，当我向下拖动时，D并没有变成E，正如你所说的，10、HO和10保持不变，因为有一个$。你能再检查一下吗？ - user1455116

好的，我读错了 - 向下拖动不会改变它的横向移动方式，所以 D 不会改变。要进行这种更改，我使用了 OFFSET> 我会修正我的答案。 - SeanC

Answer 4

我曾经遇到过类似的问题：拖动数值时，它没有按预期递增。问题在于我对数据应用了筛选器。移除筛选器后，功能恢复正常。

- David Mitten

---

它应该是注释。 - ketan