英文:
Can I show the results of COUNTIF in one column only if there is data in another column?
问题
我有一个包含多个工作表的电子表格。
工作表A是从模板生成的，用户会在这里输入要格式化为报告的数据；在提交报告后，它将被删除。
当人们在工作表A的A列输入信息时，如果我们已经在这个电子表格中输入了客户信息，他们就不需要再次输入，否则他们需要转到工作表B并输入。
为了让他们更容易（这样他们就不必每次都检查，或者在生成报告时出现错误后返回并输入信息），我使用了条件格式设置。它的工作原理是，将Z列设置为辅助列，并使用“COUNTIF”函数检查工作表A列A中的客户ID是否在工作表B列C中找到。工作表A列Z返回0或1（或理论上，如果我们有重复项，可能会返回多个值），然后工作表A列A根据列Z的值进行条件格式设置--如果客户已经存在，客户ID的单元格在输入后会变绿色；如果没有，它会变红。
它运行得很好！但是，我正在为这些工作表添加脚本，并对它们进行循环。这个辅助列从Z1到Z1000填充，这意味着我不能使用sheet.maxRow()来获取最后一行。
我在脚本方面看到了很多解决方法，但我想知道是否有人有一种巧妙的方法，可以仅在列A中的相应单元格（同一行）具有值时，使用电子表格公式将一个值输入到辅助列Z中。
我怀疑使用带有筛选器的数组公式可能会做到这一点，但我对这两者都了解甚少，无法使其起作用。
感谢您的帮助！
英文:
I have a spreadsheet with multiple sheets.
Sheet A is generated from a template, and it is where users will enter in data to be formatted into a report; it will be deleted after that report is submitted.
As people enter info into Column A of Sheet A, they don't have to enter client information if we already have them entered in this spreadsheet, but otherwise they need to go over to Sheet B and enter it in.
To make it easier for them (so they don't have to check every time, or go back and enter info later after getting an error generating the report), I have used conditional formatting. The way it works is, column Z is set up as a helper column, and uses a "COUNTIF" function to check if the client ID in Sheet A, Column A is found in Sheet B, Column C. Sheet A Column Z returns 0 or 1 (or, theoretically, more than one if we had duplicates), and then Sheet A column A has conditional formatting based on Column Z's value-- if the client is already in, the cell for client ID turns green after they type it; if not, it turns red.
It works great! However, I am adding scripts to these sheets, and looping through them. This helper column is filled from Z1 to Z1000, which means I can't use sheet.maxRow() to get the last row.
I see plenty of workarounds on the script side, but I was wondering if anyone has a clever way to input a value into the helper column Z ONLY IF the corresponding cell (same row) in column A has a value using spreadsheet formulas.
I suspect that an array formula with a filter might do it, but I have little experience with either and can't get anything to work out.
Thanks for your help!
答案1
得分: 2
A2:INDEX(A2:A,MAX(ROW(A2:A)*(A2:A<>"")))
这段代码可以结合 INDEX、MAX 和 ROW 使用，使 ARRAYFORMULA 仅扩展至该列中具有值的最后一个单元格：
然后，你可以在 Z2 中设置以下公式（请检查范围以确保你的文本读取正确，并删除 Z 中的所有其他公式，以防你之前没有使用过 arrayformula）：
=BYROW(A2:INDEX(A2:A,MAX(ROW(A2:A)*(A2:A<>""))),LAMBDA(each,IF(each="","",COUNTIF('Sheet B'!C:C,each))))
英文:
You can use this expression with INDEX, MAX and ROW to make an ARRAYFORMULA only expandable until the last cell with value in column:
A2:INDEX(A2:A,MAX(ROW(A2:A)*(A2:A<>"")))
It will go from A2 to the maximum number of row in A in which A is different than null (that's why both conditions are multiplied).
Then you can set a formula like this in Z2 (check the ranges in case something is not right from reading your text, and delete all other formulas in Z too in case you weren't using an arrayformula already):
=BYROW(A2:INDEX(A2:A,MAX(ROW(A2:A)*(A2:A<>""))),LAMBDA(each,IF(each="","",COUNTIF('Sheet B'!C:C,each))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论