英文:
Google sheet formula or query
问题
我需要帮助关于 Google 表格。我有一个表格，在其中评估竞争对手的个人订单，在“个人分数总和”和“个人订单”列中。
我还需要评估团队的订单 - 在“团队分数总和”和“团队订单”列中。但根据设置表中的设置，仅计算团队结果中的前2或3个最佳个人结果。
我无法创建查询或自定义公式...
英文:
I need help with google sheet. I have a table where I evaluate the individual order of competitors, in the column "Individual score sum" and "Individual order".
I would also need to evaluate the order of the teams - the column "Team score sum" and "Team order". But count only the 2 or 3 best individual results in the team result according to the settings in the setting table.
I can't create query or custom formula....
答案1
得分: 2
Team score sum
=map(B2:B;lambda(Σ;if(Σ="";;if(countif(B2:Σ;Σ)=1;sum(sortn(filter(C:C;B:B=Σ);setting!B1;;1;0));))))
Team order
=map(E2:E;lambda(Σ;if(Σ="";;rank(Σ;tocol(E2:E;1);0))))
英文:
You may try:
Team score sum
=map(B2:B;lambda(Σ;if(Σ="";;if(countif(B2:Σ;Σ)=1;sum(sortn(filter(C:C;B:B=Σ);setting!B1;;1;0));))))
Team order
=map(E2:E;lambda(Σ;if(Σ="";;rank(Σ;tocol(E2:E;1);0))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论