Google电子表格：基于多个条件的相对行编号 [英] Google spreadsheet: relative rows numbering based on multiple conditions

问题描述

在此前的帖子中，泰奥克提供了一个解决方案，使编号单元格在一个条件为空，跳过该单元格的编号，并将其带回到后续相关单元格，基本上，如果相邻的B单元格=值3，则使A单元格为空，并跳过编号;然后收回相对编号。

 
 |行nbr | B列| [说明] | 
 | --------- |：-------：| ------------------------- ------------ | 
 | R01 |值1 | | 
 | R02 |值1 | | 
 | R03 |价值2 | | 
 | |价值3 |中文B值= 3 =>中队A为空| 
 | R04 |价值2 |编号占用| 
 | R05 |值1 | | 
 | |价值3 |中文B值= 3 =>中队A为空| 
 | R06 |价值2 |编号占用| 
 |等等。 | | | 
 

这非常棒：

  = IF（INDIRECT（B& ROW（））=值3，，ROWS（INDIRECT（B&; B& ROW（））） -  COUNTIF（INDIRECT B&&B& ROW（）），Value 3））
  

..可以在示例spreadsheeet 。

现在我需要进一步了解，并且在多个（OR）条件下应用相同的逻辑，所以如果B单元格值=Value 3ORValue 2（不只是Value 3）。

我尝试在这里修改公式：

  = IF（OR（INDIRECT（B& ROW（））=Value 3; INDIRECT（B& ROW（））=Value 2 ; ; ROWS（INDIRECT（ B 及10&安培; ：B &安培; ROW（））） -  COUNTIFS（INDIRECT（ B 及10&安培; ：B &安培; ROW（））;价值3;（INDIRECT（B&; B& ROW（））;值2））））
  

冷杉t OR条件似乎OK我将COUNTIF切换到COUNTIFS，所以我可以使用多个条件，但这会给出错误。可能是因为我正在混合COUNTIFS和INDIRECT？因为即使这最后一部分也是一个错误：

  = COUNTIFS（INDIRECT（B& 10&：B & ROW（））;Value 3;（INDIRECT（B&&B;& ROW（））;Value 2））
  

对不起，如果我缺少明显的。

解决方案

p>组合的公式将足够长，以致将来难以维护和更新。问题是您正在生成相同的查询两次。

我建议在B插入一个新的列（将当前列B推到C），然后写一个公式，如 = IF（OR（C2 =value 2，C2 =value 3），skip，）

然后，为了清楚起见，将原始公式中的value 3更改为skip和（如果你愿意）隐藏帮助列B.这将给你一个公式更容易维护，你可以使列B中的布尔条件是复杂的，你喜欢没有重大困难。 p>

In this former post, Taosique kindly provided a solution to make numbering cell empty on ONE condition, skip numbering for this cell, and take it back on subsequent relevant cells, in a way that rows can be moved around and the (relative) numbering adapted.

Basically, if adjacent B cell = Value 3, make A cell empty, and skip numbering; then take back relative numbering.

| 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.    |         |                                     |

This works great:

=IF(INDIRECT("B"&ROW())="Value 3","",ROWS(INDIRECT("B"&10&":B"&ROW()))-COUNTIF(INDIRECT("B"&10&":B"&ROW()),"Value 3"))

..as can be seen on the example spreadsheeet.

Now I need to take this one step further, and apply that same logic with multiple (OR) conditions, so numbering gets skipped if B cell value = "Value 3" OR "Value 2" (not just "Value 3").

I tried adapting the formula here:

=IF(OR(INDIRECT("B"&ROW())="Value 3";INDIRECT("B"&ROW())="Value 2";"";ROWS(INDIRECT("B"&10&":B"&ROW()))-COUNTIFS(INDIRECT("B"&10&":B"&ROW());"Value 3";(INDIRECT("B"&10&":B"&ROW());"Value 2"))))

The first OR condition seems ok ; I switched COUNTIF to COUNTIFS, so I could use multiple criteria, but this gives an error. Maybe because I'm mixing COUNTIFS and INDIRECT? Because even this last part alone throws an error:

=COUNTIFS(INDIRECT("B"&10&":B"&ROW());"Value 3";(INDIRECT("B"&10&":B"&ROW());"Value 2"))

Sorry if I'm missing the obvious.

解决方案

The combined formula would be long enough that it would become difficult to maintain and update in the future. The problem is that you are generating the same query twice.

I recommend instead inserting a new column at B (which would push the current column B to C), and then writing a formula such as =IF(OR(C2="value 2",C2="value 3"),"skip","")

Then, for clarity's sake, change "value 3" in your original formula to "skip", and (if you wish) hide the helper column B. This would give you a formula far easier to maintain, and you can make the boolean condition in column B as complex as you like without significant difficulty.

这篇关于Google电子表格：基于多个条件的相对行编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！