vba excel表格中自动增加行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12498987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Auto increment row number in excel sheet
提问by user1455116
I have the following formula written in the cell B5
我在单元格B5中写了以下公式
=SUMPRODUCT(--(COLORINDEXOFRANGE(Data!D10:HO10,FALSE,1)=COLORINDEXOFONECELL(Data!$F,FALSE,1)))
When i drag down from B5 to the next row, the cells in the formula change to COLORINDEXOFRANGE(Data!D11:HO11,FALSE,1)
当我从 B5 向下拖动到下一行时，公式中的单元格更改为 COLORINDEXOFRANGE(Data!D11:HO11,FALSE,1)
But i want the column number to remain the same and want just the row number to change to, for example COLORINDEXOFRANGE(Data!E10:HO10,FALSE,1)for the cell C5 and COLORINDEXOFRANGE(Data!F10:HO10,FALSE,1)for cell D5 and so on.
但我希望列号保持不变，只希望行号更改为，例如COLORINDEXOFRANGE(Data!E10:HO10,FALSE,1)单元格 C5 和COLORINDEXOFRANGE(Data!F10:HO10,FALSE,1)单元格 D5 等。
COLORINDEXOFRANGE is an inbuilt function that i found on the internet.
COLORINDEXOFRANGE 是我在互联网上找到的内置函数。
Please help. Thanks
请帮忙。谢谢
采纳答案by SeanC
to change from a horizontal to a vertical reference, I have used OFFSET, where I reference the values in another sheet using =OFFSET('Forecast'!$I$17,COLUMN()-3,0)so that the column() of the reference going across becomes the row reference
要将水平参考更改为垂直参考，我使用了OFFSET，我在其中引用了另一张工作表中的值，=OFFSET('Forecast'!$I$17,COLUMN()-3,0)以便穿过的引用的 column() 成为行引用
回答by barry houdini
If the end of the range remains the same then you could use INDEXto change the start of the range only, i.e. replace Data!D10:HO10with
如果范围的结尾保持不变，那么您可以使用INDEX仅更改范围的开头，即替换Data!D10:HO10为
=INDEX(Data!D$10:HO$10,ROWS(B$5:B5)):Data!HO$10
=INDEX(Data!D$10:HO$10,ROWS(B$5:B5)):Data!HO$10
INDEX formulas of this type are only semi-volatile (they re-calculate when sheet is opened) which is perhaps preferable to fully volatile OFFSETand INDIRECTfunctions. Also ROWSfunction is better than ROWas it will cope with rows being inserted (or deleted) above the formula without changing the result
这种类型的 INDEX 公式只是半挥发性的（它们在打开工作表时重新计算），这可能比完全挥发性OFFSET和INDIRECT函数更可取。也ROWS功能优于ROW因为它会与行应付被插入（或删除）的上面的公式，而不改变结果
回答by klonq
There are a few ways to get around this dragging a dropping formulas to fixed references cells :
有几种方法可以解决将拖放公式拖放到固定引用单元格的问题：
- To answer your question literally you can reference they current row number using
ROW(), soROW() - 1gives the previous row number. - But this isn't going to work in your case because you are referencing a range. Another function is
INDIRECT(), have a look at the documentation, but this could be used as such INDIRECT("R5C" & COLUMN(), FALSE) - Because option #2 tends to make formulas very long and (almost) unreadable sometimes you can get away with a named range
- 要从字面上回答您的问题，您可以使用 引用他们当前的行号
ROW()，因此ROW() - 1给出前一行号。 - 但这在您的情况下不起作用，因为您正在引用一个范围。另一个功能是
INDIRECT()，查看文档，但这可以用作 INDIRECT("R5C" & COLUMN(), FALSE) - 因为选项 #2 往往会使公式变得很长并且（几乎）不可读，有时您可以使用命名范围
回答by David Mitten
I had a similar problem: dragging a value down, didn't increment as expected. The problem was that I had filters applied to the data. Removing the filter restored the functionality.
我有一个类似的问题：向下拖动一个值，没有按预期增加。问题是我对数据应用了过滤器。移除过滤器恢复了功能。
