问题描述
使用Google电子表格,我需要一列显示相对行号,以便:
Using Google spreadsheet, I need a column to show relative row numbering, so that:
- 电子表格行可以移动,编号保持相对(这意味着如果我在第4行和第5行之间移动第12行,则该行变为5,前5个变为6等)
- 使用基于单元格的条件给定行中的值,行编号单元格可能为空(或不是);如果为空,则该行号被跳过,并且编号在条件不匹配的下一行中备份。
这里是一个例子:
| Row nbr | B col. | [Explanation] |
|---------|:-------:|-------------------------------------|
| R01 | Value 1 | |
| R02 | Value 1 | |
| R03 | Value 2 | |
| | Value 3 | Col. B value = 3 => Col. A is empty |
| R04 | Value 2 | Numbering takes up |
| R05 | Value 1 | |
| | Value 3 | Col. B value = 3 => Col. A is empty |
| R06 | Value 2 | Numbering takes up |
| ETC. | | |
这里,条件(跳过编号)将为IF对应B单元=值3(然后跳过编号)
Here, the condition (to skip numbering) would be "IF corresponding B cell = Value 3" (then skip numbering)
现在,我正在使用一个符合上述第1项要求的公式:
Right now, I'm using a formula that matches requirement #1 above:
=ROW(INDIRECT("A"&ROW()&":A"))-9
(尾随的-9只是偏移量,所以编号可以从第10行开始)。
(The trailing "-9" is just an offset so numbering can start at 01 from row 10).
所以基本上,我需要适应更改)此代码,以便除了相对编号,对于行N,如果对应的B列单元格值= XYZ,则A列单元格为空(与编号):该行正在跳过,并且编号从N B细胞值≠XYZ的下一行。
So basically, I need to adapt (or change) this code so that besides relative numbering, for row N, if corresponding B column cell value = XYZ, then A column cell is empty (vs. numbered): that row is being skipped, and numbering takes back up from N on the next row where the B cell value ≠ XYZ.
这是一个。
许多TIA的。
推荐答案
我强烈推荐请不要在公式中使用明确指定的偏移量的 ROW()
,因为您应该删除/添加表上方的行,否则您将必须调整所有单元格中的公式。这是我的解决方案(列 A:A
是行号, B:B
- 值,开始在单元格 A10
)
I strongly recommend not to use ROW()
with explicitly specified offset in formula because should you delete/add rows above your table, your will have to adjust formulas in all cells. Here's my solution (column A:A
is row numbers, B:B
- values, start adding formula in cell A10
):
=IF($B10="Value 3","",ROWS($B$10:$B10)-COUNTIF($B$10:$B10,"Value 3"))
或者如果您坚持相对行(从顶部更改 10
到您的偏移量):
Or if you insist on relative rows (change 10
to your offset from top):
=IF(INDIRECT("B"&ROW())="Value 3","",ROWS(INDIRECT("B"&10&":B"&ROW()))-COUNTIF(INDIRECT("B"&10&":B"&ROW()),"Value 3"))
这篇关于Google电子表格相对行编号+跳过空单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!