英文:
Excel Filter by Variable
问题
对于这个问题可能有一个非常简单的解决方法，但我只是无法弄清楚。我有像在这张图片中的以下数据：
唯一的ID由于变量的微妙差异而有多行。我需要一个过滤器或某种函数，可以标识只包含变量“b”的情况。我不希望那些既有“a”又有“b”的情况，只要“b”。
一个简单的表格过滤器对于“b”仍然会返回既有“a”又有“b”的ID。我需要一个过滤器/函数，只返回那些只包含“b”的情况。
非常感谢任何帮助。谢谢！
英文:
There is probably a super simple solution to this but I just cannot figure it out. I have data like the following in this image:
Unique IDs have multiple rows due to subtle nuances in variables. I need a filter or some kind of function that will demarcate the ones that just have variable "b" in this case. I do not want the ones that have both or "a". Just "b".
A simple table filter for "b" would still return IDs with both "a" and "b". I need a filter/function that will just return with ones that just have "b".
Any and all help is greatly appreciated. Thank you!
答案1
得分: 1
你可以使用FILTER公式：
=FILTER(A2:A14,(B2:B14="b")*(COUNTIF(A2:A14,A2:A14)=1))
或者
=LET(a,A2:B14,b,TAKE(a,,1),c,TAKE(a,,-1),FILTER(b,(c="b")*(COUNTIF(b,b)=1))
如果范围经常变化，可以更轻松地更改范围（不建议使用整个列如A:A，因为这会使公式计算变慢）。
结果：
英文:
You can use FILTER formula:
=FILTER(A2:A14,(B2:B14="b")*(COUNTIF(A2:A14,A2:A14)=1))
or
=LET(a,A2:B14,b,TAKE(a,,1),c,TAKE(a,,-1),FILTER(b,(c="b")*(COUNTIF(b,b)=1)))
to change range easier if it changes often (wouldn't recommend using whole column like A:A as it will make formula to calculate slow)
Result:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论