英文:
Excel-Is is able to return current result if the condition fails
问题
以下表格描述了我的期望。
"空白计数"列是一个从A列到C列连续计数空白单元格的计数器。如果以下任何单元格有值，则停止计数并返回当前值。（请参见第6行和第7行）
基本上，我正在寻找一个公式，如果条件失败，可以返回当前计数器值。
这两个公式只是用于计算范围内的空白单元格：
=COUNTIF(B8:D8,"")
=SUM((IF((B8:D8)="",1,0)))
希望有人能帮忙解决这个连续的问题。
| 空白计数 | A列 | B列 | C列 |
|---|---|---|---|
| 0 | 1 | 2 | 3 |
| 1 | 2 | 3 | |
| 2(应为1) | 2 | ||
| 2(应为0) | 1 |
英文:
Below table describes what I expect for.
The "Count Blank" Column is a counter for counting continuous blank cells in order from Column A to Column C. If any of the following cell has value then stop counting and return current value. (plz see row6 and row7)
Basically, I'm looking for a formula which could return the current counter value if the condition fails.
| Count Blank | Column A | Column B | Column C |
|---|---|---|---|
| 0 | 1 | 2 | 3 |
| 1 | 2 | 3 | |
| 2 | 3 | ||
| 3 | |||
| 1 | 2 | ||
| 0 | 1 |
These two formulas are just for counting the blank cells in the range:
=COUNTIF(B8:D8,"")
=SUM((IF((B8:D8)="",1,0)))
Hoping anyone can help with the continuous problem.
| Count Blank | Column A | Column B | Column C |
|---|---|---|---|
| 0 | 1 | 2 | 3 |
| 1 | 2 | 3 | |
| 2(should be 1) | 2 | ||
| 2(should be 0) | 1 |
答案1
得分: 2
我找到了这个链接: https://exceljet.net/formulas/get-first-non-blank-value-in-a-list
你实际上不需要计算任何东西，只需要找到第一个非空单元格。你可以尝试使用这个公式：
=IFERROR(MATCH(FALSE;ISBLANK(B2:D2);0)-1;COLUMNS(B2:D2))
这是一个数组公式，所以在输入到单元格时按下 ctrl+shift+enter。
英文:
I found this: https://exceljet.net/formulas/get-first-non-blank-value-in-a-list
You don't really want to count anything, you just need to find the first non blank cell. You can try this formula:
=IFERROR(MATCH(FALSE;ISBLANK(B2:D2);0)-1;COLUMNS(B2:D2))
This is an array formula, so press ctrl+shift+enter when entering it into a cell.
答案2
得分: 0
你可以使用新的（截至2020年）REDUCE和LAMBDA函数：
=VALUE(REDUCE(0, B1:D1, LAMBDA(prev, curr, IF(ISTEXT(prev), prev, IF(curr = "", prev + 1, TEXT(prev,"0"))))))
英文:
You can use the new (as of 2020) reduce and lambda functions:
=VALUE(REDUCE(0, B1:D1, LAMBDA(prev, curr, IF(ISTEXT(prev), prev, IF(curr = "", prev + 1, TEXT(prev,"0"))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论