英文:
How do we pull text columns basis on the dates using filter formula in google sheets?
问题
我已在表格中创建了一个示例表，其中我希望每年或根据需要每月的活跃客户，这些客户一直存在，直到丢失，并且将在每个期间内计数，直到丢失。
我希望使用筛选公式或任何其他可以帮助解决Google表格查询的公式来填充这个表。
附上示例电子表格，其中黄色区域是期望的结果。
我尝试了筛选公式，例如对于2022年：
=筛选(<Col a>, <colb>="<=2022-12-31",<Col D>="Live")
但这没有起作用，不清楚我们在2021年需要的逻辑。
英文:
I have created a sample table in the sheet where I want yearly, or if required monthly active clients which are recurring until they are lost and will be counted for each period until it gets lost.
I want to populate this using a filter formula or any other formula that might help solve the query in google sheets.
Attaching the sample spreadsheet to where the yellow area is the desired outcome.
https://docs.google.com/spreadsheets/d/1VIRp18HJyJ2UdfaHhaCpCHpfO6ctHW29TtP4iFJwYiU/edit#gid=0
I tried filter formula, for example for the year 2022
= filter(<Col a>, <colb>="<=2022-12-31",<Col D>="Live")
which did not work and do not know the logic of where we will need it for the year 2021
答案1
得分: 1
You may try:
=let(Σ, map(B2:B, C2:C, D2:D, lambda(b, c, d, if(or(b="",d="Inactive"),,year(if(c="-",now(),c))-year(b)+1))),
Δ, {tocol(map(A2:A, Σ, lambda(a, z, if(a="",,wraprows(a, z, a)))), 3), tocol(map(B2:B, Σ, lambda(b, z, if(b="",,sequence(1, z, year(b), 1))), 3)},
bycol(torow(unique(index(Δ, , 2))), lambda(z, {z; filter(index(Δ, , 1), index(Δ, , 2) = z})))
英文:
You may try:
=let(Σ,map(B2:B,C2:C,D2:D,lambda(b,c,d,if(or(b="",d="Inactive"),,year(if(c="-",now(),c))-year(b)+1))),
Δ,{tocol(map(A2:A,Σ,lambda(a,z,if(a="",,wraprows(a,z,a)))),3),tocol(map(B2:B,Σ,lambda(b,z,if(b="",,sequence(1,z,year(b),1)))),3)},
bycol(torow(unique(index(Δ,,2))),lambda(z,{z;filter(index(Δ,,1),index(Δ,,2)=z)})))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论