英文:
How to increment only if cell is not empty on input with IFNA in Google Sheets?
问题
I found this prior answer to the issue here:
But how to make it work on columns with IFNAs as blanks?
Demo:
表格:
| 1 | 只有一个测试。 | 1 | 一个 | 1 | 一个 | ||
|---|---|---|---|---|---|---|---|
| 2 | 2 | ||||||
| 2 | 只有两个测试。 | 3 | 两个 | 3 | 两个 | ||
| 4 | 4 | ||||||
| 3 | 只有三个测试。 | 5 | 三个 | 5 | 三个 | ||
| 6 | 6 | ||||||
| 4 | 只有四个测试。 | 7 | 四个 | 7 | 四个 | ||
| 8 | 8 | ||||||
| 9 | 9 | ||||||
| 10 | 10 | ||||||
| 5 | 只有五个测试。 | 11 | 五个 | 11 | 五个 | ||
| 12 | 12 |
公式:
A1:B12:
=arrayformula(
iferror(
countifs(row(B1:B), "<=" & row(B1:B), B1:B, "<>")
/
not(isblank(B1:B))
)
)
D1:D12:
=arrayformula(
iferror(
countifs(row(E1:E), "<=" & row(E1:E), E1:E, "<>")
/
not(isblank(E1:E))
)
)
=IFNA(ArrayFormula(REGEXEXTRACT(B1:B,"\\b([A-Z]{2,})+(?:\\s+[A-Z]+)*\\b")), "")
我尝试了带有IFNA的解决方案，但它没有起作用。
G1:H12:
=IFNA(arrayformula(
iferror(
countifs(row(H1:H), "<=" & row(H1:H), H1:H, "<>")
/
not(isblank(H1:H))
)
), "")
=IFNA(ArrayFormula(REGEXEXTRACT(E1:E,"\\b([A-Z]{2,})+(?:\\s+[A-Z]+)*\\b")), "")
非常感谢您的解决方案。
英文:
I found this prior answer to the issue here:
How to increment only if cell is not empty
But how to make it work on columns with IFNAs as blanks?
Demo:
<https://i.stack.imgur.com/GGOr6.gif>
Table:
| 1 | Test ONE and only. | 1 | ONE | 1 | ONE | ||
|---|---|---|---|---|---|---|---|
| 2 | 2 | ||||||
| 2 | Test TWO and only. | 3 | TWO | 3 | TWO | ||
| 4 | 4 | ||||||
| 3 | Test THREE and only. | 5 | THREE | 5 | THREE | ||
| 6 | 6 | ||||||
| 4 | Test FOUR and only. | 7 | FOUR | 7 | FOUR | ||
| 8 | 8 | ||||||
| 9 | 9 | ||||||
| 10 | 10 | ||||||
| 5 | Test FIVE and only. | 11 | FIVE | 11 | FIVE | ||
| 12 | 12 |
Formulas:
A1:B12:
=arrayformula(
iferror(
countifs(row(B1:B), "<=" & row(B1:B), B1:B, "<>")
/
not(isblank(B1:B))
)
)
D1:D12:
=arrayformula(
iferror(
countifs(row(E1:E), "<=" & row(E1:E), E1:E, "<>")
/
not(isblank(E1:E))
)
)
=IFNA(ArrayFormula(REGEXEXTRACT(B1:B,"\b([A-Z]{2,})+(?:\s+[A-Z]+)*\b")),"")
I tried solution with IFNA as below but it's not working.
G1:H12:
=IFNA(arrayformula(
iferror(
countifs(row(H1:H), "<=" & row(H1:H), H1:H, "<>")
/
not(isblank(H1:H))
)
),"")
=IFNA(ArrayFormula(REGEXEXTRACT(E1:E,"\b([A-Z]{2,})+(?:\s+[A-Z]+)*\b")),"")
Your solution is greatly appreciated.
答案1
得分: 1
以下是您要的代码翻译：
=ARRAYFORMULA(IF(ISNA(E:E),,SCAN(,E:E,LAMBDA(a,c,a+NOT(ISNA(c)))))
Update
=ARRAYFORMULA(IFNA(IF(D:D="",,SCAN(,D:D,LAMBDA(a,c,a+(IFNA(c)<>"")))))
英文:
Try this out:
=ARRAYFORMULA(IF(ISNA(E:E),,SCAN(,E:E,LAMBDA(a,c,a+NOT(ISNA(c))))))
Update
=ARRAYFORMULA(IFNA(IF(D:D="",,SCAN(,D:D,LAMBDA(a,c,a+(IFNA(c)<>""))))))
答案2
得分: 1
=index(if(H:H="",,scan(,H:H,lambda(a,c,if(c="",a,a+1)))))
英文:
You may try:
=index(if(H:H="",,scan(,H:H,lambda(a,c,if(c="",a,a+1)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论