英文:
Create numbering in a column in excel that updates automatically but skips if there is an empty value in another column
问题
我有一个Excel表格，格式如下，我想创建一个编号，每当添加包含信息的新行时，该编号会自动更新，新行可以位于表格末尾，也可以插入在表格中间。
基本上，我只希望在列B不为空时，在列A中有一个编号。
我已经使用=IF(ISEMPTY(B18)," ",A15+1)来找到A18的值，但我不确定如何扩展到表格的其余部分。我可能需要查找前一个非空值，但我不确定如何做到这一点。
有什么建议吗？如果有更好的方法，请告诉我。
英文:
I have an excel sheet in the following format and I would like to create numbering that updates itself whenever a new row with information is added which could be both at the end of the sheet or could also be a line that is inserted in the middle of the sheet.
Basically I want there only to be a number in column A if column B is not empty.
I have used =IF(ISEMPTY(B18)," ",A15+1) to find the value for A18, but I'm not sure how to expand that to the rest of the sheet. Instead of A15, I would probably need to look for the previous non-empty value, but I'm not sure how to do that.
Any suggestions? Also, if there is a better way to do this, please let me know.
答案1
得分: 2
以下是您要翻译的内容：
尝试：
• 用于单元格 A2 的公式
=IF(B2="", "", MAX($A$1:A1)+1)
或者，
=IF(B2="", "", SUBTOTAL(3, B$2:B2))
使用 BYROW()
=BYROW(B2:B19, LAMBDA(x, LET(y, B2, IF(x="", "", COUNTIF(y:x, "<>")))))
英文:
Try:
• Formula used in cell A2
=IF(B2="","",MAX($A$1:A1)+1)
Or,
=IF(B2="","",SUBTOTAL(3,B$2:B2))
With BYROW()
=BYROW(B2:B19,LAMBDA(x,LET(y,B2,IF(x="","",COUNTIF(y:x,"<>")))))
答案2
得分: 1
=IF(A2="", "", "变量 " & COUNTA($A$2:$A2))
英文:
Try-
=IF(A2="","","Variable " & COUNTA($A$2:$A2))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论